基于MySQL的电商零售订单数据分析

最新推荐文章于 2024-09-05 22:52:12 发布

分析鬼才

最新推荐文章于 2024-09-05 22:52:12 发布

阅读量1.9k

点赞数 4

分类专栏：电商订单数据分析文章标签：数据分析 mysql 数据库电子商务

本文链接：https://blog.csdn.net/weixin_51297533/article/details/117513368

版权

电商订单数据分析专栏收录该内容

3 篇文章 2 订阅

订阅专栏

1.提出问题

1.1.电子零售商面对的挑战和问题

近年来全球网民渗透率逐步提高，欧美地区网民渗透率远高于亚非地区。得益于互联网及全球网民规模的不断发展，全球零售总体规模保持增长，2019年全球零售总额为25万亿美元，其中网络零售额为3.5万亿美元，占比14%。

在2020年发布的《世界电子商务报告》中，全球有7个国家网购用户数量过亿，从市场规模来看，中国是全球最大、最活跃的互联网互用市场，英国是欧洲最大的电子商务市场，互联网普及达93%，而拉丁美洲是最受欢迎的电子商务新兴市场。全球使用移动端进行消费的用户占12%，中国使用移动段进行网购的消费者占88%，全球排名第一。

传统零售商争相往电子商务转型，因为有互联网的依托，网络消费者的消费行为变得有迹可循，产生了大量消费数据，如何利用这些数据挖掘出有用的商业信息是所有电子零售商都需要面对的挑战。

电子零售商面对的问题可能有：

（1）哪些产品最受欢迎，也即被访问的次数最多，购买的次数最多？
（2）哪些用户是最有价值的用户？也即在特定的时间范围内消费总额最高，这些高价值用户有什么样的用户特征？
（3）哪些用户是最忠诚的用户？也即复购率最高用户，如何提高这些用户的消费体验，增加消费额度？
（4）用户的消费习惯是什么？哪些产品是用户们喜欢一起购买的？或者有特定的购买时间顺序？
（5）促销对于哪些用户最有效？也即用户反应最活跃？促销策略能够考虑这些因素？

1.2.本次分析的业务问题及适用指标

本次分析要解决的核心业务问题是：从大量的电子零售交易数据中分析并找到价值用户，针对价值用户提供以消费者为中心的智能商业模式。

适用的指标有：

本次电子零售数据分析主要分为了两大方面：

（1）了解电子零售商的整体运营情况，通过分析计算运营的各项指标来了解目前经营的效率和经营趋势。
（2）使用RFM模型对用户进行分级，找出价值用户，从而进行经营策略管理，比如进一步挖掘最高价值用户的消费模式，提出个性化的销售服务提高价值用户体验，从而提高经营效率和利润。

2.理解数据

2.1. 数据来源

kaggle数据平台数据：https://link.zhihu.com/?target=https%3A//www.kaggle.com/jihyeseo/online-retail-data-set-from-uci-ml-repo

数据集来自一个在英国注册的在线电子零售公司，在2010年12月1日到2011年12月9日期间发生的网络交易数据。

2.2.本次分析选取的数据样本

本次分析选取一整年的交易数据，即从2010年12月1日到2011年12月1日期间的数据。原数据集储存在.csv格式的文件中，总共有541909条数据，8个字段。整个文件导入mysql，在清洗过程中才进行节选样本。

2.3.字段含义

字段含义:

InvoiceNo：发票编号。为每笔订单唯一分配的6位整数。若以字母'C'开头，则表示该订单被取消。
StockCode：产品代码。为每个产品唯一分配的编码。
Description：产品描述。
Quantity：数量。每笔订单中各产品分别的数量。
InvoiceDate：发票日期和时间。每笔订单发生的日期和时间。
UnitPrice：单价。单位产品价格，单位为英镑。
CustomerID：客户编号。为每个客户唯一分配的5位整数。
Country：国家。客户所在国家/地区的名称

3.数据清洗/处理

3.1.选择字段

根据分析目的选择字段，数据集共8个字段，在这里根据分析目选取 InvoiceNo、StockCode、Quantity、InvoiceDate、UnitPrice、CustomerID、Country等七个字段。

3.2.删除重复值

#创建表来存放不重复的数据
create table sales_facts like data;
insert into sales_facts select distinct * from data;

结果如下：

