Edited at

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

More than 1 year has passed since last update.


概要

https://www.en.advertisercommunity.com/t5/Data-Studio/How-is-Firebase-Analytics-Monthly-30-day-Active-User-count/td-p/1638935#

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

こちらのリンクが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


おわりに

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