[인프런 스터디 빅쿼리 3주차] SQL 리텐션 분석 (Retention analysis) : 서비스의 성과와 고객 경험을 극대화하기 위한 전략

인프런에서 강의도 듣고 스터디 신청시 30% 할인 쿠폰을 통해 강의도 수강하고 ~ 스터디도 참여할 수 있습니다. 카일스쿨님의 인프런 빅쿼리 SQL 스터디 3주차에서 학습한 SQL의 리텐션 분석 내용을 활용해 재구성했습니다.  활용 코드는 모두 직접 작성했습니다. 

 

 

 

 

혹시 리텐션, 들어보시거나 아래와 같은 그림들 보신적 있으실까요? 

 

 

 

 

애매하게 자리잡았던 리텐션 개념들 + 기준 정하는 법 + 활용 코드까지 뿌시러 가보겠습니다! 

 

 

 

 

🔑 중요 키워드: 리텐션 분석, 코호트, Classic 리텐션, Range 리텐션, Rolling 리텐션 

📄 1. 리텐션 개념 정의 

 

리텐션 (retention) 이란?

 

리텐션은 특정 기간 동안 고객이 제품이나 서비스를 지속적으로 사용하는지를 측정하는 중요한 지표로, 보통 사용자가 처음으로 서비스를 접한 후에도 얼마나 오랜 기간 서비스를 계속 이용하는지를 나타냅니다. 리텐션 분석은 고객이 제품에 다시 돌아오는 빈도와 주기를 추적함으로써, 사용자 유지율을 파악하고 이탈 원인을 분석하는 데 중점을 둡니다. 예를 들어, 신규 고객의 첫 접속일 이후, 일정 기간 내에 다시 제품을 사용하는지를 확인하거나, 기존 고객이 주기적으로 얼마나 제품을 사용하는지 등을 통해 사용자 경험과 제품의 가치를 평가할 수 있습니다.

 

이미지 출처 : churnkey

 

 

 

 

이미지 출처 : digital analytics blog

 

📌 리텐션을 구분하는 기준

리텐션은 다양한 방식으로 정의될 수 있으며, 상황과 제품에 따라 분석 목표가 달라질 수 있습니다.

 

주로 다음과 같은 기준에 따라 리텐션을 정의할 수 있습니다.

 

 

1. 접속 빈도에 따른 리텐션
   • 일일 리텐션(Daily Retention): 사용자가 매일 제품을 사용하는지 확인하는 방식으로, 일일 사용 주기가 중요한 서비스에 적합합니다. 예: 뉴스 앱, 메신저 앱 등.
   • 주간 리텐션(Weekly Retention): 일주일 단위로 사용자가 다시 접속하는지 보는 방식으로, 주간 사용 패턴이 중요한 제품에 적합합니다. 예: 쇼핑 앱, 금융 서비스.
   • 월간 리텐션(Monthly Retention): 월 단위로 사용자 유지율을 측정하며, 장기적인 사용 패턴을 관찰할 수 있습니다. 예: 교육 플랫폼, 스트리밍 서비스
     
     
     
2. 핵심 이벤트에 따른 리텐션
   제품마다 핵심 기능이나 이벤트가 다르기 때문에, 단순 접속만을 기준으로 하지 않고 사용자가 특정 핵심 이벤트를 다시 수행했는지를 기준으로 리텐션을 정의할 수 있습니다.
   예를 들어, 음식 배달 앱에서는 ‘음식 주문’이 핵심 이벤트가 되고, 소셜 미디어 앱에서는 ‘피드 보기’ 등이 주요 리텐션 이벤트로 간주될 수 있습니다.
   
   
   
3. 고객의 유형에 따른 리텐션
   고객의 유형을 신규 고객, 기존 고객, 복귀 고객, 이탈 고객으로 구분해 리텐션을 분석할 수 있습니다. 각각의 그룹은 다른 특성과 행동 패턴을 가지므로 세분화된 리텐션 분석이 필요합니다.
   • 신규 고객(New User): 처음 서비스를 사용하는 고객의 리텐션을 관찰하여 초기 이탈 원인을 파악할 수 있습니다.
   • 기존 고객(Current User): 제품을 꾸준히 사용하는 기존 고객의 리텐션을 분석해 만족도를 확인하고 개선 기회를 찾습니다.
   • 복귀 고객(Resurrected User): 한동안 사용을 중단했다가 다시 돌아온 고객의 리텐션을 분석해, 복귀 후 이탈 방지를 위한 전략을 설정합니다.
   • 이탈 고객(Dormant User): 일정 기간 사용을 멈춘 고객을 파악하여 복귀 가능성을 높이는 방안을 모색합니다.

 

 

