自學數據分析——Learn by doing(EXCEL-RFM建模實戰)

來自專欄自學數據分析之路

這是自學數據分析Learn by doing的第二篇（RFM實戰篇），通過一個實例來撕碎RFM高大上的外衣，讓它成為你我的掌上玩物。沒錯，不僅是了解，還能親手建立一個屬於自己的RFM模型。

實戰數據（脫敏）下載鏈接已放在文末。

RFM模型的大名可謂如雷貫耳，官方說法：RFM模型是衡量客戶價值和客戶創利能力的重要工具和手段。

簡單的說就是按照R（最近一次購買距今多少天)，F（購買了多少次）以及M（平均或者累計購買金額）將客戶進行分類，按照這3個維度可以切分出8類客戶，最後我們根據不同類型客戶佔比情況來評估客戶的整體分布，並針對不同類型的客戶進行有針對性的營銷。

RFM模型只需要3個欄位（R、F、M），就能夠對客戶進行價值分類，且適用於各行各業。

咳！概念一講起來就像教科書一樣乏味，我們來看一組對比。

你的原始數據是這樣的：

學完後你的模型是這樣的：

是不是有一絲心動了？

整個流程可以分成4步：觀察數據——數據清洗——維度確認——分值計算——結果展示。

Step 1 觀察數據

數據分析和建模的第一步就是就是認識數據，請，屏息觀察數據30S，

不難發現：

1、源數據一共28834行，涉及12個欄位，每一行代表一筆訂單。

2、按「付款時間」排序，發現訂單數據時間範圍是2017年11月1日-2018年4月30日共6個月數據

3、總體數據規整，但」發貨時間「存在缺失值，且「發貨時間」為空的行，對應「訂單狀態」的值是「付款以後用戶退款成功，交易自動關閉」，退款用戶數據不該納入模型，這是我們需要剔除的數據。

Step 2 數據清洗：

2.1 刪除

首先，需要剔除掉退款的訂單，

選中「訂單狀態」列(G列），點擊「開始」模塊的」排序和篩選「，勾選「篩選」

這時候「訂單狀態」列會出現一個小三角，點擊之，

OK，所有退款的訂單已經被我們篩出來了，

選中所有篩選出來的行，切記，先按"ALT + ;「（選取區域只選擇可見區域，避免刪除正常交易數據），然後右鍵，」刪除行「，這樣，所有退款數據被刪除，我們再在「訂單狀態」勾選「交易成功」的訂單，發現初步清洗後的數據還剩下27794行。

2.2 關鍵欄位提取

這一步我們要整理出模型所需要的關鍵欄位，RFM模型，當然是R、F、M三兄弟的值了：

R（最近一次購買距今多少天)，F（購買了多少次）以及M（平均或者累計購買金額）。

我們把源數據複製到另一個SHEET（養成好習慣，備份一個咯），再把多餘的列刪掉，留下需要的3個欄位——「買家昵稱」、「付款時間」、「實付金額」：

等等，RFM模型所需要的欄位好像還沒有出來誒！

別急，上面3個欄位能夠衍生出模型所需要的所有關鍵值。

有請數據透視表先生登場。

選中所有數據，插入「數據透視表」，把「買家昵稱」拖到行的位置，

再把「付款時間」、「買家昵稱」、實付金額「都拖到列的位置（按照R,F,M的邏輯）：

到這一步，還記得上一篇講的數據透視表嗎？數據透視表自動將」買家昵稱「作為分組依據，換句話說，我們源數據中一個客戶多次下單的數據，現在都匯總到1行了。

數據透視表默認是計數表示，也就是說，「買家昵稱」那一列顯示的數字，就是客戶購買次數，即F的值。

那，時間，金額怎麼算呢？

我們需要分別設置他們的欄位格式，選中「付款時間」右鍵「值欄位設置」，計算類型默認是計數，我們把他改成「最大值」（假如一個客戶購買過3次，我們只需要獲取他最近1次的購買時間，對時間類型的數據來說，時間越大值越大，比如2018年1月1日 10:00 > 2017年12月1日 10:00，這裡的最大值就是最近一次購買時間）

設置完成後時間格式會變成數字，不要慌，選中「付款時間」列，設置成「年/月/日 時:分」的格式：

M值的設置同理，我們想要知道每個客戶平均下單金額，把計算類型改成「平均值」即可。

