数据来源
数据来源于kaggle，https://www.kaggle.com/carrie1/ecommerce-data
理解数据
InvoiceNo：发票编号 ；每笔交易分配唯一的6位整数，而退货订单的代码以字母'c'开头
StockCode：产品编号；每个不同的产品分配唯一的5位整数
Description：产品描述；对每件产品的简略描述
Quantity：产品数量；每笔交易的每件产品的数量
InvoiceDate：交易日期；每笔交易发生的日期和时间
UnitPrice：单价(英镑)；单位产品价格
CustomerID：顾客ID；每个客户分配唯一的5位整数
Country：国家；每个客户所在国家/地区的名称
提出问题
基于RFM模型提出一下问题：
1.客户维度：各类客户的占比为多少，是否正常？
2.时间维度：各类客户分别都在哪些月份购买产品？
3.区域维度：各类客户分别集中在哪些州/国/地区？
4.产品维度：各类客户集中购买的产品分别是哪些？
数据清洗
用python对数据进行清洗(此处省略)
构建模型
1.将利用python清洗的数据导出为csv表:gift_retail_online，将表导入MySQL中。
2.创建分析所需的新表
即:
地区表：online_country_state
价格区间表：online_price_range
客户分类表：online_customer_group
建表过程
①选出国家再人工进行查询得到online_country_state（地区表）
SELECT DISTINCT 国家 AS 国家 FROM gift_retail_online
将国家选出来，由于地区数据需要查询，所以导出国家再进行人工查询其中文名称、所属洲及在其所属洲的位置，部分表截图如下：
②创建临时表price再转化为新表online_price_range（价格区间表）
CREATE VIEW price AS
SELECT 产品编号,
(CASE WHEN 单价=0 THEN '赠品'
WHEN 单价<1 THEN '<1'
WHEN 单价<10 THEN '1-10'
WHEN 单价<50 THEN "10-50"
WHEN 单价<300 THEN "50-300"
WHEN 单价<1000 THEN "300-1000"
WHEN 单价<3000 THEN "1000-3000"
ELSE ">3000" END) AS 价格区间
FROM (SELECT * FROM gift_retail_online WHERE 单价>=0) AS a
3. 由于需要对客户分类
根据RFM模型，创建R_value、F_value、M_value三张临时表，再由三张临时表创建出临时客户分类表，再转化为新表online_customer_group（客户分类表）
*由于这里需要知道R、F、M各自的四分位数才可以给出区间随后进行打分，在这里使用python的quantitle函数，求出各自的分类标准
R指标
数据采集日期与最近消费日期的相隔天数
CREATE VIEW R_value AS
SELECT 顾客ID,
(CASE WHEN 购买天数 BETWEEN 0 AND 30 THEN 5
WHEN 购买天数 BETWEEN 30 AND 90 THEN 4
WHEN 购买天数 BETWEEN 90 AND 180 THEN 3
WHEN 购买天数 BETWEEN 180 AND 360 THEN 2
WHEN 购买天数 BETWEEN 360 AND 720 THEN 1
ELSE 0 END
) AS 购买得分
FROM
(SELECT 顾客ID,DATEDIFF('2011-12-09',MAX(交易日期)) AS 购买天数
FROM
(SELECT * FROM gift_retail_online WHERE 单价>0
AND 发票编号 NOT IN
(SELECT 发票编号 FROM gift_retail_online WHERE 发票编号 LIKE "C%")
AND 顾客ID NOT IN
(SELECT 顾客ID FROM gift_retail_online WHERE 顾客ID='U')
) AS a
GROUP BY 顾客ID) AS b
ORDER BY 购买得分 DESC
F指标
CREATE VIEW F_value AS
SELECT 顾客ID,
(CASE WHEN 购买次数 BETWEEN 1 AND 2 THEN 1
WHEN 购买次数 BETWEEN 2 AND 5 THEN 2
WHEN 购买次数 BETWEEN 5 AND 10 THEN 3
WHEN 购买次数 BETWEEN 10 AND 20 THEN 4
WHEN 购买次数 BETWEEN 20 AND 8000 THEN 5
ELSE 0 END
) AS 购买频率得分
FROM
(SELECT 顾客ID,COUNT(顾客ID) AS 购买次数
FROM
(SELECT * FROM gift_retail_online WHERE 单价>0
AND 发票编号 NOT IN
(SELECT 发票编号 FROM gift_retail_online WHERE 发票编号 LIKE "C%")
AND 顾客ID NOT IN
(SELECT 顾客ID FROM gift_retail_online WHERE 顾客ID='U')
) AS a
GROUP BY 顾客ID) AS b
ORDER BY 购买频率得分 DESC
M指标
CREATE VIEW M_value AS
SELECT 顾客ID,
(CASE WHEN 购买金额 BETWEEN 0 AND 500 THEN 1
WHEN 购买金额 BETWEEN 500 AND 2000 THEN 2
WHEN 购买金额 BETWEEN 2000 AND 5000 THEN 3
WHEN 购买金额 BETWEEN 5000 AND 10000 THEN 4
WHEN 购买金额 BETWEEN 10000 AND 280000 THEN 5
ELSE 0 END
) AS 购买金额得分
FROM
(SELECT 顾客ID,SUM(笔销售额) AS 购买金额
FROM
(SELECT * FROM gift_retail_online WHERE 单价>0
AND 发票编号 NOT IN
(SELECT 发票编号 FROM gift_retail_online WHERE 发票编号 LIKE "C%")
AND 顾客ID NOT IN
(SELECT 顾客ID FROM gift_retail_online WHERE 顾客ID='U')
) AS a
GROUP BY 顾客ID) AS b
ORDER BY 购买金额得分 DESC
计算三者的平均值
SELECT avg(购买得分) from r_value
3.832
SELECT avg(购买频率得分) from f_value
4.4539
SELECT avg(购买金额得分) from m_value
1.8882
根据R,F,M三个虚拟表创建客户分类表，转化为新表online_customer_group（客户分类表）
CREATE VIEW 客户分类 AS
SELECT 顾客ID,
(CASE WHEN R>3.82 AND F>4.45 AND M>1.89 THEN '重要价值客户'
WHEN R>3.82 AND F>4.45 AND M<1.89 THEN '一般价值客户'
WHEN R>3.82 AND F<4.45 AND M>1.89 THEN '重要发展客户'
WHEN R>3.82 AND F<4.45 AND M<1.89 THEN '一般发展客户'
WHEN R<3.82 AND F>4.45 AND M>1.89 THEN '重要保持客户'
WHEN R<3.82 AND F>4.45 AND M<1.89 THEN '一般挽留客户'
WHEN R<3.82 AND F<4.45 AND M>1.89 THEN '重要挽留客户'
ELSE '流失客户' END) AS 客户类型
FROM
(SELECT a.顾客ID,a.购买得分 AS R,
b.购买频率得分 AS F,
c.购买金额得分 AS M
FROM r_value AS a
INNER JOIN
f_value AS b
on a.顾客ID=b.顾客ID
INNER JOIN
m_value AS c
ON a.顾客ID=c.顾客ID) AS c
数据分析
1.各类客户的占比及判断是否正常
SELECT a.客户类型,a.客户类型数量,b.总客户数,FORMAT(a.客户类型数量/b.总客户数,2) AS 占比 FROM
(SELECT 客户类型,COUNT(客户类型) AS 客户类型数量
FROM online_customer_group
GROUP BY 客户类型
ORDER BY 客户类型数量 DESC) AS a,
(SELECT COUNT(顾客ID) AS 总客户数 FROM online_customer_group) AS b
客户分类及其占比.png
对客户类别进行等级划分，划分为A、B、C级
A级客户：重要价值客户占比45% + 重要发展客户占比3% = 48%
B级客户：重要保持客户10% + 一般发展客户10% + 一般价值客户9% + 重要挽留客户2%=31%
C 级客户：流失客户14% + 一般保持客户7% = 21%
2.不同类型客户的购买时间对比
SELECT a.年份,a.月份,SUM(a.笔销售额) AS 各个类型顾客销售额,b.客户类型 FROM
(SELECT 年份,月份,单价,发票编号,顾客ID,笔销售额 FROM gift_retail_online
WHERE 单价>0 AND 笔销售额>0 AND 发票编号 NOT IN
(SELECT 发票编号 FROM gift_retail_online WHERE 发票编号 LIKE "C%")) AS a
INNER JOIN
(SELECT 顾客ID,客户类型 FROM online_customer_group) AS b
ON a.顾客ID=b.顾客ID
GROUP BY 客户类型,年份,月份
流失客户与一般保持客户的每月销售额情况对比.png
重要发展客户与一般发展客户的每月销售额情况对比.png
重要保持客户与重要挽留客户的每月销售额情况对比.png
重要价值客户与一般价值客户的每月销售额情况对比.png
重要价值客户与整体销售情况的每月销售额情况对比.png
i. 流失客户与一般保持客户同为C级客户，从图上看特征基本相同，消费金额都较低且时间只到消费截止到9月份，10-12月并没有交易记录。
ii. 重要发展客户与一般发展客户分别为A、B级客户，从图上明显看出区分两者的为消费金额，截至11月前的曲线基本相同，但在12月份出现相反的走向，由此可知重要发展客户会在12月份进行集中购买，而一般发展客户只在8-11月份内有少量的消费。
iii. 重要保持客户与重要挽留客户同为B级客户，从图上可看出重要挽留客户在1月份与6月份的消费金额存在大小峰值，重要保持客户的消费金额波动幅度并不大，两者的消费也都截止在9月份，10-12月并没有交易记录。
iv. 重要价值客户与一般价值客户分别为A、B级客户，该图有主次两个坐标轴，重要价值客户消费金额高且在11月出现峰值，一般价值客户的消费分时间段进行，分别为1-3月、5-6月、8-11月。
v. 重要价值客户对比整体销售情况，两个曲线走势基本相同，可以明显看出整体的消费的90%来自重要价值客户。
3.各类客户分别集中在哪些区域
SELECT b.所属洲,b.所处位置,b.中文名称 AS 国家,COUNT(a.num) AS 客户数量,c.客户类型 FROM
(SELECT 国家,顾客ID,COUNT(DISTINCT `顾客ID`) AS num FROM gift_retail_online
WHERE 单价>0 AND 笔销售额>0 AND 发票编号 NOT IN
(SELECT 发票编号 FROM gift_retail_online WHERE 发票编号 LIKE "C%")
GROUP BY 顾客ID) AS a
INNER JOIN
(SELECT 国家,中文名称,所属洲,所处位置 FROM online_country_state) AS b
ON a.国家=b.国家
INNER JOIN
(SELECT 顾客ID,客户类型 FROM online_customer_group) AS c
ON a.顾客ID=c.顾客ID
GROUP BY 客户类型,国家
不同州各个类别客户数量.png
不同州各个类别客户数量占比.png
i. 从上图可以看出客户基本上集中在欧洲，其它洲的客户极少，如非洲只存在1位重要价值客户，南美洲只存在1为重要保持客户
4.各类客户集中购买的产品
SELECT * FROM
(SELECT *,row_number() OVER(PARTITION BY 客户类型 ORDER BY 产品数量 DESC) AS ranking
FROM
(SELECT 产品编号,SUM(产品数量) AS 产品数量,客户类型 FROM
(SELECT 国家,顾客ID,产品编号,产品数量 FROM gift_retail_online
WHERE 单价>0 AND 笔销售额>0 AND 发票编号 NOT IN
(SELECT 发票编号 FROM gift_retail_online WHERE 发票编号 LIKE "C%")) AS a
INNER JOIN
(SELECT 顾客ID,客户类型 FROM online_customer_group) AS b
ON a.顾客ID=b.顾客ID
GROUP BY 客户类型,产品编号)AS d) AS c
WHERE ranking<=5
各类客户集中购买的产品.png
从图中可以看出，重要发展客户与重要挽留客户分别对产品编号为23843与23166有集中的偏好，其它客户对应购买量前5的产品之间区别并没有特别大
网友评论