Time Aware Metrics

Time Metrics is currently in Beta. Contact support@honeydew.ai to activate it for your account.

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:

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).
Comparing time periods (i.e. current month event count vs previous month)
Building period-to-date aggregations (i.e. a year-to-date total count metric)

Time Metrics require a Time Spine entity.

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:

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

Once an entity is connected to a time spine, it is considered a “time-aware” entity.

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

date.year	lineitem.sum_qty
NULL	153,078,795

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:

date.year	lineitem.sum_qty
1992-01-01	19,305,356
1993-01-01	23,184,525
…

Default date field will use the grain defined in the referenced field.

Use the default time spine when all metrics on an entity are always counted on the same date field. That makes time metrics natural and automatic.However, when multiple date fields are possible (such as lineitem that has both a shipping date and an order received date) then metrics operating on a different date field will need to explicitly set the date field they operate on.

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.

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.

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

date.year	lineitem.sum_qty
NULL	153,078,795

A regular metric does not know how to relate to a time spine, thus will receive NULL as is the value for date fields.

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

date.year	lineitem.sum_qty_when_shipped
1992-01-01	19,305,356
1993-01-01	23,184,525
1994-01-01	23,189,319
1995-01-01	23,343,871
1996-01-01	23,307,638
1997-01-01	23,247,128
1998-01-01	17,500,958

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:

date.year	lineitem.sum_qty_when_shipped	lineitem.sum_qty_when_received
1992-01-01	19,305,356	18,326,418
1993-01-01	23,184,525	23,177,759
1994-01-01	23,189,319	23,198,628
1995-01-01	23,343,871	23,338,423
1996-01-01	23,307,638	23,294,742
1997-01-01	23,247,128	23,250,072
1998-01-01	17,500,958	18,484,509

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

date.year	lineitem.sum_qty_when_shipped	lineitem.sum_qty_when_shipped_prev_year
1992-01-01	19,305,356	NULL
1993-01-01	23,184,525	19,305,356
1994-01-01	23,189,319	23,184,525
1995-01-01	23,343,871	23,189,319
1996-01-01	23,307,638	23,343,871
1997-01-01	23,247,128	23,307,638
1998-01-01	17,500,958	23,247,128
1999-01-01	NULL	17,500,958

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)

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 time 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

City	Month	Revenue	Contribution
Tel Aviv	Jan	10	10/60
Tel Aviv	Feb	20	20/60
Tel Aviv	March	30	30/60
London	Jan	40	40/…

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)

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.

Quick Start

Semantic Ecosystem

Modeling Reference

Context Layer

Management

Integrations

Security

Release Notes

Introduction

Connecting Entities to Time

Example

Time Metrics

Example

Composing Time Metrics

Combining qualifiers

Common Examples

Revenue on close date

Revenue based on a different time spine

Monthly revenue

Monthly revenue at previous month

Monthly revenue at next month

Same time last year

Balance on the last day of the month

Revenue of the 2nd week of the month

MoM growth

Daily contribution

Monthly contribution to monthly total

Monthly contribution to city

Common Examples - Cumulative

Last 30 days rolling revenue

Last 30 days rolling revenue, yesterday

Month-to-date (MTD) revenue

Year-to-date (YTD) revenue

Monthly open deals

​Introduction

​Connecting Entities to Time

​Example

​Time Metrics

​Example

​Composing Time Metrics

​Combining qualifiers

​Common Examples

​Revenue on close date

​Revenue based on a different time spine

​Monthly revenue

​Monthly revenue at previous month

​Monthly revenue at next month

​Same time last year

​Balance on the last day of the month

​Revenue of the 2nd week of the month

​MoM growth

​Daily contribution

​Monthly contribution to monthly total

​Monthly contribution to city

​Common Examples - Cumulative

​Last 30 days rolling revenue

​Last 30 days rolling revenue, yesterday

​Month-to-date (MTD) revenue

​Year-to-date (YTD) revenue

​Monthly open deals

Introduction

Connecting Entities to Time

Example

Time Metrics

Example

Composing Time Metrics

Combining qualifiers

Common Examples

Revenue on close date

Revenue based on a different time spine

Monthly revenue

Monthly revenue at previous month

Monthly revenue at next month

Same time last year

Balance on the last day of the month

Revenue of the 2nd week of the month

MoM growth

Daily contribution

Monthly contribution to monthly total

Monthly contribution to city

Common Examples - Cumulative

Last 30 days rolling revenue

Last 30 days rolling revenue, yesterday

Month-to-date (MTD) revenue

Year-to-date (YTD) revenue

Monthly open deals