Are our customers coming back?

When would you use this?

You want to know how many customers bought today, this week, and this month - and whether that number is growing.
Your marketing team runs a loyalty campaign and wants to track weekly engagement lift over the following month.
Leadership asks “Are our regulars becoming more or less frequent?” - you need a stickiness ratio to answer that.
You are building a retention dashboard and need each data point to show the active user count for its own period, not a snapshot of today.

What this recipe builds

Four metrics - DAU, WAU, MAU, and a WAU/MAU stickiness ratio. Each metric uses TIME_METRIC with a different grain, so each date on the time spine shows the active customer count for its own window: a daily count for DAU, a rolling week for WAU, a rolling month for MAU. The stickiness ratio divides WAU by MAU - a high value means customers are ordering frequently, not just once a month. All four metrics are sliceable by city, truck, menu type, or any other dimension.

This recipe uses the time metrics concept. See the documentation for the full technical reference.

Prerequisites

A date entity must be marked as a time spine.

TIME_METRIC handles the join to the time spine automatically.

# Mark the date entity as a time spine
type: entity
name: date
is_time_spine: yes

Steps

Daily Active Users

Distinct customers per day. Each date point on the time spine shows the count for that specific day only.

SQL
YAML

TIME_METRIC(
  COUNT(DISTINCT order_header.customer_id),
  date_field => order_header.order_date,
  grain => day
)

type: metric
entity: order_header
name: dau
display_name: Daily Active Users
sql: |-
  TIME_METRIC(
    COUNT(DISTINCT order_header.customer_id),
    date_field => order_header.order_date,
    grain => day
  )

Weekly Active Users

Distinct customers per calendar week. Constant within Mon–Sun, then updates at the week boundary.

SQL
YAML

TIME_METRIC(
  COUNT(DISTINCT order_header.customer_id),
  date_field => order_header.order_date,
  grain => week
)

type: metric
entity: order_header
name: wau
display_name: Weekly Active Users
sql: |-
  TIME_METRIC(
    COUNT(DISTINCT order_header.customer_id),
    date_field => order_header.order_date,
    grain => week
  )

Monthly Active Users

Distinct customers per calendar month. Stays constant for the whole month - the denominator for the stickiness ratio.

SQL
YAML

TIME_METRIC(
  COUNT(DISTINCT order_header.customer_id),
  date_field => order_header.order_date,
  grain => month
)

type: metric
entity: order_header
name: mau
display_name: Monthly Active Users
sql: |-
  TIME_METRIC(
    COUNT(DISTINCT order_header.customer_id),
    date_field => order_header.order_date,
    grain => month
  )

WAU to MAU ratio

What fraction of monthly customers also ordered this week? High values indicate frequent visitors; low values indicate mostly one-time monthly buyers.

SQL
YAML

DIV0(order_header.wau, order_header.mau)

type: metric
entity: order_header
name: wau_to_mau_ratio
display_name: WAU to MAU Ratio
sql: |-
  DIV0(order_header.wau, order_header.mau)

DIV0 is Snowflake-native syntax. For other warehouses, replace with NULLIF - for example, order_header.wau / NULLIF(order_header.mau, 0).

Sample output

First week of June 2022:

Date	DAU	WAU	MAU	WAU/MAU
2022-06-01	18,469	97,559	203,391	47.97%
2022-06-02	16,720	97,559	203,391	47.97%
2022-06-03	17,806	97,559	203,391	47.97%
2022-06-04	18,187	97,559	203,391	47.97%
2022-06-05	18,872	97,559	203,391	47.97%
2022-06-06	18,020	100,468	203,391	49.40%
2022-06-07	18,121	100,468	203,391	49.40%

On June 6, a new calendar week starts - WAU jumps from 97,559 to 100,468 and the stickiness ratio updates accordingly.

Key design notes

Concern	Guidance
Time spine required	`TIME_METRIC` needs an entity marked `is_time_spine: yes`. Without it, you cannot produce a per-period time series.
Grain argument	`grain => day / week / month` controls the window each row reports on. Changing grain changes the semantics - they are not interchangeable.
Correct aggregation	Active customer metrics use `COUNT(DISTINCT customer_id)` - each customer counts once per period regardless of how many orders they placed.
Works on any grain	DAU, WAU, and MAU each reflect their own window, so the stickiness ratio remains meaningful whether you slice by day, city, or truck.

Time metrics - reference for TIME_METRIC
Time spines - how to mark an entity as a time spine
Year-over-year growth - another recipe using TIME_METRIC, with the offset argument

Quick Start

Semantic Ecosystem

Modeling Reference

Context Layer

Management

Integrations

Security

Release Notes

When would you use this?

What this recipe builds

Prerequisites

Steps

Sample output

Key design notes

​When would you use this?

​What this recipe builds

​Prerequisites

​Steps

​Sample output

​Key design notes

​Related reading

When would you use this?

What this recipe builds

Prerequisites

Steps

Sample output

Key design notes

Related reading