某電商平臺(tái)2019年銷售數(shù)據(jù)分析

時(shí)間：2023-03-15 23:52:01 | 來源：電子商務(wù)

時(shí)間：2023-03-15 23:52:01 來源：電子商務(wù)

1、業(yè)務(wù)背景

某電商平臺(tái)有一份2019年的銷售數(shù)據(jù)，包含用戶、商品、銷量、訂單時(shí)間，是否退款等數(shù)據(jù)。為了給2020年的平臺(tái)運(yùn)營提供業(yè)務(wù)指導(dǎo)，需對(duì)2019年的銷售情況進(jìn)行分析。

數(shù)據(jù)為數(shù)據(jù)分析部門提取以及手動(dòng)錄入，可能存在錯(cuò)誤。

數(shù)據(jù)中下架商品id統(tǒng)一標(biāo)識(shí)為PR000000。

平臺(tái)規(guī)定用戶下單后必須在30分鐘內(nèi)支付，否則訂單自動(dòng)取消。

2、理解數(shù)據(jù)

數(shù)據(jù)文件名為 order2019,包含一個(gè) sheet 工作表，名為 data。

data 數(shù)據(jù)共 104558 行（含標(biāo)題行），11 列（即11個(gè)字段）。

各字段信息如下：

• A: id: 數(shù)據(jù)行索引，用于標(biāo)記數(shù)據(jù)的行數(shù)，不重復(fù)
• B: orderID: 訂單編號(hào)，用戶購買訂單編號(hào)
• C: userID: 用戶編號(hào)，每個(gè)用戶都有自己的用戶識(shí)別編號(hào)
• D: goodsID: 商品編號(hào)，用戶購買商品識(shí)別編碼
• E: orderAmount: 訂單金額，用戶下單時(shí)的金額
• F: payment: 用戶支付金額，相對(duì)訂單金額，用戶支付金額可能會(huì)有折扣
• G: channelID: 用戶渠道來源
• H: platformTypr: 用戶購買平臺(tái)，如 APP，網(wǎng)頁，小程序等
• I: orderTime: 用戶下單時(shí)間，年月日以及時(shí)間
• J: payTime: 用戶支付時(shí)間，應(yīng)晚于下單時(shí)間，一般 30 分鐘內(nèi)必須支付，否則訂單自動(dòng)取消
• K: chargeback: 是否退款，標(biāo)記訂單是否退款

3、分析思路

4、提取數(shù)據(jù)

4.1 備份數(shù)據(jù)

新建一個(gè) sheet,命名為"備份"，然后將 data 中的數(shù)據(jù)全選，復(fù)制到"備份"工作表進(jìn)行數(shù)據(jù)備份。此操作的目的是暴增后續(xù)數(shù)據(jù)分析期間，出現(xiàn)異常時(shí)可用備份數(shù)據(jù)繼續(xù)分析。

4.2 凍結(jié)首行

進(jìn)入"data"工作表中，點(diǎn)擊菜單欄【視圖】【凍結(jié)窗格】【凍結(jié)首行】，用以固定標(biāo)題行。字段比較多，Excel固定首行功能可以方便在滾動(dòng)時(shí)，依然能查看數(shù)據(jù)字段名稱。

4.3 篩選

選中數(shù)據(jù)標(biāo)題行，單擊菜單【開始】【排序和篩選】【篩選】

4.4 提取數(shù)據(jù)子集

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

1. 篩選 orderTime 不在在 2019/1/1 至 2019/12/31 之間的數(shù)據(jù)，刪除。

• 點(diǎn)擊 orderTime 的篩選按鈕，依次點(diǎn)擊【日期篩選】【自定義篩選】

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

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

• 點(diǎn)擊 A 列查看數(shù)據(jù)，還剩 102497 行。

1. 刪除支付時(shí)間早于訂單時(shí)間的數(shù)據(jù)

購物流程是先下單，再支付。支付時(shí)間早于訂單時(shí)間的數(shù)據(jù)屬于錯(cuò)誤數(shù)據(jù)，不符合業(yè)務(wù)邏輯，需要?jiǎng)h除。如何判斷是否有這個(gè)錯(cuò)誤？需要新建一列輔助列。

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

• 鼠標(biāo)放至 L2 單元格右下角，當(dāng)鼠標(biāo)形狀變成十字形時(shí)，雙擊，即可向下填充所有數(shù)據(jù)，并且每個(gè)數(shù)據(jù)都自動(dòng)按公式計(jì)算。

• 篩選 L 列為'Y'的數(shù)據(jù)，即為支付時(shí)間早于下單時(shí)間的錯(cuò)誤數(shù)據(jù)。

