[데이터분석을 위한 SQL 레시피] 11강(2)

june·2023년 5월 10일

sql

SQL

목록 보기

27/31

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

사용자의 행동 패턴으로 속성을 정의하는 방법과 서비스 실태를 확인하는 지표를 알아보자
코드에서 중복 쿼리는 일부 생략함

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

벤 다이어그램으로 사용자 액션 집계하기

SQL SIGN함수, SUM함수, CASE식, CUBE구문

서비스 내부의 여러 기능을 모두 사용하는 사용자는 많지 않다.
따라서 각 기능의 사용 상황을 조사해야 한다.

SELECT *
FROM action_log

코드 11-11

WITH user_action_flag AS (
	-- 사용자가 액션을 했으면 1, 안 했으면 0으로 플래그 붙이기
	SELECT user_id
		 , SIGN(SUM(CASE WHEN ACTION = 'purchase' THEN 1 ELSE 0 END)) AS has_purchase
		 , SIGN(SUM(CASE WHEN ACTION = 'review' THEN 1 ELSE 0 END)) AS has_review
		 , SIGN(SUM(CASE WHEN ACTION = 'favorite' THEN 1 ELSE 0 END)) AS has_favorite
	FROM action_log
	GROUP BY user_id
)
SELECT *
FROM user_action_flag

user_id	has_purchase	has_review	has_favorite
U002	1.0	0.0	0.0
U001	1.0	1.0	1.0

SIGN( ) : 양수이면 1, 음수이면 -1을 반환한다.
이유는 나중에 RFM 분석에서 사용될 RFM 스코어를 계산할 때, 고객의 행동이 있는지 없는지 여부를 -1로 표시하여 더 나은 분석 결과를 얻기 위해서 (링크)

코드 11-12 (postgreSQL)

WITH action_venn_diagram AS (
	-- 모든 액션 조합 구하기
	SELECT has_purchase
		 , has_review
		 , has_favorite
		 , COUNT(1) AS users
	FROM user_action_flag
	GROUP BY CUBE(has_purchase, has_review, has_favorite)
)
SELECT *
FROM action_venn_diagram
ORDER BY has_purchase, has_review, has_favorite

has_purchase	has_review	has_favorite	users
1.0	0.0	0.0	1
1.0	0.0		1
1.0	1.0	1.0	1
1.0	1.0		1
1.0		0.0	1
1.0		1.0	1
1.0			2
	0.0	0.0	1
	0.0		1
	1.0	1.0	1
	1.0		1
		0.0	1
		1.0	1
			2

COUNT(1) : 해당 그룹의 행(row) 수를 세는 집계 함수. 1은 실제로 의미 없는 값이고, 열의 값이 NULL이 아닌 경우에만 해당 값의 개수를 계산

CUBE( ) : postgreSQL만 가능. 여러 그룹화 집합을 생성한다.
일반적으로 CUBE( )에 지정된 열 수가 n개 이면 2^n개의 조합이 있다. (링크)
CUBE(c1,c2,c3)
-- 조합은 8개
GROUPING SETS (
    (c1,c2,c3), 
    (c1,c2),
    (c1,c3),
    (c2,c3),
    (c1),
    (c2),
    (c3), 
    ()
 ) 

코드 11-13(표준 SQL)