原有数据541909条，去重后数据536641条，删除重复值5268条。

3.3.缺失值处理

查询每个字段的数据数量，具体代码如下:

SELECT count(InvoiceNo),count(StockCode),count(Quantity)
,count(InvoiceDate),count(UnitPrice),count(CustomerID),count(Country),count(Description)
FROM sales_facts;

查询结果如下：

可以看出CustomerID存在缺失值135037条，Description出现缺失值1454条。数据都很大，不可能全部删除。由于字段Description是产品描述不是我们要分析的，故不予处理。对于字段CustomerID存在的缺失值，由于无法确定实际的情况，这里暂且把处于缺失状态的值替换为0，代码如下：

UPDATE sales_facts
set CustomerID=0
where CustomerID is null;

3.4.异常值处理

观察数据集字段，可能出现的异常值的字段有InvoiceDate（订单日期）、Quantity（销量）、UnitPrice（单价）等三个字段。

1.查看字段Quantity（销量）是否存在异常

SELECT MAX(UnitPrice)AS'最高价格',MIN(UnitPrice)AS'最低价格',MAX(Quantity)AS'最高销量',MIN(Quantity)AS'最低销量'
FROM sales_facts;

很明显可以发现销量和价格存在异常值。

由于最低销量为-1，我们并不清楚具体的含义，这里对于销量我们做一个初步的假设，销量为0 或负值的表示这一个订单已被取消。进一步来观察数据，找出销量为0或负值的数据，代码如下：

select * from sales_facts where Quantity<=0;

观察以上结果可以看出，交易数量为负值的发票编号都是以"C”开头的。在前面字段中已经说明，以“C”开头的订单号是取消订单，共有10587条取消订单数据。这里我们分析的目的为提高销售额，所以选择删除这些取消的订单。具体代码如下：

delete from sales_facts where  Quantity<=0;

2.查看字段UnitPrice（单价）是否存在异常

通过前面的查询可以看出最高价格为正常值，最低价格为负值，所以单价的异常主要是零值和负值。这里我们可以分析，单价为零的可能是赠品，与我们分析分销售额无关，所以选择删除，具体代码如下：

#查看价格为0的数据
select * from sales_facts where UnitPrice=0;
#删除价格为0的数据
delete from sales_facts where UnitPrice=0;

查看价格为负值的数据，代码如下：

select * from sales_facts where UnitPrice<0;

结果如下：

只有两条数据，这里选择删除，代码如下：

delete from sales_facts where UnitPrice<0;

3.5.格式一致化处理

需要一致化处理的字段只有InvoiceDate（订单日期），由于导入数据时导入的是字符串类型的，所以需要把InvoiceDate转换成可以处理的日期。具体代码如下：

alter table sales_facts add column order_date varchar(255) not null; 
update sales_facts set order_date=str_to_date(Invoicedate,'%m/%d/%Y %H:%i');
update sales_facts set order_date=DATE_FORMAT(order_date, '%Y-%m-%d');

4.数据分析——建立RFM模型

根据分析目的，本次分析采用RFM模型分析客户。RFM模型需要知道每位客户最近的一次消费时间（这里以2011/12/09为参考时间），时间范围内的消费频率和总交易金额。

创建一个临时表RFM来存放时间间隔、交易次数、交易金额。代码如下：

CREATE TEMPORARY TABLE RFM AS 
select CustomerID,InvoiceNo,Country,datediff('2011-12-09',MAX(order_date))as '最近一次时间间隔',
count(distinct InvoiceNo)as'交易次数',
sum(Quantity*UnitPrice)as'交易金额' 
from sales_facts 
group by CustomerID 
order by 最近一次时间间隔 desc,交易金额 desc ,交易次数 desc;

结果如下：

4.1. R：最近一次消费时间

最近一次消费到2011/12/09的间隔）等级划分

select a.`天数差`,count(CustomerID) as '人数'
from 
(select distinct CustomerID,datediff('2011-12-09',MAX(ORDER_date))as '天数差',count(distinct CustomerID)
from sales_facts
GROUP BY CustomerID) a
GROUP BY a.`天数差` 
order by a.`天数差` ;

最近一次消费间隔的人数分布：

由上图可以看出，消费时间间隔最长的是373天，最短0天；80%的客户在180天内都有交易记录，说明客户忠诚度不错。

