[데이터리안] SQL로 리텐션 구하는 방법(클래식, 롤링 리텐션)

양승화님의 그로스 해킹 책을 통해 리텐션의 개념을 익히고, 개인 프로젝트를 통해 리텐션을 구하는 쿼리도 작성해봤다.

 

쿼리를 짜면서도 리텐션을 구하는 쿼리가 어렵다고 생각하고, 서비스별로 리텐션을 측정하는 방법이나 기준이 다양하기에 이번 코테를 준비하면서 한 번 정리해보려고 한다.

**구글링을 통해 찾은 리텐션 쿼리를 리뷰하는 방향으로 작성했다! 
+) 이해가 어려운 부분은 gpt를 통해 세부 설명을 추가하여 학습 
+) 사용한 쿼리의 페이지 링크를 앞부분에 출처를 추가

 

1. 클래식 리텐션

출처: 데이터 리안 실습 후기 블로그👇

*아래 문제는 데이터리안 분석 캠프 실전반 실습 문제입니다. 

데이터리안 - SQL 데이터 분석캠프 실전반- 2주차: 클래식 리텐션 실습

---

STEP 0. first_order_date 생성하기

위 출처의 포스팅에는  first_order_date가 이미 주어져있지만, order_date만 있다고 가정할 때 추출하는 쿼리를 추가로 작성해봤다

1) customer_id별로 min(order_date)로 first_order_date를 생성한다.

WITH first_order AS (
  SELECT
    customer_id,
    MIN(order_date) AS first_order_date -- 각 고객의 첫 번째 주문 날짜를 구함
  FROM records
  GROUP BY customer_id
),

 

STEP 1. order_date와 first_order_date를 ‘YYYY-MM-01’ 형태로 가공

WITH절로 날짜 형식을 변경한 테이블 생성

1) DATE_FORMAT(컬럼명, '%Y-%m-01')로 날짜 데이터의 일자를 1일로 반환하여 주문월과 첫주문월을 구함
    (ex 2025-02-08 -> 2025-02-01)

WITH records_preprocessed AS (
  SELECT r.customer_id
     , r.order_id
     , r.order_date
     , c.first_order_date
     , DATE_FORMAT(r.order_date, '%Y-%m-01') AS order_month
     , DATE_FORMAT(c.first_order_date, '%Y-%m-01') AS first_order_month
  FROM records r
      INNER JOIN customer_stats c ON r.customer_id = c.customer_id
  )

STEP 2. 첫 구매한 달로부터 2~11개월 뒤에도 주문한 고객 수 계산

1)아래에 month0과, month1~11까지를 도출하는 세부과정을 나누어 설명!

SELECT first_order_month
     , COUNT(DISTINCT customer_id) AS month0
     , COUNT(DISTINCT CASE WHEN order_month = DATE_ADD(first_order_month, INTERVAL 1 MONTH) THEN customer_id END) AS month1
     , COUNT(DISTINCT CASE WHEN order_month = DATE_ADD(first_order_month, INTERVAL 2 MONTH) THEN customer_id END) AS month2
     , COUNT(DISTINCT CASE WHEN order_month = DATE_ADD(first_order_month, INTERVAL 3 MONTH) THEN customer_id END) AS month3
     , COUNT(DISTINCT CASE WHEN order_month = DATE_ADD(first_order_month, INTERVAL 4 MONTH) THEN customer_id END) AS month4
     , COUNT(DISTINCT CASE WHEN order_month = DATE_ADD(first_order_month, INTERVAL 5 MONTH) THEN customer_id END) AS month5
     , COUNT(DISTINCT CASE WHEN order_month = DATE_ADD(first_order_month, INTERVAL 6 MONTH) THEN customer_id END) AS month6
     , COUNT(DISTINCT CASE WHEN order_month = DATE_ADD(first_order_month, INTERVAL 7 MONTH) THEN customer_id END) AS month7
     , COUNT(DISTINCT CASE WHEN order_month = DATE_ADD(first_order_month, INTERVAL 8 MONTH) THEN customer_id END) AS month8
     , COUNT(DISTINCT CASE WHEN order_month = DATE_ADD(first_order_month, INTERVAL 9 MONTH) THEN customer_id END) AS month9
     , COUNT(DISTINCT CASE WHEN order_month = DATE_ADD(first_order_month, INTERVAL 10 MONTH) THEN customer_id END) AS month10
     , COUNT(DISTINCT CASE WHEN order_month = DATE_ADD(first_order_month, INTERVAL 11 MONTH) THEN customer_id END) AS month11
FROM records_preprocessed
GROUP BY first_order_month

예상되는 결과 테이블(gpt)