WITH user_action_flag AS (
	-- 사용자가 액션을 했으면 1, 안 했으면 0으로 플래그 붙이기
	SELECT user_id
		 , SIGN(SUM(CASE WHEN ACTION = 'purchase' THEN 1 ELSE 0 END)) AS has_purchase
		 , SIGN(SUM(CASE WHEN ACTION = 'review' THEN 1 ELSE 0 END)) AS has_review
		 , SIGN(SUM(CASE WHEN ACTION = 'favorite' THEN 1 ELSE 0 END)) AS has_favorite
	FROM action_log
	GROUP BY user_id
)
, action_venn_diagram AS (
	-- 모든 액션 조합을 개별적으로 구하고 UNION ALL 로 결합
	-- 3개의 액션을 모두 한 경우 : 2row
	SELECT has_purchase, has_review, has_favorite, COUNT(1) AS users
	FROM user_action_flag
	GROUP BY has_purchase, has_review, has_favorite
	-- 3개의 액션 중 2개의 액션을 한 경우 : 6
	UNION ALL
		SELECT NULL AS has_purchase, has_review, has_favorite, COUNT(1) AS users
		FROM user_action_flag
		GROUP BY has_review, has_favorite
	UNION ALL
		SELECT has_purchase, NULL AS has_review, has_favorite, COUNT(1) AS users
		FROM user_action_flag
		GROUP BY has_purchase, has_favorite
	UNION ALL
		SELECT has_purchase, has_review, NULL AS has_favorite, COUNT(1) AS users
		FROM user_action_flag
		GROUP BY has_purchase, has_review
	-- 3개의 액션 중 1개의 액션을 한 경우 : 5
	UNION ALL
		SELECT NULL AS has_purchase, NULL AS has_review, has_favorite, COUNT(1) AS users
		FROM user_action_flag
		GROUP BY has_favorite
	UNION ALL
		SELECT NULL AS has_purchase, has_review, NULL AS has_favorite, COUNT(1) AS users
		FROM user_action_flag
		GROUP BY has_review
	UNION ALL
		SELECT has_purchase, NULL AS has_review, NULL AS has_favorite, COUNT(1) AS users
		FROM user_action_flag
		GROUP BY has_purchase
	-- 액션과 관계 없이 모든 사용자 집계 : 1
	UNION ALL
		SELECT NULL AS has_purchase, NULL AS has_review, NULL AS has_favorite, COUNT(1) AS users
		FROM user_action_flag
)
SELECT *
FROM action_venn_diagram
ORDER BY has_purchase, has_review, has_favorite

모든 미들웨어에서 동작하지만, UNION ALL 사용으로 성능은 좋지 않다.
UNION ALL을 연결할 때는 공백이 있는 줄이 있으면 에러 발생..

벤 다이어그램 만들기 위해 가독성 높게 결과를 가공하면..
코드 11-15

SELECT
-- 0, 1 플래그를 문자열로 가공하기
CASE has_purchase
	WHEN 1 THEN 'purchase' WHEN 0 THEN 'not purchase' ELSE 'any'
END AS has_purchase
, CASE has_review
	WHEN 1 THEN 'review' WHEN 0 THEN 'not review' ELSE 'any'
END AS has_review
, CASE has_favorite
	WHEN 1 THEN 'favorite' WHEN 0 THEN 'not favorite' ELSE 'any'
END AS has_favorite
, users
-- 전체 사용자 수를 기반으로 비율 구하기
, 100.0 * users / NULLIF(
-- 모든 액션이 NULL인 사용자 수가 전체 사용자 수를 나타내므로
-- 해당 레코드의 사용자 수를 윈도우 함수로 구하기
	SUM(CASE WHEN has_purchase IS NULL
			  AND has_review IS NULL
			  AND has_favorite IS NULL
			 THEN users ELSE 0 end) over()
	, 0)
	AS ratio
FROM action_venn_diagram
ORDER BY has_purchase, has_review, has_favorite

has_purchase	has_review	has_favorite	users	ratio
any	any	any	2	100.0000000000000000
any	any	favorite	1	50.0000000000000000
any	any	not favorite	1	50.0000000000000000
any	not review	any	1	50.0000000000000000
any	not review	not favorite	1	50.0000000000000000
any	review	any	1	50.0000000000000000
any	review	favorite	1	50.0000000000000000
purchase	any	any	2	100.0000000000000000
purchase	any	favorite	1	50.0000000000000000
purchase	any	not favorite	1	50.0000000000000000
purchase	not review	any	1	50.0000000000000000
purchase	not review	not favorite	1	50.0000000000000000
purchase	review	any	1	50.0000000000000000
purchase	review	favorite	1	50.0000000000000000

NULLIF(expr1, expr2) : expr1 = expr2 이면 NULL, 그렇지 않으면 expr1을 반환한다.
이 함수는 보통 분모가 0일 때 오류를 방지하고자 할 때 많이 사용되어 expr1가 0인 경우 NULL로 반환