Q. 그렇다면, 이 많은 기준들 중 리텐션 기준을 정할때 어떤걸 고려해야 할까요? 

 

 

 

📝  리텐션을 정의할 때 고려해야 할 요소 

 

1. 제품의 사용 주기
   제품의 사용 주기는 리텐션 정의에 큰 영향을 미칩니다. 예를 들어, 금융 서비스는 사용 빈도가 일별보다는 주별, 월별일 가능성이 크며, 교육 플랫폼의 경우 수강 주기에 맞춰 월간 리텐션을 측정하는 것이 더 유용할 수 있습니다.
   
   
   
2. 고객의 문제 해결 빈도
   고객이 겪는 문제의 빈도가 리텐션 정의에 중요한 역할을 합니다. 만약 고객이 자주 겪는 문제를 해결하는 제품이라면 일일 리텐션을 보는 것이 좋지만, 상대적으로 빈도가 낮은 문제라면 주간, 월간 리텐션을 확인하는 것이 더 적절합니다.
   
   
   
3. PMF(Product Market Fit)
   리텐션 분석의 궁극적인 목표 중 하나는 제품이 시장에서 자리 잡았는지, 즉 PMF를 달성했는지를 확인하는 것입니다. 리텐션 커브가 평평해지는 지점이 존재한다면 이는 고객이 제품의 핵심 가치를 꾸준히 경험하고 있다는 뜻이므로 PMF를 달성했다고 볼 수 있습니다.

 

 

 

💻 리텐션 측정 방식 종류 : Classic, Range, Rolling

리텐션 분석에서 사용하는 주요 지표는 Classic 리텐션, Range 리텐션, Rolling 리텐션으로 나뉩니다.

 

 

 

리텐션 방식	주요 특징	주로 활용되는 시점	예시 활용
Classic 리텐션	특정 날짜에 재방문 여부 측정	신규 유저의 초기 리텐션 분석	마케팅 캠페인
Range 리텐션	특정 기간 내 재방문 여부 측정	주간/월간 등 주기적인 리텐션 분석	구독 서비스
Rolling 리텐션	기간 내 어느 날이라도 재방문 시 포함	장기 리텐션 확인이나 누적 사용 분석	교육 앱

 

 

1. Classic 리텐션(N DAY 리텐션) :

특정 시점(예: Day 0) 이후, 정해진 날에 다시 참여했는지를 보는 방식입니다.

이 방식은 특정 날짜 이후 일정 기간 동안 사용자가 돌아오는 비율을 측정하는 데 유용합니다. 특히 초기 이탈률을 분석하거나 마케팅 캠페인의 효과를 검증할 때 주로 활용됩니다.

 

 

예시: 한 쇼핑 앱에서 신규 고객이 첫 방문(예: Day 0) 이후 Day 1(1일 차), Day 7(7일 차), Day 30(30일 차)에 다시 방문했는지를 체크합니다. 만약 Day 0에 100명이 앱을 다운로드하고, Day 1에 다시 접속한 사용자가 40명이라면 **Day 1 리텐션율은 40%**로 계산됩니다.

 

 

활용 시점: Classic 리텐션은 사용자가 특정 이벤트 이후 며칠 이내에 다시 돌아오는지 파악하는 데 효과적이며, 주로 신규 유저의 초기 리텐션 분석에 적합합니다. 예를 들어, 신규 사용자의 Day 1 리텐션이 낮다면 초기 온보딩 과정에서의 문제를 의심할 수 있습니다.

 

 

 

2. Range 리텐션(N일 간격 리텐션) :

N일 간격으로 특정 기간 내에 고객이 재방문했는지를 평가하는 방식으로, 특히 주간 또는 월간 리텐션을 확인할 때 유용합니다.
Range 리텐션은 사용자가 특정 기간 내에 한 번이라도 방문했는지를 확인해, 사용 주기가 길거나 불규칙한 서비스의 리텐션을 분석하는 데 적합합니다.

 

 

