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

# Are our customers coming back?

> Measure customer engagement with daily, weekly, and monthly active user counts

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

<Tip>
  This recipe uses the [time metrics](/docs/advanced-modeling/time-metrics) concept.
  See the documentation for the full technical reference.
</Tip>

## Prerequisites

A `date` entity must be marked as a [time spine](/docs/advanced-modeling/time-spines).

<Tip>
  `TIME_METRIC` handles the join to the time spine automatically.
</Tip>

```yaml theme={null}
# Mark the date entity as a time spine
type: entity
name: date
is_time_spine: yes
```

## Steps

<Steps>
  <Step title="Daily Active Users">
    Distinct customers per day. Each date point on the time spine shows the count for
    that specific day only.

    <Tabs>
      <Tab title="SQL">
        ```sql theme={null}
        TIME_METRIC(
          COUNT(DISTINCT order_header.customer_id),
          date_field => order_header.order_date,
          grain => day
        )
        ```
      </Tab>

      <Tab title="YAML">
        ```yaml theme={null}
        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
          )
        ```
      </Tab>
    </Tabs>
  </Step>

  <Step title="Weekly Active Users">
    Distinct customers per calendar week. Constant within Mon–Sun, then updates at the
    week boundary.

    <Tabs>
      <Tab title="SQL">
        ```sql theme={null}
        TIME_METRIC(
          COUNT(DISTINCT order_header.customer_id),
          date_field => order_header.order_date,
          grain => week
        )
        ```
      </Tab>

      <Tab title="YAML">
        ```yaml theme={null}
        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
          )
        ```
      </Tab>
    </Tabs>
  </Step>

  <Step title="Monthly Active Users">
    Distinct customers per calendar month. Stays constant for the whole month - the
    denominator for the stickiness ratio.

    <Tabs>
      <Tab title="SQL">
        ```sql theme={null}
        TIME_METRIC(
          COUNT(DISTINCT order_header.customer_id),
          date_field => order_header.order_date,
          grain => month
        )
        ```
      </Tab>

      <Tab title="YAML">
        ```yaml theme={null}
        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
          )
        ```
      </Tab>
    </Tabs>
  </Step>

  <Step title="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.

    <Tabs>
      <Tab title="SQL">
        ```sql theme={null}
        DIV0(order_header.wau, order_header.mau)
        ```
      </Tab>

      <Tab title="YAML">
        ```yaml theme={null}
        type: metric
        entity: order_header
        name: wau_to_mau_ratio
        display_name: WAU to MAU Ratio
        sql: |-
          DIV0(order_header.wau, order_header.mau)
        ```
      </Tab>
    </Tabs>

    <Tip>
      `DIV0` is Snowflake-native syntax. For other warehouses, replace with
      `NULLIF` - for example,
      `order_header.wau / NULLIF(order_header.mau, 0)`.
    </Tip>
  </Step>
</Steps>

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

## Related reading

* [Time metrics](/docs/advanced-modeling/time-metrics) - reference for `TIME_METRIC`
* [Time spines](/docs/advanced-modeling/time-spines) - how to mark an entity as a time spine
* [Year-over-year growth](/docs/recipes/yoy-revenue-growth) - another recipe using
  `TIME_METRIC`, with the `offset` argument