들어가기전..
어떤 음식점있다고 했을 때, 이 음식점에는 단골 손님이 있을 수 있고, 처음 방문한 손님도 있을 수 있습니다😃. 아니면 자주 방문하지는 않지만 한번 방문할 때 큰 돈을 쓰는 사람도 있을 수 있구요.
가끔 그 음식점에 단골손님이 되어 자주 오고 많은 소비를 하다보면 사장님이 알아 볼때가 있을텐데요! 그럴때 작은 음료, 음식, 할인쿠폰 같은 서비스를 받았던 경험이 있지 않나요?
당근 사장님 입장에서도 자주 와주고 가계의 매출을 올려주니 고마우셨을겁니다.
사장님의 입장에서 위의 단골손님은 당연히 놓쳐서는 안되는 손님일거에요. 그래서 더 자주 올 수 있도록 음료,음식,할인쿠폰 같은 혜택들을 주신거구요. 음식점 사장님의 기억에 손님들의 데이터가 남고 나름대로 손님들을 세분화해서 전략을 세운거라고 생각 할 수 있죠.
(조금 끼워 맞쳐보았지만😅) 이렇듯 RFM분석을 통한 사례는 주변에서도 쉽게 경험할 수 있습니다!
(이미지출처 : What is Recency, Frequency and Monetary Value (RFM) Analysis?)
RFM?
Recency (최근성) 얼마나 최근에 구매했나?
Frequency (빈도성) 얼마나 자주 구매했나?
Monetary (규모성) 얼마나 많이 구매했나?
RFM분석은 얼마나 "Recency (최근에), Frequency (자주), Monetary (많이) " 구매(행동)를 했는지 세가지 기준을 통해 고객들을 세분화하여 마케팅에 활용되는 분석모형중 하나입니다.
기준에 따라 고객들에게 RFM 점수를 부여해서 세분화된 고객들이 어떤 특징을 갖고 있는지 분석하고, 그룹에 맞게 마케팅 전략을 세울 수 있는거죠. 하지만 점수를 매기는 기준은 회사가 정의한 비즈니스 의사결정에 따라 다르기 때문에 "RFM 세그먼트를 나누는 기준" 을 정하는 게 RFM 분석의 핵심이라고 합니다.
RFM 답변이 가능한 질문들
- 최고의 고객은 누구인지
- 이미 이탈했거나 곧 이탈할것 같은 고객이 누구인지
- 가치 있는 고객이 될 수 있는 (잠재)고객이 누구인지
- 주로 어떤 고객을 타겟할 수 있는지
- 어떤 이벤트를 열었을 때 가장 반응할 가능성이 높은 고객이 누구인지 등..
(참고 : RFM analysis for Customer Segmentation)
그래서 오늘은 kaggle에서 제공하는 US E-Commerce 2020 data를 이용하여 간단한 RFM 분석을 해보려고 합니다. 본 프로젝트의 핵심 목표는 다음과 같습니다.
프로젝트의 목적
" US E-Commerce 회사는 2020년의 사용자 데이터를 갖고 기존고객의 유지와 회사의 매출의 극대화를 위한 전략을 세우려고 합니다."
- SQL을 이용하여 RFM 분석의 전반적인 이해
- RFM 세그먼트를 나누는 기준은 분석 목적, 비즈니스 의사결정이 무엇인지 따라 다르다. 비즈니스 목표를 정의해보고 어떻게 세그먼트를 나누는 것이 좋을지 고민해보자.
- RFM 세그멘테이션을 통해서 각 고객군에 대한 마케팅 전략을 수립
💻DATA
US E-Commerce 2020 데이터 구조
1. 데이터구축
분석에 앞서 RFM 3가지 기준이 들어간 TABLE이 필요합니다. 고객(Customer_ID)별로 First_Order_Date(첫주문일) , Last_Order_Date(마지막주문일), cnt_order(주문횟수), sum_sales(매출합계)가 들어가 있습니다. 이 테이블을 customer_states 라고 지정하겠습니다. ( 주문일은 R , 주문횟수는 F, 매출합계는 M의 점수를 매기기위한 컬럼입니다. )
WITH customer_stats AS ( SELECT Customer_ID
, DATE(MIN(Order_Date)) AS First_Order_Date #첫주문일
, DATE(MAX(Order_Date)) AS Last_Order_Date #마지막주문일
, COUNT(DISTINCT order_id) AS cnt_order # 주문횟수
, ROUND(SUM(Sales),2) AS sum_sales # 매출합계
FROM us
GROUP BY Customer_ID
ORDER BY Customer_ID
)
table. Customer_states
2. 데이터 탐색
RFM 분석을 하기전 데이터에 대한 탐색/이해가 필요했습니다 . RFM 점수를 계산하는 기준이 무엇인지에 알기 위해서는 서비스 특성을 고려해야 하기 때문에 회사가 주로 어떤 품목을 판매하는지, 주 고객층이 누구인지, 고객들은 어떤 특징을 갖고있는지 등 간단한 EDA를 통해 알아보려고 합니다!
################################## 카테고리별 판매 비중 ##################################
SELECT *
, SUM(sub_cnt_sales) OVER (PARTITION BY category) AS category_cnt_sales
, ROUND(SUM(sub_category_sales) OVER (PARTITION BY category),2) AS category_sales
, SUM(sub_category_sales) OVER () AS total_sales
, ROUND(sub_category_sales/SUM(sub_category_sales) OVER (PARTITION BY category),2) as categoy_pct
, ROUND(sub_category_sales / SUM(sub_category_sales) OVER (),3) as total_pct
FROM (SELECT Category
, Sub_category
, COUNT(*) AS sub_cnt_sales
, ROUND(SUM(Sales),2) AS sub_category_sales
FROM US
GROUP BY Category, Sub_Category) A✔ 카테고리별 판매 금액, 주문 횟수
✔ US사 제품 주문횟수 TOP10
✔ US사의 매출액 TOP10
US사의 경우 Technology, Office Supplies, Furniture 총 3개 카테고리로 나뉘어집니다. 카테고리별 주문횟수는 Office Supplies 제품이 가장 높지만 매출액은 Technology 제품이 가장 많이 차지합니다.
Binders, Paper 같은 비교적 저가의 제품이 가장 많은 주문횟수를 차지하고 Furnishings, Phones 같이 중/고가의 제품 또한 판매되고 있습니다.
중/고가의 제품인 Copiers, Machines의 경우 주문량은 낮지만 전체 매출액의 22%를 차지합니다. 반면에 주문량과 매출액 비중에도 큰 차지를 하는 Phones, Chairs, Binders, Storage, Appliances 의 경우 US사의 주력 제품 이라고 볼 수 있겠습니다.
현재 판매하고 있는 품목을 통해 US사는 "주로 사무실 안에서 사용하는 가구, 전자제품, 사무용품 등" 을 판매하는 이커머스사 라는 것을 알 수 있습니다.
👉 Paper 같은 소비재의 경우 구매주기가 짧지만 매출의 큰 비중이 되지는 않아보인다. 반면에 Chairs, Phones 같이 가구/전자제품의 경우 구매주기가 비교적 긴 편이지만 총매출에 큰 기여를 하고 있다. 인기 제품 중에서 연관 있는 제품들끼리 묶어서 파는 전략을 생각해 볼 수 있을거 같다.
✔ US사를 이용하고 있는 고객
##################### 고객별 매출 ###########################
, seg as (
SELECT segment
, cnt_segment
, sum_sales
, SUM(cnt_segment) OVER() as total_cnt
, SUM(sum_sales) OVER() as total_sales
, ROUND(sum_sales/SUM(sum_sales) OVER(),2) as sales_pct
, ROUND(cnt_segment/SUM(cnt_segment) OVER() ,2)as cnt_pct
FROM (SELECT Segment
,count(*) as cnt_segment
,ROUND(SUM(sales),2) as sum_sales
FROM US
GROUP BY Segment) A
) (1) 고객별 구입수, 구입금액 비중
| segment | cnt_segment | sum_sales | total_cnt | total_sales | sales_pct* | cnt_pct* |
|---|---|---|---|---|---|---|
| Consumer | 1651 | 330834.34 | 3275 | 729034.15 | 0.45 | 0.50 |
| Corporate | 970 | 239508.28 | 3275 | 729034.15 | 0.33 | 0.30 |
| Home Office | 654 | 158691.53 | 3275 | 729034.15 | 0.22 | 0.20 |
US사의 고객층은 크게 Consumer, Corporate, Home Office로 나뉩니다. 그 중에서 Consumer 고객이 구입금액, 주문 횟수 각각 45%, 50% 로 가장 높은 비율을 차지하고 있었습니다. 주고객층이 누군지 알 수 있으면 그에 맞는 전략을 생각해 볼 수 있었지만... 아예 Consumer를 US사의 주고객층으로 보기는 어렵고 Corporate, Home Office 고객층도 합쳐서 50%이상을 차지하기 때문에 일단 모두 중요한 고객층으로 봐야 할 것 같습니다.
3. RFM 점수 기준 세우기
RFM을 분석하기전 기업의 비즈니스 의사결정이 무엇인지 알아야합니다. US사의 정확한 비즈니스 목표가 무엇인지는 모르겠지만 고객유지와 매출의 극대화는 어느 회사든 중요할 것 입니다. 그래서 저는 기업이 판매하는 제품과 고객들의 특성을 고려하여 " 기존고객의 유지와 회사의 매출의 극대화"를 목표로 RFM 점수를 나눠보려고 합니다.
회사 입장에서는 당연하게 주문 횟수가 떨어진다거나 구입 금액이 적은 경우 덜 중요한 고객군이라고 판단할 것입니다. 반면에 전체 매출에 큰 기여를 하고 있는 상위 고객들은 매우 중요할 것이구요. 그래서 저는 (1) 그래프를 통해 그룹이 자연스럽게 나뉘어지는 부분과 (2) "파레토 법칙"을 참고하여 세그먼트를 나눠 보려고 합니다.
파레토 법칙이란, 사회 현상의 80%는 20%로 인해 발생한다는 경험법칙으로서 ‘상위 20%에 속하는 고객이 전체 매출의 80%를 차지한다.’ 는 이론입니다. 파레토 법칙과 그래프들을 참고하여 세운 RFM 점수를 요약하면 다음과 같습니다.
RFM 점수 요약
점수 Recency Frequency Monetary 0 50일 이후 3회 미만 $1395 이상 1 50일 이내 3회 이상 $1395 미만
Recency
- 마지막 구매이후 몇일이 지났는지 확인해봐야 한다. 값이 적을 수록 최근에 방문.
- 최근에 구매한 사람이 월등히 많다. 한쪽에 치우쳐진 분포. 아웃라이어 존재
- 파레토 법칙을 적용하면 24일을 기준으로 세그먼트를 나눠야 하지만 그래프를 보면 기준이 많이 엄격해보인다.
- 그래프를 참고하여 자연스럽게 그룹이 나뉘어지는 42~56일 사이 50일이 적당해 보인다.
SELECT *
, ROUND(percent_rank() OVER (ORDER BY days ),2) AS per
FROM ( SELECT Customer_ID
,DATEDIFF('2020-12-31',last_order_date) AS days
FROM customer ) a
Frequency
- 고객의 대부분이 주문 횟수 1~2회에 몰려있다.
- 상위 20% 고객의 주문 횟수는 3회이며, 기준으로 적당해보인다.
SELECT Customer_id
, cnt_orders
, ROUND(percent_rank() OVER (ORDER BY cnt_orders desc),5) as per
FROM customer
ORDER BY cnt_orders
Monetary
- 1인당 구매 금액의 분포 결과 왼쪽에 치우쳐져 있는 형태. 아웃라이어 존재
- 큰 금액을 소비한 고객은 아주 소수이다.
- 최대 구매 금액 $14500, 상위 20%의 기준은 $1578
- 그래프상 1115~1673 사이 $1395 (파레토 법칙을 좀 여유있게 적용, 상위 23%)
SELECT customer_id
, sum_sales
, ROUND(percent_rank() OVER (ORDER BY sum_sales DESC ),2) as per
FROM customer
4. RFM 분석
################### RFM #####################
,RFM AS (SELECT *
,CASE WHEN days <= 50 THEN 1
ELSE 0 END AS recency
,CASE WHEN cnt_orders >= 3 THEN 1
ELSE 0 END AS frequency
,CASE WHEN sum_sales >= 1395 THEN 1
ELSE 0 END AS monetary
FROM Customer_stats)
####################### 각 고객별 등급 매기기 ########################################
,RFM_SEG AS( SELECT Customer_ID
, SEGMENT
FROM (SELECT *
,CASE WHEN recency = 1 and frequency = 1 and monetary = 1 THEN 'vip'
WHEN recency = 1 and frequency = 1 and monetary = 0 THEN 'poten_vip1'
WHEN recency = 1 and frequency = 0 and monetary = 1 THEN 'poten_vip2'
WHEN recency = 0 and frequency = 1 and monetary = 1 THEN 'left_vip'
WHEN recency = 1 and frequency = 0 and monetary = 0 THEN 'new_user'
WHEN recency = 0 and frequency = 0 and monetary = 0 THEN 'left_user'
ELSE 'else' END AS SEGMENT
FROM RFM) A
)(1) RFM Segment table
(2) 세그먼트별 매출액 비중
5. 마케팅 전략
나뉘어진 모든 세그먼트를 보면 총 7개의 세그먼트로 보이는데요. 회사의 입장에서 지출할 수 있는 최소비용이 있다면 무작정 모든 고객 세그먼트를 챙기기 보다 최적의 방법으로 매출을 유지/증가 할 수있는 전략을 세워야 한다는 생각이 들었습니다. 그래서 세그먼트별 매출액을 참고했는데요.
US사를 자주 이용하고있는 VIP 고객층은 당연히 중요했지만 생각보다 떠나간VIP 고객 비중도 컸습니다. 즉, 세그먼트를 나눴을 때 큰 특징이 없는 고객층을 전략한다기 보다 이미 US사의 경험이 많은 VIP 고객층들을 유지/복귀 하는 전략으로가는 것이 적당해 보였습니다. 그래서 VIP 에 해당하는 고객들을 중점으로 두고
" 기존 VIP 고객을 위주로 그리고 이탈고객을 잡을 거면 구매를 많이 경험해본 고객을 잡자. " 라는 전략을 세워 보았습니다.
(1) VIP고객의 성향을 고려한 크로스셀링, 할인쿠폰
크로스셀링 이란 기존 상품을 구입하였던 고객이다른 연관된 상품의 구매로 이어질 수 있도록 하는 마케팅 방법을 말합니다. 먼저, VIP 고객이 주로 구매하는 제품을 알기 위해 카테고리별로 구매횟수(sub_cnt)가 높거나 총매출액(total_pct)의 비중이 높은 제품들을 뽑아서 엮어 보았습니다.
(참고: 업셀링&크로스셀링)
| Category | Sub_category | sub_cnt | sub_sales | category_pct | total_pct |
|---|---|---|---|---|---|
| Technology | Phones | 120 | 45966.88 | 0.38 | 0.138 |
| Technology | Accessories | 127 | 27045.9 | 0.22 | 0.081 |
| Office Supplies | Binders | 220 | 49716.63 | 0.42 | 0.149 |
| Office Supplies | Storage | 113 | 28082.64 | 0.24 | 0.084 |
| Office Supplies | paper | 193 | 11769.05 | 0.1 | 0.035 |
| Furniture | Chairs | 92 | 44000.05 | 0.46 | 0.132 |
| Furniture | Tables | 41 | 24298.26 | 0.26 | 0.073 |
| Furniture | Furnishings | 147 | 14356.53 | 0.15 | 0.043 |
Technology카테고리 인기상품Phones를 구매할시 다른 연관된 상품의 구매로 이어질 수 있도록 할인쿠폰이나 1+1 이벤트를 제공합니다. 구매횟수도 높고 연관성이 있는Accessories가 적당해 보입니다.Office Supplies카테고리 대표상품Binders를 구매할시,Papers제품을 추가로 제공하는 이벤트나 쿠폰을 제공합니다.Furniture카테고리 대표상품Chairs를 구매할시,Furniture 카테고리 전용 할인쿠폰을 제공합니다. 가구의 경우 다른 카테고리에 비해 제품끼리의 연관성이 높기 때문에 카테고리 전용 쿠폰을 지급하면 좋을것 같습니다.- 이 외에 구매를 자주한다는 VIP고객층 특성을 고려하여
배송비 무료등의 혜택을 주면 좋을것 같습니다.- 사용자가 웹사이트 또는 앱을 처음 방문할 때부터 해당 사용자 맞춤 랜딩 페이지, 그리고 특정 상품 추천 기능을 제공합니다.
