[데이터 분석을 위한 SQL 레시피] 11강 - 사용자 전체의 특징과 경향 찾기

목차

1) 사용자의 액션 수 집계하기

2) 연령별 구분 집계하기

3) 연령별 구분의 특징 추출하기

4) 사용자의 방문 빈도 집계하기

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

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

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

 

1) 사용자의 액션 수 집계하기

사용자가 서비스 내부에서 제공되는 기능 등을 얼마나 이용하는지 집계하는 작업

• 액션과 관련된 지표 집계하기
  • 사용률 (usage_rate) = 특정 액션 UU를 전체 액션 UU로 나눈 것. → 특정 액션을 얼마나 자주 사용하는지 (** UU = Unique Users. 중복없이 집계된 사용자 수)
  • 1명 당 액션 수 (count_per_user) → 사용자가 평균적으로 액션을 몇 번이나 사용했는지

  # 11-1 액션 수와 비율을 계산하는 쿼리
  
  WITH
  stats AS(
  	-- 로그 전체의 유니크 사용자 수 구하기
  	SELECT COUNT(DISTINCT session) AS total_uu  #밑에선 group by를 해버리니까 전체 UU는 이렇게 따로 구함. 밑에서 사용하기도 하고.
  	FROM action_log
  )
  SELECT
  	l.**action**
  	-- 액션 UU
  	, COUNT(DISTINCT l.session) AS **action_uu**  # 얘네는 group by action 한거라 위에 total_uu 랑 값이 다름. 특정 액션 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
  		state AS s
  GROUP BY
  	l.action, s.total_uu
  ;

** COUNT(*) vs. COUNT(1)

-두 명령문 모두 동일한 방식으로 작동, 성능상 차이 거의 없음, 가독성을 위해 1보다는 * 사용을 권장.

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

로그인하지 않아도 서비스 일부를 사용할 수 있는 사이트의 경우, 회원과 비회원을 따로 나누어 집계하는 것이 좋음. → 충성도 높은 사용자와 낮은 사용자의 경향 분석

 

user_id 에 값이 들어있다면 → login

로그인 했을 때 출력되는 user_id 값이 빈 레코드 → guest(비로그인 사용자)

login_status가 guest와 login 인 경우 모두 전체 all 로 함께 집계 (ROLL UP 사용)

# 11-2, 11-3 로그인 상태 판별, 상태에 따라 액션 수 등을 따로 집계하는 쿼리

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   #user_id가 null이면 ''(빈 문자 반환) -> 빈 문자와 빈 문자가 같지 않다면(user_id가 null이나 빈문자 ''가 아니라면) login 반환
		AS login_status
	FROM 
		action_log
)
SELECT
	COALESCE(action, 'all') AS action   # 빈값이면 'all' (합치고 이름 없어져서 null 되니까 그 행은 all로 표현)
	, COALESCE(login_status, 'all') AS login_status
	, COUNT(DISTINCT session) AS action_uu
	, COUNT(1) AS action_count
FROM
	action_log_with_status
GROUP BY
	ROLLUP(action, login_status)
;

# 실행 결과

action  |  login_status  | action_uu  |  action_count
-----------------------------------------------------
add_cart | guest   |  89   | 348
add_cart | login   |  301  | 630
add_cart | all     |  381  | 978     # action_uu 값이 390(guest+login) 아닌 이유는 action과 login_status는 로그 정보의 user_id 정보를 기반으로 집계한 데이터이므로, (비로그인 사용자가 로그인하면 각각의 액션에 1씩 추가됨.) 반대로 all 은 session을 기반으로 집계한 것.
purchase | login   |  234  | 819
purchase | all     |  234  | 819
view     | guest   |  3425 | 48922
view     | login   |  1034 | 3161
view     | all     |  4365 | 52083
all      | all     |  4365 | 53880

** COALESCE 함수 → parameter를 순차적으로 확인하여 앞에 값이 null이면 뒤에 값을 사용하고, 만약 해당값도 null이면, 그 뒤에 들어온 값을 사용한다.

** <> - ≠ ‘다르다’

 

• 회원과 비회원을 구분해서 집계하기

로그인 상태의 사용자와 비로그인 상태의 사용자만 구분하고 싶다면 위에걸로 ok.

