B3C 플랫폼 분석 리텐션 개념

리텐션이란? 특정 기간에 가입한 고객들이 이후에도 계속 서비스를 이용하는 비율

 

1. 기본 리텐션 (N일 후 재방문율)

예시: 1월 가입자들의 1개월 후 리텐션

 

sql

-- 1월 신규 가입자 중 2월에도 활동한 비율
with jan_new_users as (
    select customer_id
    from users 
    where date_format(join_date, '%Y-%m') = '2025-01'
),
feb_active_users as (
    select distinct customer_id
    from user_activity
    where date_format(activity_date, '%Y-%m') = '2025-02'
)
select 
    count(distinct j.customer_id) as jan_new_users,
    count(distinct f.customer_id) as feb_retained_users,
    count(distinct f.customer_id) * 100.0 / count(distinct j.customer_id) as retention_rate
from jan_new_users j
left join feb_active_users f on j.customer_id = f.customer_id;

 

2. 코호트 리텐션 (월별 추적)

각 가입월별로 이후 월들의 리텐션 추적

 

sql

with first_activity as (
    select 
        customer_id,
        date_format(min(activity_date), '%Y-%m') as cohort_month
    from user_activity
    group by customer_id
),
monthly_activity as (
    select 
        customer_id,
        date_format(activity_date, '%Y-%m') as activity_month
    from user_activity
)
select 
    f.cohort_month,
    m.activity_month,
    timestampdiff(month, 
                  str_to_date(f.cohort_month, '%Y-%m'), 
                  str_to_date(m.activity_month, '%Y-%m')) as month_number,
    count(distinct f.customer_id) as cohort_size,
    count(distinct m.customer_id) as retained_users,
    count(distinct m.customer_id) * 100.0 / count(distinct f.customer_id) as retention_rate
from first_activity f
left join monthly_activity m on f.customer_id = m.customer_id
group by f.cohort_month, m.activity_month
order by f.cohort_month, month_number;

결과 예시:

 

cohort_month | activity_month | month_number | retention_rate
2025-01     | 2025-01       | 0            | 100.0%
2025-01     | 2025-02       | 1            | 65.5%
2025-01     | 2025-03       | 2            | 45.2%