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

데이터분석/PostgreSQL

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

환성 2023. 7. 5. 15:33

728x90

목차

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

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

3. 연령별 구분 집계하기

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

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

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

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

실습에 필요한 테이블

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

액션과 관련된 지표를 집계하는데에는 사용률(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;

저작자표시 변경금지

'데이터분석 > PostgreSQL' 카테고리의 다른 글

웹사이트에서의 행동을 파악하는 데이터 추출하기 (0)	2023.07.17
사용자를 파악하기 위한 데이터 추출 - 2 (2)	2023.07.05
시계열 기반으로 데이터 집계하기 (0)	2023.07.04
여러 개의 테이블에 대한 조작하기 (0)	2023.07.04
테이블에 대한 조작하기 (0)	2023.07.04

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

컴공생의 개인공부일지

데이터 분석가가 되기위해

postgresql #sql #데이터분석, SQL #데이터리안 #데이터 분석 캠프, R #통계학 #컴퓨터공학 #ML, sql #postgresql, 머신러닝 #크롤링, 데이터 #postgresql #sql, UST, SQLD #DB, pyspark #Jupyter Lab #Docker, UST #인턴, ADSP #DB, 알고리즘, 소프트웨어공학, tableau, 파이썬, postgresql #sql, R #통계학 #ML, 영어 #숙어 #idiom, 벅스 #Bugs #워드클라우드, wsl2 #docker,

Today :
Yesterday :

컴공생의 개인공부일지

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

실습에 필요한 테이블

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

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

연령별 구분 집계하기

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

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

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

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

'데이터분석 > PostgreSQL' 카테고리의 다른 글

'데이터분석/PostgreSQL'의 다른글

티스토리툴바

« 2024/11 »
일	월	화	수	목	금	토
					1	2
3	4	5	6	7	8	9
10	11	12	13	14	15	16
17	18	19	20	21	22	23
24	25	26	27	28	29	30

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

실습에 필요한 테이블

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

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

연령별 구분 집계하기

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

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

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

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

'데이터분석 > PostgreSQL' 카테고리의 다른 글

'데이터분석/PostgreSQL'의 다른글

관련글

티스토리툴바