but, 로그인하지 않은 상태라도, 이전에 한 번이라도 로그인했다면 회원으로 계산하고 싶을수도 있는 경우 회원 상태 추가

# 11-4 회원 상태를 판별하는 쿼리

WITH
action_log_with_status AS(
	SELECT
		session
		, user_id
		, action
		-- 로그를 타임스탬프 순서로 나열하고, 한번이라도 로그인한 사용자일 경우 이후의 모든 로그 상태를 member로 설정
		, CASE WHEN
				COALESCE(MAX(user_id) # 해당 세션에 한번이라도 로그인했다면
					OVER(PARTITION BY session ORDER BY stamp #해당 session에서 한 번이라도 로그인 했다면 MAX(user_id)로 사용자 ID 추출 가능. 로그인하기 이전의 상태를 비회원으로 다루기 위해 ORDER BY stamp 지정
						ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW #처음부터 현재행까지
						, '') <> '' #MAX(user_id)가 null이라면 ''(빈 문자) 반환 -> 빈 문자와 빈 문자가 같지 않다면(MAX(user_id) null이나 빈문자 ''가 아니라면)
					THEN 'member' #'member'반환
				ELSE 'none'
			END AS member_status
			, stamp
	FROM 
		action_log
)
SELECT *
FROM 
	action_log_with_status
;

 

2) 연령별 구분 집계하기

연령별 구분 = 성별과 연령을 구분하는 것.

처음 가입할 때 나이를 입력받아 저장하면, 시간이 지나면서 실제 나이와 일치하지 않을 수 있음

→ 생일을 기반으로 리포트를 만드는 시점에 집계

** substring(컬럼명, 시작인덱스(1부터), 시작인덱스로부터 몇개뽑을지) = substring(string, start, length)

** 'CAST(value AS type)'

# 11-5, 11-6 사용자의 생일 계산, 성별과 연령으로 연령별 구분을 계산하는 쿼리

WITH
mst_users_with_int_birth_date AS(
	SELECT
		*
	-- 특정 날짜(2017년 1월 1일)의 정수표현
		, 20170101 AS int_specific_date
	-- 문자열로 구성된 생년월일을 정수 표현으로 변환하기
		, CAST(replace(substring(birth_date,1,10),'-','') AS integer) AS int_birth_date # 1부터 10까지 - 포함해서 뽑은 거에서 '-'를 ''로 변환
	FROM
		mst_users
)
, mst_users_with_age AS (
	SELECT
		*
	-- 특정 날짜(2017년 1월 1일)의 나이 (특정날짜 - 생일)
		, floor((int_specific_date - int_birth_date) / 10000) AS age
	FROM
		mst_users_with_int_birth_date
)
, mst_users_with_category AS(
	SELECT
		user_id
		, sex
		, age
		, CONCAT(
				CASE
					WHEN 20 <= age THEN sex     # 연령이 20살 이상이라면, 성별 접두사로 'M' 또는 'F'를 출력함.
					ELSE ''
				END
				, CASE
						WHEN age BETWEEN 4 AND 12 THEN 'C'   # 나이 구분에 따라, 참고로 연령이 3살 이하인 사용자인 경우 연령 구분 코드가 NULL이 되어 CONCAT의 결과도 NULL(CONCAT은 매개 변수 중 하나만 NULL이어도 전체 결과가 NULL이 되기 때문)
						WHEN age BETWEEN 13 AND 19 THEN 'T'
						WHEN age BETWEEN 20 AND 34 THEN '1'
						WHEN age BETWEEN 35 AND 49 THEN '2'
						WHEN age >= 50 THEN '3'
					END
				) AS category
	FROM
		mst_user_with_age
)
SELECT *
FROM
	mst_users_with_category
;

 

3) 연령별 구분의 특징 추출하기

연령별로 각각 구매한 상품의 카테고리 집계

# 11-8 연령별 구분과 카테고리를 집계하는 쿼리

