?? ? ? ?? ? ? ?

叮當(dāng)之前主要是做增長方向的，平時工作中主要基于問題做數(shù)據(jù)分析，大部分時候都是怎么快怎么來。

很少有各種工具，各種分析方法全來一遍的，所以本次借分析“淘寶用戶行為數(shù)據(jù)集”為案例，梳理一下自己的數(shù)據(jù)分析技能。

本文以“淘寶用戶行為數(shù)據(jù)集”的分析全過程為例，展示數(shù)據(jù)分析的全過程，分析部分需要一些SQL基礎(chǔ)。

——使用工具：MySQL，Excel，Navicat，PowerBI

——數(shù)據(jù)來源：阿里天池實驗室-淘寶用戶行為數(shù)據(jù)集

https://tianchi.aliyun.com/dataset/dataDetail?dataId=649&userId=1

——分析類型：描述分析，診斷分析

——分析方法：漏斗分析，用戶路徑分析，RFM用戶價值分析，活躍/存留分析，帕累托分析，假設(shè)驗證分析。

（考慮到閱讀體驗文章中只放了SQL截圖）

目錄如下

? ? ? ?? ? ? ?

一、分析流程和方法

1.1 數(shù)據(jù)分析類型

? ? ? ?

當(dāng)沒有清晰的數(shù)據(jù)看板時我們需要先清洗雜亂的數(shù)據(jù)，基于分析模型做可視化，搭建描述性的數(shù)據(jù)看板。

在沒有很明確問題或問題很多很復(fù)雜的情況下，直接看雜亂的源數(shù)據(jù)不僅效率很低，也很難得到有價值的信息。

然后基于描述性的數(shù)據(jù)挖掘問題，提出假設(shè)做優(yōu)化，或者基于用戶特征數(shù)據(jù)進(jìn)行預(yù)測分析找規(guī)律，基于規(guī)律設(shè)計策略。簡單來說：

——描述性分析就是：“畫地圖”

——診斷性分析就是：“找問題”

——預(yù)測性分析就是 ：“找規(guī)律”

1.2 數(shù)據(jù)分析的兩個典型場景

在數(shù)據(jù)分析中有兩個典型的場景：

一種是有數(shù)據(jù)，沒有問題：需要先整體分析數(shù)據(jù)，然后再根據(jù)初步的描述分析，挖掘問題做診斷性分析，提出假設(shè)，設(shè)計策略解決問題。

?

另一種是已經(jīng)發(fā)現(xiàn)了問題，或者已經(jīng)有了假設(shè)，這種做數(shù)據(jù)分析更偏向于驗證假設(shè)。

?

二、淘寶用戶行為分析

本次是對“淘寶用戶行為數(shù)據(jù)集”進(jìn)行分析，在分析之前我們并不知道有什么問題，所以需要先進(jìn)行描述性分析，分析數(shù)據(jù)挖掘問題。

2.1 解讀元數(shù)據(jù)

我們首先來看下這個數(shù)據(jù)集的元數(shù)據(jù)：

? ? ??

數(shù)據(jù)集包含了2017年11月25日至2017年12月3日之間，有行為的約一百萬隨機(jī)用戶的所有行為（行為包括四種：點擊商品詳情頁、購買商品、將商品放入購物車、收藏商品）。數(shù)據(jù)集的每一行表示一條用戶行為，由用戶ID、商品ID、商品類目ID、行為類型和時間戳組成，并以逗號分隔。

本數(shù)據(jù)集包含：用戶數(shù)量987994；商品數(shù)量4162024；商品類目數(shù)量9439；所有行為數(shù)量100150807

2.2 選擇分析方法

根據(jù)以上數(shù)據(jù)字段我們可以拿用戶行為為主軸從縱深方向提出一些問題，然后再從數(shù)據(jù)中找答案。

? ? ? ?? ? ? ?

縱向：

——這個數(shù)據(jù)集中用戶的日活躍和周活躍時間有什么規(guī)律嗎？

——在當(dāng)日活躍的用戶次日，三日，四日……還有多少活躍？

深向：

——用戶從瀏覽到購買的整體轉(zhuǎn)化率怎么樣？

——用戶從瀏覽到購買的路徑是怎么樣子的？?

——平臺主要會給用戶推送什么商品？

