리텐션

리텐션

특정 기간 동안 사용자가 제품 또는 서비스를 다시 사용하는 비율

(시간이 지날 수록 얼마나 많은 유저가 제품으로 다시 돌아오는지를 측정한 것)

리텐션 분석의 필요성

• 신규 사용자 확보보다 기존 사용자 유지가 더욱 비용 효율적
• 제품 개선 방향을 설정하는 중요한 지표
• 코호트 분석 및 세그먼트 분석을 통해 유지 전략 최적화 가능

N-day 리텐션

• 첫 방문 후 특정 일자에만 재방문한 유저 비율을 나타냄
• 유저가 제품을 최초로 사용한 날을 Day 0으로 설정
  Day 0은 유저가 처음으로 앱을 다운로드 받은 날일 수도 있고, 회원 가입한 날일 수도 있으며, 앱 내에서 음악을 처음 재생했거나 친구를 초대하는 등 특정한 액션을 취한 날일 수도 있음
• N-Week, N-month (제품 사용간격을 이해해야함!!)
• 예를 들어, 소셜 앱이나 게임 앱에서 사용자의 일일 습관을 형성하는 것이 중요한 경우 유용하게 사용할 수 있음

리텐션 실습

user_action Table

리텐션 로직

1. 활성 유저를 어떻게 볼것인가 정의하기 (위치 업데이트 활동이 활성했다고 정의 내림)
2. 유저별 최초 이벤트 구하기 (코호트 그룹 생성)
3. 코호트 그룹별 유저별 그 후 이벤트한 월 가져오기
4. 최초 이벤트 월, 활성 이벤트 월 차이 구하기
5. 코호트 그룹별 월 차이별 고유 유저 집계
6. 인덱스: 코호트 그룹, 컬럼: 월 차이, 값: 해당하는 유저 집계
7. 시각화

① cohort

cohort AS (
    SELECT 
        user_id,
        min(event_time) AS cohort_mth
    FROM user_action
    GROUP BY 1
)

② active_with_cohort

active_with_cohort AS (
    SELECT 
        c.cohort_mth,
        c.user_id,
        uc.event_time AS active_mth
    FROM cohort c
    LEFT JOIN user_action uc
        ON c.user_id = uc.user_id
        AND c.cohort_mth <= uc.event_time
)

③ retain

retain AS (
    SELECT 
        cohort_mth,
        active_mth,
        (DATE_PART('year', active_mth) - DATE_PART('year', cohort_mth)) * 12 +
        (DATE_PART('month', active_mth) - DATE_PART('month', cohort_mth)) AS mth_diff,
        count(DISTINCT user_id) AS retained_users
    FROM active_with_cohort
    WHERE active_mth IS NOT null
    GROUP BY 1, 2
)

④ 코호트

SELECT 
    TO_CHAR(cohort_mth, 'YYYY-MM') AS cohort_group,
    COALESCE(sum(CASE WHEN mth_diff = 0 THEN retained_users ELSE NULL end)) month0,
    COALESCE(sum(CASE WHEN mth_diff = 1 THEN retained_users ELSE NULL end)) month1,
    COALESCE(sum(CASE WHEN mth_diff = 2 THEN retained_users ELSE NULL end)) month2,
    COALESCE(sum(CASE WHEN mth_diff = 3 THEN retained_users ELSE NULL end)) month3,
    COALESCE(sum(CASE WHEN mth_diff = 4 THEN retained_users ELSE NULL end)) month4,
    COALESCE(sum(CASE WHEN mth_diff = 5 THEN retained_users ELSE NULL end)) month5,
    COALESCE(sum(CASE WHEN mth_diff = 6 THEN retained_users ELSE NULL end)) month6,
    COALESCE(sum(CASE WHEN mth_diff = 7 THEN retained_users ELSE NULL end)) month7,
    COALESCE(sum(CASE WHEN mth_diff = 8 THEN retained_users ELSE NULL end)) month8,
    COALESCE(sum(CASE WHEN mth_diff = 9 THEN retained_users ELSE NULL end)) month9,
    COALESCE(sum(CASE WHEN mth_diff = 10 THEN retained_users ELSE NULL end)) month10,
    COALESCE(sum(CASE WHEN mth_diff = 11 THEN retained_users ELSE NULL end)) month11,
    COALESCE(sum(CASE WHEN mth_diff = 12 THEN retained_users ELSE NULL end)) month12
from retain
WHERE cohort_mth >= '2022-09-01'
GROUP BY cohort_mth
ORDER BY cohort_mth

잔존율 구하기

