[SQL 코테] 코호트 리텐션 완전 정복 🔥

🚨 핵심 포인트 (시험 전 필독!)

1. 코호트 = 같은 시기에 가입한 유저 그룹
2. TIMESTAMPDIFF(단위, 시작일, 종료일) = 종료일 - 시작일 (순서 주의!)
3. JOIN이니까 "가입자 + 구매자"만 집계됨 (전체 가입자 아님!)
4. COUNT(DISTINCT user_id) 로 유저 수 세기

📝 문제: 월별 코호트 리텐션

테이블 구조

users 테이블
user_id | signup_date
--------|------------
1       | 2024-01-05
2       | 2024-01-15
3       | 2024-02-10
4       | 2024-02-20

orders 테이블
order_id | user_id | order_date
---------|---------|------------
1        | 1       | 2024-01-10
2        | 1       | 2024-02-15
3        | 2       | 2024-01-20
4        | 3       | 2024-02-15
5        | 1       | 2024-03-05

요구사항

• 가입월(cohort) 기준으로, 가입 후 N개월 차에 주문한 유저 수 구하기

✅ 정답 쿼리

SELECT 
  DATE_FORMAT(u.signup_date, '%Y-%m') AS cohort_month,
  TIMESTAMPDIFF(MONTH, u.signup_date, o.order_date) AS month_number,
  COUNT(DISTINCT u.user_id) AS retained_users
FROM users u
JOIN orders o ON u.user_id = o.user_id
GROUP BY cohort_month, month_number
ORDER BY cohort_month, month_number;

결과

cohort_month | month_number | retained_users
-------------|--------------|---------------
2024-01      | 0            | 2
2024-01      | 1            | 1
2024-01      | 2            | 1
2024-02      | 0            | 1

💡 핵심 개념 정리

1. 코호트(Cohort)가 뭐야?

코호트 = 같은 시기에 가입한 유저 그룹

• 1월 가입자 = 1월 코호트
• 2월 가입자 = 2월 코호트

왜 이렇게 묶냐?
→ "1월에 가입한 사람들이 시간이 지나도 계속 서비스 쓰냐?" 를 추적하려고!

2. 각 줄의 의미

DATE_FORMAT(u.signup_date, '%Y-%m') AS cohort_month

→ "이 유저가 언제 가입했냐?" (2024-01, 2024-02...)

TIMESTAMPDIFF(MONTH, u.signup_date, o.order_date) AS month_number

→ "가입 후 몇 개월 차에 주문했냐?"

3. TIMESTAMPDIFF 순서 주의! ⚠️

TIMESTAMPDIFF(단위, 시작일, 종료일)

→ 종료일 - 시작일 을 계산!

TIMESTAMPDIFF(MONTH, '2024-01-05', '2024-03-10')
-- 3월 - 1월 = 2개월
-- 결과: 2

DATEDIFF랑 순서가 반대!
| 함수 | 순서 | 결과 |
|------|------|------|
| DATEDIFF(A, B) | A - B | 일수 |
| TIMESTAMPDIFF(단위, A, B) | B - A | 단위별 차이 |

4. GROUP BY, ORDER BY 왜?

GROUP BY cohort_month, month_number

→ "가입월 + 몇 개월 차" 조합별로 유저 수를 세려고!

ORDER BY cohort_month, month_number

→ 보기 좋게 정렬 (1월→2월, 0개월→1개월→2개월...)

5. JOIN이니까 "가입자 + 구매자"만 집계됨!

FROM users u
JOIN orders o ON u.user_id = o.user_id

⚠️ 주문 기록이 있는 유저만 나옴!

정확한 해석:

2024-01 | 0 | 100  ← 1월에 가입하고, 1월에 주문한 유저 100명
2024-01 | 1 | 60   ← 1월에 가입하고, 2월에 주문한 유저 60명

❌ "1월 가입자 100명" 아님!
✅ "1월에 가입하고 1월에 주문한 유저 100명"

📊 실제 활용 예시

10줄 정도 데이터가 있다면:

cohort_month | month_number | retained_users
-------------|--------------|---------------
2024-01      | 0            | 100
2024-01      | 1            | 60
2024-01      | 2            | 45
2024-01      | 3            | 40
2024-02      | 0            | 120
2024-02      | 1            | 80
2024-02      | 2            | 55
2024-03      | 0            | 90
2024-03      | 1            | 70

이걸 퍼센트로 바꾸면 "리텐션율":

cohort_month | month_0 | month_1 | month_2 | month_3
-------------|---------|---------|---------|--------
2024-01      | 100%    | 60%     | 45%     | 40%
2024-02      | 100%    | 67%     | 46%     | -
2024-03      | 100%    | 78%     | -       | -

채널톡 같은 SaaS에서 이걸 왜 봐?
1. "3개월 후에도 40%가 남네? 우리 제품 괜찮은데!"
2. "2월 코호트가 1월보다 리텐션 높네? 뭐가 달라졌지?"
3. "마케팅 캠페인 후 가입자의 리텐션이 더 좋나?"

→ 제품 개선, 마케팅 효과 측정 등에 활용!

🎯 라이브 코테 체크리스트

• TIMESTAMPDIFF 순서: (단위, 시작일, 종료일) = 종료일 - 시작일
• JOIN이니까 구매 기록 있는 유저만 집계됨
• COUNT(DISTINCT user_id) 로 유저 수 세기
• GROUP BY cohort_month, month_number

채널톡 DA 인턴 코테 준비 중 정리한 내용입니다 🚀