目錄：

• 項(xiàng)目背景
• 項(xiàng)目目標(biāo)
• 字段說明
• 數(shù)據(jù)預(yù)處理
  • 數(shù)據(jù)探索
  • 數(shù)據(jù)清洗
• 用戶行為分析
  • RFM模型
    • RFM模型建立
    • 用戶價(jià)值分析
  • 新老客戶占比
    • 每月新客占比
    • 新老客戶消費(fèi)比
  • 用戶生命周期
  • 用戶復(fù)購率
• 商品分析
  • 商品基本信息
  • 商品ABC分類
  • 商品退貨分析

一盈罐、項(xiàng)目背景

數(shù)據(jù)來源于Kaggle的電商數(shù)據(jù)集The UCI Machine Learning Repository僵朗，英國在線零售商在2010年12月1日到2011年12月9日的在線銷售數(shù)據(jù)夺克，該電商公司主要以銷售各類禮品為主熙宇，多數(shù)客戶都是批發(fā)商逊谋。

二座哩、項(xiàng)目目標(biāo)

使用Oracle 對(duì)數(shù)據(jù)進(jìn)行處理與清洗箍铭，通過RFM模型螟炫、復(fù)購率、消費(fèi)生命周期等對(duì)用戶維度進(jìn)行分析仅醇，利用ABC分類冗美、退貨率等維度展開剖析甲抖，結(jié)合Excel圖表進(jìn)行可視化展示，為精準(zhǔn)營銷與個(gè)性化服務(wù)提供支持挫剑。

三艾岂、字段說明

InvoiceNo: 發(fā)票編號(hào)氓辣，代表每筆交易的編號(hào)体斩，如果編號(hào)開頭為C想幻，代表該筆交易取消
StockCode: 產(chǎn)品編號(hào)
Description: 產(chǎn)品名稱
Quantity: 每筆交易的數(shù)量(含有負(fù)數(shù)幔崖，代表退貨)
InvoiceDate: 交易時(shí)間
UnitPrice: 產(chǎn)品單價(jià)
CustomerID: 用戶ID
Country: 產(chǎn)生交易國家

四渠退、數(shù)據(jù)清洗/處理

1恰梢、選擇字段

根據(jù)分析目的選擇字段，數(shù)據(jù)集共8個(gè)字段猜惋，如果表格字段較多，視情根據(jù)分析目的的需要選擇合適的字段摹察。

2恩掷、刪除重復(fù)值

CREATE TABLE new_ecommerce AS SELECT DISTINCT * FROM e_ecom;

創(chuàng)建備用表new_ecommerce，將舊表的數(shù)據(jù)去重添加進(jìn)備用表供嚎。原有數(shù)據(jù)541909條黄娘， 去重后數(shù)據(jù)536641條，刪除重復(fù)值5268條克滴。

3逼争、缺失值處理

檢查缺失值

SELECT 
SUM(CASE WHEN CustomerID IS NULL THEN 1 ELSE 0 END)  "客戶編號(hào)",
SUM(CASE WHEN InvoiceNo IS NULL THEN 1 ELSE 0 END )"發(fā)票編號(hào)",
SUM(CASE WHEN StockCode IS NULL THEN 1 ELSE 0 END )"產(chǎn)品編號(hào)",
SUM(CASE WHEN Quantity IS NULL THEN 1 ELSE 0 END )"發(fā)票編號(hào)",
SUM(CASE WHEN InvoiceDate IS NULL THEN 1 ELSE 0 END )"數(shù)量",
SUM(CASE WHEN UnitPrice IS NULL THEN 1 ELSE 0 END )"單價(jià)",
SUM(CASE WHEN Description IS NULL THEN 1 ELSE 0 END )"產(chǎn)品描述",
SUM(CASE WHEN Country IS NULL THEN 1 ELSE 0 END )"國家"
FROM new_ecommerce;

CustomerID存在缺失值135037條，Description出現(xiàn)缺失值1454條劝赔。數(shù)據(jù)都很大誓焦，不可能全部刪除。Description產(chǎn)品描述不是項(xiàng)目分析望忆，不用處理罩阵。

在實(shí)際工作中，像CustomerID客戶ID缺失启摄，首先找業(yè)務(wù)部門或者數(shù)據(jù)來源部門確認(rèn)信息并且補(bǔ)上稿壁。本項(xiàng)目只有單一數(shù)據(jù)，無法找到相關(guān)人員確認(rèn)歉备，暫且把NULL值替換為0傅是。

UPDATE new_ecommerce SET customerid = NVL(customerid,'0') ;

5、異常值處理

