Cumulative metrics
Cumulative metrics aggregate a measure over a given accumulation window. If no window is specified, the window is considered infinite and accumulates values over all time. You will need to create a time spine model before you add cumulative metrics.
Cumulative metrics are useful for calculating things like weekly active users, or month-to-date revenue. The parameters, description, and types for cumulative metrics are:
Note that we use the double colon (::) to indicate whether a parameter is nested within another parameter. So for example, measure::name means the name parameter is nested under measure.
Parameters
Complete specification
The following displays the complete specification for cumulative metrics, along with an example:
Cumulative metrics example
Cumulative metrics measure data over a given window and consider the window infinite when no window parameter is passed, accumulating the data over all time.
The following example shows how to define cumulative metrics in a YAML file:
Window options
This section details examples of when to specify and not to specify window options.
- When a window is specified, MetricFlow applies a sliding window to the underlying measure, such as tracking weekly active users with a 7-day window.
- Without specifying a window, cumulative metrics accumulate values over all time, useful for running totals like current revenue and active subscriptions.
Grain to date
You can choose to specify a grain to date in your cumulative metric configuration to accumulate a metric from the start of a grain (such as week, month, or year). When using a window, such as a month, MetricFlow will go back one full calendar month. However, grain to date will always start accumulating from the beginning of the grain, regardless of the latest date of data.
For example, let's consider an underlying measure of order_total.
measures:
- name: order_total
agg: sum
We can compare the difference between a 1-month window and a monthly grain to date.
- The cumulative metric in a window approach applies a sliding window of 1 month
- The grain to date by month resets at the beginning of each month.
Cumulative metric with grain to date:
SQL implementation example
To calculate the cumulative value of the metric over a given window we do a time range join to a timespine table using the primary time dimension as the join key. We use the accumulation window in the join to decide whether a record should be included on a particular day. The following SQL code produced from an example cumulative metric is provided for reference:
To implement cumulative metrics, refer to the SQL code example:
select
count(distinct distinct_users) as weekly_active_users,
metric_time
from (
select
subq_3.distinct_users as distinct_users,
subq_3.metric_time as metric_time
from (
select
subq_2.distinct_users as distinct_users,
subq_1.metric_time as metric_time
from (
select
metric_time
from transform_prod_schema.mf_time_spine subq_1356
where (
metric_time >= cast('2000-01-01' as timestamp)
) and (
metric_time <= cast('2040-12-31' as timestamp)
)
) subq_1
inner join (
select
distinct_users as distinct_users,
date_trunc('day', ds) as metric_time
from demo_schema.transactions transactions_src_426
where (
(date_trunc('day', ds)) >= cast('1999-12-26' as timestamp)
) AND (
(date_trunc('day', ds)) <= cast('2040-12-31' as timestamp)
)
) subq_2
on
(
subq_2.metric_time <= subq_1.metric_time
) and (
subq_2.metric_time > dateadd(day, -7, subq_1.metric_time)
)
) subq_3
)
group by
metric_time,
limit 100;
Limitations
If you specify a window in your cumulative metric definition, you must include metric_time as a dimension in the SQL query. This is because the accumulation window is based on metric time. For example,
select
count(distinct subq_3.distinct_users) as weekly_active_users,
subq_3.metric_time
from (
select
subq_2.distinct_users as distinct_users,
subq_1.metric_time as metric_time
group by
subq_3.metric_time