1、業務背景

某電商平臺有一份2019年的銷售數據，包含用戶、商品、銷量、訂單時間，是否退款等數據。為了給2020年的平臺運營提供業務指導，需對2019年的銷售情況進行分析。

數據為數據分析部門提取以及手動錄入，可能存在錯誤。

數據中下架商品id統一標識為PR000000。

平臺規定用戶下單后必須在30分鐘內支付，否則訂單自動取消。

2、理解數據

數據文件名為 order2019,包含一個 sheet 工作表，名為 data。

data 數據共 104558 行（含標題行），11 列（即11個字段）。

各字段信息如下：

A: id: 數據行索引，用于標記數據的行數，不重復B: orderID: 訂單編號，用戶購買訂單編號C: userID: 用戶編號，每個用戶都有自己的用戶識別編號D: goodsID: 商品編號，用戶購買商品識別編碼E: orderAmount: 訂單金額，用戶下單時的金額F: payment: 用戶支付金額，相對訂單金額，用戶支付金額可能會有折扣G: channelID: 用戶渠道來源H: platformTypr: 用戶購買平臺，如 APP，網頁，小程序等I: orderTime: 用戶下單時間，年月日以及時間J: payTime: 用戶支付時間，應晚于下單時間，一般 30 分鐘內必須支付，否則訂單自動取消K: chargeback: 是否退款，標記訂單是否退款3、分析思路4、提取數據4.1 備份數據

新建一個 sheet,命名為"備份"，然后將 data 中的數據全選，復制到"備份"工作表進行數據備份。此操作的目的是暴增后續數據分析期間，出現異常時可用備份數據繼續分析。

4.2 凍結首行

進入"data"工作表中，點擊菜單欄【視圖】【凍結窗格】【凍結首行】，用以固定標題行。字段比較多，Excel固定首行功能可以方便在滾動時，依然能查看數據字段名稱。

4.3 篩選

選中數據標題行，單擊菜單【開始】【排序和篩選】【篩選】

4.4 提取數據子集

本次業務分析范圍為 2019 年的訂單數據，需剔除訂單時間不在 2019/1/1 至 2019/12/31 之間的數、不符合邏輯的數據以及支付時間超過30min的數據。

篩選 orderTime 不在在 2019/1/1 至 2019/12/31 之間的數據，刪除。點擊 orderTime 的篩選按鈕，依次點擊【日期篩選】【自定義篩選】

在自定義篩選方式中，第一個選擇"在以下日起之后"，然后輸入"2019/12/31 23:59"；第二個選擇"在以下日起之前"，然后輸入"2019/1/1 00:00"，條件選擇"或"，然后點擊確定。

可以看到 261 條數據被篩選出來。這些數據訂單日起不在 2019 年，所以選中這些行，全部刪除。

點擊 A 列查看數據，還剩 102497 行。

刪除支付時間早于訂單時間的數據

購物流程是先下單，再支付。支付時間早于訂單時間的數據屬于錯誤數據，不符合業務邏輯，需要刪除。如何判斷是否有這個錯誤？需要新建一列輔助列。

在 L2 單元格輸入公式"=IF(J2<I2,'Y','N')"。

鼠標放至 L2 單元格右下角，當鼠標形狀變成十字形時，雙擊，即可向下填充所有數據，并且每個數據都自動按公式計算。

篩選 L 列為'Y'的數據，即為支付時間早于下單時間的錯誤數據。

可以發現有 5 條數據，選中這 5 條，然后刪除。

點擊 A 列查看數據，還剩 102495 行。

刪除支付時間間隔大于 30min 的數據

一般平臺為了確保商品不被占用，規定用戶從下單到支付時間不得小于 30min，超過此時間支付的，屬于異常數據，需刪除。

新建一列輔助列，判斷支付時間與下單時間間隔是否小于 30min.在 L2 單元格輸入公式"=(J2-I2)*24*60"，由于 J 列和 I 列單元格是帶時間的日期，兩個日期相減的單位是天數，需要轉換成分鐘數，1 天 24 小時，1 小時 60min，所以兩個日期差值要乘以 24 再乘以 60。雙擊 L2 單元格右下角下填充公式。

篩選 L 列大于 30 的數據。

可以發現有 937 條數據屬于支付時間超 30min 的數據。選中并刪除這 937 行數據。

刪除支付金額為負數的數據支付金額應為正數，需刪除金額為負數的數據。篩選F列小于0的數據。發現有6條數據。選中并刪除這6條數據。

選中A列，查看數總數，剩余103349條數據。

5、清洗數據5.1 查看數據

鼠標選中各個字段所在的列。可以在底部狀態欄查看數據計數，對于數值型數據，很能看到平均值、總和等。

5.2 清洗 orderID刪除 orderID 重復數據。選中orderID列，點擊菜單欄【數據】【刪除重復項】，選擇【擴展選定區域】，點擊【刪除重復項】