5.1檢查日期是否在范圍內(nèi)（2010年12月1日到2011年12月9日）

SELECT MAX(InvoiceDate),MIN(InvoiceDate) FROM new_ecommerce

SELECT MAX(UnitPrice)"最高價(jià)格",MIN(UnitPrice)"最低價(jià)格",MAX(Quantity)"最高銷量",MIN(Quantity)"最低銷量"
FROM new_ecommerce;

交易成功，銷量不可能為負(fù)值或零值龟再。如果銷量為零或者負(fù)值情況书闸，那么需要和業(yè)務(wù)/數(shù)據(jù)來源部門確認(rèn)具體的原因。這里假設(shè)出現(xiàn)負(fù)值是客戶退貨情況利凑。

DELETE FROM (SELECT * FROM new_ecommerce WHERE quantity <0 AND invoiceno NOT LIKE'C%');

檢查發(fā)現(xiàn)交易銷量小于0的發(fā)票編號(hào)大都是"C”開頭的浆劲，有部分異常銷量小于0但不是以"C"開頭，這里做刪除處理哀澈。

SELECT * FROM  new_ecommerce WHERE unitprice = 0;
DELETE FROM (SELECT * FROM new_ecommerce WHERE unitprice = 0);

檢查發(fā)現(xiàn)有單價(jià)為0的免費(fèi)單牌借，共計(jì)1174。暫且不分析免費(fèi)單割按，直接刪除免費(fèi)單的數(shù)據(jù)膨报。

DELETE FROM ( SELECT * FROM new_ecommerce WHERE unitprice < 0)

檢查發(fā)現(xiàn)兩筆壞賬，單價(jià)都是負(fù)值，故把它刪除现柠。

6院领、一致化處理

根據(jù)分析目的，我們處理InvoiceDate日期數(shù)據(jù)晒旅。這里只做日期分析栅盲，不分析小時(shí)分鐘，故轉(zhuǎn)換為日期格式废恋。

ALTER TABLE new_ecommerce ADD Date_id VARCHAR2(15)
UPDATE new_ecommerce SET DATE_id = to_char(invoicedate,'yyyy-mm-dd')

五谈秫、用戶行為分析

