MYSQL로 코호트 분석, 리텐션 분석하기

코호트 분석? 리텐션 분석?

코호트 분석은 시점을 기준으로 특정 행태의 유저를 분석하는 방법입니다.

서비스 이용이나 상품 구매, 앱 가입, 웹 첫방문, 캠페인 유입 등 정의할 수 있는 특정 행태를 보인 유저들을 묶어서 일, 주, 월 등과 같은 시점 기준으로 패턴을 확인합니다. 

 

보통 코호트는 리텐션 분석에서 많이 사용되고 있는데요.

리텐션 분석은 앞서 정의한 행태들을 반복적으로 보이는지를 분석하는 것입니다.

서비스 재이용, 재구매로 정의할 수 있는 특정 행태를 앞서와 마찬가지로 일,주,월 단위에서 계속적으로 지속하고 있는지를 확인합니다.

이를 위해선 언제 맨 처음 해당 행위가 발생했는지를 알아야 하고 그 이후에 같은 행위가 어떤 시점에 발생했는지도 알아야 합니다.

 

아래 예시는 서비스 이용을 완료한 달을 기준으로 코호트 분석을 하기 위한 쿼리입니다.

코호트 분석, 리텐션 확인용 쿼리

-- 1. 코호트 대상자의 첫 사용월을 정의 
WITH cohort AS (
	SELECT user_id, MIN(date_format(start_at,'%Y-%m-01')) 'first_use_month'
    FROM `schedule`
    WHERE status = 'completed'
    GROUP BY user_id
) 
-- 2. 코호트 대상자의 첫 사용월과 사용 이력을 비교하여 month_number를 부여 
,activity AS (
	SELECT 
		c.user_id
        , c.first_use_month
        , date_format(s.start_at,'%Y-%m-01') 'use_date'
        , TIMESTAMPDIFF(MONTH, c.first_use_month, date_format(s.start_at,'%Y-%m-01')) AS month_number
	FROM `cohort` c 
    JOIN `schedule` s on s.user_id = c.user_id
    WHERE s.status = 'completed'
)
-- 3. 첫 이용월과 실제 년월로 액티브 유저수를 그룹핑 함
,cohort_retention AS (
	SELECT 
		first_use_month
        , month_number
		, COUNT(distinct user_id) 'active_users'
	FROM activity
	GROUP BY first_use_month, month_number
)

-- 4. 조건문으로 M0부터 시작해 1년치 코호트를 기록함
SELECT
    first_use_month,
	max(CASE WHEN month_number = 0 THEN active_users END) 'M0',
    max(CASE WHEN month_number = 1 THEN active_users END) 'M1',
	max(CASE WHEN month_number = 2 THEN active_users END) 'M2',
    max(CASE WHEN month_number = 3 THEN active_users END) 'M3',
    max(CASE WHEN month_number = 4 THEN active_users END) 'M4',
    max(CASE WHEN month_number = 5 THEN active_users END) 'M5',
    max(CASE WHEN month_number = 6 THEN active_users END) 'M6',
    max(CASE WHEN month_number = 7 THEN active_users END) 'M7',
    max(CASE WHEN month_number = 8 THEN active_users END) 'M8',
    max(CASE WHEN month_number = 9 THEN active_users END) 'M9',
    max(CASE WHEN month_number = 10 THEN active_users END) 'M10',
    max(CASE WHEN month_number = 11 THEN active_users END) 'M11'
FROM cohort_retention
GROUP BY first_use_month
ORDER BY first_use_month;

 

1. 특정 유저가 맨 처음 이용한 달 정보를 얻기 위해 처음 스케줄을 완료한 날짜 정보를 추출합니다.

2. 특정 유저가 맨 처음 이용한 달 정보는 그대로 두고 그 이후에 이용한 스케줄들의 정보를 전부 가져오면서 month_number를 생성합니다.

3. 처음 이용한 달과 month_number를 그룹핑해서 유니크 사용자 수를 셉니다.

4. 마지막으로 특정 월에 이용한 유저 기준으로 +n개월에 이용했는지를 아래처럼 만들어줍니다.

 

꿀팁

M11까지만 구했지만, 만약에 더 긴 기간을 보고 싶으면 max(case when ~ 이 부분에서 month_number 값과 M11 이 부분만 고치면 되는데요. GPT에 원하는 만큼 만들어달라고 요청하면 금방 만들어줍니다. 사람이 하나하나 적을 때보다 훨씬 빨라요 ㅋㅋㅋ

옛날에는 저 구문 매번 적는게 싫어서 파이썬으로 했었는데, 지금은 GPT가 금방 해주니까 MYSQL로 만들어도 편한 것 같습니다.