• 可以發(fā)現(xiàn)有 5 條數(shù)據(jù)，選中這 5 條，然后刪除。

• 點(diǎn)擊 A 列查看數(shù)據(jù)，還剩 102495 行。

1. 刪除支付時(shí)間間隔大于 30min 的數(shù)據(jù)

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

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

• 篩選 L 列大于 30 的數(shù)據(jù)。

• 可以發(fā)現(xiàn)有 937 條數(shù)據(jù)屬于支付時(shí)間超 30min 的數(shù)據(jù)。選中并刪除這 937 行數(shù)據(jù)。

1. 刪除支付金額為負(fù)數(shù)的數(shù)據(jù)

• 支付金額應(yīng)為正數(shù)，需刪除金額為負(fù)數(shù)的數(shù)據(jù)。篩選F列小于0的數(shù)據(jù)。發(fā)現(xiàn)有6條數(shù)據(jù)。選中并刪除這6條數(shù)據(jù)。

• 選中A列，查看數(shù)總數(shù)，剩余103349條數(shù)據(jù)。

5、清洗數(shù)據(jù)

5.1 查看數(shù)據(jù)

鼠標(biāo)選中各個(gè)字段所在的列?？梢栽诘撞繝顟B(tài)欄查看數(shù)據(jù)計(jì)數(shù)，對(duì)于數(shù)值型數(shù)據(jù)，很能看到平均值、總和等。

5.2 清洗 orderID

• 刪除 orderID 重復(fù)數(shù)據(jù)。選中orderID列，點(diǎn)擊菜單欄【數(shù)據(jù)】【刪除重復(fù)項(xiàng)】，選擇【擴(kuò)展選定區(qū)域】，點(diǎn)擊【刪除重復(fù)項(xiàng)】

• 取消全選，然后選中orderID，點(diǎn)擊確定。

• 執(zhí)行完成后，提示如下：

5.3 清洗 goodsID

• PR000000 屬于下架商品，需刪除。篩選goodsID列為PR000000的數(shù)據(jù)，選中相關(guān)行，刪除。

5.4 清洗 channelID

• 選中G列，發(fā)現(xiàn)數(shù)據(jù)計(jì)數(shù)比其他列少，說明有空值存在。對(duì)于空值，可選用眾數(shù)填充。
• 新建一個(gè)sheet并命名為數(shù)據(jù)透視表。
• 查找channelID眾數(shù)，選中channelID列，點(diǎn)擊菜單欄【插入】【數(shù)據(jù)透視表】

• 放置數(shù)據(jù)透視表的位置選擇現(xiàn)有工作表，點(diǎn)擊向上箭頭符號(hào)，選擇【數(shù)據(jù)透視表】sheet,點(diǎn)擊A1單元格，點(diǎn)擊確定。

• 將chennelID字段拖放到行、值區(qū)域，自動(dòng)生產(chǎn)一個(gè)透視表。

• 在數(shù)據(jù)透視表中B列任意單元格右鍵，點(diǎn)擊【排序】【降序】

• 得到一個(gè)降序排列的表，第一個(gè)【渠道-0896】就是出現(xiàn)次數(shù)最多的眾數(shù)。

• 回到data表中，選中chanelID列，按住ctrl + G，彈出定位框，點(diǎn)擊【定位條件】。

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

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

5.5 清洗 platformtype

• 利用數(shù)據(jù)透視表來查看 platformtype 的類別數(shù)。發(fā)現(xiàn)有些相同的數(shù)值，由于空格原因被分位兩類。

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

• 查找內(nèi)容輸入快輸入一個(gè)空格，替換為輸入框不輸入任何字符。然后點(diǎn)擊【全部替換】

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

• 再次用數(shù)據(jù)透視表查看類別數(shù)，觀察已無問題。

、

5.6 清洗 payment

• 新建計(jì)算折扣字段discount.在G2單元格輸入公式：=F2/E2，然后向下填充。
• 選中G列，點(diǎn)擊菜單欄【數(shù)據(jù)】【數(shù)據(jù)分析】，選擇【描述統(tǒng)計(jì)】，點(diǎn)擊確定。

• 查看折扣的描述統(tǒng)計(jì)平均值為1.11029，說明有折扣大于1的情況，這類數(shù)據(jù)屬于錯(cuò)誤數(shù)據(jù)，需進(jìn)行修正。

• 篩選G列大于1的數(shù)據(jù)，發(fā)現(xiàn)有2004條數(shù)據(jù)。

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

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

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

6、分析數(shù)據(jù)

6.1 總體概覽

