Contents
1. 導入（このSQLで解決できる課題）
各セッションのランディングページから、実際に閲覧された次ページ（以降のページ）とCV有無を集計し、入口ごとの動線の強弱を把握できる。
2. 実行結果イメージ（抜粋
views：そのセッションで該当 next_page が閲覧された回数
avg_views_before_exit：ランディングから見て何番目に訪れたかの平均（低いほど“すぐ遷移”）
EntryCompleteCount：そのセッション中のCV回数（例：
purchaseなど）
3. 背景・利用シーン
LPや一覧ページからどのページに流すとCVにつながりやすいかを把握したい
内部導線のボトルネック（例：一覧→詳細に進まず離脱）を特定したい
媒体横断の分析前に、まずは入口（ランディング）別の既定動線を押さえたい
4. SQLコード（提示＆分解解説）
-- ▼ ここをあなたのテーブルに置換（例：`myproj.analytics_XXXX.events_*`）
-- 期間は「直近300日」を _table_suffix で動的に絞り込み
WITH session_data AS (
SELECT
user_pseudo_id,
CONCAT(
CAST(user_pseudo_id AS STRING), "_",
CAST((SELECT ep.value.int_value FROM UNNEST(event_params) ep WHERE ep.key = 'ga_session_id') AS STRING)
) AS session_id,
-- 完全URL（必要に応じてパス化を推奨：後述）
(SELECT ep.value.string_value FROM UNNEST(event_params) ep WHERE ep.key = 'page_location') AS page_location,
event_timestamp,
DATE(TIMESTAMP_MICROS(event_timestamp)) AS event_date,
ROW_NUMBER() OVER (
PARTITION BY user_pseudo_id,
CONCAT(CAST(user_pseudo_id AS STRING), "_",
CAST((SELECT ep.value.int_value FROM UNNEST(event_params) ep WHERE ep.key='ga_session_id') AS STRING))
ORDER BY event_timestamp
) AS page_view_rank
FROM
`YOUR_PROJECT.YOUR_DATASET.events_*`
WHERE
_TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_ADD(CURRENT_DATE(), INTERVAL -300 DAY))
AND FORMAT_DATE('%Y%m%d', CURRENT_DATE())
AND event_name = 'page_view'
),
-- ★ ランディングページ（セッションの最初のpage_viewだけ）を厳密に取得
landing_pages AS (
SELECT
user_pseudo_id,
session_id,
page_location AS landing_page,
event_timestamp AS landing_timestamp,
DATE(TIMESTAMP_MICROS(event_timestamp)) AS landing_date
FROM session_data
QUALIFY page_view_rank = 1
),
-- CV（コンバージョン）件数をセッション単位で付与
entry_complete AS (
SELECT
CONCAT(
CAST(user_pseudo_id AS STRING), "_",
CAST((SELECT ep.value.int_value FROM UNNEST(event_params) ep WHERE ep.key = 'ga_session_id') AS STRING)
) AS session_id,
COUNT(*) AS EntryCompleteCount
FROM
`YOUR_PROJECT.YOUR_DATASET.events_*`
WHERE
_TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_ADD(CURRENT_DATE(), INTERVAL -300 DAY))
AND FORMAT_DATE('%Y%m%d', CURRENT_DATE())
AND event_name = '計測するイベント名を入力' -- ★ここをCVイベント名に置換（例：'purchase'）
GROUP BY session_id
),
-- ランディング以降のページビュー（同一セッション内、時系列順）
page_views AS (
SELECT
sd.user_pseudo_id,
sd.session_id,
sd.page_location,
sd.page_view_rank,
lp.landing_page,
lp.landing_date
FROM session_data sd
JOIN landing_pages lp
ON sd.user_pseudo_id = lp.user_pseudo_id
AND sd.session_id = lp.session_id
WHERE sd.event_timestamp >= lp.landing_timestamp
)
-- 最終出力：セッション×(ランディング→次ページ)の出現回数・平均閲覧順・CV件数
SELECT
pv.session_id,
pv.landing_date AS event_date,
pv.landing_page,
pv.page_location AS next_page,
COUNT(*) AS views,
AVG(pv.page_view_rank) AS avg_views_before_exit,
COALESCE(ec.EntryCompleteCount, 0) AS EntryCompleteCount
FROM page_views pv
LEFT JOIN entry_complete ec
ON pv.session_id = ec.session_id
GROUP BY
pv.session_id, pv.landing_date, pv.landing_page, pv.page_location, ec.EntryCompleteCount
ORDER BY EntryCompleteCount DESC;
ここがポイント
ランディング抽出は
QUALIFY page_view_rank = 1で唯一行に固定（セッション単位の最初のPV）次ページ以降は、同セッションかつランディング時刻以降の page_view を JOIN して採取
**平均閲覧順（avg_views_before_exit）**は「ランディングから何番目でそのページが現れるか」の平均。小さいほど“すぐ現れる”導線
さらに「純粋な“次ページ”」だけを見たいなら、
LEAD()を使った派生版も有効です（補強版を下に掲載）。
5. 注意点・よくあるエラー（＋改善Tips）
URLの正規化（強く推奨）
集計前に パス化 すると重複が減ります：
併せて クエリパラメータ除去・小文字化 も：
ランディングの取り方
オリジナルの
landing_pagesはGROUP BY page_locationで複数行化の恐れあり。必ずQUALIFY ROW_NUMBER()=1などで1行に。
CVイベント名の置換忘れ
計測するイベント名を入力を実プロジェクトのCVイベント（例：purchase,generate_lead）に変更。同一セッションで複数CVが起きる場合の扱い（カウント or フラグ）も要件に合わせて調整。
タイムゾーン差
GA4エクスポートは基本UTC。JSTで日付を見たい場合は
DATE(TIMESTAMP_MICROS(event_timestamp), 'Asia/Tokyo')を使用。
期間フィルタの粒度
_TABLE_SUFFIXは日付文字列基準。存在しない日付を挟むと0件に見えることがあります。必要に応じて固定期間で検証を。
“次ページだけ”を見たい場合の補強版
現行は“以降のページ全体”。直後の1ページだけを確実に取りたいなら、
LEAD(page_location)を使い、self-join不要にできます（下記）。
6. まとめ（SQLの効果と使いどころ）
入口（ランディング）→次ページ（以降）の実動線をセッション粒度で把握でき、離脱ポイントや強い導線が見える
CV有無を同テーブルで扱えるため、“つながる動線”の優先改善がしやすい
URL正規化（パス化）・ランディング抽出の厳密化（
QUALIFY）・（必要なら）LEADで次ページ特定まで行うと、精度と運用性が向上する
