實戰案例——用Excel對會員客戶交易數據進行RFM分析

---

背景：一個會員服務的企業，有近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分析。

操作步驟：

第一步：數據的清洗

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

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

Excel操作：

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

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

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

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

第二步：數據處理

根據分析需要，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針對每個客戶編號的值

第三步：

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單元格的公式為： =E5100+F510+G5

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

得到結果如下：

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

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

Excel操作：

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

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

第四步：數據分析結果解讀和可視化

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

Excel操作：

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

選中B5:B22列

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

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

第五步：數據分析結果的商業解讀（略）

 作者：趙興峰 乾兆億(R)數據分析創始人 企業經營數據分析專家 bbk365.com/js/bbk365_ad_ext.html">

詳情請咨詢！

客服熱線：023-66090381