-- 기간: 2022-08-01 ~ 2022-11-30
-- event_date KST 기준인 부분 확인한 것으로 가정
-- 우선 event_date -> Week 변환 event_week
-- event_name = click_payment  first_week user_pseudo_id 기준
-- diff week
WITH base AS (
SELECT
 event_date,
 event_name,
 user_pseudo_id
FROM advanced.app_logs
WHERE event_date BETWEEN "2022-08-01" AND "2022-11-30"
      AND event_name = "click_payment"
), few_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) AS first_week,
  DATE_TRUNC(event_date, WEEK) AS event_week
FROM base
)
), diff_cnt AS (
SELECT
 diff_of_week,
 COUNT(DISTINCT user_pseudo_id) AS user_cnt
FROM few_diff
GROUP BY diff_of_week
ORDER BY diff_of_week
)
SELECT
 *,
 ROUND(SAFE_DIVIDE(user_cnt,f_cnt),2) AS week_retention
FROM(
SELECT
 *,
 FIRST_VALUE(user_cnt) OVER (ORDER BY diff_of_week) AS f_cnt
FROM diff_cnt
)

결과 출력

데이터 해석

첫주 click_payment 클릭한 유저가 7664명인데, 2주차에 클릭한 유저는 89명으로 0.01% 대로 급락 다만, 2주차에 남은 유저들은 8주차까지는 전주 대비 90%이상의 높은 재접속율을 보임

→ 2주차 리텐션 개선 액션을 추진하여 2주차에 남는 유저 모수를 늘리는 것이 우선 과제로 생각됨(지금과 유사한 형태를 보인다면 8주차까지 높은 재접속율이 예상됨)