背景

一個會員服務的企業，有近1年約1200個會員客戶的收銀數據。由于公司想針對不同類别不活躍客戶進行激活促銷；同時，為回饋重點客戶，也計劃推出一系列針對重點客戶的優惠活動，希望保留這些客戶，維持其活躍度。因此希望利用該數據進行客戶分類研究。

根據客戶的需求，RFM模型相對簡單并且直接，按照R(Recency-近度）、F(Frequency-頻度）和M（Monetary-額度）三個維度進行細分客戶群體。由于該客戶的數量較少（約1200個），所以，采用3x3x3=27個魔方（1200/27=44左右）較為合适，雖然平均每類客戶數量較少，考慮到集中度分布情況，數量多的分類也能夠有200-300左右，适合針對會員客戶進行短期的電話、短信營銷或者信函營銷的數量。

RFM模型原理：

RFM模型是一個簡單的根據客戶的活躍程度和交易金額貢獻所做的分類。因為操作簡單，所以，較為常用。

近度R：R代表客戶最近的活躍時間距離數據采集點的時間距離，R越大，表示客戶越久未發生交易，R越小，表示客戶越近有交易發生。R越大則客戶越可能會“沉睡”，流失的可能性越大。在這部分客戶中，可能有些優質客戶，值得公司通過一定的營銷手段進行激活。

頻度F：F代表客戶過去某段時間内的活躍頻率。F越大，則表示客戶同本公司的交易越頻繁，不僅僅給公司帶來人氣，也帶來穩定的現金流，是非常忠誠的客戶；F越小，則表示客戶不夠活躍，且可能是競争對手的常客。針對F較小、且消費額較大的客戶，需要推出一定的競争策略，将這批客戶從競争對手中争取過來。

額度M：表示客戶每次消費金額的多少，可以用最近一次消費金額，也可以用過去的平均消費金額，根據分析的目的不同，可以有不同的标識方法。一般來講，單次交易金額較大的客戶，支付能力強，價格敏感度低，是較為優質的客戶，而每次交易金額很小的客戶，可能在支付能力和支付意願上較低。當然，也不是絕對的。

RFM的分析工具有很多，可以使用SPSS或者SAS進行建模分析，然後深度挖掘。IBM SPSS還有個Modeler，有專門的RFM挖掘算法供使用。本文為了普及，介紹使用Excel（2007版）做初步的RFM分析。

操作步驟：

1數據的清洗

原始數據集：數據請參考附件Excel（模拟數據.xlsx）。大家可以下載練習。該數據集共有26600多條數據，包含記錄ID（數據庫的primarykey）、客戶編号、收銀時間、銷售金額、銷售類型共5個字段

通過簡單的篩選，可以看到，在交易金額中有0消費額，有負數消費額，繼續查看交易類型，發現為“贈送”和“退貨”所造成。這些數據在本次分析中用不到，所以在數據處理時需要通過篩選除去。

Excel操作：

Ø 鼠标點擊第一行的行标“1”以選中第一行

Ø 菜單欄點擊“數據”，快捷按鈕欄點選“篩選”

Ø 鼠标點擊“銷售類型”篩選下拉按鈕，可以看到所有數據集中有的銷售類型

Ø 點選“銷售金額”字段的小角标也可以看到有負數出現。

2數據處理

根據分析需要，R用客戶最後成交時間跟數據采集點時間的時間差（天數）作為計量标準；F根據數據集中每個會員客戶的交易次數作為計量标準（1年的交易次數）；M以客戶平均的交易額為計量标準。通過Excel的透視表即可計算以上RFM數據。

Excel操作：

Ø 菜單欄點擊“插入”

Ø 快捷按鈕欄點擊“透視表”

Ø 選擇數據區域，确認所有的數據都被選擇

Ø 選擇在“新工作表”中插入數據，然後點擊“确定”

Ø 将“客戶編号”拖入“行标簽”欄

Ø 将“收銀時間”、“記錄ID”、“交易金額”拖入數值計算欄

Ø 點擊“收銀時間”數值計算欄按鈕，選擇“值字段設置”

Ø 在“計算類型”中選擇“最大值”

Ø 在對話框左下角，點擊“數字格式”，設定時間格式為：yyyy-mm-dd，然後“确定”

Ø 點擊“銷售金額”數值計算欄按鈕，選擇“值字段設置”

Ø 在“計算類型”中選擇“平均值”，然後“确定”

Ø 在“記錄ID”數值計算按鈕欄，選擇“值字段設置”

Ø 在“計算類型”中選擇“計數”，然後“确定”

在透視表頂部篩選項“銷售類型”處，點擊下拉按鈕小角标，在“選擇多項”前的小方框中打勾，然後點掉“退貨”和“贈送”前的勾，然後“确定”會得到如下結果。

以上我們得到了：

1）F值：客戶這1年共消費了多少次

2）M值：客戶每次交易的平均消費金額

但是，R值還需要做些處理。目前R值隻得到的是客戶最近一次消費日期，需要計算距離數據采集日期的天數。

Excel操作：

Ø 鼠标拉選列标簽ABCD,選中透視表所在的四列

Ø 按ctrl^C（複制），點擊“開始”菜單欄下，快捷按鈕欄“粘帖”下的小下拉三角标，選擇“粘帖值”【或者點“選擇性粘帖”，然後選擇粘帖值】，用單純的數據形式覆蓋原有透視表。

Ø 在C1單元格中輸入數據采集日期2010-09-27，格式為yyyy-mm-dd

Ø 然後選中C1單元格，複制其中内容

Ø 選中B5：B1204【快捷操作：點中B5,同時按住Shift^Ctrl後點擊向下箭頭，松開ctrl鍵，繼續按住shift鍵，按一次向上箭頭，取消數據最後一行的彙總數據】

Ø 點擊“開始”菜單欄下快捷按鈕欄上的“粘帖”按鈕下方的下拉箭頭，選擇“選擇性粘帖”，在對話框中勾選“減”，然後“确定”

Ø 在不取消目前選擇的情況下，選擇“開始”菜單欄下快捷按鈕欄上的格式化下拉菜單，選擇“數字”

因為得到的數據為最後交易日期 減去 數據采集日期的天數，是負值，所以，還需要處理。

Ø 在D1單元格中輸入-1

Ø 然後ctrl^C複制D1單元格中的值（-1）

Ø 然後選中B5：B1204【快捷操作同上】

Ø “開始”-“粘帖”下拉按鈕-“選擇性粘帖”-在計算部分選擇“乘”，然後點擊“确定”

最後得到：

到此，我們得到R,F,M針對每個客戶編号的值

3數據分析

R-score, F-score, M-score的值，為了對客戶根據R,F,M進行三等分，我們需要計算數據的極差（最大值和最小值的差），通過對比R（或者F,M）值和極差三等分距，來确定R（或者F,M）的R-score, F-score, M-score。

所以先計算R、F、M的最大值、最小值、極差三等分距

Excel操作：

Ø F1到H1代表R\F\M的最大值，利用公式“=max(B5:B1204)” 計算,（計算F時B換成C，M時B換成D即可）

Ø F2到H2代表R\F\M的最小值，利用公式“=min(B5:B1204)”計算（計算F時B換成C，M時B換成D即可）

Ø F3到H3代表R\F\M的極差三等分距，利用公式“=(F1-F2)/3”計算（計算F時F換成G，M時F換成H即可）

Ø 【以上快捷操作可用，先輸入F1,F2,F3單元格裡的公式，選擇F1：F3三個單元格，然後拉動右下角的黑色小十字叉，向右拖動複制F列公式到G和H列即可】

R-score的計算公式為：

Ø E5單元格内輸入：“=IF(ROUNDUP((B5-$F$2)/$F$3,0)=0,1,ROUNDUP((B5-$F$2)/$F$3,0)) ”

Ø 之所以使用IF判斷函數，主要是考慮到當R值為最小值時，roundup(B5,0)為0，用if函數判斷如果為0，則強制為1。

Ø 之所以用$F$2鎖定引用的單元格，是為了後續的公式複制，最小值和極差三等分距不會發生相對引用而變化位置【鎖定引用單元格除了手工添加$符号外，快捷方式是選中引用的單元格按F4快捷鍵，此處都比較麻煩，手工輸入$符号還快些】

Ø 【另外一種簡單的處理方式就是直接用公式“=ROUNDUP((B5-$F$2)/$F$3,0)”，然後用ctrl^H快捷操作，将0值替換成1即可，這個替換需要将公式複制-快捷粘帖為數值後進行】

F-score和M-score如法炮制。

Ø F5單元格公式為： =IF(ROUNDUP((C5-$G$2)/$G$3,0)=0,1,ROUNDUP((C5-$G$2)/$G$3,0))

Ø G5單元格公式為： =IF(ROUNDUP((D5-$H$2)/$H$3,0)=0,1,ROUNDUP((D5-$H$2)/$H$3,0))

RFM-score的計算，利用分别乘以100-10-1然後相加的方式，讓R、F、M分别為一個三位數字的三個百分位、十分位和個位表達，該三位數的三個位代表了3x3x3=27魔方三個維度上的坐标。

Ø H5單元格的公式為： =E5*100 F5*10 G5

Ø 選中E4到H4區域，雙擊右下角小黑色十字叉，複制E4到H4公式到所有客戶數據中

Ø 得到結果如下：

接下來的步驟就是統計各個魔方上的客戶數量

再次利用透視表形成統計結果

Excel操作：

Ø “插入”菜單欄下快捷按鈕欄按“透視表”，在數據表區域中選擇A4：H1204【确認這個選擇，自動跳出來的區域要改一下的哦】，然後點擊“确定”

Ø 将RFM-Score拖入“行标簽”中，将“客戶編号”拖入“數值計算”欄中，點擊“數值計算”欄中的“客戶編号”項，選擇“字段數值設置”，選擇計算方法為“計數”，得到處理結果如下：

4數據分析結果解讀和可視化

得到這個分析結果，利用Excel的條件格式功能可以對得到的數據分析結果做簡單的視覺化。

Excel操作：

Ø 将透視表中B列拉寬（如上圖）

Ø 選中B5:B22列

Ø “開始”菜單欄下快捷按鈕欄點擊“條件格式”下拉菜單中選擇“數據條”，然後選擇一個顔色即可

通過條形圖的視覺化，可以直觀地對比哪類客戶數量較多。

5數據分析結果的商業解讀（略）

因為此數據為模拟數據，且數據分析過程中有許多需要商業活動參與執行者參與的過程。此案例僅僅是一種操作的演示。具體的分析結果解讀，讀者可以自行思考。

大家可以思考以下幾個問題：

1）哪一類客戶是最優質的客戶？

2）哪一類客戶是具有高流失風險的優質客戶？

3）哪一類客戶是需要進行Upsell的客戶？

4）哪一類客戶是公司的高成本客戶（不賺錢客戶）？

附件：

官方數據分析精選（sjfxjx），覆蓋大數據與互聯網科技行業8.1萬人群。

商務合作：請聯系QQ1339638765

,

更多精彩资讯请关注tft每日頭條，我们将持续为您更新最新资讯!

查看全部