GA4 + BigQuery로 ROAS 파이프라인 직접 만들기 — 플랫폼 대시보드 못 믿을 때

project.analytics_<property_id>.events_YYYYMMDD

WITH google_touches AS (
  SELECT
    e.user_pseudo_id,
    TIMESTAMP_MICROS(e.event_timestamp) AS touch_time,
    'google_ads' AS channel,
    CAST(
      e.session_traffic_source_last_click.google_ads_campaign.campaign_id
      AS STRING
    ) AS campaign_id,
    1 AS mapping_priority
  FROM `project.analytics_<id>.events_*` e
  WHERE _TABLE_SUFFIX BETWEEN '20260325' AND '20260430'
    AND e.event_name = 'session_start'
    AND e.user_pseudo_id IS NOT NULL
    AND e.session_traffic_source_last_click.google_ads_campaign.campaign_id
        IS NOT NULL
),
manual_touches AS (
  SELECT
    e.user_pseudo_id,
    TIMESTAMP_MICROS(e.event_timestamp) AS touch_time,
    m.channel,
    m.campaign_id,
    2 AS mapping_priority
  FROM `project.analytics_<id>.events_*` e
  JOIN `project.mart.dim_paid_campaign_map` m
    ON LOWER(e.collected_traffic_source.manual_source) = LOWER(m.utm_source)
   AND LOWER(e.collected_traffic_source.manual_medium) = LOWER(m.utm_medium)
   AND LOWER(e.collected_traffic_source.manual_campaign_name)
       = LOWER(m.utm_campaign)
  WHERE _TABLE_SUFFIX BETWEEN '20260325' AND '20260430'
    AND e.event_name = 'session_start'
    AND e.user_pseudo_id IS NOT NULL
),
touches AS (
  SELECT * EXCEPT(mapping_priority)
  FROM (
    SELECT * FROM google_touches
    UNION ALL
    SELECT * FROM manual_touches
  )
  QUALIFY ROW_NUMBER() OVER (
    PARTITION BY user_pseudo_id, touch_time
    ORDER BY mapping_priority
  ) = 1
),
purchases AS (
  SELECT
    e.ecommerce.transaction_id,
    e.user_pseudo_id,
    TIMESTAMP_MICROS(e.event_timestamp) AS purchase_time,
    o.net_revenue_krw AS revenue_krw,
    e.event_timestamp
  FROM `project.analytics_<id>.events_*` e
  JOIN `project.mart.orders` o
    ON e.ecommerce.transaction_id = o.transaction_id
  WHERE e.event_name = 'purchase'
    AND e.ecommerce.transaction_id IS NOT NULL
    AND _TABLE_SUFFIX BETWEEN '20260401' AND '20260430'
  QUALIFY ROW_NUMBER() OVER (
    PARTITION BY e.ecommerce.transaction_id
    ORDER BY e.event_timestamp
  ) = 1
),
attributed AS (
  SELECT p.*, t.touch_time, t.channel, t.campaign_id
  FROM purchases p
  LEFT JOIN touches t
    ON p.user_pseudo_id = t.user_pseudo_id
   AND t.touch_time BETWEEN TIMESTAMP_SUB(p.purchase_time, INTERVAL 7 DAY)
                        AND p.purchase_time
  QUALIFY ROW_NUMBER() OVER (
    PARTITION BY p.transaction_id
    ORDER BY t.touch_time DESC NULLS LAST
  ) = 1
),
revenue_daily AS (
  SELECT DATE(touch_time, 'Asia/Seoul') AS date_kst, channel, campaign_id,
         COUNT(*) AS orders, SUM(revenue_krw) AS revenue_krw
  FROM attributed
  WHERE touch_time IS NOT NULL
  GROUP BY 1, 2, 3
),
spend_daily AS (
  SELECT date_kst, channel, campaign_id, SUM(spend_krw) AS spend_krw
  FROM `project.mart.ads_spend`
  GROUP BY 1, 2, 3
)
SELECT
  COALESCE(r.date_kst, s.date_kst) AS date_kst,
  COALESCE(r.channel, s.channel) AS channel,
  COALESCE(r.campaign_id, s.campaign_id) AS campaign_id,
  COALESCE(s.spend_krw, 0) AS spend_krw,
  COALESCE(r.revenue_krw, 0) AS revenue_krw,
  COALESCE(r.orders, 0) AS orders,
  SAFE_DIVIDE(
    COALESCE(r.revenue_krw, 0), COALESCE(s.spend_krw, 0)
  ) AS roas
FROM revenue_daily r
FULL OUTER JOIN spend_daily s
  ON r.date_kst = s.date_kst
 AND r.channel = s.channel
 AND r.campaign_id = s.campaign_id