【BigQuery】Firebase AnalyticsのDAU, WAU, MAUを取得するクエリ(新スキーマ)

概要

内容は上記リンクを参考にしている。

こちらのリンクがBigQueryの旧スキーマでのクエリだったため
新しいスキーマへ調整した版を作成した。

結論

これです。

SELECT
date,
SUM(CASE WHEN period = 1 THEN users END) as days_01,
SUM(CASE WHEN period = 7 THEN users END) as days_07,
SUM(CASE WHEN period = 30 THEN users END) as days_30
FROM (
SELECT
dates.date as date,
periods.period as period,
COUNT(DISTINCT user_pseudo_id) as users
FROM `firebase_project_table.app_events_*`
CROSS JOIN (SELECT event_date as date FROM `firebase_project_table.app_events_*` WHERE event_name = 'user_engagement' GROUP BY date) as dates
CROSS JOIN (SELECT period FROM (SELECT 1 as period) UNION ALL
(SELECT 7 as period) UNION ALL (SELECT 30 as period)) as periods
WHERE dates.date >= event_date
AND SAFE_CAST(FLOOR(DATE_DIFF(PARSE_DATE("%Y%m%d",dates.date), PARSE_DATE("%Y%m%d",event_date), DAY)/periods.period) AS INT64) = 0
GROUP BY 1,2
)

GROUP BY date
ORDER By date

変更点

① ユニークユーザーの取得

COUNT(DISTINCT user_dim.app_info.app_instance_id) as users 
↓
COUNT(DISTINCT user_pseudo_id) as users 

② エンゲージメントイベントの指定方法

CROSS JOIN (SELECT e.date FROM `firebase_project_table.app_events_*`, UNNEST(event_dim) as e WHERE e.name = 'user_engagement' GROUP BY date) as dates
↓
CROSS JOIN (SELECT event_date as date FROM `firebase_project_table.app_events_*` WHERE event_name = 'user_engagement' GROUP BY date) as dates

③ 細かい所
日付の指定

○○.date 
↓
event_date

おわりに

間違いがあれば、コメントいただけますと幸いです。