How to calculate MAU for each day in Google BigQuery?

A short guide on how to create a table in Google BigQuery that outputs, for each day, MAU (monthly active users), as well as WAU and any other *AU variant.
In other words, in the end we want to get a table like this:

So that we can then build, in Google Data Studio, the same nice chart as in Firebase or Google Analytics 4:

Example chart from Firebase

Writing the query

For this it is enough to first create a common table expression (CTE):

WITH users AS (
  SELECT PARSE_DATE('%Y%m%d', event_date) as event_date
  , user_pseudo_id
  FROM `your_table`
  WHERE event_name = 'screen_view'
  GROUP BY 1, 2
)

where user_pseudo_id is the user’s unique id, and event_date is the date as a string.

In my case, since I work with Firebase tables, in order to get a unique list of user ids I added an extra filter on the event_name field.

And we add to this the second part of the query:

SELECT 
  DATE_ADD(event_date, INTERVAL i-1 DAY) AS period
  , COUNT(DISTINCT user_pseudo_id) AS mau
  , COUNT(DISTINCT IF(i<8,user_pseudo_id,null)) AS wau
  , COUNT(DISTINCT IF(i<2,user_pseudo_id,null)) AS dau
FROM users, UNNEST(GENERATE_ARRAY(1, 30)) i
WHERE DATE_ADD(event_date, INTERVAL i-1 DAY) <= (SELECT MAX(event_date) from users)
GROUP BY 1

This part performs the main calculation of the metrics: MAU, WAU, and DAU.

If you need some other period, you need to change the number 30 to the one you need in the array-generation line UNNEST(GENERATE_ARRAY(1, 30)). And then add or change the lines that count the number of unique users, that is, this part:

  , COUNT(DISTINCT user_pseudo_id) AS mau
  , COUNT(DISTINCT IF(i<8,user_pseudo_id,null)) AS wau
  , COUNT(DISTINCT IF(i<2,user_pseudo_id,null)) AS dau

The final query

Let’s join the two parts together and get the final query:

WITH users AS (
  SELECT PARSE_DATE('%Y%m%d', event_date) as event_date
  , user_pseudo_id
  FROM `your_table`
  WHERE event_name = 'screen_view'
  GROUP BY 1, 2
)

SELECT 
  DATE_ADD(event_date, INTERVAL i-1 DAY) AS period
  , COUNT(DISTINCT user_pseudo_id) AS mau
  , COUNT(DISTINCT IF(i<8,user_pseudo_id,null)) AS wau
  , COUNT(DISTINCT IF(i<2,user_pseudo_id,null)) AS dau
FROM users, UNNEST(GENERATE_ARRAY(1, 30)) i
WHERE DATE_ADD(event_date, INTERVAL i-1 DAY) <= (SELECT MAX(event_date) from users)
GROUP BY 1