마티니 Martinee

가이드
인사이트
Full FunnelPerformanceGrowthCRM
이벤트Creatives마티니 만나보기
GROWTH

[코호트분석] 올바른 코호트 분석 방법

달력 단위 코호트 vs life time 단위 코호트

이재현
|
August 30, 2024

이 글을 읽고 계시다면 코호트 분석을 이미 하고 계실 건데 측정 기준에 대해서 의문이 생기신 분이 보실 것 같네요

구글에 '코호트 분석 SQL' 라고 검색하면 정말 많은 글들이 많습니다.

‍

글에서 소개하는 쿼리 예시는 대부분 datediff함수를 활용해서 Date Granularity를  계산합니다.

‍

이해하기 쉽게 예를 들어보겠습니다. 

‍

유저 1 : 23:30 에 회원가입 후 다음날 다시 들어왔습니다. 

유저 2 : 13:30에 회원가입 후 다음날 다시 들어왔습니다.

‍

day 단위로 계산을 하면 유저 1 은 우리 서비스를 30분 경험하고 다음날 재방문했다고 계산됩니다. 

유저 2는 약 10시간 30분 서비스를 경험하고 재방문을 했다고 계산됩니다. 

‍

동일한 조건일까요? 그렇지 않죠? 

‍

만일 시간 단위로 계산을 하게 되면 특정 행동을 수행한 시간부터 다음 행동까지의 Time window를 24시간 뒤로 하면 이 유저는 다음날이 아닌 모레 재방문했다고 계산되겠죠? 

‍

DATEDIFF( [first_event_dt], [second_event], DAY )

‍

DATEDIFF( [first_event_dt], [second_event], HOUR ) / 24 )

 

월단위로 계산할 때도 마찬가지입니다.

 

월별 일자수가 모두 다릅니다. 1월(31일), 2월(28일), 4월(30일)... 

 

월 단위로 측정할 때도 30일로 모두 통일해줍니다. 

‍

DATEDIFF( [first_event_dt], [second_event], HOUR ) / 24  * 30)

 

이렇게 계산되면 유저별로 경과 시간은 모두 통일 되었습니다!!

 

실제로 Amplitude(앰플리튜드)의 코호트 분석 기능에는 이런 기능들이 존재합니다.  만약 안 쓰고 계시다면 직접 쿼리를 날려서... 

 

여기 가이드를 보시면 앰플리튜드가 24시간 단위로 경과 시간을 측청 하는 방식을 설명해 두었습니다. 

Amplitude 24시간 단위 경과 시간 측정 방식 유저 가이드 북마크 이밎

‍

‍

 

앰플리튜드 24시간 단위 경과 시간 설정 화면
앰플리튜드 화면 캡쳐

 

24시간 윈도우 기준, 캘린더 기준으로 경과 시간(t)을 측정하는 옵션이 있죠? 

 

얼마나 차이를 보였는지 가상의 데이터로 확인을 해보았습니다. 

 

(참고로 더미 데이터는 kaggle 이나 Mockaroo 에서 생성하실 수 있습니다)

 

참고로 해당 데이터 계산 기준은 월별 첫 구매 기준 재구매율입니다. 

 

월별 첫 구매 기준 재구매율 기준 리텐션 비교

 

t = 1 지점부터 차이를 보이기 시작하는데 t = 0 이 100%라서 차이가 잘 안 보입니다. 로그 스케일을 통해 다시 확인해 보면 

 

월별 첫 구매 기준 재구매율 기준 리텐션 비교, 로그 스케일로 확인한 그래프

 

확실히 달력 기준의 리텐션율이 조금 더 높아 보이네요 

 

얼마나 차이 나는지 두 기준의 리텐션율을 나눠 보겠습니다 최대  1.27배까지 납니다. (아래 차트에서는 0은 무시합니다. t = 0 은 100%이기 때문에)

 

t = 1 : 1.15배

t = 22 : 1.27배

 

달력(day) 기준과 24시간 기준 리텐션율

 

데이터에 따라서 차이가 달라지겠지만 

 

코호트의 기준이 만일 회원가입일 기준의 재구매율이거나 회원가입일 기준 재방문율을 측정한다면 더 많은 차이를 보일 수 있을 걸로 예상됩니다. 

 

제가 사용한 쿼리는 아래와 같습니다.

‍

