【2025年最新版】GA4×BigQueryセッションのズレ補正！検証結果と全SQLクエリ公開

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'

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))

WITH base AS (
  SELECT
    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 session_id,
    LOWER(IFNULL((SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'source'), '')) AS source,
    LOWER(IFNULL((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,
    session_id,
    source,
    medium,
    CASE
      WHEN medium = '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.*|.*reddit.*)')
           AND medium IN ('social', 'social-organic', 'referral') THEN 'Organic Social'
      WHEN REGEXP_CONTAINS(source, r'(.*facebook.*|.*instagram.*|.*twitter.*|.*t\\.co.*|.*linkedin.*|.*pinterest.*|.*reddit.*)')
           AND REGEXP_CONTAINS(medium, r'(paidsocial|.*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 medium IN ('email', 'e-mail') THEN 'Email'
      WHEN medium = 'affiliate' THEN 'Affiliates'
      WHEN medium IN ('display', 'banner', 'cpm') THEN 'Display'
      WHEN medium IN ('video', 'paidvideo') THEN 'Paid Video'
      WHEN medium = 'organic-video' THEN 'Organic Video'
      WHEN medium = 'audio' THEN 'Audio'
      WHEN medium IN ('sms', 'text') THEN 'SMS'
      WHEN medium IN ('push', 'notification') THEN 'Mobile Push Notifications'
      WHEN medium = 'referral' THEN 'Referral'
      WHEN source IN ('', '(direct)', '(not set)') AND medium IN ('', '(none)', '(not set)') THEN 'Direct'
      ELSE 'Unassigned'
    END AS default_channel_group
  FROM base
)
SELECT
  parsed_event_date,
  default_channel_group,
  source,
  medium,
  COUNT(DISTINCT CONCAT(user_pseudo_id, CAST(session_id AS STRING))) AS sessions
FROM classified
GROUP BY parsed_event_date, default_channel_group, source, medium
ORDER BY parsed_event_date, default_channel_group, source, medium;

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,
    LOWER(IFNULL(collected_traffic_source.manual_source, '')) AS source,
    LOWER(IFNULL(collected_traffic_source.manual_medium, '')) AS medium,
    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
  FROM SessionData
  GROUP BY user_pseudo_id, session_id, parsed_event_date
),
RankedSessions AS (
  SELECT
    *,
    LAST_VALUE(non_direct_source IGNORE NULLS) OVER (
      PARTITION BY user_pseudo_id
      ORDER BY session_start_time
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS final_source,
    LAST_VALUE(non_direct_medium IGNORE NULLS) OVER (
      PARTITION BY user_pseudo_id
      ORDER BY session_start_time
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS final_medium
  FROM UserJourneys
),
Finalized AS (
  SELECT
    parsed_event_date,
    user_pseudo_id,
    session_id,
    session_start_time,
    is_direct_session,
    CASE
      WHEN is_direct_session AND final_source IS NULL THEN ''
      WHEN is_direct_session THEN final_source
      ELSE non_direct_source
    END AS source,
    CASE
      WHEN is_direct_session AND final_medium IS NULL THEN ''
      WHEN is_direct_session THEN final_medium
      ELSE non_direct_medium
    END AS medium
  FROM RankedSessions
)
SELECT
  parsed_event_date,
  CASE
    WHEN medium = '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.*|.*reddit.*)')
         AND medium IN ('social', 'social-organic', 'referral') THEN 'Organic Social'
    WHEN REGEXP_CONTAINS(source, r'(.*facebook.*|.*instagram.*|.*twitter.*|.*t\\.co.*|.*linkedin.*|.*pinterest.*|.*reddit.*)')
         AND REGEXP_CONTAINS(medium, r'(paidsocial|.*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 medium IN ('email', 'e-mail') THEN 'Email'
    WHEN medium = 'affiliate' THEN 'Affiliates'
    WHEN medium IN ('display', 'banner', 'cpm') THEN 'Display'
    WHEN medium IN ('video', 'paidvideo') THEN 'Paid Video'
    WHEN medium = 'organic-video' THEN 'Organic Video'
    WHEN medium = 'audio' THEN 'Audio'
    WHEN medium IN ('sms', 'text') THEN 'SMS'
    WHEN medium IN ('push', 'notification') THEN 'Mobile Push Notifications'
    WHEN medium = 'referral' THEN 'Referral'
    WHEN source IN ('', '(direct)', '(not set)') AND medium IN ('', '(none)', '(not set)') THEN 'Direct'
    ELSE 'Unassigned'
  END AS default_channel_group,
  source,
  medium,
  COUNT(DISTINCT CONCAT(user_pseudo_id, CAST(session_id AS STRING))) AS sessions
FROM Finalized
GROUP BY parsed_event_date, default_channel_group, source, medium
ORDER BY parsed_event_date, default_channel_group, source, medium;

WITH base AS (
  SELECT
    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 session_id,
    LOWER(IFNULL(session_traffic_source_last_click.manual_campaign.source, '')) AS source,
    LOWER(IFNULL(session_traffic_source_last_click.manual_campaign.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,
    session_id,
    source,
    medium,
    CASE
      WHEN medium = '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.*|.*reddit.*)')
           AND medium IN ('social', 'social-organic', 'referral') THEN 'Organic Social'
      WHEN REGEXP_CONTAINS(source, r'(.*facebook.*|.*instagram.*|.*twitter.*|.*t\\.co.*|.*linkedin.*|.*pinterest.*|.*reddit.*)')
           AND REGEXP_CONTAINS(medium, r'(paidsocial|.*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 medium IN ('email', 'e-mail') THEN 'Email'
      WHEN medium = 'affiliate' THEN 'Affiliates'
      WHEN medium IN ('display', 'banner', 'cpm') THEN 'Display'
      WHEN medium IN ('video', 'paidvideo') THEN 'Paid Video'
      WHEN medium = 'organic-video' THEN 'Organic Video'
      WHEN medium = 'audio' THEN 'Audio'
      WHEN medium IN ('sms', 'text') THEN 'SMS'
      WHEN medium IN ('push', 'notification') THEN 'Mobile Push Notifications'
      WHEN medium = 'referral' THEN 'Referral'
      WHEN source IN ('', '(direct)', '(not set)') AND medium IN ('', '(none)', '(not set)') THEN 'Direct'
      ELSE 'Unassigned'
    END AS default_channel_group
  FROM base
)
SELECT
  parsed_event_date,
  default_channel_group,
  source,
  medium,
  COUNT(DISTINCT CONCAT(user_pseudo_id, CAST(session_id AS STRING))) AS sessions
FROM classified
GROUP BY parsed_event_date, default_channel_group, source, medium
ORDER BY parsed_event_date, default_channel_group, source, medium;

SELECT
  PARSE_DATE('%Y%m%d', event_date) AS parsed_event_date,
  session_traffic_source_last_click.cross_channel_campaign.default_channel_group AS default_channel_group,
  LOWER(IFNULL(session_traffic_source_last_click.manual_campaign.source, '')) AS source,
  LOWER(IFNULL(session_traffic_source_last_click.manual_campaign.medium, '')) AS medium,
  COUNT(DISTINCT CONCAT(user_pseudo_id, CAST((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS STRING))) AS sessions
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'
GROUP BY
  parsed_event_date, default_channel_group, source, medium
ORDER BY
  parsed_event_date, default_channel_group, source, medium;