WITH
mst_users_with_int_birth_date AS(
	SELECT
		*
	-- 특정 날짜(2017년 1월 1일)의 정수표현
		, 20170101 AS int_specific_date
	-- 문자열로 구성된 생년월일을 정수 표현으로 변환하기
		, CAST(replace(substring(birth_date,1,10),'-','') AS integer) AS int_birth_date # 1부터 10까지 - 포함해서 뽑은 거에서 '-'를 ''로 변환
	FROM
		mst_users
)
, mst_users_with_age AS (
	SELECT
		*
	-- 특정 날짜(2017년 1월 1일)의 나이 (특정날짜 - 생일)
		, floor((int_specific_date - int_birth_date) / 10000) AS age
	FROM
		mst_users_with_int_birth_date
)
, mst_users_with_category AS(
	SELECT
		user_id
		, sex
		, age
		, CONCAT(
				CASE
					WHEN 20 <= age THEN sex     # 연령이 20살 이상이라면, 성별 접두사로 'M' 또는 'F'를 출력함.
					ELSE ''
				END
				, CASE
						WHEN age BETWEEN 4 AND 12 THEN 'C'   # 나이 구분에 따라, 참고로 연령이 3살 이하인 사용자인 경우 연령 구분 코드가 NULL이 되어 CONCAT의 결과도 NULL(CONCAT은 매개 변수 중 하나만 NULL이어도 전체 결과가 NULL이 되기 때문)
						WHEN age BETWEEN 13 AND 19 THEN 'T'
						WHEN age BETWEEN 20 AND 34 THEN '1'
						WHEN age BETWEEN 35 AND 49 THEN '2'
						WHEN age >= 50 THEN '3'
					END
				) AS category
	FROM
		mst_users_with_age
)

SELECT
	p.category AS product_category
	, u.category AS user_category
	, COUNT(*) AS purchase_count
FROM
	action_log AS p
	JOIN  # JOIN = INNER JOIN
		mst_users_with_category AS u
	ON p.user_id = u.user_id
WHERE
	-- 구매로그만 선택하기
	action = 'purchase'
GROUP BY
p.category, u.category
ORDER BY
	p.category, u.category
;

 

4) 사용자의 방문 빈도 집계하기

‘서비스를 한 주 동안 며칠 사용하는 사용자가 몇 명인지’

# 11-9 한 주에 며칠 사용되었는지를 집계하는 쿼리 -> 사용자 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(DISINCT dt) AS action_day_count
		FROM
			action_log_with_dt
		WHERE
			--2016년 11월 1일부터 11월 7일까지의 한 주 동안을 대상으로 지정
			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
;

# 실행 결과

action_day_count   |  user_count
-----------------------------------
1       |     38752
2       |     21392
3       |     18760
...

# 한 주동안 하루 들어온 사람 38752, 2일 사용한 사람 21392, 3일 사용한 사람 18760

 

 

구성비와 구성비누계 산출

# 11-10 구성비와 구성비누계를 계산하는 쿼리
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(DISINCT dt) AS action_day_count
		FROM
			action_log_with_dt
		WHERE
			--2016년 11월 1일부터 11월 7일까지의 한 주 동안을 대상으로 지정
			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 * <며칠별들어온사용자수>/<전체 들어온 사용자수>
	, 100.0
		* COUNT(DISTINCT user_id)
		/ SUM(COUNT(DISTINCT user_id)) OVER()
		AS composition_ratio

-- 구성비누계 : 100.0 * <며칠별들어온 누계사용자수>/<전체 들어온 사용자수>
	, 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
;

 

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

여러 기능의 사용 상황을 조사한 뒤 제공하는 기능을 사용자가 받아들이는지 확인

** SIGN 함수 → 매개변수가 양수라면 1, 0이라면 0, 음수라면 -1을 리턴하는 함수

** CUBE 구문 → 모든 조합에 대한 사용자 수를 세는 쿼리

** nullif(1,2) : 1과 2가 같으면 null 반환 -> nullif(impression, 0) : impression이 0이면 null 반환

# 11-11,12, 15 사용자들의 액션 플래그를 집계, 모든 액션 조합에 대한 사용자 수 계산, 벤다이어그램을 만들기 위해 데이터를 가공하는 쿼리

