사용자를 파악하기 위한 데이터 추출 - 1

목차

1. 사용자의 액션 수와 비율 계산하기

 

2. 로그인 사용자와 비로그인 사용자를 구분해서 집계하기

 

3. 연령별 구분 집계하기

 

4. 한 주에 며칠정도 사용되었는지를 집계하기

 

5. 구성비와 구성비누계 산출하기

 

6. Decile 분석을 사용해 사용자를 10단계 그룹으로 나누기

 

7. RFM 분석으로 사용자를 3가지 관점의 그룹으로 나누기

 

실습에 필요한 테이블

사용자 마스터(mst_users) 테이블

사용자 로그(action_log) 테이블

사용자의 액션 수와 비율 계산하기

• 액션과 관련된 지표를 집계하는데에는 사용률(usage_rate)이 필요하다
• 특정 액션 UU를 전체 액션 UU로 나눈 것을 사용률(usage_rate)이라고 한다.
• UU : Unique Users 중복없이 집계된 사용자 수 

WITH 
stats AS (
	-- 로그 전체의 유니크 사용자 수 구하기
	SELECT COUNT(DISTINCT session) AS total_uu
	FROM action_log
)
SELECT
	l.action
	-- 액션 UU
	, COUNT(DISTINCT l.session) AS action_uu
	-- 액션의 수
	, COUNT(1) AS action_count
	-- 전체 UU
	, s.total_uu
	-- 사용률 : <액션 UU> / <전체 UU>
	, 100.0 * COUNT(DISTINCT l.session) / s.total_uu AS usage_rate
	-- 1인당 액션 수 : <액션 수> / <액션 UU>
	, 1.0 * COUNT(1) / COUNT(DISTINCT l.session) AS count_per_user
FROM 
	action_log AS l
        -- 로그 전체의 유니크 사용자 수를 모든 레코드에 결합하기
		CROSS JOIN 
			stats AS s
GROUP BY
	l.action, s.total_uu;

 

 

로그인 사용자와 비로그인 사용자를 구분해서 집계하기

• 로그인 하지 않아도 서비스의 일부를 사용할 수 있는 사이트의 경우 일반적으로 회원, 비회원을 나누어 집계한다.
• 이를 판별하려면 로그 데이터에 세션에 대한 정보가 있어야 한다.

WITH 
action_log_with_status AS (
	SELECT
		session
		, user_id
		, action
		-- user_id가 NULL 또는 빈 문자가 아닌 경우 login이라고 판정하기
		, CASE WHEN COALESCE(user_id, '') <> '' THEN 'login' ELSE 'guest' END AS login_status
	FROM 
	action_log
)
SELECT *
FROM 
	action_log_with_status;

 

 

연령별 구분 집계하기

• 광고 디자인과 캐치 프레이즈를 검토하려면 사용자 속성 집계해야 한다.
• CAST와 substring을 이용해 생년월일로 표현된 것을 정수 형태로 타입캐스트를 진행한다.
• 이후 날짜에서 출생 날짜를 빼고 10000을 나누어 주는데 그 이유는 날짜 형식은 8자리 숫자로 나타내게 되는데 Ex.) 20230705 뺄셈을 진행하면 5자리수가 남게되고 그것을 10000으로 나누어주면 나이 형식인(1자리~2자리 수만 남게 된다)

WITH 
mst_users_with_int_birth_date AS (
	SELECT *
	-- 특정 날짜의 정수 표현
	, 20170101 AS int_specific_date
	-- 문자열로 구성된 생년월일을 정수 표현으로 변환하기
	, CAST(replace(substring(birth_date, 1, 10), '-', '') AS integer) AS int_birth_date
	FROM mst_users
)
, mst_users_with_age AS(
	SELECT *
	-- 특정 날짜의 나이
	, floor((int_specific_date - int_birth_date) / 10000) AS age
	FROM mst_users_with_int_birth_date
)
SELECT 
	user_id, sex, birth_date, age
FROM 
	mst_users_with_age;

 

 

한 주에 며칠정도 사용되었는지를 집계하기

• .사용자 ID, 액션, 날짜가 기록되어 있는 action_log 테이블이 있을 때 사용자 ID별로 날짜에 DISTINCT 적용시 사용 일수를 집계할 수 있다.
• 로그에 시각까지 기록되어 있는 경우, 시각 부분을 뗴어버린 후 집계하면 된다.

WITH 
action_log_with_dt AS (
	SELECT *
		-- 타임 스탬프에서 날짜 추출하기
		, substring(stamp, 1, 10) AS dt
	FROM action_log
)
, action_day_count_per_user AS (
	SELECT 
		user_id
		, COUNT(DISTINCT dt) AS action_day_count
	FROM
		action_log_with_dt
	WHERE
		dt BETWEEN '2016-11-01' AND '2016-11-07'
	GROUP BY
		user_id
)
SELECT
	action_day_count
	, COUNT(DISTINCT user_id) AS user_count
