[PostgreSQL] 6. RFM 분석으로 사용자 그룹 나누기

RFM 분석

• Recency : 최근 구매일
  • 최근에 무언가를 구매한 사용자를 우량 고객으로 취급
• Frequency : 구매 횟수 
  • 사용자가 구매한 횟수를 세고, 많을수록 우량 고객으로 취급
• Monetary : 구매 금액 합계
  • 사용자의 구매 금액 합계를 집계하고, 금액이 높을수록 우량 고객으로 취급

 

👉 지난 포스팅에서 살펴본 Decile 분석에서는 한 번의 구매로 비싼 물건을 구매한 사용자와 정기적으로 저렴한 물건을 여러 번 구매한 사용자가 같은 그룹으로 판정되기도 한다. 하지만 RFM 분석은 이러한 사용자들도 구분할 수 있다.

 

data

---

1. RFM 구하기

• 고객별로 가장 최근 구매일과 구매 금액이 있다면 RFM을 구할 수 있다.
• max(dt)로 가장 최근 구매일을 구하고, current_date와의 날짜 차이를 구한다(postgreSQL은 날짜끼리 빼기 연산 지원)
  • 이때 max(dt::date)로 날짜형으로 바꿔줘야 한다.

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
)
SELECT *
FROM user_rfm
;

---

2. RFM 랭크 정의하기

• 3개의 지표를 각각 4개 또는 5개의 그룹으로 나누는 것이 일반적이다. 이렇게 하면 125개의 그룹으로 사용자를 나누어 파악할 수 있다.
• 다음과 같은 예에 기반하여 RFM 단계를 정의해보자.
• 각 지표마다 CASE WHEN을 사용하여 구분해준다.

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 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
;

결과 예(데이터 일부)

---

3. RFM 통합 랭크 계산

• 3개의 지표를 모두 더해서 total_rank를 만들어준다.
• 각 RFM에 속한 사용자 수를 group by count()로 계산한다. 👉 통합 랭크 기준이 아님! 

-- user_rfm_rank 테이블까지 동일 
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 ;

 

• 통합 랭크별로 사용자수를 집계하려면 다음과 같다.

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

---

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

• RFM 지표 2개를 사용해서 사용자 층을 정의하는 방법도 있다.
• 각 사용자 층에 대해 어떤 마케팅 대책을 실시할지, 각 사용자 층을 보다 상위 사용자 층으로 어떻게 옮길 수 있을지 등을 생각할 수 있다. 
• 다음 예는 R, F 만 사용하여 사용자를 2차원으로 인식한 예이다. 이 그림에서 각 셀에 사용자가 얼마나 있는지 산출하는 쿼리를 작성해본다.

출처 : 데이터 분석을 위한 SQL 레시피R

 

• r값을 기준으로 f 값에 따라 count()를 해준다.
• 이때 f가 1부터 5까지의 값이 있으므로 각각의 경우를 살피기 위해 count(CASE WHEN..)을 사용한다.

-- user_rfm_rank 까지 동일 --
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
;

결과 예시

 

• 해당 결과에 따라 고객군을 살피고 적절한 조치를 취할 수 있다. 
• 교재에서 소개하는 조치 예시는 다음과 같다.