——用戶喜歡什么類目？喜歡什么商品？?

——怎么判斷哪些是高價值用戶 ？?

?

下面是叮當(dāng)整理的常用分析方法：? ? ??? ? ? ?

我們可以給前面的問題匹配一下分析方法，便于后面的分析：

2.3 數(shù)據(jù)清洗

為了便于后面的數(shù)據(jù)分析，在分析之前我們需要先對做一下清洗

2.3.1數(shù)據(jù)預(yù)處理：

看元數(shù)據(jù)（字段解釋，數(shù)據(jù)來源，數(shù)據(jù)類型，數(shù)據(jù)量……）初步發(fā)現(xiàn)問題為之后的處理做準(zhǔn)備。

數(shù)據(jù)導(dǎo)入：由于整體數(shù)據(jù)集有100W+條數(shù)據(jù)，導(dǎo)入太慢，本次僅導(dǎo)入10W條分析

添加列名：數(shù)據(jù)導(dǎo)入時默認(rèn)使用第一行數(shù)據(jù)作為列名，由于本數(shù)據(jù)集沒有列名，需要添加

? ? ? ?? ? ? ?

2.3.2缺失值清洗：

確定缺失值范圍，去除不需要字段，填充缺失內(nèi)容? ??

2.3.3格式內(nèi)容清洗：

根據(jù)元數(shù)據(jù)格式和后續(xù)分析需要的格式對數(shù)據(jù)進(jìn)行處理

timestamps字段是時間戳字符類型，而后面要做存留分析和用戶活躍時間段需要用到時間戳中的日期字段和時間字段，在這里需要提前分下列。

??

2.3.4邏輯錯誤清洗

去除重復(fù)值，異常值

——去除重復(fù)值：并把用戶ID,商品ID，時間戳設(shè)置為主鍵

——異常值處理：查詢并刪除2017年11月25日至2017年12月3日之外的數(shù)據(jù)

剔除不在本次分析范圍的數(shù)據(jù)

? ? ??

查詢并刪除小于2017-11-25的

——驗證數(shù)據(jù)：? ? ??

2.4 描述分析

2.4.1這個數(shù)據(jù)集中用戶的日活躍和周活躍時間有什么規(guī)律嗎？

——分析思路：

從“時間戳“字段中抽取出“日期”和“小時”的數(shù)據(jù)，創(chuàng)建一個“活躍時間”字段，并從“行為類型”中用分組方式把用戶的“瀏覽”“收藏”“加購物車”“購買”行為抽離出來，組成一個視圖表，導(dǎo)出到Excel中用透視表分析用戶的日活躍規(guī)律和周活躍規(guī)律。

——SQL提數(shù)：

增加活躍時間字段

? ? ? ?? ? ? ?

查詢用戶 活躍時間分布，并創(chuàng)建視圖

? ? ? ?? ? ? ?

——Excel可視化：

? ? ? ?? ? ? ?

活躍曲線整體為上升狀態(tài)，同為周六日，12月2號，3號相比11月25日，26日活躍度更高。

是否是用戶增長帶來的？

用戶在周六周日相比其他時間更活躍（周六周日為休息日，用戶有更多時間）

? ? ??

一天內(nèi)用戶活躍的最高峰期為21點（用戶在這個時間段空閑較多）

正常工作職場工作者的睡前時間，996的應(yīng)該也下班啦~

?

2.4.2在當(dāng)日活躍的用戶次日，三日，四日……還有多少活躍？

——分析思路：

用戶存留的分析可以分為“新用戶存留”和“活躍用戶存留”

新用戶存留一般指：新注冊用戶在一定時間周期內(nèi)還會不會再登錄

活躍用戶存留需要根據(jù)產(chǎn)品類型和用戶場景選擇“關(guān)鍵行為”和選擇“時間周期”

-關(guān)鍵行為：淘寶作為購物網(wǎng)站，用戶瀏覽，收藏，加購，購買商品與交易行為高度相關(guān)都可作為關(guān)鍵行為。

-時間周期：淘寶擁有海量的SKU，基本可以滿足用戶各方面的需求，理論上用戶每天都有購買需求，時間周期可以按天。

