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

왜 직접 만들어야 할까

Google Analytics 360, Amplitude, Mixpanel... 엔터프라이즈 분석 도구들의 연간 비용은 만만치 않습니다.

스타트업에서 이 비용을 정당화하기란 쉽지 않습니다. 하지만 raw 이벤트 로그만 있으면 SQL로 동일한 분석이 가능합니다. 이 도구들이 제공하는 핵심 기능의 90%는 SQL로 재현할 수 있습니다.

1. 세션(Session) — 30분 타임아웃의 비밀

세션이 정확히 뭔가요?

GA가 보여주는 "세션"은 단순한 "방문"이 아닙니다:

새 세션이 시작되는 조건:

1. 30분 이상 비활동 후 재활동
2. 자정(UTC) 경과
3. 캠페인 소스 변경 (UTM 파라미터 변경)
4. 첫 방문

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;

2. 어트리뷰션 — 어느 채널이 기여했나?

어트리뷰션 모델 비교

고객 여정: Google Ads -> Facebook -> Email -> 구매($100)

First Touch 구현

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;

Last Touch 구현

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;

3. 퍼널 분석 — 단계별 이탈률

비순차 퍼널 구현

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;

4. ROAS — 광고비 대비 수익

ROAS란

ROAS = 광고 수익 / 광고 비용

예: 광고비 $1,000, 매출 $5,000 -> ROAS = 5.0 (500%)

업종별 벤치마크:

• E-commerce: 3.0~4.0x
• SaaS: 5.0~7.0x
• B2B: 10.0x+

기본 ROAS 계산

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;

5. 마케팅 대시보드 마트

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;

마무리: SQL로 마케팅 분석 완성하기

세션, 어트리뷰션, 퍼널, ROAS — 유료 분석 도구 없이도 SQL로 100% 구현 가능합니다.

SQL 마케팅 분석의 장점:

• 데이터 완전 통제: 샘플링 없이 100% 데이터 분석
• 무제한 커스터마이징: GA에서 안 되는 분석도 가능
• 검증 투명성: 쿼리 보여주면 로직 검증 완료
• 비용 효율: BigQuery 월 $100 미만

"분석 도구가 비싸서..." 라는 변명은 이제 통하지 않습니다.