[그림 11-6] 처럼 사용자 형태를 벤다이어그램으로 나타낼 수 있다.
어떤 액션을 수행했을 때, 효과가 발생한 사용자가 얼마나 되는지 벤 다이어그램으로 확인하면, 액션을 제대로 세웠는지 확인할 수 있다.

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

SQL NTILE 윈도 함수

사용자 특징을 분석할 때 성별, 연령 같은 데모그래픽한 데이터가 존재하지 않는 경우, 사용자 액션으로 특징을 정의해보는 것도 방법
데이터를 10단계로 분할해서 중요도를 파악하는 Decile분석 방법을 알아보자.
사용자의 구매 금액에 따라 순위를 구분하고 중요도를 구분하는 단계는 다음과 같다.
1. 사용자를 구매 금액이 많은 순으로 정렬
2. 상위부터 10%씩 Decile1 ~ 10까지 그룹을 할당
3. 그룹별 구매 금액 합계를 구함
4. (각 Decile 그룹 / 전체 구매 금액) 으로 구매 금액 비율을 계산
5. 상위에서 누적으로 어느 정도 비율을 차지하는지 구성비 누계를 집계

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

SQL CASE식, generate_series 함수

6절에서 사용자의 구매 금액 합계를 기반으로 10개 그룹으로 분석해봄
Decile분석은 검색 기간에 따라 고객 분류가 달라질 수 있다.
RFM분석은 Decile분석보다 자세하게 사용자를 그룹으로 나누는 방법이다.

RFM 분석의 3가지 지표 집계하기

Recency : 최근 구매일
Frequency : 구매 횟수
Monetary : 구매 금액 합계
즉, 사용자별로 얼마나 최근에, 얼마나 자주, 얼마나 많은 금액을 지출했는지에 따라
사용자들의 분포를 확인하거나 사용자 그룹(또는 등급)을 나누어 분류하는 분석 기법

코드 11-19

SELECT *
FROM action_log

WITH purchase_log AS (
	SELECT user_id
		 , amount
		 -- 타임스탬프 기반으로 날짜 추출하기
		 , SUBSTRING(stamp, 1, 10) AS dt
	FROM action_log
	WHERE action = 'purchase'
)
, user_rfm AS (
	SELECT user_id
		 , MAX(dt) AS recent_date
		 , CURRENT_DATE - MAX(dt::date) AS recency
	FROM purchase_log
	GROUP BY user_id
)
SELECT *
FROM user_rfm

user_id	recent_date	recency	frequency	monetary
U001	2016-11-03	2380	2	4000
U002	2016-11-04	2379	3	3000

::는 PostgreSQL에서 타입 캐스팅(type casting) 연산자. 이 연산자로 다른 데이터 타입으로 변환할 수 있다.

dt::date는 timestamp을 date 타입으로 캐스팅하여 날짜 정보만 추출한다.
CURRENT_DATE : 2023-05-11 이다.

RFM 랭크 정의하기

RFM분석에서는 3개 지표를 각 5개 그룹으로 나누는 것이 일반적이고 사용자를 125개의 그룹으로 파악할 수 있다.
[표 11-4] 테이블 참고

코드 11-20

WITH 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

user_id	recent_date	recency	frequency	monetary	r	f	m
U001	2016-11-03	2380	2	4000	1	2	1
U002	2016-11-04	2379	3	3000	1	2	1

코드 11-21

WITH mst_rfm_index AS ( 
	-- 1부터 5까지의 숫자를 가지는 테이블 만들기
			  SELECT 1 AS rfm_index
	UNION ALL SELECT 2 AS rfm_index
	UNION ALL SELECT 3 AS rfm_index
	UNION ALL SELECT 4 AS rfm_index
	UNION ALL SELECT 5 AS rfm_index
)
, rfm_flag AS (
	SELECT mst.rfm_index
		 , CASE WHEN mst.rfm_index = rnk.r THEN 1 ELSE 0 END AS r_flag
		 , CASE WHEN mst.rfm_index = rnk.f THEN 1 ELSE 0 END AS f_flag
		 , CASE WHEN mst.rfm_index = rnk.m THEN 1 ELSE 0 END AS m_flag
	FROM mst_rfm_index AS mst
		CROSS JOIN user_rfm_rank AS rnk
)
SELECT rfm_index
	 , SUM(r_flag) AS r
	 , SUM(f_flag) AS f
	 , SUM(m_flag) AS m
