Daily, Weekly, Monthly Active Users (DAU, WAU, MAU)

We want to know the customer engagement of our store. To do this, we need to use an Active Users metric.

Use case

We want to know the customer engagement of our store. To do this, we need to use an Active Users metric.

Data modeling

Daily, weekly, and monthly active users are commonly referred to as DAU, WAU, MAU. To get these metrics, we need to use a rolling time frame to calculate a daily count of how many users interacted with the product or website in the prior day, 7 days, or 30 days. Also, we can build other metrics on top of these basic metrics. For example, the WAU to MAU ratio, which we can add by using already defined weekly_active_users and monthly_active_users.

To calculate daily, weekly, or monthly active users we’re going to use the rolling_window measure parameter.

YAML

cubes:
  - name: active_users
    sql: |
      SELECT user_id, created_at FROM public.orders

    measures:
      - name: monthly_active_users
        type: count_distinct
        sql: user_id
        rolling_window:
          trailing: 30 day
          offset: start

      - name: weekly_active_users
        type: count_distinct
        sql: user_id
        rolling_window:
          trailing: 7 day
          offset: start

      - name: daily_active_users
        type: count_distinct
        sql: user_id
        rolling_window:
          trailing: 1 day
          offset: start

      - name: wau_to_mau
        title: WAU to MAU
        type: number
        sql:
          "1.0 * {weekly_active_users} / NULLIF({monthly_active_users}, 0)"
        format: percent

    dimensions:
      - name: created_at
        type: time
        sql: created_at

Result

Query all four measures with a timeDimensions date range to get the active user counts for any given day. For example, for a single day in 2020:

MAU	WAU	DAU	WAU/MAU
22	4	0	18.18%