這是數(shù)據(jù)分析BY EXCEL的第二篇（RFM實戰(zhàn)篇），內(nèi)容很干。
主要通過一個實例來撕碎RFM模型高大上的外衣，讓它成為你我的掌上玩物。沒錯，不僅是了解，還能親手建立一個屬于自己的RFM模型。
實戰(zhàn)數(shù)據(jù)（脫敏）下載鏈接已放在文末。
RFM模型的大名可謂如雷貫耳，官方說法：RFM模型是衡量客戶價值和客戶創(chuàng)利能力的重要工具和手段。
簡單的說就是按照R（最近一次購買距今多少天)，F(xiàn)（購買了多少次）以及M（平均或者累計購買金額）將客戶進行分類，按照這3個維度可以切分出8類客戶，最后我們根據(jù)不同類型客戶占比情況來評估客戶的整體分布，并針對不同類型的客戶進行有針對性的營銷。
RFM模型只需要3個字段（R、F、M），就能夠?qū)蛻暨M行價值分類，且適用于各行各業(yè)。
咳！概念一講起來就像教科書一樣乏味，我們來看一組對比。
你的原始數(shù)據(jù)是這樣的：
是不是有一絲心動了？
整個流程可以分成4步：觀察數(shù)據(jù)——數(shù)據(jù)清洗——維度確認(rèn)——分值計算——結(jié)果展示。
Step 1 觀察數(shù)據(jù)
數(shù)據(jù)分析和建模的第一步就是就是認(rèn)識數(shù)據(jù)，請，屏息觀察數(shù)據(jù)30S，
不難發(fā)現(xiàn)：
1、源數(shù)據(jù)一共28834行，涉及12個字段，每一行代表一筆訂單。
2、按“付款時間”排序，發(fā)現(xiàn)訂單數(shù)據(jù)時間范圍是2017年11月1日-2018年4月30日共6個月數(shù)據(jù)
3、總體數(shù)據(jù)規(guī)整，但”發(fā)貨時間“存在缺失值，且“發(fā)貨時間”為空的行，對應(yīng)“訂單狀態(tài)”的值是“付款以后用戶退款成功，交易自動關(guān)閉”，退款用戶數(shù)據(jù)不該納入模型，這是我們需要剔除的數(shù)據(jù)。
Step 2 數(shù)據(jù)清洗：
2.1 刪除
首先，需要剔除掉退款的訂單，
選中“訂單狀態(tài)”列(G列），點擊“開始”模塊的”排序和篩選“，勾選“篩選”
這時候“訂單狀態(tài)”列會出現(xiàn)一個小三角，點擊之，
選中所有篩選出來的行，切記，先按'ALT + ;“（選取區(qū)域只選擇可見區(qū)域，避免刪除正常交易數(shù)據(jù)），然后右鍵，”刪除行“，這樣，所有退款數(shù)據(jù)被刪除，我們再在“訂單狀態(tài)”勾選“交易成功”的訂單，發(fā)現(xiàn)初步清洗后的數(shù)據(jù)還剩下27794行。
2.2 關(guān)鍵字段提取
這一步我們要整理出模型所需要的關(guān)鍵字段，RFM模型，當(dāng)然是R、F、M三兄弟的值了：
R（最近一次購買距今多少天)，F(xiàn)（購買了多少次）以及M（平均或者累計購買金額）。
我們把源數(shù)據(jù)復(fù)制到另一個SHEET（養(yǎng)成好習(xí)慣，備份一個咯），再把多余的列刪掉，留下需要的3個字段——“買家昵稱”、“付款時間”、“實付金額”：
別急，上面3個字段能夠衍生出模型所需要的所有關(guān)鍵值。
有請數(shù)據(jù)透視表先生登場。
選中所有數(shù)據(jù)，插入“數(shù)據(jù)透視表”，把“買家昵稱”拖到行的位置，
再把“付款時間”、“買家昵稱”、實付金額“都拖到列的位置（按照R,F,M的邏輯）：
數(shù)據(jù)透視表默認(rèn)是計數(shù)表示，也就是說，“買家昵稱”那一列顯示的數(shù)字，就是客戶購買次數(shù)，即F的值。
那，時間，金額怎么算呢？
我們需要分別設(shè)置他們的字段格式，選中“付款時間”右鍵“值字段設(shè)置”，計算類型默認(rèn)是計數(shù)，我們把他改成“最大值”（假如一個客戶購買過3次，我們只需要獲取他最近1次的購買時間，對時間類型的數(shù)據(jù)來說，時間越大值越大，比如2018年1月1日 10:00 > 2017年12月1日 10:00，這里的最大值就是最近一次購買時間）
值的設(shè)置同理，我們想要知道每個客戶平均下單金額，把計算類型改成“平均值”即可。
到這里，我們的初始數(shù)據(jù)整理基本告一段落，結(jié)果如下：
等等！R（最近一次購買距今多久）是要求距離現(xiàn)在多少天，現(xiàn)在只有一個最近付款的時間是什么鬼！
所以，我們插入一個輔助列。
由于訂單是截止到4月30日的，我們假設(shè)建模時間是2018年5月1日，求每個客戶R值，就是求5月1日這一天距離他最近一次付款時間的間隔天數(shù)。
直接上公式：
我們先用DATE(2018,5,1)賦予2018年5月1日時間格式，便于DAYS（）計算。
再用DAYS(DATE(2018,5,1),最近一次下單日期)，直接計算出客戶最后一次購買時間距離現(xiàn)在有多少天。
R值出爐，F(xiàn)值現(xiàn)成（買家昵稱那一列），M值已定（平均實付金額列），每個客戶都一個R、F、M值與之對應(yīng)，我們調(diào)整下列的位置，修改下列名，整理好的數(shù)據(jù)如下：
STEP 3——維度確認(rèn)：
現(xiàn)在，每個客戶都有一個對應(yīng)的R、F、M值。
簡單來說，維度確認(rèn)是給每個客戶的（R值、F值、M值）打分，我們需要建立一個評判標(biāo)準(zhǔn)，給我們期待的值（比如最后一次購買時間距離今天越近越好）打上高分，給不喜歡的值（付款時間距離今天越久越不好）打低分。
以R值為例，我們篩選出了每個客戶最后一次下單距離現(xiàn)在x天，那這個x是不是越大越好呢？并不是！這個值越大，說明客戶越久沒有回購，他流失可能性也更大；這個值越小，就表明客戶最后一次付款時間距離現(xiàn)在越近。
一般是采用5分制（以30天為維度），這里我們規(guī)定最近一次購買時間距離今天0-30天的，給它打5分，30-60天的，是4分，60-90、90-120、120-720天分別是3、2、1分。
怎么操作呢？我們創(chuàng)建一張新的sheet，可以制作如下輔助表格：
接著再給F（購買頻次）和M平均購買金額打分：
特別說明一下，這里的分?jǐn)?shù)區(qū)間都是包含最小值不包含最大值的，拿F值來說，1=<購買頻次<2時，F(xiàn)值是1分，而這個區(qū)間的唯一整數(shù)是1，也就是給購買頻次為1的客戶打1分，2次2分，5次到100次客戶打5分（之所以最高分的最大值都是一個大的很離譜的數(shù)字，是因為這樣更可能把數(shù)據(jù)中極端的值包含在內(nèi)，避免后面公式出現(xiàn)錯誤）
注：這個分值應(yīng)根據(jù)實際業(yè)務(wù)進行調(diào)整。
STEP 4——分值計算
回顧一下，我們現(xiàn)在已經(jīng)掌握了每個客戶的R\F\M值，也已經(jīng)創(chuàng)建好了打分的維度框架。
這里復(fù)習(xí)一下上一篇文章的IF函數(shù)，一分鐘迅速嘮叨一下哈，函數(shù)的形式是介個樣子的：IF(表達式，表達式為真的操作，表達式為假的操作）。不太形象是吧。IF(A1>1，'大于1',“等于0”)，就是如果A1的值大于1，公式所在的單元格就顯示為大于1，否則就顯示為等于0。IF的強大之處在于它可以無限嵌套。
以R值為例，參考我們STEP2中確定的打分維度，在M列后新建一個R-SCORE列，在F2的位置輸入如下公式：
這個公式是說，當(dāng)C2單元格（R值）小于30，打5分，大于等于30小于60，打4分，依次類推，當(dāng)大于120天，打1分。
F、M值打分公式邏輯一樣，不同的只是維度：
F值：
M值：
我們，已經(jīng)，完成了第一輪打分了，結(jié)果差不多是這樣的：
難道還有第二輪？
恭喜你答對了！
客觀請看，現(xiàn)在R、F、M值分值在1-5之間，3個值進行組合，111，112，113...這樣可以組合出125種結(jié)果，如果將結(jié)果分成這么多類和不分沒有什么兩樣，一般來說，我們只需要判斷每個客戶的R、F、M值是否大于平均值即可。
下面，我們用AVERAGE()函數(shù)，分別計算出R、F、M值的平均值（分別計算出所有客戶R值的平均、F值的平均、M值的平均）:
這一步很簡單
用IF（值>=平均值，1，0）即可。結(jié)果如下：
分別代表什么呢？
每一個客戶的1和0分別代表著是否大于對應(yīng)的平均值，我們可以把客戶分成下面8組：
同理，潛力客戶呢，是最近有購買，購買頻次高，但是每次購買金額低的客戶，需要我們?nèi)ネ诰颍嵘麄兊目蛦?，讓他們轉(zhuǎn)化為重要價值客戶。
重要深耕客戶呢，他們是最近有購買，但是不經(jīng)常買，雖然每次消費金額高，我們當(dāng)然要想方設(shè)法提升他們的購買頻次。
下面各種類型客戶，也是這樣一個推理邏輯。
在操作上，依然是一個IF嵌套函數(shù)：
I、J、K列分別對應(yīng)客戶R值是否大于均值，F(xiàn)值是否大于均值，M值是否大于均值。
AND(I2=1,J2=1,K2=1)=TRUE 是說如果3個值都等于1（意思是客戶3個值都大于平均值），則歸為重要價值客戶，以此類推。（不要被IF語句迷惑，其實就是一串嵌套）
到這一步，RFM模型地基已經(jīng)建立完畢，3值鼎立之勢已經(jīng)被我們化成了8類。
只需插入數(shù)據(jù)透視表就可以知道店鋪這一段時間，不同類型的客戶人數(shù)和占比，不過，社會主義接班人的我們，還可以更進一步，一探金額和客戶類型之間的關(guān)系。
我們在最后加入一列，用客戶的F值 X M值，得到用戶累計消費金額。
STEP 5 結(jié)果展示：
選中所有數(shù)據(jù)，插入數(shù)據(jù)透視表，
5.1 客戶占比分析：
這一步，我們想知道不同類型的客戶人數(shù)具體有多少，每個占比分別是多少。
將“客戶類型”放到行區(qū)域，再把”客戶類型“（從上面的數(shù)據(jù)透視表字段區(qū)域拖）和“客戶類型”拖至值區(qū)域。沒錯，拖兩次，一次是為了計數(shù)，一次是為了看占比。
右鍵“客戶類型2”列，選擇“值顯示方式”，“列匯總的百分比”：
別對兩列數(shù)據(jù)作圖之，
5.2 客戶金額分析：
我們還想知道不同類型客戶他們花了多少錢以及金額占比是怎么樣的，和4.1邏輯類似，這里省略透視操作：），只給結(jié)果：
1、流失客戶占比最高，達8605人，人數(shù)占比33.85%（這類客戶最近無購買，當(dāng)初購買頻次低于平均值且平均下單金額也低），這部分客戶（通過看源數(shù)據(jù)時間）集中在去年雙11下單，屬價格敏感型客戶，在即將到來的618（同為大促，優(yōu)惠力度大）我們可以嘗試對他們進行喚醒。
2、挽回客戶（最近未購買，購買頻次低，購買金額高）7108人，人數(shù)占比27.96%，但支付金額占比最高。也就是說，對店鋪銷售貢獻最高的客戶，下單時間遠、購買頻次低，已經(jīng)瀕于流失邊緣。他們和流失客戶的區(qū)別在于他們平均消費金額較高，一方面，我們可以抽樣獲取他們的聯(lián)系方式，進行回訪，調(diào)查客戶沉睡原因；另一方面，篩選出他們購買的產(chǎn)品，結(jié)合復(fù)購率進行分析，是未到回購周期（上次購買的產(chǎn)品還沒用完），還是產(chǎn)品復(fù)購率本身就很低，店鋪近期拉新乏力，導(dǎo)致挽回客戶消費金額占比最高。
3、BLABLABLA..
至此，我們基于訂單源數(shù)據(jù)完成了整個RFM模型的建立。
寫這篇文章再+整理資料（源數(shù)據(jù)，每一步都分成單獨SHEET）真的累慘了。。。。
整理不易，覺得有那么一點點幫助的話，點點右下角的好看！
感恩~
附上實戰(zhàn)數(shù)據(jù)鏈接：
鏈接：pan.baidu.com/s/1FkI4ko
密碼：um8q
聯(lián)系客服
