[10] 시계열에 따른 사용자 전체의 변화 찾기 - 지속/정착률 산출, 지속/정착률에 영향을 주는 액션 요인 집계, 액션 수에 따른 정착률 집계

목차

3. 데이터 가공을 위한 SQL
 3-1. 하나의 값 조작하기 (5강)
 3-2. 여러 개의 값에 대한 조작 (6강)
  3-3. 하나의 테이블에 대한 조작 (7강)
3-4. 여러 개의 테이블 조작하기 (8강)
4. 매출을 파악하기 위한 데이터 추출 
  4-1. 시계열 기반으로 데이터 집계하기 (9강)
4-2. 다면적인 축을 사용해 데이터 집약하기 (10강)
5. 사용자를 파악하기 위한 데이터 추출 
  5-1. 사용자 전체의 특징과 경향 찾기 (11강)
  5-2. 시계열에 따른 사용자 전체의 상태 변화 찾기 (12강)
  5-3. 시계열에 따른 사용자의 개별적인 행동 분석하기
6. 웹사이트에서 행동을 파악하는 데이터 추출하기
  6-1. 사이트 전체의 특징/경향 찾기
  6-2. 사이트 내의 사용자 행동 파악하기
  6-3. 입력 양식 최적화하기
7. 데이터 활용의 정밀도를 높이는 분석 기술
  7-1. 데이터를 조합해서 새로운 데이터 만들기
  7-2. 이상값 검출하기
  7-3. 데이터 중복 검출하기
  7-4. 여러 개의 데이터셋 비교하기
8. 데이터를 무기로 삼기 위한 분석 기술
  8-1. 검색 기능 평가하기
  8-2. 데이터 마이닝
  8-3. 추천
  8-4. 점수 계산하기
9. 지식을 행동으로 옮기기
  9-1. 데이터 활용의 현장

---

※ Mysql 기준
 
5장. 사용자를 파악하기 위한 데이터 추출

12강. 시계열에 따른 사용자 전체의 상태 변화 찾기

샘플 데이터 action_log & mst_users

action_log

mst_users

 

12-1. 등록 수의 추이와 경향 보기

COUNT(DISTINCT~), 등록 수, 작대비

사용자가 서비스에서 이탈할지 아닐지를 분석하기 이전에, 현재 등록 수 파악하기

SELECT
	register_date,
    COUNT(DISTINCT user_id) AS register_count
FROM mst_users
GROUP BY register_date
ORDER BY register_date;

---

월 별 등록 수 추이

- 월 별 등록 수 집계 (COUNT)

- 월 별 등록 수 집계

- 전월비 집계 (LAG)

월 별 등록 수와 전월비

WITH temp AS(
SELECT *,
	MID(register_date, 1, 7) AS date
FROM mst_users)
SELECT
	date,
	COUNT(DISTINCT user_id) AS register_count,
    LAG(COUNT(DISTINCT user_id))
    OVER(ORDER BY date) AS last_month_count,
    1.0 * LAG(COUNT(DISTINCT user_id))
    OVER(ORDER BY date) AS month_over_month_ratio
FROM temp
GROUP BY date;

---

등록 디바이스별 추이

- 월 별 등록 수 집계 (COUNT)

디바이스 별 등록 수

WITH temp AS(
	SELECT *,
    MID(register_date,1,7) AS date
FROM mst_users)
SELECT
	date,
	COUNT(DISTINCT user_id) AS register_count,
    COUNT(DISTINCT CASE WHEN register_device='pc' THEN user_id END) AS register_pc,
    COUNT(DISTINCT CASE WHEN register_device='sp' THEN user_id END) AS register_sp,
    COUNT(DISTINCT CASE WHEN register_device='app' THEN user_id END) AS register_app
FROM temp
GROUP BY date;

-- CASE WHEN 에서 END 까먹지 말기
-- DISTINCT 괄호 안침

 

---

12-2. 지속률과 정착률 산출하기

COUNT(DISTINCT~)

지속률

: 등록일 기준으로 이후 지정일 동안 사용자가 서비스를 얼마나 이용했는지 나타내는 지표

= <사용자 수> / <등록 수>

EX.

(6/12) 등록 수 = 100,

(6/13) 사용자 수 = 80 => 다음날 지속률 = 80%

(6/14) 사용자 수 = 60 => 2일 지속률 = 60%

*용도: 사용자가 매일 사용했으면 하는 서비스 (뉴스 사이트, 소셜 게임, SNS 등)

 

정착률

: 등록일 기준으로 이후 지정한 7일 동안 사용자가 서비스를 사용했는지 나타내는 지표

