1. 리텐션이란?
리텐션
일정 기간이 지난 뒤에도 사용자가 서비스를 계속 이용하거나 재방문하는 비율을 나타내는 핵심 지표.
리텐션율의 공식
(기간 종료 시점 고객 수 − 해당 기간 내 신규 고객 수) / 기간 시작 시점 고객 수
리텐션율은 기간 시작 시점의 고객 수를 기준으로, 유지된 고객 수를 나누어 계산한다.
이때 '유지된 고객'에는 해당 기간 동안 새롭게 유입된 고객은 포함하지 않아야 한다.
2. 리텐션 계산 문제
https://platform.stratascratch.com/coding/2053-retention-rate?code_type=1
전체 테이블 구조 :
| record_date | account_id | user_id |
| 2021-01-01 | A1 | U1 |
| 2021-01-01 | A1 | U2 |
| 2021-01-06 | A1 | U3 |
| 2021-01-02 | A1 | U1 |
| 2020-12-24 | A1 | U2 |
| 2020-12-08 | A1 | U1 |
| 2020-12-09 | A1 | U1 |
| 2021-01-10 | A2 | U4 |
| 2021-01-11 | A2 | U4 |
| 2021-01-12 | A2 | U4 |
| 2021-01-15 | A2 | U5 |
| 2020-12-17 | A2 | U4 |
| 2020-12-25 | A3 | U6 |
| 2020-12-25 | A3 | U6 |
| 2020-12-25 | A3 | U6 |
| 2020-12-06 | A3 | U7 |
| 2020-12-06 | A3 | U6 |
| 2021-01-14 | A3 | U6 |
| 2021-02-07 | A1 | U1 |
| 2021-02-10 | A1 | U2 |
| 2021-02-01 | A2 | U4 |
| 2021-02-01 | A2 | U5 |
| 2020-12-05 | A1 | U8 |
3. 문제 해설
이 문제에 대한 해설이다.
- 2020년 12월의 모든 활성 사용자를 찾고, 2021년 1월도 똑같이 진행한다. ( = 12월과 1월 리텐션에 대한 분모)
- 사용자 별 가장 최근 활동 날짜를 찾는다. ( = 사용자가 12월,1월 이후 활동이 있었는지 파악)
- 12월, 1월의 모든 활성 사용자에 대해서 최근 활동 날짜를 조인한다.
( = 남아 있는 사용자 수 파악 가능
= 12월 이후 활동한 사용자 수를 계산하고 이를 12월 사용자 수로 나누면 12월 리텐션을 구할 수 있다.)- account_id (계정_id)로 통합하여 계정별 리텐션의 비율을 구한다.
1. 2020년 12월에 활동한 사용자 찾기
WITH dec_2020 AS
(SELECT DISTINCT account_id,
user_id
FROM sf_events
WHERE EXTRACT(MONTH
FROM date) = 12
AND EXTRACT(YEAR
FROM date) = 2020 ),
2020년 12월에 활동한 사용자의 account_id와 user_id를 찾는다.
이때, 동일한 유저가 중복 카운팅되는 것을 방지하기 위해서 DISTINCT 사용.
2. 2021년 1월에 활동한 사용자 찾기
jan_2021 AS
(SELECT DISTINCT account_id,
user_id
FROM sf_events
WHERE EXTRACT(MONTH
FROM date) = 1
AND EXTRACT(YEAR
FROM date) = 2021 ),
12월과 동일하게 계산
3. 유저별 최신 활동 날짜 찾기
max_date AS
(SELECT user_id,
MAX(Date) AS max_date
FROM sf_events
GROUP BY user_id),
유저별로 가장 최근 활동 날짜를 구한다.
4. 12월 retention 계산하기
retention_dec_2020 AS
(SELECT account_id,
SUM(CASE
WHEN max_date > '2020-12-31' THEN 1.0
ELSE 0
END) / COUNT(*) * 100.0 AS retention_dec
FROM dec_2020
JOIN max_date ON dec_2020.user_id = max_date.user_id
GROUP BY account_id),
1) 12월 활성 사용자에 대해서 각 사용자의 최신 활동 날짜를 조인한다.
(이때, 12월 사용자의 테이블을 기준으로 조인했기 때문에, 신규 가입자는 자연스레 고려되지 않는다.)
FROM dec_2020
JOIN max_date ON dec_2020.user_id = max_date.user_id
2) 최신 활동 날짜가 12월 31일 이후라면, 해당 사용자는 12월 이후에 활동을 한 것으로 간주한다.
WHEN max_date > '2020-12-31'
3) CASE절을 사용하여, 12월 이후 활동한 사용자는 1로 표시하고 활동이 없는 사용자는 0으로 표시한 뒤, 총합을 구한다.
=> 12월 이후 활동한 사용자의 수를 구할 수 있다.
SUM(CASE
WHEN max_date > '2020-12-31' THEN 1.0 ELSE 0
END)
A1에서는 U1, U2가, A2에서는 U4, A3에서는 U6가 12월 이후에도 활동했다.
4) 이 수를 12월 전체 사용자 수로 나누어 리텐션을 구한다.
SUM(CASE
WHEN max_date > '2020-12-31' THEN 1.0
ELSE 0
END) / COUNT(*) * 100.0 AS retention_dec
A1에서는 원래 U1, U2, U8 3명이 12월에 활동했으며,
A2에서는 U4 1명,
A3에서는 U6, U7 2명이 활동했다.
5. 1월 retention 계산하기
retention_jan_2021 AS
(SELECT account_id,
SUM(CASE
WHEN max_date > '2021-01-31' THEN 1.0
ELSE 0
END) / COUNT(*) * 100.0 AS retention_jan
FROM jan_2021
JOIN max_date ON jan_2021.user_id = max_date.user_id
GROUP BY account_id)
12월과 동일하게 계산한다.
6. 12월과 1월의 retention 비율 구하기
SELECT retention_jan_2021.account_id,
retention_jan / retention_dec AS retention
FROM retention_jan_2021
INNER JOIN retention_dec_2020 ON retention_jan_2021.account_id = retention_dec_2020.account_id
12월과 1월의 account_id별 리텐션을 구했으니, 이제 비율을 구한다.
⚠️그런데, 해당 블로그에서 마지막 비율을 구하는 쿼리는 만약 12월 리텐션이 0이 나온다면, 분모에 0이 들어가므로 계산이 불가능하다. 또한 12월 리텐션이 0이 나오는 경우에는 값을 0으로 출력하라는 문제 요구사항에 어긋난다.
따라서 쿼리를
SELECT retention_jan_2021.account_id,
IF(retention_dec <> 0, retention_jan / retention_dec, 0) AS retention
FROM retention_jan_2021
INNER JOIN retention_dec_2020 ON retention_jan_2021.account_id = retention_dec_2020.account_id
이렇게 수정하거나 WHEN절을 사용하면, 요구사항을 충족하는 답이 나올것이다.