인프런 커뮤니티 질문&답변

리텐션 연습 문제 푸는 중에 어려운 점이 있어 질문 남깁니다!

• click_payment 이벤트 기준으로 cohort 리텐션 분석 쿼리 올바르게 작성했을까요? 유저수의 합계가 0주차일때 안 맞는 거 같습니다...

• 코호트 분석 시각화 했을 때 저렇게 비어 보이는 경우에는 어떻게 해석하면 좋을까?

  

   

WITH base AS (
  SELECT
    DISTINCT
      user_id,
      event_name,
      TIMESTAMP_MICROS(event_timestamp) AS event_datetime,
      DATE(TIMESTAMP_MICROS(event_timestamp)) AS event_date,
      user_pseudo_id
  FROM `advanced.app_logs`
  WHERE 
    event_date > '2022-08-01'
    AND 
    event_name = 'click_payment'
)
,first_week_data AS (
  SELECT
  DISTINCT 
    user_pseudo_id,
    DATE_TRUNC(MIN(event_date) OVER(PARTITION BY user_pseudo_id),WEEK(MONDAY)) AS first_week,
    DATE_TRUNC(event_date,WEEK(MONDAY)) AS event_week
  FROM base
)
,weeks_after_first_week_data AS (
  SELECT
    *,
    DATE_DIFF(event_week,first_week,WEEK) AS weeks_after_first_week
  FROM first_week_data
)
,active_user_count AS (
  SELECT
  first_week,
  weeks_after_first_week,
  COUNT(DISTINCT user_pseudo_id) AS active_users
  FROM weeks_after_first_week_data 
  GROUP BY ALL
)
,cohort_user_count AS (
SELECT 
 *,
 FIRST_VALUE(active_users) OVER(PARTITION BY first_week ORDER BY weeks_after_first_week) AS cohort_users
FROM active_user_count
)
SELECT 
 *,
 ROUND(SAFE_DIVIDE(active_users,cohort_users),2) AS rentention_rate
FROM cohort_user_count