[SQL] 📌 코호트 분석 - 리텐션 알아보기 | Kaggle 이커머스 데이터 활용 예제

Cohort Analysis (코호트 분석)

 

Cohort는 '동질집단'이라는 뜻이다.

Cohort 분석은 공통된 특성을 가진 사용자 집단(코호트)의 행동 변화를 시간에 따라 분석하여 트렌드와 패턴을 파악하는 기법이다.

 

플랫폼의 사용자들이 서비스를 어떤 비율로 재방문/재구매하는지 확인하고 싶을 때,

즉 사용자의 리텐션을 파악하고자 할 때 유용하게 사용할 수 있다.

보통 월(monthly) 또는 주(weekly) 단위로 리텐션을 확인한다.

 

Data 준비

데이터는 캐글의 온라인 세일 데이터를 사용했다.

 

https://www.kaggle.com/datasets/jihyeseo/online-retail-data-set-from-uci-ml-repo

Online Retail Data Set from UCI ML repo

 

데이터에서 유저ID와 구매날짜만 있으면 되기 때문에 가공해서 SQL에 import 해준다.

활용한 데이터는 이렇게 생겼다.

 

 

SQL 코드 : 방법 1

MySQL workbench에서 코드를 실행했다.

 

-- 유저별 첫 구매일 구하기
WITH first_purchase AS (
    SELECT
        CustomerID AS user_id,
        MIN(InvoiceDate) AS first_purchase_date
    FROM cohort_practice.online_sale_data
    GROUP BY 1
),

-- 첫 구매일이 담긴 테이블과 일반 구매일을 조인, 두 날짜의 월 차이 계산
cohort_group as (
SELECT 
    ch.CustomerID AS user_id,
    DATE_FORMAT(fp.first_purchase_date, '%Y-%m') AS cohort_month,
    (YEAR(ch.InvoiceDate) * 12 + MONTH(ch.InvoiceDate)) - 
    (YEAR(fp.first_purchase_date) * 12 + MONTH(fp.first_purchase_date)) AS cohort_index -- 첫 구매일과 구매일의 월 차이 계산
FROM cohort_practice.online_sale_data ch
LEFT JOIN first_purchase fp
    ON ch.CustomerID = fp.user_id )

-- 첫 구매일, 재구매일을 기준으로 사용자 규모 count
select cohort_month, cohort_index, count(distinct user_id) as quantity
from cohort_group
group by cohort_month, cohort_index

 

 

1. first_purchase CTE :

유저ID별로 그룹화를 하여 유저별 최초 구매일을 구해준다.

 

2. cohort_group CTE :

첫 구매일이 담긴 테이블과 원본 데이터를 조인해준다.

그러면 한 row 당 유저 ID / 그 유저의 첫 구매일 / 일반 구매일(InvoiceDate) 이렇게 우리가 필요한 데이터를 한 곳에 모을 수 있다.

 

여기서 우리는 일반 구매일(InvoieDate)과 첫 구매일의 차이를 구해야 한다. 

따라서 InvoiceDate와 first_purchase_date을 1이 한 달 단위인 숫자로 변환해주고 차이를 구해준다.

그 차이는 cohort_index로 나중에 첫 구매일로부터 1달, 2달, 3달 뒤인 시간의 흐름을 나타내주는 역할을 한다.

 

다른 블로그에서는 DATEDIFF와 TIMESTAMPDIFF를 사용했는데 그렇게 하면 두 날짜 사이의 간격을 기준으로 숫자를 반환하게 되는 문제가 생긴다.

예를 들어, 2024년 3월 24일이 첫 구매일, 2024년 4월 1일이 재구매일인 경우

원하는 값 : 1 ( 한달 차이 ) 인데

DATEDIFF/TIMESTAMPDIFF를 사용하는 경우 : 0 (두 날짜 간격이 11일로 한달 미만) 으로

원하는 값을 구할 수 없다.

따라서 위와 같은 방법을 사용하였다.

 

3. 최종 select문:

