一富弦、項(xiàng)目背景
??英國(guó)一家主要銷(xiāo)售創(chuàng)意禮品的24小時(shí)線上零售店，這家零售店在Kaggle開(kāi)源了從2010年12月1日至2011年12月9日大約53萬(wàn)行真實(shí)的交易數(shù)據(jù)噪裕。其中一些消費(fèi)者都屬于批發(fā)商級(jí)別萧锉，需要利用RFM模型對(duì)客戶進(jìn)行精準(zhǔn)管理，挖掘客戶價(jià)值洗贰。
關(guān)鍵詞： MySQL；Tableau陨倡；RFM模型
二敛滋、理解數(shù)據(jù)
首先在Kaggle上閱讀了解各個(gè)字段記錄的信息以及之間的邏輯關(guān)系：
InvoiceNo --> 訂單編號(hào)
StockCode --> 產(chǎn)品代碼
Description --> 產(chǎn)品描述
Quantity --> 產(chǎn)品購(gòu)買(mǎi)數(shù)量
InvoiceDate --> 訂單生成時(shí)間
UnitPrice --> 單價(jià)
CustomerID --> 顧客ID
Country --> 客戶所在國(guó)家
注意:需要重新設(shè)置字段數(shù)據(jù)類(lèi)型兴革。
alter table data modify column Quantity int(20);
alter table data modify column UnitPrice double;
三绎晃、數(shù)據(jù)清洗與數(shù)據(jù)預(yù)處理
1、刪除重復(fù)值
# 刪除重復(fù)值
create table uq_data
select into DISTINCT(*)
from data
2杂曲、異常值處理
??查看Quantity和UnitPrice中是否存在小于0的值
# 查看Quantity和UnitPrice中是否存在小于0的值
select * from uq_data
where Quantity <= 0 or UnitPrice <= 0;
??注意到UnitPrice為null的行Description同樣為空庶艾，需要和相關(guān)業(yè)務(wù)人員進(jìn)行溝通確定缺失原因和處理措施。
刪除異常值
# 刪除異常值
create table backup_data
select *
from uq_data
where Quantity > 0 and UnitPrice > 0;
3擎勘、缺失值處理
1）查看哪些字段存在缺失值及其所占比例
# 查看各列是否有缺失值以及所占比例
select
CONCAT(COUNT(*)-COUNT(InvoiceNo),' ','|',ROUND((1 - COUNT(InvoiceNo)/COUNT(*))*100,1), '%') as 訂單編號(hào),
CONCAT(COUNT(*)-COUNT(StockCode),' ','|',ROUND((1 - COUNT(StockCode)/COUNT(*))*100,1), '%') as 產(chǎn)品代碼,
CONCAT(COUNT(*)-COUNT(Description),' ','|',ROUND((1 - COUNT(Description)/COUNT(*))*100,1), '%') as 產(chǎn)品描述,
CONCAT(COUNT(*)-COUNT(Quantity),' ','|',ROUND((1 - COUNT(Quantity)/COUNT(*))*100,1), '%') as 產(chǎn)品購(gòu)買(mǎi)數(shù)量,
CONCAT(COUNT(*)-COUNT(InvoiceDate),' ','|',ROUND((1 - COUNT(InvoiceDate)/COUNT(*))*100,1), '%') as 訂單生成時(shí)間,
CONCAT(COUNT(*)-COUNT(UnitPrice),' ','|',ROUND((1 - COUNT(UnitPrice)/COUNT(*))*100,1), '%') as 單價(jià),
CONCAT(COUNT(*)-COUNT(CustomerID),' ','|',ROUND((1 - COUNT(CustomerID)/COUNT(*))*100,1), '%') as 顧客ID,
CONCAT(COUNT(*)-COUNT(Country),' ','|',ROUND((1 - COUNT(Country)/COUNT(*))*100,1), '%') as 國(guó)家
from backup_data;
??發(fā)現(xiàn)’產(chǎn)品描述‘和’顧客ID‘字段存在缺失值咱揍，所占比例分別為0.1%和25.1%。
??2）對(duì)于‘產(chǎn)品描述’字段货抄，考慮到其對(duì)總體的影響以及比例非常小述召，可以直接刪除所在的行朱转；重新創(chuàng)建一張Description不包含null值得表使用蟹地，原始數(shù)據(jù)保存积暖。
# 刪除Description含有空值的行,并且創(chuàng)建一張新的表
create table backup1_data
select *
from backup_data
where Description is not null;
drop table backup_data;
??3）’顧客ID‘字段為非計(jì)算字段并且缺失的比例較大，若直接刪除可能會(huì)對(duì)數(shù)據(jù)建模的準(zhǔn)確性產(chǎn)生較大影響怪与。實(shí)際業(yè)務(wù)中應(yīng)該和業(yè)務(wù)人員進(jìn)行溝通確定缺失的原因和處理措施夺刑。
??此外，留意到項(xiàng)目背景中特意提到存在一些批發(fā)商級(jí)別的客戶分别，這是不是意味著在數(shù)量那一欄需要分組處理呢遍愿？瀏覽數(shù)據(jù)后決定把Quantity分為五組（1-10、11-100耘斩、101-500沼填、501-1000、1000+）我們先看一下各個(gè)訂單下單品購(gòu)買(mǎi)數(shù)量規(guī)模分布括授，然后計(jì)算一下不同分組中顧客ID缺失的行所占的比例坞笙。
??為方便處理，我們先添加一列關(guān)于CustomerID是否為null的字段荚虚，作為分類(lèi)標(biāo)簽薛夜。
# 添加關(guān)于CustomerID是否含有空值的標(biāo)簽字段,is null :1,is not null:0
alter table backup1_data add column CId_null_label int;
update backup1_data set CId_null_label = 1
where CustomerID is null;
update backup1_data set CId_null_label = 0
where CustomerID is not null;
??Tableau連接到MySQL，并且利用分組功能將各個(gè)訂單下單品購(gòu)買(mǎi)數(shù)量級(jí)分為1-10版述、11-100梯澜、101-500、501-1000渴析、1000+五組晚伙，我們看一下各個(gè)數(shù)量級(jí)對(duì)產(chǎn)品購(gòu)買(mǎi)總量的貢獻(xiàn)。
??再看一下各個(gè)類(lèi)別下俭茧，CustomerID為null所占的比例:
??通過(guò)條形圖我們可以直觀的看出在1-10類(lèi)別中CustomerID為null的比例超過(guò)25%撬腾，在11-100中接近16%，在其他類(lèi)別中的所占比例較低恢恼。
??結(jié)論：由于缺失值所在類(lèi)別集中在1-100區(qū)間民傻，并不是隨機(jī)分布，直接刪除會(huì)對(duì)樣本分析的準(zhǔn)確性產(chǎn)生影響场斑，需要根據(jù)實(shí)際的分析需求和業(yè)務(wù)人員溝通后再?zèng)Q定這部分?jǐn)?shù)據(jù)的后續(xù)處理漓踢。
??但是此處我們需要進(jìn)行RFM模型分析，并不需要建模漏隐，故暫時(shí)做刪除處理喧半。
??刪除CustomerID缺失的行,并且創(chuàng)建一張新的表
# 刪除CustomerID缺失的行,并且創(chuàng)建一張新的表
create table backup2_data
select *
from backup1_data
where CustomerID is not null;
drop table backup1_data;
4、格式化處理
RFM模型分析中需要某段時(shí)間的交易次數(shù)和交易金額青责。
1）nvoiceDate格式標(biāo)準(zhǔn)化
# InvoiceDate格式標(biāo)準(zhǔn)化
alter table backup2_data add column nvoiceTime varchar(255) not null;
update backup2_data set InvoiceTime = STR_TO_DATE(InvoiceDate, '%m/%d/%Y %H:%I');
2）添加交易金額Seals
# 添加交易金額Seals
alter table backup2_data add column Seals float not null;
update backup2_data set Seals = Quantity * UnitPrice;
四挺据、數(shù)據(jù)分析
RFM模型是根據(jù)客戶活躍程度和交易金額取具，進(jìn)行客戶價(jià)值細(xì)分的一種分析方法
RFM分析指標(biāo)：
客戶精細(xì)分類(lèi)參考：
1、數(shù)據(jù)準(zhǔn)備
用戶總數(shù)：4339
訂單總數(shù)：18536
統(tǒng)計(jì)時(shí)間區(qū)間：2010/12/01-2011/12/09
間隔天數(shù)計(jì)算錨點(diǎn):2011/12/09
間隔天數(shù)：用戶最近一次交易日期-間隔天數(shù)計(jì)算錨點(diǎn)
交易頻率：統(tǒng)計(jì)時(shí)間區(qū)間內(nèi)交易次數(shù)/1
交易金額：統(tǒng)計(jì)時(shí)間區(qū)間內(nèi)總的交易額
create table RFM_model
select CustomerID,
DATEDIFF('2011-12-09',MAX(InvoiceTime))as 間隔天數(shù),
COUNT(DISTINCT InvoiceNo)as 交易頻率,
ROUND(SUM(Seals),2) as 交易金額
from backup2_data
group by CustomerID
order by 間隔天數(shù) DESC,交易金額 DESC,交易頻率 DESC;
2扁耐、各個(gè)維度分析及客戶評(píng)分
R_S：距離當(dāng)前日期越近暇检，得分越高，最高5份婉称，最低1份
評(píng)分標(biāo)準(zhǔn)：10%块仆、25%、50%王暗、75%分位數(shù)附近值
F_S：交易頻率越高,得分越高悔据， 最高5份，最低1份
評(píng)分標(biāo)準(zhǔn)：10%俗壹、25%科汗、50%、75%分位數(shù)附近值
M_S：交易金額越高绷雏，得分越高头滔， 最高5份，最低1份
評(píng)分標(biāo)準(zhǔn)：10%之众、25%拙毫、50%、75%分位數(shù)附近值
1）R-S維度分析及客戶評(píng)分
通過(guò)設(shè)置不同的組距發(fā)現(xiàn)最合適的評(píng)分標(biāo)準(zhǔn)為：30棺禾、90缀蹄、180、360膘婶、>360
添加R-S列：
alter table RFM_model add R_S int(5);
update RFM_model set R_S =
case
when 間隔天數(shù) <= 30 then 5
when 間隔天數(shù) >30 and 間隔天數(shù) <= 90 then 4
when 間隔天數(shù) >90 and 間隔天數(shù) <=180 then 3
when 間隔天數(shù) >180 and 間隔天數(shù) <= 360 then 2
else 1
end
2）F-S維度分析及客戶評(píng)分
??發(fā)現(xiàn)數(shù)據(jù)乘完美的冪律分布，把組距調(diào)至10并且添加合計(jì)百分比標(biāo)簽：
??發(fā)現(xiàn)85%以上的Customer每年的消費(fèi)次數(shù)集中在30次以內(nèi)（可能是因?yàn)樯唐肥嵌Y物的原因衅码，消費(fèi)頻率較低，畢竟大家不會(huì)天天送禮物脊岳，實(shí)際業(yè)績(jī)表現(xiàn)需要跟業(yè)務(wù)人員確認(rèn)）逝段，并且1-10，10-20之間的頻率分布差異較大割捅，需要將交易頻率篩選范圍調(diào)制0-30奶躯，組距調(diào)為1進(jìn)行分析。
??發(fā)現(xiàn)最合適的評(píng)分標(biāo)準(zhǔn)為：>32嘹黔，32，10莫瞬，3儡蔓，=1郭蕉。只有一次交易記錄說(shuō)明沒(méi)有復(fù)購(gòu)，單獨(dú)作為一組喂江。
添加F-S列：
# 添加F-S列:
alter table RFM_model add F_S int(5);
update RFM_model set F_S =
case
when 交易頻率 > 32 then 5
when 交易頻率 > 10 and 交易頻率 <= 32 then 4
when 交易頻率 > 3 and 交易頻率 <= 10 then 3
when 交易頻率 >= 2 and 交易頻率 <= 3 then 2
else 1
end;
3）M-S維度分析及客戶評(píng)分
??96.02%的訂單金額都在6K以內(nèi)召锈。因?yàn)榇嬖诙嗽瓌t，將度量由計(jì)數(shù)改為總和看一下交易額在6K以內(nèi)的訂單占總交易額的比例开呐。
??交易額在6K以內(nèi)的訂單占總交易額的比例為72.22%烟勋，剩下3.98%交易額大于6K的訂單貢獻(xiàn)了其余的27.78%的交易額规求。
??現(xiàn)在將組距調(diào)至0.5K：
??發(fā)現(xiàn)最合適的評(píng)分標(biāo)準(zhǔn)為：>6K筐付，6k，4k阻肿，2K瓦戚，1K，<1K丛塌。
添加F-S列:
# 添加M-S列:
alter table RFM_model add M_S int(5);
update RFM_model set M_S =
case
when 交易金額 > 6000 then 5
when 交易金額 > 4000 and 交易金額 <= 6000 then 4
when 交易金額 > 2000 and 交易金額 <= 4000 then 3
when 交易金額 >= 1000 and 交易金額 <= 2000 then 2
else 1
end;
3较解、客戶分層
??此時(shí)有125個(gè)層次，每個(gè)維度需要通過(guò)平均值分為兩層赴邻，將客戶分層減為8層印衔。實(shí)際業(yè)務(wù)中需要實(shí)際需求進(jìn)行分層。
??1）求平均值
# 求平均值
select
ROUND(AVG(R_S),1)as R_mean,
ROUND(AVG(F_S),1)as F_mean,
ROUND(AVG(M_S),1)as M_mean
from RFM_model;
??2）添加R_level,F_level,M_level標(biāo)簽
# 添加R_level,F_level,M_level標(biāo)簽
alter table RFM_model add (
R_level int(5),
F_level int(5),
M_level int(5))
update RFM_model set
R_level =
case
when R_S >3.8 then 1 else 0 end,
F_level =
case
when F_S >2.1 then 1 else 0 end,
M_level =
case
when M_S >1.7 then 1 else 0 end;
??3）依據(jù)客戶精細(xì)分類(lèi)參考添加客戶類(lèi)別標(biāo)簽姥敛，可以明顯看出R奸焙、F、M的權(quán)重依次升高
# 添加客戶類(lèi)別標(biāo)簽
alter table RFM_model add Cus_level varchar(255);
update RFM_model set Cus_level =
case
when R_level=1 and F_level=1 and M_level=1 then '0重要價(jià)值客戶'
when R_level=0 and F_level=1 and M_level=1 then '1重要保持客戶'
when R_level=1 and F_level=0 and M_level=1 then '2重要發(fā)展客戶'
when R_level=0 and F_level=0 and M_level=1 then '3重要挽留客戶'
when R_level=1 and F_level=1 and M_level=0 then '4一般價(jià)值客戶'
when R_level=1 and F_level=0 and M_level=0 then '5一般發(fā)展客戶'
when R_level=0 and F_level=1 and M_level=0 then '6一般保持客戶'
else '7流失客戶' end;
五彤敛、RFM模型分析結(jié)果
??統(tǒng)計(jì)區(qū)間（2010年12月1日至2011年12月9日）內(nèi)与帆，不包含空CustomerID的情況下，用戶總數(shù)為4372位墨榄。包含空CustomerID的情況下玄糟，總訂單25900筆，其中（Quantity<0）訂單5172筆袄秩，總交易金額9747747.93英鎊阵翎，產(chǎn)品銷(xiāo)售總量5628434件。其中之剧，用于RFM 模型分析的用戶數(shù)量為4339位郭卫，有效訂單18536筆。
??在2010年12月1日到2011年12月9日期間猪狈，0高價(jià)值客戶數(shù)量為1158位箱沦，所占數(shù)量占總客戶量的比例為26.69%，所占交易額占總交易額的比例為73.08%雇庙，基本符合現(xiàn)實(shí)中的二八原則谓形。
??數(shù)量占比26.90%的7流失客戶對(duì)總交易額的貢獻(xiàn)占比僅為4.44%灶伊，利用RFM模型有效的篩選出了低價(jià)值客戶，提高精準(zhǔn)營(yíng)銷(xiāo)的精度寒跳，降低營(yíng)銷(xiāo)成本的同時(shí)提高轉(zhuǎn)化率聘萨。
