[SQL/BigQuery] 그룹별 퍼널 분석

 

📌 트래픽소스별 product → cart → purchase 단계별 유저 전환율을 계산하라. (순차퍼널)

WITH user_first_event AS (
  SELECT 
    traffic_source,
    user_id,
    MIN(CASE WHEN event_type = 'product' THEN created_at END) AS first_product,
    MIN(CASE WHEN event_type = 'cart' THEN created_at END) AS first_cart,
    MIN(CASE WHEN event_type = 'purchase' THEN created_at END) AS first_purchase
  FROM `bigquery-public-data.thelook_ecommerce.events`
  WHERE event_type IN ('product', 'cart', 'purchase')
  GROUP BY traffic_source, user_id
)
SELECT 
  traffic_source,
  COUNTIF(first_product IS NOT NULL) AS product_users,
  COUNTIF(first_cart IS NOT NULL AND first_cart > first_product) AS cart_users,
  COUNTIF(first_purchase IS NOT NULL AND first_purchase > first_cart) AS purchase_users,
  ROUND(100 * COUNTIF(first_cart IS NOT NULL AND first_cart > first_product) / COUNTIF(first_product IS NOT NULL), 2) AS product_to_cart_conv,
  ROUND(100 * COUNTIF(first_purchase IS NOT NULL AND first_purchase > first_cart) / COUNTIF(first_cart IS NOT NULL), 2) AS cart_to_purchase_conv
FROM user_first_event
GROUP BY traffic_source;

 

mysql

WITH first_event AS(
  SELECT 
    traffic_source,
    user_id,
    MIN(CASE WHEN event_type = 'product' THEN DATE(created_at) END) AS first_product,
    MIN(CASE WHEN event_type = 'cart' THEN DATE(created_at) END) AS first_cart,
    MIN(CASE WHEN event_type = 'purchase' THEN DATE(created_at) END) AS first_purchase
  FROM `bigquery-public-data.thelook_ecommerce.events`
  GROUP BY traffic_source, user_id
)
SELECT
  traffic_source,
  SUM(CASE WHEN first_product IS NOT NULL THEN 1 ELSE 0 END) AS product_size,
  SUM(CASE WHEN first_cart IS NOT NULL AND first_product < first_cart THEN 1 ELSE 0 END) AS cart_size,
  SUM(CASE WHEN first_purchase IS NOT NULL AND first_cart < first_purchase THEN 1 ELSE 0 END) AS purchase_size,
  ROUND(100*SUM(CASE WHEN first_cart IS NOT NULL AND first_product < first_cart THEN 1 ELSE 0 END) 
    / NULLIF(SUM(CASE WHEN first_product IS NOT NULL THEN 1 ELSE 0 END),0),2) AS product_to_cart_conv,
  ROUND(100*SUM(CASE WHEN first_purchase IS NOT NULL AND first_cart < first_purchase THEN 1 ELSE 0 END)
    /  NULLIF(SUM(CASE WHEN first_cart IS NOT NULL AND first_product < first_cart THEN 1 ELSE 0 END),0),2) AS cart_to_purchase_conv
FROM first_event
GROUP BY traffic_source;

 

 

📌 코호트별(첫방문주 기준) product → cart → purchase 단계별 유저 전환율을 계산하라. (순차퍼널)

WITH user_cohort AS(
  SELECT 
    user_id, 
    DATE_TRUNC(MIN(created_at),WEEK) as cohort_week
  FROM `bigquery-public-data.thelook_ecommerce.events`
  GROUP BY user_id
),
user_first_event AS (
  SELECT 
    c.cohort_week,
    e.user_id,
    MIN(CASE WHEN e.event_type = 'product' THEN e.created_at END) AS first_product,
    MIN(CASE WHEN e.event_type = 'cart' THEN e.created_at END) AS first_cart,
    MIN(CASE WHEN e.event_type = 'purchase' THEN e.created_at END) AS first_purchase
  FROM `bigquery-public-data.thelook_ecommerce.events` e
    JOIN user_cohort c USING(user_id)
  GROUP BY c.cohort_week, e.user_id
)
SELECT 
  cohort_week,
  COUNT(*) AS cohort_size,
  COUNTIF(first_product IS NOT NULL) AS product_users,
  COUNTIF(first_cart IS NOT NULL AND first_cart > first_product) AS cart_users,
  COUNTIF(first_purchase IS NOT NULL AND first_purchase > first_cart) AS purchase_users,
  ROUND(100 * COUNTIF(first_cart IS NOT NULL AND first_cart > first_product) / COUNTIF(first_product IS NOT NULL), 2) AS product_to_cart_conv,
  ROUND(100 * COUNTIF(first_purchase IS NOT NULL AND first_purchase > first_cart) / COUNTIF(first_cart IS NOT NULL), 2) AS cart_to_purchase_conv
FROM user_first_event
GROUP BY cohort_week
ORDER BY cohort_week;

mysql

WITH user_cohort AS(
  SELECT user_id, MIN(WEEKOFYEAR(DATE(created_at))) AS cohort_week
  FROM `bigquery-public-data.thelook_ecommerce.events`
  GROUP BY user_id
),
first_event AS(
  SELECT 
    c.cohort_week, 
    e.user_id,
    MIN(CASE WHEN e.event_type = 'product' THEN DATE(e.created_at) END) AS first_product,
    MIN(CASE WHEN e.event_type = 'cart' THEN DATE(e.created_at) END) AS first_cart,
    MIN(CASE WHEN e.event_type = 'purchase' THEN DATE(e.created_at) END) AS first_purchase  
  FROM `bigquery-public-data.thelook_ecommerce.events` e
  LEFT JOIN user_cohort c ON e.user_id = c.user_id
  GROUP BY c.cohort_week, e.user_id
)
SELECT 
  cohort_week,
  SUM(CASE WHEN first_product IS NOT NULL THEN 1 ELSE 0 END) AS cohort_size,
  ROUND(100*SUM(CASE WHEN first_cart IS NOT NULL AND first_product < first_cart THEN 1 ELSE 0 END)
    / IFNULL(SUM(CASE WHEN first_product IS NOT NULL THEN 1 ELSE 0 END),0),2) AS product_to_cart_conv,
  ROUND(100*SUM(CASE WHEN first_purchase IS NOT NULL AND first_cart < first_purchase THEN 1 ELSE 0 END)
    / IFNULL(SUM(CASE WHEN first_cart IS NOT NULL THEN 1 ELSE 0 END),0),2) AS cart_to_purchase_conv  
FROM first_event
GROUP BY cohort_week

 

 

* 첫번째 이벤트 기준 집계 이유

1. 단순화 & 중복 제거

2. 시간 순서 보장

3. 실제 비즈니스 KPI와 일치