GA4ページ別エンゲージメント分析_SQLアーキテクチャ解剖

// TECHNICAL_CODE_WALKTHROUGH GA4 ページ別エンゲージメント分析: SQL アーキテクチャ解剖 段階的クエリ処理構造とデータ変換パイプラインの解説

Query Architecture / 全体処理パイプライン [Phase 0: UDF] | 対象期間の定義 (date_from, date_to) [Phase 1: CTE 1] | ランディングページの特定 (Session_Landing_Page) [Phase 2: CTE 2] | エンゲージメント時間の算出 (Session_Engagement) [Phase 3: CTE 3] | ディメンションと指標の結合 (Joined_Data) [Phase 4: CTE 4] | レポート用文字列フォーマット (Format_Clac) [Phase 5: Output] | 最終結果の抽出

Phase 0: 処理対象期間の固定 (UDF定義) -- ランディングページ別のセッションあたり平均エンゲージメント時間 CREATE TEMP FUNCTION date_from() RETURNS STRING AS ('20260301'); CREATE TEMP FUNCTION date_to() RETURNS STRING AS ('20260331'); Memory Block date_from() | date_to() '20260301' | '20260331' Global Query Scope 役割: クエリ全体で使用する開始日・終了日を一時関数 (TEMP FUNCTION) として定義。 解説: テーブルサフィックスの指定で何度も記述する日付をハードコードして一元管理。期間変更時の修正漏れを防ぎ、クエリの保守性と可読性を向上させています。

Phase 1-A: LP特定 - 生データの抽出 (FROM句の内側)