到這裡，我們的初始數據整理基本告一段落，結果如下：

下面我們把做好的透視表的所有數據（不要複製最下面空白行和總計行）複製到另一張sheet（防止公式衝突），此後的操作都是針對複製的表格。

等等！R（最近一次購買距今多久）是要求距離現在多少天，現在只有一個最近付款的時間是什麼鬼！

所以，我們插入一個輔助列。

由於訂單是截止到4月30日的，我們假設建模時間是2018年5月1日，求每個客戶R值，就是求5月1日這一天距離他最近一次付款時間的間隔天數。

直接上公式：

註：B2是第一個客戶最近一次付款時間所在的單元格

我們先用DATE(2018,5,1)賦予2018年5月1日時間格式，便於DAYS（）計算。

再用DAYS(DATE(2018,5,1),最近一次下單日期)，直接計算出客戶最後一次購買時間距離現在有多少天。

R值出爐，F值現成（買家昵稱那一列），M值已定（平均實付金額列），每個客戶都一個R、F、M值與之對應，我們調整下列的位置，修改下列名，整理好的數據如下：

至此，三值鼎立之勢已成。

STEP 3——維度確認：

現在，每個客戶都有一個對應的R、F、M值。

簡單來說，維度確認是給每個客戶的（R值、F值、M值）打分，我們需要建立一個評判標準，給我們期待的值（比如最後一次購買時間距離今天越近越好）打上高分，給不喜歡的值（付款時間距離今天越久越不好）打低分。

以R值為例，我們篩選出了每個客戶最後一次下單距離現在x天，那這個x是不是越大越好呢？並不是！這個值越大，說明客戶越久沒有回購，他流失可能性也更大；這個值越小，就表明客戶最後一次付款時間距離現在越近。

一般是採用5分制（以30天為維度），這裡我們規定最近一次購買時間距離今天0-30天的，給它打5分，30-60天的，是4分，60-90、90-120、120-720天分別是3、2、1分。

怎麼操作呢？我們創建一張新的sheet，可以製作如下輔助表格：

其中R-SCORE列就是我們給每個區間打的分數。

接著再給F（購買頻次）和M平均購買金額打分

特別說明一下，這裡的分數區間都是包含最小值不包含最大值的，拿F值來說，1=<購買頻次<2時，F值是1分，而這個區間的唯一整數是1，也就是給購買頻次為1的客戶打1分，2次2分，5次到100次客戶打5分（之所以最高分的最大值都是一個大的很離譜的數字，是因為這樣更可能把數據中極端的值包含在內，避免後面公式出現錯誤）

註：這個分值應根據實際業務進行調整。

STEP 4——分值計算

回顧一下，我們現在已經掌握了每個客戶的RFM值，也已經創建好了打分的維度框架。

下面就是按照確認好的維度（STEP2），對每個客戶的3個值進行打分。

