[MySQL] Retention & Cohort Analysis

이번 포스팅에서는 MySQL 쿼리문을 이용하여 다양한 종류의 Retention을 산출하는 방법에 대해 설명해보려고 합니다. 이를 설명하기 이전에 우선 간략하게 Retention, 그리고 이와 밀접한 관계가 있는 Cohort Analysis에 대해 먼저 설명하겠습니다.

 

1. Retention 이란?

아마 고객 데이터 분석에 대해 관심이 있다면 Retention에 대해 들어본적이 있을꺼라고 생각합니다. Retention은 도메인을 막론하고 고객 분석을 진행할 때 대부분의 스타트업에서 매우 중요한 지표입니다. Retention을 통해 서비스를 지속적으로 이용하는 고객의 비중을 알 수 있습니다. 일반적으로 신규 고객을 유입시키는 비용보다 기존 고객을 유지하는 비용이 낮기때문에 기존 고객 유지와 관련되어 있는 Retention은 더더욱 중요합니다.

 

고객들이 서비스를 사용하는 주기성 및 서비스의 특성에 따라 어떠한 Retention 공식을 사용할지 정해야합니다. Retention을 구하는 대표적인 방법 3가지는 아래와 같습니다.

 

1. N-Day Retention: 가입 이후 특정 날에 active한 회원 비중
2. Unbounded Retention: 가입 이후 중 특정 날 또는 특정 날 이후 active한 회원 비중
3. Bracket Retention: 가입 이후 특정 기간(주, 월, 분기, 등등)에 active한 회원 비중

3가지 방식 중 서비스에 가장 적합한 retention을 사용하면 됩니다. 모바일 게임처럼 매일 접속해야 의미가 있는 서비스의 경우 N-Day Retention이 가장 적합한 방식이고, 생필품 쇼핑몰처럼 특정 주기에 접속 & 구매를 해야 의미가 있는 서비스의 경우 그 주기에 알맞은 Bracket Retention이 가장 적합한 방식입니다. 방금의 예시에서 이미 눈치채셨을지 모르겠지만 모바일 게임의 경우 active한 기준을 접속 이라고 정의하였고, 생필품 쇼핑몰의 경우 접속 & 구매 라고 정의 했습니다. 이처럼 서비스별로 사용할 Retention 방식뿐만 아니라 active한 유저의 기준도 정의해야지 더욱 효과적인 분석을 할 수 있습니다.

 

2. Cohort Analysis 란?

Cohort Analysis를 들어보신적이 없는 분들도 아마 코호트(Cohort)라는 단어는 2020년 이후에 코로나 때문에라도 들어보셨을 거라고 생각합니다. 코로나 초기에 코호트 격리라는 단어를 뉴스를 통해 쉽게 볼 수 있었는데 바로 그 코호트가 Cohort Analysis의 코호트 입니다. 코호트에 대한 위키피디아의 정의는 아래와 같습니다.

특정한 기간에 태어나거나 결혼을 한 사람들의 집단과 같이 통계상의 인자(因子)를 공유하는 집단

 

즉, 코호트 분석은 유사한 성격을 지니는 집단(코호트)별로 분석을 하는 방법입니다. Cohort Analysis와 retention이 밀접한 관계를 지니는 이유는 Cohort별로 Retention을 보면 집단별로 Retention의 차이를 알 수 있기 때문에 더욱 유의미한 분석이 될 수 있기 때문입니다. 가장 대표적인 방법으로는 특정 시점에 가입 및 유입된 유저들의 Retention을 보는 방식의 Cohort Retention이 있습니다. 이렇게 글로만 보면 이해가 어려우니 제 블로그에 연결되어 있는 Google Analytics에서 제공하는 Cohort Analysis 부분을 참고하면 아래와 같습니다.(개인 블로그이다 보니 Retention은 처참합니다.)

 

개인 블로그 cohort retention 표

 

이제 다음 부분에서는 쿼리문으로 Retention을 산출하는 방식에 대해 정리해보겠습니다.

 

3. N-Day Retention & Bracket Retention Query

우선 가장 기본적인 N-Day Retention을 구하기 위해서는 아래와 같은 테이블이 필요합니다.

JOIN_DATE	USER_ID	ACTIVE_DATE
2022-01-01	A	2022-01-01
2022-01-01	B	2022-01-01
2022-01-01	A	2022-01-02
2022-01-01	A	2022-01-04
2022-01-02	C	2022-01-02
...	...	...

• JOIN_DATE: 가입일
• USER_ID: 유저 아이디
• ACTIVE_DATE: 접속일(편의상 active한 기준을 접속으로)

 

