SQL 실무 : RFM 분석

 

RFM 분석은 Recency, Frequency, Monetary라는 3개의 앞 글자를 따와서 RFM 분석이라고 부르며, CRM 업무 시 고객을 분류하기 위해 사용하는 방법론입니다. RFM 분석을 통해 고객을 분류하게 되면 얼마나 우리 상품을 자주, 많이, 최근에 구매했는지 빠르게 파악할 수 있게 됩니다. 

- Recency : 최근 구매일자 (고객이 얼마나 최근에 우리 상품을 구매 했는지)
- Frequency : 구매횟수 (고객이 특정 기간 동안 얼마나 자주 우리 상품을 구매 했는지)
- Monetary : 구매금액 합계 (고객이 특정 기간 동안 우리 상품을 구매한 총액은 얼마인지)

 

RFM Analysis: Customer Segmentation for Target Marketing Strategies

 

 

RFM 분석을 위해 사용할 데이터셋은 UCI 대학교의 Online Retail Data Set을 사용할 것입니다. 아래 링크를 통해 동일한 데이터를 내려 받을 수 있으니 참고 바랍니다.

데이터셋 : [UCI] Online Retail Data Set

 

1. RFM 지표 계산 

 

데이터를 내려 받아 분석을 시작할 준비가 되었습니다. 먼저 세 가지 기준(Recency, Frequency, Monetary)에 대해 계산을 해보겠습니다. MAX 함수를 이용해 최근 구매일자를 계산해주고, 각각 COUNT, SUM을 이용해 구매횟수와 구매금액 합계를 계산해줄 수 있습니다.

 

-- RFM 계산
SELECT CustomerID
     , MAX(SUBSTR(InvoiceDate,1,10)) recent_date
     , DATEDIFF('2010-12-12', MAX(SUBSTR(InvoiceDate,1,10))) recency
     , COUNT(CustomerID) frequency
     , SUM(amount) monetary
FROM (SELECT *, quantity * unitprice amount
      FROM sales
      ) sales
GROUP BY CustomerID
ORDER BY CustomerID;

 

✏️ 실행결과

 

CustomerID	recent_date	recency	frequency	monetary
12347	2010-12-07	5	31	711.79
12386	2010-12-08	4	8	258.9
12395	2010-12-03	9	12	346.1
12427	2010-12-03	9	10	303.5
12429	2010-12-09	3	20	1281.5

 

 

2. RFM 점수 계산

 

앞에서 계산했던 세 가지 기준을 가지고 일정 기준에 의해 등급(점수)을 나누고 그 등급에 맞는 점수를 고객에게 부여하도록 하겠습니다. RFM에서 등급을 나누는 기준에 대해서는 다양한 연구 결과가 있는데요. 여기에서는 방법론적인 부분 보다는 실무에서 사용할 수 있는 쿼리를 짜는 것이 우선이기 때문에 임의의 기준으로 4단계로 구분해보도록 하겠습니다.

 

-- RFM 점수 계산
WITH rfm AS (
SELECT CustomerID
     , MAX(SUBSTR(InvoiceDate,1,10)) recent_date
     , DATEDIFF('2010-12-12', MAX(SUBSTR(InvoiceDate,1,10))) recency
     , COUNT(CustomerID) frequency
     , SUM(amount) monetary
FROM (SELECT *, quantity * unitprice amount
      FROM sales
      ) sales
GROUP BY CustomerID
ORDER BY CustomerID
)
, rfm_score AS (
SELECT CustomerID
     , recent_date
     , recency
     , frequency
     , monetary
     , CASE WHEN recency <= 3 THEN 4
            WHEN recency <= 6 THEN 3
            WHEN recency <= 9 THEN 2
            ELSE 1 END AS R
	 , CASE WHEN frequency >= 50 THEN 4
            WHEN frequency >= 10 THEN 3
            WHEN frequency >= 2 THEN 2
            ELSE 1 END AS F
	 , CASE WHEN monetary >= 1000 THEN 4
            WHEN monetary >= 500 THEN 3
            WHEN monetary >= 100 THEN 2
            ELSE 1 END AS M
FROM rfm
)
SELECT *
     , R+F+M RFM
FROM rfm_score;

 

이렇게 하면 고객ID별로 RFM 점수가 합산되어 어떤 고객이 RFM 점수가 높고, 낮은지 파악할 수 있게 됩니다.

 

✏️ 실행결과

 

CustomerID	recent_date	recency	frequency	monetary	R	F	M	RFM
12347	2010-12-07	5	31	711.79	3	3	3	9
12386	2010-12-08	4	8	258.9	3	2	2	7
12395	2010-12-03	9	12	346.1	2	3	2	7
12427	2010-12-03	9	10	303.5	2	3	2	7
12429	2010-12-09	3	20	1281.5	4	3	4	11

 

 

3. RFM 점수에 따른 회원수 계산

 

마지막으로 RFM 점수별로 몇 명의 회원이 있는지 볼 수 있는 쿼리를 소개하겠습니다. 지금까지 잘 따라오셨다면 COUNT 함수로 인원만 세어주면 되기 때문에 방법은 굉장히 간단합니다. 

 

-- RFM 점수에 따른 회원수
WITH rfm AS (
SELECT CustomerID
     , MAX(SUBSTR(InvoiceDate,1,10)) recent_date
     , DATEDIFF('2010-12-12', MAX(SUBSTR(InvoiceDate,1,10))) recency
     , COUNT(CustomerID) frequency
     , SUM(amount) monetary
FROM (SELECT *, quantity * unitprice amount
	  FROM sales
      ) sales
GROUP BY CustomerID
ORDER BY CustomerID
)
, rfm_score AS (
SELECT CustomerID
	 , recent_date
     , recency
     , frequency
     , monetary
     , CASE WHEN recency <= 3 THEN 4
            WHEN recency <= 6 THEN 3
            WHEN recency <= 9 THEN 2
            ELSE 1 END AS R
	 , CASE WHEN frequency >= 50 THEN 4
            WHEN frequency >= 10 THEN 3
            WHEN frequency >= 2 THEN 2
            ELSE 1 END AS F
	 , CASE WHEN monetary >= 1000 THEN 4
            WHEN monetary >= 500 THEN 3
            WHEN monetary >= 100 THEN 2
            ELSE 1 END AS M
FROM rfm
)
SELECT R + F + M RFM
     , COUNT(CustomerID) count
FROM rfm_score
GROUP BY RFM
ORDER BY RFM DESC;

 

✏️ 실행결과

 

RFM	count
12	3
11	16
10	35
9	78
8	133
7	131
6	95
5	39
4	15
3	3

 

 

작성된 내용 중 잘못된 부분이나 궁금한 사항이 있다면 언제든지 피드백 부탁 드립니다.

 

감사합니다.