Time Aware Metrics

​

Introduction

Defining how metrics relate to time is a common modeling need. The time metrics framework allows to build time-aware metrics.

Time awareness allows:

1. Filling in missing dates when data is incomplete (i.e. when counting events for a particular day, should count 0 if the day has no events at all).
2. Comparing time periods (i.e. current month event count vs previous month)
3. Building period-to-date aggregations (i.e. a year-to-date total count metric)

​

Connecting Entities to Time

Entities in Honeydew can be connected to a time spine on a default date field. Once connected, all metrics on the entity operate in relation to the default date field:

1. Dates missing in the default date field will be filled
2. Time offsets and period-to-date calculations in the entity will be relative to the default date field.

​

Example

When the lineitem entity is not connected to a time spine, metrics operating on it can’t relate to date.

For example, quantity by year will yield

However once the lineitem entity is connected to a time spine then its metrics become time aware.

For example, can add the lineitem.l_shipdate field as a default date field for lineitem:

type: entity
name: lineitem
relations:
  - target_entity: date
    rel_type: many-to-one
    rel_join_type: right
    cross_filtering: one-to-many
    connection:
      - src_field: l_shipdate
        target_field: date

Then the sum_qty metric will be counted by shipping date:

​

Time Metrics

This creates a time metric from an aggregation:

TIME_METRIC(
	metric or aggregate expression,
    offset => interval,
    -- join based on field and grain
	grain => grain (day/week/month/quarter/year),
    date_field => join attribute,
    -- join based on a range
    date_range_start => join range start date expression,
	date_range_end => join range end date expression
)

Parameters:

• expression: either a Honeydew metric or aggregation expression. Must not include a GROUP BY.
• offset: based on Snowflake interval expression syntax, sets a negative interval to offset the grain. Note that if has a higher grain than the chosen grain (i.e. a week interval on a monthly metric) it might get truncated to the chosen grain.
• grain: based on Snowflake date or time part syntax - day/week/month/quarter/year. Sets the minimal grain of a metric.
• date_field: a field that is used to join to the time spine. When combined with grain a date_field is truncated to the grain.
• date_range_start, date_range_end: a pair of fields that are used to join to the time spine.

All parameters (except for expression) are optional. However, a time metric is valid only with join configuration (date_field or date_range_*) set.

​

Example

Consider the following metric sum_qty in lineitem entity:

sum(lineitem.l_quantity)

Time metrics connect automatically to the time spine entity date.

However sum_qty is not a time metric.

A query that groups it by date.year will have a result such as

If a time metric named sum_qty_when_shipped, that counts shipped quantity, is built of top of it

TIME_METRIC(
    lineitem.sum_qty,
    date_field => lineitem.l_shipdate)

Then connecting it to date will be done by joining it to the l_shipdate column, attributing all quantity to the shipping date

However, a different metric sum_qty_when_received may count quantity to be shipped by counting quantity on the date an order was received:

TIME_METRIC(
    lineitem.sum_qty,
    date_field => lineitem.l_receiptdate)

Comparing the two would show that shipped quantity is less than the quantity to be shipped at the first year, and then follows:

May also use time metrics to look at a previous time period. For example, the following metric sum_qty_when_shipped_prev_year

TIME_METRIC(
    lineitem.sum_qty,
    date_field => lineitem.l_shipdate,
    offset => '1 year')

This can be used for YoY growth calculations (note to coalesce the null to zero).

​

Composing Time Metrics

​

Combining qualifiers

It is possible to compose time metrics by combining qualifiers, i.e.

orders.t1 = TIME_METRIC(orders.count, date_field => orders.order_date)
-- compose a time metric by adding a grain to t1
orders.t2 = TIME_METRIC(orders.t1, grain => month)
-- compose a time metric by adding an offset to t2
orders.t3 = TIME_METRIC(orders.t2, offset => '1 month')

-- t3 is equivalent to
orders.t3_full = TIME_METRIC(
    orders.count,
    date_field => orders.order_date,
    grain => month,
    offset => '1 month'
)

Note that a time metric is computable only when has both date_field and grain set.

​

Common Examples

​

Revenue on close date

A metric that sets the time spine, but not a grain.

TIME_METRIC(order_lines.revenue, date_field => orders.close_date)

​

Revenue based on a different time spine

TIME_METRIC(order_lines.revenue, date_field => orders.shipping_date)

​

Monthly revenue

TIME_METRIC(order_lines.revenue, grain => month)

​

Monthly revenue at previous month

TIME_METRIC(order_lines.revenue, grain => month, offset => '1 month')

​

Monthly revenue at next month

TIME_METRIC(order_lines.revenue, grain => month, offset => '-1 month')

​

Same time last year

TIME_METRIC(order_lines.revenue, offset => '12 months')

​

Balance on the last day of the month

TIME_METRIC(MAX_BY(orders.balance, orders.date), grain => month)

​

Revenue of the 2nd week of the month

TIME_METRIC(order_lines.revenue, grain => month) FILTER (WHERE WEEK(date.month) = 2)

​

MoM growth

TIME_METRIC(order_lines.revenue, grain => month) /
TIME_METRIC(order_lines.revenue, grain => month, offset = '1 month')

​

Daily contribution

TIME_METRIC(order_lines.revenue, grain => day) /
TIME_METRIC(order_lines.revenue, grain => month)

​

Monthly contribution to monthly total

TIME_METRIC(order_lines.revenue, grain => month) /
TIME_METRIC(order_lines.revenue, grain => month) GROUP BY (date.month)

​

Monthly contribution to city

TIME_METRIC(order_lines.revenue, grain => month) / (order_lines.revenue GROUP BY (*, orders.city))

Generates

​

Common Examples - Cumulative

​

Last 30 days rolling revenue

TIME_METRIC(order_lines.revenue, date_range_start = orders.close_date - interval '30 days', date_range_end => orders.close_date)

Note this is a daily metric (each day has a different value) but uses a window to accumulate.

​

Last 30 days rolling revenue, yesterday

TIME_METRIC(order_lines.revenue, date_range_start = orders.close_date - interval '30 days', date_range_end => orders.close_date, offset = '1 day')

​

Month-to-date (MTD) revenue

TIME_METRIC(order_lines.revenue, date_range_start => DATE_TRUNC('month', orders.close_date), date_range_end => orders.close_date)

​

Year-to-date (YTD) revenue

TIME_METRIC(order_lines.revenue, date_range_start => DATE_TRUNC('year', orders.close_date), date_range_end => orders.close_date)

​

Monthly open deals

TIME_METRIC(deals.count, grain => month, date_range_start => deals.create_date, date_range_end => deals.close_date)