[11] 시계열에 따른 사용자 전체의 변화 찾기(2) -정착률/잔존율, 방문 빈도 기반 사용자 속성 정의, 방문 종류 기반 성장 지수 집계

목차

3. 데이터 가공을 위한 SQL
 3-1. 하나의 값 조작하기 (5강)
 3-2. 여러 개의 값에 대한 조작 (6강)
  3-3. 하나의 테이블에 대한 조작 (7강)
3-4. 여러 개의 테이블 조작하기 (8강)
4. 매출을 파악하기 위한 데이터 추출 (9강)
  4-1. 시계열 기반으로 데이터 집계하기 (10강)
4-2. 다면적인 축을 사용해 데이터 집약하기
5. 사용자를 파악하기 위한 데이터 추출
  5-1. 사용자 전체의 특징과 경향 찾기
  5-2. 시계열에 따른 사용자 전체의 상태 변화 찾기
  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장. 사용자를 파악하기 위한 데이터 추출

11강. 사용자 전체의 특징과 경향 찾기

 

샘플 데이터 action_log & mst_users

action_log

mst_users

 

12-5. 사용 일수에 따른 정착률 집계하기

COUNT 함수, SUM 윈도 함수, AVG 함수, 정착률

7일 정착 기간 동안 사용자가 며칠 사용했는지가 이후 정착률에 어떠한 영향을 주는지 확인해보자.

다음과 같은 내용을 알 수 있다.

- 70%의 사용자가 7일 정착 판정 기간 동안 1-4일 밖에 사용하지 않음

- 7일 정착 판정 기간 동안 1일밖에 사용하지 않은 사용자의 28일 정착률은 20.8%

- 5일 동안 사용한 사용자의 28일 정착률은 45%

- 6일 동안 사용한 사용자의 28일 정착률은 55.5%로, 3일 동안의 사용자에 비해 약 10%가 높음

그러면 이러한 조치를 취할 수 있다.

- 1-5일동안 연속으로 접속 로그인 보상등을 주어 어떻게든 6일간은 계속해서 사용하도록 만든다.

 

Steps

1. 등록한 다음날부터 7일 동안의 사용 일수 집계
2. 사용 일수별로 집계한 사용자 수의 구성비와 구성비 누계 계산
3. 사용 일수별로 집계한 사용자 수를 분모로 두고, 28일 정착률을 집계한 뒤 그 비율을 계산

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)

일시 테이블 repeat_interval

,
temp AS(
SELECT
m.user_id,
m.register_date,
CAST(a.stamp AS date) AS action_date,
MAX(CAST(a.stamp AS date)) OVER() AS latest_date,
r.index_name,
DATE_ADD(CAST(m.register_date AS date), interval r.interval_begin_date day) AS index_begin_date,
DATE_ADD(CAST(m.register_date AS date), interval r.interval_end_date day) AS index_end_date
FROM mst_users m
LEFT OUTER JOIN action_log a
ON m.user_id = a.user_id
CROSS JOIN repeat_interval AS r)

일시테이블 temp

