简介
项目介绍：使用 Kaggle 的 E-Commerce 数据集。 该数据集基于一个英国礼品电商公司 13 个月的真实交易数据。通过用户消费行为分析， 建立 RFM 模型进行用户分层， 针对性维护高价值用户， 实现精细化用户运营管理。
负责内容： 1. 使用 SQL 语法 select/alter/update/create 等对数据集进行清洗加工，实现缺失值、异常值和重复值处理，日期格式转换与清洗后数据新表建立。
- 使用 Power BI 实现可视化展示，用于分析消费频次、 累计消费金额、新老客户占比、用户生命周期、 复购率与回购率等指标。 针对不同用户制定合运营策略。
- 建立 RFM 模型将用户分为八类群体， 分析八类客户的月度订单量、消费金额与地域特点，进一步刻画群体特征。 方便精准营销以降低运营成本。
项目背景
本项目基于一个英国电商公司从2010年12月1日到2011年12月9日的真实交易数据集。该电商主要销售的商品是各类礼品， 主要客户是来自不同国家的的分销商。本文主要使用SQL语法分析月度消费趋势、个体消费情况、RFM用户分层、用户生命周期、计算复购率与回购率等关键指标，并使用Power BI实现数据可视化。希望从数据中更深入的了解用户消费行为，为商家运营提供洞察。
分析目的
利用数据探索用户消费趋势，为商家制定营销策略提供分析及建议，并利用RFM模型实现客户精细化管理。
分析框架
- 用户月度消费趋势分析（消费金额、产品数量、消费人数、消费次数、客单价、订单均价）
- 用户个体消费情况分析（用户地域、消费金额频次、累计消费金额曲线）
- 用户消费行为分析（用户首购与最后一次购买时间分析、每月新老用户占比、用户分层RFM模型及各类用户数量和占比）
- 用户生命周期分析（用户购买周期、用户生命周期）
- 用户月度复购率和回购率分析
分析过程
1. 数据预处理
这部分主要包括无关字段的删除，缺失值检查，异常值检查、重复值检查与日期格式统一。
- 由于解决问题不需要用到Description字段，故去除该列。
- 本文不分析InvoiceNo列中以C开头的退货情况，故也将此部分行删去。
create table datanew as select CustomerID, InvoiceNo, InvoiceDate, StockCode, Quantity, UnitPrice, Country from data where InvoiceNo not like 'C%' ;
SELECT count(*) FROM `datanew`;
处理完成后共有532621条数据。
异常值检查
接着看下数据是否有缺失值。
select sum(case when CustomerID is null then 1 else 0 end) as '客户编号',
sum(case when InvoiceNo is null then 1 else 0 end) as '订单编号',
sum(case when InvoiceDate is null then 1 else 0 end) as '订单日期',
sum(case when StockCode is null then 1 else 0 end) as '产品编号',
sum(case when Quantity is null then 1 else 0 end) as '产品数量',
sum(case when UnitPrice is null then 1 else 0 end) as '产品单价',
sum(case when Country is null then 1 else 0 end )as '国家' from datanew;
上图说明客户编号存在134697条缺失，需与业务确认缺失原因，由于本次分析已客户为主体，故缺失客户编码的数据删除。
create table cleandata as select * from datanew where CustomerID is not null;
SELECT count(*) FROM cleandata;
清洗后数据共397924条。
异常值检查
SELECT max(quantity),min(quantity), max(unitprice),min(unitprice), max(invoicedate),min(invoicedate) FROM cleandata;
其中产品数量和订单时间无异常，但产品单价存在为0的异常值，这类商品可能是赠品，不应该算入用户主动消费行为中，故删除。
delete from cleandata where unitprice=0;
SELECT count(*) FROM cleandata;
清洗后数据共397884条。
重复值检查
create table cleandata1 as select distinct * from cleandata;
select count(*) from cleandata1;
最终清洗后数据共392690条。
格式转换
将订单时间列字符串转化为统一的sql日期格式，方便使用日期函数进行操作。
UPDATE cleandata1 set InvoiceDate= str_to_date(InvoiceDate,'%m/%d/%Y %H:%i');
预处理完成如下：
2. 月度消费趋势分析（消费金额、产品数量、消费人数、消费次数，客单价，订单均价）
select left(invoicedate,7) as Date, sum(quantity*unitprice) as Amount from cleandata1 group by left(invoicedate,7);
select left(invoicedate,7) as Date, sum(quantity) as Quantity from cleandata1 group by left(invoicedate,7);
select left(invoicedate,7) as Date, count(distinct invoiceno) as order_num, count(distinct customerid) as customer_num from cleandata1 group by left(invoicedate,7);
select left(invoicedate,7) as Date, round((sum(quantity*unitprice)/count(distinct customerid)),2) as Amount_per_customer from cleandata1 group by left(invoicedate,7);
select left(invoicedate,7) as Date, (sum(quantity*unitprice)/count(distinct invoiceno)) as Amount_per_order from cleandata1 group by left(invoicedate,7);
上图可知，消费金额从2010年12月到2011年8月比较稳定，呈波动上升趋势；2011年8月开始明显上升，在11月达到最高值约110+万，12月却迅速回落。
上图可知，产品消费数量在2011年8月之前沿平均约35万件的位置来回波动，从8月开始上升，在11月达到峰值约67万件，12月回落至不到30万件的位置。
上图可知，消费次数总体大于消费人数，走势基本相同。2011年8月前月均消费约1200次，月均消费人数约900人。在8月两变量开始提升后，到11月均达到峰值，消费次数约为2600，消费人数约为1600。
上图可知，人均消费金额总体大于订单均价，走势基本相同。2011年1月两者均出现小高峰，可能与活动有关。2011年2月到2011年9月较平稳，分别约为450和650元。两变量峰值均出现在2011年12月，分别约为850和670元。
3. 用户个体消费情况分析（用户地域、消费金额频次、累计消费金额曲线）
用户地域分析
SELECT Country, round(sum(Quantity*UnitPrice),2) as Amount, count(distinct InvoiceNo) as order_num, round(sum(Quantity*UnitPrice)/count(distinct InvoiceNo),2) as Average_order_price, count(distinct CustomerID) as customer_num, round(sum(Quantity*UnitPrice)/count(distinct CustomerID),2) as per_customer_transaction FROM `cleandata1` group by Country order by Amount desc;
上图可知，超过90%的用户来自英国，且贡献近82%的销售金额。值得一提的是，查看原始数据发现，荷兰和爱尔兰用户数仅有12个，但消费金额却排名第二和三，占有6%。说明荷兰和爱尔兰存在采购量极大的大客户需重点关注。另外，有9个国家只有1个客户，建议衡量投入产出比，适当放弃一些地区，集中优势在英国。进一步看下各国家人均订单量和人均消费量。
上图可知，人均消费量TOP5分别来自爱尔兰、荷兰、新加坡、澳大利亚和瑞典。说明这些地区大客户较多，而英国小客户较多。
消费金额频次分析
select t.bins, count(t.ct) as Frequency from
(select customerid, count(customerid) as ct, sum(quantity*unitprice) as amount,
case when sum(quantity*unitprice)>0 and sum(quantity*unitprice)<=250 then '0-250'
when sum(quantity*unitprice)>250 and sum(quantity*unitprice)<=500 then '250-500'
when sum(quantity*unitprice)>500 and sum(quantity*unitprice)<=750 then '500-750'
when sum(quantity*unitprice)>750 and sum(quantity*unitprice)<=1000 then '750-1000'
when sum(quantity*unitprice)>1000 and sum(quantity*unitprice)<=1250 then '1000-1250'
when sum(quantity*unitprice)>1250 and sum(quantity*unitprice)<=1500 then '1250-1500'
when sum(quantity*unitprice)>1500 and sum(quantity*unitprice)<=1750 then '1500-1750'
when sum(quantity*unitprice)>1750 and sum(quantity*unitprice)<=2000 then '1500-1750'
when sum(quantity*unitprice)>2000 and sum(quantity*unitprice)<=2250 then '1500-1750'
when sum(quantity*unitprice)>2250 and sum(quantity*unitprice)<=2500 then '1500-1750'
else 'over2500' end as bins
from cleandata1
group by customerid) t
group by t.bins;
去除几个极大值后，我们发现，用户消费金额集中在0~500元，有约1750名用户，占比约40%。
累计金额曲线
select n, CustomerID, M, sum(a.M) over(order by a.n) AS cumsum, (sum(a.M) over(order by a.n)/8886657.04) as per
from
(SELECT CustomerID, M, row_number() over(order by M) as n FROM `rfm2` order by M) a;
上图中横坐标表示累计用户数量，纵坐标表示累计消费金额百分比。分析可知，50%的用户仅贡献约10%的消费金额，80%的用户贡献约25%的消费金额，也就是说存在20%高价值客户贡献约75%的金额，基本符合消费二八定律。集中精力拓展与这20%高价值客户的合作，比分散精力对所有客户更值得。排名4000以后的300余名用户（不到10%）则贡献了约60%的消费金额，这部分是最需要维系的大客户。
4. 用户消费行为分析
用户首购与最后一次购买分析
select customerid, min(InvoiceDate), max(InvoiceDate), DATEDIFF(max(InvoiceDate), min(InvoiceDate)) from cleandata1 group by CustomerID order by CustomerID;
上图可知，用户首购时间最多集中在2010年12月上半月，每日均值大于50人；从2011年1月开始首购人数保持平稳，每日均值约为15人。可能10年12月份因该电商新成立在引流期有优惠引入了大量新用户，但之后新用户流入减少。后续新用户能维持在一定水平，是运营平稳的表现。
上图可知，从2010年12月到2011年9月用户最后一次购买稳定集中在15人左右；而从9月开始大幅上升，12月最高值达到100+人。时间越长，流失部分用户也是比较正常的情况。在新用户大量增加的前期没有大量流失，且大量用户最后一次购买集中在最后的统计日期，说明大多数用户近三个月有购买记录，仍处于活跃购买状态，并没有在引入后快速流失，是比较理想的。
每月新老用户占比
select left(c.InvoiceDate,7), count(distinct a.CustomerID), left(a.min_date,7) from
(select CustomerID, min(InvoiceDate) as min_date from cleandata1 group by CustomerID) a
join cleandata1 c
on a.CustomerID=c.CustomerID
where left(c.InvoiceDate,7)=left(a.min_date,7)
group by left(c.InvoiceDate,7);
上图可知，从2010年12月开始至2011年6月，新用户占比持续下降，用户流失风险高；直到2011年7月有所回升，可与业务人员确认原因，7月至12月趋于稳定，基本维持在25%左右。
基于RFM模型的用户分层
RFM模型包含三个关键维度，最近一次消费 (Recency)、消费频率(Frequency)和消费金额 (Monetary)。使用这三个维度对用户进行分类，可以将用户分为2x2x2=8种类型，之后根据每类用户的消费特征进行精细化运营。
接下来依次求R值、F值、M值。
create table rfm as SELECT *, datediff('2011-12-10',max(InvoiceDate)) as R, count(distinct InvoiceNo) as F, round(sum(Quantity*UnitPrice),2) as M from cleandata1 group by CustomerID;
给RFM模型打分的方法大概有两种，1. 选取分位数打分。 2. 依据行业和业务经验设定阈值。这里选择分位数法，由于SQL不方便进行RFM打分分层，这里使用python计算分位数。
通过pd.cut方法，将用户分层并打上标签，这里我用的分层方法是python中的quantile函数。
import math
def quantile_p(data, p):
#pos = (len(data) + 1)*p
pos = 1 + (len(data)-1)*p
pos_integer = int(math.modf(pos)[1])
pos_decimal = pos - pos_integer
Q = data[pos_integer - 1] + (data[pos_integer] - data[pos_integer - 1])*pos_decimal
return Q
data = df['R'].sort_values().tolist()
Q1 = quantile_p(data, 0.2)
print("Q1:", Q1)
Q2 = quantile_p(data, 0.4)
print("Q2:", Q2)
Q3 = quantile_p(data, 0.6)
print("Q3:", Q3)
Q4 = quantile_p(data, 0.8)
print("Q3:", Q4)
将R、F、M分别打分（分层）。
create TABLE rfm1 AS select *,
(case when R<=13.8 then 5
when R>13.8 AND R<=33 then 4
when R>33 AND R<=72 then 3
when R>72 AND R<=180 then 2 else 1 end) as 'score_R',
(case when F<=1 then 1
when F>1 AND F<=2 then 2
when F>2 AND F<=3 then 3
when F>3 AND F<=6 then 4 else 5 end) as 'score_F',
(case when M<=249.34 then 1
when M>249.34 AND M<=487.41 then 2
when M>487.41 AND M<=933.35 then 3
when M>933.35 AND M<=2055.05 then 4 else 5 end) as 'score_M' FROM rfm;
接下来，求出R、F、M的均值，以均值为界，将每个变量分为高低两维度（大于等于均值取1，小于均值取0）。
select ROUND(AVG(score_R),1) as avg_R, ROUND(AVG(score_F),1) as avg_F, ROUND(AVG(score_M),1) as avg_M from rfm1;
alter table rfm1 add (final_R int, final_F int, final_M int);
update rfm1 set final_R=1 where score_R>=3.0;
update rfm1 set final_R=0 where score_R<3.0;
update rfm1 set final_F=1 where score_F>=2.6;
update rfm1 set final_F=0 where score_F<2.6;
update rfm1 set final_M=1 where score_M>=3.0;
update rfm1 set final_M=0 where score_M<3.0;
生成用户分层表
create table rfm2 as SELECT *,
(case when final_R=1 and final_F=1 and final_M=1 then '重要价值客户'
when final_R=0 and final_F=1 and final_M=1 then '重要唤回客户'
when final_R=1 and final_F=0 and final_M=1 then '重要发展客户'
when final_R=0 and final_F=0 and final_M=1 then '重要挽留客户'
when final_R=1 and final_F=1 and final_M=0 then '潜力客户'
when final_R=0 and final_F=1 and final_M=0 then '一般客户'
when final_R=1 and final_F=0 and final_M=0 then '新到客户'
when final_R=0 and final_F=0 and final_M=0 then '流失客户' end) as 客户分类
FROM rfm1
用户数量分析
上图分析发现，所有客户中重要价值客户最多，占总数的35.73%；总体来说是一个不错的情况，因为这类型客户的价值最高，应努力提高该类型客户的满意度，增加留存量。其次是流失客户21.99%，新到客户14.91%，重要挽留客户8.78%，重要发展客户7.98%和重要唤回客户7.51%。另外，不难看出在近一年的交易中，有21.99%是处于流失边缘的客户。为什么会流失？哪些地区的流失情况比较严重？流失的客户都有哪些特点？都是需要引起注意的问题。
消费金额分析
上图可见，贡献最高金额和订单数的是重要价值客户，金额690万，订单数1550个远超其他客户。重要唤回客户贡献金额位列第二，为60万，这类客户是高频次和高金额的用户，将其恢复购买可以带来可观的收益，流失原因需要结合业务情况和订单进一步研究。
流失客户和新到客户呈现高订单数低消费金额的情况，说明新客户很多是被优惠活动引入，而流失用户可能包含一些羊毛用户。
用户地域分析
条形图按消费人数正向排序，总体来看，重要价值客户都保持在四分之一到二分之一的比例，活跃客户群较稳定，维系此类客户可以提供VIP待遇，提升用户满意度。
英国本土要注意流失客户数量较多，对于该国策略可以采取保持重要价值客户，并找到客户流失原因，减少重要挽回向流失用户转化。对于德国来说重要挽留客户第二多，挽留客户有较高流失风险，要主动联系用户并明确流失原因，再次激活用户购物。同时加大新客户引入。法国情况基本与英国本土相同。
营销策略
· 重要价值客户（111）目标是让其一直留存下来，可以提供专项客服，个性化增值服务等。
· 重要唤回客户（011）目标是唤回近期消费，可以推出活动，精准提供所需资源，推送消息召回等。
· 重要发展客户（101）目标提高消费频次，可以推荐其他产品，提供积分计划或每日任务等。
· 重要挽留客户（001）目标是让其回归再次消费并了解其不继续购买的原因，可以使用电话或短信等联系，调查原因，弥补不足。
· 新到客户（100）目标是提高其消费兴趣，可以通过活动营销，提供社群互动，砍价等策略留住用户。
· 一般客户（010）属于容易流失的群体，一般维持。可以使用赠送优惠券、推动活动信息等方法与客户重新联系。
· 潜力客户（110）目标挖掘其消费潜力，可以多推荐价值更高且用户喜好的产品。
· 流失客户（000）较大概率不是目标客户，可以选择性放弃。
5. 用户生命周期分析
select count(distinct customerid) from cleandata1 group by datediff(max(invoicedate),min(invoicedate));
select count(distinct invoiceno) from cleandata1 group by datediff(max(invoicedate),min(invoicedate));
用户生命周期分布受一次性购买用户（用户生命周期0天）影响比较严重，故排除一次性购买用户（共有1548名，占36%）重作图。
总体上用户平均生命周期131天，中位数93天。最大值为373天，最小值0天。
6. 用户月度复购率和回购率分析
复购率：单位时间内购买次数大于1的人数与当月总购买人数比值。
回购率：单位时间内曾购买用户在下一单位时间内再次购买的人数与单位时间内曾购买用户比值。
（这里由于2011年12月的数据仅有9天，考虑到可能影响整体结果，故不包括12月的复购率与2011年11月和12月的回购率）
月度复购率
SELECT 下单年月, COUNT(c) AS 下单人数
, COUNT(if(c > 1, 1, NULL)) AS 当月复购人数
, concat(round(COUNT(if(c > 1, 1, NULL)) / COUNT(c) * 100, 2), '%') AS '当月复购率'
FROM (
SELECT left(InvoiceDate,7) AS 下单年月
, CustomerID, COUNT(distinct InvoiceNo) AS c
FROM cleandata1
GROUP BY 下单年月, CustomerID
) a
GROUP BY 下单年月;
上图可知，月度复购率均值为23%，根据《精益数据分析》中的理论，目前此电商处于用户混合模式，在新用户转化和老用户留存、复购上的应该做到平均分配精力和资源。前期复购率较低可能因为初创推广期大量新用户涌入，造成分母被持续扩大。
90天内重复购买率达到1%~15%；说明你处于用户获取模式；把更多的精力和资源投入到新用户获取和转化； 90天内重复购买率达到15~30%；说明你处于混合模式；平衡用在新用户转化和老用户留存、复购上的精力和资源； 90天内重复购买率达到30%以上；说明你处于忠诚度模式；把更多的精力和资源投入到用户复购上；(《精益数据分析》原文)
月度回购率
SELECT a.date, COUNT(a.CustomerID) AS 当月购买人数, COUNT(b.CustomerID) AS 次月回购人数
, concat(round(COUNT(b.CustomerID) / COUNT(a.CustomerID) * 100, 2), '%') AS 次月回购率
FROM (
SELECT DATE_FORMAT(InvoiceDate, '%Y-%m-%01') AS date, CustomerID
FROM cleandata1
GROUP BY date, CustomerID
ORDER BY date, CustomerID
) a
LEFT JOIN (
SELECT DATE_FORMAT(InvoiceDate, '%Y-%m-%01') AS date, CustomerID
FROM cleandata1
GROUP BY date, CustomerID
ORDER BY date, CustomerID
) b
ON a.CustomerID = b.CustomerID
AND a.date = date_sub(b.date, INTERVAL 1 MONTH)
GROUP BY date;
上图所知，平均次月回购率达到了39%，总体维持在一个不错的水平。该电商复购率与回购率越高，反映消费者对品牌的忠诚度就越高。
小结
1、用户月度消费趋势：
消费金额及数量：从2010年12月到2011年8月比较稳定，呈波动上升趋势。2011年8月开始明显上升，在11月达到最高值，12月迅速回落。
消费次数及人数：消费次数总体大于消费人数，走势基本相同。2011年8月前月均消费约1200次，月均消费人数约900人。在8月两变量开始提升后，到11月均达到峰值，消费次数约为2600，消费人数约为1600。
2、用户个体消费情况：
地域分析：发现超过90%的用户来自英国，且贡献近82%的销售金额。但荷兰和爱尔兰存在采购量极大的大客户需重点关注。有9个国家只有1个客户，建议衡量投入产出比，适当放弃。
累计消费金额分析：累计金额曲线发现20%高价值客户贡献约75%的金额，基本符合消费二八定律。集中精力拓展与这20%高价值客户的合作，比分散精力对所有客户更值得。
3、用户消费行为分析：
首购与末购分析：用户首购时间最多集中在2010年12月上半月，每日均值大于50人；从2011年1月开始首购人数保持平稳，每日均值约为15人。从2010年12月到2011年9月用户最后一次购买稳定集中在15人左右；而从9月开始大幅上升，12月最高值达到100+人。
新老用户分析：从2010年12月开始至2011年6月，新用户占比持续下降，用户流失风险高；直到2011年7月有所回升，7月至12月趋于稳定，基本维持在25%左右。
4、RFM用户分层分析：
单量、金额与人数分析：8种客户中，重要价值客户订单量、消费金额和人数均位列榜首；而重要唤回客户的消费金额排第二位，但订单量和人数均较落后；流失客户人数和订单量排第二，但消费金额仅排第五。
地域分析：总体来看，重要价值客户都保持在四分之一到二分之一的比例，活跃客户群较稳定。英国本土要注意流失客户数量较多，对于该国策略可以采取保持重要价值客户，并找到客户流失原因，减少重要挽回向流失用户转化。对于德国来说重要挽留客户第二多，挽留客户有较高流失风险，要主动联系用户并明确流失原因，再次激活用户购物。同时加大新客户引入。法国情况基本与英国本土相同。
营销策略：
· 重要价值客户（111）目标是让其一直留存下来，可以提供专项客服，个性化增值服务等。
· 重要唤回客户（011）目标是唤回近期消费，可以主动联系召回（推送、邮件、公众号等），给予优惠券等。
· 重要发展客户（101）目标提高消费频次，可以推荐其他产品，提供积分计划或每日任务等。
· 重要挽留客户（001）目标是让其回归再次消费并了解其不继续购买的原因，可以使用电话或短信等联系，调查原因，弥补不足。
· 新到客户（100）目标是提高其消费兴趣，可以通过活动营销，提供社群互动，砍价等策略留住用户。
· 一般客户（010）属于容易流失的群体，一般维持。可以使用赠送优惠券、推动活动信息等方法与客户重新联系。
· 潜力客户（110）目标挖掘其消费潜力，可以多推荐价值更高且用户喜好的产品。
· 流失客户（000）较大概率不是目标客户，可以选择性放弃。
5、用户生命周期分析：
一次性购买用户占36%，排除这部分影响后，用户平均生命周期131天，中位数93天。最大值为373天，最小值0天。
6、复购率和回购率分析：
复购率：平均月度复购率为23%，根据《精益数据分析》中的理论，目前此电商处于用户混合模式，在新用户转化和老用户留存、复购上的应该做到平均分配精力和资源。前期复购率较低可能因为初创推广期大量新用户涌入，造成分母被持续扩大。
回购率：平均次月回购率达到39%，总体维持在一个不错的水平。该电商复购率与回购率越高，反映消费者对品牌的忠诚度就越高。
