目录：

• 项目背景
• 项目目标
• 字段说明
• 数据预处理
  • 数据探索
  • 数据清洗
• 用户行为分析
  • 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

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类畅销产品，需要重点关注，查看是否与商品批次的质量与把控不严有关，与客户取得联系，了解退货原因，及时制定改进措施，防止损坏公司形象