GA4 ランディングページ 抽出クエリの解剖学

SELECT event_date, event_name, (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') as page_location FROM `your-project.analytics_xxxxxx.events_YYYYMMDD` WHERE event_name = 'session_start'... THE DEFINITIVE BLUEPRINT FOR GA4 BIGQUERY ANALYSIS GA4 ランディングページ 抽出クエリの解剖学 堅牢で保守性に優れたBigQuery SQLアーキテクチャの 構造的分解

なぜこのクエリが「決定版」と呼ばれるのか？ Precision GA4仕様に忠実な着地判定。 entrances = 1 を利用し、セッションの入り口を正確に特定。 Cleanliness 適切なデータクレンジング。 URLのクエリパラメータを除去し、同一ページへのセッション分散を防止。 Efficiency 効率的なデータ絞り込み。 _TABLE_SUFFIX によるスキャン量の大幅な削減とコストコントロール。

The Master Blueprint: 5つの論理ブロック
CREATE TEMP FUNCTION date_from() RETURNS STRING AS ('20260301');
CREATE TEMP FUNCTION date_to() RETURNS STRING AS ('20260331');
WITH LP_Session_Daily AS (
SELECT ymd, page_location AS landing_page, SUM(entrances) AS _ss
FROM (
SELECT PARSE_DATE("%Y%m%d", event_date) AS ymd,
REGEXP_REPLACE((SELECT value.string_value FROM UNNEST(event_params) p
WHERE p.key = 'page_location'), r"?. *", "") AS page_location,
(SELECT value.int_value FROM UNNEST(event_params) p WHERE p.key =
'entrances') AS entrances
FROM <project>.<dataset>.events_*
WHERE _TABLE_SUFFIX BETWEEN date_from() AND date_to() AND event_name =
'page_view'
)
WHERE entrances = 1
GROUP BY ymd, landing_page
ORDER BY ymd ASC, _ss DESC
)
SELECT * FROM LP_Session_Daily
[1] 期間指定の関数化
(変数初期化)
[2] メインクエリ構造
(CTE定義)
[3] サブクエリ
(抽出と加工)
[4] 絞り込みと集計
[5] 最終出力

[Block 1] 期間指定の関数化によるメンテナンス性の向上 CREATE TEMP FUNCTION date_from() RETURNS STRING AS ('20260301'); CREATE TEMP FUNCTION date_to() RETURNS STRING AS ('20260331'); 期間変更 クエリ内に直接日付を記述する のではなく、一時関数(TEMP FUNCTION)として定義。 集計期間を変更する際はこの 2行を書き換えるだけで完了し、 人為的ミスを防止する。

[Block 2] メインクエリの構造 (CTE) WITH LP_Session_Daily AS ( . . . ) 集計処理全体を LP_Session_Daily という一つ の「CTE (共通テーブル式)」 としてパッケージ化。最終的 な処理をシンプルにし、コード の可読性を劇的に高める。 LP_Session_Daily

[Block 3] サブクエリ：データの抽出と加工のコアエンジン SELECT . . . FROM <project>.<dataset>.events_* WHERE event_name = 'page_view' events_* (生データ) 抽出と加工 (Extraction & Transformation) ymd page_location entrances '20260301' /home 1 '20260301' /home 2 '20260301' /home 1 このブロックがクエリの核。生データの型変換、ネストの解除、不要な文字列の 除去を行い、集計可能なクリーンデータへと変換する。

[Block 3-A] データ型の最適化と大幅なコスト削減 PARSE_DATE ('%Y%m%d', event_date) AS ymd '20260301' -> [PARSE_DATE] 文字列である event_date を日付型へ変換し、分析を容易に。 WHERE _TABLE_SUFFIX BETWEEN date_from() AND date_to() Total Data _TABLE_SUFFIX Cost Optimization Scanned Data ワイルドカードテーブルを日付で絞り込み、スキャンデータ量を 最小化。クエリコストを大幅に削減する必須テクニック。

[Block 3-B] UNNESTによるネストデータの展開 (SELECT value.string_value FROM UNNEST(event_params) p WHERE p.key = 'page_location') event_params UNNEST page_location entrances /home 50 /product/123 20 GA4特有のキーバリュー形式で配列として格納されたデータ構造。これを UNNESTで展開(平坦化)しなければ、特定の値(パラメータ)を取りことは できない。GA4分析における最重要ステップ。

[Block 3-C] 正規表現によるURLの「正規化」 REGEXP_REPLACE(..., r"?. *", "") AS page_location Before example.com/page?utm=abc example.com/page?gclid=123 After example.com/page Merged session count (e.g., 2) クエリパラメータ(?以降)を REGEXP_REPLACE で取り除く。 これにより、同一のページがパラメータ違いで別々に集計(分散)される されるのを完全に防ぐ。

[Block 4-A] 仕様に忠実な「着地(ランディング)」判定 WHERE entrances = 1 AND event_name = 'page_view' page_view events Gatekeeper entrances = 1 セッションの入り口となるイベントのみに絞り込む。entrances = 1 を条件とすることが、GA4において「ランディングページ」を特定する 唯一にして最も確実なフラグである。

[Block 4-B] レポートの構造化とソート GROUP BY ymd, landing_page ORDER BY ymd ASC, _ss DESC ... 134 ... 233 ... 146 ... 90 ... 160 ... 106 16 ... ... 7 ... 50 ... 150 GROUP BY Date Page Session Count 2023-01-01 landing_page 150 2023-01-02 landing_page 120 2023-01-03 Page 90 2023-01-04 landing_page 60 2023-01-05 Page 30 2023-01-06 landing_page 15 抽出したデータを日付(ymd)とページ(landing_page)ごとにセッ ション数(_ss)として合計(SUM)。さらに日付順・セッション数の多 い順にソートし、分析しやすい形へ整える。

[Block 5] 最終出力：カプセル化された処理の実行 SELECT * FROM LP_Session_Daily LP_Session_Daily -> Execute CTEで定義・構築した一連の複雑な処理結果を、最後にシン プルに呼び出してテーブルとして出力する。

Synthesis: なぜこれが「決定版」のクエリなのか SELECT FROM _TABLE_SUFFIX & event_name & event_name = , , WER " " & SQL Technique Business Value _TABLE_SUFFIX & event_name 効率的なデータ絞り込み(高速化とコスト最適化) REGEXP_REPLACE 適切なデータクレンジング(データの分散防止・精度の向上) entrances = 1 GA4仕様に忠実な着地判定(正確なトラフィック評価) この3点が完全に押さえられているため、 実務で安心して使える堅牢なデータ基盤となる。

Next Steps: アーキテクチャの拡張 LP_Session_Daily JOIN CV_Events (CVイベント) LP別 CVR (Conversion Rate) このクエリは拡張性も高い。構築したこの基盤に対して「CVイベント」を結合(JOIN)させ ることで、ランディングページ別のコンバージョン率(CVR)を算出するような高度な分析へ もスムーズに展開できる。

The GA4 Analytics Engine: Assembled. 美しいコードは、正確で効率的な意思決定の基盤となる。 WITH base_events AS ( SELECT event_date, event_timestamp, event_name, user_pseudo_id, (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS page_location, (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'entrances') AS entrances FROM `analytics_XXXXXXXX.events_*` WHERE _TABLE_SUFFIX BETWEEN '20240101' AND '20240131' ), session_starts AS ( SELECT user_pseudo_id, event_timestamp, page_location FROM base_events WHERE entrances = 1 ), daily_sessions AS ( SELECT DATE(TIMESTAMP_MICROS(event_timestamp)) AS session_date, REGEXP_REPLACE(page_location, r'\?. *', '') AS landing_page, COUNT(DISTINCT user_pseudo_id) AS users, COUNT(*) AS sessions FROM session_starts GROUP BY 1, 2 ) SELECT session_date, landing_page, users, sessions FROM daily_sessions ORDER BY session_date, sessions DESC EOF SYSTEM TEARDOWN COMPLETE.