【後編】GA4×BigQuery セッションのズレを補正する実践ガイド｜チャネルの再現とSQL実装のポイント

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

SELECT
  user_pseudo_id,
  (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS session_id,
  session_traffic_source_last_click.source AS session_source,
  session_traffic_source_last_click.medium AS session_medium,
  session_traffic_source_last_click.campaign AS session_campaign
FROM
  `your_project.your_dataset.ga4_events_*`
WHERE
  _TABLE_SUFFIX BETWEEN '20240717' AND '20241231'
  AND event_name = 'session_start'

WITH SessionData AS (
  SELECT
    user_pseudo_id,
    FORMAT_TIMESTAMP('%Y%m%d', TIMESTAMP_MICROS(event_timestamp)) AS event_date,
    event_timestamp,
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS session_id,
    collected_traffic_source.source AS source,
    collected_traffic_source.medium AS medium,
    collected_traffic_source.campaign AS campaign,
    CASE 
      WHEN (collected_traffic_source.source IS NULL OR collected_traffic_source.source = 'direct') 
           AND (collected_traffic_source.medium IS NULL OR collected_traffic_source.medium = '(none)') 
      THEN TRUE ELSE FALSE 
    END AS is_direct
  FROM
    `your_project.your_dataset.ga4_events_*`
  WHERE
    _TABLE_SUFFIX BETWEEN '20230101' AND '20231231'
  GROUP BY
    user_pseudo_id, event_date, event_timestamp, session_id, source, medium, campaign, is_direct
),

UserJourneys AS (
  SELECT
    user_pseudo_id,
    session_id,
    event_date,
    MIN(event_timestamp) AS session_start_time,
    MAX(is_direct) AS is_direct_session,
    CASE 
      WHEN MAX(is_direct) = FALSE THEN MAX(IF(is_direct = FALSE, source, NULL)) 
      ELSE NULL 
    END AS non_direct_source,
    CASE 
      WHEN MAX(is_direct) = FALSE THEN MAX(IF(is_direct = FALSE, medium, NULL)) 
      ELSE NULL 
    END AS non_direct_medium,
    CASE 
      WHEN MAX(is_direct) = FALSE THEN MAX(IF(is_direct = FALSE, campaign, NULL)) 
      ELSE NULL 
    END AS non_direct_campaign
  FROM
    SessionData
  GROUP BY
    user_pseudo_id, session_id, event_date
),

LastNonDirectAttribution AS (
  SELECT
    a.user_pseudo_id,
    a.session_id,
    a.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 attributed_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 attributed_medium,
    CASE
      WHEN a.is_direct_session THEN
        LAST_VALUE(b.non_direct_campaign 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_campaign
    END AS attributed_campaign
  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
  user_pseudo_id,
  session_id,
  event_date,
  is_direct_session,
  COALESCE(attributed_source, 'direct') AS final_source,
  COALESCE(attributed_medium, '(none)') AS final_medium,
  attributed_campaign AS final_campaign
FROM
  LastNonDirectAttribution
ORDER BY
  user_pseudo_id, session_start_time