[SQL] 20. 많이 쓰이는 분석 프레임(3) - 리텐션 다지기

리텐션에도 종류가 있다 - 리텐션 다지기

신규 고객 유치보다 기존 고객의 유지가 도움이 되는 이유로 리텐션이 중요하다

 

다양한 리텐션 산출 방법

• 목적과 상황에 맞게 리텐션 산출 방법을 사용한다.

1. 클래식 리텐션
2. Range 리텐션
3. 롤링 리텐션

1. 클래식 리텐션 = Day N 리텐션

• N일 뒤에 다시 돌아온 유저의 비율

ex)

8/1 일 (10명)  /  8/2 월 (3명)  /   8/3 화 (2명)

• Day 1 리텐션 = 3 / 10 = 30%
• Day 2 리텐션 = 2 / 10 = 20%

클래식 리텐션 (Day N 리텐션) = D+N일 뒤에 방문한 사람 수 / D0에 방문한 사람 수

• 장점 : 개념이 간단하기 때문에 계산하기 쉽고 설명하기 쉽다
• 단점 : 일별 이벤트에 영향을 많이 받는다
  • D0 ~ D+N 사이에 여러본 오더라도 Day N 리텐션에 계산 되지 않는다
  • 얼마나 자주 왔는지가 반영되지 않는다는 것이 큰 단점

 

2. Range 리텐션

• 다음 Period에 다시 돌아온 유저의 비율
• 클래식 리텐션과 유사 하나, 기간으로 늘려서 살펴본다
• Period의 일반적인 기준은 주 혹은 월 단위

ex)

주 단위

8/1 일 (10명)  /  8/2 월 (10명)  /   8/3 화 (10명)  /  8/4 수 (10명)  /  8/5 목 (10명)  /   8/6 금 (6명) - 50명 중에 6명이 재방문

8/8 일 (5명)   /                                                                                                         /   8/6 금 (4명)

• Range 리텐션 = 9 / 50 = 18%
• 기준 Period 단위 내에 다시 온 사람은 카운트하지 않는다 (6명은 카운트 X)

Range 리텐션 = Next Period에 방문한 사람 수 / 최초 Period에 방문한 사람 수

• 장점 : 개념이 간단하기 때문에 설명하기 쉽다
  • 클래식리텐션 (Day N 리텐션)에서 발생할 수 있는 일별 노이즈를 줄일 수 있다
  • 클래식리텐션은 특정일에 어떤 이벤트가 있었을 때 영향을 많이 받는다
• 단점 : Period 기간만큼 시간이 지나야 지표를 확인할 수 있다
  • 한 달로 정해놨다면, 이번달이 끝나야 지난달과 비교가능 하다
• 유의할 점 : Period를 비즈니스에 맞게 정하는 것이 중요하다
  • 배달음식 - 7일을 period로 설정한다
  • 구독형식 - 30일로 retention range를 설정한다

• 코호트 분석은 Range 리텐션을 각각 산출한 버전이다

 

롤링 리텐션 = 리턴 리텐션

• 일정 기간 이후에 리턴한 유저의 비율
• 몇 일이 지나도 여전히 살아있는 유저가 몇명이냐 하는 비율
• 긴 관계를 유지하는 유저의 수를 판단하는 데 적합한 지표

ex)

8/1 일 (10명)     /                                                                                                             /  8/7 토 (1(A))

                         /                            /  8/10 화 (1(B)) 

8/15 일 (1(C))  /  8/16 월 (1(C)) 

 

• 7일 롤링 리텐션 = 3 / 10 = 30%
  • 처음 이후로 7일째 되는 날 이후로 몇명이 돌아왔냐, A, B, C 3명 돌아옴
• 14일 롤링 리텐션 = 1 / 10 = 10%
  • 14일째 되는 날 이후로 몇명이 돌아왔냐, C 1명 돌아옴

롤링 리텐션 = N일 이후 방문한 사람 수 / D0에 방문한 사람 수

• 장점 :
  • 이탈에 대한 정보를 얻을 수 있다 (100% - 이탈율)
    • 아직 완전히 떨어져 나가지 않는 비율을 구한 것이 롤링 리텐션이기 떄문
  • 계산이 빠르다
    • 첫번째 방문일자, 마지막 방문일자로만 계산한다
• 단점 :
  • 충성고객과 N일 이후 한번만 온 사람을 같게 취급한다
    • 기간 동안 한번도 안오다가 측정 때 온 사람과 기간 동안 매일 오고 측정 때 온 사람을 같게 취급한다
  • 시간이 지나면서 숫자가 바뀔 수 있다.
    • 원래는 오지 않았던 고객이 기간 후 오게 되면 리텐션에 카운트가 되게 된다.
• 그래서, 기존 리텐션 지표의 보조로 사용되는 경우가 많다.

 

실습 : 롤링 리텐션 구하기

• 롤링 리텐션에서는 고객의 최초 구매 일자와 마지막 구매 일자가 필요하다.
• 그리고 그 둘의 차이가 30일 롤링 리텐션 기준으로 30일보다 크냐 작냐를 따져보면 된다.

SELECT customer_id, MIN(invoice_date) AS first_purchase, MAX(invoice_date) AS recent_purchase
FROM data.sales
GROUP BY customer_id

• 일자 형태로 변환

SELECT customer_id, DATE(MIN(invoice_date)) AS first_purchase, DATE(MAX(invoice_date)) AS recent_purchase
FROM data.sales
GROUP BY customer_id

• 최근 일자와 최초 일자의 차이를 일(DAY)단위로 구한다.

SELECT customer_id, DATE(MIN(invoice_date)) AS first_purchase,
       DATE(MAX(invoice_date)) AS recent_purchase,
       DATE_DIFF(DATE(MAX(invoice_date)), DATE(MIN(invoice_date)), DAY) AS diff_day
FROM data.sales
GROUP BY customer_id

• 위를 서브쿼리로 하여 롤링 리텐션을 구한다
• 롤링 리텐션 기준 기간을 넘어서는 유저를 구할 때는 CASE_WHEN과 COUNT 사용
• 30일째 부터 = 차이가 29
• 29보다 크거나 같으면 1로, 속하지 않으면 NULL로 계산되어 COUNT된다. (NULL은 카운트 안됨)

SELECT COUNT(customer_id) AS total_customer,
       COUNT(CASE WHEN diff_day>=29 THEN 1 END) AS retention_customer
FROM (
  SELECT customer_id, DATE(MIN(invoice_date)) AS first_purchase,
         DATE(MAX(invoice_date)) AS recent_purchase,
         DATE_DIFF(DATE(MAX(invoice_date)), DATE(MIN(invoice_date)), DAY) AS diff_day
  FROM data.sales
  GROUP BY customer_id
)

SELECT COUNT(customer_id) AS total_customer,
       COUNT(CASE WHEN diff_day>=29 THEN 1 END) AS retention_customer,
       COUNT(CASE WHEN diff_day>=29 THEN 1 END) / COUNT(customer_id) AS rolling_retention_30
FROM (
  SELECT customer_id, DATE(MIN(invoice_date)) AS first_purchase,
         DATE(MAX(invoice_date)) AS recent_purchase,
         DATE_DIFF(DATE(MAX(invoice_date)), DATE(MIN(invoice_date)), DAY) AS diff_day
  FROM data.sales
  GROUP BY customer_id
)

• 한 달이 지나도 여전히 살아있는 고객은 60%정도 된다 = 롤링 리텐션