銷售 GMV：所有訂單金額（含退款產(chǎn)生的訂單金額）

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

實(shí)際成交額：平臺(tái)實(shí)際銷售收入（不含退款支付金額）

訂單數(shù)量：orderID去重后的數(shù)量

退貨率：退貨訂單數(shù)量/總訂單數(shù)量

用戶數(shù)：userID去重后的數(shù)量

6.2 銷售情況

• 創(chuàng)建訂單月份字段，輸入公式=month(J2),然后向下填充。

• 數(shù)據(jù)透視每月 GMV，然后復(fù)制粘貼為數(shù)值，并除以10000將單位轉(zhuǎn)換為萬元
• 數(shù)據(jù)透視每月實(shí)際付款，然后復(fù)制粘貼為數(shù)值，并除以10000將單位轉(zhuǎn)換為萬元
• 數(shù)據(jù)透視每月不含退單銷售額，然后復(fù)制粘貼為數(shù)值，并除以10000將單位轉(zhuǎn)換為萬元

• 將數(shù)據(jù)匯總粘貼為如下表格，然后插入折線圖。

• 分析結(jié)論

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

6.3 渠道分析

• 利用數(shù)據(jù)透視對(duì)渠道 ID、銷量、銷售額進(jìn)行透視分析

• 分析結(jié)論

1. 渠道-0896產(chǎn)生的銷量和銷售額最高，需重點(diǎn)發(fā)展該渠道業(yè)務(wù)
2. 各渠道銷售額與各渠道銷量呈線性關(guān)系

6.4 下單平臺(tái)分析

• 利用數(shù)據(jù)透視對(duì)用戶下單平臺(tái)銷量進(jìn)行分析，繪制餅圖

• 分析結(jié)論

1. 用戶下單主要通過APP和WechatMP,分別占50.14%和41.19%，通過WEB、WechatShop和Wap平臺(tái)下單相對(duì)較少，由此可見用戶主要通過移動(dòng)端app和微信小程序進(jìn)行下單購買商品。

6.5 用戶行為

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

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

• 分析結(jié)論

1. 周日、周一、周二訂單量相對(duì)較高、周三至周五訂單量有下降，說明客戶偏向于在周日、周一、周二購物
2. 每天上午訂單量主鍵增加，到中午13點(diǎn)達(dá)到最大，下午銷量主鍵下降，到下班之后，銷量又主鍵上升，在8點(diǎn)鐘再次達(dá)到高峰，之后銷量逐漸下降。說明用戶偏向于總中午和晚上睡前購物，可在此時(shí)間進(jìn)行業(yè)務(wù)推送。

6.6 復(fù)購率、回購率

• 客戶復(fù)購率：同一時(shí)間段內(nèi)（這里為一個(gè)月）購物超過兩次的用戶
• 利用數(shù)據(jù)透視表，行區(qū)域拖入month、orderTime、userID,值區(qū)域拖入userID，計(jì)算方式設(shè)為計(jì)數(shù)。

• 生成如下透視表格：

• 復(fù)制粘貼為數(shù)值，然后新建輔助列，用于統(tǒng)計(jì)是否產(chǎn)生復(fù)購。I列大于1則產(chǎn)生復(fù)購，否則沒有復(fù)購。

• 加上輔助再次進(jìn)行數(shù)據(jù)透視，得到每個(gè)月下單總?cè)藬?shù)和復(fù)購人數(shù)，新建一列計(jì)算復(fù)購率。

• 繪制每月復(fù)購率折線圖

• 客戶回購率：上一個(gè)月有購買的客戶在本月也購買的比例
• 利用數(shù)據(jù)透視表，行區(qū)域拖入userID，列區(qū)域拖入月份，值區(qū)域拖入userID，計(jì)算方式設(shè)為計(jì)數(shù)。

• 生成每個(gè)用戶每個(gè)月產(chǎn)生訂單數(shù)的矩陣表

• 復(fù)制粘貼一份，并對(duì)每一個(gè)月新建一列計(jì)算驗(yàn)證是否產(chǎn)生回購。計(jì)算是否產(chǎn)生回購的公式(以2月為例）：=IF(AND((AK6<>0),(AL6<>0)),1,0)。產(chǎn)生回購則標(biāo)記為1，否則為0。

• 在每一列底部統(tǒng)計(jì)每個(gè)月的下單總?cè)藬?shù)和回購人數(shù)。

• 將每月購買人數(shù)和下月回購人數(shù)制成下標(biāo)，計(jì)算每月回購率，并繪制折線圖

• 分析結(jié)論：

