このコードは、Google Analytics 4 (GA4) データを分析し、ユーザーの行動履歴とコンバージョンデータを統合して、以下のような情報を抽出するために使用されます。
主な機能・目的
ユーザーセッションデータの取得: ユーザーがサイトに訪問した日時、ランディングページ、流入元情報を取得します。
コンバージョンデータの取得: ユーザーがコンバージョン（特定の目標達成イベント、例: エントリー完了）した日時とその際の流入情報を取得します。
コンバージョンに至る過去の訪問履歴: 各ユーザーごとに、コンバージョン日までのすべての訪問セッションを整理します。
ランディングページ単位でセッションを整理: 重複データを削除し、訪問日時順に並べた訪問履歴を作成します。
コンバージョンしたユーザーが、どのメディアで、どの手動コンテンツで流入したかを計測します。
結論:ユニークユーザー単位で、コンバージョンしたユーザーの経路を各ページ経由で確認することが可能です。
WITH user_sessions AS (
SELECT
user_pseudo_id AS user_id,
TIMESTAMP_SECONDS(CAST(event_timestamp / 1000000 AS INT64)) AS event_datetime,
DATE(TIMESTAMP_SECONDS(CAST(event_timestamp / 1000000 AS INT64))) AS visit_date,
(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS landing_page,
traffic_source.name AS traffic_source_name,
traffic_source.medium AS traffic_source_medium
FROM
`~~~~~~~~~~~.events_202502*` #GA4のイベントよりテーブルIDを取得
WHERE
event_name = 'page_view' -- ✅ ページビューイベントのみ対象
AND (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') IS NOT NULL
),
cv_users AS (
SELECT DISTINCT
user_pseudo_id AS user_id,
DATE(TIMESTAMP_SECONDS(CAST(event_timestamp / 1000000 AS INT64))) AS cv_date,
(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS cv_landing_page,
traffic_source.name AS traffic_source_name,
traffic_source.medium AS traffic_source_medium
FROM
`~~~~~~~~~~~.events_202502*` #GA4のイベントよりテーブルIDを取得
WHERE
event_name = 'コンバージョン' #GA4の集計したいイベントを記載
),
latest_cv_users AS (
SELECT
user_id,
MAX(cv_date) AS latest_cv_date
FROM
cv_users
GROUP BY
user_id
),
filtered_cv_users AS (
SELECT
cu.user_id,
cu.cv_date,
cu.cv_landing_page,
cu.traffic_source_name,
cu.traffic_source_medium
FROM
cv_users cu
JOIN
latest_cv_users lcu
ON
cu.user_id = lcu.user_id AND cu.cv_date = lcu.latest_cv_date
),
user_journey AS (
SELECT DISTINCT
us.user_id,
us.event_datetime,
us.visit_date,
us.landing_page,
cu.cv_date,
cu.cv_landing_page,
cu.traffic_source_name AS conversion_traffic_source_name,
cu.traffic_source_medium AS conversion_traffic_source_medium
FROM
user_sessions us
JOIN
filtered_cv_users cu
ON
TRIM(us.user_id) = TRIM(cu.user_id)
WHERE
us.visit_date <= cu.cv_date
),
ranked_sessions AS (
SELECT
user_id,
event_datetime,
visit_date,
landing_page,
ROW_NUMBER() OVER (
PARTITION BY user_id, cv_date
ORDER BY event_datetime
) AS session_number,
cv_date,
cv_landing_page,
conversion_traffic_source_name,
conversion_traffic_source_medium
FROM
user_journey
WHERE
event_datetime IS NOT NULL
)
SELECT
user_id,
cv_date AS conversion_date,
cv_landing_page AS conversion_landing_page,
conversion_traffic_source_name,
conversion_traffic_source_medium,
-- ✅ `page_view` のみを対象にした過去のセッション情報を横持ち
MAX(IF(session_number = 1, visit_date, NULL)) AS past_session_1_date,
MAX(IF(session_number = 1, event_datetime, NULL)) AS past_session_1_time,
MAX(IF(session_number = 1, landing_page, NULL)) AS past_session_1_landing_page,
MAX(IF(session_number = 2, visit_date, NULL)) AS past_session_2_date,
MAX(IF(session_number = 2, event_datetime, NULL)) AS past_session_2_time,
MAX(IF(session_number = 2, landing_page, NULL)) AS past_session_2_landing_page,
MAX(IF(session_number = 3, visit_date, NULL)) AS past_session_3_date,
MAX(IF(session_number = 3, event_datetime, NULL)) AS past_session_3_time,
MAX(IF(session_number = 3, landing_page, NULL)) AS past_session_3_landing_page,
MAX(IF(session_number = 4, visit_date, NULL)) AS past_session_4_date,
MAX(IF(session_number = 4, event_datetime, NULL)) AS past_session_4_time,
MAX(IF(session_number = 4, landing_page, NULL)) AS past_session_4_landing_page,
-- 5〜50のセッション情報を一括生成（Pythonで自動生成）
MAX(IF(session_number = 5, visit_date, NULL)) AS past_session_5_date,
MAX(IF(session_number = 5, event_datetime, NULL)) AS past_session_5_time,
MAX(IF(session_number = 5, landing_page, NULL)) AS past_session_5_landing_page,
MAX(IF(session_number = 6, visit_date, NULL)) AS past_session_6_date,
MAX(IF(session_number = 6, event_datetime, NULL)) AS past_session_6_time,
MAX(IF(session_number = 6, landing_page, NULL)) AS past_session_6_landing_page,
MAX(IF(session_number = 7, visit_date, NULL)) AS past_session_7_date,
MAX(IF(session_number = 7, event_datetime, NULL)) AS past_session_7_time,
MAX(IF(session_number = 7, landing_page, NULL)) AS past_session_7_landing_page,
MAX(IF(session_number = 8, visit_date, NULL)) AS past_session_8_date,
MAX(IF(session_number = 8, event_datetime, NULL)) AS past_session_8_time,
MAX(IF(session_number = 8, landing_page, NULL)) AS past_session_8_landing_page,
MAX(IF(session_number = 9, visit_date, NULL)) AS past_session_9_date,
MAX(IF(session_number = 9, event_datetime, NULL)) AS past_session_9_time,
MAX(IF(session_number = 9, landing_page, NULL)) AS past_session_9_landing_page,
MAX(IF(session_number = 10, visit_date, NULL)) AS past_session_10_date,
MAX(IF(session_number = 10, event_datetime, NULL)) AS past_session_10_time,
MAX(IF(session_number = 10, landing_page, NULL)) AS past_session_10_landing_page,
MAX(IF(session_number = 11, visit_date, NULL)) AS past_session_11_date,
MAX(IF(session_number = 11, event_datetime, NULL)) AS past_session_11_time,
MAX(IF(session_number = 11, landing_page, NULL)) AS past_session_11_landing_page,
MAX(IF(session_number = 12, visit_date, NULL)) AS past_session_12_date,
MAX(IF(session_number = 12, event_datetime, NULL)) AS past_session_12_time,
MAX(IF(session_number = 12, landing_page, NULL)) AS past_session_12_landing_page,
MAX(IF(session_number = 13, visit_date, NULL)) AS past_session_13_date,
MAX(IF(session_number = 13, event_datetime, NULL)) AS past_session_13_time,
MAX(IF(session_number = 13, landing_page, NULL)) AS past_session_13_landing_page,
MAX(IF(session_number = 14, visit_date, NULL)) AS past_session_14_date,
MAX(IF(session_number = 14, event_datetime, NULL)) AS past_session_14_time,
MAX(IF(session_number = 14, landing_page, NULL)) AS past_session_14_landing_page,
MAX(IF(session_number = 15, visit_date, NULL)) AS past_session_15_date,
MAX(IF(session_number = 15, event_datetime, NULL)) AS past_session_15_time,
MAX(IF(session_number = 15, landing_page, NULL)) AS past_session_15_landing_page,
MAX(IF(session_number = 16, visit_date, NULL)) AS past_session_16_date,
MAX(IF(session_number = 16, event_datetime, NULL)) AS past_session_16_time,
MAX(IF(session_number = 16, landing_page, NULL)) AS past_session_16_landing_page,
MAX(IF(session_number = 17, visit_date, NULL)) AS past_session_17_date,
MAX(IF(session_number = 17, event_datetime, NULL)) AS past_session_17_time,
MAX(IF(session_number = 17, landing_page, NULL)) AS past_session_17_landing_page,
MAX(IF(session_number = 18, visit_date, NULL)) AS past_session_18_date,
MAX(IF(session_number = 18, event_datetime, NULL)) AS past_session_18_time,
MAX(IF(session_number = 18, landing_page, NULL)) AS past_session_18_landing_page,
MAX(IF(session_number = 19, visit_date, NULL)) AS past_session_19_date,
MAX(IF(session_number = 19, event_datetime, NULL)) AS past_session_19_time,
MAX(IF(session_number = 19, landing_page, NULL)) AS past_session_19_landing_page,
MAX(IF(session_number = 20, visit_date, NULL)) AS past_session_20_date,
MAX(IF(session_number = 20, event_datetime, NULL)) AS past_session_20_time,
MAX(IF(session_number = 20, landing_page, NULL)) AS past_session_20_landing_page,
MAX(IF(session_number = 21, visit_date, NULL)) AS past_session_21_date,
MAX(IF(session_number = 21, event_datetime, NULL)) AS past_session_21_time,
MAX(IF(session_number = 21, landing_page, NULL)) AS past_session_21_landing_page,
MAX(IF(session_number = 22, visit_date, NULL)) AS past_session_22_date,
MAX(IF(session_number = 22, event_datetime, NULL)) AS past_session_22_time,
MAX(IF(session_number = 22, landing_page, NULL)) AS past_session_22_landing_page,
MAX(IF(session_number = 23, visit_date, NULL)) AS past_session_23_date,
MAX(IF(session_number = 23, event_datetime, NULL)) AS past_session_23_time,
MAX(IF(session_number = 23, landing_page, NULL)) AS past_session_23_landing_page,
MAX(IF(session_number = 24, visit_date, NULL)) AS past_session_24_date,
MAX(IF(session_number = 24, event_datetime, NULL)) AS past_session_24_time,
MAX(IF(session_number = 24, landing_page, NULL)) AS past_session_24_landing_page,
MAX(IF(session_number = 25, visit_date, NULL)) AS past_session_25_date,
MAX(IF(session_number = 25, event_datetime, NULL)) AS past_session_25_time,
MAX(IF(session_number = 25, landing_page, NULL)) AS past_session_25_landing_page,
MAX(IF(session_number = 26, visit_date, NULL)) AS past_session_26_date,
MAX(IF(session_number = 26, event_datetime, NULL)) AS past_session_26_time,
MAX(IF(session_number = 26, landing_page, NULL)) AS past_session_26_landing_page,
MAX(IF(session_number = 27, visit_date, NULL)) AS past_session_27_date,
MAX(IF(session_number = 27, event_datetime, NULL)) AS past_session_27_time,
MAX(IF(session_number = 27, landing_page, NULL)) AS past_session_27_landing_page,
MAX(IF(session_number = 28, visit_date, NULL)) AS past_session_28_date,
MAX(IF(session_number = 28, event_datetime, NULL)) AS past_session_28_time,
MAX(IF(session_number = 28, landing_page, NULL)) AS past_session_28_landing_page,
MAX(IF(session_number = 29, visit_date, NULL)) AS past_session_29_date,
MAX(IF(session_number = 29, event_datetime, NULL)) AS past_session_29_time,
MAX(IF(session_number = 29, landing_page, NULL)) AS past_session_29_landing_page,
MAX(IF(session_number = 30, visit_date, NULL)) AS past_session_30_date,
MAX(IF(session_number = 30, event_datetime, NULL)) AS past_session_30_time,
MAX(IF(session_number = 30, landing_page, NULL)) AS past_session_30_landing_page,
MAX(IF(session_number = 31, visit_date, NULL)) AS past_session_31_date,
MAX(IF(session_number = 31, event_datetime, NULL)) AS past_session_31_time,
MAX(IF(session_number = 31, landing_page, NULL)) AS past_session_31_landing_page,
MAX(IF(session_number = 32, visit_date, NULL)) AS past_session_32_date,
MAX(IF(session_number = 32, event_datetime, NULL)) AS past_session_32_time,
MAX(IF(session_number = 32, landing_page, NULL)) AS past_session_32_landing_page,
MAX(IF(session_number = 33, visit_date, NULL)) AS past_session_33_date,
MAX(IF(session_number = 33, event_datetime, NULL)) AS past_session_33_time,
MAX(IF(session_number = 33, landing_page, NULL)) AS past_session_33_landing_page,
MAX(IF(session_number = 34, visit_date, NULL)) AS past_session_34_date,
MAX(IF(session_number = 34, event_datetime, NULL)) AS past_session_34_time,
MAX(IF(session_number = 34, landing_page, NULL)) AS past_session_34_landing_page,
MAX(IF(session_number = 35, visit_date, NULL)) AS past_session_35_date,
MAX(IF(session_number = 35, event_datetime, NULL)) AS past_session_35_time,
MAX(IF(session_number = 35, landing_page, NULL)) AS past_session_35_landing_page,
MAX(IF(session_number = 36, visit_date, NULL)) AS past_session_36_date,
MAX(IF(session_number = 36, event_datetime, NULL)) AS past_session_36_time,
MAX(IF(session_number = 36, landing_page, NULL)) AS past_session_36_landing_page,
MAX(IF(session_number = 37, visit_date, NULL)) AS past_session_37_date,
MAX(IF(session_number = 37, event_datetime, NULL)) AS past_session_37_time,
MAX(IF(session_number = 37, landing_page, NULL)) AS past_session_37_landing_page,
MAX(IF(session_number = 38, visit_date, NULL)) AS past_session_38_date,
MAX(IF(session_number = 38, event_datetime, NULL)) AS past_session_38_time,
MAX(IF(session_number = 38, landing_page, NULL)) AS past_session_38_landing_page,
MAX(IF(session_number = 39, visit_date, NULL)) AS past_session_39_date,
MAX(IF(session_number = 39, event_datetime, NULL)) AS past_session_39_time,
MAX(IF(session_number = 39, landing_page, NULL)) AS past_session_39_landing_page,
MAX(IF(session_number = 40, visit_date, NULL)) AS past_session_40_date,
MAX(IF(session_number = 40, event_datetime, NULL)) AS past_session_40_time,
MAX(IF(session_number = 40, landing_page, NULL)) AS past_session_40_landing_page,
MAX(IF(session_number = 41, visit_date, NULL)) AS past_session_41_date,
MAX(IF(session_number = 41, event_datetime, NULL)) AS past_session_41_time,
MAX(IF(session_number = 41, landing_page, NULL)) AS past_session_41_landing_page,
MAX(IF(session_number = 42, visit_date, NULL)) AS past_session_42_date,
MAX(IF(session_number = 42, event_datetime, NULL)) AS past_session_42_time,
MAX(IF(session_number = 42, landing_page, NULL)) AS past_session_42_landing_page,
MAX(IF(session_number = 43, visit_date, NULL)) AS past_session_43_date,
MAX(IF(session_number = 43, event_datetime, NULL)) AS past_session_43_time,
MAX(IF(session_number = 43, landing_page, NULL)) AS past_session_43_landing_page,
MAX(IF(session_number = 44, visit_date, NULL)) AS past_session_44_date,
MAX(IF(session_number = 44, event_datetime, NULL)) AS past_session_44_time,
MAX(IF(session_number = 44, landing_page, NULL)) AS past_session_44_landing_page,
MAX(IF(session_number = 45, visit_date, NULL)) AS past_session_45_date,
MAX(IF(session_number = 45, event_datetime, NULL)) AS past_session_45_time,
MAX(IF(session_number = 45, landing_page, NULL)) AS past_session_45_landing_page,
MAX(IF(session_number = 46, visit_date, NULL)) AS past_session_46_date,
MAX(IF(session_number = 46, event_datetime, NULL)) AS past_session_46_time,
MAX(IF(session_number = 46, landing_page, NULL)) AS past_session_46_landing_page,
MAX(IF(session_number = 47, visit_date, NULL)) AS past_session_47_date,
MAX(IF(session_number = 47, event_datetime, NULL)) AS past_session_47_time,
MAX(IF(session_number = 47, landing_page, NULL)) AS past_session_47_landing_page,
MAX(IF(session_number = 48, visit_date, NULL)) AS past_session_48_date,
MAX(IF(session_number = 48, event_datetime, NULL)) AS past_session_48_time,
MAX(IF(session_number = 48, landing_page, NULL)) AS past_session_48_landing_page,
MAX(IF(session_number = 49, visit_date, NULL)) AS past_session_49_date,
MAX(IF(session_number = 49, event_datetime, NULL)) AS past_session_49_time,
MAX(IF(session_number = 49, landing_page, NULL)) AS past_session_49_landing_page,
MAX(IF(session_number = 50, visit_date, NULL)) AS past_session_50_date,
MAX(IF(session_number = 50, event_datetime, NULL)) AS past_session_50_time,
MAX(IF(session_number = 50, landing_page, NULL)) AS past_session_50_landing_page
FROM
ranked_sessions
WHERE
session_number <= 50 -- 過去50セッションのみ対象
GROUP BY
user_id, cv_date, cv_landing_page, conversion_traffic_source_name, conversion_traffic_source_medium
ORDER BY
cv_date ASC;