SELECT PARSE_DATE('%Y%m%d', event_date) AS ymd,
user_pseudo_id,
(SELECT value.int_value FROM
UNNEST(event_params) WHERE key = 'ga_session_
id') AS ga_session_id,
REGEXP_REPLACE((SELECT value.string_value FROM
UNNEST(event_params) WHERE key =
'page_location'), r'\?.', '') AS
landing_page,
(SELECT value.int_value FROM UNNEST(event_params)
WHERE key = 'entrances') AS entrances
FROM `project.dataset.events_*`
WHERE _TABLE_SUFFIX BETWEEN date_from() AND date_to()
AND event_name = 'page_view'

UNNEST Flattening Process
{ "params": [ { "key": "canmo", "key": 1000, "value": "1/20750" }, { "key": "canmo", "key": 1000, "value": "1/20750" } ] } event_params (Nested Array)
UNNEST() -> ymd, ga_session_id, landing_page, entrances

・役割: ベースとなるログデータから必要なカラムを抽出。
・解説: FROM句の内側のクエリ (データの源泉) です。指定期間のpage_viewイベントを抽出し、UNNEST関数でネストされたパラメータを展開。REGEXP_REPLACEを用いてURLのクエリパラメータを除外し、正規化されたページパスを取得しています。

Phase 1-B: LP特定 - ランディング判定 (外側のクエリ) "WITH Session_Landing_Page AS ( -- 【1】各セッションのランディングページを特定 SELECT ymd, user_pseudo_id, ga_session_id, landing_page FROM ( [前スライドのInner Query] ) WHERE entrances = 1 )," All Page Views (Inner Query Output) | | | | | WHERE entrances = 1 | | | True Landing Pages (Session_Landing_Page) ・役割: 抽出したデータから「真のランディングページ」のみを絞り込み。 ・解説: 内側のクエリで抽出した仮想テーブルに対して外側からフィルタリングを実行します。entrances = 1 (流入元となったページビュー) を満たすレコードのみを残すことで、セッションごとのランディングページを確定させます。

Phase 2: エンゲージメント時間の集計 Session_Engagement AS ( -- 【2】各セッションの合計エンゲージメント時間を算出 SELECT user_pseudo_id, (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS ga_session_id, SUM(FLOOR(SAFE_DIVIDE(COALESCE( (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'engagement_time_msec') , 0), 1000))) AS session_seconds ) FROM `project.dataset.events_*` WHERE _TABLE_SUFFIX BETWEEN date_from() AND date_to() GROUP BY user_pseudo_id, ga_session_id ), engagement_time_msec engagement_time_msec engagement_time_msec engagement_time_msec GROUP BY: user_pseudo_id + ga_session_id / 1000 (SAFE_DIVIDE) -> FLOOR() -> SUM() -> session_seconds ・役割: 各セッションが消費したエンゲージメント時間を秒単位で計算。 ・解説: ユーザーIDとセッションIDでGROUP BYを実行。ミリ秒単位の時間を抽出し、COALESCEでNULLを0に変換。その後SAFE_DIVIDEで1000で割り、安全に秒単位の合計値を算出しています。

Phase 3: ディメンションと指標の結合 (INNER JOIN) Joined_Data AS ( -- 【3】LPと時間を結合 SELECT slp.ymd, slp.landing_page, COUNT(*) AS _ss, SUM(se.session_seconds) AS _total_engagement_time_seconds FROM Session_Landing_Page slp INNER JOIN Session_Engagement se ON slp.user_pseudo_id = se.user_pseudo_id AND slp.ga_session_id = se.ga_session_id GROUP BY ymd, landing_page ), Session_Landing_Page (Phase 1) ymd user_pseudo_id ga_session_id landing_page Session_Engagement (Phase 2) user_pseudo_id ga_session_id session_seconds INNER JOIN Joined_Data ymd | landing_page | _ss | _total_engagement_time_seconds 役割: 特定したLPと計算した時間を統合。 解説: 複合キーを用いて両データを結合。日付とLPでグループ化し、セッション数 (COUNT(*)) と合計エンゲージメント時間 (SUM) を集計して最終集計のベースを作成します。

Phase 4: 出力用フォーマットへの変換 Format_Clac AS ( -- 【4】最終出力: 数値列とフォーマット列を併記 SELECT ymd, landing_page, _ss, _total_engagement_time_seconds AS _engagement_time_seconds, CONCAT( FORMAT('%02d時間 ', CAST(FLOOR( (_total_engagement_time_seconds / 3600) AS INT64)), FORMAT('%02d分 ', CAST(FLOOR(MOD(CAST( (_total_engagement_time_seconds AS INT64), 3600) / 60) AS INT64)), FORMAT('%02d秒', CAST(MOD(CAST( (_total_engagement_time_seconds AS INT64), 60) AS INT64)) ) AS format_total_time FROM Joined_Data ORDER BY ymd ASC, _ss DESC ) Total Seconds (e.g., 3665s) / 3600 -> 01時間 MOD 3600 / 60 -> 01分 MOD 60 -> 05秒 CONCAT -> 01時間 01分 05秒 ・役割: 数値の秒数を人間が読みやすい形式に変換。 ・解説: FLOOR (切り捨て) とMOD (剰余算) を組み合わせ、秒数から時間、分、秒を抽出。合計時間だけでなく、セッション数で割った平均時間も同様に文字列化しています。

Phase 5: クエリ結果の最終出力 SELECT * FROM Session_Engagement ymd | landing_page | _ss | _engagement_time_seconds 2023-10-27 | /product/A | 120 | 3600 2023-10-27 | /blog/B | 50 | 1800 2023-10-27 | /home | 200 | 7200 System Note (Important) 提供された元SQLでは最終出力として Session_Engagement が指定されています。全体のフォーマット結果 (Phase 4) を確認する場合は、ここを SELECT * FROM Format_Clac に変更して実行することが想定されるアーキテクチャとなっています。元SQLの完全性を維持するため、現状のコードを明示しています。 ・役割: 構築したテーブル構造からデータを呼び出し。 ・解説: 全ての処理が完了した後、最終的なSELECT文で結果を出力します。