사용자 유지율(Retention rate) 한번에 계산하기

아웃코드 자동화에서 조회화면을 쉽게 만들 수 있는데요, Retention rate 조회화면을 만들어보겠습니다.

‍

"Retention rate"는 비즈니스나 서비스가 특정 기간 동안 얼마나 많은 고객이나 사용자를 유지하는지를 측정하는 지표입니다. Retention rate를 계산하려면 일정 기간 동안의 고객 또는 사용자 접속 기록 데이터가 필요합니다. 유지율(Retention rate)을 계산하는 방법은 간단하게 다음과 같이 요약될 수 있습니다:

1. 선택한 기간 정의: 먼저, 어떤 기간 동안의 유지율을 계산할 것인지 정의해야 합니다. 예를 들어, 주간, 월간 등을 선택할 수 있습니다.
2. 시작 시점 설정: 선택한 기간 내에서 고객이나 사용자가 서비스를 시작한 날짜를 설정합니다. 이를 "시작 시점"이라고 합니다. 이 날짜로부터 고객의 활동을 추적합니다.
3. 유지된 고객 수 계산: 선택한 기간 내에서 시작 시점에서 우리 서비스에 접속한 고객의 수를 계산합니다.
4. 재방문한 고객 수 계산: 시작 시점 이후에도 계속 서비스를 이용한 고객의 수를 계산합니다. 이 수치는 유지된 고객 중에서 재방문한 고객을 나타냅니다.
5. 유지율 계산: 재방문한 고객 수를 유지된 고객 수로 나눈 후, 백분율로 표현하여 유지율을 계산합니다.

‍

‍

데이터베이스에 있는 일자가 포함된 사용자 접속 로그데이터와 사용자 고유 아이디(예를 들어 이메일주소)를 기반으로 산정할 때는 아래와 같은 주의 사항이 필요합니다.

‍

• 하루에 여러번 접속한 사용자도 중복카운팅이 되면 안됩니다.
• 접속 로그가 JSON 형태로 저장되는 경우가 많습니다. 이 경우 접속날짜와 시간을 추출해야합니다.
• 시작시점을 설정합니다. (예를 들어, 10월 1일부터 가입한 사용자의 유지율을 계산)
• 유료와 무료 사용자, 구매전/구매 후 사용자의 유지율이 다른 경우가 많습니다. 이때는 구분하여 유지율을 2개로 구분합니다.

‍

‍

사용하는 데이터소스에 따라 쿼리가 다를 수 있지만 일반적인 MySQL 데이터베이스 쿼리 예시는 다음과 같습니다.

‍

(아래 쿼리를 아웃코드 자동화앱에 복사 붙여넣기하여 수정해보세요!)

‍

WITH user_access_info AS (
select JSON_UNQUOTE(JSON_EXTRACT(CUH.data, '$.email')) as user_email,
 DATE_FORMAT(CUH.created_date, '%Y-%m-%d') as access_date
from USER_HIST AS CUH
where JSON_UNQUOTE(JSON_EXTRACT(CUH.data, '$.email')) IN (
SELECT DISTINCT email AS email
FROM USER AS CU
WHERE deleted_date IS NULL
 
)
and CUH.created_date >= '2023-10-01'
group by 1,2

), retention_1 as (
 
 SELECT
 initial_access.access_date AS cohort_date,
 DATEDIFF(u.access_date, initial_access.access_date) AS day_number,
 COUNT(u.user_email) AS retained_users
 -- COUNT(u.user_email) / initial_users.total_users * 100 AS retention_percentage
FROM (
 SELECT
   user_email,
   MIN(access_date) AS access_date
 FROM user_access_info
 GROUP BY user_email
) AS initial_access
JOIN user_access_info AS u ON initial_access.user_email = u.user_email
JOIN (
 SELECT
   access_date,
   COUNT(DISTINCT user_email) AS total_users
 FROM user_access_info
 GROUP BY access_date
) AS initial_users ON initial_access.access_date = initial_users.access_date
GROUP BY cohort_date, day_number
ORDER BY cohort_date, day_number
 
), retention_basis as (
select cohort_date, max(retained_users) as total_users
from retention_1
group by 1
), retention_agg as (

select retention_1.cohort_date, retention_1.day_number,
 retention_basis.total_users, retention_1.retained_users,
 round(retention_1.retained_users/retention_basis.total_users*100,2) as retention_percentage
from retention_1
left join retention_basis on retention_1.cohort_date = retention_basis.cohort_date
 
 ), total_users_cnt as (

select sum(total_users) as total_users
from retention_basis
 
 )

select retention_agg.day_number, total_users_cnt.total_users,
 sum(retained_users) as retained_users,
round(sum(retained_users)/total_users_cnt.total_users*100 ,2) as avg_retention_percentage
 
from retention_agg, total_users_cnt
group by 1,2
order by 1 asc

‍