Skip to main content

Cumulative Metrics

What is a cumulative metric?#

Cumulative metrics aggregate a measure over a given window. If no window is specified, the window is considered infinite and would accumulate the measure over all time.

  # Define the measure and the window.  type: cumulative  type_params:    measures:      - distinct_users    # the default window is infinity - omitting window will accumulate the measure over all time    window: 7 days

Window Options#

Omitting window will accumulate the measure over all time. Otherwise, the available granularites are:

  • day or days
  • week or weeks
  • quarter or quarters
  • month or months

You can use simple phrases to describe the window:

  • 7 days
  • 1 month
  • 3 months

Grain to Date#

You can optionally specify grain to date in your cumulative metric configuration to accummulate the metric from the start of a grain (i.e. week, year, month). When using a window, like month, Transform will go back one calendar month, whereas grain to date will always start at the beginning of the grain regardless of the latest date of data.

For example, in this case we are calculating cumulative transaction revenue month to date using grain_to_date. 

metric:  name: "revenue_mtd"  description: "revenue mtd"  owners:    - bobby@transformdata.io  type: cumulative  type_params:    measures:      - txn_revenue    grain_to_date: month

Implementation#

The current implementation for this metric runs a join on dates:

SELECT  COUNT(DISTINCT b.user_id)  , a.dsFROM (  SELECT DISTINCT ds  FROM main.fct_messages) aLEFT OUTER JOIN main.fct_messages bON b.ds <= a.ds AND b.ds >= DATE(a.ds, '-7 day')GROUP by a.dsORDER BY a.ds

There is active development to improve the efficiency of this metric type.