[플레이그라운드] 코호트 리텐션 계산

문제 상황

 

특정 기간 후 이탈률이 높아지는 지점을 식별하여 집중적인 고객 관리 전략을 수립하기 위한 비즈니스 시나리오를 가정하고, 유저들의 첫번째 주문일을 기준으로 코호트를 나눈 후 유저의 첫 번째 주문 이후의 주문 리텐션을 계산해 보았다.

 

단, 한 유저가 같은 날 여러 건의 주문을 했을 경우 하루에 한 건으로 집계한다.

 

Solution

WITH step1 AS (
  SELECT r.*
       , c.first_order_date
       , DATEDIFF(r.order_date, c.first_order_date) AS day_diff
  FROM records r
    JOIN customer_stats c ON r.customer_id = c.customer_id
)
SELECT first_order_date AS cohort_date
     , day_diff
     , COUNT(DISTINCT customer_id) AS user_cnt
FROM step1
GROUP BY first_order_date, day_diff

• step1 cte :
  주문 데이터와 고객 데이터를 조인하여 각 고객의 주문일과 첫 주문일 차이(일수)를 계산
  
• 최종 select :
  cohort_date와 day_diff를 기준으로 고유 고객 수를 집계
  
  

step1 예시 데이터

20년 1월 7일 코호트의 첫 주문 이후 리텐션

 

💡위 결과를 바탕으로 낮은 리텐션을 보이는 코호트에 대해 원인을 분석하고 개선점을 도출하여 리텐션을 개선하는 전략을 세울 수 있을 것이다.