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`
    }
  }
});

Where and how did you get the “events” table? Was it manually created and what is this schema? Is this unrelated to the Events cube that follows in the Event Analytics Example?(https://cube.dev/docs/recipes/event-analytics)

Hey @Scott_Francis :wave:

The events table used in the example above is just a dummy table that has the following columns with data related to some user activity: id, user_id, timestamp. If you have a table that follows this structure, you can use the approach described above with minimal adjustments.

The table mentioned in the Event Analytics example is similar, indeed.