예시: 구독 서비스 앱에서 고객이 처음 사용한 Day 0 이후 Week 1(1주차), Week 2(2주차) 동안 최소 1회 이상 다시 접속했는지를 측정합니다. 예를 들어, Day 0에 200명이 앱을 처음 사용했고, 첫 번째 주 안에 다시 접속한 사용자가 60명이었다면 **Week 1 리텐션율은 30%**가 됩니다.

 

 

활용 시점: Range 리텐션은 사용 주기가 길거나 자주 방문하지 않는 서비스의 리텐션 분석에 유리합니다. 예를 들어, 가전 렌탈 서비스나 월간 관리가 필요한 제품에서 Range 리텐션을 사용하면 매주나 매월 사용자가 다시 돌아오는 비율을 정확히 확인할 수 있습니다.

 

 

 

 

 

3. Rolling 리텐션 :

Day N 이후 어느 날이라도 참여한 경우를 포함해 리텐션을 측정하지만, 현재는 잘 사용되지 않는 방식입니다.

특정 날에 재방문 여부를 확인하는 Classic 리텐션이나 주기적인 기간 내 방문을 확인하는 Range 리텐션과 달리, Rolling 리텐션은 해당 기간 내 어느 날에든 다시 사용한 경우 리텐션으로 간주합니다. 

 

 

예시: 교육 앱에서 Day 0에 가입한 사용자가 Day 7 이후 아무 날에나 접속한 경우를 Rolling 리텐션으로 계산합니다. 예를 들어, Day 0에 100명이 가입하고, Day 7 이후 어느 날이라도 재접속한 사용자가 50명이면 **Rolling 리텐션율은 50%**입니다. 이 방식은 사용자가 돌아오는 특정 날보다 재방문 여부 자체를 더 중요시할 때 활용됩니다.

 

 

 

활용 시점: Rolling 리텐션은 특정 기간 내 다시 접속한 사용자 비율을 일괄적으로 확인할 때 사용됩니다. 다만, 특정 날짜에 대한 리텐션 변화를 세밀히 측정하지는 못하기 때문에 리텐션 커브를 상세히 분석해야 하는 경우에는 Classic이나 Range 리텐션을 사용하는 것이 더 적합합니다.

 

 

 

 

📊 SQL Weekly_retention 주간 리텐션 분석 

*아래는 SQL 쿼리를 이용해 주간 리텐션을 분석하는 방법입니다.

 

SQL 쿼리 예제코드

WITH base AS ( 
  SELECT DISTINCT 
    user_id, 
    user_pseudo_id,
    event_name, 
    DATE(datetime(timestamp_micros(event_timestamp), 'Asia/Seoul')) AS event_datetime,
    DATE(datetime(timestamp_micros(event_timestamp), 'Asia/Seoul')) AS event_date
  FROM advanced.app_logs 
  WHERE event_date BETWEEN '2022-08-01' AND '2022-11-03'
), 

first_week_and_diff AS (
  SELECT *, 
  DATE_DIFF(event_week, first_week, WEEK) AS diff_of_week 
  FROM (
    SELECT DISTINCT
      user_pseudo_id,
      DATE_TRUNC(MIN(event_date) OVER (PARTITION BY user_pseudo_id), WEEK(MONDAY)) AS first_week, 
      DATE_TRUNC(event_date, WEEK(MONDAY)) AS event_week
    FROM base 
    ORDER BY first_week 
  )
), 

