[SQL] 리텐션 구하기 | N-Day Retention, Range Retention, Rolling Retention

---

리텐션이란?

우리가 흔히 사용하는 '쇼핑앱'이나 '여행앱' 등 각종 서비스들은 신규 고객 유치를 위해 각종 마케팅을 하는데요, 이 고객들이 이탈하지 않고 계~~속해서 사용해줘야 서비스가 잘 유지되겠죠?

고객이 서비스를 얼마나 재사용하는가! 를 파악하기 위한 지표가 '리텐션'입니다. 

 

서비스의 리텐션을 산출하기에 앞서 아래 3가지를 우선 정의해야 합니다.

a. 이벤트 기준 (어느 기준으로 리텐션을 볼 것인지. 예를 들면 방문이나 주문)

b. 리텐션 정의 (어떻게 리텐션을 산출할것인지)

c. 리텐션 주기 (유저의 사용 빈도에 따라 주단위로 할건지, 월단위로 할건지)

 

이는 서비스 지향점에 따라, 목적에 따라 각기 다른 방식으로 정의해서 보곤합니다.

저희는 주로 살펴보는 '방문' 리텐션으로, 리텐션 정의와 리텐션 주기에 대해 설명드리려 하는데요.

 

 

---

리텐션 정의

리텐션은 관점과 기준에 따라서 산출 방식이 여러 가지가 있습니다.

이 기준에 대한 정의가 서로 달라 위 지표를 보고 실무에서 질문이 많이 오곤 합니다. 😭

예를 들면, "Week2 Retention 값이 1주차와 2주차 둘다 사용했다는 건가요? 2주차에만 사용했다는건가요?" 이런 질문을 수도 없이 듣곤 합니다.

그래서! 리텐션 산출 방식이 무엇이 있고, 어느 상황에 써야할지, 그리고 실무진들끼리 어떻게 커뮤니케이션하는지 아래에 구체적으로 설명드리겠습니다.

 

 

가. N-Day Retetion (클래식 리텐션)

N-Day Retetion = D+N일에 방문한 유저 수 / D0에 방문한 유저 수

쉽게 말하면 N일차의 사용 유무를 파악한다!

 

자, 먼저 아래의 유저 방문 히스토리가 있다고 봅시다.

 

보다시피 계산은 간단하나, B 유저와 같이 Day3까지 방문했으나 Day2엔 방문하지 않는 유저는 2-Day Retention 집계에선 빠지게 됩니다. 그래서 리텐션이 들쭉날쭉하게 되어 혼동이 있곤 합니다. 

