가입 코호트별 월간 리텐션

문제

쉽게 설명하면

3월에 입학한 애들이 1달 뒤·2달 뒤에 몇 명 남았나. 입학한 달마다 따로 추적한다. 반이 다르면 섞지 않는다.

가로가 '가입 후 몇 달째', 세로가 '언제 가입한 묶음'. 분모는 그 줄의 전체 인원이고 끝까지 안 바뀐다.

이렇게 푼다

1. 사람마다 가입한 달을 붙인다. 이게 그 사람이 속한 '묶음' 이다.
2. 접속 기록마다 가입 후 몇 달째인가를 계산한다 (접속한 달 − 가입한 달).
3. 가입한 달 × 경과 개월로 묶어서 사람 수를 센다.
4. 그 수를 그 묶음의 전체 인원으로 나눈다.

이것만 피하면 된다

자세히

묻는 것 · 코호트 분석의 뼈대. 분모 고정 + 경과 개월.

풀이 순서

1. 코호트 = 가입 월. 유저마다 하나.
2. 활동 = app_open 이 있는 월. 유저×월 중복 제거.
3. TIMESTAMPDIFF(MONTH, 코호트, 활동월) 로 경과 개월 M+1, M+2…
4. 분모는 코호트 전체 인원. 활동 안 한 사람도 포함.

정답 (MySQL)

WITH base AS (
  SELECT user_id, DATE_FORMAT(signup_date, '%Y-%m-01') AS cohort FROM users
),
act AS (
  SELECT DISTINCT user_id, DATE_FORMAT(event_date, '%Y-%m-01') AS m
  FROM events WHERE event_name = 'app_open'
)
SELECT DATE_FORMAT(b.cohort, '%Y-%m') AS cohort_month,
       COUNT(DISTINCT b.user_id) AS cohort_size,
       ROUND(100.0 * COUNT(DISTINCT CASE WHEN TIMESTAMPDIFF(MONTH, b.cohort, a.m)=1
             THEN b.user_id END) / COUNT(DISTINCT b.user_id), 1) AS m1_retention,
       ROUND(100.0 * COUNT(DISTINCT CASE WHEN TIMESTAMPDIFF(MONTH, b.cohort, a.m)=2
             THEN b.user_id END) / COUNT(DISTINCT b.user_id), 1) AS m2_retention,
       ROUND(100.0 * COUNT(DISTINCT CASE WHEN TIMESTAMPDIFF(MONTH, b.cohort, a.m)=3
             THEN b.user_id END) / COUNT(DISTINCT b.user_id), 1) AS m3_retention
FROM base b
LEFT JOIN act a ON a.user_id = b.user_id
GROUP BY cohort_month
ORDER BY cohort_month;

함정

이렇게 변형된다

주 단위면 TIMESTAMPDIFF(WEEK, ...). “결제 리텐션”이면 활동 테이블을 payments 로 바꾼다.