Небольшая инструкция о том, как создать таблицу в Google BigQuery с выводом за каждый день MAU (monthly active users), а также WAU и любой другой вариант *AU.
Т. е. в итоге мы хотим получить таблицу вида:

Чтобы затем построить в Google Data Studio такой же красивый график, как в Firebase или Google Analytics 4:

Пишем запрос

Для этого достаточно создать сначала общее табличное выражение (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
)

где user_pseudo_id — это уникальный id пользователя, event_date — это дата в виде строки.

В моем случае, поскольку я работаю с таблицами Firebase, то чтобы получить уникальный список id пользователей, я добавил дополнительный фильтр по полю event_name.

И добавляем к этому вторую часть запроса:

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

В этой части идет основной подсчет метрик: MAU, WAU и DAU.

Если вам нужен какой-либо другой период, то нужно изменить число 30 на нужное вам в строке генерации массива чисел UNNEST(GENERATE_ARRAY(1, 30)). А затем добавить или поменять строки подсчета количества уникальных пользователей, т. е. вот эту часть:

  , 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

Итоговый запрос

Соединим вместе две части и получим итоговый запрос:

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