WITH
user_action_flag AS(
	-- 사용자가 액션을 했으면 1, 안했으면 0으로 플래그 붙이기
	SELECT
		user_id
		, SIGN(SUM(CASE WHEN action = 'purchase' THEN 1 ELSE 0 END)) AS has_purchase  # purchase에 해당되어 1인거 다 합쳐져서 양수 되면 sign 함수 거쳐서 1로 나오고, 0은 합쳐져도 0으로
		, 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(
	-- CUBE를 사용해서 모든 액션 조합 구하기
	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
	--0, 1 플래그를 문자열로 가공하기
	CASE has_purchase
		WHEN 1 THEN 'purchase' WHEN 0 THEN 'not purchase' ELSE 'any'  #else-> null 에 해당하는 경우(해당 액션을 했는지 안했는지 모르는 경우)
	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'  # purchase/any/any -> 구매액션을 한 사용자, purchase/not_review/any -> 구매 액션을 했지만 리뷰를 작성하지 않은.
	END AS has_favorite
	, users
		-- 전체 사용자 수를 기반으로 비율 구하기
	, 100.0 * users
		/ NULLIF(
		-- 모든 액션이 NULL인 사용자 수가 전체 사용자 수를 나타내므로 해당 레코드의 사용자 수를 WINDOW 함수로 구하기(any/any.any -> 모든 사용자 수)
				SUM(CASE WHEN has_purchase IS NULL
								AND has_review IS NULL
								AND has_favorite IS NULL
								THEN users ELSE 0 END) OVER()
			,0) # 0인건 null로 바꿈으로써 0으로 나누는 것 피하기
		AS ratio
FROM action_venn_diagram
ORDER BY has_purchase, has_review, has_favorite
;

 

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

Decile = 10분의 1

→ 데이터를 10단계로 분할해서 중요도를 파악하는 분석

 

사용자의 구매 금액에 따라 순위를 구분하고 중요도를 파악하는 리포트를 만들 때

1. 사용자를 구매 금액이 많은 순으로 정렬
2. 정렬된 사용자 상위부터 10%씩 Decile 1부터 Decile 10까지의 그룹을 할당
3. 각 그룹의 구매 금액 합계를 집계
4. 전체 구매 금액에 대해 각 Decile의 구매 금액 비율(구성비)를 계산
5. 상위에서 누적으로 어느 정도의 비율을 차지하는지 구성비누계를 집계

 

** ntile 윈도 함수 = N등분 한 후 자신의 위치 출력, 같은 수로 데이터 그룹 만들 때 사용

# 11-16,17,18 구매액이 많은 순서로 사용자 그룹 10등분, 10분할한 Decile들을 집계하는 쿼리, 구매액이 많은 Decile 순서로 구성비와 구성비 누계를 계산하는 쿼리

--구매액이 많은 순서로 사용자 그룹 10등분
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
)
-- 10분할한 Decile들을 집계하는 쿼리
	, decile_with_purchase_amount AS (
		SELECT
			decile
			, SUM(purchase_amount) AS amount  # 각 그룹의 합계
			, AVG(purchase_amount) AS avg_amount  # 평균 구매 금액
			, SUM(SUM(purchase_amount)) OVER (ORDER BY decile) AS cumulative_amount  # 누계 구매 금액
			, SUM(SUM(purchase_amount)) OVER() AS total_amount
		FROM
			users_with_decile
		GROUP_BY
			decile
)
-- 구매액이 많은 Decile 순서로 구성비와 구성비 누계 계산
SELECT
	decile
	, amount
	, avg_amount
	, 100.0 * amount / total_amount AS total_ratio
	, 100.0 * cumulative_amount / total_amount AS cumulative_ratio
FROM
	decile_with_purchase_amount
;

ex) decile 7~ 10은 정착되지 않은 고객을 나타냄.

 

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

decile 분석은 데이터 검색 기나에 따라 문제가 있음.

→ 검색 기간이 너무 장기간이면 과거에는 우수 고객 이었어도, 현재는 다른 서비스를 사용하는 휴먼 고객이 포함될 가능성.

→ 검색 기간이 단기간이라면 정기적으로 구매하는 안정 고객이 포함되지 않고, 해당 기간 동안에만 일시적으로 많이 구매한 사용자가 우수 고객으로 취급될 수 있음.

RFM 분석은 보다 자세하게 사용자를 그룹으로 나눌 수 있는 분석 방법

