【検証】GA4×BigQueryセッションのズレ補正に挑戦しました！（全クエリ掲載・続編あり）

本記事は、下記の実践ガイド記事の続編です：
👉 【後編】GA4×BigQuery セッションのズレを補正する実践ガイド

前回の記事では、GA4とBigQuery間で発生するセッション数のズレや、チャネル情報（default_channel_group）の不一致について、その背景や構造的な違いを解説し、代表的な補正SQLを紹介しました。

今回はその実装を踏まえ、実際にどこまでズレを補正できるか？を検証してみました。
使用したのは、ChatGPTに組んでもらった3種類のSQLクエリです。
（※使用したSQLクエリ全文は本記事の最後に記載しています）

🧪 今回の検証でわかったこと（結論）
1. 前回紹介した補正方法の復習
- ✅ 補正方法(1)：2024年7月17日以降のデータ用（抜粋）
- ✅ 補正方法(2)：2024年7月16日以前のデータ用
2. SQLクエリの使い方（Looker Studio で実行）
3. 検証結果（クエリ別に比較）
- 📊 セッションのメディア別セッション数
- 🧮 デフォルトチャネルグループ別セッション数
4. なぜチャネルグループ再現に失敗したのか？（要検証）
5. 今回使用したSQLクエリ全文

🧪 今回の検証でわかったこと（結論）

✅ セッション数の補正は想定通り成功
❌ チャネルグループの復元は一部失敗／誤分類が残った

特に (none) / (not set) / Direct の境界処理や Organic Social の分類でズレが見られました。
実際の計測値やクエリ全文も末尾に記載していますので、ぜひ参考にしてください。

1. 前回紹介した補正方法の復習

GA4の画面で表示されている「セッションの流入元」には、ダイレクト流入した再訪者が、前回訪問時に非ダイレクト流入していた場合は、前回の流入元からのアクセスとしてカウントするロジックが組み込まれています。
BigQueryにエクスポートされるデータにはその補正が反映されないため、SQLクエリによって再現する必要があります。

ただし、2024年7月17日以降のデータについては「session_traffic_source_last_click」という構造体(※)が実装されたため、再現が大幅に楽になりました。

※構造体：まとまった情報セット（フィールドのかたまり）

✅ 補正方法(1)：2024年7月17日以降のデータ用（抜粋）

SELECT
  session_traffic_source_last_click.manual_campaign.source,
  session_traffic_source_last_click.manual_campaign.medium