這裡複習一下上一篇文章的IF函數，一分鐘迅速嘮叨一下哈，函數的形式是介個樣子的：IF(表達式，表達式為真的操作，表達式為假的操作）。不太形象是吧。IF(A1>1，"大於1",「等於0」)，就是如果A1的值大於1，公式所在的單元格就顯示為大於1，否則就顯示為等於0。IF的強大之處在於它可以無限嵌套。

以R值為例，參考我們STEP2中確定的打分維度，在M列後新建一個R-SCORE列，在F2的位置輸入如下公式：

這個公式是說，當C2單元格（R值）小於30，打5分，大於等於30小於60，打4分，依次類推，當大於120天，打1分。

F、M值打分公式邏輯一樣，不同的只是維度：

F值：

M值：

我們，已經，完成了第一輪打分了，結果差不多是這樣的：

EXCUSE ME？第一輪？

難道還有第二輪？

恭喜你答對了！

客觀請看，現在R、F、M值分值在1-5之間，3個值進行組合，111，112，113...這樣可以組合出125種結果，如果將結果分成這麼多類和不分沒有什麼兩樣，一般來說，我們只需要判斷每個客戶的R、F、M值是否大於平均值即可。

下面，我們用AVERAGE()函數，分別計算出R、F、M值的平均值（分別計算出所有客戶R值的平均、F值的平均、M值的平均）:

然後，我們再把每個客戶的3個值與平均值進行比較，進行二次判斷。大於等於平均值的顯示1，小於的顯示0。（二次判斷是為了簡化分類結果，經過二次判斷，客戶分類結果最多只有8類）。

這一步很簡單

用IF（值>=平均值，1，0）即可。結果如下：

這樣，我們將1000名客戶按照R、F、M值分成8組（111，110，101，100，011，010，001，000）

分別代表什麼呢？

每一個客戶的1和0分別代表著是否大於對應的平均值，我們可以把客戶分成下面8組：

舉個栗子，一個客戶，R大於均值（1），也就是最近有購買，F大於均值（1），也就是購買頻率超過平均次數，M大於均值（1），平均購買金額大於所有客戶的平均金額，綜合來說，近期有購買，購買頻次高，每次購買金額高，這樣的客戶，當然是重要價值客戶了！

同理，潛力客戶呢，是最近有購買，購買頻次高，但是每次購買金額低的客戶，需要我們去挖掘，提升他們的客單，讓他們轉化為重要價值客戶。

重要深耕客戶呢，他們是最近有購買，但是不經常買，雖然每次消費金額高，我們當然要想方設法提升他們的購買頻次。

下面各種類型客戶，也是這樣一個推理邏輯。

在操作上，依然是一個IF嵌套函數：

I、J、K列分別對應客戶R值是否大於均值，F值是否大於均值，M值是否大於均值。

AND(I2=1,J2=1,K2=1)=TRUE 是說如果3個值都等於1（意思是客戶3個值都大於平均值），則歸為重要價值客戶，以此類推。（不要被IF語句迷惑，其實就是一串嵌套）

到這一步，RFM模型地基已經建立完畢，3值鼎立之勢已經被我們化成了8類。

只需插入數據透視表就可以知道店鋪這一段時間，不同類型的客戶人數和佔比，不過，社會主義接班人的我們，還可以更進一步，一探金額和客戶類型之間的關係。

我們在最後加入一列，用客戶的F值 X M值，得到用戶累計消費金額。

STEP 5 結果展示：

選中所有數據，插入數據透視表，

5.1 客戶佔比分析：

這一步，我們想知道不同類型的客戶人數具體有多少，每個佔比分別是多少。

將「客戶類型」放到行區域，再把」客戶類型「（從上面的數據透視表欄位區域拖）和「客戶類型」拖至值區域。沒錯，拖兩次，一次是為了計數，一次是為了看佔比。

值欄位默認都是計數，數據透視表結果區域變成了這樣：

右鍵「客戶類型2」列，選擇「值顯示方式」，「列匯總的百分比」：

我們拿到了不同類型的客戶人數及佔比：

分別對兩列數據作圖之，

5.2 客戶金額分析：

我們還想知道不同類型客戶他們花了多少錢以及金額佔比是怎麼樣的，和4.1邏輯類似，這裡省略透視操作：），只給結果：

基於匯總結果和訂單源數據，我們發現（這裡簡單說兩點，拋磚引玉）：

1、流失客戶佔比最高，達8605人，人數佔比33.85%（這類客戶最近無購買，當初購買頻次低於平均值且平均下單金額也低），這部分客戶（通過看源數據時間）集中在去年雙11下單，屬價格敏感型客戶，在即將到來的618（同為大促，優惠力度大）我們可以嘗試對他們進行喚醒。

2、挽回客戶（最近未購買，購買頻次低，購買金額高）7108人，人數佔比27.96%，但支付金額佔比最高。也就是說，對店鋪銷售貢獻最高的客戶，下單時間遠、購買頻次低，已經瀕於流失邊緣。他們和流失客戶的區別在於他們平均消費金額較高，一方面，我們可以抽樣獲取他們的聯繫方式，進行回訪，調查客戶沉睡原因；另一方面，篩選出他們購買的產品，結合復購率進行分析，是未到回購周期（上次購買的產品還沒用完），還是產品復購率本身就很低，店鋪近期拉新乏力，導致挽回客戶消費金額佔比最高。

3、BLABLABLA..

至此，我們基於訂單源數據完成了整個RFM模型的建立。

寫這篇文章再+整理資料（源數據，每一步都分成單獨SHEET）真的累慘了。。。。

覺得有那麼一點點幫助的話，不要只收藏不點贊不評論吶！

附上實戰數據鏈接：

鏈接：https://pan.baidu.com/s/1FkI4koryOIhSsf4SsKcSzQ

密碼：um8q