[PostgreSQL] 8. N일 후 지속률 산출하기(종합쿼리 첨부)

🎯 등록 시점을 기준으로 일정 기간 동안 사용자가 지속해서 사용하고 있는지를 조사할 때
지속률과 정착률을 사용하면 경향을 쉽게 파악할 수 있다.

 

지속률과 정착률이란?

• 지속률 : 등록일 기준으로 이후 지정일 동안 사용자가 서비스를 얼마나 이용했는지 나타내는 지표
  • 등록일 이후 매일 서비스를 사용하지 않더라도, 판정 날짜 안에 사용했다면 지속자로 취급함
  • 집계 방법 : 등록자와 사용자 수를 집계하고, (사용자 수 / 등록 수) 로 구한다.
• 정착률 : 등록일 기준으로 이후 지정한 7일 동안 사용자가 서비스를 사용했는지 나타내는 지표
  • 7일이라는 기간 동안 한 번이라도 서비스를 사용했다면 정착자로 다룸
  • 7일 정착률은 등록 후 1일부터 7일까지의 정착률을 기준으로 산출함
  • 14일 정착률은 이후 7일(등록일부터 8일 후부터 14일까지의 정착률)
  • 집계 방법 : 사용자 수 / 등록 수 
• 사용자에게 기대하는 사용 사이클은 서비스별로 다르다. 
  • 소셜 게임과 뉴스 사이트, SNS는 사용자가 틈틈이 계속 이용하기를 기대할 것이다 👉 지속률
  • 이커머스 사이트는 매일 쇼핑하라 것을 기대할 것이다 👉 정착률
  • 리뷰 사이트는 사용자가 무언가를 체험할 때마다 사용하기를 기대할 것이다 👉 정착률

 

실습 데이터(mst_user)

action_log 데이터

---

날짜별 N일 지속률 추이

• 지속률을 올릴 수 있는 대책의 효과가 의도한 대로 수행되는지를 확인하려면, 지속률을 날짜에 따라 집계한 리포트를 만들어야 한다.
• 서비스를 더 활발하게 하려면 등록 다음 날의 지속률을 높이는 것이 중요하다.

▶︎ 다음날(1일) 지속률 집계

• 다음날 지속률은 '지정한 날짜에 등록한 사용자 중 다음날에도 서비스를 사용한 사람의 비율'이다.
• 지정한 날짜 다음에 사용한 사용자에 1, 사용하지 않은 사용자에 0이라는 플래그를 붙이고, 이러한 값에 AVG 함수를 적용해 평균을 구하는 방법이 간단하다.
• 단, 다음날 지속률을 집계하려면 다음날의 로그 데이터가 모두 쌓여 있어야 한다!!
  👉 등록일 다음날 작업한 사용자가 정말로 0인 경우와, 로그가 아직 쌓이지 않아서 0명으로 나오는 경우를 구분해야 하기 때문
  👉 여기서는 로그 집계 기간 중 가장 최신 날짜를 추출하고, 이 일자를 넘는 기간의 지속률은 NULL로 출력하게 한다.

1. 로그의 최근 날짜 추출하기
2. 사용자별 등록일과 다음날의 날짜를 집계하기
   ▶︎ 등록 다음 날짜 : CAST(u.register_date::date + '1 day'::INTERVAL, date)

WITH 
action_log_with_mst_users AS (
	SELECT u.user_id
		, u.register_date
	 	, cast(a.stamp AS date) AS action_date --액션 날짜 자료형 변 
		, max(cast(a.stamp AS date)) OVER () AS last_date -- 전체 로그에서 가장 최근 액션 날짜 
		, cast(u.register_date::date + '1 day'::INTERVAL AS date) AS next_day_1
	FROM mst_users AS u
	LEFT JOIN action_log AS a 
	ON u.user_id = a.user_id
)
SELECT *
FROM action_log_with_mst_users
ORDER BY register_date
;

 

3. 지정한 날의 다음 날에 액션을 했는지 0과 1로 표시하기
  → 지정한 날의 다음날이 로그의 가장 최근 날짜를 넘는 경우에는 NULL로 처리