SO，實際上這個問題就是在求，數(shù)據(jù)集第一日在APP有關(guān)鍵行為的用戶在第二天，第三天……還會繼續(xù)在APP中有關(guān)鍵行為的用戶占比

我們需要先列出每用戶每天及當(dāng)天后面又活躍的日期，用于后面求次日存留，三日存留……

之后按日期對用戶進(jìn)行分組，并抽取之后9天依然活躍的用戶數(shù)量

最后用活躍用戶表中后續(xù)活躍用戶除首日活躍數(shù)量乘100加%號

——SQL提數(shù)：

列出每用戶每天及當(dāng)天后面又活躍的日期，并創(chuàng)建“活躍時間間隔表”用于后面求次日存留，三日存留……

? ? ? ?? ? ? ?

對“活躍時間間隔表視圖”引用進(jìn)行分組統(tǒng)計，計算每日存留人數(shù)并創(chuàng)建視圖

? ?

對存留人數(shù)表進(jìn)行計算，統(tǒng)計活躍用戶留存率

?? ?

——Excel可視化：

? ? ? ?? ? ? ?

• 用戶增長：從2017年11月15日致2017年12月3日，活躍用戶新增38%

• 存留增長：從2017年11月15日致2017年12月3日，活躍用戶次日留存增長18.67%，當(dāng)日的活躍用戶留存也在快速增長，第七日留存比次日留存高18.56%。

假設(shè)隨時間增長的留存率提升來源于新dau提升策略的優(yōu)化，后續(xù)存留的提升來源于召回策略的優(yōu)化。

2.4.3用戶從瀏覽到購買的整體轉(zhuǎn)化率怎么樣？

——分析思路：

將數(shù)據(jù)集中按不同用戶，不同商品維度進(jìn)行分組獲得某一用戶行為對某一商品不同行為的數(shù)據(jù)，然后對“用戶行為漏斗表”中的瀏覽，加購物車，收藏，購買行為進(jìn)行分組統(tǒng)計。

——SQL提數(shù)：

-把各種用戶行為分離出來并創(chuàng)建視圖方便后續(xù)查詢用戶行為數(shù)據(jù)

?? ?

查詢整體數(shù)據(jù)漏斗

——Excel可視化：

? ? ? ??? ? ?

用戶從瀏覽到購買整體轉(zhuǎn)化率2.3%，具體主要在哪個環(huán)節(jié)流失還需要再細(xì)分用戶路徑分析

?

2.4.4用戶從瀏覽到購買的路徑是怎么樣子的？?

——分析思路：

窮舉所有可能的用戶路徑，引用“用戶行為漏斗表”視圖，計在數(shù)據(jù)中點擊行為大于0，購買行為大于0，其他兩項為0，則判定本用戶購買路徑為：點擊—購買，其他路徑同理，多次查詢并用Excel表記錄查詢數(shù)據(jù)，用戶PowerBI桑基圖做可視化。

? ? ? ??? ? ?

——SQL提數(shù)：

——PowerBI可視化：

? ? ? ?

用戶從瀏覽到購買的路徑主要有4條，路徑越長轉(zhuǎn)化率越底

路徑1：瀏覽→購買：轉(zhuǎn)化率1.45%

路徑2：瀏覽→加購物車→購買：轉(zhuǎn)化率0.33

路徑3：瀏覽→收藏→購買：轉(zhuǎn)化率0.11%

路徑4：瀏覽→收藏→加購物車→購買：轉(zhuǎn)化率0.03%

以上轉(zhuǎn)化率等于起始路徑到購買的轉(zhuǎn)化

2.4.5平臺主要給用戶推送什么商品？

——分析思路：

雖然我們沒法直接從數(shù)據(jù)中找到平臺推送的數(shù)據(jù)，但作為平臺流量傾斜的商品，瀏覽量一般都會比其他商品的瀏覽量高一些，我們可以引用“用戶行為漏斗表”視圖統(tǒng)計瀏覽量前100的商品及其類目。

——SQL提數(shù)：

——Excel可視化：

? ? ? ?? ? ? ?

——描述性分析：

瀏覽量top100的商品瀏覽量呈階梯分布，越靠前的階梯之間的落差相對越大在這個階梯中的商品越少，越靠后商品瀏覽量階梯之間的落差相對越小，同階梯內(nèi)的商品越多。

