电商销售数据分析案例(Oracle)
2021-04-01 本文已影响0人
蓝天花园
目录：
- 项目背景
- 项目目标
- 字段说明
- 数据预处理
- 数据探索
- 数据清洗
- 用户行为分析
- RFM模型
- RFM模型建立
- 用户价值分析
- 新老客户占比
- 每月新客占比
- 新老客户消费比
- 用户生命周期
- 用户复购率
- RFM模型
- 商品分析
- 商品基本信息
- 商品ABC分类
- 商品退货分析
一、项目背景
数据来源于Kaggle的电商数据集The UCI Machine Learning Repository，英国在线零售商在2010年12月1日到2011年12月9日的在线销售数据，该电商公司主要以销售各类礼品为主，多数客户都是批发商。
二、项目目标
使用Oracle 对数据进行处理与清洗，通过RFM模型、复购率、消费生命周期等对用户维度进行分析，利用ABC分类、退货率等维度展开剖析，结合Excel图表进行可视化展示，为精准营销与个性化服务提供支持。
三、字段说明
InvoiceNo: 发票编号，代表每笔交易的编号，如果编号开头为C，代表该笔交易取消
StockCode: 产品编号
Description: 产品名称
Quantity: 每笔交易的数量(含有负数，代表退货)
InvoiceDate: 交易时间
UnitPrice: 产品单价
CustomerID: 用户ID
Country: 产生交易国家
四、数据清洗/处理
1、选择字段
根据分析目的选择字段，数据集共8个字段，如果表格字段较多，视情根据分析目的的需要选择合适的字段。
2、删除重复值
CREATE TABLE new_ecommerce AS SELECT DISTINCT * FROM e_ecom;
创建备用表new_ecommerce，将旧表的数据去重添加进备用表。原有数据541909条， 去重后数据536641条，删除重复值5268条。
3、缺失值处理
检查缺失值
SELECT
SUM(CASE WHEN CustomerID IS NULL THEN 1 ELSE 0 END) "客户编号",
SUM(CASE WHEN InvoiceNo IS NULL THEN 1 ELSE 0 END )"发票编号",
SUM(CASE WHEN StockCode IS NULL THEN 1 ELSE 0 END )"产品编号",
SUM(CASE WHEN Quantity IS NULL THEN 1 ELSE 0 END )"发票编号",
SUM(CASE WHEN InvoiceDate IS NULL THEN 1 ELSE 0 END )"数量",
SUM(CASE WHEN UnitPrice IS NULL THEN 1 ELSE 0 END )"单价",
SUM(CASE WHEN Description IS NULL THEN 1 ELSE 0 END )"产品描述",
SUM(CASE WHEN Country IS NULL THEN 1 ELSE 0 END )"国家"
FROM new_ecommerce;
CustomerID存在缺失值135037条，Description出现缺失值1454条。数据都很大，不可能全部删除。Description产品描述不是项目分析，不用处理。
在实际工作中，像CustomerID客户ID缺失，首先找业务部门或者数据来源部门确认信息并且补上。本项目只有单一数据，无法找到相关人员确认，暂且把NULL值替换为0。
UPDATE new_ecommerce SET customerid = NVL(customerid,'0') ;
5、异常值处理
5.1检查日期是否在范围内（2010年12月1日到2011年12月9日）
SELECT MAX(InvoiceDate),MIN(InvoiceDate) FROM new_ecommerce
结果显示日期都在范围里，数据正常
5.2检查单价与销量数量，如出现负值与零值
SELECT MAX(UnitPrice)"最高价格",MIN(UnitPrice)"最低价格",MAX(Quantity)"最高销量",MIN(Quantity)"最低销量"
FROM new_ecommerce;
交易成功，销量不可能为负值或零值。如果销量为零或者负值情况，那么需要和业务/数据来源部门确认具体的原因。这里假设出现负值是客户退货情况。
DELETE FROM (SELECT * FROM new_ecommerce WHERE quantity <0 AND invoiceno NOT LIKE'C%');
检查发现交易销量小于0的发票编号大都是"C”开头的，有部分异常销量小于0但不是以"C"开头，这里做删除处理。
SELECT * FROM new_ecommerce WHERE unitprice = 0;
DELETE FROM (SELECT * FROM new_ecommerce WHERE unitprice = 0);
检查发现有单价为0的免费单，共计1174。暂且不分析免费单，直接删除免费单的数据。
DELETE FROM ( SELECT * FROM new_ecommerce WHERE unitprice < 0)
检查发现两笔坏账，单价都是负值，故把它删除。
6、一致化处理
根据分析目的，我们处理InvoiceDate日期数据。这里只做日期分析，不分析小时分钟，故转换为日期格式。
ALTER TABLE new_ecommerce ADD Date_id VARCHAR2(15)
UPDATE new_ecommerce SET DATE_id = to_char(invoicedate,'yyyy-mm-dd')
五、用户行为分析
根据分析目的，本次分析将采用RFM模型
在RFM模式中：
R：最近一次消费时间（最近一次消费到参考时间的间隔）
F：消费的频率(消费了多少次）
M：消费的金额 （总消费金额）
一般的分析型RFM强调以客户的行为来区分客户。
1、RFM值计算
-
R值计算
数据集的时间距离间隔较远，我们选取2011-12-09(最后一天)作为判断最近一次消费的距离日期
SELECT customerid,(to_date('2011-12-09','yyyy-mm-dd')-to_date(MAX(date_id),'yyyy-mm-dd')) R_time
FROM new_ecommerce
GROUP BY customerid
ORDER BY R_time
根据最近一次消费与客户数的分析结果显示最长的天数差是373天，最短0天；80%的客户在200天内都有交易记录，说明客户忠诚度不错。
-
F值计算
频率帮助我们了解客户进行了多少次消费。
SELECT customerid,count(DISTINCT invoiceno) F_times
FROM new_ecommerce
GROUP BY customerid;
分析显示，10次交易记录以内的客户占绝大部分，说明客户是很认可产品和服务。
- M值计算
SELECT customerid,sum(Quantity*UnitPrice) M_times
FROM new_ecommerce
GROUP BY customerid;
在2010年12月1日到2011年12月9日期间，交易金额主要集中在 1000英镑以内和1000-3000英镑这两个范围内。
-
RFM数据汇总
为方便后续数据处理，为RFM值创建视图
CREATE VIEW view_rfm AS
(select CustomerID,
(to_date('2011-12-09','yyyy-mm-dd')-to_date(MAX(date_id),'yyyy-mm-dd')) "天数差",
count(DISTINCT invoiceno) "消费次数",
sum(Quantity*UnitPrice) "消费金额"
from new_ecommerce
group by CustomerID)
-
RFM评分
现在需要给RFM评分，以便做客户分层处理，具体的评分需要根据业务进行处理。
SELECT CustomerID,天数差,消费次数,消费金额,(case when 天数差<=30 then 5
when 天数差 >30 and 天数差 <=90 then 4
when 天数差>90 and 天数差<=180 then 3
when 天数差>180 and 天数差<=365 then 2 else 1 END) "R评分",
(case when 消费次数<=10 then 1
when 消费次数 >10 and 消费次数 <=30 then 2
when 消费次数>30 and 天数差<=50 then 3
when 消费次数>50 and 消费次数<=80 then 4 else 5 END) "F评分",
(case when 消费金额<=1000 then 1
when 消费金额 >1000 and 消费金额 <=3000 then 2
when 消费金额>3000 and 消费金额<=5000 then 3
when 消费金额>5000 and 消费金额<=8000 then 4 else 5 END) "M评分"
from view_rfm;
-
客户分层阈值
RFM评分完成，保存视图为view_rfm1，用各自的平均值做为客户划分的阈值，再做最后的客户分层处理
select ROUND(avg(R评分),1) "R平均值",ROUND(avg(F评分),1) "F平均值",ROUND(avg(M评分),1) "M平均值"
FROM view_rfm1;
select CustomerID,
(case when R评分>3 then 1 else 0 END) "R值",
(case when F评分>1 then 1 else 0 END) "F值",
(case when M评分>1.1 then 1 else 0 END) "M值"
FROM view_rfm1;
-
客户分层
根据RFM阈值和客户评分分层表，对客户行为进行分层处理。
SELECT CustomerID,
(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) "客户分层"
from view_rfm2
分析发现，该电商平台总交易客户数4372位。交易客户中，一般发展客户（可以说是新客户）最多，占总数的34%，其次是一般挽留客户(流失客户)29%，重要发展客户22%，重要价值客户10%，重要挽留客户5%和重要挽回客户0.16%。
- 重要价值客户，RFM值都很高，可为其提供优质专属服务，提高其消费体验，增加客户忠诚度
- 重要发展客户，消费频率低，但是R和M值都很高，可以进行定向推广或提供价格优惠，提高其消费频次
- 重要保持客户，最近消费的时间较为久远，可能存在流失，但以前的消费金额与频率较高，需了解具体长时间未消费的原因，针对性提供对策
- 重要挽留客户，消费金额高，但长时间未进行消费且消费频率低，可主动联系或举行老客户召回活动，尽可能挽留
2.新老客户占比
每月新客数量及其占比
SELECT mon,SUM(new_1),SUM(total) mon_total,ROUND(SUM(new_1)/SUM(total),4) first_per
FROM(SELECT mon,customerid,MAX(is_new)new_1,COUNT(DISTINCT CustomerID ) total
FROM(SELECT a.*,
b.first_pur,
to_char(INVOICEDATE,'yyyy-mm') mon,
(CASE WHEN b.first_pur = to_char(INVOICEDATE,'yyyy-mm') THEN 1 ELSE 0 END) is_new
FROM new_ecommerce a
JOIN (
SELECT
CustomerID,
MIN(to_char(INVOICEDATE,'yyyy-mm')) first_pur
FROM new_ecommerce
GROUP BY CustomerID)b ON b.CustomerID = a.customerid)
GROUP BY mon,customerid
)
WHERE mon !='2010-12'
GROUP BY mon
每月的新老客户的销售数量与销售金额
SELECT mon,is_new,SUM(total_q),SUM(total_a)
FROM(SELECT mon,customerid,is_new,SUM(quantity) total_q,SUM(quantity*unitprice) total_a
FROM(SELECT a.*,
b.first_pur,
to_char(INVOICEDATE,'yyyy-mm') mon,
(CASE WHEN b.first_pur = to_char(INVOICEDATE,'yyyy-mm') THEN 1 ELSE 0 END) is_new
FROM new_ecommerce a
JOIN (
SELECT
CustomerID,
MIN(to_char(INVOICEDATE,'yyyy-mm')) first_pur
FROM new_ecommerce
GROUP BY CustomerID)b ON b.CustomerID = a.customerid)
GROUP BY mon,customerid,is_new
)
WHERE mon !='2010-12'
GROUP BY mon,is_new
- 新客户逐年下降明显，新客户的销售贡献比例也跟新客户数量走势基本匹配，销量的上升主要通过老客户的重复购买，证明商家的商品质量、服务质量以及对老客服务有一定保证，可以适度增加获客成本，设定新客获取通道和奖励营销活动促进新客以及老带新的转化率
3、用户生命周期分析
用户生命周期 = 最近一次购买时间 - 第一次购买时间
SELECT customerid,
MIN(date_id) first_time,
MAX(date_id) last_time,
(MAX(to_date(date_id,'yyyy-mm-dd')) - MIN(to_date(date_id,'yyyy-mm-dd'))) life_time
FROM New_Ecommerce
GROUP BY customerid
- 用户生命周期平均为195天，中位数为203天，分布情况呈双峰型，一方面较多客户消费生命周期较短，可能是前期活动吸引较多新客户，当前数据集不能体现其完整的消费生命周期，另一方面较多客户的消费生命周期较长，说明商家培养了一定的忠诚客户，商家的营销比较理想。
4、用户复购率分析
SELECT mon,ROUND(SUM(CASE WHEN user_mon_buy_times >1 THEN 1 ELSE 0 END)/COUNT(1),2) "复购率"
FROM(SELECT mon,COUNT(invoiceno) user_mon_buy_times
FROM( SELECT to_char(invoicedate,'yyyy-mm')mon,customerid,invoiceno
FROM NEW_ECOMMERCE
WHERE quantity >0
GROUP BY to_char(invoicedate,'yyyy-mm'),customerid,invoiceno)
GROUP BY mon,customerid)
GROUP BY mon
- 复购率比较有波动，结合前面的新老客分析，新客数量呈下降趋势，且当新客获取成本居高不下时，商家应花费成本与新用户成功的建立起联系，提高新客转化率。再加强培养老客户对品牌和商家的忠诚度，防止老客户流失，保持客户再次购买并且持续购买，才能有效增长长期利润。
5、商品分析
- 1、商品销量与单价
SELECT stockcode,
SUM(quantity) sales,
MAX(unitprice) price --销售价格存在折扣，取原价（最大值）
FROM new_ecommerce
WHERE quantity > 0 --销量存在退货
GROUP BY stockcode
ORDER BY sales;
- 2、商品ABC分类
- 计算商品销售额占总销量，按销量额降序排序
SELECT stockcode,sales,SUM(sales/total_sales) OVER(ORDER BY sales DESC) per_sales,row_number() OVER(ORDER BY sales DESC) RANK
FROM(SELECT DISTINCT stockcode,
SUM(quantity*unitprice) OVER(PARTITION BY stockcode) sales,
SUM(quantity*unitprice) OVER() total_sales
FROM new_ecommerce
WHERE quantity > 0)
- 划分ABC等级，累计销量占比在50%范围以内为A类产品，累计销量占比在50%~80%的范围为B类产品，累计销量占比在80%范围以上为C类产品
--创建商品销售额占比的视图，即是上面代码的视图 view_sales
SELECT stockcode,
sales,
per_sales,
CASE WHEN per_sales <0.5 THEN 'A'
WHEN per_sales <0.8 THEN 'B'
ELSE 'C' END CLASS
FROM view_sales
- 统计分类情况
SELECT CLASS,
SUM(sales) total_sales,
COUNT(CLASS) total_class
FROM(SELECT stockcode,
sales,
per_sales,
CASE WHEN per_sales <0.5 THEN 'A'
WHEN per_sales <0.8 THEN 'B'
ELSE 'C' END CLASS
FROM view_sales)
GROUP BY CLASS
- A类产品大约在221种左右，为公司畅销的商品，货物应陈列显眼主推位置，且需保持足够库存应对销售
- B类产品需注意库存水平，商品陈列位置位于A类产品后
- C类产品数量达到了3120种，陈列位置应靠后，但也需增加让利促销活动，加快促销周转速度，释放存货资金，后续调整库存水平
商品退货分析
- 计算商品退货率
SELECT stockcode,
COUNT(1) sales_times,
SUM(CASE WHEN quantity < 0 THEN 1 ELSE 0 END) return_times,
SUM(CASE WHEN quantity < 0 THEN 1 ELSE 0 END)/COUNT(1) return_rate
FROM NEW_ECOMMERCE
GROUP BY stockcode
ORDER BY sales_times DESC
- 月度退货金额
SELECT
to_char(invoicedate,'yyyy-mm') mon,
SUM(CASE WHEN Quantity < 0 THEN Quantity*Unitprice ELSE 0 END) return_amount,
SUM(CASE WHEN Quantity > 0 THEN Quantity*Unitprice ELSE 0 END) sales
FROM New_Ecommerce
GROUP BY to_char(invoicedate,'yyyy-mm')
结合ABC分类进行分析，选取退货率大于均值且为A级的商品（主要是综合上文提及的ABC分类和退货率计算，通过创建view的形式进行联结后筛选，创建退货率视图为view_return_rate，ABC分类视图为view_class），这里筛选出64个商品。
SELECT vc.stockcode,sales,sales_times,return_times,return_rate
FROM view_class vc
JOIN view_return_rate vr
ON vc.stockcode = vr.stockcode
WHERE CLASS = 'A' AND return_rate > (SELECT AVG(return_rate) FROM view_return_rate)
- 数据集的时间段有限，因此退货的商品购买时间可能发生在数据集时间以前，所以计算上存在缺陷，仅做分析参考
- 部分商品退货率较高，尤其是A类畅销产品，需要重点关注，查看是否与商品批次的质量与把控不严有关，与客户取得联系，了解退货原因，及时制定改进措施，防止损坏公司形象