1. 復(fù)購率整體偏低，說明客戶大多數(shù)為一次性購買客戶
2. 上半年回購客戶呈上漲趨勢(shì)，下半年回購客戶基本保持穩(wěn)定。

6.7 客戶 RFM 模型

• RFM的含義：
• R（Recency）最近一次消費(fèi)時(shí)間：表示用戶最近一次消費(fèi)距離現(xiàn)在的時(shí)間。消費(fèi)時(shí)間越近的客戶價(jià)值越大。1年前消費(fèi)過的用戶肯定沒有1周前消費(fèi)過的用戶價(jià)值大。
• F（Frequency）消費(fèi)頻率：消費(fèi)頻率是指用戶在統(tǒng)計(jì)周期內(nèi)購買商品的次數(shù)，經(jīng)常購買的用戶也就是熟客，價(jià)值肯定比偶爾來一次的客戶價(jià)值大。
• M（Monetary）消費(fèi)金額：消費(fèi)金額是指用戶在統(tǒng)計(jì)周期內(nèi)消費(fèi)的總金額，體現(xiàn)了消費(fèi)者為企業(yè)創(chuàng)利的多少，自然是消費(fèi)越多的用戶價(jià)值越大。
• 數(shù)據(jù)透視表生成每個(gè)用戶最近一次消費(fèi)時(shí)間、消費(fèi)次數(shù)、消費(fèi)金額。userID拖入行區(qū)域，payTime、userID、payment拖入值區(qū)域，分別設(shè)置計(jì)數(shù)方式為最大值、計(jì)數(shù)、求和。

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

• 選中H、I、J列，點(diǎn)擊【數(shù)據(jù)分析】查看三列描述統(tǒng)計(jì)的均值

• 建立RFM客戶類別識(shí)別碼

• 新建輔助列K L M列，對(duì)R、F、M進(jìn)行均值比較，N列生產(chǎn)RFM識(shí)別碼，O列匹配客戶分類。

• 數(shù)據(jù)透視生成客戶類型與銷量、銷售額的表格

• 繪制RFM 銷量/銷售額圖表

• 分析結(jié)論

當(dāng)客戶價(jià)值分類完成，需要針對(duì)不同層級(jí)的客戶實(shí)施不同的運(yùn)營策略。

（1）重要價(jià)值客戶：維持現(xiàn)狀，重點(diǎn)維護(hù)

最近消費(fèi)時(shí)間近，消費(fèi)頻次和消費(fèi)金額都很高；重點(diǎn)維護(hù)對(duì)象，VIP大用戶組織建設(shè)是必要的，權(quán)益專享、定期福利等；

（2）重要發(fā)展客戶：提升頻次，重點(diǎn)深耕

最近消費(fèi)時(shí)間較近，消費(fèi)金額高，但頻次不高；屬于忠誠度不高用戶。著力讓用戶在平臺(tái)上活躍，優(yōu)化產(chǎn)品和服務(wù)，幫助用戶提升頻次。

（3）重要保持客戶：用戶回流，重點(diǎn)挽回

最近消費(fèi)時(shí)間較遠(yuǎn)，但消費(fèi)頻次和金額都很高；說明這是個(gè)一段時(shí)間沒來的忠實(shí)客戶，定期的EDM、PUSH、短信，主動(dòng)和用戶保持聯(lián)系和互動(dòng)；

（4）重要挽留客戶：提前預(yù)警，重點(diǎn)召回

最近消費(fèi)時(shí)間較遠(yuǎn)，消費(fèi)頻次不高，消費(fèi)金額高；屬于重點(diǎn)的預(yù)流失用戶，提前做好預(yù)流失預(yù)警和策略觸達(dá)。

（5）一般價(jià)值用戶：潛力用戶，刺激復(fù)購

最近消費(fèi)時(shí)間較近，消費(fèi)頻次高，就是消費(fèi)金額低；屬于重點(diǎn)潛力用戶，可以發(fā)放大額卡券，引導(dǎo)此類用戶不斷增加投資。

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

最近消費(fèi)時(shí)間較近，消費(fèi)頻次低，消費(fèi)金額也低；可能是新用戶，最近投資過，需要客服回復(fù)工作加強(qiáng)，以及福利及時(shí)提醒。

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

最近消費(fèi)時(shí)間較遠(yuǎn)，消費(fèi)頻次高，消費(fèi)金額低；屬于流失用戶，可能前期在普通很活躍，后期情感受挫，或是產(chǎn)品、服務(wù)、獎(jiǎng)勵(lì)力度達(dá)不到心里預(yù)期，需要做好利益與情感雙重觸達(dá)。

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

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