[SQL/BigQuery] Day 1 / Day 7 / Day 30 리텐션

• Day 1 / Day 7 / Day 30 누적 리텐션

WITH first_event AS(
  SELECT user_id, MIN(DATE(created_at)) AS first_date
  FROM `bigquery-public-data.thelook_ecommerce.events`
  WHERE event_type IN ('home', 'product', 'cart', 'purchase') AND user_id IS NOT NULL
  GROUP BY user_id
)
, user_activity AS(
  SELECT 
    e.user_id
    , DATE_DIFF(DATE(e.created_at), f.first_date, DAY) AS days
  FROM `bigquery-public-data.thelook_ecommerce.events` e
  LEFT JOIN first_event f ON e.user_id = f.user_id 
  WHERE DATE_DIFF(DATE(e.created_at), f.first_date, DAY) BETWEEN 1 AND 30
)
SELECT
  f.first_date
  , COUNT(DISTINCT f.user_id) AS cohort_size
  , ROUND(100 * COUNT(DISTINCT CASE WHEN ua.days = 1 THEN f.user_id END)/COUNT(DISTINCT f.user_id),2) AS day1_retention
  , ROUND(100 * COUNT(DISTINCT CASE WHEN ua.days BETWEEN 1 AND 7 THEN f.user_id END)/COUNT(DISTINCT f.user_id),2) AS day7_retention
  , ROUND(100 * COUNT(DISTINCT CASE WHEN ua.days BETWEEN 1 AND 30 THEN f.user_id END)/COUNT(DISTINCT f.user_id),2) AS day30_retention
FROM first_event f
LEFT JOIN user_activity ua ON f.user_id = ua.user_id
GROUP BY f.first_date
ORDER BY f.first_date;