2주차 - SQL로 코호트 분석 해보기(MySQL)

https://ddukbbok-kang.tistory.com/19

2주차 - AARRR, 코호트 분석, 롤링

 

 

위 글에 이어, SQL로 코호트 분석을 해보려고 한다.

수업 때 사용한 데이터는 블로그에 올릴 수 없으므로 캐글에 있는 online retail 데이터를 활용했다!

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

Online Retail Data Set from UCI ML repo

2010년 1월 12일부터 2011년 9월 12일 까지 영국에 기반 비매장 온라인 소매에서 발생한 모든 거래 데이터이며, 나는 csv로 다운받아 DBeaver 테이블로 불러왔다. 그런데 수업 때 했던 데이터와 다른 데이터로 하려다 보니 컬럼들이 달라서 수업 때 배운 코호트 분석 쿼리를 활용할 수가 없었다 ㅠㅠ

그래서 결국 예전에 파이썬으로 구현했던 코호트 분석을 그대로 sql로 구현하고자 했다. 근데 파이썬 코드를 sql로 구현하는 게 쉬운 일이 아니었다.... 그래서 서브쿼리가 3개가 등장한다.. 근데 mysql에는 with문이 없나?ㅠㅠ with문이 작동이 안됐다...

하지만 아무튼 성공 했으니 블로그에 기록을 남겨본다!

 

** 본 내용은 캠프에서 배운 내용을 바탕으로 쿼리를 재구성한 것이며, 수업에서 사용하는 쿼리와 동일하지 않습니다.

 

 

 

미리 말하자면, 왜인지는 모르겠는데 DBeaver에서 with문 작동이 안되어 어쩔 수 없이 서브쿼리를 마구 썼다..
그래서 가독성이 매우 매우 떨어지지만 일단 코호트 분석을 구현했다는 것에 의의를 두고
실무에서는 with 문을 활용하거나 다른 방법을 찾는 게 나을듯 싶다...

SELECT *
FROM online;

데이터는 이런 형태다. 컬럼명을 보면 어떤 컬럼인지 유추가 가능하기 때문에 컬럼명 설명은 생략하겠다 ㅎㅎ

 

SELECT *, DATE_FORMAT(InvoiceDate , '%Y-%m-01') AS event_month
FROM online
WHERE InvoiceDate IS NOT NULL;

InvoiceDate열에서 일자를 1일로 바꿔준 후 'event_month'라는 열로 만들어준다.

 

 

SELECT *, MIN(event_month) OVER(PARTITION BY CustomerID) AS cohort_month
FROM (SELECT *, DATE_FORMAT(InvoiceDate , '%Y-%m-01') AS event_month
		FROM online
		WHERE InvoiceDate IS NOT NULL) sub;

그리고 기존 테이블을 sub 테이블로 하여, CustomerID별로 최초 구매월을 구해 cohort_month 열로 만들어준다.

'CustomerID별'이기 때문에 PARTITIOON BY CustomerID를 써주었고, '최초 구매 월'을 구하고 싶기 때문에 MIN(event_month)를 해주었다.

위 사진에 있는 CustomerID 18287은 2011년 10월 달에도 구매를 했지만, 제일 최초로 구매한 달은 5월이라는 것이다!

 

 

SELECT cohort_month, MONTH(event_month)-MONTH(cohort_month) as cohort_period	
FROM (SELECT *, MIN(event_month) OVER(PARTITION BY CustomerID) AS cohort_month
		FROM (SELECT *, DATE_FORMAT(InvoiceDate , '%Y-%m-01') AS event_month
				FROM online
				WHERE InvoiceDate IS NOT NULL) sub)sub2;

이전 테이블을 sub2 테이블로 하여, event_month 와 cohort_month 달의 차이를 구하여 cohort_period열로 만들어준다. 즉, 구매가 일어난 달과 최초 구매 달의 차이를 구해주는 것이다.

 

 