——是否是用于淘寶流量分配規(guī)則的原因造成的？（假設(shè)淘寶的規(guī)則是給所有商品分配的初始流量是一樣的，后期這些商品中那些商品轉(zhuǎn)化率高就給哪些商品更多曝光。）

瀏覽量TOP100的商品所屬類目中，4756105，3607361，4357323三個類目瀏覽量遠(yuǎn)超其他類目。

——這個幾個類目商品類型是否是高頻剛需類型的呢？

2.4.6用戶喜歡什么商品？?

——分析思路：

找高轉(zhuǎn)化率的商品（銷量高的有可能只是低價或者流量大）

——SQL提數(shù)：

查詢計算商品轉(zhuǎn)化率，升序排列，取前100個

? ? ? ??

——Excel可視化：

? ? ? ??

——描述性分析：

從商品看：有17款商品轉(zhuǎn)化率超過了1。

是否是由于用戶直接從購物車或者商品收藏直接復(fù)購，未點擊商詳？

從類目看：這些商品所屬類目分布均勻，除965809，4801426，2735466，2640118，5063620，4789432，2945933這7個類目之外，其他類目都只有一個商品在轉(zhuǎn)化率TOP100的商品中。

是否是由于淘寶是根據(jù)“同一類目下的高轉(zhuǎn)化商品”給用戶做推薦的？

2.4.7怎么判斷哪些是高價值用戶 ？?

——分析思路：

用戶價值分析常用的分析方式是RFM模型

RFM模型是3個指標(biāo)的縮寫，最近一次消費(fèi)時間（R），消費(fèi)頻率（F），消費(fèi)金額（M）

然后給這三個指標(biāo)根據(jù)價值分5個等級 ，進(jìn)行打分計算分值和平均值，然后根據(jù)分值與平均值對比，分出“高”“中”“低”，綜合進(jìn)行用戶分層。

? ? ? ?? ? ? ?

本次分析中的R,F,M具體定義（僅用于演示分析方法，無實際業(yè)務(wù)參考價值）：

-R：根據(jù)用戶最近一次的購買時間與2017年12月3日之間的差值，判斷用戶最近一次消費(fèi)時間間隔。

-F：將數(shù)據(jù)集中用戶在2017年11月25日至2017年12月3日9天時間內(nèi)的購買次數(shù)作為消費(fèi)頻率

-M：由于本數(shù)據(jù)集中未包含購買金額字段，暫時排除此指標(biāo)。

?

——SQL取數(shù)與分析：

1）建立打分標(biāo)準(zhǔn)：先計算R,F的值，并排序，根據(jù)R,F值最大值和最小值得區(qū)間設(shè)計本次得打分標(biāo)準(zhǔn)

關(guān)于打分標(biāo)準(zhǔn)：不同業(yè)務(wù)的用戶消費(fèi)頻率，消費(fèi)金額，精細(xì)化運(yùn)營策略與成本……都是不同，一般常用”分位數(shù)“建立打分標(biāo)準(zhǔn)，由于SQL并不是專業(yè)得統(tǒng)計分析工具，計算分位數(shù)較為復(fù)雜，本次僅使用最大值和最小值的區(qū)間初略建立規(guī)則。

分位數(shù)：是指在統(tǒng)計學(xué)中把所有數(shù)值由小到大排列并分成幾等份，取處于對應(yīng)幾個分割點位置的數(shù)值。

-查詢并計算R，F值創(chuàng)建視圖

? ? ? ?? ? ? ?

-引用RF數(shù)值表，分別查詢R,F的最大值和最小值

? ? ? ?

? ? ? ?? ? ? ?

-結(jié)合人工瀏覽的建立打分標(biāo)準(zhǔn)? ? ??

消費(fèi)時間間隔：在1~8區(qū)間內(nèi)四等分

消費(fèi)頻率：由于人工 瀏覽時發(fā)現(xiàn)很少有超過20次購買的，故消費(fèi)頻率在20以內(nèi)四等分

2）給R，F按價值打分

3）計算價值的平均值? ? ?? ? ? ?

4）用平均值和用戶分類規(guī)則表比較得出用戶分類? ?

-查詢各類用戶數(shù)量

——Excel可視化? ? ??

由于缺失了商品價格部分的數(shù)據(jù)，本模塊暫時沒有分析結(jié)論。