FROM rfm_flag
GROUP BY rfm_index
ORDER BY rfm_index DESC

rfm_index	r	f	m
5	0	0	0
4	0	0	0
3	0	0	0
2	0	2	0
1	2	0	2

SELECT n AS rfm_index : rfm_index 컬럼에 n 값을 가진 레코드를 생성

CROSS JOIN : 한 쪽 테이블의 모든 행들과 다른 테이블의 모든 행을 조인

여기서 125개 그룹은 관리하기 어려워서 적은 그룹 수로 관리하는 방법을 알아보자

사용자를 1차원으로 구분하기

RFM 각 랭크 합계를 기반으로 13개 그룹(3~15)으로 나누어 관리하는 방법

코드 11-22

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 r + f + m AS total_rank
	 , r, f, m
	 , COUNT(user_id)
FROM user_rfm_rank
GROUP BY r, f, m
ORDER BY total_rank DESC, r DESC, f DESC, m DESC

total_rank	r	f	m	count
4	1	2	1	2

이어서 종합 랭크별 사용자 수를 집계

코드 11-23

SELECT r + f + m AS total_rank
	 , COUNT(user_id)
FROM user_rfm_rank
GROUP BY total_rank
ORDER BY total_rank DESC

total_rank	count
4	2

2차원으로 사용자 인식하기

RFM 지표 2개를 사용해서 사용자 층을 정의하는 방법
이번에는 R과 F를 예를 들어 집계해본다.
각 셀에 사용자 수를 집계했다면, 높은 랭크로 사용자를 이동시키려면 어떤 액션이 필요할지 생각해보자.

SELECT *
FROM user_rfm_rank

코드 11-24

SELECT CONCAT('r_', r) AS r_rank
	 , COUNT(CASE WHEN f = 5 THEN 1 END) AS f_5
	 , COUNT(CASE WHEN f = 4 THEN 1 END) AS f_4
	 , COUNT(CASE WHEN f = 3 THEN 1 END) AS f_3
	 , COUNT(CASE WHEN f = 2 THEN 1 END) AS f_2
	 , COUNT(CASE WHEN f = 1 THEN 1 END) AS f_1
FROM user_rfm_rank
GROUP BY r
ORDER BY r_rank DESC

r_rank	f_5	f_4	f_3	f_2	f_1
r_1	0	0	0	2	0

CONCAT( ) : 여러 문자열을 이어 붙여 하나의 문자열로 만드는 함수

[그림 11-11] 을 보면서 2차원으로 인식한 사용자층을 어떤 액션을 실시할지 생각해보자.
RFM 분석의 각 지표에 따라 사용자의 속성을 정의하고 3차원 -> 1차원 -> 2차원으로 표현하는 방법을 살펴봄.
서비스 개선 검토, 사용자에 따른 메일 최적화 등 용도로 활용해보자.

june

나의 계절은

has_purchase	has_review	has_favorite	users
1.0	0.0	0.0	1
1.0	0.0		1
1.0	1.0	1.0	1
1.0	1.0		1
1.0		0.0	1
1.0		1.0	1
1.0			2
	0.0	0.0	1
	0.0		1
	1.0	1.0	1
	1.0		1
		0.0	1
		1.0	1
			2

has_purchase	has_review	has_favorite	users
1.0	0.0	0.0	1
1.0	0.0		1
1.0	1.0	1.0	1
1.0	1.0		1
1.0		0.0	1
1.0		1.0	1
1.0			2
	0.0	0.0	1
	0.0		1
	1.0	1.0	1
	1.0		1
		0.0	1
		1.0	1
			2