[SQL] MySQL로 클래식 리텐션 (Classic Retention) 측정하기

이번 포스트에서는 클래식 리텐션을 SQL로 어떻게 측정할 수 있는지 알아보겠습니다.

최종적으로 구현한 쿼리를 실행하면 위와 같은 결과를 얻을 수 있습니다. 먼저 클래식 리텐션에 대해서 간단하게 알아볼게요.

클래식 리텐션이란?

리텐션은 사용자들이 서비스를 지속적으로 사용하고 있는지를 측정한 지표입니다. 그중에서도 클래식 리텐션은 사용자가 특정 시점(예: 첫 구매, 첫 방문 등) 이후에 일정 기간 동안 서비스를 다시 이용했는지를 측정하는 방식입니다.

예를 들어, A라는 서비스에서 한 사용자가 1월에 처음으로 상품을 구매했다면 2월, 3월, 4월에도 다시 구매했는지를 파악하여 사용자의 리텐션을 월 단위로 확인할 수 있습니다.

또한, 지속적인 사용의 기준은 서비스의 특성에 맞게 정의해야 합니다. 어떤 서비스에서는 '방문'을 기준으로 할 수도 있고 다른 서비스에서는 '결제', '조회' 등을 기준으로 할 수도 있습니다. 일별, 주별, 월별 등의 단위도 서비스의 성격에 맞게 선택하는 것이 중요합니다.

클래식 리텐션 측정을 위한 시나리오

데이터

미국 이커머스 데이터인 US E-Commerce 2020 data를 활용하여 SQL 쿼리를 통해 월별 클래식 리텐션을 측정해보겠습니다.

쿼리

Step 1

-- 클래식 리텐션
WITH initial_purchase AS (
	SELECT customer_id
         , DATE_FORMAT(MIN(order_date), '%Y-%m-01') AS first_order_month  -- 첫 주문일을 'YYYY-MM-01' 형식으로 변환
	FROM ecommerce.records
    GROUP BY customer_id
)

먼저, 사용자별 첫 구매일을 담은 initial_purchase 테이블을 만듭니다. 이때, 날짜 연산의 편의를 위해 모든 날짜를 '해당 월의 첫째 날'로 변환합니다.

Step 2

-- 클래식 리텐션
WITH initial_purchase AS (
	SELECT customer_id
         , DATE_FORMAT(MIN(order_date), '%Y-%m-01') AS first_order_month  -- 첫 주문일을 'YYYY-MM-01' 형식으로 변환
	FROM ecommerce.records
    GROUP BY customer_id
),
purchase AS (
	SELECT customer_id
		 , DATE_FORMAT(order_date, '%Y-%m-01') AS order_month
	FROM ecommerce.records
)

마찬가지로, 전체 구매 내역을 담은 purchase 테이블을 만들고 날짜 연산의 편의를 위해 모든 구매일을 '해당 월의 첫째 날'로 변환합니다.

Step 3

-- 클래식 리텐션
WITH initial_purchase AS (
	SELECT customer_id
         , DATE_FORMAT(MIN(order_date), '%Y-%m-01') AS first_order_month  -- 첫 주문일을 'YYYY-MM-01' 형식으로 변환
	FROM ecommerce.records
    GROUP BY customer_id
),
purchase AS (
	SELECT customer_id
		 , DATE_FORMAT(order_date, '%Y-%m-01') AS order_month
	FROM ecommerce.records
)
SELECT DATE_FORMAT(ip.first_order_month, '%Y-%m') AS first_order_month
	 
     -- month0: 첫 구매한 고객 수 (해당 월에 첫 구매한 고객 수)
	 , COUNT(DISTINCT ip.customer_id) AS month0
     
     -- month1 ~ month11: 첫 구매 이후 각 월에 재구매한 고객 수
     , COUNT(DISTINCT (CASE WHEN DATE_ADD(ip.first_order_month, INTERVAL 1 MONTH) = p.order_month THEN ip.customer_id END)) AS month1
     , COUNT(DISTINCT (CASE WHEN DATE_ADD(ip.first_order_month, INTERVAL 2 MONTH) = p.order_month THEN ip.customer_id END)) AS month2
     , COUNT(DISTINCT (CASE WHEN DATE_ADD(ip.first_order_month, INTERVAL 3 MONTH) = p.order_month THEN ip.customer_id END)) AS month3
     , COUNT(DISTINCT (CASE WHEN DATE_ADD(ip.first_order_month, INTERVAL 4 MONTH) = p.order_month THEN ip.customer_id END)) AS month4
     , COUNT(DISTINCT (CASE WHEN DATE_ADD(ip.first_order_month, INTERVAL 5 MONTH) = p.order_month THEN ip.customer_id END)) AS month5
     , COUNT(DISTINCT (CASE WHEN DATE_ADD(ip.first_order_month, INTERVAL 6 MONTH) = p.order_month THEN ip.customer_id END)) AS month6
     , COUNT(DISTINCT (CASE WHEN DATE_ADD(ip.first_order_month, INTERVAL 7 MONTH) = p.order_month THEN ip.customer_id END)) AS month7
     , COUNT(DISTINCT (CASE WHEN DATE_ADD(ip.first_order_month, INTERVAL 8 MONTH) = p.order_month THEN ip.customer_id END)) AS month8
     , COUNT(DISTINCT (CASE WHEN DATE_ADD(ip.first_order_month, INTERVAL 9 MONTH) = p.order_month THEN ip.customer_id END)) AS month9
     , COUNT(DISTINCT (CASE WHEN DATE_ADD(ip.first_order_month, INTERVAL 10 MONTH) = p.order_month THEN ip.customer_id END)) AS month10
     , COUNT(DISTINCT (CASE WHEN DATE_ADD(ip.first_order_month, INTERVAL 11 MONTH) = p.order_month THEN ip.customer_id END)) AS month11
FROM purchase AS p
LEFT JOIN initial_purchase AS ip ON p.customer_id = ip.customer_id
GROUP BY ip.first_order_month;

마지막으로, 각 사용자가 처음 구매한 월을 기준으로 해당 월에 처음 구매한 고객 수와 1개월 이후부터 11개월 이후까지 각 월별 재구매 고객 수를 집계합니다. 이를 위해 전체 구매 내역과 최초 구매일을 고객 아이디로 조인한 후에 DATE_ADD 함수를 사용해 재구매 시점을 비교하여 클래식 리텐션을 측정합니다.

참고
🔗 클래식 리텐션 Classic Retention