with cohort AS (
    SELECT 
        user_id,
        min(event_time) AS cohort_mth
    FROM user_action
    GROUP BY 1
),
active_with_cohort AS (
    SELECT 
        c.cohort_mth,
        c.user_id,
        ua.event_time AS active_mth
    FROM cohort c
    LEFT JOIN user_action ua
        ON c.user_id = ua.user_id
        AND c.cohort_mth <= ua.event_time
),
cohort_sizes AS (
    SELECT 
        cohort_mth,
        count(DISTINCT user_id) AS cohort_size
    FROM cohort
    GROUP BY 1
),
min_cohort AS (
    select
        min(cohort_mth) AS earliest_cohort_month
    FROM cohort_sizes
),
acc_cohort AS (
    SELECT 
        cs.cohort_mth,
        sum(cohort_size) OVER (ORDER BY cs.cohort_mth asc) AS acc_users,
        (DATE_PART('year', cs.cohort_mth) - DATE_PART('year', mc.earliest_cohort_month)) * 12 +
        (DATE_PART('month', cs.cohort_mth) - DATE_PART('month', mc.earliest_cohort_month)) AS mth_diff
    FROM cohort_sizes cs
    CROSS JOIN min_cohort mc
),
retension AS (
    SELECT 
        cohort_mth,
        active_mth,
        (DATE_PART('year', active_mth) - DATE_PART('year', cohort_mth)) * 12 +
        (DATE_PART('month', active_mth) - DATE_PART('month', cohort_mth)) AS mth_diff,
        COUNT(DISTINCT user_id) AS retained_users
    FROM active_with_cohort
    WHERE active_mth IS NOT NULL
    GROUP BY cohort_mth, active_mth
)
SELECT 
    ac.cohort_mth,
    ac.mth_diff,
    ac.acc_users AS total_users,
    SUM(r.retained_users) AS retained_users,
    ROUND(SUM(r.retained_users) * 100.0 / ac.acc_users, 2) AS retention_percentage
FROM acc_cohort ac
JOIN retension r
    ON ac.mth_diff = r.mth_diff
    AND r.cohort_mth <= ac.cohort_mth
GROUP BY ac.cohort_mth, ac.mth_diff, ac.acc_users
ORDER BY 1, 2;

 

1. cohort – 유저별 최초 활동 시점(월) 구하기

WITH cohort AS (  
SELECT 
user_id,  
min(event_time) AS cohort\_mth  
FROM user_action  
GROUP BY 1  
),

• 각 유저가 언제 처음 활동했는지를 기준으로 cohort 월을 정의
• min(event_time)은 최초 활동 시간
• cohort_mth는 이 유저의 기준 월

 

2. active_with_cohort – 유저의 활동 로그 연결

active_with_cohort AS (
    SELECT 
        c.cohort_mth,
        c.user_id,
        ua.event_time AS active_mth
    FROM cohort c
    LEFT JOIN user_action ua
        ON c.user_id = ua.user_id
        AND c.cohort_mth <= ua.event_time
),

• cohort 유저 기준으로 활동 로그 붙이기
• left join으로 유저가 활동한 모든 기록을 가져옴
• 단, cohort 시점 이후의 활동만 (가입 이전은 제외)
• 결과적으로 각 유저가 몇 월에 활동했는지 active_mth가 붙음

 

3. cohort_sizes – cohort별 유저 수

    cohort_sizes as (
     select
         cohort_mth,
         count(distinct user_id) as cohort_size
     from cohort
     group by 1
    )

• 각 cohort 월에 가입한 유저 수 집계
• 이후 누적 합계 계산을 위한 준비 단계

 

4. min_cohort – 가장 오래된 cohort 월

    min_cohort as (
     select min(cohort_mth) as early_cohort_mth
     from cohort_sizes
    ),

• 이후 month_diff를 계산할 기준점
• 가장 오래된 cohort 월을 기준으로 몇 개월 지났는지를 계산

 

5. acc_cohort - 누적 유저 수 및 month_diff 계산

acc_cohort AS (
    SELECT
        cs.cohort_mth,
        SUM(cs.cohort_size) OVER (ORDER BY cs.cohort_mth desc) AS acc_users,
        (DATE_PART('year', cs.cohort_mth) - DATE_PART('year', mc.early_mth)) * 12 +
        (DATE_PART('month', cs.cohort_mth) - DATE_PART('month', mc.early_mth)) AS mth_diff
    FROM cohort_sizes cs
    CROSS JOIN min_cohort mc
)

• acc_users: 현재 cohort_mth 까지 누적된 유저 수
• mth_diff: 가장 처음 cohort 월로부터 몇 개월 차이인지

 

6. retension - 실제 리텐션 계산

retention AS (
	SELECT mth_diff, sum(retained_users) AS sum_retained_users
	FROM (
	    SELECT
	        cohort_mth,
	        (DATE_PART('year', active_mth) - DATE_PART('year', cohort_mth)) * 12 +
	        (DATE_PART('month', active_mth) - DATE_PART('month', cohort_mth)) AS mth_diff,
	        COUNT(DISTINCT user_id) AS retained_users
	    FROM active_with_cohort
	    WHERE active_mth IS NOT NULL
	    GROUP BY cohort_mth, mth_diff
	) sub1
	GROUP BY 1
)

• 한 유저가 자신의 cohort_mth로부터 n개월 뒤에도 활동했는가?
• mth_diff: 활동 월 - cohort 월
• count(disticnt user_id): 그 시점에 남아있는 유저 수

 

7. 누적 기준 리텐션 계산

SELECT
    ac.mth_diff,
   	r.sum_retained_users,
    ROUND(r.sum_retained_users::numeric / ac.acc_users * 100, 2) AS retention_percent
FROM acc_cohort ac
JOIN retention r
    ON ac.mth_diff = r.mth_diff
ORDER BY 1 ASC;

• n 개월 기준으로, 그 시점까지 누적된 cohort 유저 수 대비 실제 남은 유저 수
• retention_percentage: 리텐션 퍼센트 계산