first_order_month	month0	month1	..	..	..	..	..	..	..	..	..	month11
2022-01-01	100	50	30	20	10	5	3	2	1	1	0	0
2022-02-01	150	75	45	30	20	15	10	8	6	4	2	1
2022-03-01	200	120	90	60	40	25	18	15	12	8	5	3

STEP 2 -1. 각 월별 첫 구매 고객 수 계산(month0)

아래부터는 리텐션을 구하는 실제 쿼리에는 포함되지 않으며, 과정을 이해하기 위한 쿼리!

1) first_order_month로 group by해서 월별로 첫구매한 고유한 유저 수를  count (ex 2025-01-01, 104명)

    * 해당 월에 여러번 구매한 유저가 있을 수 있으므로 꼭 DISTINCT 챙기기!

SELECT first_order_month
     , COUNT(DISTINCT customer_id) AS month0
FROM records_preprocessed
GROUP BY first_order_month

STEP 2-2.  첫 구매한 달의 다음 달에도 구매한 고객 수 계산(month1)

1) DATE_ADD(기준 컬럼, INTERVAL 숫자 기간단위) => 날짜에서 주어진 기간을 더할 때 사용

   * 기간 단위: DAY, MONTH, YEAR, HOUR, MINUTE 등
    *반대로 빼주는 것은 SUB를 사용하면 된다 DATE_SUB

2) order_month가 first_order_month +1과 같다면, month1으로 count

    *동일하게 해당 월에 여러번 구매한 유저가 있을 수 있으므로 DISTINCT 챙기기!

3) 이와 같은 방식으로 INTERVAL에 원하는 월을 넣어서 0~n월까지의 재구매 수를 count

SELECT first_order_month
     , COUNT(DISTINCT customer_id) AS month0
     , COUNT(DISTINCT CASE WHEN order_month = DATE_ADD(first_order_month, INTERVAL 1 MONTH) THEN customer_id END) AS month1
FROM records_preprocessed
GROUP BY first_order_month

 

STEP 3 리텐션율 테이블 만들기

1) 추가로 리텐션율을 보여주는 테이블을 만들기 위해 STEP2의 재구매 고객 수를 WITH절로 만들기
2) SELECT문에서는 재구매 고객수 테이블을 활용하여 리텐션 테이블 작성(N월의 구매자 수/첫구매자 수)

WITH records_preprocessed AS (
    SELECT 
        r.customer_id,
        r.order_date,
        c.first_order_date,
        DATE_FORMAT(r.order_date, '%Y-%m-01') AS order_month,
        DATE_FORMAT(c.first_order_date, '%Y-%m-01') AS first_order_month
    FROM records r
    INNER JOIN customer_stats c 
        ON r.customer_id = c.customer_id
),
customer_counts AS (
    SELECT 
        first_order_month,
        COUNT(DISTINCT customer_id) AS month0,
        COUNT(DISTINCT CASE WHEN order_month = DATE_ADD(first_order_month, INTERVAL 1 MONTH) THEN customer_id END) AS month1,
        COUNT(DISTINCT CASE WHEN order_month = DATE_ADD(first_order_month, INTERVAL 2 MONTH) THEN customer_id END) AS month2,
        COUNT(DISTINCT CASE WHEN order_month = DATE_ADD(first_order_month, INTERVAL 3 MONTH) THEN customer_id END) AS month3,
        COUNT(DISTINCT CASE WHEN order_month = DATE_ADD(first_order_month, INTERVAL 4 MONTH) THEN customer_id END) AS month4,
        COUNT(DISTINCT CASE WHEN order_month = DATE_ADD(first_order_month, INTERVAL 5 MONTH) THEN customer_id END) AS month5,
        COUNT(DISTINCT CASE WHEN order_month = DATE_ADD(first_order_month, INTERVAL 6 MONTH) THEN customer_id END) AS month6,
        COUNT(DISTINCT CASE WHEN order_month = DATE_ADD(first_order_month, INTERVAL 7 MONTH) THEN customer_id END) AS month7,
        COUNT(DISTINCT CASE WHEN order_month = DATE_ADD(first_order_month, INTERVAL 8 MONTH) THEN customer_id END) AS month8,
        COUNT(DISTINCT CASE WHEN order_month = DATE_ADD(first_order_month, INTERVAL 9 MONTH) THEN customer_id END) AS month9,
        COUNT(DISTINCT CASE WHEN order_month = DATE_ADD(first_order_month, INTERVAL 10 MONTH) THEN customer_id END) AS month10,
        COUNT(DISTINCT CASE WHEN order_month = DATE_ADD(first_order_month, INTERVAL 11 MONTH) THEN customer_id END) AS month11
    FROM records_preprocessed
    GROUP BY first_order_month
)
# 기존 쿼리를 WITH절로 작성, 하단의 메인쿼리에서 리텐센율을 구하기
SELECT 
    first_order_month,
    ROUND(100 * month1 / NULLIF(month0, 0), 2) AS retention_month1,
    ROUND(100 * month2 / NULLIF(month0, 0), 2) AS retention_month2,
    ROUND(100 * month3 / NULLIF(month0, 0), 2) AS retention_month3,
    ROUND(100 * month4 / NULLIF(month0, 0), 2) AS retention_month4,
    ROUND(100 * month5 / NULLIF(month0, 0), 2) AS retention_month5,
    ROUND(100 * month6 / NULLIF(month0, 0), 2) AS retention_month6,
    ROUND(100 * month7 / NULLIF(month0, 0), 2) AS retention_month7,
    ROUND(100 * month8 / NULLIF(month0, 0), 2) AS retention_month8,
    ROUND(100 * month9 / NULLIF(month0, 0), 2) AS retention_month9,
    ROUND(100 * month10 / NULLIF(month0, 0), 2) AS retention_month10,
    ROUND(100 * month11 / NULLIF(month0, 0), 2) AS retention_month11
