> ## Documentation Index
> Fetch the complete documentation index at: https://honeydew.ai/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# Time Aware Metrics

<Info>
  Time Metrics is currently in Beta. Contact [support@honeydew.ai](mailto:support@honeydew.ai) to activate it for your account.
</Info>

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

<Warning>
  Time Metrics require a [Time Spine](/advanced-modeling/time-spines) entity.
</Warning>

## Connecting Entities to Time

Entities in Honeydew can be connected to a time spine on a [default date field](/advanced-modeling/time-spines#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.

<Tip>
  Once an entity is connected to a time spine, it is considered a "time-aware" entity.
</Tip>

### 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](/advanced-modeling/time-spines#default-date-field) then
its metrics become time aware.

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

```yaml theme={null}
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        |
| ...        |                   |

<Note>
  Default date field will use the grain defined in the referenced field.
</Note>

<Tip>
  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.
</Tip>

## Time Metrics

This creates a time metric from an aggregation:

```sql theme={null}
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](https://docs.snowflake.com/en/sql-reference/data-types-datetime#interval-constants), 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](https://docs.snowflake.com/en/sql-reference/functions-date-time#label-supported-date-time-parts) - 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.

<Tip>
  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.
</Tip>

<Info>
  `date_range_start` and `date_field` fields are mutually exlcusive.
</Info>

## Example

Consider the following metric `sum_qty` in `lineitem` entity:

```sql theme={null}
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       |

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

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

```sql theme={null}
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:

```sql theme={null}
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`

```sql theme={null}
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.

```sql theme={null}
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.

```sql theme={null}
TIME_METRIC(order_lines.revenue, date_field => orders.close_date)
```

<Tip>
  Note this can be expanded further with a `TIME_METRIC` that just adds a grain without changing the date\_field
</Tip>

### Revenue based on a different time spine

```sql theme={null}
TIME_METRIC(order_lines.revenue, date_field => orders.shipping_date)
```

### Monthly revenue

```sql theme={null}
TIME_METRIC(order_lines.revenue, grain => month)
```

<Tip>
  Note this can be expanded further with a TIME\_METRIC that just adds a date\_field without changing the grain
</Tip>

### Monthly revenue at previous month

```sql theme={null}
TIME_METRIC(order_lines.revenue, grain => month, offset => '1 month')
```

### Monthly revenue at next month

```sql theme={null}
TIME_METRIC(order_lines.revenue, grain => month, offset => '-1 month')
```

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

### Same time last year

```sql theme={null}
TIME_METRIC(order_lines.revenue, offset => '12 months')
```

### Balance on the last day of the month

```sql theme={null}
TIME_METRIC(MAX_BY(orders.balance, orders.date), grain => month)
```

<Tip>
  Uses the MAX\_BY aggregation to take the value of the last date in the group
</Tip>

### Revenue of the 2nd week of the month

```sql theme={null}
TIME_METRIC(order_lines.revenue, grain => month) FILTER (WHERE WEEK(date.month) = 2)
```

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

### MoM growth

```sql theme={null}
TIME_METRIC(order_lines.revenue, grain => month) /
TIME_METRIC(order_lines.revenue, grain => month, offset = '1 month')
```

### Daily contribution

```sql theme={null}
TIME_METRIC(order_lines.revenue, grain => day) /
TIME_METRIC(order_lines.revenue, grain => month)
```

### Monthly contribution to monthly *total*

```sql theme={null}
TIME_METRIC(order_lines.revenue, grain => month) /
TIME_METRIC(order_lines.revenue, grain => month) GROUP BY (date.month)
```

### Monthly contribution to city

```sql theme={null}
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

```sql theme={null}
TIME_METRIC(order_lines.revenue, date_range_start = orders.close_date - interval '30 days', date_range_end => orders.close_date)
```

<Tip>
  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
</Tip>

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

### Last 30 days rolling revenue, yesterday

```sql theme={null}
TIME_METRIC(order_lines.revenue, date_range_start = orders.close_date - interval '30 days', date_range_end => orders.close_date, offset = '1 day')
```

<Tip>
  The 1 day `offset` is applied to both `date_range_start` and `date_range_end`
</Tip>

### Month-to-date (MTD) revenue

```sql theme={null}
TIME_METRIC(order_lines.revenue, date_range_start => DATE_TRUNC('month', orders.close_date), date_range_end => orders.close_date)
```

<Tip>
  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
</Tip>

### Year-to-date (YTD) revenue

```sql theme={null}
TIME_METRIC(order_lines.revenue, date_range_start => DATE_TRUNC('year', orders.close_date), date_range_end => orders.close_date)
```

<Tip>
  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
</Tip>

### Monthly open deals

```sql theme={null}
TIME_METRIC(deals.count, grain => month, date_range_start => deals.create_date, date_range_end => deals.close_date)
```

<Tip>
  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.
</Tip>