?

2.5 診斷分析

通過描述性分析得到可視化的數(shù)據(jù)后我們一般會先看一下是否符合業(yè)務(wù)常識

如：假設(shè)一個頁面的UV（瀏覽人數(shù)）比PV（瀏覽次數(shù)）還高，那這個數(shù)據(jù)質(zhì)量肯定是有問題的

如果符合常識接下來我們會通過與行業(yè)平均數(shù)據(jù)和本產(chǎn)品的同比環(huán)比對比看是否正常，如果不正常就要找原因，設(shè)計解決方案，如果正常那就看是否有可以優(yōu)化的地方。

? ? ? ??

2.5.1診斷分析結(jié)論

我們首先來看一下這些描述性分析是否符合業(yè)務(wù)常識和指標(biāo)是否正常：

? ? ? ?? ? ? ?

1.活躍曲線整體為上升狀態(tài)，同為周六日，12月2號，3號相比11月25日，26日活躍度更高。

正常：結(jié)合描述分析4中的活躍用戶的增長。

2.用戶在周六周日相比其他時間更活躍

正常：周六周日為休息日，用戶有更多時間來刷淘寶，反映在數(shù)據(jù)上就是活躍度的增加。

3.一天內(nèi)用戶活躍的最高峰期為21點

正常：用戶在這個時間段有空閑，996的都下班啦~

4.從2017年11月15日致2017年12月3日，活躍用戶新增38%

還需驗證：

如果是由于新注冊用戶或者老用戶召回策略帶來的增長符合常識，具體還需結(jié)合新注冊用戶數(shù)據(jù)和用戶召回策略數(shù)據(jù)做驗證。

5.從2017年11月15日致2017年12月3日，活躍用戶次日留存增長18.67%，當(dāng)日的活躍用戶留存也在快速增長，第七日留存比次日留存高18.56%。

不符合常識：因為從長期來看用戶都是會流失的，只是生命周期長短問題，而從淘寶的用戶行為來看同批用戶的存留數(shù)據(jù)竟然隨著時間的增加而增加。

假設(shè)場景可能是這樣的：用戶小A注冊了淘寶APP，第二天就不再登錄了，而第三天收到了淘寶的推薦提醒（APP消息，短信……）在消息中發(fā)現(xiàn)了自己喜歡的商品，而且還有優(yōu)惠下單買了，第四天又收到了淘寶的消息，還是自己喜歡的……

這里的具體數(shù)據(jù)還需要結(jié)合用戶生命周期運(yùn)營的策略和數(shù)據(jù)做驗證。

6.用戶從瀏覽到購買整體轉(zhuǎn)化率2.3%

正常，根據(jù)之前了解到的電商數(shù)據(jù)，多種客單價的商品（幾十~幾千）在一起，整體轉(zhuǎn)化率在2%~3%之間，當(dāng)然具體還需要結(jié)合歷史的同比，環(huán)比數(shù)據(jù)取看。

7.用戶從瀏覽到購買的路徑主要有4條，路徑越長轉(zhuǎn)化率越低。

正常：從流量的角度，每多一個步驟就會多一些用戶流失這個符合常識。

8.瀏覽量top100的商品瀏覽量呈階梯分布，越靠前的階梯之間的落差相對越大在這個階梯中的商品越少，越靠后商品瀏覽量階梯之間的落差相對越小，同階梯內(nèi)的商品越多。

待驗證：

假設(shè)淘寶會給高轉(zhuǎn)化的爆款商品更多的曝光，商品瀏覽量呈金字塔分布是正常的。

9.瀏覽量TOP100的商品所屬類目中，4756105，3607361，4357323三個類目瀏覽量遠(yuǎn)超其他類目。

還需驗證：

——抽取購買購買次數(shù)判斷這個幾個類目商品類型是否是高頻剛需類型的呢？

10.從商品看：有17款商品轉(zhuǎn)化率超過了1。

不正常：

還需驗證：是否是由于用戶直接從購物車或者商品收藏直接復(fù)購，未點擊商詳？

11.從類目看：這些商品所屬類目分布均勻，除965809，4801426，2735466，2640118，5063620，4789432，2945933這7個類目之外，其他類目都只有一個商品在轉(zhuǎn)化率TOP100的商品中。

