Daily, Weekly, Monthly Active Users

You may be familiar with Active Users metric , which is commonly used to get a sense of your engagement. 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.

You need event data to build this analysis. You can use tools like Google Analytics, Segment, Snowplow, or your custom event tracking system.

To calculate daily, weekly, or monthly active users we’re going to use the rollingWindow measure parameter. rollingWindow accepts 3 parameters: trailing, leading, and offset. You can read about what each of them does here.

For our purpose, we need only offset and trailing. We will set offset to start and the trailing parameter to the number of days – 1, 7, or 30.

In the example below, we’ll create a cube called ActiveUsers with data from our events table.

Please note, we are using interval literal in the trailing parameter. The example below should work in Redshift and BigQuery. The exact interval literal could be different, depending on your database.

cube(`ActiveUsers`, {
  sql: `select id, user_id, timestamp from events`,

  measures: {
    monthlyActiveUsers: {
      sql: `user_id`,
      type: `countDistinct`,
      rollingWindow: {
        trailing: `30 day`,
        offset: `start`
      }
    },

    weeklyActiveUsers: {
      sql: `user_id`,
      type: `countDistinct`,
      rollingWindow: {
        trailing: `1 week`,
        offset: `start`
      }
    },

    dailyActiveUsers: {
      sql: `user_id`,
      type: `countDistinct`,
      rollingWindow: {
        trailing: `1 day`,
        offset: `start`
      }
    }
  },

  dimensions: {
    timestamp: {
      sql: `timestamp`,
      type: `time`
    }
  }
});

Going further, we can build other metrics on top of these basic metrics. For example, the DAU to MAU ratio is one of the most popular metrics used to measure the stickiness of the product . We can easily add it, using already defined dailyActiveUsers and monthlyActiveUsers .

cube(`ActiveUsers`, {
  measures: {
    dauToMau: {
      title: `DAU to MAU`,
      sql: `100.000 * ${dailyActiveUsers} / NULLIF(${monthlyActiveUsers}, 0)`,
      type: `number`,
      format: `percent`
    }
  }
});