取消全選，然后選中orderID，點擊確定。

執行完成后，提示如下：

5.3 清洗 goodsIDPR000000 屬于下架商品，需刪除。篩選goodsID列為PR000000的數據，選中相關行，刪除。

5.4 清洗 channelID選中G列，發現數據計數比其他列少，說明有空值存在。對于空值，可選用眾數填充。新建一個sheet并命名為數據透視表。查找channelID眾數，選中channelID列，點擊菜單欄【插入】【數據透視表】

放置數據透視表的位置選擇現有工作表，點擊向上箭頭符號，選擇【數據透視表】sheet,點擊A1單元格，點擊確定。

將chennelID字段拖放到行、值區域，自動生產一個透視表。

在數據透視表中B列任意單元格右鍵，點擊【排序】【降序】

得到一個降序排列的表，第一個【渠道-0896】就是出現次數最多的眾數。

回到data表中，選中chanelID列，按住ctrl G，彈出定位框，點擊【定位條件】。

選擇【空值】，點擊【確定】，這樣就定位到chanelID為空值的所有單元格。

直接輸入"渠道-0896"，然后按ctrl Enter，這樣所有的空值都會填充"渠道-0896"

5.5 清洗 platformtype利用數據透視表來查看 platformtype 的類別數。發現有些相同的數值，由于空格原因被分為兩類。

清除空格。選中H列，點擊菜單欄【開始】【查找與替換】【替換】

查找內容輸入快輸入一個空格，替換為輸入框不輸入任何字符。然后點擊【全部替換】

替換完成。提示116630處替換。

再次用數據透視表查看類別數，觀察已無問題。5.6 清洗 payment新建計算折扣字段discount.在G2單元格輸入公式：=F2/E2，然后向下填充。選中G列，點擊菜單欄【數據】【數據分析】，選擇【描述統計】，點擊確定。

查看折扣的描述統計平均值為1.11029，說明有折扣大于1的情況，這類數據屬于錯誤數據，需進行修正。

篩選G列大于1的數據，發現有2004條數據。

這2004條數據對應的支付金額payment大于訂單金額orderamount。我們使用正常數據（即折扣小于等于1）中的折扣均值乘以訂單金額作為填充值。篩選G列<1 的數據，然后查看Excel狀態欄顯示的平均值為0.92.

新建一列payment作為payment修正值。在G2單元格輸入公式：=IF(H2>1.E2*0.92,F2)。公式表示如果折扣大于1，name支付金額等于訂單金額乘以平均折扣0.92，折扣小于1則按原支付金額填充。

填充完成后，將帶公式的數據粘貼為數值，然后刪除原有的payment列。

6、分析數據6.1 總體概覽

銷售 GMV：所有訂單金額（含退款產生的訂單金額）

成交總額：用戶支付金額（含退款部分）

實際成交額：平臺實際銷售收入（不含退款支付金額）

訂單數量：orderID去重后的數量

退貨率：退貨訂單數量/總訂單數量

用戶數：userID去重后的數量

6.2 銷售情況創建訂單月份字段，輸入公式=month(J2),然后向下填充。

數據透視每月 GMV，然后復制粘貼為數值，并除以10000將單位轉換為萬元數據透視每月實際付款，然后復制粘貼為數值，并除以10000將單位轉換為萬元數據透視每月不含退單銷售額，然后復制粘貼為數值，并除以10000將單位轉換為萬元

將數據匯總粘貼為如下表格，然后插入折線圖。

分析結論從每月銷售折線圖可以看出，2019年2月份全年銷量最低，2月份正值春節，銷售額可能受物流影響2019年2、3、4月銷量逐漸上升，5月份增長較快，6、7、8月份保持平穩，9、10月略有下降。說明下半年下銷量比上半年好，在5月、11月可加大促銷力度。

6.3 渠道分析利用數據透視對渠道 ID、銷量、銷售額進行透視分析分析結論渠道-0896產生的銷量和銷售額最高，需重點發展該渠道業務各渠道銷售額與各渠道銷量呈線性關系

6.4 下單平臺分析利用數據透視對用戶下單平臺銷量進行分析，繪制餅圖分析結論用戶下單主要通過APP和WechatMP,分別占50.14%和41.19%，通過WEB、WechatShop和Wap平臺下單相對較少，由此可見用戶主要通過移動端app和微信小程序進行下單購買商品。

6.5 用戶行為創建星期字段，輸入公式=CHOOSE(WEEKDAY(J2,2),"周一","周二","周三","周四","周五","周六","周日"),然后向下填充。利用數據透視分析周一至周日訂單量變化，并繪制柱形圖。

創建時間段字段，輸入公式=hour(J2)，然后向下填充。利用數據透視分析一天中24小時訂單量變化，并繪制柱形圖。