우리가 고객을 얼마나 유지하고 있는지(Retain)의 관점에서 B 유저는 Day2 시점에도 여전히 서비스를 사용하고 있는데 말이죠. (요걸 보완한게 아래의 Rolling Retention 입니다.

• 장점 : 간단하다
• 단점 : 자주 방문하나 N일차에 방문하지 않은 유저는 계산되지 않는다. 얼마나 자주 방문했는가는 모름

1/1 첫 방문 유저의 N일차 리텐션

# n-day retention
select distinct min_date, day_diff
	, count(distinct fullVisitorId) over (partition by min_date) as base_cnt
	, count(distinct fullVisitorId) over (partition by min_date, visit_date) as work_cnt
	, count(distinct fullVisitorId) over (partition by min_date, visit_date) / count(distinct fullVisitorId) over (partition by min_date) as retention
from (
  select distinct fullVisitorId, min(visit_date) over (partition by fullVisitorId) as min_date, visit_date
    , date_diff(visit_date, min(visit_date) over (partition by fullVisitorId), DAY) as day_diff
  from log_
)
order by 1,2

 

나. Range Retention

Range Retention = N 기간에 방문한 유저 수 / D0에 방문한 유저 수

쉽게 말하면 N일간 사용 유무를 파악한다!

1주차에 방문한 유저가 그 다음 주차, 그 다다음 주차에 방문한 비율을 살펴봅니다. (주로 이 리텐션으로 보곤 합니다!)

• 장점 : 간단하며, 클래식 리텐션의 한계를 보완 할 수 있음
• 단점 : N기간이 지나야 지표 확인 가능하다 (한달이라면 한달이 지나야)

이는 업종마다 기간을 다르게 정의해야 합니다.

요일 트랜드가 있는 서비스(배달)은 주단위, 한 달 구독 서비스(OTT)는 월단위로 집계해야 합니다.

Week1 첫 방문 유저의 Week별 리텐션

# range retention (weekly) 
select distinct min_week, week_diff
	, count(fullVisitorId) over (partition by min_week) as base_cnt
    , count(fullVisitorId) over (partition by min_week, visit_week) as work_cnt
    , count(fullVisitorId) over (partition by min_week, visit_week)/count(fullVisitorId) over (partition by min_week) as retention
from 
(
  select distinct fullVisitorId
	, min(visit_week) over (partition by fullVisitorId) as min_week 
	, visit_week
	, date_diff(visit_week, min(visit_week) over (partition by fullVisitorId), WEEK) as week_diff
  from
  (
    select distinct fullVisitorId, date_trunc(visit_date, WEEK(MONDAY)) as visit_week #,format_date('%W', visit_date) as week_nm
    from log_
  ) 
) 
order by 1,2

 

 

다. Rolling Retetion

Rolling Retention = N일 이후 방문한 유저 수 / 전체 유저 수

쉽게 말하면 N일 뒤의 사용 유무를 파악한다!

‘사용자가 이탈하지 않고 남아있는가'를 파악하는 지표인데요, 위 표처럼 마지막 방문일 이전은 방문한 것으로 간주하는 겁니다. D 유저와 같이 Day4까지 방문하지 않다가 Day5에 방문했어도, '우리의 서비스를 계속 이용하고 있구나' 라고 보는거죠.

다만, A 유저와 D 유저를 동급으로 취급하기 때문에, 이 지표는 충성 유저와 뜨내기 유저의 분간이 안됩니다.

 

• 장점 : 최초 방문일과 마지막 방문일만 계산하기 때문에 계산이 빠르다. 직관적이다
• 단점
  • 집계 시점에 따라 마지막 방문일은 바뀔 수 있음. 트렌드 파악용(서브)으로 권장
  • 충성 유저와 오랜 기간 방문을 하지 않다가 방문한 유저를 동일하게 취급한다
  • 방문 주기가 긴 서비스에서 주로 사용 (여행, 부동산 등)

1/1 첫 방문인 유저의 N일 이후 잔존 비율

# rolling retention
select *
	, sum(work_cnt) over (partition by min_date order by day_diff desc) as work_cum --잔존 유저 누적합
    , sum(work_cnt) over (partition by min_date order by day_diff desc)/base_cnt as retention 
  # 2안 (복잡하다 추천하지 않음)
  ## lag_work_cnt = lag(work_cnt,1) over (partition by min_date order by day_diff) --N+1일에 이탈한 유저 수
  ## retention = (base_cnt - sum(lag_work_cnt) over (partition by  min_date order by day_diff))/base_cnt
from (
  select distinct min_date, day_diff
  	, count(fullVisitorId) over (partition by min_date) as base_cnt
    , count(fullVisitorId) over (partition by min_date, max_date) as work_cnt --N일에 마지막 방문인 유저 수
  from (
    select distinct fullVisitorId, min(visit_date) over (partition by fullVisitorId) as min_date, max(visit_date) over (partition by fullVisitorId) as max_date
      , date_diff(max(visit_date) over (partition by fullVisitorId), min(visit_date) over (partition by fullVisitorId) , DAY) as day_diff
    from log_ 
    )
  ) 
order by 1,2

 

---

리텐션은 유저가 서비스를 지속적으로 사용하는지 보기 위한 가장 기본이 되는 지표이지만, 서비스의 특성과 분석 목적에 따라 알맞게 활용해야 유의미한 인사이트를 도출 할 수 있습니다!

 

그러니, 위의 내용을 잘 파악해서 실무에서 활용하시길 바랍니다!

 

---

Appendix. SQlite ver

--N Day Retention
with log_ as 
(
  select distinct min_date, day_diff
    , count(user_pseudo_id) over (partition by min_date) as base_value
    , count(user_pseudo_id) over (partition by min_date, day_diff) as work_value
    , round(cast(count(user_pseudo_id) over (partition by min_date, day_diff) as real)/cast(count(user_pseudo_id) over (partition by min_date) as real),2) as retention
  from 
  (
    select distinct user_pseudo_id, min(event_date_kst) over (partition by user_pseudo_id) as min_date, event_date_kst
      , JULIANDAY(event_date_kst) - JULIANDAY(min(event_date_kst) over (partition by user_pseudo_id)) as day_diff
    from ga
  )
)

select min_date
  , max(base_value) as base_value
  , max(case when day_diff = 0 then retention end) as diff0
  , max(case when day_diff = 1 then retention end) as diff1
  , max(case when day_diff = 2 then retention end) as diff2
from log_
group by 1
order by 1​

-- Rolling Retetion
select min_date, count(user_pseudo_id) as base_value
  , count(case when day_diff > 7 then user_pseudo_id end) as day7_value
from
(
  select user_pseudo_id, min(event_date_kst) as min_date, max(event_date_kst) as max_date
    , julianday(max(event_date_kst)) - julianday(min(event_date_kst)) as day_diff
  from ga
  group by 1
)
group by 1