以如下区间给R进行分级：

间隔在270~373天设为1
间隔在180~270天设为2
间隔在90~180天设为3
间隔在30~90天设为4
间隔在0-30天设为5

4.2. F：消费频率（F）等级划分

#这段代码是用from后面创建一个新表a，as省略
select a.交易次数,count(CustomerID) as '人数'
from 
(select  CustomerID,count(distinct InvoiceNo) '交易次数'
from sales_facts
GROUP BY CustomerID) a
GROUP BY a.交易次数 
order by a.交易次数;

分析显示，只有一次交易记录的客户有1499位，说明65.37%的客户是有复购行为，说明这些客户是很认可产品和服务等的。

由分布可知交易次数主要集中在10次以下，对F的等级划分如下所示：

交易次数为1次设为1
交易次数在2~3次设为2
交易次数在4~5次设为3
交易次数在6~8次设为4
交易次数在9次以上的设为5

4.3. M：消费金额（M）等级划分

查看消费金额大概的分布，代码如下：

SELECT CustomerID,SUM(Quantity*UnitPrice) as 消费金额
FROM sales_facts
GROUP BY CustomerID;

由上表可以看出，在2010年12月1日到2011年12月9日期间，消费金额主要集中在 0-4000英镑和4000-8000英镑这两个范围内，占到总顾客数的97%。对M的等级划分如下所示：

消费金额在1000英镑以下的设为1
消费金额在1000~2000英镑的设为2
消费金额在2000~4000英镑的设为3
消费金额在4000~8000英镑的设为4
消费金额在8000英镑以上的设为5

4.4. RFM评分

#创建临时表存放RFM评分
#用case,end创建一个案例，用when,and,then进行切分
CREATE TEMPORARY TABLE RFM评分 AS 
select *,
(case when 最近一次时间间隔 <=30 then 5
when 最近一次时间间隔 >30 and 最近一次时间间隔 <=90 then 4 
when 最近一次时间间隔 >90 and 最近一次时间间隔<=180 then 3  
when 最近一次时间间隔 >180 and 最近一次时间间隔<=270 then 2 else 1 END)as 'R评分',
(case when 交易次数<=1 then 1
when  交易次数 >1 and 交易次数 <=3 then 2 
when 交易次数>3 and 交易次数<=5 then 3 
when 交易次数>5 and 交易次数<=8 then 4 else 5 END)as 'F评分',
(case when 交易金额<=1000 then 1
when  交易金额 >1000 and 交易金额 <=2000 then 2 
when 交易金额>2000 and 交易金额<=4000 then 3  
when 交易金额>4000 and 交易金额<=8000 then 4 else 5 END)as 'M评分'
from RFM;
select * from RFM评分

结果如下：

4.5. 确定RFM值

给RFM评分完成，求得各自的平均值，把平均值做为客户划分的阈值，将RFM的值分别与阈值比较，确定RFM值。代码如下：

#计算平均值
select ROUND(avg(R评分),1)as 'R平均值',ROUND(avg(F评分),1)as 'F平均值',ROUND(avg(M评分),1)as 'M平均值'
from RFM评分;
#创建临时表RFM值
CREATE TEMPORARY TABLE RFM值 AS 
select *,case when R评分>3.8 then 1 else 0 end as 'R值',
case when F评分>2.3 then 1 else 0 end as 'F值',
case when M评分>1.7 then 1 else 0 end as 'M值'
from RFM评分;

select * from RFM值;

4.6. 确定客户类别

具体代码如下：

#创建临时表 客户分类 ，把所有顾客分类
CREATE TEMPORARY TABLE 客户分类 AS
select CustomerID,InvoiceNo,Country,
case when R值=1 and F值=1 and M值=1 then '重要价值客户'
     when R值=0 and F值=1 and M值=1 then '重要发展客户'
     when R值=1 and F值=0 and M值=1 then '重要保持客户'      
     when R值=0 and F值=0 and M值=1 then '重要挽留客户'
     when R值=1 and F值=1 and M值=0 then '一般价值客户'
     when R值=1 and F值=0 and M值=0 then '一般发展客户'
     when R值=0 and F值=1 and M值=0 then '一般保持客户'
 else '一般挽留客户' end as '客户类别'
from RFM值;
# 查看客户分类表
select * from 客户分类;

结果如下：