= 지속과는 다르게 7일이라는 기간에 한 번이라도 서비스를 사용했다면 정착자로 다룬다.

EX.

(6/12) 등록 수 = 100,

(6/13~19) 사용자 수 = 80 => 7일 정착률 = 80%

(6/20~6/26) 사용자 수 = 60 => 14일 정착률 = 60%

*용도: 사용자에게 어떤 목적이 생겼을 때 사용했으면 하는 서비스 (리뷰 사이트, Q&A 사이트 등)

---

지속률 관련 리포트

날짜별 N일 지속률 추이

 

1. 다음날(1일) 지속률 집계 방법 생각해보기

: 지정한 날짜 다음에 사용한 사용자에 1을, 사용하지 않은 사용자에 0이라는 플래그를 붙이고 AVG함수를 적용해 평균을 구하는 방법이 간단하다.

 

2. 로그 데이터가 모두 있는지 확인하기

: 로그 집계 기간 중에 가장 최신 날짜를 추출하고, 최신 일자를 넘는 기간의 지속률은 NULL로 출력되도록 만들기

 

'로그 최근 일자'와 '사용자별 등록일의 다음날' 계산하기

WITH
temp AS(
SELECT
m.user_id, -- master 
m.register_date,
/*action 날짜와 로그 전체의 최신 날짜를 날짜 자료형으로 변환*/
CAST(a.stamp AS date) AS action_date, -- action_log 
MAX(CAST(a.stamp AS date)) OVER() AS latest_date,
/*등록일 다음날의 날짜 계산하기, mysql은 bigquery와 동일하게 date_add사용*/
DATE_ADD(CAST(m.register_date AS date), interval 1 day) AS next_day_1
FROM mst_users m
LEFT OUTER JOIN
action_log a
ON m.user_id = a.user_id)
SELECT * FROM temp
ORDER BY register_date;

▼

1. next_day_1이 latest_date보다 이전 일 때,

2. action_date와 일치하는 경우 1을 부여해서

3. 합계가 양수인지 sign으로 판정하기

사용자의 액션 플래그 계산하기

WITH
temp AS(
SELECT
m.user_id, -- master 
m.register_date,
CAST(a.stamp AS date) AS action_date, -- action_log 
MAX(CAST(a.stamp AS date)) OVER() AS latest_date,
DATE_ADD(CAST(m.register_date AS date), interval 1 day) AS next_day_1
FROM mst_users m
LEFT OUTER JOIN
action_log a
ON m.user_id = a.user_id),
user_flag AS(
SELECT
user_id,
register_date,
/* 등록일 다음날이 로그의 최신 날짜 이전인지 확인하고,
다음날에 액션을 했다면 1, 안 했다면 0으로 지정하고, flag합계를 sign으로 판정하기 */
SIGN(SUM(CASE WHEN next_day_1 <= latest_date THEN
CASE WHEN next_day_1 = action_date THEN 1 ELSE 0 END END)) AS next_1_day_action
FROM temp
GROUP BY user_id, register_date)
SELECT * FROM user_flag;

log stamp의 가장 최신 날짜였던 11/2이후의 날짜의 플래그는 null로 뜨는 모습

▼

100.0을 곱하고 avg함수로 평균을 구해 퍼센트 단위로 나타내기

다음 날 지속률 계산하기

/* user_flag 생성까지 동일*/
SELECT 
register_date,
AVG(100.0 * next_1_day_action) AS repeat_rate_1_day
FROM user_flag
GROUP BY register_date
ORDER BY register_date;

데이터 부족 ㅜ

→ 2일째 이후의 지속률 계산 시에도 n번째 이후의 날짜를 계산하면 된다.

→ 쿼리가 복잡해지기에, 지표를 관리하는 일시 테이블을 사용해서 지표를 세로 기반으로 표현하기

▼

index_name(지표 이름)과 interval_date(등록 후 며칠 째 지표인지)를 가진 테이블로 변환해서 저장하기

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

▼

지속률을 세로 기반으로 집계하기

 

앞 부분 repeat_interval temp 만들고, next_day_1을 index_date로 변경

SELECT 
register_date,
index_name,
AVG(100.0 * index_date_action) AS repeat_rate
FROM user_flag
GROUP BY register_date, index_name
ORDER BY register_date, index_name;

---

정착률 관련 리포트

매일의 N일 정착률 추이

지속률 구할 때 사용했던 방식에서, 정착률 산출을 위해 대상이 되는 기간(interval_date)을 interval_begin_date와 interval_end_date로 확장해야 한다. 

정착률 지표를 관리하는 마스터 테이블을 작성하는 쿼리

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