,
user_flag AS(
SELECT
user_id, register_date, index_name,
SIGN(SUM(CASE WHEN index_end_date <= latest_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 temp
GROUP BY user_id, register_date, index_name, index_begin_date, index_end_date)

일시테이블 user_flag

Step 1.

등록한 다음날부터 7일 동안의 사용 일수 집계 (→CAST~BETWEEN~DATEADD~)

register_action_flag AS(
SELECT
m.user_id,
COUNT(DISTINCT CAST(a.stamp AS date)) AS dt_count,
index_name,
index_date_action
FROM mst_users AS m
LEFT JOIN action_log AS a
ON m.user_id = a.user_id
AND CAST(a.stamp AS date)
BETWEEN DATE_ADD(CAST(m.register_date AS date), INTERVAL 1 DAY)
AND DATE_ADD(CAST(m.register_date AS date), INTERVAL 8 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, f.index_name, f.index_date_action)
SELECT * FROM register_action_flag;

register_action_flag

Step 2,3.

사용 일수별로 집계한 사용자 수의 구성비와 구성비 누계 계산

사용 일수별로 집계한 사용자 수를 분모로 두고, 28일 정착률을 집계한 뒤 그 비율을 계산

-- register_action_flag까지 동일
SELECT
dt_count AS dates,
COUNT(user_id) AS users,
100.0 * COUNT(user_id) / SUM(COUNT(user_id)) OVER() AS  user_ratio,
100.0 * SUM(COUNT(user_id)) OVER (
ORDER BY index_name, dt_count)
/ SUM(COUNT(user_id)) OVER() AS cum_ratio,
SUM(index_date_action) AS achieve_users,
AVG(100.0 * index_date_action) AS achieve_ratio
FROM register_action_flag
GROUP BY index_name, dt_count
ORDER BY index_name, dt_count;

데이터가 부족해 . . .

---

12-6. 사용자의 잔존율 집계하기

CROSS JOIN, SUM(CASE~), AVG(CASE~), 잔존율

다음과 같은 항목 확인하기

1. 이전과 비교해 n개월 후의 잔존율이 내려갔는지
2. n개월 후에 잔존율이 낮아지는 경향이 있는지
3. 오래 사용하던 사용자인데도 특정 월을 기준으로 사용하지 않게 되었는지

Step 1.

12개월 후까지의 월을 도출하기위한 보조 테이블 만들기

※ select all union 대신 mysql에서는 WITH RECURSIVE()를 사용할 수 있음

WITH RECURSIVE repeat_interval AS (
  SELECT 1 AS interval_month
  UNION ALL
  SELECT interval_month + 1
  FROM repeat_interval
  WHERE interval_month < 12
)
SELECT * FROM repeat_interval;

Step 2,3.

사용자의 등록일부터 12개월 후까지의 월을 사용자 마스터에 추가하기

그리고 등록월부터 12개월 후까지의 월을 추가한 사용자 마스터와 결합해서 월 단위 잔존율을 집계

WITH RECURSIVE mst_intervals AS (
  SELECT 1 AS interval_month
  UNION ALL
  SELECT interval_month + 1
  FROM mst_intervals
  WHERE interval_month < 12
),
mst_users_with_index_month AS(
SELECT
m.user_id,
m.register_date,
DATE_ADD(CAST(m.register_date AS date), INTERVAL 1 MONTH) AS index_date,
SUBSTRING(m.register_date, 1, 7) AS register_month,
SUBSTRING(DATE_ADD(CAST(m.register_date AS date), INTERVAL 1 MONTH), 1, 7) AS index_month
FROM mst_users AS m
CROSS JOIN mst_intervals AS i)
SELECT * FROM mst_users_with_index_month;

 

a. 사용자의 등록일, 등록일로부터의 한달 후의 날짜(index_date)와 등록 월(register_month) 그리고 index_month를 계산

b. CROSS JOIN을 사용하여 모든 사용자에게 적용

일시 테이블 mst_users_with_index_month

-- mst_users_with_index까지 동일
,
action_log_in_month AS(
SELECT DISTINCT user_id,
SUBSTRING(stamp, 1, 7) AS action_month
FROM action_log)
SELECT * FROM action_log_in_month;

 

a. action_log 테이블에서 각 사용자의 활동이 기록된 월(action_month)를 찾기

b. DISTINCT로 중복을 제거

일시 테이블 action_log_in_month

-- action_log_in_month까지 동일
SELECT
m.register_month,
m.index_month,
SUM(CASE WHEN a.action_month IS NOT NULL THEN 1 ELSE 0 END) AS users,
AVG(CASE WHEN a.action_month IS NOT NULL THEN 100.0 ELSE 0.0 END) AS retension_rate
FROM mst_users_with_index_month AS m
LEFT JOIN
action_log_in_month AS a
ON m.user_id = a.user_id
AND m.index_month = a.action_month
GROUP BY m.register_month, m.index_month
ORDER BY m.register_month, m.index_month;

 

a. 두 테이블을 left join하여 각 사용자의 등록 월과 인덱스 월에 대한 활동을 확인

b. 등록 월과 인덱스 월 별로 사용자 수(sum)와 유지율(avg) 계산

c. 유지율 계산 시 사용자가 활동 월(action_month)에 있었을 시 1, 그렇지 않다면 0을 사용(case when) 

데이터가 없어서 0으로 나옴

 

→ 이런 리포트를 작성할 때에는 해당 월에 실시한 대책/캠페인 등의 이벤트를 함께 기록하면, 수치 변화의 원인 등도 쉽게 파악할 수 있다.

---

12-7. 방문 빈도를 기반으로 사용자 속성을 정의하고 집계하기

CASE 식, NULLIF 함수, LAG 함수, MAU, 리피트, 컴백

MAU

: 특정 월에 사용한 사용자의 수(Monthly Active Users)

- MAU 중에서 몇 명이 기존 사용자이고, 몇 명이 신규 사용자인지 알 수 없기에, MAU 수치만으로는 서비스의 가치를 알 수 없다.

MAU를 3개의 속성 신규, 리피트, 컴백에 따라 분석하기

EX. 10만명의 MAU → 신규 사용자 2만명, 리피트 사용자 7만명, 컴백 사용자 1만명→ 신규 사용자 2만명을 대상으로 어떠한 대책을 시행할지 등을 검토

---

신규 사용자 수, 리피트 사용자 수, 컴백 사용자 수 집계하기

WITH
monthly_user_action AS(
SELECT
m.user_id,
SUBSTRING(m.register_date, 1, 7) AS register_month,
SUBSTRING(l.stamp, 1, 7) AS action_month,
SUBSTRING(DATE_ADD(CAST(m.register_date AS date), INTERVAL -1 MONTH), 1, 7) AS action_month_prev
FROM mst_users AS m
JOIN action_log AS l
ON m.user_iD = l.user_id)
SELECT * FROM monthly_user_action;

일시 테이블 monthly_user_action

-- monthly_user_action까지 동일
,
monthly_user_with_type AS(
SELECT
action_month,
user_id,
CASE WHEN register_month = action_month THEN 'new_user' -- 등록 월과 액션 월이 일치하면 신규
	WHEN action_month_prev = LAG(action_month) OVER(PARTITION BY user_id ORDER BY action_month) THEN 'repeat_usr' -- 이전 월(prev)에 액션이 있다면 리피트
ELSE 'come_back_user' END AS c,action_month_priv
FROM monthly_user_action)
SELECT * FROM monthly_user_with_type;

일시테이블 monthly_user_with_type

-- monthly_user_with_type까지 동일
SELECT 
action_month,
COUNT(user_id) AS mau,
COUNT(CASE WHEN c='new_user' THEN 1 END) AS new_users,
COUNT(CASE WHEN c='repeat_user' THEN 1 END) AS repeat_users,
COUNT(CASE WHEN c='come_back_user' THEN 1 END) AS come_back_users
FROM monthly_user_with_type
GROUP BY action_month
ORDER BY action_month;

 

리피트 사용자를 3가지로 분류하기

이전 달의 사용자 상태에 따라 추가로 리피트 사용자를 다음 3가지로 분류 가능

1. 신규 리피트 사용자: 이전 달에는 신규 사용자로 분류, 이번 달에도 사용
2. 기존 리피트 사용자: 이전 달에도 리피트 사용자로 분류, 이번 달에도 사용
3. 컴백 리피트 사용자: 이전 달에 컴백 사용자로 분류, 이번 달에도 사용

---

리피트 사용자를 세분화해서 집계하기

일시테이블 monthly_user_with_type

-- monthly_user_with_type까지 동일
,
monthly_users AS(
SELECT
m1.action_month,
COUNT(m1.user_id) AS mau,
COUNT(CASE WHEN m1.c = 'new_user' THEN 1 END) AS new_users,
COUNT(CASE WHEN m1.c = 'repeat_user' THEN 1 END) AS repeat_users,
COUNT(CASE WHEN m1.c = 'come_back_user' THEN 1 END) AS come_back_users,
COUNT(CASE WHEN m1.c = 'repeat_user' AND m0.c = 'new_user' THEN 1 END) AS new_repeat_users,
COUNT(CASE WHEN m1.c = 'repeat_user' AND m0.c = 'repeat_user' THEN 1 END) AS continuous_repeat_users,
COUNT(CASE WHEN m1.c = 'repeat_user' AND m0.c = 'come_back_user' THEN 1 END) AS come_back_repeat_users
FROM monthly_user_with_type AS m1
LEFT OUTER JOIN monthly_user_with_type AS m0
ON m1.user_id = m0.user_id
AND m1.action_month_prev = m0.action_month
GROUP BY m1.action_month)
SELECT * FROM monthly_users
ORDER BY action_month;

 

MAU 속성 별 반복률 계산하기

위의 결과로는 다음과 같은 것을 파악하기 어려움

1. 이전 달의 신규 등록 사용자 중에 어느 정도가 리피트 사용자로 전환되었는지
2. 이전 달에 시행한 컴백 사용자를 늘리기 위한 캠페인의 효과가 얼마나 되는지

MAU 내역과 MAU 속성들의 반복률 계산하기

lag함수를 사용해서 지정한 열의 이전 행 값을 사용 → 이전 달의 신규 사용자 수를 기반으로 계산

('over~order by action_month', 월 별로 데이터 정렬 필수)

nullif를 사용해서 0으로 나뉘는 경우를 방지

-- monthly_users까지 동일
SELECT 
action_month,
mau,
new_users,
repeat_users,
come_back_users,
new_repeat_users,
continuous_repeat_users,
come_back_repeat_users,
-- 이전 달에 신규 사용자이면서, 해당 월에 신규 리피트 사용자인 사용자의 비율
100.0 * new_repeat_users 
/ NULLIF(LAG(new_users) OVER(ORDER BY action_month ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING),0)
AS prev_new_repeat_ratio,
-- 리피트-리피트
100.0 * continuous_repeat_users
/ NULLIF(LAG(new_users) OVER(ORDER BY action_month ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING),0)
AS prev_continuous_repeat_ratio,
-- 컴백-리피트
100.0 * come_back_repeat_users
/ NULLIF(LAG(new_users) OVER(ORDER BY action_month ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING),0)
AS prev_continuous_repeat_ratio
FROM monthly_users
ORDER BY action_month;

 

1일에 등록한 사용자가 30일 이상 사용하지 앟으면 리피트 사용자가 되지 않지만, 월의 마지막날에 등록한 사용자는 2일만 사용해도 리피트 사용자로 간주된다는 점이 문제점이다. 필요시 독자적인 정의를 추가하여 계산

---

12-8.  방문 종류를 기반으로 성장지수 집계하기

CAST 식, LAG 함수, SUM(CASE~), 성장 지수

성장지수

: 사용자의 서비스 사용과 관련된 상태 변화를 수치화한 것

- 성장 지수가 1이상이면 성장한다는 의미이며, 0보다 낮다면 서비스가 퇴보중인 것

 

서비스 사용과 관련된 상태 변화 패턴

1. Signup: 신규 등록하고 사용 시작
2. Deactivation: 액티브 유저가 비액티브 유저가 됨
3. Reactivation: 비액티브 유저가 액티뷰 유저로 돌아옴
4. Exit: 서비스를 탈퇴하거나 사용을 중지함

성장지수 집계 방법

'서비스를 쓰게 된 사용자'와 '떠난 사용자'를 집계하고, 어떤 사용자가 많은지 비교

- 즉, 계속해서 사용하거나/사용하지 않는 중간 위치에 속한 사용자는 성장지수에 영향을 주지 않음

- 성장 지수 개선 방법에는 'Singup'과 'Reactivation'을 높이거나, 'Deactivation'을 낮추는 방법이 있을 것

---

성장지수 집계하기

action_log

WITH 
unique_action_log AS(
SELECT
DISTINCT user_id,
SUBSTRING(stamp, 1, 10) AS action_date
FROM action_log)

 

중복 없이 고유한 user_id와 활동 날짜 추출

일시테이블 unique_action_log

,
mst_calendar AS(
SELECT '2016-10-01' AS dt
UNION ALL SELECT '2016-11-01' AS dt
UNION ALL SELECT '2016-12-01' AS dt
UNION ALL SELECT '2017-01-01' AS dt),
target_date_with_user AS(
SELECT
c.dt AS target_date,
m.user_id,
m.register_date,
m.withdraw_date
FROM mst_users AS m
CROSS JOIN mst_calendar AS C)
SELECT * FROM target_date_with_user;

 

mst_usrs과 mst_calendar로 모든 가능한 사용자+날짜 조합 생성

일시테이블 target_date_with_user

-- target_date_with_user까지 동일
,
user_status_log AS(
SELECT
u.target_date,
u.user_id,
u.withdraw_date,
a.action_date,
CASE WHEN u.register_date = a.action_date THEN 1 ELSE 0 END AS is_new,
CASE WHEN u.withdraw_date = a.action_date THEN 1 ELSE 0 END AS is_exit,
CASE WHEN u.target_date = a.action_date THEN 1 ELSE 0 END AS is_access,
LAG(CASE WHEN u.target_date = a.action_date THEN 1 ELSE 0 END)
OVER(PARTITION BY u.user_id ORDER BY u.target_date
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS was_access
FROM target_date_with_user as u
LEFT JOIN unique_action_log AS a
ON u.user_id = a.user_id
AND u.target_date = a.action_date
WHERE u.register_date<u.target_date
AND(u.withdraw_DATE IS NULL OR  u.target_date <= u.withdraw_date))
SELECT
target_date,
user_id,
is_new,
is_exit,
is_access,
was_access
FROM user_status_log;

 

a. CASE문으로 새로 가입했는지, 탈퇴했는지, 접속했는지 여부 판단

b. LAG함수로 이전 날짜에 접속했는지 여부 확인 (order by 1 preceding~)

c. WHERE절로 target_date에 활동중인 사용자만 포함

- register_date가 target_date 이전이고, 사용자가 탈퇴 전이고(withdraw_date IS NULL), target_date가 withdarw_date보다 이전인 경우만

 

매일의 성장지수 계산하기

Signup, Reactivation, Deactivation, Exit, Growht_index 날짜별로 계산

-- user_status_log까지 동일
,
user_growth_index AS(
SELECT *,
CASE WHEN is_new + is_exit = 1 THEN 
CASE WHEN is_new = 1 THEN 'signup' -- 어떤 날짜에 신규 등록(signup), 탈퇴한 경우(exit) 판정
WHEN is_exit = 1 THEN 'exit' END
WHEN is_new + is_exit = 0 THEN
CASE WHEN was_access = 0 AND is_access = 1 THEN 'reactivation'
WHEN was_access = 1 AND is_access = 0 THEN 'deactivation' END
END AS growth_index
FROM user_status_log)
SELECT * FROM user_growth_index;

 

'CASE WHEN is_new + is_exit = 1'

→ 두 값 중 하나는 1이고, 하나는 0인 경우

→ 그 날짜에 신규로 가입했거나, 탈퇴한 경우

→ sign_up이랑 exit 할당

 

'CASE WHEN is_new + is_exit = 0'

→ 두 값 모두 0인 경우

→ 그 날짜에 가입하거나 탈퇴하지 않은 경우

→ reactivation(was_access=0, is_access=1)과 deactivation(was_access=1, is_access=0) 

---

12-9. 지표 개선 방법 익히기

지표를 향상시키려면

1. 달성하고(높이고) 싶은 지표 정하기
2. 사용자 행동 중 지표에 영향을 많이 줄 것 같은 행동 결정하기
3. 결정한 행동 여부와 횟수를 집계하고, 결정한 지표를 만족하는 사용자의 비율 비교하기

EX.

1. 다음날 지속률 개선 목표

2. 다음날 지속해서 사용하는 사용자가 등록 당일에 특정 액션을 취하는 경향이 있었음

3. 등록 당일에 액션했는지 여부를 집계하고, 다음날 지속률 비율을 비교

 

EX2.

1. 글의 업로드/댓글 수를 늘리고 싶은 경우

2. 팔로우된 사람 수에 따라 차이가 있는지 확인 (사용자의 행동 중 팔로우 여부가 글/댓글 수에 영향)

3. 팔로우된 사람에 따라 글의 업로드/댓글 수가 다른지 비율 비교

 

그외

• 신규 사용자의 리피트율 개선 - 등록한 달 올린 글의 수/팔로한 사람 수/7일 이내 사용 일수에 따라 리피트율의 차이가 있는지 확인
• CVR 개선 - 구매 전 상세 페이지를 본 횟수/관심 상품 기능 사용 여부에 따라 차이가 있는지 확인

혹은 행동이 아니더라도, 예를 들어 남성보다 여성의 다음날 지속률이 높다고 판명되었다면, 이를 기반으로 광고 배너, 사이트 디자인 등을 재검토할 수 있다. 

이처럼 개선하고 싶은 지표에 대해 다양한 가설을 세워 검증해보면, 주력해야 하는 부분과 대책 등을 찾을 수 있음

---