一、项目背景

英国一家主要销售创意礼品的24小时线上零售店，这家零售店在Kaggle开源了从2010年12月1日至2011年12月9日大约53万行真实的交易数据。其中一些消费者都属于批发商级别，需要利用RFM模型对客户进行精准管理，挖掘客户价值。

关键词： MySQL；Tableau；RFM模型

二、理解数据

首先在Kaggle上阅读了解各个字段记录的信息以及之间的逻辑关系：
InvoiceNo --> 订单编号

StockCode --> 产品代码

Description --> 产品描述

Quantity --> 产品购买数量

InvoiceDate --> 订单生成时间

UnitPrice --> 单价

CustomerID --> 顾客ID

Country --> 客户所在国家

然后,使用Navicat可以非常方便的将CSV数据导入数据库中。

注意:需要重新设置字段数据类型。

alter table data modify column Quantity int(20);
alter table data modify column UnitPrice double;

三、数据清洗与数据预处理

1、删除重复值

# 删除重复值
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同样为空，需要和相关业务人员进行沟通确定缺失原因和处理措施。

删除异常值

# 删除异常值
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 订单编号,
CONCAT(COUNT(*)-COUNT(StockCode),' ','|',ROUND((1 - COUNT(StockCode)/COUNT(*))*100,1), '%') as 产品代码,
CONCAT(COUNT(*)-COUNT(Description),' ','|',ROUND((1 - COUNT(Description)/COUNT(*))*100,1), '%') as 产品描述,
CONCAT(COUNT(*)-COUNT(Quantity),' ','|',ROUND((1 - COUNT(Quantity)/COUNT(*))*100,1), '%') as 产品购买数量,
CONCAT(COUNT(*)-COUNT(InvoiceDate),' ','|',ROUND((1 - COUNT(InvoiceDate)/COUNT(*))*100,1), '%') as 订单生成时间,
CONCAT(COUNT(*)-COUNT(UnitPrice),' ','|',ROUND((1 - COUNT(UnitPrice)/COUNT(*))*100,1), '%') as 单价,
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 国家
from backup_data;

发现’产品描述‘和’顾客ID‘字段存在缺失值，所占比例分别为0.1%和25.1%。
2）对于‘产品描述’字段，考虑到其对总体的影响以及比例非常小，可以直接删除所在的行；重新创建一张Description不包含null值得表使用，原始数据保存。

# 删除Description含有空值的行,并且创建一张新的表
create table backup1_data
select *
from backup_data
where Description is not null;
drop table backup_data;

3）’顾客ID‘字段为非计算字段并且缺失的比例较大，若直接删除可能会对数据建模的准确性产生较大影响。实际业务中应该和业务人员进行沟通确定缺失的原因和处理措施。
此外，留意到项目背景中特意提到存在一些批发商级别的客户，这是不是意味着在数量那一栏需要分组处理呢？浏览数据后决定把Quantity分为五组（1-10、11-100、101-500、501-1000、1000+）我们先看一下各个订单下单品购买数量规模分布，然后计算一下不同分组中顾客ID缺失的行所占的比例。
为方便处理，我们先添加一列关于CustomerID是否为null的字段，作为分类标签。

# 添加关于CustomerID是否含有空值的标签字段,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，并且利用分组功能将各个订单下单品购买数量级分为1-10、11-100、101-500、501-1000、1000+五组，我们看一下各个数量级对产品购买总量的贡献。

再看一下各个类别下，CustomerID为null所占的比例:

CustomerID为null的订单所占的比例

通过条形图我们可以直观的看出在1-10类别中CustomerID为null的比例超过25%，在11-100中接近16%，在其他类别中的所占比例较低。
结论：由于缺失值所在类别集中在1-100区间，并不是随机分布，直接删除会对样本分析的准确性产生影响，需要根据实际的分析需求和业务人员沟通后再决定这部分数据的后续处理。

但是此处我们需要进行RFM模型分析，并不需要建模，故暂时做删除处理。
删除CustomerID缺失的行,并且创建一张新的表

# 删除CustomerID缺失的行,并且创建一张新的表
create table backup2_data
select *
from backup1_data
where CustomerID is not null;
drop table backup1_data;

4、格式化处理

RFM模型分析中需要某段时间的交易次数和交易金额。
1）nvoiceDate格式标准化

# InvoiceDate格式标准化
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;

添加InvoiceTime和Seals

四、数据分析

