[PostgreSQL] 9. 매일의 N일 정착률 추이 확인하기(종합쿼리 포함)

N일 지속률과 N일 정착률의 추이

• n일 지속률과 n일 정착률을 따로 집계하면 등록 후 며칠간 사용자가 안정적으로 서비스를 사용하는지, 며칠 후에 그만두는 사용자가 많아지는지 등을 알 수 있다.
• 만약 지속률이나 정착률이 극단적으로 떨어지는 시점이 있다면, 해당 시점을 기준으로 공지사항 등을 전달하거나 N일 이상 사용한 사용자에게 보너스를 주는 등의 대책을 수행할 수 있을 것이다

 

✨ 이번 포스팅에서는 지속률에 이어 정착률을 확인할 수 있는 종합 쿼리를 작성해본다

 

 

매일의 N일 정착률 추이

👉 지속률과 마찬가지로, 대책이 의도한 대로의 효과가 있는지 확인하려면 정착률을 매일 집계한 리포트가 필요하다

👉 참고로 7일 정착률이 극단적으로 낮은 경우에는 정착률이 아니라 '다음날 지속률' ~ '7일 지속률'을 확인해서 문제를 검토하는 것이 일반적이다

 

• 지난 포스팅에서 확인한 '지속률' 지표를 관리하는 마스터 테이블 작성 쿼리를 응용한다.
• 이때, 정착률을 산출할 경우 대상이 되는 기간이 여러 일자에 걸쳐 있으므로 interval_date를 interval_begin_date와 interval_end_date로 확장해야 한다.

WITH 
repeat_interval(index_name, interval_begin_date, interval_end_date) AS (
	VALUES
	  ('07 day retention', 1, 7)
	, ('14 day retention', 8, 14)
	, ('21 day retention', 15, 21)
	, ('28 day retention', 22, 28)
)
SELECT *
FROM repeat_interval
ORDER BY index_name
;

 

• 유저별 로그를 날짜로 추출하고, 액션 로그의 가장 최근 날짜도 추출한다.
• index_name과 interval을 기반으로 지속률 계산의 기준이 되는 시작일과 종료일을 계산한다.

WITH 
repeat_interval(index_name, interval_begin_date, interval_end_date) AS (
	VALUES
	  ('07 day retention', 1, 7)
	, ('14 day retention', 8, 14)
	, ('21 day retention', 15, 21)
	, ('28 day retention', 22, 28)
)
, action_log_with_index_date AS (
	SELECT u.user_id
		, u.register_date
		, CAST(a.stamp AS date) AS action_date
		, MAX(CAST(a.stamp AS date)) OVER () AS lastest_date
		, r.index_name
		-- 지표의 대상기간 시작일과 종료일 계산 
		, CAST(u.register_date::date + '1 day'::INTERVAL * r.interval_begin_date AS date) 
		  AS index_begin_date
		, CAST(u.register_date::date + '1 day'::INTERVAL * r.interval_end_date AS date)
		  AS index_end_date
	FROM mst_users AS u
	LEFT JOIN action_log AS a ON u.user_id = a.user_id
	CROSS JOIN repeat_interval AS r
)
SELECT *
FROM action_log_with_index_date

 

 

• 이제 각 지속률 계산 범위에 들어가는 기간에 활동이 있었는지를 판단하기 위해 SIGN 함수와 SUM 함수를 사용한다.(지속률 산출 방식과 동일)
• 그리고 등록일과 index_name을 기준으로 그룹화하여 정착률 평균을 계산한다

-- 위 쿼리와 이어짐(action_log_with_index_date)--

, user_action_flag AS (
	SELECT user_id
		, register_date
		, index_name
		, SIGN(
			SUM(CASE WHEN index_end_date <= lastest_date THEN
				CASE WHEN action_date BETWEEN index_begin_date AND index_end_date THEN 1 ELSE 0 END
			END)
		) AS index_date_action
	FROM action_log_with_index_date
	GROUP BY user_id, register_date, index_name, index_begin_date, index_end_date
)
SELECT register_date
	, index_name
	, AVG(100.0 * index_date_action) AS index_rate
FROM user_action_flag
GROUP BY register_date, index_name
ORDER BY register_date, index_name
;

결과 예시

결과