SQL을 활용하여 RFM 분석하기_MySQL

안녕하세요!

 

SQL을 활용한 RFM 분석하기입니다.

RFM 분석은 Recency (최근성) / Frequency (빈도) / Monetary (금액) 의 지표로

고객을 평가, 분류하는 방법입니다.

 

사용한 데이터셋은 Kaggle에 있는 US E-commerce 2020 입니다.

---

- Recency 지표 구하기

고객의 서비스 사용일이 최근에 가까울 수록 높은 점수를 부여하는 것입니다.

 

2020-12-31 기준으로 3개월(90일) 이내 주문한 고객을 대상으로 한다면

기간 내의 고객은 점수 1을 부여하고 그렇지 않다면 점수를 부여하지 않겠습니다.

SELECT customer_id
     , CASE WHEN DATEDIFF(20201231, max_order_dt) <= 90 THEN 1 ELSE 0 END r_index
FROM   
  (SELECT customer_id
        , MAX(order_date) max_order_dt
        , COUNT(DISTINCT order_id) order_cnt
        , SUM(sales) sum_sales
   FROM records
   GROUP BY 1) rfm

---

- Frequency 지표 구하기

고객의 서비스 사용빈도가 높을 수록 높은 점수를 부여하는 것입니다.

주문이 4회 이상인 고객들에게 점수 1을 부여했습니다.

SELECT customer_id
     , CASE WHEN order_cnt >= 4 THEN 1 ELSE 0 END f_index
FROM   
  (SELECT customer_id
        , MAX(order_date) max_order_dt
        , COUNT(DISTINCT order_id) order_cnt
        , SUM(sales) sum_sales
   FROM records
   GROUP BY 1) rfm

---

- Monetary 지표 구하기

고객의 서비스 사용금액이 많을 수록 높은 점수를 부여하는 것입니다.

총 주문 금액이 2000 이상인 고객에게 점수 1을 부여했습니다.

SELECT customer_id
     , CASE WHEN sum_sales >= 2000 THEN 1 ELSE 0 END m_index
FROM   
  (SELECT customer_id
        , MAX(order_date) max_order_dt
        , COUNT(DISTINCT order_id) order_cnt
        , SUM(sales) sum_sales
   FROM records
   GROUP BY 1) rfm

---

- 종합 쿼리

SELECT customer_id
     , r_index
     , f_index
     , m_index
     , r_index * 100 + f_index * 10 + m_index rfm_index
FROM
  (SELECT DISTINCT customer_id
        , CASE WHEN DATEDIFF(20201231, max_order_dt) <= 90 THEN 1 ELSE 0 END r_index
        , CASE WHEN order_cnt >= 4 THEN 1 ELSE 0 END f_index
        , CASE WHEN sum_sales >= 2000 THEN 1 ELSE 0 END m_index
  FROM
    (SELECT customer_id
          , MAX(order_date) max_order_dt
          , COUNT(DISTINCT order_id) order_cnt
          , SUM(sales) sum_sales
    FROM records
    GROUP BY 1) rfm
  ORDER BY 1) final

- 결과물

이런 형태의 결과물이 출력됩니다.

---

예시로 작성하기 위해 임의의 기준을 정했습니다.

RFM 지표는 목적에 따라 기준을 정하면 되겠습니다.

성격과 상황에 따라 분류한 고객을 어떻게 관리할 것인지 정해야겠습니다.