RFM이란?

RFM? 고객의 등급을 매김.

등급 매기는 기준? 언제샀나(R), 자주 샀나(F), 얼마 썼나(M).

매기면 알 수 있는것. 고객의 충성도, 마케팅 인사이드.

매겨서 뭐함? 등급별로 전략 생각하기. 분류된 고객군의 특성에 맞게 CRM 전략

---

활용 데이터

https://solvesql.com/playground/

*solvesql의 US E-Commerce Records 2020 데이터 참고.

US E-Commerce Records 2020 데이터의 원본은 여기 이며, CC0 Public 1.0 의 적용을 받습니다.

---

RFM 기준

• Recency: 한 달 이내에 구매 기록이 있으면 1점 이외는 0점 (현재는 2021년 1월 1일로 가정)
• Frequency: 3회 이상 구매시 1점, 3회 미만 구매시 0점
• Monetary: 500달러 이상 구매시 1점, 500달러 미만 구매시 0점

---

R부터 구해보자.

Q1. 고객님이 최근 한 달 이내에 결제 했으면 1점, 안했으면 0점. (2021년 1월 1일 기준)

SELECT customer_id,
    CASE WHEN last_order_date BETWEEN '2020-12-01' AND '2020-12-31' THEN 1 ELSE 0 END AS r
FROM customer_stats

Q2. 1점과 0점은 각각 몇명?

SELECT 
    CASE WHEN last_order_date BETWEEN '2020-12-01' AND '2020-12-31' THEN 1 ELSE 0 END AS r
  ,COUNT(customer_id)
FROM customer_stats
GROUP BY r
ORDER BY r DESC

---

F를 구해보자.

Q1. 고객님이 올해 3번 이상 결제 했으면 1점, 안했으면 0점. (2020년 기준)

SELECT 
    customer_id
   ,CASE WHEN cnt_orders >= 3 THEN 1 ELSE 0 END AS f
FROM customer_stats

Q2. 1점과 0점은 각각 몇명?

SELECT 
    CASE WHEN cnt_orders >= 3 THEN 1 ELSE 0 END AS f
  ,COUNT(customer_id)
FROM customer_stats
GROUP BY f
ORDER BY f DESC

---

M을 구해보자.

Q1. 고객님이 500달러 이상 결제 했으면 1점, 안했으면 0점. (2020년 기준)

SELECT customer_id,
    CASE WHEN sum_sales >= 500 THEN 1 ELSE 0 END AS m
FROM customer_stats

Q2. 1점과 0점은 각각 몇명?

SELECT 
     CASE WHEN sum_sales >= 500 THEN 1 ELSE 0 END AS m
    ,COUNT(customer_id)
FROM customer_stats
GROUP BY m
ORDER BY m desc

---

RFM 합쳐보자.

Q1. r,f,m 컬럼을 한표에 모아보면?

SELECT customer_id
    ,CASE WHEN last_order_date BETWEEN '2020-12-01' AND '2020-12-31' THEN 1 END AS r
    ,CASE WHEN cnt_orders >= 3 THEN 1 END AS f
    ,CASE WHEN sum_sales >= 500 THEN 1 END AS m
FROM customer_stats

Q2. 고객별 RFM 점수

SELECT customer_id
    ,CASE WHEN last_order_date BETWEEN '2020-12-01' AND '2020-12-31' THEN 1 ELSE 0 END AS r
    ,CASE WHEN cnt_orders >= 3 THEN 1 ELSE 0 END AS f
    ,CASE WHEN sum_sales >= 500 THEN 1 ELSE 0 END AS m
    ,(CASE WHEN last_order_date BETWEEN '2020-12-01' AND '2020-12-31' THEN 1 ELSE 0 END) + (CASE WHEN cnt_orders >= 3 THEN 1 ELSE 0 END) + (CASE WHEN sum_sales >= 500 THEN 1 ELSE 0 END) as rfm
FROM customer_stats
ORDER BY rfm DESC

Q3. 각 점수별 고객의 수는 몇명?
IF문으로 다시 작성해보았음.

SELECT 
      IF(last_order_date BETWEEN '2020-12-01' AND '2020-12-31', 1, 0) as R
     ,IF(cnt_orders >= 3, 1, 0) as F
     ,IF(sum_sales >= 500, 1, 0)as M
     ,count(customer_id) AS c_customer
FROM customer_stats
GROUP BY r, f, m
ORDER BY r DESC, f DESC, m DESC

---

결과

R	F	M	c_count	설명
1	1	1	92	로열티가 높은 고객, VIP고객
1	1	0	21	로열티 높지만 결제액이 많지 않음
1	0	1	41	최근 결제액 높음. 재구매를 유도하면 VIP가 될지도.
1	0	0	41	최근에 접속했지만 구매는 별로 안하는 고객
0	1	1	127	떠나간 VIP
0	1	0	53	자주 구매했지만 최근에 구매하진 않은 고객
0	0	1	128	가끔 와서 큰 금액 결제하고 간 고객
0	0	0	190	떠났지만 뼈아프진 않은 고객

---

심화_VIP 고객 분석

VIP 고객에 대해 더 알고 싶어졌다.

Q1. VIP들이 주로 주문하는 제품의 카테고리는 뭘까?

1위. 사무용품

2위. 가구

3위. IT기기

SELECT
     r.category
    ,COUNT(r.category) category_count
FROM customer_stats cs
  INNER JOIN records r ON cs.customer_id = r.customer_id
WHERE last_order_date BETWEEN '2020-12-01' AND '2020-12-31'
    AND cnt_orders >= 3
    AND sum_sales >= 500  
GROUP BY r.category
ORDER BY category_count DESC

Q2. VIP들은 어디 주에 살까?

1위. 캘리포니아

2위. 뉴욕

3위. 텍사스

SELECT
     r.state
    ,COUNT(r.state) state_count
FROM customer_stats cs
  INNER JOIN records r ON cs.customer_id = r.customer_id
WHERE last_order_date BETWEEN '2020-12-01' AND '2020-12-31'
    AND cnt_orders >= 3
    AND sum_sales >= 500  
GROUP BY r.state
ORDER BY state_count DESC

---

연습 후 얻은 것

• 가장 기초적인 RFM 분석을 처음 해봤다.
• 배운 쿼리만으로 분석을 할 수 있다니 감격!

해야할 것

• 가설, 검증 프로세스를 설정한 후 RFM 분석할 것.
• 데이터 시각화 필요.