[SQL/BigQuery] 코호트별 n주차 리텐션율

• 가입주차(cohort_week)별로, 가입 이후 n주차(예: 1~8주) 동안 사이트에 다시 방문한 유저의 비율

WITH users_cohort AS(
  SELECT id AS user_id, DATE_TRUNC(DATE(created_at),WEEK) AS cohort_week
  FROM `bigquery-public-data.thelook_ecommerce.users`
),
retention AS(
  SELECT
    c.cohort_week,
    e.user_id, 
    DATE_DIFF(DATE_TRUNC(DATE(e.created_at),WEEK),cohort_week,WEEK) AS week_number
  FROM `bigquery-public-data.thelook_ecommerce.events` e
  LEFT JOIN users_cohort c ON e.user_id = c.user_id
  WHERE e.event_type IN ('home', 'product', 'cart', 'purchase')
    AND DATE_DIFF(DATE_TRUNC(DATE(e.created_at),WEEK),cohort_week,WEEK) BETWEEN 0 AND 8
),
base AS (
  SELECT
    cohort_week,
    week_number,
    COUNT(DISTINCT user_id) AS user_size
  FROM retention
  GROUP BY cohort_week, week_number
),
cohort_size AS (
  SELECT
    cohort_week,
    COUNT(DISTINCT user_id) AS cohort_size
  FROM users_cohort
  GROUP BY cohort_week
)
SELECT 
  b.cohort_week, 
  b.week_number, 
  c.cohort_size,
  b.user_size,
  ROUND(100*b.user_size/c.cohort_size,2) AS retention_rate
FROM base b
LEFT JOIN cohort_size c ON b.cohort_week = c.cohort_week
ORDER BY b.cohort_week, b.week_number