GA4 없이 마케팅 퍼널 직접 만들기 — 세션/어트리뷰션/ROAS SQL 구현

WITH events_with_gap AS (
  SELECT
    user_id, event_id, event_timestamp, event_name,
    LAG(event_timestamp) OVER (
      PARTITION BY user_id ORDER BY event_timestamp
    ) AS prev_event_time,
    TIMESTAMP_DIFF(
      event_timestamp,
      LAG(event_timestamp) OVER (
        PARTITION BY user_id ORDER BY event_timestamp
      ),
      MINUTE
    ) AS minutes_since_prev
  FROM events
  WHERE DATE(event_timestamp) >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
),
session_boundaries AS (
  SELECT *,
    CASE
      WHEN prev_event_time IS NULL THEN 1
      WHEN minutes_since_prev > 30 THEN 1
      ELSE 0
    END AS is_session_start
  FROM events_with_gap
),
sessions AS (
  SELECT *,
    CONCAT(user_id, '_', SUM(is_session_start) OVER (
      PARTITION BY user_id
      ORDER BY event_timestamp
      ROWS UNBOUNDED PRECEDING
    )) AS session_id
  FROM session_boundaries
)
SELECT * FROM sessions ORDER BY user_id, event_timestamp;

WITH user_first_touch AS (
  SELECT user_id, utm_source,
    ROW_NUMBER() OVER (
      PARTITION BY user_id ORDER BY event_timestamp ASC
    ) AS touch_order
  FROM events WHERE utm_source IS NOT NULL
),
first_channel AS (
  SELECT user_id, utm_source AS first_source
  FROM user_first_touch WHERE touch_order = 1
),
user_conversions AS (
  SELECT user_id, SUM(revenue) AS total_revenue
  FROM events WHERE event_name = 'purchase' AND revenue > 0
  GROUP BY user_id
)
SELECT
  COALESCE(fc.first_source, 'direct') AS source,
  COUNT(DISTINCT uc.user_id) AS converting_users,
  SUM(uc.total_revenue) AS attributed_revenue
FROM user_conversions uc
LEFT JOIN first_channel fc ON uc.user_id = fc.user_id
GROUP BY 1 ORDER BY attributed_revenue DESC;

WITH user_journeys AS (
  SELECT user_id, event_timestamp, event_name, revenue,
    LAST_VALUE(CASE WHEN utm_source IS NOT NULL THEN utm_source END IGNORE NULLS) OVER (
      PARTITION BY user_id ORDER BY event_timestamp
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS last_known_source
  FROM events
)
SELECT
  COALESCE(last_known_source, 'direct') AS source,
  SUM(CASE WHEN event_name = 'purchase' THEN revenue ELSE 0 END) AS attributed_revenue
FROM user_journeys
WHERE event_name = 'purchase'
GROUP BY 1 ORDER BY attributed_revenue DESC;

WITH user_funnel AS (
  SELECT
    user_id,
    MAX(CASE WHEN event_name = 'page_view' THEN 1 ELSE 0 END) AS step1,
    MAX(CASE WHEN event_name = 'view_product' THEN 1 ELSE 0 END) AS step2,
    MAX(CASE WHEN event_name = 'add_to_cart' THEN 1 ELSE 0 END) AS step3,
    MAX(CASE WHEN event_name = 'purchase' THEN 1 ELSE 0 END) AS step4
  FROM events
  WHERE DATE(event_timestamp) >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)
  GROUP BY user_id
)
SELECT
  SUM(step1) AS step1_users,
  SUM(step2) AS step2_users,
  SUM(step3) AS step3_users,
  SUM(step4) AS step4_users,
  ROUND(SUM(step4) * 100.0 / SUM(step1), 2) AS conversion_rate
FROM user_funnel;

WITH ordered_events AS (
  SELECT user_id, event_name,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_timestamp) AS seq
  FROM events
  WHERE event_name IN ('page_view', 'view_product', 'add_to_cart', 'purchase')
),
user_step_seq AS (
  SELECT user_id,
    MIN(CASE WHEN event_name = 'page_view' THEN seq END) AS step1_seq,
    MIN(CASE WHEN event_name = 'view_product' THEN seq END) AS step2_seq,
    MIN(CASE WHEN event_name = 'add_to_cart' THEN seq END) AS step3_seq,
    MIN(CASE WHEN event_name = 'purchase' THEN seq END) AS step4_seq
  FROM ordered_events GROUP BY user_id
),
sequential_funnel AS (
  SELECT user_id,
    CASE WHEN step1_seq IS NOT NULL THEN 1 ELSE 0 END AS completed_step1,
    CASE WHEN step2_seq > step1_seq THEN 1 ELSE 0 END AS completed_step2,
    CASE WHEN step3_seq > step2_seq AND step2_seq > step1_seq THEN 1 ELSE 0 END AS completed_step3,
    CASE WHEN step4_seq > step3_seq AND step3_seq > step2_seq THEN 1 ELSE 0 END AS completed_step4
  FROM user_step_seq
)
SELECT SUM(completed_step1) AS step1, SUM(completed_step2) AS step2,
  SUM(completed_step3) AS step3, SUM(completed_step4) AS step4
FROM sequential_funnel;

WITH ad_spend_daily AS (
  SELECT DATE(spend_date) AS date, platform, campaign_name,
    SUM(spend) AS total_spend
  FROM ad_spend
  WHERE DATE(spend_date) >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
  GROUP BY 1, 2, 3
),
conversions_daily AS (
  SELECT DATE(event_timestamp) AS date,
    LOWER(REGEXP_EXTRACT(page_url, r'utm_source=([^&]+)')) AS platform,
    SUM(CASE WHEN event_name = 'purchase' THEN revenue ELSE 0 END) AS total_revenue
  FROM events WHERE event_name = 'purchase' AND revenue > 0
  GROUP BY 1, 2
)
SELECT
  a.date, a.platform, a.campaign_name,
  a.total_spend, c.total_revenue,
  SAFE_DIVIDE(c.total_revenue, a.total_spend) AS roas,
  c.total_revenue - a.total_spend AS profit
FROM ad_spend_daily a
LEFT JOIN conversions_daily c ON a.date = c.date AND a.platform = c.platform
ORDER BY a.date DESC, roas DESC NULLS LAST;

CREATE OR REPLACE TABLE analytics.marketing_daily_mart AS
WITH daily_traffic AS (
  SELECT
    DATE(event_timestamp) AS date,
    COALESCE(utm_source, 'direct') AS source,
    COUNT(DISTINCT user_id) AS unique_users,
    COUNT(DISTINCT CASE WHEN event_name = 'purchase' THEN user_id END) AS purchasers,
    SUM(CASE WHEN event_name = 'purchase' THEN revenue ELSE 0 END) AS revenue
  FROM events
  WHERE DATE(event_timestamp) >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY)
  GROUP BY 1, 2
),
daily_spend AS (
  SELECT DATE(spend_date) AS date, platform AS source, SUM(spend) AS ad_spend
  FROM ad_spend GROUP BY 1, 2
)
SELECT
  t.date, t.source, t.unique_users, t.purchasers, t.revenue,
  COALESCE(s.ad_spend, 0) AS ad_spend,
  SAFE_DIVIDE(t.purchasers, t.unique_users) * 100 AS conversion_rate,
  SAFE_DIVIDE(t.revenue, COALESCE(s.ad_spend, 0)) AS roas,
  t.revenue - COALESCE(s.ad_spend, 0) AS profit
FROM daily_traffic t
LEFT JOIN daily_spend s ON t.date = s.date AND t.source = s.source;