根據(jù)分析目的，本次分析將采用RFM模型
在RFM模式中：
R：最近一次消費(fèi)時(shí)間（最近一次消費(fèi)到參考時(shí)間的間隔）
F：消費(fèi)的頻率(消費(fèi)了多少次）
M：消費(fèi)的金額 （總消費(fèi)金額）
一般的分析型RFM強(qiáng)調(diào)以客戶的行為來區(qū)分客戶鱼鼓。

1拟烫、RFM值計(jì)算

• R值計(jì)算
  數(shù)據(jù)集的時(shí)間距離間隔較遠(yuǎn)，我們選取2011-12-09(最后一天)作為判斷最近一次消費(fèi)的距離日期

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

根據(jù)最近一次消費(fèi)與客戶數(shù)的分析結(jié)果顯示最長(zhǎng)的天數(shù)差是373天迄本，最短0天硕淑；80%的客戶在200天內(nèi)都有交易記錄，說明客戶忠誠度不錯(cuò)嘉赎。

• F值計(jì)算
  頻率幫助我們了解客戶進(jìn)行了多少次消費(fèi)置媳。

SELECT customerid,count(DISTINCT invoiceno) F_times
FROM new_ecommerce
GROUP BY customerid;

分析顯示，10次交易記錄以內(nèi)的客戶占絕大部分公条，說明客戶是很認(rèn)可產(chǎn)品和服務(wù)拇囊。

• M值計(jì)算

SELECT customerid,sum(Quantity*UnitPrice) M_times
FROM new_ecommerce
GROUP BY customerid;

在2010年12月1日到2011年12月9日期間，交易金額主要集中在 1000英鎊以內(nèi)和1000-3000英鎊這兩個(gè)范圍內(nèi)靶橱。

• RFM數(shù)據(jù)匯總
  為方便后續(xù)數(shù)據(jù)處理寥袭，為RFM值創(chuàng)建視圖

CREATE VIEW view_rfm AS
(select CustomerID,
       (to_date('2011-12-09','yyyy-mm-dd')-to_date(MAX(date_id),'yyyy-mm-dd')) "天數(shù)差",
       count(DISTINCT invoiceno) "消費(fèi)次數(shù)",
       sum(Quantity*UnitPrice) "消費(fèi)金額" 
from new_ecommerce 
group by CustomerID)

• RFM評(píng)分
  現(xiàn)在需要給RFM評(píng)分，以便做客戶分層處理关霸，具體的評(píng)分需要根據(jù)業(yè)務(wù)進(jìn)行處理传黄。

SELECT CustomerID,天數(shù)差,消費(fèi)次數(shù),消費(fèi)金額,(case when 天數(shù)差<=30 then 5
when  天數(shù)差 >30 and 天數(shù)差 <=90 then 4 
when 天數(shù)差>90 and 天數(shù)差<=180 then 3  
when 天數(shù)差>180 and 天數(shù)差<=365 then 2 else 1 END) "R評(píng)分",
(case when 消費(fèi)次數(shù)<=10 then 1
when  消費(fèi)次數(shù) >10 and 消費(fèi)次數(shù) <=30 then 2 
when 消費(fèi)次數(shù)>30 and 天數(shù)差<=50 then 3 
when 消費(fèi)次數(shù)>50 and 消費(fèi)次數(shù)<=80 then 4 else 5 END) "F評(píng)分",
(case when 消費(fèi)金額<=1000 then 1
when  消費(fèi)金額 >1000 and 消費(fèi)金額 <=3000 then 2 
when 消費(fèi)金額>3000 and 消費(fèi)金額<=5000 then 3  
when 消費(fèi)金額>5000 and 消費(fèi)金額<=8000 then 4 else 5 END) "M評(píng)分"
from view_rfm;

• 客戶分層閾值
  RFM評(píng)分完成，保存視圖為view_rfm1队寇，用各自的平均值做為客戶劃分的閾值膘掰，再做最后的客戶分層處理

select ROUND(avg(R評(píng)分),1) "R平均值",ROUND(avg(F評(píng)分),1) "F平均值",ROUND(avg(M評(píng)分),1) "M平均值"
FROM view_rfm1;

select CustomerID,
(case when R評(píng)分>3 then 1 else 0 END)  "R值",
(case when F評(píng)分>1 then 1 else 0 END)  "F值",
(case when M評(píng)分>1.1 then 1 else 0 END)  "M值"
FROM view_rfm1;

• 客戶分層
  根據(jù)RFM閾值和客戶評(píng)分分層表，對(duì)客戶行為進(jìn)行分層處理佳遣。
  

SELECT CustomerID,
(case when R值=1 and F值=1 and M值=1 then '重要價(jià)值客戶'
  when R值=0 and F值=1 and M值=1 then '重要喚回客戶'
  when R值=1 and F值=0 and M值=1 then '重要發(fā)展客戶'      
  when R值=0 and F值=0 and M值=1 then '重要挽留客戶'
  when R值=1 and F值=1 and M值=0 then '一般價(jià)值客戶'
  when R值=1 and F值=0 and M值=0 then '一般發(fā)展客戶'
  when R值=0 and F值=1 and M值=0 then '一般保持客戶' 
ELSE '一般挽留客戶' END) "客戶分層"
from view_rfm2

分析發(fā)現(xiàn)炭序，該電商平臺(tái)總交易客戶數(shù)4372位。交易客戶中苍日，一般發(fā)展客戶（可以說是新客戶）最多，占總數(shù)的34%窗声，其次是一般挽留客戶(流失客戶)29%相恃，重要發(fā)展客戶22%，重要價(jià)值客戶10%笨觅，重要挽留客戶5%和重要挽回客戶0.16%拦耐。

• 重要價(jià)值客戶耕腾，RFM值都很高，可為其提供優(yōu)質(zhì)專屬服務(wù)杀糯，提高其消費(fèi)體驗(yàn)扫俺，增加客戶忠誠度
• 重要發(fā)展客戶，消費(fèi)頻率低固翰，但是R和M值都很高狼纬，可以進(jìn)行定向推廣或提供價(jià)格優(yōu)惠，提高其消費(fèi)頻次
• 重要保持客戶骂际，最近消費(fèi)的時(shí)間較為久遠(yuǎn)疗琉，可能存在流失，但以前的消費(fèi)金額與頻率較高歉铝，需了解具體長(zhǎng)時(shí)間未消費(fèi)的原因盈简，針對(duì)性提供對(duì)策
• 重要挽留客戶，消費(fèi)金額高太示，但長(zhǎng)時(shí)間未進(jìn)行消費(fèi)且消費(fèi)頻率低柠贤，可主動(dòng)聯(lián)系或舉行老客戶召回活動(dòng)，盡可能挽留

2.新老客戶占比

每月新客數(shù)量及其占比

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

每月的新老客戶的銷售數(shù)量與銷售金額

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

• 新客戶逐年下降明顯类缤，新客戶的銷售貢獻(xiàn)比例也跟新客戶數(shù)量走勢(shì)基本匹配臼勉，銷量的上升主要通過老客戶的重復(fù)購買，證明商家的商品質(zhì)量呀非、服務(wù)質(zhì)量以及對(duì)老客服務(wù)有一定保證坚俗，可以適度增加獲客成本，設(shè)定新客獲取通道和獎(jiǎng)勵(lì)營銷活動(dòng)促進(jìn)新客以及老帶新的轉(zhuǎn)化率

3岸裙、用戶生命周期分析

用戶生命周期 = 最近一次購買時(shí)間 - 第一次購買時(shí)間

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天猖败，中位數(shù)為203天，分布情況呈雙峰型降允，一方面較多客戶消費(fèi)生命周期較短恩闻，可能是前期活動(dòng)吸引較多新客戶，當(dāng)前數(shù)據(jù)集不能體現(xiàn)其完整的消費(fèi)生命周期剧董，另一方面較多客戶的消費(fèi)生命周期較長(zhǎng)幢尚，說明商家培養(yǎng)了一定的忠誠客戶，商家的營銷比較理想翅楼。

4尉剩、用戶復(fù)購率分析

SELECT mon,ROUND(SUM(CASE WHEN user_mon_buy_times >1 THEN 1 ELSE 0 END)/COUNT(1),2) "復(fù)購率"
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

• 復(fù)購率比較有波動(dòng)，結(jié)合前面的新老客分析毅臊，新客數(shù)量呈下降趨勢(shì)理茎，且當(dāng)新客獲取成本居高不下時(shí)，商家應(yīng)花費(fèi)成本與新用戶成功的建立起聯(lián)系，提高新客轉(zhuǎn)化率皂林。再加強(qiáng)培養(yǎng)老客戶對(duì)品牌和商家的忠誠度朗鸠，防止老客戶流失，保持客戶再次購買并且持續(xù)購買础倍，才能有效增長(zhǎng)長(zhǎng)期利潤烛占。

5、商品分析

• 1沟启、商品銷量與單價(jià)

SELECT stockcode,
       SUM(quantity) sales,
       MAX(unitprice) price --銷售價(jià)格存在折扣忆家，取原價(jià)（最大值）
FROM new_ecommerce
WHERE quantity > 0  --銷量存在退貨
GROUP BY stockcode
ORDER BY sales; 

• 2、商品ABC分類
• 計(jì)算商品銷售額占總銷量美浦，按銷量額降序排序

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等級(jí)弦赖，累計(jì)銷量占比在50%范圍以內(nèi)為A類產(chǎn)品，累計(jì)銷量占比在50%~80%的范圍為B類產(chǎn)品浦辨，累計(jì)銷量占比在80%范圍以上為C類產(chǎn)品

--創(chuàng)建商品銷售額占比的視圖蹬竖，即是上面代碼的視圖 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   

• 統(tǒng)計(jì)分類情況

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類產(chǎn)品大約在221種左右，為公司暢銷的商品流酬，貨物應(yīng)陳列顯眼主推位置币厕，且需保持足夠庫存應(yīng)對(duì)銷售
• B類產(chǎn)品需注意庫存水平，商品陳列位置位于A類產(chǎn)品后
• C類產(chǎn)品數(shù)量達(dá)到了3120種芽腾，陳列位置應(yīng)靠后旦装，但也需增加讓利促銷活動(dòng)，加快促銷周轉(zhuǎn)速度摊滔，釋放存貨資金阴绢，后續(xù)調(diào)整庫存水平

商品退貨分析

• 計(jì)算商品退貨率

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') 

結(jié)合ABC分類進(jìn)行分析，選取退貨率大于均值且為A級(jí)的商品（主要是綜合上文提及的ABC分類和退貨率計(jì)算艰躺，通過創(chuàng)建view的形式進(jìn)行聯(lián)結(jié)后篩選呻袭，創(chuàng)建退貨率視圖為view_return_rate，ABC分類視圖為view_class）腺兴，這里篩選出64個(gè)商品左电。

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)  

• 數(shù)據(jù)集的時(shí)間段有限，因此退貨的商品購買時(shí)間可能發(fā)生在數(shù)據(jù)集時(shí)間以前页响，所以計(jì)算上存在缺陷篓足，僅做分析參考
• 部分商品退貨率較高，尤其是A類暢銷產(chǎn)品闰蚕，需要重點(diǎn)關(guān)注栈拖，查看是否與商品批次的質(zhì)量與把控不嚴(yán)有關(guān)，與客戶取得聯(lián)系没陡，了解退貨原因辱魁，及時(shí)制定改進(jìn)措施烟瞧，防止損壞公司形象