• 등록일 다음날이 로그의 최신 날짜 이전인지 먼저 조건을 case when 으로 걸고, 등록일 다음 날에 액션이 일어났는지 한 번 더 case when으로 확인하여 1, 0을 반환 👉 SIGN 함수

WITH 
action_log_with_mst_users 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 -- 전체 로그에서 가장 최근 액션 날짜 
		, cast(u.register_date::date + '1 day'::INTERVAL AS date) AS next_day_1
	FROM mst_users AS u
	LEFT JOIN action_log AS a 
	ON u.user_id = a.user_id
)
-- 사용자의 액션 플래그를 계산 --
,user_action_flag AS (
	SELECT user_id
		, register_date
		, SIGN(
			sum(CASE WHEN next_day_1 <= lastest_date THEN
				CASE WHEN next_day_1 = action_date THEN 1 ELSE 0 END
				END)
		) AS next_1_day_actioin 
	FROM action_log_with_mst_users
	GROUP BY user_id, register_date
)
SELECT *
FROM user_action_flag
ORDER BY register_date, user_id ;

 

 

• 여기서 날짜별로, next_1_day_action의 평균을 구하고 100을 곱하면 1일 이후의 지속률을 구할 수 있다.

-- user_action_flag 까지 동일 --
SELECT register_date
	, avg(100.0 * next_1_day_actioin) AS repeat_rate_1_day
FROM user_action_flag
GROUP BY register_date
ORDER BY register_date
;

 

---

🎯 종합 쿼리 | 지속률 지표를 관리하는 마스터 테이블을 작성하는 쿼리

 

1. 지표 마스터 생성

• PostgreSQL은 VALUES로 테이블을 생성할 수 있다.

WITH 
repeat_interval(index_name, interval_day) AS (
	VALUES
		 ('01 day repeat', 1)
		,('02 day repeat', 2)
		,('03 day repeat', 3)
		,('04 day repeat', 4)
		,('05 day repeat', 5)
		,('06 day repeat', 6)
		,('07 day repeat', 7)
)
SELECT *
FROM repeat_interval
ORDER BY index_name
;

 

2. 날짜별 지속률 계산 쿼리

• action_log_with_index_date 테이블
  • 위에서 만든 repeat_interval을 CROSS JOIN 한다.
  • 각 로그의 날짜를 date 형식으로 바꿔주고
  • 유저별 가장 최신 로그도 구해준다 (max)
  • 지속률을 계산하고 싶은 날짜를 interval '1 day'에 곱해주고 이를 등록일에 더해서 index_date를 만든다
• user_action_flag 테이블
  • 등록일 다음 날짜(기준날짜)인 index_date가 가장 최신 로그 날짜보다 이전인지 확인하고, 
    index_date가 action_date와 동일하면 1, 그렇지 않은 경우 0을 반환
• 마지막 SELECT절
  • 등록일과 index_name(확인하고 싶은 지속률 날짜 기준)을 기준으로 그룹화하여 지속률 평균을 구한다.

WITH 
repeat_interval(index_name, interval_date) AS (
	VALUES
		 ('01 day repeat', 1)
		,('02 day repeat', 2)
		,('03 day repeat', 3)
		,('04 day repeat', 4)
		,('05 day repeat', 5)
		,('06 day repeat', 6)
		,('07 day repeat', 7)
)
, 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 --등록일로부터 n일 후의 날짜 
		, cast(cast(u.register_date AS date) + INTERVAL '1 day' * r.interval_date AS date) AS index_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 
)
, user_action_flag AS (
	SELECT user_id
		, register_date
		, index_name
		, sign(
			sum(CASE WHEN index_date <= lastest_date THEN 
				CASE WHEN index_date = action_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_date
)
SELECT register_date
	, index_name
	, avg(100.0 * index_date_action) AS repeat_rate
FROM user_action_flag
GROUP BY register_date, index_name
ORDER BY register_date, index_name
;