RFM模型是根据客户活跃程度和交易金额，进行客户价值细分的一种分析方法
RFM分析指标：

RFM分析指标

客户精细分类参考：

1、数据准备

用户总数：4339
订单总数：18536
统计时间区间：2010/12/01-2011/12/09
间隔天数计算锚点:2011/12/09
间隔天数：用户最近一次交易日期-间隔天数计算锚点
交易频率：统计时间区间内交易次数/1
交易金额：统计时间区间内总的交易额

create table RFM_model
select CustomerID, 
DATEDIFF('2011-12-09',MAX(InvoiceTime))as 间隔天数,
COUNT(DISTINCT InvoiceNo)as 交易频率, 
ROUND(SUM(Seals),2) as 交易金额
from backup2_data
group by CustomerID
order by 间隔天数 DESC,交易金额 DESC,交易频率  DESC;

2、各个维度分析及客户评分

R_S：距离当前日期越近，得分越高，最高5份，最低1份
评分标准：10%、25%、50%、75%分位数附近值
F_S：交易频率越高,得分越高，最高5份，最低1份
评分标准：10%、25%、50%、75%分位数附近值
M_S：交易金额越高，得分越高，最高5份，最低1份
评分标准：10%、25%、50%、75%分位数附近值

1）R-S维度分析及客户评分

通过设置不同的组距发现最合适的评分标准为：30、90、180、360、>360
添加R-S列：

alter table RFM_model add R_S int(5);
update RFM_model set R_S = 
case 
    when 间隔天数 <= 30 then 5
    when 间隔天数 >30 and 间隔天数 <= 90 then 4
    when 间隔天数 >90 and 间隔天数 <=180 then 3
    when 间隔天数 >180 and 间隔天数 <= 360 then 2
    else 1
    end

2）F-S维度分析及客户评分

交易频率筛选范围：all，组距：1

发现数据乘完美的幂律分布，把组距调至10并且添加合计百分比标签：

i交易频率筛选范围：all，组距：10

发现85%以上的Customer每年的消费次数集中在30次以内（可能是因为商品是礼物的原因，消费频率较低，毕竟大家不会天天送礼物，实际业绩表现需要跟业务人员确认），并且1-10，10-20之间的频率分布差异较大，需要将交易频率筛选范围调制0-30，组距调为1进行分析。

交易频率筛选范围：0-30，组距：1

发现最合适的评分标准为：>32，32，10，3，=1。只有一次交易记录说明没有复购，单独作为一组。
添加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维度分析及客户评分

度量：计数

96.02%的订单金额都在6K以内。因为存在二八原则，将度量由计数改为总和看一下交易额在6K以内的订单占总交易额的比例。

度量：总和

交易额在6K以内的订单占总交易额的比例为72.22%，剩下3.98%交易额大于6K的订单贡献了其余的27.78%的交易额。
现在将组距调至0.5K：

发现最合适的评分标准为：>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、客户分层

此时有125个层次，每个维度需要通过平均值分为两层，将客户分层减为8层。实际业务中需要实际需求进行分层。
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标签

# 添加R_level,F_level,M_level标签
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）依据客户精细分类参考添加客户类别标签，可以明显看出R、F、M的权重依次升高

# 添加客户类别标签
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重要价值客户'
      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重要发展客户'    
      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一般价值客户'
      when R_level=1 and F_level=0 and M_level=0 then '5一般发展客户'
      when R_level=0 and F_level=1 and M_level=0 then '6一般保持客户'
            else '7流失客户' end;

五、RFM模型分析结果

统计区间（2010年12月1日至2011年12月9日）内，不包含空CustomerID的情况下，用户总数为4372位。包含空CustomerID的情况下，总订单25900笔，其中（Quantity<0）订单5172笔，总交易金额9747747.93英镑，产品销售总量5628434件。其中，用于RFM 模型分析的用户数量为4339位，有效订单18536笔。

客户分类结果

在2010年12月1日到2011年12月9日期间，0高价值客户数量为1158位，所占数量占总客户量的比例为26.69%，所占交易额占总交易额的比例为73.08%，基本符合现实中的二八原则。

高价值客户数量所占比例和交易金额所占比例分布图

数量占比26.90%的7流失客户对总交易额的贡献占比仅为4.44%，利用RFM模型有效的筛选出了低价值客户，提高精准营销的精度，降低营销成本的同时提高转化率。

流失客户数量所占比例和交易金额所占比例分布图

数据分析|UK E-Commerce真实数据RFM模型分析