Ratio Metrics

What is a ratio metric?

Ratio metrics create a ratio out of two measures. You must define a numerator and a denominator. The metric can include a constraint string which applies a dimensional filter to both numerator and denominator when computing the metric.

  # Define the measures from the data source as numerator or denominator
  type: ratio 
  type_params:
    numerator: cancellations_usd
    denominator: transaction_amount_usd
  constraint: | # add optional constraint string. This applies to both the numerator and denominator
      is_internal = false

What happens if the numerator and denominator are from different data sources?

If the numerator and denominator are measures from different data sources, MetricFlow will calculate the values in subqueries, then join the result set on common dimensions to calculate the final ratio. An example of the generated sql from a ratio metrics with measures from different data sources is below:

🔎 SQL executed for successful query (remove --explain to see data): 
-- Join Aggregated Measures with Standard Outputs
-- Pass Only Elements:
--   ['metric_time', 'mql_queries_created_test', 'distinct_query_users']
-- Compute Metrics via Expressions
-- Order By [] Limit 100
SELECT
  subq_15577.metric_time AS metric_time
  , CAST(subq_15577.mql_queries_created_test AS DOUBLE) / CAST(NULLIF(subq_15582.distinct_query_users, 0) AS DOUBLE) AS mql_queries_per_active_user
FROM (
  -- Aggregate Measures From Numerator
  SELECT
    metric_time
    , SUM(mql_queries_created_test) AS mql_queries_created_test
  FROM (
    -- Read Elements From Data Source 'mql_queries_test'
    -- Pass Only Additive Measures
    -- Metric Time Dimension 'ds'
    -- Pass Only Elements:
    --   ['mql_queries_created_test', 'metric_time']
    SELECT
      CAST(query_created_at AS DATE) AS metric_time
      , case when query_status IN ('PENDING','MODE') then 1 else 0 end AS mql_queries_created_test
    FROM prod_dbt.mql_query_base mql_queries_test_src_2552 -- Numerator Data Source
  ) subq_15576
  GROUP BY
    metric_time
) subq_15577
INNER JOIN (
  -- Aggregate Measures From Denominator
  SELECT
    metric_time
    , COUNT(DISTINCT distinct_query_users) AS distinct_query_users
  FROM (
    -- Read Elements From Data Source 'mql_queries'
    -- Pass Only Additive Measures
    -- Metric Time Dimension 'ds'
    -- Pass Only Elements:
    --   ['distinct_query_users', 'metric_time']
    SELECT
      CAST(query_created_at AS DATE) AS metric_time
      , case when query_status in ('MODE','PENDING') then email else null end AS distinct_query_users
    FROM prod_dbt.mql_query_base mql_queries_src_2585 --Denominator Data Source
  ) subq_15581
  GROUP BY
    metric_time
) subq_15582
ON -- Join on Common Dimensions
  (
    (
      subq_15577.metric_time = subq_15582.metric_time
    ) OR (
      (
        subq_15577.metric_time IS NULL
      ) AND (
        subq_15582.metric_time IS NULL
      )
    )
  )
LIMIT 100

Adding constraints to the numerator or denominator in a ratio metric

Users can now define constraints on input measures for a metric by applying a constraint directly to the measure, like so:

metric:
  name: frequent_purchaser_ratio
  description: Fraction of active users who qualify as frequent purchasers
  owners:
    - support@transformdata.io
  type: ratio
  locked_metadata:
    value_format: ".2%"
  type_params:
    numerator:
      name: distinct_purchasers
      constraint: is_frequent_purchaser
      alias: frequent_purchasers
    denominator:
      name: distinct_purchasers

Note the new constraint and alias parameters for the measure referenced in the numerator. The constraint parameter defines the constraint the user wishes to apply. That constraint filter will be applied to the measure it is attached to, and no others.

The alias parameter is only necessary if the same measure is used more than once in the metric but with different constraints, as shown above. This is necessary to avoid column name collisions in the rendered SQL queries. If all input measures are already distinct, the alias parameter may be omitted.