SELECT cohort_month,
		COUNT(CASE WHEN cohort_period =0 THEN 1 ELSE NULL END) AS "0달 후",
		COUNT(CASE WHEN cohort_period =1 THEN 1 ELSE NULL END) AS "1달 후",
		COUNT(CASE WHEN cohort_period =2 THEN 1 ELSE NULL END) AS "2달 후",
		COUNT(CASE WHEN cohort_period =3 THEN 1 ELSE NULL END) AS "3달 후",
		COUNT(CASE WHEN cohort_period =4 THEN 1 ELSE NULL END) AS "4달 후",
		COUNT(CASE WHEN cohort_period =5 THEN 1 ELSE NULL END) AS "5달 후",
		COUNT(CASE WHEN cohort_period =6 THEN 1 ELSE NULL END) AS "6달 후",
		COUNT(CASE WHEN cohort_period =7 THEN 1 ELSE NULL END) AS "7달 후",
		COUNT(CASE WHEN cohort_period =8 THEN 1 ELSE NULL END) AS "8달 후",
		COUNT(CASE WHEN cohort_period =9 THEN 1 ELSE NULL END) AS "9달 후",
		COUNT(CASE WHEN cohort_period =10 THEN 1 ELSE NULL END) AS "10달 후",
		COUNT(CASE WHEN cohort_period =11 THEN 1 ELSE NULL END) AS "11달 후",
		COUNT(CASE WHEN cohort_period =12 THEN 1 ELSE NULL END) AS "12달 후"
FROM(SELECT cohort_month, MONTH(event_month)-MONTH(cohort_month) AS cohort_period	
		FROM (SELECT *, MIN(event_month) OVER(PARTITION BY CustomerID) AS cohort_month
				FROM (SELECT *, DATE_FORMAT(InvoiceDate , '%Y-%m-01') AS event_month
						FROM online
						WHERE InvoiceDate IS NOT NULL) sub)sub2)sub3
GROUP BY cohort_month;

쿼리가 갑자기 길어졌다 ㅋㅋㅋㅋ

이전 테이블을 sub3 테이블로 하여 피봇테이블을 만들어준다.

cohort_month를 그룹핑하여, cohort_period가 1달, 2달 3달....12달인 것들의 개수를 세주는 것이다.

그러면 위 결과와 같이 코호트 분석의 모양이 나온다!

 

 

좀 더 보기 쉽게 엑셀 히트맵으로 표현해봤다.

1월 달에 구매한 고객의 수가 많아 매우 빨갛게 표시되어, 이 경우를 제외한 다른 곳은 상대적으로 대비가 약해졌다. 그래서 70,294를 제외하고 다시 히트맵을 만들어봤다.

그럼 이걸 보고 어떤 걸 알 수 있고, 앞으로 어떻게 해야할까?

내 주관적인 해석은 다음과 같다.

 

AS-IS

• 1월 달에 구매한 고객의 수가 70,294명으로 제일 많았다. 아마도 이벤트나 프로모션 등으로 인해 유입된 고객의 수가 많았고, 많은 고객이 구매로 이어지지 않았나 예측해볼 수 있다.
• 혹은, 2011년 1월이 해당 서비스를 새로 출시한 달이라 유독 유입된 고객 및 구매 고객이 많았을 수도 있다.
• 신규 구매 고객이 대체로 겨울에 많은 것을 보아 시즌성을 가지는 서비스일지도 모르겠다(12월 제외).
• 1월 코호트가 다른 집단보다 유독 지속적으로 구매하는 것으로 보인다.

 

TO-BE

• 시즌성을 가지는 서비스라면, 한 곳에 집중하면 좋을 것 같다. 아예 겨울 관련 제품들을 밀고 나가 겨울에 구매 고객들을 지금보다 훨씬 많이 유치하던지, 아니면 여름 관련 제품들도 구비하여 겨울뿐만 아니라 여름에도 구매 고객들을 유치하던지 말이다.
• 1월 코호트에 초점을 맞춰 마케팅 전략을 수립해본다. 1월 코호트가 유독 충성 고객 성향을 보이므로, 이들의 구매량을 늘리면 전체적인 매출 증대로 이어질 수 있을 것이다.
• 1월 코호트를 제외한 다른 코호트의 재구매율도 높이는 방안을 모색해야한다. 1월 코호트와 다른 코호트의 중점적인 차별점을 탐구하고 다른 코호트의 재구매를 이끌어낼 요인을 찾아 이를 중심으로 마케팅해야 한다.

 

 

 

 

 

본 내용은 데이터리안 'SQL 데이터 분석 캠프 실전반' 을 수강하며 작성한 내용입니다.

SQL 데이터 분석 캠프에서 제공하는 수업 자료의 저작권은 데이터리안에게 있으며, 자료의 무단 복제 및 배포, 상업적 이용을 금지합니다.