마지막으로 첫 구매일 (cohort_month)와 월 차이 (cohort_index)를 기준으로 그룹화를 하여 유저ID를 count해준다.

그러면 첫 구매일을 기준으로 n월 떨어진 날짜에 몇명이 구매를 했는지를 알 수 있게 된다.

 

 

결과 테이블

쿼리 실행 결과 테이블은 이렇게 생겼다.

이것을 우리가 아는 삼각형 모양 차트로 만들어주기 위해 SQL을 사용할 수도 있지만

엑셀이 더 간편하기 때문에 엑셀로 옮겨준다 !

 

Cohort 차트 : 엑셀 사용

엑셀의 피벗 테이블을 사용해서 우리가 원하는 삼각형 모양 차트로 만들어준다.

그 다음에 조건부 서식을 사용해서 스케일별로 색칠을 해준다.

 

결과를 해석해보자면, 

2010년 12월에 첫 구매를 한 유저는 948명이다.

그리고 그 유저 중 38% (362명)이 다음 달인 2011년 1월에도 구매 행위를 했다.

33%가 그 다음 달에도 구매를 했고 39%, 36% 이렇게 월 단위의 시간의 흐름에 따라 유저가 재구매를 했는지 확인할 수 있다.

 

---

 

 

 

SQl 코드 : 방법 2

-- 첫 구매일 구하기
WITH first_order AS (
    SELECT 
        CustomerID as customer_id,
        MIN(InvoiceDate) AS first_order_date
    FROM cohort_practice.online_sale_data
    GROUP BY customer_id
),
-- 최초 구매일과 일반 구매일 구하기
order_month_table AS (
    SELECT 
        a.CustomerID as customer_id, 
        date_format(first_order_date, '%Y-%m-01') AS first_order_month, 
        date_format(InvoiceDate, '%Y-%m-01') AS order_month
    FROM cohort_practice.online_sale_data a
    LEFT JOIN first_order b
    ON a.CustomerID = b.customer_id
),
-- 첫 구매일을 기준으로 그룹화
-- month0은 그냥 distinct 유저id count
-- month1~11은 case when 사용
-- month1은 구매일 = 첫 구매일 + 1m 일 때, distinct 유저id count
cohort_chart 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 order_month_table
GROUP BY first_order_month
ORDER BY first_order_month)

select
	first_order_month,
    round(month0 * 100 / month0, 2) as month0,
    round(month1 * 100 / month0, 2) as month1,
    round(month2 * 100 / month0, 2) as month2,
    round(month3 * 100 / month0, 2) as month3,
    round(month4 * 100 / month0, 2) as month4,
    round(month5 * 100 / month0, 2) as month5,
    round(month6 * 100 / month0, 2) as month6,
    round(month7 * 100 / month0, 2) as month7,
    round(month8 * 100 / month0, 2) as month8,
    round(month9 * 100 / month0, 2) as month9,
    round(month10 * 100 / month0, 2) as month10,
    round(month11 * 100 / month0, 2) as month11
from cohort_chart

 

• step 1 : 첫번째 구매일 구하기

유저ID를 기준으로 min(결제일), 즉 최소 결제일을 구해준다.

• step 2 : 첫번째 구매일과 원본 테이블 조인시키기

[유저 ID, 최소 결제월, 일반 결제월] 구조의 테이블을 만들기 위해 조인을 해준다.

• step 3 : 첫번째 구매일을 기준으로 그룹화하여 월별 리텐션 구하기

최초 결제월을 기준으로 그룹화를 하여 카운트한다.

최초 결제월의 유저 수를 count 하면 그게 month0이 된다.

그리고 최초 결제월로부터 + 1달인 일반 결제월의 유저 수를 count 하면 month 1이 된다.

같은 방법으로 month11까지 구해준다!

 

결과 테이블

 

 

SQL에서 바로 이렇게 리텐션 차트를 그려줄 수 있다.

물론 시각화 툴을 활용해서 그려주는 게 훨씬 가독성도 있고 관리하기 쉬울 것이다.