FROM
	action_day_count_per_user
GROUP BY
	action_day_count
ORDER BY
	action_day_count;

 

 

구성비와 구성비누계 산출하기

WITH 
action_log_with_dt AS (
	SELECT *
		-- 타임 스탬프에서 날짜 추출하기
		, substring(stamp, 1, 10) AS dt
	FROM action_log
)
, action_day_count_per_user AS (
	SELECT 
		user_id
		, COUNT(DISTINCT dt) AS action_day_count
	FROM
		action_log_with_dt
	WHERE
		dt BETWEEN '2016-11-01' AND '2016-11-07'
	GROUP BY
		user_id
)
SELECT
	action_day_count
	, COUNT(DISTINCT user_id) AS user_count
	-- 구성비
	, 100.0 * COUNT(DISTINCT user_id) / SUM(COUNT(DISTINCT user_id)) OVER() AS composition_ratio
	-- 구성비누계
	, 100.0 * SUM(COUNT(DISTINCT user_id)) OVER(ORDER BY action_day_count ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
	/ SUM(COUNT(DISTINCT user_id)) OVER() AS cumulative_ratio
FROM
	action_day_count_per_user
GROUP BY
	action_day_count
ORDER BY
	action_day_count;

 

 

Decile 분석을 사용해 사용자를 10단계 그룹으로 나누기

• 데이터를 10단계로 분할해서 중요도를 파악하는 것을 Decile 분석이라고 한다.
• 분석과정
  
  1. 사용자를 구매 금액이 많은 순으로 정렬한다.
  2. 정렬된 사용자 상위부터 10%씩 Decile1부터 Decile10까지 그룹을 할당한다.
  3. 각 그룹의 구매 금액 합계를 집계한다.
  4. 전체 구매 금액에 대해 각 Decile의 구매 금액 비율(구성비)를 계산한다.
  5. 상위에서 누적으로 어느 정도의 비율을 차지하는지 구성비누계를 집계한다.
• NTILE 함수를 사용한다.

WITH
user_purchase_amount AS (
		SELECT
			user_id
			, SUM(amount) AS purchase_amount
		FROM
			action_log
		WHERE
			action = 'purchase'
		GROUP BY
			user_id
)
, users_with_decile AS (
		SELECT
			user_id
			, purchase_amount
			, ntile(10) OVER (ORDER BY purchase_amount DESC) AS decile
		FROM
			user_purchase_amount
)
SELECT *
FROM 
	users_with_decile;

 

 

RFM 분석으로 사용자를 3가지 관점의 그룹으로 나누기

• RFM 분석으로 Decile 분석보다 자세하게 사용자 그룹을 나눌 수 있다.
• Recency : 최근 구매일
• Frequency : 구매 횟수
• Monetary : 구매 금액 합계
• Decile 분석에서는 한 번의 구매로 비싼 물건을 구매한 사용자와 정기적으로 저렴한 물건을 여러 번 구매한 사용자 그룹을 나눌 수 없었지만, RFM 분석에서는 가능하다.
• RFM 랭크 정의하기(3개의 지표를 5개 그룹으로 나누는게 일반적이다)
• RFM 랭크 정의 테이블

랭크	R : 최근 구매일	F : 누계 구매 횟수	M : 누계 구매 금액
5	14일 이내	20회 이상	300만원 이상
4	28일 이내	10회 이상	100만원 이상
3	60일 이내	5회 이상	30만원 이상
2	90일 이내	2회 이상	5만원 이상
1	91일 이내	1회	5만원 미만

WITH
user_rfm AS (
	SELECT
		user_id
		, MAX(dt) AS recent_date
		, CURRENT_DATE - MAX(dt::date) AS recency
		, COUNT(dt) AS frequency
		, SUM(amount) AS monetary
	FROM
		purchase_log
	GROUP BY
		user_id
)
, user_rfm_rank AS (
	SELECT
		user_id
		, recent_date
		, recency
		, frequency
		, monetary
		, CASE
			WHEN recency < 14 THEN 5
			WHEN recency < 28 THEN 4
			WHEN recency < 60 THEN 3
			WHEN recency < 90 THEN 2
				ELSE 1
			END AS r
		, CASE
			WHEN 20 <= frequency THEN 5
			WHEN 10 <= frequency THEN 4
			WHEN 5 <= frequency THEN 3
			WHEN 2 <= frequency THEN 2
			WHEN 1 <= frequency THEN 1
			END AS f
		, CASE
			WHEN 300000 <= monetary THEN 5
			WHEN 100000 <= monetary THEN 4
			WHEN 30000 <= monetary THEN 3
			WHEN 5000 <= monetary THEN 2
				ELSE 1
			END AS m
		FROM 
			user_rfm
)
SELECT *
FROM 
	user_rfm_rank;