About This Block

Active Users is a common metric you can use to measure how customers are engaging with your product over different timeframes. In the Daily, Weekly, Monthly Active Users Analytical Block pattern, we use a rolling timeframe to calculate a daily count of how many users interacted with the product today, in the past seven days, and in the past 30 days. This block will allow you to answer questions like:

• How is my active customer base changing over time?
• What are the characteristics of my active users?
• How do changes to my product affect my active customer base?

Ideal Data Types

This block is a great way to analyze event data, which can come from Google Analytics, Segment, Snowplow, or a custom event-tracking system (like a table that has a row for every user event).

Expected Output

This block helps us segment customers into monthly, weekly, and daily active users — in other words, for each date, the users who have made a purchase in the last 30 days, in the last 7 days, and on that same day.

Note: This is a static data set, so we set the filters for a specific timeframe.

By joining a daily_use derived table (discussed in greater detail below) with our user data table, we can explore active users by any category for which we have data. For example, here are the weekly active users over the past 90 days, cohorted by their signup month:

Try It Yourself

How It's Done

The first step is to build a date table. The construction of this table can vary based on the SQL dialect being used. In the examples below, we use Redshift, BigQuery, and Snowflake. We have Community posts for how to create this type of table in MySQL and PostgreSQL as well.

We can leverage a large table (here it's order_items) that has more rows than we need for dates. We will use the window function row_number to generate a series of integers to build our date table.

Next, we will construct a daily_use SQL-based derived table that has the user_id and date for every day a user made a purchase. In this derived table we will cross join the date table with the daily_use table, where the calendar date falls between the usage date and the usage date plus 30 days. This allows us to determine, for each calendar date, whether a user has been active up to 30 days prior. To make this table, we use the user_id, date, and how many days it's been since the user's last activity, which will look like this in an Explore:

Each new calendar date increments the days since last action. In the example above, we see that user 1 was active on January 7, but was not active any day after. This daily_use table is persisted in the code below to increase query performance.

Finally, we add dimensions and measures to the daily_use derived table view (called active_users) that will allow us to count and drill in on monthly, weekly, and daily active users.

Example Code

Starting in Looker 7.4, the filters parameter syntax has changed. See the filters parameter documentation page to view the new syntax.

Redshift:

explore: active_users {
 join: users {
   type: left_outer
   sql_on: ${active_users.user_id} = ${users.id} ;;
   relationship: many_to_one
 }
}

view: dates {
 derived_table: {
   distribution_style: all
   sortkeys: ["date"]
   sql_trigger_value: GETDATE() ;;
   sql: -- ## 1) Create a Date table with a row for each date.
     SELECT '2001-01-01'::DATE + d AS date
     FROM
      (SELECT ROW_NUMBER() OVER(ORDER BY id) -1 AS d FROM orders ORDER BY id LIMIT 20000) AS d
     ;;
 }
}

view: active_users {
 derived_table: {
   sql_trigger_value: GETDATE();;
   distribution: "user_id"
  sortkeys: ["date"]
  sql: WITH daily_use AS (
      -- ## 2 ) Create a table of days and activity by user_id
      SELECT
        user_id
        , DATE_TRUNC('day', created_at) as activity_date
      FROM orders
      GROUP BY 1, 2
    )
    --  ## 3) Cross join activity and dates to build a row for each user/date combo with
    -- days since last activity
    SELECT
          daily_use.user_id
        , wd.date as date
        , MIN(wd.date::date - daily_use.activity_date::date) as days_since_last_action
    FROM ${dates.SQL_TABLE_NAME} AS wd
    LEFT JOIN daily_use
        ON wd.date >= daily_use.activity_date
        AND wd.date < daily_use.activity_date + interval '30 day'
    GROUP BY 1,2
     ;;
}

dimension: date {
  type: date
  sql: ${TABLE}.date ;;
}

dimension: user_id {
  type: number
  sql: ${TABLE}.user_id ;;
}

dimension: days_since_last_action {
  type: number
  sql: ${TABLE}.days_since_last_action ;;
  value_format_name: decimal_0
}

dimension: active_this_day {
  type: yesno
  sql: ${days_since_last_action} <  1 ;;
}

dimension: active_last_7_days {
  type: yesno
  sql: ${days_since_last_action} < 7 ;;
}

measure: user_count_active_30_days {
  label: "Monthly Active Users"
  type: count_distinct
  sql: ${user_id} ;;
  drill_fields: [users.id, users.name]
}

measure: user_count_active_this_day {
  label: "Daily Active Users"
  type: count_distinct
  sql: ${user_id} ;;
  drill_fields: [users.id, users.name]

  filters: {
    field: active_this_day
    value: "yes"
  }
}

measure: user_count_active_7_days {
  label: "Weekly Active Users"
  type: count_distinct
  sql: ${user_id} ;;
  drill_fields: [users.id, users.name]

  filters: {
    field: active_last_7_days
    value: "yes"
  }
}
}

BigQuery:

view: dates {
 derived_table: {

   sql_trigger_value: SELECT CURRENT_DATE() ;;
   sql: 
     SELECT cast(date as date) as date
FROM UNNEST(GENERATE_DATE_ARRAY(DATE_SUB(CURRENT_DATE, INTERVAL 5 YEAR), CURRENT_DATE)) date
      ;;
 }
}

view: active_users {
  derived_table: {
  sql_trigger_value: SELECT CURRENT_DATE();;

    sql: WITH daily_use AS (
      SELECT
        user_id as user_id
        , cast(TIMESTAMP_TRUNC(transactiondate,day) as date) as activity_date
      FROM users
      GROUP BY 1, 2
      )
 
      SELECT
        daily_use.user_id
       , wd.date as date
       , MIN( DATE_DIFF(wd.date, daily_use.activity_date, day) ) as days_since_last_action
      FROM ${dates.SQL_TABLE_NAME} AS wd
      CROSS JOIN daily_use
        WHERE wd.date BETWEEN daily_use.activity_date AND DATE_ADD(daily_use.activity_date, INTERVAL 30 DAY)
      GROUP BY 1,2
        ;;
   }

dimension_group: date {
   type: time
   timeframes: [date,month,quarter,quarter_of_year,year,raw]
   sql: CAST( ${TABLE}.date AS TIMESTAMP);;
 }

 dimension: user_id {
   type: number
   sql: ${TABLE}.user_id ;;
 }

 dimension: days_since_last_action {
   type: number
   sql: ${TABLE}.days_since_last_action ;;
   value_format_name: decimal_0
 }

 dimension: active_this_day {
   type: yesno
   sql: ${days_since_last_action} < 1 ;;
 }

 dimension: active_last_7_days {
   type: yesno
   sql: ${days_since_last_action} < 7 ;;
 }

 measure: user_count_active_30_days {
   label: "Monthly Active Users"
   type: count_distinct
   sql: ${user_id} ;;
   drill_fields: [users.id, users.name]
 }

 measure: user_count_active_this_day {
   label: "Daily Active Users"
   type: count_distinct
   sql: ${user_id} ;;
   drill_fields: [users.id, users.name]

   filters: {
     field: active_this_day
     value: "yes"
   }
 }

 measure: user_count_active_7_days {
   label: "Weekly Active Users"
   type: count_distinct
   sql: ${user_id} ;;
   drill_fields: [users.id, users.name]

   filters: {
     field: active_last_7_days
     value: "yes"
   }
 }
}

Snowflake:

view: event_active_users_pdt {
  derived_table: {
    sql:
      WITH dau AS (
      SELECT TO_DATE(CREATED_AT) as event_date, COUNT(DISTINCT USER_ID) AS dau
      FROM SCHEMA.EVENTS
      WHERE -- <.. filter criteria...>
      GROUP BY event_date
    )
   SELECT event_date, dau,
            (SELECT COUNT(DISTINCT USER_ID)
            FROM SCHEMA.EVENTS
            WHERE -- <... filter criteria...>
            AND TO_DATE(CREATED_AT) BETWEEN DATEADD(DAY, -29, dau.event_date) AND dau.event_date
            ) AS mau,
           (SELECT COUNT(DISTINCT USER_ID)
            FROM SCHEMA.EVENTS
            WHERE -- <... filter criteria...>
            AND TO_DATE(CREATED_AT) BETWEEN DATEADD(DAY, -7, dau.event_date) AND dau.event_date
            ) AS wau
   FROM dau
   ;;
   datagroup_trigger: events_datagroup
 }

 dimension: event_active_users_pk {
   type: string
   primary_key: yes
   hidden: yes
   sql: TO_VARCHAR(${event_date}, 'YYYYMMDD');;
 }

 dimension_group: event {
   type: time
   timeframes: [
     raw,
     date,
     day_of_week,
     day_of_month,
     day_of_year,
     week,
     week_of_year,
     month,
     quarter,
     year
   ]
   sql: ${TABLE}."EVENT_DATE" ;;
 }

 dimension: dau {
   label: "DAU"
   type: number
   sql: ${TABLE}."DAU" ;;
 }

 dimension: mau {
   label: "MAU"
   type: number
   sql: ${TABLE}."MAU" ;;
 }

 dimension: wau {
   label: "WAU"
   type: number
   sql: ${TABLE}."WAU" ;;
 }

 dimension: dau_mau {
   label: "DAU/MAU"
   type: number
   value_format_name: decimal_2
   sql: 1.0 * (${dau} / nullif(${mau}, 0)) ;;
 }

 dimension: wau_mau {
   label: "WAU/MAU"
   type: number
   value_format_name: decimal_2
   sql: 1.0 * (${wau} / nullif(${mau}, 0)) ;;
 }

 dimension: dau_wau {
   label: "DAU/MAU"
   type: number
   value_format_name: decimal_2
   sql: 1.0 * (${dau} / nullif(${wau}, 0)) ;;
 }

 measure: count {
   label: "Number of Records"
   type: count
   drill_fields: [detail*]
 }

 measure: average_dau_mau {
   label: "Average DAU/MAU"
   type: average
   sql: ${dau_mau} ;;
   value_format_name: decimal_2
   drill_fields: [detail*]
 }

 measure: average_wau_mau {
   label: "Average WAU/MAU"
   type: average
   sql: ${wau_mau} ;;
   value_format_name: decimal_2
   drill_fields: [detail*]
 }

 measure: average_dau_wau {
   label: "Average DAU/WAU"
   type: average
   sql: ${dau_wau} ;;
   value_format_name: decimal_2
   drill_fields: [detail*]
 }

 measure: min_dau_mau {
   label: "Min DAU/MAU"
   type: min
   sql: ${dau_mau} ;;
   value_format_name: decimal_2
   drill_fields: [detail*]
 }

 measure: min_wau_mau {
   label: "Min WAU/MAU"
   type: min
   sql: ${wau_mau} ;;
   value_format_name: decimal_2
   drill_fields: [detail*]
 }

 measure: min_dau_wau {
   label: "Min DAU/WAU"
   type: min
   sql: ${dau_wau} ;;
   value_format_name: decimal_2
   drill_fields: [detail*]
 }

 measure: max_dau_mau {
   label: "Max DAU/MAU"
   type: max
   sql: ${dau_mau} ;;
   value_format_name: decimal_2
   drill_fields: [detail*]
 }

 measure: max_wau_mau {
   label: "Max WAU/MAU"
   type: max
   sql: ${wau_mau} ;;
   value_format_name: decimal_2
   drill_fields: [detail*]
 }

 measure: max_dau_wau {
   label: "Max DAU/WAU"
   type: max
   sql: ${dau_wau} ;;
   value_format_name: decimal_2
   drill_fields: [detail*]
 }

 set: detail {
   fields: [event_date, dau, mau, wau]
 }