Metric Filters

For Data Analysts

Metric filters allow you to constrain which data is included in a metric calculation. Filters are applied at the metric level and persist across all queries that use the metric.

Overview

Filters serve several purposes:

• Data Quality - Exclude invalid or test data
• Business Logic - Apply consistent business rules
• Segmentation - Create segment-specific metrics
• Security - Enforce data access boundaries

Filter Syntax

Basic Filter

metrics:
  - name: completed_orders
    type: simple
    expression: orders.order_count
    filters:
      - field: status
        operator: equals
        value: completed

Multiple Filters

Multiple filters are combined with AND logic:

metrics:
  - name: us_enterprise_revenue
    type: simple
    expression: orders.total_revenue
    filters:
      - field: country
        operator: equals
        value: US
      - field: customer_tier
        operator: equals
        value: enterprise
    description: Revenue from US enterprise customers only

Filter Operators

Equality Operators

filters:
  # Exact match
  - field: status
    operator: equals
    value: active

  # Not equal
  - field: status
    operator: not_equals
    value: cancelled

Comparison Operators

filters:
  # Greater than
  - field: amount
    operator: gt
    value: 100

  # Greater than or equal
  - field: amount
    operator: gte
    value: 100

  # Less than
  - field: amount
    operator: lt
    value: 1000

  # Less than or equal
  - field: amount
    operator: lte
    value: 1000

List Operators

filters:
  # Value in list
  - field: region
    operator: in
    value: [US, CA, MX]

  # Value not in list
  - field: channel
    operator: not_in
    value: [test, internal, demo]

String Operators

filters:
  # Contains substring
  - field: email
    operator: contains
    value: "@company.com"

  # Does not contain substring
  - field: name
    operator: not_contains
    value: "TEST"

Null Operators

filters:
  # Is null
  - field: deleted_at
    operator: is_null

  # Is not null
  - field: verified_at
    operator: is_not_null

Operator Reference

Operator	SQL Equivalent	Value Required
equals	=	Yes
not_equals	!=	Yes
gt	>	Yes
gte	>=	Yes
lt	<	Yes
lte	<=	Yes
in	IN (...)	Yes (list)
not_in	NOT IN (...)	Yes (list)
contains	LIKE '%...%'	Yes
not_contains	NOT LIKE '%...%'	Yes
is_null	IS NULL	No
is_not_null	IS NOT NULL	No

Practical Examples

Excluding Test Data

metrics:
  - name: production_revenue
    type: simple
    expression: orders.total_revenue
    filters:
      - field: is_test
        operator: equals
        value: false
      - field: customer_email
        operator: not_contains
        value: "@test.com"
    description: Revenue excluding test orders and test accounts

Status-Based Filtering

metrics:
  - name: successful_transactions
    type: simple
    expression: payments.transaction_count
    filters:
      - field: status
        operator: in
        value: [completed, settled, cleared]
      - field: voided_at
        operator: is_null
    description: Count of successfully processed transactions

Time-Based Constraints

metrics:
  - name: recent_active_users
    type: simple
    expression: users.active_count
    filters:
      - field: last_login_days_ago
        operator: lte
        value: 30
    description: Users who logged in within the last 30 days

Geographic Segmentation

metrics:
  - name: emea_revenue
    type: simple
    expression: orders.total_revenue
    filters:
      - field: region
        operator: in
        value: [UK, DE, FR, IT, ES, NL, BE, SE, NO, DK, FI, IE, AT, CH]
    description: Revenue from EMEA region countries

Numeric Range Filtering

metrics:
  - name: high_value_orders
    type: simple
    expression: orders.order_count
    filters:
      - field: order_amount
        operator: gte
        value: 1000
    description: Orders with value of $1000 or more

Combining Multiple Conditions

metrics:
  - name: qualified_leads
    type: simple
    expression: leads.lead_count
    filters:
      - field: status
        operator: equals
        value: qualified
      - field: source
        operator: not_in
        value: [spam, bot, test]
      - field: email
        operator: is_not_null
      - field: score
        operator: gte
        value: 50
    description: |
      Qualified leads that meet all criteria:
      - Status is qualified
      - Not from spam/bot/test sources
      - Has valid email
      - Lead score >= 50

Filter Inheritance

Metric Filters vs Query Filters

Metric filters are always applied, while query filters are additional:

# Metric definition with built-in filter
metrics:
  - name: active_subscriptions
    type: simple
    expression: subscriptions.subscription_count
    filters:
      - field: status
        operator: equals
        value: active

When querying, additional filters combine with metric filters:

{
  "metrics": ["active_subscriptions"],
  "filters": [
    {"field": "plan", "operator": "equals", "value": "enterprise"}
  ]
}

The resulting query applies both filters: status = 'active' AND plan = 'enterprise'.

Creating Metric Variants

Use filters to create metric variants for different segments:

metrics:
  - name: total_revenue
    type: simple
    expression: orders.total_revenue
    description: Total revenue across all segments

  - name: b2b_revenue
    type: simple
    expression: orders.total_revenue
    filters:
      - field: customer_type
        operator: equals
        value: business
    description: Revenue from business customers

  - name: b2c_revenue
    type: simple
    expression: orders.total_revenue
    filters:
      - field: customer_type
        operator: equals
        value: consumer
    description: Revenue from consumer customers

Filter Best Practices

Document Filter Logic

Always explain why filters exist:

metrics:
  - name: net_revenue
    type: simple
    expression: orders.total_revenue
    filters:
      - field: status
        operator: not_in
        value: [refunded, cancelled, disputed]
    description: |
      Net revenue excluding non-revenue transactions.

      ## Excluded Statuses
      - refunded: Full refunds processed
      - cancelled: Orders cancelled before fulfillment
      - disputed: Orders under chargeback review

Use Consistent Field Names

Reference dimension fields consistently across metrics:

# Good: Consistent field naming
filters:
  - field: customer_status
    operator: equals
    value: active

# Avoid: Inconsistent references
filters:
  - field: cust_stat  # Abbreviated
    operator: equals
    value: active

Avoid Over-Filtering

Keep filters focused and avoid complex nested logic:

# Good: Clear, focused filters
filters:
  - field: is_valid
    operator: equals
    value: true
  - field: region
    operator: in
    value: [US, CA]

# Avoid: Too many conditions that belong in the cube
filters:
  - field: condition_1
    operator: equals
    value: x
  - field: condition_2
    operator: gte
    value: 10
  - field: condition_3
    operator: not_in
    value: [a, b, c, d, e, f]
  # ... many more conditions

If you need complex filtering, consider creating a filtered model or cube segment instead.

Test Filter Behavior

Verify filters produce expected results:

# Query with the metric
curl -X POST http://localhost:8000/api/v1/query \
  -H "Content-Type: application/json" \
  -d '{
    "metrics": ["active_subscriptions"],
    "dimensions": ["subscriptions.plan"]
  }'

Next Steps

• Time Grains - Configure time-based aggregations
• Metric Types - Learn about simple, derived, and ratio metrics