Time Metrics is in Beta - reach out to support@honeydew.ai to enable in your account

Introduction

Modeling time based metrics is a common need of semantic layers. The Time Metric accessor is a wrapper around Honeydew metrics to allow easily defining time metrics.

Time Metrics require a Time Spine defined to join to.

The time metric allows to extend a pure aggregation with time properties - grain, offest, join field to a date spine and more.

Time Metrics

This creates a time metric from an aggregation:

TIME_METRIC(
	metric or aggergate 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.

Use date_field when each event is counted once at the associated grain.

Use date_range_start, date_range_end when events are counted in each matching date rage - for example cumulative metrics or grain-to-date metrics.

See examples below for different use cases.

date_range_start and date_field fields are mutually exlcusive.

Composing Time Metrics

It is possible to compose time metrics, 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.

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)

Note this can be expanded further with a TIME_METRIC that just adds a grain without changing the date_field

Revenue based on a different date spine

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

Monthly revenue

TIME_METRIC(order_lines.revenue, grain => month)

Note this can be expanded further with a TIME_METRIC that just adds a date_field without changing the grain

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')

Offset is applied by default as an interval to the past (previous) period. By reversing it can look at the future (next) period.

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)

Uses the MAX_BY aggregation to take the value of the last date in the group

Revenue of the 2nd week of the month

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

Note this is a monthly grain metric (there is a value per month that counts only on 2nd week)

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

CityMonthRevenueContribution
Tel AvivJan1010/60
Tel AvivFeb2020/60
Tel AvivMarch3030/60
LondonJan4040/…

Examples - Cumulative Metrics

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)

This metric uses the inteval function to define where to accumulate from, using the expression orders.close_date - interval '30 days' to start 30 days before the date an event 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')

The 1 day offset is applied to both date_range_start and date_range_end

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)

This metric uses the DATE_TRUNC function to define where to accumulate from, using the expression DATE_TRUNC('month', orders.close_date) to start at beginning of a month

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)

This metric uses the DATE_TRUNC function to define where to accumulate from, using the expression DATE_TRUNC('year', orders.close_date) to start at beginning of a year

Monthly open deals

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

This metric uses a range of dates between the deal creation and the deal closing, to calculate the number of open deals during each month.