[MYSQL] 예약 테이블을 통해 RFM 테이블 만들기 방법 및 쿼리

 

어떤 고객에게 마케팅을 할 것인지 선정하기 위해서, 고객이 최근 우리 서비스에서 언제 결제했고, 최근 결제 했다면 오늘 부터 얼마나 지났으며, 금액은 얼마고, 몇 번을 결제했는지 알고 싶다고 한다.

 

여기서 RFM은 다음과 같이 정의한다.

• Recency: 고객이 얼마나 최근에 구매를 했는가?
• Frequency: 고객이 (주어진 기간 동안) 얼마나 자주 구매를 했는가?
• Monetary: 고객이 (주어진 기간 동안) 구매에 얼마를 지출하였는가?

SELECT SUB.user_id, -- 결제한 고객 ID
MAX(SUB.date) AS recent_date, -- 고객별 최근 구매일
DATEDIFF(CURRENT_DATE(), (MAX(SUB.date))) AS recency, -- 고객별 구매 최근성
ROUND(SUM(SUB.prcie)) AS monetary -- 고객별 금액 합산
, COUNT(SUB.date) AS frequency -- 고객별 구매 빈도수
FROM (SELECT A.user_id, 
          A.price,
          A.date -- 결제 일자 
   	  FROM 결제 테이블 A) SUB
WHERE 1=1
	AND SUB.date >'2021-01-01' -- 2021.01.01보다 기준으로 RFM을 구하겠다.
GROUP BY SUB.user_id

 

위 간단한 Query를 통해서 아래와 같은 테이블을 얻을 수 있다.

 

user_id	recent_date	recency	monetary	frequency
1	26/03/21 18:42	82	476,190.00	1
2	13/05/21 22:24	34	1,485,000.00	1
3	10/03/21 13:31	98	605,000.00	2
4	20/05/21 13:46	27	203,500.00	2
5	13/04/21 18:31	64	808,500.00	1
6	01/04/21 11:44	76	1,977,800.00	5