연습과제

Weekly Retention을 구하는 쿼리를 바닥부터 스스로 작성해보세요.

SQL

WITH base AS(
  SELECT
    DATE(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul') AS event_date,
    DATE_TRUNC(DATE(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul'), WEEK(MONDAY)) AS event_week,
    event_name,
    user_pseudo_id
  FROM advanced.app_logs
), week_diff_data AS(
  #first_week, weeks_after_first_week 추출
  SELECT
    *,
    DATE_DIFF(event_week, first_week, WEEK(MONDAY)) AS weeks_after_first_week
  FROM(
    SELECT
      *,
      MIN(event_week) OVER(PARTITION BY user_pseudo_id) AS first_week
    FROM base
  )
), visit_cnt_data AS(
  #active_users, cohort_users 추출
  SELECT
    *,
    FIRST_VALUE(active_users) OVER(PARTITION BY first_week ORDER BY weeks_after_first_week) AS cohort_users
  FROM(
    SELECT
      first_week,
      weeks_after_first_week,
      COUNT(DISTINCT user_pseudo_id) AS active_users,
    FROM week_diff_data
    GROUP BY ALL
  )
)

#retention_rate 추출
SELECT
  *,
  ROUND(SAFE_DIVIDE(active_users, cohort_users), 2) AS retention_rate
FROM visit_cnt_data
ORDER BY first_week, weeks_after_first_week

리텐션 분석 결과

(방문 주차 기준 / 붉은색으로 갈수록 낮은 값, 초록색으로 갈수록 높은 값을 의미)

• 2022-08-01 ~ 2022-10-03: 전반적으로 붉은색을 띠며, 최저 3% 정도로 낮은 경향을 보임.
• 2022-10-10 ~ 2022-12-12: 전반적으로 초록색으로 전환되는 양상을 보이며, 해당 상승세는 2022-12-12에 최대 14%, 최저 11%로 정점을 찍음. ←- 해당 방문 주차 사람들이 리텐션이 높음!
• 2022-12-19 ~ 2023-01-16: 전반적으로 다시 붉은색으로 전환되는 양상을 보이며, 해당 하락세는 2023-01-16에 최대 8%, 최저 5%로 바닥을 찍음.
• 총평: 분석 시작 주차(2022-08-01)부터 저조한 리텐션을 기록하다가, 2022-10-10경부터 리텐션 상승을 이끌어낸 어떤 Action(또는 Event)이 있었을 것으로 추정됨. 다만 상승세가 약 두 달 뒤인 2022-12-12에 정점을 찍은 뒤 꺾인 것으로 보아, 해당 Action의 유효기간 또는 Event의 마감일이 종료됨과 동시에 두 달 전 수준의 리텐션으로 돌아가는 경향을 보이는 것으로 추정됨. 또한 전반적으로 리텐션 비율이 높지 않은 점을 고려하면, 일회성 이벤트가 아닌 근본적인 리텐션 상승 방안을 강구해야 할 것으로 판단됨.

https://docs.google.com/spreadsheets/d/1YXni_sBkk79UPQFc3LL7vcHBk3CbN7mUx3CQvGJLMjU/edit?usp=sharing