-- event_date|event_timestamp|user_pseudo_id
-- DAY -> WEEK / timestamp -> Asia/Seoul 기준 KST
-- 첫 접속주 - 다음 주 - 그 다음 주
-- 주별로 DISTINTCT user_pseudo_id  count
-- DATE_DIFF 접속주 - 첫 접속주 
-- SAFE_DIVIDE 첫접속주 count
WITH base AS (
  SELECT
  DATE_TRUNC(event_date, WEEK) AS event_week,
  DATETIME(TIMESTAMP_MICROS(event_timestamp),'Asia/Seoul') AS kst_edate,
  user_pseudo_id
  FROM advanced.app_logs
  WHERE event_date BETWEEN "2022-08-01"AND "2022-11-03"
), fw_cnt AS (
SELECT
 event_week,
 MIN(event_week) OVER (PARTITION BY user_pseudo_id) AS first_week,
 COUNT(DISTINCT user_pseudo_id) AS user_cnt
FROM base
GROUP BY event_week, user_pseudo_id
), diff AS (
SELECT
 diff_of_week, 
 SUM(user_cnt) AS u_count
FROM(
  SELECT
  DATE_DIFF(event_week, first_week, WEEK) AS diff_of_week,
  user_cnt
  FROM fw_cnt
)
GROUP BY diff_of_week
)
SELECT
 diff_of_week, 
 u_count,
 f_cnt,
 ROUND(SAFE_DIVIDE(u_count,f_cnt),2) AS retention
FROM(
SELECT 
 *,
 FIRST_VALUE(u_count) OVER (ORDER BY u_count DESC) AS f_cnt
FROM diff
)

결과 출력

데이터 해석

1주차 리텐션 7% 수준으로 남는 유저가 많지 않으나, 1주차에 남는 유저라면 Week 2주차부터 어느 정도 안정적으로 유지하며 감소(1주차 남은 유저의 10%내외의 유저가 이탈하는 수준) 2가지 포인트를 가지고 어떻게 액션을 진행할 지 고려 필요

1. 1주차 리텐션을 끌어올린다
2. 2주차 이후 리텐션 그래프를 개선: 2주차 이후 완만하게 감소하나 14주차에는 유저가 거의 남지 않기에, 남은 유저들에 대한 서비스 케어를 통해 어떻게 그래프 감소 기울기를 더 줄일 수 있을 지를 검토

—> 1) 을 개선하는 것이 더 우선적일 것으로 생각됨 1주차 리텐션을 끌어올리면, 1주차 남는 유저 파이가 증가하여 2주차 이후 리텐션이 비슷하게 유지될 것을 고려하더라도 N주차 이상에도 남는 유저가 증가하여 서비스 수익성을 좀 더 확장하기 용이해짐 단, 어느정도 수익성이 안정화되면, 2)에 대한 개선을 진행하여, 남는 유저의 수가 계속 쌓이는 구조가 되도록 해야 장기 서비스의 기반이 될 수 있을 것으로 생각됨