分析結論周日、周一、周二訂單量相對較高、周三至周五訂單量有下降，說明客戶偏向于在周日、周一、周二購物每天上午訂單量主鍵增加，到中午13點達到最大，下午銷量主鍵下降，到下班之后，銷量又主鍵上升，在8點鐘再次達到高峰，之后銷量逐漸下降。說明用戶偏向于總中午和晚上睡前購物，可在此時間進行業務推送。

6.6 復購率、回購率客戶復購率：同一時間段內（這里為一個月）購物超過兩次的用戶利用數據透視表，行區域拖入month、orderTime、userID,值區域拖入userID，計算方式設為計數。

生成如下透視表格：

復制粘貼為數值，然后新建輔助列，用于統計是否產生復購。I列大于1則產生復購，否則沒有復購。

加上輔助再次進行數據透視，得到每個月下單總人數和復購人數，新建一列計算復購率。

繪制每月復購率折線圖

客戶回購率：上一個月有購買的客戶在本月也購買的比例利用數據透視表，行區域拖入userID，列區域拖入月份，值區域拖入userID，計算方式設為計數。

生成每個用戶每個月產生訂單數的矩陣表

復制粘貼一份，并對每一個月新建一列計算驗證是否產生回購。計算是否產生回購的公式(以2月為例）：=IF(AND((AK6<>0),(AL6<>0)),1,0)。產生回購則標記為1，否則為0。

在每一列底部統計每個月的下單總人數和回購人數。

將每月購買人數和下月回購人數制成下表，計算每月回購率，并繪制折線圖分析結論：復購率整體偏低，說明客戶大多數為一次性購買客戶上半年回購客戶呈上漲趨勢，下半年回購客戶基本保持穩定。

6.7 客戶 RFM 模型RFM的含義：R（Recency）最近一次消費時間：表示用戶最近一次消費距離現在的時間。消費時間越近的客戶價值越大。1年前消費過的用戶肯定沒有1周前消費過的用戶價值大。F（Frequency）消費頻率：消費頻率是指用戶在統計周期內購買商品的次數，經常購買的用戶也就是熟客，價值肯定比偶爾來一次的客戶價值大。M（Monetary）消費金額：消費金額是指用戶在統計周期內消費的總金額，體現了消費者為企業創利的多少，自然是消費越多的用戶價值越大。數據透視表生成每個用戶最近一次消費時間、消費次數、消費金額。userID拖入行區域，payTime、userID、payment拖入值區域，分別設置計數方式為最大值、計數、求和。

復制粘貼為數值，新建一列為最近消費時間差，輸入公式：=DATEDIF(G4,$J$2,"D")，計算客戶最近一次消費距離2020年1月日的時間差。

選中H、I、J列，點擊【數據分析】查看三列描述統計的均值

建立RFM客戶類別識別碼

新建輔助列K L M列，對R、F、M進行均值比較，N列生產RFM識別碼，O列匹配客戶分類。

數據透視生成客戶類型與銷量、銷售額的表格

繪制RFM 銷量/銷售額圖表

分析結論

當客戶價值分類完成，需要針對不同層級的客戶實施不同的運營策略。

（1）重要價值客戶：維持現狀，重點維護

最近消費時間近，消費頻次和消費金額都很高；重點維護對象，VIP大用戶組織建設是必要的，權益專享、定期福利等；

（2）重要發展客戶：提升頻次，重點深耕

最近消費時間較近，消費金額高，但頻次不高；屬于忠誠度不高用戶。著力讓用戶在平臺上活躍，優化產品和服務，幫助用戶提升頻次。

（3）重要保持客戶：用戶回流，重點挽回

最近消費時間較遠，但消費頻次和金額都很高；說明這是個一段時間沒來的忠實客戶，定期的EDM、PUSH、短信，主動和用戶保持聯系和互動；

（4）重要挽留客戶：提前預警，重點召回

最近消費時間較遠，消費頻次不高，消費金額高；屬于重點的預流失用戶，提前做好預流失預警和策略觸達。

（5）一般價值用戶：潛力用戶，刺激復購

最近消費時間較近，消費頻次高，就是消費金額低；屬于重點潛力用戶，可以發放大額卡券，引導此類用戶不斷增加投資。

（6）一般發展用戶：多為新用戶，挖掘需求

最近消費時間較近，消費頻次低，消費金額也低；可能是新用戶，最近投資過，需要客服回復工作加強，以及福利及時提醒。

（7）一般保持用戶：流失召回

最近消費時間較遠，消費頻次高，消費金額低；屬于流失用戶，可能前期在普通很活躍，后期情感受挫，或是產品、服務、獎勵力度達不到心里預期，需要做好利益與情感雙重觸達。

（8）一般挽留用戶：可放棄治療

最近消費時間，消費頻次與消費金額都很低，此類用戶流失已久，較難挽回，預算受限的情況下，可以放棄此類用戶。