WITH tb_pay_first AS (
        SELECT country
                ,user_id
                ,min(pay_datetime_id) first_pay_datetime_id
        FROM order
        GROUP BY 1,2

)

, tb_base_ AS (SELECT  st0.*
                    , FLOOR(TIMESTAMPDIFF(HOUR, st1.first_pay_datetime_id, st0.pay_datetime_id) / 24) AS days_since_first_pay
                    , FLOOR(TIMESTAMPDIFF(HOUR, st1.first_pay_datetime_id, st0.pay_datetime_id) / (24 * 30)) AS months_since_first_pay_period_24h
                    , (YEAR(pay_datetime_id) - YEAR(first_pay_datetime_id)) * 12 + (MONTH(pay_datetime_id) - MONTH(first_pay_datetime_id)) AS months_since_first_pay_period_day
                    , st1.first_pay_datetime_id
            FROM order st0
            LEFT JOIN tb_pay_first st1
                ON st0.user_id = st1.user_id
                AND st0.country = st1.country
            WHERE 1 = 1

)

, tb_base_24h AS (
                SELECT time_id_
                        , country
                        , since_time_period_24h
                        , CASE 
                            WHEN 'acc' = 'normal' THEN SUM(SUM(IF(since_time_period_24h = max_since_time_period_24h, repurchase_user_cnt, 0))) OVER 
                                (PARTITION BY time_id_, country ORDER BY since_time_period_24h DESC RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
                            ELSE SUM(repurchase_user_cnt)
                            END AS repurchase_user_cnt
                        , count(1) pay_user_cnt
                        , sum(sales) AS sales

                FROM   (SELECT *
                                , CASE WHEN since_time_period_24h = 0 AND pay_cnt > 1 THEN 1
                                        WHEN since_time_period_24h = 0 AND pay_cnt <= 1 THEN 0
                                        ELSE 1 
                                    END AS repurchase_user_cnt
                                , MAX(since_time_period_24h) OVER (PARTITION BY country, user_id) as max_since_time_period_24h
                        FROM
                                (SELECT tmp0.time_id_
                                        , tmp0.country
                                        , tmp0.since_time_period_24h 
                                        , tmp0.user_id
                                        , SUM(tmp0.pay_cnt) AS pay_cnt
                                        , SUM(tmp0.sales) AS sales
                                FROM 
                                        (SELECT DATE_FORMAT(first_pay_datetime_id ,'%Y-%m-01') time_id_
                                                , country
                                                -- , months_since_first_pay_period_day AS since_time_period_day
                                                , months_since_first_pay_period_24h AS since_time_period_24h
                                                , user_id
                                                , COUNT(distinct order_id) AS pay_cnt
                                                , SUM(sales) as sales
                                        FROM tb_base_
                                        -- WHERE DATE_FORMAT(first_pay_datetime_id ,'%Y-%m-01') >= '2023-01-01'
                                        GROUP BY 1,2,3,4) tmp0
                                GROUP BY tmp0.time_id_
                                        , tmp0.country
                                        , tmp0.since_time_period_24h 
                                        , tmp0.user_id
                                ) tmp
                                    ) tmp1
                GROUP BY time_id_ 
                        , country
                        , since_time_period_24h
)

, tb_base_day AS (
                SELECT time_id_
                        , country
                        , since_time_period_day
                        , CASE 
                            WHEN 'acc' = 'normal'  THEN SUM(SUM(IF(since_time_period_day = max_since_time_period_day, repurchase_user_cnt, 0))) OVER 
                                (PARTITION BY time_id_, country ORDER BY since_time_period_day DESC RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
                            ELSE SUM(repurchase_user_cnt)
                            END AS repurchase_user_cnt
                        , count(1) pay_user_cnt
                        , sum(sales) AS sales
                FROM   (SELECT *
                                , CASE WHEN since_time_period_day = 0 AND pay_cnt > 1 THEN 1
                                        WHEN since_time_period_day = 0 AND pay_cnt <= 1 THEN 0
                                        ELSE 1 
                                    END AS repurchase_user_cnt
                                , MAX(since_time_period_day) OVER (PARTITION BY country, user_id) as max_since_time_period_day
                        FROM
                                (SELECT tmp0.time_id_
                                        , tmp0.country
                                        , tmp0.since_time_period_day 
                                        , tmp0.user_id
                                        , SUM(tmp0.pay_cnt) AS pay_cnt
                                        , SUM(tmp0.sales) AS sales
                                FROM 
                                        (SELECT DATE_FORMAT(first_pay_datetime_id ,'%Y-%m-01') time_id_
                                                , country
                                                , months_since_first_pay_period_day AS since_time_period_day
                                                -- , months_since_first_pay_period_24h AS since_time_period_24h
                                                , user_id
                                                , COUNT(distinct order_id) AS pay_cnt
                                                , SUM(sales) as sales
                                        FROM tb_base_
                                        GROUP BY 1,2,3,4) tmp0
                                GROUP BY tmp0.time_id_
                                        , tmp0.country
                                        , tmp0.since_time_period_day 
                                        , tmp0.user_id
                                ) tmp
                                    ) tmp1
                GROUP BY time_id_ 
                        , country
                        , since_time_period_day
)


, cohort_base_24h AS 
                (SELECT time_id_
                    , country 
                    , since_time_period_24h
                    , repurchase_user_cnt
                    , pay_user_cnt
                    , sales
                    , SUM(sales) OVER w AS acc_sales
                    , FIRST_VALUE(pay_user_cnt) OVER w AS cohort_user_cnt
                    , COUNT(1) OVER (PARTITION BY country) AS cohort_cnt
                FROM tb_base_24h
                WINDOW w AS (PARTITION BY time_id_, country ORDER BY since_time_period_24h RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
                )
)

, cohort_base_day AS 
                (SELECT time_id_
                    , country 
                    , since_time_period_day
                    , repurchase_user_cnt
                    , pay_user_cnt
                    , sales
                    , SUM(sales) OVER w AS acc_sales
                    , FIRST_VALUE(pay_user_cnt) OVER w AS cohort_user_cnt
                    , COUNT(1) OVER (PARTITION BY country) AS cohort_cnt
                FROM tb_base_day
                WINDOW w AS (PARTITION BY time_id_, country ORDER BY since_time_period_day RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
                )
)


SELECT *
    , (pay_user_cnt * 100) / cohort_user_cnt AS retention_rate
FROM
            (SELECT 1 AS time_id
                    , country
                    , since_time_period_24h
                    , SUM(repurchase_user_cnt) AS repurchase_user_cnt
                    , SUM(pay_user_cnt) AS pay_user_cnt
                    , FIRST_VALUE(sum(cohort_user_cnt)) OVER(PARTITION BY country RANGE BETWEEN UNBOUNDED PRECEDING and CURRENT ROW) as cohort_user_cnt
            FROM cohort_base_24h
            GROUP BY 1
                    , country
                    , since_time_period_24h) tmp3

정리해 보면!

분석의 기준은 굉장히 중요합니다. 어떤 기준으로 분석하느냐에 따라서 의사결정 방향이 달라질 수 있겠죠? 

 

코호트 분석은 시간을 계산해서 집계하는 분석인만큼 시간의 기준을 제대로 설정하는 게 중요합니다. 

 

Text Color Change 달력 단위의 코호트 측정이 조금 더 실제로 과대 평가되기 때문에 유저별로 통일된 24시간 기준 으로 계산하여 코호트를 측정하는 것이 올바른 방법이라고 생각이 됩니다. 

 

저도 실제로 분석해 보면서 분석 기준의 중요성을 다시 한번 깨달을 수 있었습니다.

 

혹시 지금 day 단위로 코호트 활용해 재구매율을 측정하고 계신다면 24시간 단위로 계산해 보시면 어떨까요? 

 

 

Reference

https://medium.com/@paul.levchuk/how-to-build-user-cohort-properly-b70a74e5e1c8

이재현
Growth Manager
InstagramFacebookLinkedIn
데이터 리터러시 향상을 위한 환경을 구축하고, 데이터 분석 능력을 강화하여, 개인과 조직의 성장에 기여합니다.

Related

recomm_thumbnail
EVENT

[Review] 원시그널 Surge Seoul 2025

April 23, 2025

recomm_thumbnail
GROWTH

그로스마케팅과 AARRR 퍼널 분석 (ft. 29CM)

June 20, 2024

recomm_thumbnail
CRM

클릭을 부르는 CRM 카피라이팅 4가지 법칙

April 26, 2024

NewsCareers개인정보처리방침

©Martinee.io All Rights Reserved.