FROM `your_project.your_dataset.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20240717' AND '20241231'
  AND event_name = 'session_start'

✅ 補正方法(2)：2024年7月16日以前のデータ用

2024年7月16日以前のデータには「session_traffic_source_last_click」が存在しないため、自力で下記のロジックを再現する必要があります。

処理ステップ：

セッションごとのトラフィックソースを収集
ダイレクト流入を除いた前回の流入元をユーザー単位で保持
セッション順に遡って非Directのソースを引き継ぐ

BigQuery上で正しく実装すれば、GA4画面にかなり近いセッション分類が可能になるはずです。

2. SQLクエリの使い方（Looker Studio で実行）

Looker Studio の「カスタムクエリ機能」を使えば、そのまま接続して利用できます（下図参照）

今回使用したSQLクエリは、本記事の最後に全文掲載しています。
データの取得期間は過去30日になっていますので、取得期間を変えたいときは下記の部分を修正してください。

WHERE _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY))
                      AND FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))

3. 検証結果（クエリ別に比較）

下記の検証は、2025年3月30日〜4月12日（14日間）の弊社サイトのGA4エクスポートデータでおこないました。

クエリ(1)：補正なし、チャネルグループのみ作成
クエリ(2)：新バージョン補正（session_traffic_source_last_click使用）
クエリ(3)：旧バージョン補正（Last Non-Direct Click方式）

📊 セッションのメディア別セッション数

medium	GA4	クエリ(1)	クエリ(2)	クエリ(3)
referral	48	43	48	47
organic	44	31	42	43
(none)	30	null	30	30
(not set)	1	―	1	―
null	―	47	1	30

🧮 デフォルトチャネルグループ別セッション数

default_channel_group	GA4	クエリ(1)	クエリ(2)	クエリ(3)
Organic Search	44	31	42	43
Organic Social	44	―	―	―
Direct	30	―	―	―
Referral	4	90	79	78
Unassigned	1	―	―	―

4. なぜチャネルグループ再現に失敗したのか？（要検証）

Organic Social の正規表現がクエリ(1)に未実装
(none) や (not set) を Direct とみなすロジックが不十分
Unassigned を拾う条件（source/mediumが null）を含めていない

次回の改善ポイントとして整理し、再検証を進める予定です。

5. 今回使用したSQLクエリ全文

以下が、今回使用したSQLクエリになります。
・「your_project」のところに、BigQueryのプロジェクトID
・「your_dataset」のところに、BigQueryのデータセット名
を入れてご利用ください。

クエリ(1)：補正なし（チャネルグループ分類のみ）

WITH base AS (
  SELECT
    event_date,
    PARSE_DATE('%Y%m%d', event_date) AS parsed_event_date,
    user_pseudo_id,
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS ga_session_id,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'source') AS source,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'medium') AS medium
  FROM
    `your_project.your_dataset.events_*`
  WHERE
    _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY))
                     AND FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
    AND event_name = 'session_start'
),
classified AS (
  SELECT
    parsed_event_date,
    user_pseudo_id,
    ga_session_id,
    source,
    medium,
    CASE
      WHEN REGEXP_CONTAINS(medium, r"^organic$") AND REGEXP_CONTAINS(source, r'(google|bing|yahoo|baidu|duckduckgo|search\\.brave\\.com)') THEN 'Organic Search'
      WHEN REGEXP_CONTAINS(source, r'(facebook|instagram|twitter|t.co|linkedin|pinterest)')
           AND REGEXP_CONTAINS(medium, r'^(social|social-organic)$') THEN 'Organic Social'
      WHEN REGEXP_CONTAINS(source, r'(facebook|instagram|twitter|t.co|linkedin|pinterest)')
           AND REGEXP_CONTAINS(medium, r'^(.*cp.*|ppc|retargeting|paid.*)$') THEN 'Paid Social'
      WHEN REGEXP_CONTAINS(medium, r'^(cpc|ppc|paidsearch)$')
           AND REGEXP_CONTAINS(source, r'(google|bing|yahoo|baidu|duckduckgo|search\\.brave\\.com)') THEN 'Paid Search'
      WHEN REGEXP_CONTAINS(medium, r'^(email|e-mail)$') THEN 'Email'
      WHEN REGEXP_CONTAINS(medium, r'^(affiliate)$') THEN 'Affiliates'
      WHEN REGEXP_CONTAINS(medium, r'^(display|banner|cpm)$') THEN 'Display'
      WHEN REGEXP_CONTAINS(medium, r'^(video|paidvideo)$') THEN 'Paid Video'
      WHEN REGEXP_CONTAINS(medium, r'^(organic-video)$') THEN 'Organic Video'
      WHEN REGEXP_CONTAINS(medium, r'^(audio)$') THEN 'Audio'
      WHEN REGEXP_CONTAINS(medium, r'^(sms|text)$') THEN 'SMS'
      WHEN REGEXP_CONTAINS(medium, r'^(push|notification)$') THEN 'Mobile Push Notifications'
      WHEN source = '(direct)' AND (medium = '(not set)' OR medium = '(none)') THEN 'Direct'
      ELSE 'Referral'
    END AS default_channel_group
  FROM base
)
SELECT
  parsed_event_date,
  default_channel_group,
  medium,
  COUNT(DISTINCT CONCAT(user_pseudo_id, CAST(ga_session_id AS STRING))) AS sessions
FROM classified
GROUP BY parsed_event_date, default_channel_group, medium
ORDER BY parsed_event_date, default_channel_group, medium;

クエリ(2)：新バージョン補正（session_traffic_source_last_click使用）

WITH base AS (
  SELECT
    event_date,
    PARSE_DATE('%Y%m%d', event_date) AS parsed_event_date,
    user_pseudo_id,
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS ga_session_id,
    session_traffic_source_last_click.manual_campaign.source AS session_source,
    session_traffic_source_last_click.manual_campaign.medium AS session_medium
  FROM
    `your_project.your_dataset.events_*`
  WHERE
    _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY))
                     AND FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
    AND event_name = 'session_start'
),
classified AS (
  SELECT
    parsed_event_date,
    user_pseudo_id,
    ga_session_id,
    session_source,
    session_medium,
    CASE
      WHEN REGEXP_CONTAINS(session_medium, r"^organic$") AND REGEXP_CONTAINS(session_source, r'(google|bing|yahoo|baidu|duckduckgo|search\\.brave\\.com)') THEN 'Organic Search'
      WHEN REGEXP_CONTAINS(session_source, r'(facebook|instagram|twitter|t.co|linkedin|pinterest)')
           AND REGEXP_CONTAINS(session_medium, r'^(social|social-organic)$') THEN 'Organic Social'
      WHEN REGEXP_CONTAINS(session_source, r'(facebook|instagram|twitter|t.co|linkedin|pinterest)')
           AND REGEXP_CONTAINS(session_medium, r'^(.*cp.*|ppc|retargeting|paid.*)$') THEN 'Paid Social'
      WHEN REGEXP_CONTAINS(session_medium, r'^(cpc|ppc|paidsearch)$')
           AND REGEXP_CONTAINS(session_source, r'(google|bing|yahoo|baidu|duckduckgo|search\\.brave\\.com)') THEN 'Paid Search'
      WHEN REGEXP_CONTAINS(session_medium, r'^(email|e-mail)$') THEN 'Email'
      WHEN REGEXP_CONTAINS(session_medium, r'^(affiliate)$') THEN 'Affiliates'
      WHEN REGEXP_CONTAINS(session_medium, r'^(display|banner|cpm)$') THEN 'Display'
      WHEN REGEXP_CONTAINS(session_medium, r'^(video|paidvideo)$') THEN 'Paid Video'
      WHEN REGEXP_CONTAINS(session_medium, r'^(organic-video)$') THEN 'Organic Video'
      WHEN REGEXP_CONTAINS(session_medium, r'^(audio)$') THEN 'Audio'
      WHEN REGEXP_CONTAINS(session_medium, r'^(sms|text)$') THEN 'SMS'
      WHEN REGEXP_CONTAINS(session_medium, r'^(push|notification)$') THEN 'Mobile Push Notifications'
      WHEN session_source = '(direct)' AND (session_medium = '(not set)' OR session_medium = '(none)') THEN 'Direct'
      ELSE 'Referral'
    END AS default_channel_group
  FROM base
)
SELECT
  parsed_event_date,
  default_channel_group,
  session_medium AS medium,
  COUNT(DISTINCT CONCAT(user_pseudo_id, CAST(ga_session_id AS STRING))) AS sessions
FROM classified
GROUP BY parsed_event_date, default_channel_group, medium
ORDER BY parsed_event_date, default_channel_group, medium;

クエリ(3)：旧バージョン補正（Last Non-Direct Click方式）

WITH SessionData AS (
  SELECT
    user_pseudo_id,
    PARSE_DATE('%Y%m%d', FORMAT_TIMESTAMP('%Y%m%d', TIMESTAMP_MICROS(event_timestamp))) AS parsed_event_date,
    event_timestamp,
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS session_id,
    collected_traffic_source.manual_source AS source,
    collected_traffic_source.manual_medium AS medium,
    collected_traffic_source.manual_campaign_name AS campaign,
    CASE
      WHEN (collected_traffic_source.manual_source IS NULL OR collected_traffic_source.manual_source = 'direct')
           AND (collected_traffic_source.manual_medium IS NULL OR collected_traffic_source.manual_medium = '(none)')
      THEN TRUE ELSE FALSE
    END AS is_direct
  FROM
    `your_project.your_dataset.events_*`
  WHERE
    _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY))
                     AND FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
),
UserJourneys AS (
  SELECT
    user_pseudo_id,
    session_id,
    parsed_event_date,
    MIN(event_timestamp) AS session_start_time,
    MAX(is_direct) AS is_direct_session,
    MAX(IF(is_direct = FALSE, source, NULL)) AS non_direct_source,
    MAX(IF(is_direct = FALSE, medium, NULL)) AS non_direct_medium,
    MAX(IF(is_direct = FALSE, campaign, NULL)) AS non_direct_campaign
  FROM SessionData
  GROUP BY user_pseudo_id, session_id, parsed_event_date
),
LastNonDirectAttribution AS (
  SELECT
    a.user_pseudo_id,
    a.session_id,
    a.parsed_event_date,
    a.session_start_time,
    a.is_direct_session,
    CASE WHEN a.is_direct_session THEN
      LAST_VALUE(b.non_direct_source IGNORE NULLS)
        OVER (PARTITION BY a.user_pseudo_id ORDER BY a.session_start_time ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
    ELSE a.non_direct_source END AS final_source,
    CASE WHEN a.is_direct_session THEN
      LAST_VALUE(b.non_direct_medium IGNORE NULLS)
        OVER (PARTITION BY a.user_pseudo_id ORDER BY a.session_start_time ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
    ELSE a.non_direct_medium END AS final_medium
  FROM UserJourneys a
  LEFT JOIN UserJourneys b
    ON a.user_pseudo_id = b.user_pseudo_id
   AND b.session_start_time <= a.session_start_time
)
SELECT
  parsed_event_date,
  CASE
    WHEN REGEXP_CONTAINS(final_medium, r"^organic$") AND REGEXP_CONTAINS(final_source, r'(google|bing|yahoo|baidu|duckduckgo|search\\.brave\\.com)') THEN 'Organic Search'
    WHEN REGEXP_CONTAINS(final_source, r'(facebook|instagram|twitter|t.co|linkedin|pinterest)')
         AND REGEXP_CONTAINS(final_medium, r'^(social|social-organic)$') THEN 'Organic Social'
    WHEN REGEXP_CONTAINS(final_source, r'(facebook|instagram|twitter|t.co|linkedin|pinterest)')
         AND REGEXP_CONTAINS(final_medium, r'^(.*cp.*|ppc|retargeting|paid.*)$') THEN 'Paid Social'
    WHEN REGEXP_CONTAINS(final_medium, r'^(cpc|ppc|paidsearch)$')
         AND REGEXP_CONTAINS(final_source, r'(google|bing|yahoo|baidu|duckduckgo|search\\.brave\\.com)') THEN 'Paid Search'
    WHEN REGEXP_CONTAINS(final_medium, r'^(email|e-mail)$') THEN 'Email'
    WHEN REGEXP_CONTAINS(final_medium, r'^(affiliate)$') THEN 'Affiliates'
    WHEN REGEXP_CONTAINS(final_medium, r'^(display|banner|cpm)$') THEN 'Display'
    WHEN REGEXP_CONTAINS(final_medium, r'^(video|paidvideo)$') THEN 'Paid Video'
    WHEN REGEXP_CONTAINS(final_medium, r'^(organic-video)$') THEN 'Organic Video'
    WHEN REGEXP_CONTAINS(final_medium, r'^(audio)$') THEN 'Audio'
    WHEN REGEXP_CONTAINS(final_medium, r'^(sms|text)$') THEN 'SMS'
    WHEN REGEXP_CONTAINS(final_medium, r'^(push|notification)$') THEN 'Mobile Push Notifications'
    WHEN final_source = '(direct)' AND (final_medium = '(not set)' OR final_medium = '(none)') THEN 'Direct'
    ELSE 'Referral'
  END AS default_channel_group,
  final_medium AS medium,
  COUNT(DISTINCT CONCAT(user_pseudo_id, CAST(session_id AS STRING))) AS sessions
FROM LastNonDirectAttribution
GROUP BY parsed_event_date, default_channel_group, medium
ORDER BY parsed_event_date, default_channel_group, medium;

📌 この記事は、GA4 × BigQuery の実務分析を支援する「サイトウオンライン」の実験室よりお届けしました。