[SQL/BigQuery] 코호트별 첫구매까지 걸린 평균 일수

 

📌 25년 6월 이후 코호트(가입주차)별 첫구매까지 걸린 평균 일수

WITH users_cohort AS(
  SELECT 
    id AS user_id,
    DATE(created_at) AS join_date,
    DATE_TRUNC(DATE(created_at),WEEK) AS cohort_week
  FROM `bigquery-public-data.thelook_ecommerce.users`
  WHERE DATE(created_at) >= '2025-06-01'
),
first_purchase AS(
  SELECT 
    user_id,
    DATE(MIN(created_at)) AS first_purchase_date
  FROM `bigquery-public-data.thelook_ecommerce.events`
  WHERE event_type = 'purchase'
  GROUP BY user_id
)
SELECT 
  c.cohort_week,
  AVG(DATE_DIFF(p.first_purchase_date, c.join_date, DAY)) AS avg_days_to_first_purchase
FROM users_cohort c
JOIN first_purchase p on c.user_id = p.user_id
GROUP BY c.cohort_week
ORDER BY c.cohort_week;