還需驗證：

是否是由于淘寶是根據(jù)“同一類目下的高轉(zhuǎn)化商品”給用戶做推薦的？

2.5.2假設(shè)與驗證

根據(jù)以上診斷分析我們梳理出了以下假設(shè)，做假設(shè)驗證。

? ? ? ?? ? ? ?

假設(shè)1：這些商品中有高轉(zhuǎn)化率的爆款商品

-引用“商品轉(zhuǎn)化率視圖”查詢排名前5的商品轉(zhuǎn)化率

? ? ?? ?

-對比同類目的其他商品轉(zhuǎn)化率

? ? ? ?? ? ? ?

對比瀏覽量TOP5的商品，發(fā)現(xiàn)這些商品轉(zhuǎn)化率在同一類目下并不高，假設(shè)不成立

?

假設(shè)2：4756105，3607361，4357323三個類目屬于高頻剛需類目

抽取這幾個類目的商品某買頻次數(shù)據(jù)驗證

-創(chuàng)建類目購買頻次表? ? ?

? ? ? ?

-計算類目購買頻次平均值? ? ??

? ? ? ?

-查詢4756105，3607361，4357323三個類目的購買頻次??? ? ?? ? ? ?

4756105，3607361，4357323三個類目的用戶購買頻次明顯高于平均值，假設(shè)成立。

?

假設(shè)3：有部分用戶是未點擊商詳直接從收藏和購物車購買的。

查詢轉(zhuǎn)化率超過1的商品的用戶行為數(shù)據(jù)

? ? ? ?? ? ? ?? ? ??

用戶不是直接從收藏和購物車購買的，只是后續(xù)復(fù)購未點擊商詳，假設(shè)不成立

?

假設(shè)4：淘寶推薦的商品主要是“同一類目下的高轉(zhuǎn)化商品”

給瀏覽量TOP100的商品和轉(zhuǎn)化率TOP100的商品做匹配看其中重合的商品有多少。

? ? ? ??? ? ?

用Excel對瀏覽量TOP100的商品ID和轉(zhuǎn)化率TOP100的商品ID進(jìn)行去重，結(jié)果無重復(fù)值，假設(shè)不成立

三、結(jié)論

1）用戶活躍：用戶活躍曲線整體呈上升趨勢，在一周中周六，周日活躍度比平時更高，在一天中用戶活躍曲線從凌晨4點開始往上升，在中午12點和下午5~6點有兩個小低谷（吃飯），到晚上9點時活躍度達(dá)到頂峰。

2）用戶留存：從2017年11月15日致2017年12月3日的用戶留存數(shù)據(jù)來看，淘寶的用戶留存數(shù)據(jù)較好，活躍用戶次日留存增長18.67%，當(dāng)日的活躍用戶留存也在快速增長，第七日留存比次日留存高18.56%。

3）用戶轉(zhuǎn)化：整體轉(zhuǎn)化2.3%，用戶從瀏覽到購買的路徑主要有4條，路徑越長轉(zhuǎn)化率越低。

路徑1：瀏覽→購買：轉(zhuǎn)化率1.45%

路徑2：瀏覽→加購物車→購買：轉(zhuǎn)化率0.33

路徑3：瀏覽→收藏→購買：轉(zhuǎn)化率0.11%

路徑4：瀏覽→收藏→加購物車→購買：轉(zhuǎn)化率0.03%

4）平臺推薦與用戶偏好：從數(shù)據(jù)集中的數(shù)據(jù)來看，排除用戶興趣偏好標(biāo)簽，淘寶給用戶用戶推送的商品主要是高頻剛需的類目，促使用戶復(fù)購，流量回流平臺。

以上結(jié)論受數(shù)據(jù)量和數(shù)據(jù)類型的影響，并不一定準(zhǔn)確，僅用來練習(xí)數(shù)據(jù)分析方法。

↘好文推薦：

后臺產(chǎn)品經(jīng)理入門指南（上）

產(chǎn)品經(jīng)理要懂的「技術(shù)那些事」

美團(tuán)王慧文，王興14年的帶刀護(hù)衛(wèi)

點個“在看”吧

總結(jié)

以上是生活随笔為你收集整理的如何用SQL分析电商用户行为数据（案例）的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。