[SQL] 고객 가치 평가모형 분석하기 (RFM 분석)

* RFM이란?
- 고객의 가치를 분석할 때 사용되는 고객가치 평가모형
- Recency: 최근성
- Frequence: 구매 빈도
- Monetary: 구매 금액

1. RFM 분석용 데이터 테이블 생성

CREATE TABLE RFM AS
SELECT A.*, B.구매금액, B.구매횟수
FROM CUSTOMER AS A
LEFT JOIN
(SELECT A.MEM_NO,
SUM(A.SALES_QTY * B.PRICE) AS 구매금액, # Monetary: 구매금액
COUNT(A.ORDER_NO) AS 구매횟수 #Frequency: 구매 빈도
FROM SALES AS A 
LEFT JOIN PRODUCT AS B
ON A.PRODUCT_CODE = B.PRODUCT_CODE
WHERE YEAR(A.ORDER_DATE) = '2020' #Recency: 최근성
GROUP BY A.MEM_NO) AS B
ON A.MEM_NO = B.MEM_NO;

생성된 테이블 예시

2. RFM 세분화별 회원수

SELECT 회원세분화, COUNT(MEM_NO) AS 회원수
FROM
(SELECT *,
CASE WHEN 구매금액 > 5000000 THEN 'VIP'
WHEN 구매금액 > 1000000 OR 구매횟수 > 3 THEN '우수'
WHEN 구매금액 > 0 THEN '일반'
ELSE '잠재회원' END AS 회원세분화
FROM RFM) A
GROUP BY 회원세분화
ORDER BY 회원수;

쿼리 결과

3. RFM 세분화별 매출액

SELECT 회원세분화, SUM(구매금액) 구매금액합
FROM
(SELECT *,
CASE WHEN 구매금액 > 5000000 THEN 'VIP'
WHEN 구매금액 > 1000000 OR 구매횟수 > 3 THEN '우수' 
WHEN 구매금액 > 0 THEN '일반'
ELSE '잠재회원' END AS 회원세분화
FROM RFM) A 
GROUP BY 회원세분화
ORDER BY 구매금액합 DESC;

쿼리 결과

4. RFM 세분화별 인당 구매금액

SELECT 회원세분화, SUM(구매금액)/COUNT(MEM_NO) 인당_구매금액
FROM
(SELECT *,
CASE WHEN 구매금액 > 5000000 THEN 'VIP'
WHEN 구매금액 > 1000000 OR 구매횟수 > 3 THEN '우수' 
WHEN 구매금액 > 0 THEN '일반'
ELSE '잠재회원' END AS 회원세분화
FROM RFM) A 
GROUP BY 회원세분화
ORDER BY 인당_구매금액 DESC;

쿼리 결과

 

출처: '기초 데이터 분석을 위한 핵심 SQL'