앞 부분 repeat_interval temp 만들고, index_date를 index_begin/end_date로 변경

SELECT 
register_date,
index_name,
AVG(100.0 * index_date_action) AS index_rate
FROM user_flag
GROUP BY register_date, index_name
ORDER BY register_date, index_name;

---

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

정착률

지속률 지표를 관리하는 마스터 테이블을 정착률 형식으로 수정하기

WITH repeat_interval AS(
SELECT * FROM (
		SELECT '01 day repeat' AS index_name, 1 AS interval_begin_date,1 AS interval_end_date
		UNION ALL
		SELECT '02 day repeat',2,2
		UNION ALL
		SELECT '03 day repeat',3,3
        		UNION ALL
		SELECT '04 day repeat',4,4
        		UNION ALL
		SELECT '05 day repeat',5,5
        		UNION ALL
		SELECT '06 day repeat',6,6
        		UNION ALL
		SELECT '07 day repeat',7,7
				UNION ALL
		SELECT '07 day retention',1,7
        		UNION ALL
		SELECT '14 day retention',8,14
        		UNION ALL
		SELECT '21 day retention',15,21
        		UNION ALL
		SELECT '28 day retention',22,28
	) AS repeat_interval)
SELECT * FROM repeat_interval

-- user_flag까지 쿼리 동일
SELECT 
index_name,
AVG(100.0 * index_date_action) AS repeat_rate
FROM user_flag
GROUP BY index_name
ORDER BY index_name;

 

※ 지속률과 정착률 모두 등록일 기준으로 n일 후의 행동을 집계하는 것이기 때문에, 30일/60일 지속률처럼 값을 구하는 데 오래 걸리는 지표보다는, 1일 지속률, 7일 정착률처럼 단기간에 결과를 보고 대책을 세울 수 있는 지표를 활용하는 것이 좋다. 

---

12-3. 지속과 정착에 영향을 주는 액션 집계하기

CROSS JOIN, CASE식, AVG함수

• 앞서 지속률과 정착률을 통해서 상황을 이해했기 때문에, 무엇 때문에 그러한 추이가 발생하는지 대책을 세워야 한다.
• 1일 지속률을 개선하려면, 등록한 당일 사용자들이 무엇을 했는지 확인
• 14일 정착률을 개선하고 싶다면, 7일 정착률의 판정 기간동안 사용자가 어떤 행동을 했는지 확인

다음과 같이 무슨 액션이 정착률/지속률에 영향을 주었는지 확인

사용자의 1일 지속률이 높고, 비사용자의 1일 지속률이 낮은 액션이 1일 지속률에 더 영향을 준다고 볼 수 있다.

→ post 액션을 사용한 사용자는 다음날에도 80%나 사용한다(=1일 지속률). 반대로 비사용자의 1일 지속률은 profile_regist가 굉장히 낮다.

→ 가입 절차 간소화 등의 대책을 세울 수 있음 

 

▼
각 액션에 대한 사용자와 비사용자의 다음날 지속률을 한꺼번에 계산하기

1. 사용자 마스터 테이블과 액션 마스터 테이블을 CROSS JOIN하여 모든 액션의 조합을 만든 뒤, 2. 사용자의 액션 실행 여부를 0과 1로 나타내는 테이블을 만들어 집계하기

 

-- user_flag까지 쿼리 동일
mst_actions AS(
SELECT 'view' AS action
UNION ALL SELECT 'comment' AS action
UNION ALL SELECT 'follow' AS action),
mst_user_actions AS(
SELECT
m.user_id,
m.register_date,
a.action
FROM mst_users m
CROSS JOIN
mst_actions AS a)
SELECT * FROM mst_user_actions
ORDER BY user_id, action;

 

▼

각 사용자에 대해서, 사용자의 등록일과 다음날에 액션이 있는지 0과 1 플래그로 나타내기

-- mst_user_actions까지 쿼리 동일
,
register_action_flag AS(
SELECT DISTINCT	
m.user_id,
m.register_date,
m.action,
CASE WHEN a.action IS NOT NULL THEN 1 ELSE 0 END AS do_action,
index_name,
index_date_action
FROM mst_user_actions AS m
LEFT JOIN
action_log AS a
ON m.user_id = a.user_id
AND CAST(m.register_date AS date) = CAST(a.stamp AS date)
LEFT JOIN
user_flag AS f
ON m.user_id = f.user_id
WHERE f.index_date_action IS NOT NULL)
SELECT * FROM register_action_flag
ORDER BY user_id, index_name, action;

 

Steps

<mst_user_actions>

mst_user_actions에서 action 유무에따라 0,1 플래그를 부여하고,

 

<action_log>

