8. BigQueryで過去n日のユーザー数を日毎に集計するGA4のクエリ

GA4のユーザー数の集計期間をISOWEEKやMONTHではなく、過去7日間、過去28日間、過去30日間毎に設定し、集計するためのクエリをご紹介します。

このクエリを実行することで、日毎に過去7日間のユーザー数の推移を表示できます。
つまり、以下のようなGA4のレポートのスナップショットページに表示されるユーザー数とレンディングと同等のレポートを作成するクエリです。

過去7日間のユーザー数を日毎に集計するクエリ

今回はFirebaseデモプロジェクト（firebase-public-project、analytics_153293282.events_*）のデータを利用しています。

「FROM `analytics_XXXXXXXX.events_*`」のXXXXXXXの部分はご自身のGoogle AnalyticsのプロパティIDに置き換えてください。

-- データを取得する対象期間を設定
CREATE TEMPORARY FUNCTION fromDate() AS (DATE('2018-09-21'));
CREATE TEMPORARY FUNCTION toDate()  AS (DATE('2018-10-03'));
CREATE TEMPORARY FUNCTION intervalDays()  AS (6);

WITH
-- 縦持ちのデータを扱いやいように横持ちに変換
`convert_event_params` AS(
SELECT
PARSE_DATE("%Y%m%d", event_date) AS date
, user_pseudo_id
FROM `firebase-public-project.analytics_153293282.events_*`
WHERE
_TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', fromDate()) AND FORMAT_DATE('%Y%m%d', toDate())
AND (
       (event_name = 'user_engagement' AND platform IN ('IOS', 'ANDROID'))
       OR (event_name = 'session_start' AND platform IN ('WEB'))
   )
GROUP BY date, user_pseudo_id
)
,
-- 過去n日間のユーザー数を集計するために行を複製
`add_target_date` AS (
 SELECT
   date
   -- intervalDays()にあわせて対象日の行を複製
   , target_date
   , user_pseudo_id
 FROM `convert_event_params`
 , UNNEST(GENERATE_DATE_ARRAY(date, DATE_ADD(date, INTERVAL intervalDays() day))) AS target_date 
)
,
-- ある日から過去n日間のユーザー数を集計
`calc_user_count` AS (
 SELECT
   target_date
   , COUNT(DISTINCT user_pseudo_id) AS user_count
   , COUNT(DISTINCT date) day_count
 FROM `add_target_date`
 WHERE
   -- 過去n日間のデータのみ抽出
   date BETWEEN DATE_SUB(date, interval intervalDays() day) and date
 GROUP BY target_date
)


SELECT
 *
FROM `calc_user_count`
WHERE
 -- 7日分のユーザー数をカウントしたデータのみを抽出
 day_count = (intervalDays() + 1)

実行結果は以下のようになります。

クエリのカスタマイズについて

3行目のintervalDays()の数値を調整するだけで、過去n日ごとのユーザー数を集計できます。
例えば、過去7日毎のアクティブユーザー数を集計したい場合は「6」にします。
過去28日毎のアクティブユーザー数を集計したい場合は「27」にします。
過去30日毎のアクティブユーザー数を集計したい場合は「29」にします。

CREATE TEMPORARY FUNCTION intervalDays()  AS (6);