RFM

RFM이란?

CRM(Customer Relationship Management)의 기법 중 하나인 RFM에 대해 소개해 보겠습니다.

개념은 간단합니다. 고객을 3가지 분류를 기준으로 나누는데요

• Recency : 얼마나 최근에 구매했는가?
• Frequency : 얼마나 자주 구매했는가?
• Monetary : 얼마나 많이 구매했는가?

위의  3가지를 기준으로 고객을 나누에 고객별로 다르게 접근 하는 방식입니다. 

자세한 설명은 아래 링크에 있으니 궁금하신 분은 참고하시길 바랍니다.

 

https://www.datarian.io/blog/what-is-rfm

RFM 분석이란 무엇일까요

 

 

CASE와 IF을 활용한 RFM분석

Recency 컬럼 생성후 last_order_date를 기준으로 구매일이 2020-11-01 안에 있으면 고객에게 Recency 점수를 1점을 준다.

SELECT customer_id, 
CASE WHEN last_order_date > "2020-11-01" THEN 1 ELSE 0 END AS Recency,
last_order_date
FROM customer_stats

 

Frequency 컬럼 생성 후 cnt_orders를 기준으로 구매횟수가 5회 이상인 고객에게 Frequency 점수를 1점을 준다.

IF문 사용법 : IF(조건, 참일 때 값, 거짓일 때 값)

SELECT customer_id,
IF(cnt_orders >= 5, 1, 0) AS Frequency,
cnt_orders
FROM customer_stats

 

구매일이 2020-11-01 이후인 고객과 아닌 고객의 수를 구합니다.

SELECT CASE WHEN last_order_date >= '2020-11-01' THEN 1 ELSE 0 END AS recency,
COUNT(customer_id)
FROM customer_stats
GROUP BY recency

 

recency와 frequency를 기준으고 고객을 나누어 해당 그룹에 속하는 고객의 수를 구합니다.

SELECT IF(last_order_date >= '2020-11-01', 1, 0) AS recency, 
       IF(cnt_orders >= 5, 1, 0) AS frequency,
       COUNT(*) AS customers
FROM customer_stats
GROUP BY frequency, recency
ORDER BY recency DESC, frequency DESC

 

 

테이블 피봇

category, region별 주문량을 구했다.

COUNT(DISTINCT ~)로 중복되는 주문은 제외했다.

SELECT category, region, COUNT(DISTINCT order_id)
FROM records
GROUP BY category, region

 

order_id 컬럼과 카테고리 값이 furniture인 경우의 order_id 컬럼을 뽑는 쿼리를 작성했다.

SELECT order_id,
IF(category = 'Furniture', order_id, null) AS furniture_order_id
FROM records

 

전체 주문수와 카테고리값이 furniture인 경우의 주문수를 세는 쿼리를 작성

COUNT(DISTINCT ~)로 CASE문을 감싸줘야 된다.

SELECT COUNT(DISTINCT order_id) AS cnt_order,
       COUNT(DISTINCT CASE WHEN category = 'Furniture' THEN order_id END) AS furniture_cnt_orders 
FROM records

 

region별 전체 주문수와, 카테고리 값이 ‘Furniture’인 주문수를 계산했다.

SELECT region,
       COUNT(DISTINCT order_id) AS cnt_order, 
       COUNT(DISTINCT CASE WHEN category = 'Furniture' THEN order_id END) AS furniture_cnt_order
FROM records
GROUP BY region