diff_week_user_cnt AS ( 
  SELECT diff_of_week, 
    COUNT(DISTINCT

 

 

 

📊 SQL 유저 분류 예시 코드 

retain user를 new + current + resurrected + dormant user로 나누는 쿼리를 작성해보세요.

 

 

분류기준 정하기 :
유저들의 평균주기 (마지막 접속일 - 이전 접속일의 평균일)는? -> 약 34일 -> 결론 : 유저 분류시 한달을 기준으로 분류할것.

• 앞서 구한 평균주기에 따라 기간 : monthly(약 30일)로 분류
  
  

데이터 기간 : 예를 들어, 2023-01-20일자 데이터까지 존재한다면, 2023-12-31까지 최근 월의 이전월까지만 살펴보기

• 유저 세분화 기준

1. new 유저 = (최근월 - 1)월 = 첫접속일자(event_first_month)
2. current 유저 = (최근월 -1)월 , (최근월 -2)월 모두 활동 o
3. dormant 유저 = (최근월 - 1)월 , (최근월 -2)월 모두 활동 x
4. resurrected 유저 = (최근월 -2)월 활동 x, (최근월 -1)월 에는 활동 o

with base as (
  select 
  user_pseudo_id,
  MIN(event_date) OVER (PARTITION BY user_pseudo_id) as event_first_day,   
  event_date, 
  DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul')  as event_time
  from advanced.app_logs
) 

, period as (
  select user_pseudo_id, 
  event_date, 
  LAG(event_date) OVER (PARTITION BY user_pseudo_id ORDER BY event_date) as before_event_date
  from base 
  GROUP BY ALL 
  ORDER BY user_pseudo_id
) 


-- 평균주기 구하기 
SELECT AVG(diff_day) as avg_diff_period
FROM (
select *, 
DATE_DIFF(event_date, before_event_date, DAY ) as diff_day 
from period
) 


-- 접속 주기 분포도 확인 

SELECT diff_day, 
COUNT(distinct user_pseudo_id) as user_count
FROM (
  select *, 
  DATE_DIFF(event_date, before_event_date, DAY ) as diff_day 
  from period
)
GROUP BY ALL 
ORDER BY diff_day



- 앞서 구한 평균주기에 따라 기간 : monthly로 분류 

- 유저 세분화 기준 
1) new 유저 =  (최근월 - 1)월 = 첫접속일자(event_first_month) 
2) current 유저 =  (최근월 -1)월 , (최근월 -2)월 모두 활동 o 
3) dormant 유저 =  (최근월 - 1)월 , (최근월 -2)월 모두 활동 x
4) resurrected  유저 = (최근월 -2)월 활동 x, (최근월 -1)월 에는 활동 o 

-- 최근 월 : MAX(DATE_TRUNC(event_date, MONTH)) 
-- 최근 월 - 1 이전월 : DATE_SUB(MAX(DATE_TRUNC(event_date, MONTH)), INTERVAL 1 MONTH)
-- 최근 월 - 2 이전월 : DATE_SUB(MAX(DATE_TRUNC(event_date, MONTH)), INTERVAL 2 MONTH)



with base as (
select user_pseudo_id, 
event_date, 
DATE_TRUNC(event_date, MONTH) as event_month
from advanced.app_logs
order by 1 
) 

, min_max_month as (
  SELECT *, 
  MIN(event_month) over (partition by user_pseudo_id order by event_month) as first_month,
  MAX(DATE_TRUNC(event_date, MONTH)) OVER () as lastest_month
  from base 
) 


, filter_month  as (
  SELECT user_pseudo_id, 
  first_month, 
  lastest_month, 
  COUNT(CASE WHEN event_month = DATE_SUB(lastest_month, INTERVAL 1 MONTH) THEN 1 END) AS previous_1_lastest_month,
  COUNT(CASE WHEN event_month = DATE_SUB(lastest_month, INTERVAL 2 MONTH) THEN 1  END)  AS previous_2_lastest_month  
  FROM min_max_month 
  group by ALL 
) 


, user_classification as (
  SELECT *, 
  CASE WHEN first_month = DATE_SUB(lastest_month, INTERVAL 1 MONTH ) THEN 'New'
       WHEN previous_1_lastest_month > 0 and previous_2_lastest_month >= 0 THEN 'Current'
       WHEN previous_1_lastest_month = 0 and previous_2_lastest_month = 0 THEN 'Dormant'
       WHEN previous_1_lastest_month = 0 and previous_2_lastest_month > 0 THEN 'Resurrected'
       ELSE 'Others' END AS user_category
  FROM filter_month 
) 

select user_category, 
count(distinct user_pseudo_id)
from user_classification 
group by all

 

 

 

 

 

인프런 빅쿼리 스터디에서 제공하는 자료들은 SQL 데이터 분석에 매우 유용하며, 리텐션 분석하는 데 특히 큰 도움이 됩니다.

 

 

 

 

🙌 더 자세한 내용은 카일스쿨님 블로그 및 인프런 Bigquery(SQL) 활용편 강의에서 만나볼 수 있습니다. 

 

 

 

 

 

 

 

인스타그램 : @dataviz_easy 
(꿀정보 + Python, excel, tableau로 쉽게 구현하는 정보를 공유하고 있습니다! - 관심있는 분들 친해져요~~) 

 

이메일 : dataviz.easy@gmail.com