FROM customer_counts;

2. 롤링 리텐션

출처: 데이터 리안 실습 후기 블로그👇

*아래 문제는 데이터리안 분석 캠프 실전반 실습 문제입니다. 

데이터리안 - SQL 데이터 분석캠프 실전반- 2주차: 롤링 리텐션 실습

---

STEP 1. order_date와 first_order_date 가공

1) 위 출처의 포스팅에는 firset_order_date와 last_order_date가 주어져있지만,
나는 order_date만 있다고 가정하고  MIN과 MAX를 사용하여 새로 생성하는 형태로 변경하였다. 
2) 클래식 리텐션과 마찬가지로 DATE_FORMAT으로 1일로 형태를 변경

WITH customers AS (
  SELECT customer_id,
         MIN(order_date) AS first_order_date,
         MAX(order_date) AS last_order_date,
         DATE_FORMAT(MIN(order_date), '%Y-%m-01') AS first_order_month,
         DATE_FORMAT(MAX(order_date), '%Y-%m-01') AS last_order_month
  FROM customer_stats
  GROUP BY customer_id
)

STEP 2. 첫 주문 후 이탈하지 않은 고객의 수를 count

1) 첫구매 월을 구하는 방식은 클래식 리텐션과 동일하게 첫 구매월별 고유한 고객의 id수를 count

2) 클래식 리텐션과 동일하게 DATE_ADD INTERVAL로 n개월 이후의 수를 count
    + last_order_month보다 같거나 작은지를 확인(작은 경우 이탈)

SELECT first_order_month,
       COUNT(DISTINCT customer_id) AS month0,
       COUNT(DISTINCT CASE WHEN DATE_ADD(first_order_month, INTERVAL 1 MONTH) <= last_order_month THEN customer_id END) AS month1,
       COUNT(DISTINCT CASE WHEN DATE_ADD(first_order_month, INTERVAL 2 MONTH) <= last_order_month THEN customer_id END) AS month2,
       COUNT(DISTINCT CASE WHEN DATE_ADD(first_order_month, INTERVAL 3 MONTH) <= last_order_month THEN customer_id END) AS month3,
       COUNT(DISTINCT CASE WHEN DATE_ADD(first_order_month, INTERVAL 4 MONTH) <= last_order_month THEN customer_id END) AS month4,
       COUNT(DISTINCT CASE WHEN DATE_ADD(first_order_month, INTERVAL 5 MONTH) <= last_order_month THEN customer_id END) AS month5,
       COUNT(DISTINCT CASE WHEN DATE_ADD(first_order_month, INTERVAL 6 MONTH) <= last_order_month THEN customer_id END) AS month6,
       COUNT(DISTINCT CASE WHEN DATE_ADD(first_order_month, INTERVAL 7 MONTH) <= last_order_month THEN customer_id END) AS month7,
       COUNT(DISTINCT CASE WHEN DATE_ADD(first_order_month, INTERVAL 8 MONTH) <= last_order_month THEN customer_id END) AS month8,
       COUNT(DISTINCT CASE WHEN DATE_ADD(first_order_month, INTERVAL 9 MONTH) <= last_order_month THEN customer_id END) AS month9,
       COUNT(DISTINCT CASE WHEN DATE_ADD(first_order_month, INTERVAL 10 MONTH) <= last_order_month THEN customer_id END) AS month10,
       COUNT(DISTINCT CASE WHEN DATE_ADD(first_order_month, INTERVAL 11 MONTH) <= last_order_month THEN customer_id END) AS month11
FROM customers
GROUP BY first_order_month
ORDER BY first_order_month