[빠짝스터디 3주차 과제] 리텐션 과제

1. Weekly Retention 구하기

# Weekly Retention을 구하는 쿼리

WITH base AS (
  SELECT
    DISTINCT
      user_id,
      user_pseudo_id,
      event_name,
      DATE(DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul')) AS event_date,
      DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul') AS event_datetime
  FROM advanced.app_logs
  WHERE
    event_date BETWEEN "2022-08-01" AND "2022-11-03"
# 유저의 event_week, firts_week, diff_week 구하기
), first_week_and_diff AS (
  SELECT
    *,
    -- DATE_DIFF(event_date, first_date, DAY) AS diff_of_day
    DATE_DIFF(event_week, first_week, WEEK) AS diff_of_week
  FROM (
    SELECT
      # 일자별로 중복 제거
      DISTINCT 
        user_pseudo_id,
        -- DATE_TRUNC
        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
  )
# 유저별 event_week, first_week, diff_week 수 
), user_counts AS (
  SELECT
  diff_of_week,
  COUNT(DISTINCT user_pseudo_id) AS user_cnt
  FROM first_week_and_diff
  GROUP BY diff_of_week
)


# Weekly retention의 수와 비율
SELECT
  *,
  ROUND(SAFE_DIVIDE(user_cnt, first_week_user_cnt), 2) AS retention_rate
FROM (
  SELECT
    diff_of_week,
    user_cnt,
    FIRST_VALUE(user_cnt) OVER(ORDER BY diff_of_week ASC) AS first_week_user_cnt
  FROM user_counts
)

2. Retention User를 New +Current +Resurrected + Dormant User로 나누는 쿼리를 작성하기

WITH base AS ( 
    SELECT
    DISTINCT
      user_id,
      user_pseudo_id,
      event_name,
      DATETIME(TIMESTAMP_MICROS(event_timestamp),'Asia/Seoul') AS event_datetime,
      DATE(DATETIME(TIMESTAMP_MICROS(event_timestamp),'Asia/Seoul')) AS event_date,
    FROM advanced.app_logs)
, weekly_user_active AS (
    SELECT 
      user_pseudo_id,
      DATE_TRUNC(event_date, WEEK) AS event_week,
      MIN(DATE_TRUNC(event_date, WEEK)) OVER (PARTITION BY user_pseudo_id) AS first_active_week,
      LAG(DATE_TRUNC(event_date, WEEK)) OVER (PARTITION BY user_pseudo_id ORDER BY DATE_TRUNC(event_date, WEEK)) AS pre_active_week
      FROM base
)

, user_group AS (
  SELECT
    user_pseudo_id,
    event_week,
    DATE_DIFF(event_week, pre_active_week, WEEK(MONDAY)) AS diff_prior_week, 
    DATE_DIFF(event_week, first_active_week, WEEK(MONDAY)) AS diff_first_week,
    CASE 
      WHEN event_week = first_active_week THEN 'NEW' # 처음 사용한 유저
      WHEN DATE_DIFF(event_week, pre_active_week, WEEK) = 1 THEN 'Current' # 1주 안에 사용한 유저
      WHEN DATE_DIFF(event_week, pre_active_week, WEEK) > 1 THEN 'Resurrected' # 
      ELSE 'Dormant '
    END AS user_seg # 유저분류값
    FROM weekly_user_active
)

SELECT  
  event_week,
  user_seg,
  COUNT(DISTINCT user_pseudo_id) AS user_cnt,
FROM user_group
GROUP BY ALL
ORDER BY 1 ; 

3. 어떤 사람들이 리텐션이 높은지 찾아보기

    

   

• NEW(신규유저) : 신규 유저 10월 이후 하락

• Current (활성화 유저) : 12월까지 상승세를 보이다가 1월에 다시 하락

• Resurrected (복귀 유저) : 지속 상승

• Dormant User (휴면 유저) : 지속 상승

???: 왜 휴면유저와 복귀유저의 사용 비율이 비슷하게 나오는걸까..? 그래도 그나마 복귀유저가 리텐션이 더 높음

4. Core Event를 "click_payment"라고 설정하고 Weekly Retention을 구하기

WITH base AS (
  SELECT
    DISTINCT
      user_id,
      user_pseudo_id,
      event_name,
      DATE(DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul')) AS event_date,
      DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul') AS event_datetime
  FROM advanced.app_logs
  WHERE
    event_date BETWEEN "2022-08-01" AND "2022-11-03"
    AND event_name = 'click_payment'  -- Core Event 필터링
), 

first_week_and_diff AS (
  SELECT
    *,
    DATE_DIFF(event_week, first_week, WEEK) AS diff_of_week
  FROM (
    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
  )
),

user_counts AS (
  SELECT
    diff_of_week,
    COUNT(DISTINCT user_pseudo_id) AS user_cnt
  FROM first_week_and_diff
  GROUP BY diff_of_week
)

SELECT
  diff_of_week AS week,
  user_cnt,
  FIRST_VALUE(user_cnt) OVER(ORDER BY diff_of_week ASC) AS first_week_user_cnt,
  ROUND(SAFE_DIVIDE(user_cnt, FIRST_VALUE(user_cnt) OVER(ORDER BY diff_of_week ASC)) * 100, 2) AS retention_rate
FROM user_counts
ORDER BY diff_of_week