action_log와 user_id 기준 LEFT JOIN

 

<user_flag>

user_flag와 user_id 기준 LEFT JOIN

결과

해석

U001 사용자는

- 등록일(16.10.1)에 'comment', 'follow', 'view'액션을 실행했으며, 

- 다음날 지속률 판정기간(1일)동안 해당 액션을 실행하지 않음(index_date_action=0)

 

▼

액션에 따른 지속률과 정착률 집계하기

register_action_flag

-- register_action_flag까지 쿼리 동일
SELECT
action,
COUNT(*) AS users,
AVG ( 100.0 * do_action) AS usage_rate,
index_name,
AVG(CASE do_action WHEN 1 THEN 100.0 * index_date_action END) AS idx_rate,
AVG(CASE do_action WHEN 0 THEN 100.0 * index_date_action END) AS no_action_idx_rate
FROM register_action_flag
GROUP BY index_name, action
ORDER BY index_name, action;

 

액션 여부에 따라서(action) 지속률과 정착률이 어떠한지(index_name 별 index_rate) 알아보았다.

---

12-4. 액션 수에 따른 정착률 집계하기

CROSS JOIN, CASE식, AVG함수

 

"등록 후 1주일 이내 10명을 팔로우하면, 해당 사용자는 서비스를 계속해서 사용한다"

 

등록일과 그 이후 7일 동안(7일 정착률 기간) 실행한 액션 수에 따라 14일 정착률이 어떻게 변하는지 살펴보자.

 

다음과 같이 액션 별 도수 분포표를 만들고, 달성자의 14일 정착률 집계해보기

 

액션 별 도수 분포표 만들기(+사용자 붙이기)

mst_action_bucket AS (
SELECT * FROM (
SELECT 'comment' AS action, 0 AS min_count,0 AS max_count
UNION ALL
SELECT 'comment',1,5
UNION ALL
SELECT 'comment',6,10
UNION ALL
SELECT 'comment',11,9999
UNION ALL
SELECT 'follow',0,0
UNION ALL
SELECT 'follow',1,5
UNION ALL
SELECT 'follow',6,10
UNION ALL
SELECT 'follow',11,9999) AS mst_user_bucket)
SELECT * FROM mst_action_bucket;

-- mst_action_bucket까지 쿼리 동일
-- 사용자 마스터 테이블과 크로스 조인하여 user_id와 register_date 추가
,
mst_user_action_bucket AS(
SELECT
m.user_id,
m.register_date,
a.action,
a.min_count,
a.max_count
FROM mst_users AS m
CROSS JOIN mst_action_bucket a)
SELECT * FROM mst_user_action_bucket
ORDER BY user_id, action, min_count;

 

▼

등록 후(register_date) 7일간의 액션 수 집계하기

-- mst_user_action_bucket까지 쿼리 동일
,
-- 등록일에서 7일 후까지의 액션 수를 세고,
-- 액션 단계와 14일 정착 달성 플래그 계산
register_action_flag AS(
SELECT
m.user_id,
m.action,
m.min_count,
m.max_count,
COUNT(a.action) AS action_count,
CASE WHEN COUNT(a.action) BETWEEN m.min_count AND m.max_count THEN 1 
ELSE 0 END AS achieve,
index_name,
index_date_action
FROM mst_user_action_bucket AS m
LEFT JOIN action_log AS a
	ON m.user_id = a.user_id
	AND DATE(a.stamp)
	BETWEEN CAST(m.register_date AS date)
		AND DATE_ADD(CAST(m.register_date AS date), INTERVAL 7 DAY)
LEFT JOIN
user_flag AS f
on m.user_id = f.user_id
WHERE f.index_date_action IS NOT NULL
GROUP BY 
m.user_id,
m.action,
m.min_count,
m.max_count,
f.index_name,
f.index_date_action)
SELECT * FROM register_action_flag
ORDER BY user_id, action, min_count;

 

▼

등록 후(register_date) 7일간의 액션 횟수별로 14일간의 정착률 집계하기

-- register_action_flag까지 쿼리 동일
SELECT 
action,
CONCAT(min_count, '~', max_count) AS count_range,
SUM(CASE WHEN achieve = 1 THEN 1 ELSE 0 END) AS achieve,
index_name,
AVG(CASE WHEN achieve = 1 THEN 100.0 * index_date_action END) AS achieve_index_rate
FROM register_action_flag
GROUP BY index_name, action, min_count, max_counts
ORDER BY index_name, action, min_count;

 

액션 별로 사용자를 집계하면, 사용자가 어떤 기능을 더 많이 사용하도록 유도해야 하는지 알 수 있다.

---

12-5 이어서..~