SQL을 활용한 코호트 분석Cohort Analysis

SQL을 활용한 Cohort 분석 방법에 대해 정리한 글입니다.

 

Cohort 분석이란?

Cohort 분석은 흔히 '동질집단분석'으로 알려져 있습니다.

특정 기간에 같은 특성이나 경험을 공유하는 그룹을 나누어 분석하는 행동 분석 방법의 하나입니다.

 

쿠팡에서 발표한 Cohort 자료를 예로 들어보겠습니다. 

 

• 2019년에 가입한 고객들의 거래액은 가입한 2019년과 비교할 때 2020년에 2.19배로 증가했습니다. 2019년에 100만원어치 구매를 한 고객들이 2020년에 219만원어치 구매를 했다고 볼 수 있습니다. 쿠팡에 대한 신뢰도가 높아지면서 객단가가 높은 상품을 구매했거나, 또는 다른 채널에서 구매하던 상품도 쿠팡을 통해서 구매하는 등 긍정적인 변화가 있음을 알 수 있습니다. 
• 또한 거래액이 2배, 3배가 되기까지 소요되는 시간이 짧아지고 있습니다. 2016년에 가입한 고객군의 거래액이 3배 정도로 성장하기까지 약 4~5년 정도 소요됐음을 알 수 있습니다. 하지만 2018년에 가입한 고객군은 3년이 되었을 때 거래액이 3배가 됐음을 알 수 있습니다. 고객의 구매액 증가속도가 점점 빨라지고 있음을 알 수 있습니다.

 

Spend by cohort, indexed to year 1 ⓒ Coupang

 

저희는 다른 서비스를 활용하다가, 저희 서비스를 활용한 고객들의 거래액 성장이 어떻게 이루어지는지 확인이 필요했습니다. B2B 고객의 온보딩과 성장이 제대로 이루어지고 있는지에 대한 확인이 필요했습니다.

따라서 이런 현황을 파악하기 위해서, Cohort를 활용한 접근이 적합하다고 판단했습니다.

 

 

SQL을 활용한 Cohort 분석하기

(1) 기본 테이블 확인

우선 기본 테이블(payment)의 구성은 다음과 같습니다.

이 중 유저 아이디, 거래액, 발생한 시점 칼럼을 활용합니다.

 

※ payment 테이블

transaction_date	user_id	amount (거래액)
2022-05-08	somsung	20000
2022-04-03	somsung	30000
2022-04-06	kiah	10000
2022-03-30	kokoa	3000

 

이전한 시점을 기준으로 거래액 변화를 확인해야 합니다.

따라서 이전일이 포함된 테이블(transform_user_info)을 활용했습니다.

 

※ transform_user_info 테이블

transform_date	user_id
2022-04-02	somsung
2022-04-01	kiah

 

(2) 출력 희망 형태

 

위의 쿠팡처럼 가입한 시점의 거래액과 비교한 성장을 확인할 예정입니다. 동시에 각 시점별 가입(이전) 당월 거래액이 어느 정도인지 함께 확인할 수 있도록 아래와 같은 산출물을 만드려고 합니다.

 

※ 목표 결과물

가입월	YEAR 1 거래액	YEAR 1 (가입당월)	YEAR 2	YEAR 3
2022년 3월	30000	1.00	1.37	1.80
2022년 4월	50000	1.00	1.98
2022년 5월	70000	1.00

 

(3) 쿼리 추출

• Cohort의 기간을 월 단위로 분석하기 때문에, 우선은 월별 거래액을 합쳐주도록 합니다.
  • 거래액이 발생한 '월'에 대한 정보가 필요하기 때문에 EXTRACT를 활용하여 '월' 추출하는 방법도 떠올릴 수 있습니다. 하지만, 연도가 변화하는 시점의 계산 등이 번거로워 질 수 있다는 생각 때문에 toStartOfMonth를 활용했습니다.
• 이전일을 가진 테이블을 JOIN 합니다. 그리고 다른 서비스에서 인전한 날짜인 transform_date 역시 toStartOfMonth를 활용해서 월 초일로 형태를 변경합니다.
• 이렇게 두 테이블을 JOIN해서 거래가 발생한 월(trasaction_month), 서비스 이전이 발생한 월(transform_month)가 구해졌다면 이제 두 날짜 사이의 '월 기준' 차이를 구합니다. DATE_DIFF 함수를 활용해서 산출합니다.  

SELECT
	toStartOfMonth(trasaction_date) as trasaction_month
	,toStartOfMonth(transform_date) as transform_month 
	,user_id
	,DATEDIFF(MONTH, transform_month, trasaction_month) as gap
	,SUM(amount) as monthly_amount
FROM
	payment a
--- 회원가입일 테이블 JOIN ---
GLOBAL JOIN
	transform_user_info b
ON a.user_id = b.user_id
GROUP BY
	t, mall_id

 

※ 쿼리에 대한 결과물

trasaction_month	transform_month	user_id	gap	monthly_amount
2022-05-01	2022-04-01	somsung	1	20000
2022-04-01	2022-04-01	somsung	0	30000
2022-04-01	2022-04-01	kiah	0	10000

 

• 이렇게 산출한 결과물을 하나의 테이블로 활용하여 CASE WHEN을 적용합니다.
  • 월별 차이를 나타내는 gap 값이 0이라면 가입(이전)한 당월의 거래액(=YEAR 1)이 출력됩니다.
  • gap 값이 1이라면 가입한 다음달(=YEAR 2)의 거래액이 출력됩니다.

SELECT
	transform_month
	,SUM(CASE WHEN gap = 0 THEN monthly_amount ELSE NULL END) as YEAR1_GMV
	,1.00 AS YEAR_1
	,SUM(CASE WHEN gap = 1 THEN monthly_amount ELSE NULL END)/YEAR1_GMV as YEAR_2
	,SUM(CASE WHEN gap = 2 THEN monthly_amount ELSE NULL END)/YEAR1_GMV as YEAR_3

FROM
	(
    SELECT
        toStartOfMonth(trasaction_date) as trasaction_month
        ,toStartOfMonth(transform_date) as transform_month 
        ,user_id
        ,DATEDIFF(MONTH, transform_month, trasaction_month) as gap
        ,SUM(amount) as monthly_amount
    FROM
        payment a
    --- 회원가입일 테이블 JOIN ---
    GLOBAL JOIN
        transform_user_info b
    ON a.user_id = b.user_id
    GROUP BY
        t, mall_id
    )
GROUP BY
	transform_month
ORDER BY
	transform_month

※ 결과물

trasaction_month	YEAR1_GMV	YEAR_1	YEAR_2	YEAR_3
2022-03-01	30000	1.00	1.25	1.29
2022-04-01	40000	1.00	0.50
2022-05-01	50000	1.00	1.24

 

이 글이 도움이 되셨다면 하단의 (♥) 클릭을 부탁드립니다.