이를 이용하여 N-Day Retention을 구하기 위해서는 아래와 같은 쿼리문을 이용하여 구할 수 있습니다. 만약 위와 같은 테이블이 없다면 서브쿼리나 WITH AS를 이용하여 위와 같은 테이블 생성 후에 실행시키면 됩니다.

SELECT
  TIMESTAMPDIFF('DAY', JOIN_DATE, ACTIVE_DATE) AS N,
  COUNT(DISTINCT(USER_ID)) AS active_user_cnt,
  ROUND(COUNT(DISTINCT(USER_ID)) / (SELECT COUNT(DISTINCT(USER_ID)) FROM table), 2) AS N-Day_Retention
FROM table
GROUP BY TIMESTAMPDIFF('DAY', JOIN_DATE, ACTIVE_DATE)
ORDER BY N;

이를 실행시키면 아래와 같은 결과가 나옵니다.

 

N	active_user_cnt	N-Day_Retention
0	758	1.00
1	454	0.60
2	341	0.45
3	319	0.42
4	299	0.39
...	...	...

 

Active한 기준을 접속으로 가정했기 때문에 가입을 위해 접속한 Day 0의 경우 당연히 Retention이 100%입니다. 위 결과의 경우 제가 임의로 입력한 값들이지만 많은 서비스들의 Active 기준을 접속으로 설정한 N-Day Retention을 보면 위와 같이 초반에 급감 후 안정화 되는 추세를 보입니다.

 

기간을 가정하고 계산하는 Bracket Retention의 경우 위의 N-Day Retention 을 산출하기 위한 쿼리문에서 TIMESTAMPDIFF 부분에 DAY 대신 원하는 기간에 대한 인자값을 입력해주면 됩니다. 아래 쿼리문은 Bracket 기간을 한 달로 가정하고 산출할 때 사용 가능한 쿼리문입니다.

SELECT
  TIMESTAMPDIFF('MONTH', JOIN_DATE, ACTIVE_DATE) AS M,
  COUNT(DISTINCT(USER_ID)) AS active_user_cnt,
  ROUND(COUNT(DISTINCT(USER_ID)) / (SELECT COUNT(DISTINCT(USER_ID)) FROM table), 2) AS Bracket_Retention
FROM table
GROUP BY TIMESTAMPDIFF('MONTH', JOIN_DATE, ACTIVE_DATE)
ORDER BY M;

 

4. Cohort Retention Query

코호트 리탠션을 구하기 위해서도 아래와 같은 데이터셋이 필요합니다.

JOIN_DATE	USER_ID	ACTIVE_DATE
2022-01-01	A	2022-01-01
2022-01-01	B	2022-01-01
2022-01-01	A	2022-01-02
2022-01-01	A	2022-01-04
2022-01-02	C	2022-01-02
...	...	...

쿼리문은 다음과 같습니다.

  WITH sub1 AS (
            SELECT
            JOIN_DATE,
            COUNT(DISTINCT(USER_ID)) AS TOTAL_COUNT
            FROM table
            GROUP BY YEAR_MONTH
            ),
       sub2 AS (
            SELECT
            JOIN_DATE,
            TIMESTAMPDIFF('DAY', JOIN_DATE, ACTIVE_DATE) AS DIFFDAY,
            COUNT(DISTINCT(USER_ID)) AS ACTIVE_USER_COUNT
            FROM table
            GROUP BY YEAR_MONTH, DIFFMONTH
            )

SELECT s2.JOIN_DATE,
       s2.DIFFDAY,
       s2.ACTIVE_USER_COUNT,
       s1.TOTAL_COUNT
FROM sub2 s2
JOIN sub1 s1 ON s2.JOIN_DATE = s1.JOIN_DATE
ORDER BY s2.JOIN_DATE, s2.DIFFDAY;

이를 실행시키면 아래와 같은 결과가 나옵니다.

JOIN_DATE	DIFFDAY	ACTIVE_USER_COUNT	TOTAL_COUNT
2022-01-01	0	758	758
2022-01-01	1	454	758
2022-01-01	2	341	758
2022-01-01	3	319	758
...	...	...	...
2022-01-02	0	802	961
2022-01-02	1	781	961
2022-01-02	2	511	961
2022-01-02	3	502	961
...	...	...	...

위의 결과를 이용하여 일자별로 유입된 유저들의 Retention에서 어떤 차이가 있는지를 볼 수 있고 결과적으로 코호트 분석도 가능합니다. 만약 비교를 하고 싶은 집단(코호트)들이 일자별이 아니라면 단순히 위의 쿼리문의 TIMESTAMPDIFF 부분에서 첫번째 인자를 원하는 기간(MONTH, QUARTER, YEAR, 등등)으로 변경해주면 됩니다.

---

References

• 3 Ways To Measure User Retentio
• 코호트 분석(Cohort Analytics)이란?
• SQL을 이용한 리텐션(retention) 구하기