概要
以下2点を満たすためのSQLを検討します。
- GA4テーブルから日次でMAUを集計して、任意の宛先テーブルを更新する
- GA4テーブルは最大72時間更新されるため、 過去3日間（N-4〜N-1）のテーブルを取得して宛先テーブルを更新する
※特にアプリのGA4は1日前、2日前のテーブルの欠損が多いため、過去3日分の更新が必要となります。
テーブル
対象日とその日時点でのMAUを集計します。
例えば2025年4月4日にクエリを実行した場合の返却結果は以下のようになります。
| event_date | mau |
|---|---|
| 20250401 | 10000 |
| 20250402 | 10100 |
| 20250403 | 9000 |
SQL
最初にGoogleAnalyticsのテーブルからN-34〜N-1の範囲を取得し、一時テーブルに保存します。
そしてその後のループ処理で、期間を1日ずつずらしてN-1〜N-4の範囲のMAUを集計します。
クエリ結果の日付が宛先テーブルにすでにあればUPDATE、なければINSERTをしています。
DECLARE
target_date INT64;
DECLARE
start_date INT64;
DECLARE
counter INT64 DEFAULT 0;
DECLARE
LOOP_PERIOD INT64 DEFAULT 3; -- ループ回数（遡る日数）
DECLARE
MAU_PERIOD INT64 DEFAULT 30; -- MAUの日数定義
DECLARE
current_date DATE DEFAULT CURRENT_DATE();
CREATE TEMP TABLE tmp_table AS (
SELECT
event_date,
user_pseudo_id
FROM
`projectName.analytics_XXX.events_*`
WHERE
-- 取得対象期間
_table_suffix BETWEEN FORMAT_DATE("%Y%m%d", current_date - MAU_PERIOD - LOOP_PERIOD)
AND FORMAT_DATE("%Y%m%d", current_date - 1));
LOOP
SET
counter = counter + 1;
IF counter > LOOP_PERIOD
THEN LEAVE;
END IF;
-- MAU集計対象 終了日
SET
target_date = CAST(FORMAT_DATE("%Y%m%d", current_date - counter) AS INT64) ;
-- MAU集計対象 開始日
SET
start_date = CAST(FORMAT_DATE("%Y%m%d", current_date - counter - MAU_PERIOD) AS INT64) ;
MERGE
`projectName.datasetName.tableName` AS target
USING
(
SELECT
target_date AS date,
COUNT(DISTINCT user_pseudo_id) AS mau
FROM
tmp_table
WHERE
CAST(event_date AS INT64 ) >= start_date
AND CAST(event_date AS INT64 ) <= target_date ) AS SOURCE
ON
target.date = source.date
WHEN MATCHED
THEN UPDATE SET date = source.date, mau = source.mau
WHEN NOT MATCHED
THEN
INSERT
(date,
mau)
VALUES
(source.date, source.mau);
END LOOP;
※スケジュールクエリで実行する場合には、バックフィル実行も考慮して
current_date DATE DEFAULT CURRENT_DATE();の箇所を
current_date DATE DEFAULT @run_date;としておくと良いです。
※MAUを「30日間」と定義していますが、28日や29日とする場合は
MAU_PERIOD INT64 DEFAULT 30;の箇所を書き換えてください。
参考情報
GA4テーブルの更新
日次エクスポートのテーブル（events_YYYYMMDD）は、その日のイベントがすべて収集された後に作成されます。アナリティクスでは、テーブルの日付から最大72時間日次テーブルが更新されます。