GA4エンゲージメント時間抽出パイプラインの構造解剖

GA4 エンゲージメント時間抽出パイプラインの構造解剖 BigQuery SQLの内部実行プロセスとデータ変換のステップ・バイ・ステップ解説 For Data Analysts & Analytics Engineers

クエリの全体構造と「インサイド・アウト」実行モデル
SQLは外側から記述されますが、論理的な実行は最も内側のFROM句から開始されます。本資料では、データが辿る実際のプロセスに合わせて、最深部から外側へとクエリを解読します。
ミニマップ: 全スライドで現在地をハイライトします
CREATE TEMP FUNCTION date_from() RETURNS STRING AS ('20260301');
CREATE TEMP FUNCTION date_to() RETURNS STRING AS ('20260331');
WITH Engagement_Time AS (
SELECT ymd, _engagement_time_seconds, FORMAT_TIMESTAMP(
'%H時間 %M分 %S秒', TIMESTAMP_SECONDS(CAST(_engagement_time_seconds AS INT64)
) AS format_second
FROM(
SELECT ymd, SUM(FLOOR(SAFE_DIVIDE(COALESCE(_engagement_time_msec, 0), 1000))) AS _engagement_time_seconds
FROM(
SELECT PARSE_DATE('%Y%m%d', event_date) AS ymd,
(SELECT value.int_value FROM UNNEST(event_params)
WHERE key = 'engagement_time_msec') AS _engagement_time_msec
FROM <project>.<dataset>.events_*
WHERE _TABLE_SUFFIX BETWEEN date_from() AND date_to()
)
GROUP BY ymd
)
WHERE _engagement_time_seconds <> 0
ORDER BY ymd ASC
)
SELECT * FROM Engagement_Time
Layer 0: 期間指定
Layer 3: 表示形式の成形
Layer 2: 集計と単位変換
Layer 1: 生データの抽出と展開
Layer 2: 集計と単位変換
Layer 3: 表示形式の成形
実行の方向

Phase 0: 抽出対象期間の定義 (Global Variables)
CREATE TEMP FUNCTION date_from() RETURNS STRING AS ('20260301');
CREATE TEMP FUNCTION date_to() RETURNS STRING AS ('20260331');
Variable Scope
date_from() -> '20260301'
・BigQueryにおける一時関数(TEMP FUNCTION)を変数として代用するテクニック。
・クエリ内で日付をハードコードせず、先頭で一元管理することで、再利用性と保守性(DRY原則)を担保。
ミニマップ: 全スライドで現在地をハイライトします
CREATE TEMP FUNCTION date_from() RETURNS STRING AS ('20260301');
CREATE TEMP FUNCTION date_to() RETURNS STRING AS ('20260331');
WITH Engagement_Time AS (
SELECT ymd, _engagement_time_seconds, FORMAT_TIMESTAMP(
'%H時間 %M分 %S秒', TIMESTAMP_SECONDS(CAST(_engagement_time_seconds AS INT64)
) AS format_second
FROM(
SELECT ymd, SUM(FLOOR(SAFE_DIVIDE(COALESCE(_engagement_time_msec, 0), 1000))) AS _engagement_time_seconds
FROM(
SELECT PARSE_DATE('%Y%m%d', event_date) AS ymd,
(SELECT value.int_value FROM UNNEST(event_params)
WHERE key = 'engagement_time_msec') AS _engagement_time_msec
FROM <project>.<dataset>.events_*
WHERE _TABLE_SUFFIX BETWEEN date_from() AND date_to()
)
GROUP BY ymd
)
WHERE _engagement_time_seconds <> 0
ORDER BY ymd ASC
)
SELECT * FROM Engagement_Time
Data State Matrix
State: Global Memory -> date_from: '20260301', date_to: '20260331'

Phase 1-A: 最深部のデータ抽出 (Partition Filtering & Date Parsing)
SELECT PARSE_DATE('%Y%m%d', event_date) AS ymd,
FROM <project>.<dataset>.events_*
WHERE _TABLE_SUFFIX BETWEEN date_from() AND date_to()
Partition Filter
events_20260228
events_20260301
events_20260331
_TABLE_SUFFIX: ワイルドカードテーブルに対するパーティションプルーニング。スキャン量を最小化しコストを削減。
PARSE_DATE: 文字列 (String) のevent_dateを、後続の処理で扱いやすい日付 (Date) 型へ変換。
March 2026 Tables Only
Data State Matrix
Before: event_date (String: '20260301')
After: ymd (Date: 2026-03-01)
ミニマップ: 全スライドで現在地をハイライトします
SELECT PARSE_DATE('%Y%m%d', event_date) AS ymd,
FROM <project>.<dataset>.events_*
WHERE _TABLE_SUFFIX BETWEEN date_from() AND date_to()

Phase 1-B: 配列の展開と特定キーの抽出 (UNNEST)
(SELECT value.int_value FROM UNNEST(event_params)
WHERE key = 'engagement_time_msec') AS _engagement_time_msec
Array Flattening Diagram
event_params Array
[{key: 'page_location', ...}, {key: 'engagement_time_msec', value.int_value: 1500}, {key: 'session_id', ...}]
UNNEST
{key: 'page_location', ...}
{key: 'engagement_time_msec', value.int_value: 1500} -> _engagement_time_msec = 1500
{key: 'session_id', ...}
・GA4特有のネストされたevent_paramsレコードをUNNESTでフラット化。
・スカラサブクエリを使用し、各イベント行に対して特定のキーの値をピンポイントで抽出する処理。
Code Minimap
(SELECT value.int_value
FROM UNNEST(event_params)
WHERE key = 'engagement_time_msec') AS _engagement_time_msec

Phase 2-A: データクレンジングと単位変換 (Null Handling & Math) FLOOR(SAFE_DIVIDE(COALESCE(_engagement_time_msec, 0), 1000)) Nested Box Model COALESCE(..., 0) -> SAFE_DIVIDE(..., 1000) -> FLOOR(...) 欠損値(Null)を0に置換(安全な計算の担保) ミリ秒(msec)を秒(sec)に変換。ゼロ除算エラーも防止 小数点以下を切り捨て、整数秒として丸め処理 Data State Matrix Raw Input: Null, 1500 COALESCE: 0, 1500 SAFE_DIVIDE: 0.0, 1.5 FLOOR: 0, 1 Final Output: 0, 1 Code Minimap SELECT ymd, SUM(FLOOR(SAFE_DIVIDE(COALESCE(_engagement_time_msec, 0), 1000))) AS _engagement_time_seconds FROM Unnested_Events GROUP BY 1;

Phase 2-B: 日次集計 (Aggregation) SELECT ymd, SUM(FLOOR(SAFE_DIVIDE(COALESCE(_engagement_time_msec, 0), 1000))) AS _engagement_time_seconds GROUP BY ymd 2026-03-01 (Event level) 5s, 12s, 0s, 0s Aggregation Funnel GROUP BY ymd & SUM 2026-03-01 (Daily Summary) 17s ・変換・丸め処理が完了した秒数をベースに、ymd (日) 単位で総和 (SUM) を算出。 ・この段階で、データはイベント単位から日次サマリー単位へと粒度 (Granularity) が変化する。 Code Minimap SELECT ymd, SUM(FLOOR(SAFE_DIVIDE(COALESCE(_engagement_time_msec, 0), 1000))) AS _engagement_time_seconds GROUP BY ymd

Phase 3-A: ヒューマンリーダブルな形式への成形 (Timestamp Formatting) Code FORMAT_TIMESTAMP('%H時間 %M分 %S秒', TIMESTAMP_SECONDS(CAST(_engagement_time_seconds AS INT64))) AS format_second Type Conversion Pipeline INT64 -> Timestamp -> String CAST(.. AS INT64) (厳密な型定義) -> TIMESTAMP_SECONDS(..) (エポック秒からのタイムスタンプ化) -> FORMAT_TIMESTAMP(..) (指定フォーマット出力) タイムスタンプ関数をハックして時間をフォーマットする、BigQueryにおける洗練されたテクニック。 Data State Matrix Input: 3665 (seconds) Output: '01時間 01分 05秒' (string) Code Minimap SELECT ymd, _engagement_time_seconds, FORMAT_TIMESTAMP( '%H時間 %M分 %S秒', TIMESTAMP_SECONDS(CAST(_engagement_time_seconds AS INT64)) ) AS format_second

Phase 3-B: ノイズの除去と整列 (Filtering & Sorting) WHERE _engagement_time_seconds <> 0 ORDER BY ymd ASC Pruning & Ordering Timeline 03-01, 03-02, 03-03, 03-04 ・<> 0: エンゲージメントが全く発生しなかった日 (休業日やトラフィックゼロの日) を最終結果から除外。 ・ASC: 時系列順に並び替え、BIツールやレポートでの即時利用を可能に。 ORDER BY ymd ASC Code Minimap WHERE _engagement_time_seconds <> 0 ORDER BY ymd ASC;

Phase 4: CTEの呼び出しと最終出力 (Final Execution) SELECT * FROM Engagement_Time Output Data Matrix ymd (DATE): 2026-03-01, 2026-03-03, 2026-03-04 _engagement_time_seconds (INT64): 3665, 1820, 45 format_second (STRING): '01時間 01分 05秒', '00時間 30分 20秒', '00時間 00分 45秒' これまでに構築した Engagement_Time CTE (一時テーブル) を展開。 分析者にとって直感的に理解可能な、クリーンで構造化されたデータセットが完成。 Code Minimap SELECT * FROM Engagement_Time

Synthesis: エンゲージメント時間の変換ライフサイクル Stage 1: Raw Partition (events_20260301) Stage 2: Nested Array ([{key: 'engagement_time_msec', ...}]) Stage 3: Extracted Scalar (msec) (1500, null, 2100) Stage 4: Cleansed & Converted (sec) (1, 0, 2) Stage 5: Aggregated Daily Total (3665) Stage 6: Formatted Final Output ('01時間 01分 05秒') 複雑にネストされたクエリは、単なるコードの羅列ではなく、「抽出・クレンジング・集計・成形」というデータエンジニアリングの基本パイプラインを1つのSQLステートメント内で美しくカプセル化したものです。