• RFM 분석의 3가지 지표 집계하기
  • Recency : 최근 구매일
  최근 무언가를 구매한 사용자를 우량 고객으로 취급 → CURRENT_DATE - MAX(dt::date) AS recency
  • Frequency : 구매 횟수
  사용자가 구매한 횟수를 세고, 많을 수로 우량 고객으로 취급 → COUNT(dt) AS frequency
  • Monetary : 구매 금액 합계
  사용자의 구매 금액 합계를 집계하고, 금액이 높을수록 우량 고객으로 취급 → SUM(amount) AS monetary
• RFM 랭크 정의하기

3개의 지표를 각각 5개의 그룹으로 나눈 것이 일반적.

⇒ 125(5X5X5)개의 그룹으로 사용자를 나눠 파악할 수 있음.

 

#11-19,20,21 사용자별로 RFM을 집계, 사용자들의 RFM 랭크를 계산, 각 그룹의 사람 수를 확인.

--사용자별로 RFM을 집계
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
		, COUNT(dt) AS frequency
		, SUM(amount) AS monetary
	FROM
		purchase_log
	GROUP BY
		user_id
)
-- 사용자들의 RFM 랭크를 계산
, user_rfm_rank AS(
	SELECT
		user_id
		, recent_date
		, recency
		, frequency
		, monetary
		, CASE
				WHEN regency < 14 THEN 5
				WHEN regency < 28 THEN 4
				WHEN regency < 60 THEN 3
				WHEN regency < 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
)
-- 각 그룹의 사람 수 확인
, 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
		m.rfm_index
		, CASE WHEN m.rfm_index = r.r THEN 1 ELSE 0 END AS r_flag  # 같은 r 가진 사람 1로 처리(한 명) -> 이후 뒤에서 합치면 각 그룹(랭크)에 속한 사람 확인가능
		, CASE WHEN m.rfm_index = r.f THEN 1 ELSE 0 END AS f_flag
		, CASE WHEN m.rfm_index = r.m THEN 1 ELSE 0 END AS m_flag
	FROM
		mst_rfm_index AS m
	CROSS JOIN
		user_rfm_rank AS r
)
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      |  100  |   1   |   2
4      |  77   |   13  |   2
3      |  84   |   159 |   6
2      |  23   |   108 |   259
1      |  12   |   15  |   27

 

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

RFM의 각 랭크 합계를 기반으로 13개 그룹으로 나누어 관리

#11-23 종합 랭크별로 사용자 수를 집계하는 쿼리

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
		, COUNT(dt) AS frequency
		, SUM(amount) AS monetary
	FROM
		purchase_log
	GROUP BY
		user_id
)
-- 사용자들의 RFM 랭크를 계산
, user_rfm_rank AS(
	SELECT
		user_id
		, recent_date
		, recency
		, frequency
		, monetary
		, CASE
				WHEN regency < 14 THEN 5
				WHEN regency < 28 THEN 4
				WHEN regency < 60 THEN 3
				WHEN regency < 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
)
-- 각 그룹의 사람 수 확인
, 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
)
--여기부터 새로
SELECT
	r + f + m AS total_rank
	, COUNT(user_id)
FROM
	user_rfm_rank
GROUP BY
	total_rank #postgreSQL의 경우 SELECT 구문에서 정의한 별칭을 group by 구문에 지정할 수 있음
ORDER BY
	total_rank DESC;

 

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

RFM 지표 2개를 사용해서 사용자 층을 정의하는 방법.

# 11-24 R과 F를 사용해 2차원 사용자 층의 사용자 수를 집계하는 쿼리


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
		, COUNT(dt) AS frequency
		, SUM(amount) AS monetary
	FROM
		purchase_log
	GROUP BY
		user_id
)
-- 사용자들의 RFM 랭크를 계산
, user_rfm_rank AS(
	SELECT
		user_id
		, recent_date
		, recency
		, frequency
		, monetary
		, CASE
				WHEN regency < 14 THEN 5
				WHEN regency < 28 THEN 4
				WHEN regency < 60 THEN 3
				WHEN regency < 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
	CONCAT('r_',r) AS r_rank
	, COUNT(CASE WHEN f=5 THEN 1 END) AS f_5  # 각각 f에 해당하는거 세기
	, 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 ;