[2] SQL로 리텐션 분석

by DoctorMarvin

3 days ago

이번 시간에는 데이터 기반 의사결정의 핵심 중 하나인 리텐션 분석에 대해 배워보겠습니다.

사용자 분석의 대표 프레임워크인 AARRR 모델을 시작으로 코호트 분석과 클래식/롤링 리텐션 분석을 SQL로 직접 구현해보겠습니다.

🚀 1. AARRR 프레임워크란?

AARRR은 스타트업 지표 관리의 고전이자 실무에서도 자주 쓰이는 사용자 퍼널입니다.

Acquisition (획득)
Activation (활성화)
Retention (유지)
Revenue (수익)
Referral (추천)

이번 주는 이 중에서도 가장 중요한 요소 중 하나인 Retention(유지율)에 집중했어요. 유저가 서비스를 처음 이용한 뒤, 얼마나 다시 돌아오는지 분석하는 것이 핵심입니다.

📅 2. 코호트 분석(Cohort Analysis)

코호트 분석은 특정 시점에 유입된 유저 집단의 행동을 추적해 비교하는 방법이에요. 예를 들어, 가입 월 기준으로 유저를 나눠 1개월, 2개월 후 행동을 분석할 수 있습니다.

SELECT
  DATE_TRUNC('month', signup_date) AS cohort_month,
  DATE_TRUNC('month', activity_date) AS activity_month,
  COUNT(DISTINCT user_id) AS active_users
FROM user_log
GROUP BY 1, 2
ORDER BY 1, 2;

→ 이 쿼리는 각 가입 월(cohort) 기준으로 유저들이 이후 어떤 월에 활동했는지 집계합니다.

📈 3. 리텐션 분석

📌 3-1. 클래식 리텐션

클래식 리텐션은 특정 시점(예: 가입일 기준 7일 후)에 유저가 다시 활동했는지를 보는 방식입니다.

✔ 우리 서비스에 맞는 리텐션 찾기

뉴스 앱은 하루 뒤 리텐션, 커머스 앱은 일주일, 생산성 앱은 한 달 리텐션이 중요할 수 있어요. 서비스 특성에 따라 ‘언제 돌아왔는지’를 정의해야 합니다.

✔ 리텐션 기준 잡기

WITH first_visit AS (
  SELECT user_id, MIN(event_date) AS signup_date
  FROM user_log
  GROUP BY user_id
),
next_visit AS (
  SELECT l.user_id, f.signup_date, l.event_date,
         DATE_PART('day', l.event_date - f.signup_date) AS days_since_signup
  FROM user_log l
  JOIN first_visit f ON l.user_id = f.user_id
  WHERE DATE_PART('day', l.event_date - f.signup_date) BETWEEN 1 AND 7
)
SELECT
  COUNT(DISTINCT user_id) * 1.0 /
  (SELECT COUNT(*) FROM first_visit) AS day7_retention
FROM next_visit;

→ 가입 후 1~7일 내 재방문한 유저 비율 = 7일 리텐션입니다.

🔁 3-2. 롤링 리텐션

롤링 리텐션은 특정 기간 내에 한 번이라도 돌아왔으면 리텐션으로 간주하는 방식입니다. 예: 7일 안에 한 번이라도 활동했으면 OK.

WITH first_visit AS (
  SELECT user_id, MIN(event_date) AS signup_date
  FROM user_log
  GROUP BY user_id
),
return_users AS (
  SELECT DISTINCT l.user_id
  FROM user_log l
  JOIN first_visit f ON l.user_id = f.user_id
  WHERE l.event_date > f.signup_date
    AND l.event_date <= f.signup_date + INTERVAL '7 days'
)
SELECT
  COUNT(*) * 1.0 /
  (SELECT COUNT(*) FROM first_visit) AS rolling_day7_retention;