๐ฏ ๋ค์๊ณผ ๊ฐ์ด ์ฐ๋๋ณ RFM Segment ์ ๋ฐ๋ฅธ ์ฃผ๋ฌธ ๊ฑด ์ ๋น์จ ์ฐจ์ด๋ฅผ ๋ณผ ์ ์๋ ํธ๋ฆฌ๋งต์ ๋ง๋ ๋ค.
์ฐ๋ ํํฐ ๋ง๋ค๊ธฐ
- ์ฐ๋๋ฅผ ์ ํํ๋ฉด ์ฐ๋์ ๋ง๊ฒ RFM ์งํ๊ฐ ๊ณ์ฐ๋ ์ ์๋๋ก ํ๊ธฐ ์ํด ๋จผ์ ์์ฑ
- Max Order Date of the Year ํ๋ ์์ฑ : ํน์ ์ฐ๋ ๋ณ๋ก order date์ ์ต๋๊ฐ ๊ณ์ฐ
Recency ํ๋ ๋ง๋ค๊ธฐ
- ์ผ ์ ์ฐจ์ด๋ฅผ ๊ณ์ฐํ๋, ๊ณ ๊ฐ/์ฐ๋๋ณ๋ก ๊ฐ์ฅ ์ต๊ทผ(max)์ ๊ตฌ๋งค์ผ์์, ์์์ ๋ง๋ ์ ์ฒด ํ๋์์ ๋์ผ ์ฐ๋ ๋ด ๊ฐ์ฅ ์ต๊ทผ ๊ตฌ๋งค์ผ์ ์ฐจ์ด๋ฅผ ๊ณ์ฐ
DATEDIFF('day', {FIXED [Customer Name], YEAR([Order Date]): MAX([Order Date])},
[Max Order Date of the Year])
Frequency ํ๋ ๋ง๋ค๊ธฐ
- Number of Orders ํ๋ ์์ฑ : COUNTD([Order ID])
- Frequency ํ๋ ์์ฑ
{ FIXED [Customer Name], YEAR([Order Date]):[Number of Orders]}
Monetary ํ๋ ๋ง๋ค๊ธฐ
{ FIXED [Customer Name], YEAR([Order Date]) : SUM([Sales])}
RFM Scoring
1. Recency Score
IF [Recency] >= {FIXED YEAR([Order Date]) : PERCENTILE([Recency], 0.75)} then 1
ELSEIF [Recency] >= {FIXED YEAR([Order Date]) : PERCENTILE([Recency], 0.5)} then 2
ELSEIF [Recency] >= {FIXED YEAR([Order Date]) : PERCENTILE([Recency], 0.25)} then 3
ELSE 4
END
2. Frequency Score
IF [Frequency] >= {FIXED YEAR([Order Date]) : PERCENTILE([Frequency], 0.75)} then 4
ELSEIF [Frequency] >= {FIXED YEAR([Order Date]) : PERCENTILE([Frequency], 0.5)} then 3
ELSEIF [Frequency] >= {FIXED YEAR([Order Date]) : PERCENTILE([Frequency], 0.25)} then 2
ELSE 1
END
3. Monetary Score
IF [Monetary] >= {FIXED YEAR([Order Date]) : PERCENTILE([Monetary], 0.75)} then 4
ELSEIF [Monetary] >= {FIXED YEAR([Order Date]) : PERCENTILE([Monetary], 0.5)} then 3
ELSEIF [Monetary] >= {FIXED YEAR([Order Date]) : PERCENTILE([Monetary], 0.25)} then 2
ELSE 1
END
RFM Segmentation
โถ๏ธ Customer Segment ํ๋
if [Frequency Score]>= 4 and [Monetary Score]>= 4 and [Recency Score]>= 4 then 'Champions'
ELSEIF [Recency Score]>=2 and [Recency Score]<=4 and [Frequency Score]>=3 and [Frequency Score]<=4 and [Monetary Score]>=5 then 'Loyal Customers'
ELSEIF [Recency Score]>=3 and [Frequency Score]>=1 and [Frequency Score]<=3 and [Monetary Score]>=1 and [Monetary Score]<=3 then 'Potential Loyalist'
ELSEIF [Recency Score]>=4 and [Frequency Score]<2 and [Monetary Score]<2 then 'New Customers'
ELSEIF [Recency Score]>=3 and [Recency Score]<=4 and [Frequency Score]<2 and [Monetary Score]<2 then 'Promising'
ELSEIF [Recency Score]>=3 and [Recency Score]<=4 and [Frequency Score]>=3 and [Frequency Score]<=4 and [Monetary Score]>=3 and [Monetary Score]<=4 then 'Need Attention'
ELSEIF [Recency Score]>=2 and [Recency Score]<=3 and [Frequency Score]<3 and [Monetary Score]<3 then 'About to Sleep'
ELSEIF [Recency Score]<3 and [Frequency Score]>=2 and [Frequency Score]<=5 and [Monetary Score]>=2 and [Monetary Score]<=5 then 'At Risk'
ELSEIF [Recency Score]<2 and [Frequency Score]>=4 and [Monetary Score]>=4 then 'Cant Lose Them'
ELSEIF [Recency Score]>=2 and [Recency Score]<=3 and [Frequency Score]>=2 and [Frequency Score]<=3 and [Monetary Score]>=2 and [Monetary Score]<=3 then 'Hibernating'
ELSEIF [Recency Score]<2 and [Frequency Score]<2 and [Monetary Score]<2 then 'Lost'
end
RFM ๊ทธ๋ํ ๋ง๋ค๊ธฐ
segment ๋ณ๋ก ์ฃผ๋ฌธ ๊ฑด์ ๋น์ค
- Customer Segment : ์์์
- Number of Orders : ํฌ๊ธฐ์
- segment ๋ณ๋ก ์์ ๋ณ๊ฒฝ
- Customer Segment ๋ ์ด๋ธ์ ๋ณต์ฌ
- Number of Orders๋ ๋ ์ด๋ธ์ ๋ณต์ฌํ์ฌ ํ์ >> ํตํ
์ด๋ธ ๊ณ์ฐ(๊ตฌ์ฑ๋น์จ)
๐ ์ฃผ๋ฌธ ๊ฑด ์์ ๊ตฌ์ฑ๋น์จ์ ๋ณผ ์ ์์ - ๋ ์ด๋ธ ์์ ์์ , ๋งํฌ ์์ ์ผ์น, ๊ฐ์ด๋ฐ ์ ๋ ฌ
728x90