起點課堂會員權益
職業體系課特權
線下行業大會特權
個人IP打造特權
30+門專項技能課
1300+專題課程
12場職場軟技能直播
12場求職輔導直播
12場專業技能直播
會員專屬社群
榮耀標識案例：如何用SQL分析電商用戶行為數據
編輯導語：在日常工作中，經常會用到數據分析的方法，數據分析可以幫助我們快速清晰的了解目前數據走向，也可以對用戶的活躍度和轉化度進行分析；本文作者以“淘寶用戶行為數據集”為例，用SQL進行分析，我們一起來看一下。
筆者之前主要是做增長方向的，平時工作中主要基于問題做數據分析，大部分時候都是怎么快怎么來，很少有各種工具、各種分析方法全來一遍的；所以本次借分析“淘寶用戶行為數據集”為案例，梳理一下自己的數據分析技能。
本文以“淘寶用戶行為數據集”的分析全過程為例，展示數據分析的全過程。
- 使用工具：MySQL、Excel、Navicat、PowerBI；
- 數據來源：阿里天池實驗室-淘寶用戶行為數據集；
- 分析類型：描述分析、診斷分析；
- 分析方法：漏斗分析、用戶路徑分析、RFM用戶價值分析、活躍/存留分析、帕累托分析、假設驗證分析。
目錄如下：
一、分析流程和方法
1. 數據分析類型
當沒有清晰的數據看板時我們需要先清洗雜亂的數據，基于分析模型做可視化，搭建描述性的數據看板。
在沒有很明確問題或問題很多很復雜的情況下，直接看雜亂的源數據不僅效率很低，也很難得到有價值的信息。
然后基于描述性的數據挖掘問題，提出假設做優化，或者基于用戶特征數據進行預測分析找規律，基于規律設計策略。
簡單來說：
- 描述性分析就是：“畫地圖”；
- 診斷性分析就是：“找問題”；
- 預測性分析就是：“找規律”；
2. 數據分析的兩個典型場景
在數據分析中有兩個典型的場景：
一種是有數據，沒有問題，需要先整體分析數據，然后再根據初步的描述分析，挖掘問題做診斷性分析，提出假設，設計策略解決問題。
另一種是已經發現了問題，或者已經有了假設，這種做數據分析更偏向于驗證假設。
二、淘寶用戶行為分析
本次是對“淘寶用戶行為數據集”進行分析，在分析之前我們并不知道有什么問題，所以需要先進行描述性分析，分析數據挖掘問題。
1. 解讀元數據
我們首先來看下這個數據集的元數據：
數據集包含了2017年11月25日至2017年12月3日之間，有行為的約一百萬隨機用戶的所有行為（行為包括四種：點擊商品詳情頁、購買商品、將商品放入購物車、收藏商品）。
數據集的每一行表示一條用戶行為，由用戶ID、商品ID、商品類目ID、行為類型和時間戳組成，并以逗號分隔。
本數據集包含：用戶數量987994、商品數量4162024、商品類目數量9439；所有行為數量100150807。
2. 選擇分析方法
根據以上數據字段我們可以拿用戶行為為主軸從縱深方向提出一些問題，然后再從數據中找答案
縱向：
- 這個數據集中用戶的日活躍和周活躍時間有什么規律嗎？
- 在當日活躍的用戶次日、三日、四日……還有多少活躍？
深向：
- 用戶從瀏覽到購買的整體轉化率怎么樣？
- 用戶從瀏覽到購買的路徑是怎么樣子的？
- 平臺主要會給用戶推送什么商品？
- 用戶喜歡什么類目？喜歡什么商品？
- 怎么判斷哪些是高價值用戶 ？
下面是叮當整理的常用分析方法：
我們可以給前面的問題匹配一下分析方法，便于后面的分析：
3. 數據清洗
為了便于后面的數據分析，在分析之前我們需要先對做一下清洗。
1）數據預處理
看元數據（字段解釋，數據來源，數據類型，數據量……）初步發現問題為之后的處理做準備。
數據導入：由于整體數據集有100W+條數據，導入太慢，本次僅導入10W條分析。
添加列名：數據導入時默認使用第一行數據作為列名，由于本數據集沒有列名，需要添加。
2）缺失值清洗
確定缺失值范圍，去除不需要字段，填充缺失內容。
3）格式內容清洗
根據元數據格式和后續分析需要的格式對數據進行處理。
timestamps字段是時間戳字符類型，而后面要做存留分析和用戶活躍時間段需要用到時間戳中的日期字段和時間字段，在這里需要提前分下列。
4）邏輯錯誤清洗
去除重復值，異常值。
去除重復值：并把用戶ID、商品ID、時間戳設置為主鍵。
異常值處理：查詢并刪除2017年11月25日至2017年12月3日之外的數據。
剔除不在本次分析范圍的數據。
查詢并刪除小于2017-11-25的。
驗證數據：
4. 描述分析
1）這個數據集中用戶的日活躍和周活躍時間有什么規律嗎？
分析思路：
從“時間戳“字段中抽取出“日期”和“小時”的數據，創建一個“活躍時間”字段，并從“行為類型”中用分組方式把用戶的“瀏覽”“收藏”“加購物車”“購買”行為抽離出來，組成一個視圖表，導出到Excel中用透視表分析用戶的日活躍規律和周活躍規律。
SQL提數：
增加活躍時間字段。
查詢用戶 活躍時間分布，并創建視圖。
Excel可視化：
活躍曲線整體為上升狀態，同為周六日，12月2號、3號相比11月25日、26日活躍度更高。
是否是用戶增長帶來的？
用戶在周六周日相比其他時間更活躍（周六周日為休息日，用戶有更多時間。）
一天內用戶活躍的最高峰期為21點（用戶在這個時間段空閑較多。）
正常工作職場工作者的睡前時間，996的應該也下班啦。
2）在當日活躍的用戶次日、三日、四日……還有多少活躍？
分析思路：
用戶存留的分析可以分為“新用戶存留”和“活躍用戶存留”。
新用戶存留一般指：新注冊用戶在一定時間周期內還會不會再登錄。
活躍用戶存留需要根據產品類型和用戶場景選擇“關鍵行為”和選擇“時間周期”。
- 關鍵行為：淘寶作為購物網站，用戶瀏覽，收藏，加購，購買商品與交易行為高度相關都可作為關鍵行為。
- 時間周期：淘寶擁有海量的SKU，基本可以滿足用戶各方面的需求，理論上用戶每天都有購買需求，時間周期可以按天。
SO，實際上這個問題就是在求，數據集第一日在APP有關鍵行為的用戶在第二天、第三天……還會繼續在APP中有關鍵行為的用戶占比。
我們需要先列出每用戶每天及當天后面又活躍的日期，用于后面求次日存留，三日存留……之后按日期對用戶進行分組，并抽取之后9天依然活躍的用戶數量；最后用活躍用戶表中后續活躍用戶除首日活躍數量乘100加%號。
SQL提數：
列出每用戶每天及當天后面又活躍的日期，并創建“活躍時間間隔表”用于后面求次日存留、三日存留……。
對“活躍時間間隔表視圖”引用進行分組統計，計算每日存留人數并創建視圖。
對存留人數表進行計算，統計活躍用戶留存率。
Excel可視化：
- 用戶增長：從2017年11月15日致2017年12月3日，活躍用戶新增38%；
- 存留增長：從2017年11月15日致2017年12月3日，活躍用戶次日留存增長18.67%，當日的活躍用戶留存也在快速增長，第七日留存比次日留存高18.56%。
假設隨時間增長的留存率提升來源于新dau提升策略的優化，后續存留的提升來源于召回策略的優化。
3）用戶從瀏覽到購買的整體轉化率怎么樣？
分析思路：
將數據集中按不同用戶，不同商品維度進行分組獲得某一用戶行為對某一商品不同行為的數據；然后對“用戶行為漏斗表”中的瀏覽、加購物車、收藏、購買行為進行分組統計。
SQL提數：
把各種用戶行為分離出來并創建視圖方便后續查詢用戶行為數據。
查詢整體數據漏斗。
Excel可視化：
用戶從瀏覽到購買整體轉化率2.3%，具體主要在哪個環節流失還需要再細分用戶路徑分析。
4）用戶從瀏覽到購買的路徑是怎么樣子的？
分析思路：
窮舉所有可能的用戶路徑，引用“用戶行為漏斗表”視圖，計在數據中點擊行為大于0，購買行為大于0，其他兩項為0，則判定本用戶購買路徑為；點擊—購買，其他路徑同理，多次查詢并用Excel表記錄查詢數據，用戶PowerBI?；鶊D做可視化。
SQL提數：
PowerBI可視化：
用戶從瀏覽到購買的路徑主要有4條，路徑越長轉化率越低：
- 路徑1：瀏覽→購買：轉化率1.45%；
- 路徑2：瀏覽→加購物車→購買：轉化率0.33；
- 路徑3：瀏覽→收藏→購買：轉化率0.11%；
- 路徑4：瀏覽→收藏→加購物車→購買：轉化率0.03%；
以上轉化率等于起始路徑到購買的轉化
5）平臺主要給用戶推送什么商品？
分析思路：
雖然我們沒法直接從數據中找到平臺推送的數據，但作為平臺流量傾斜的商品，瀏覽量一般都會比其他商品的瀏覽量高一些；我們可以引用“用戶行為漏斗表”視圖統計瀏覽量前100的商品及其類目。
SQL提數：
Excel可視化：
描述性分析：
瀏覽量top100的商品瀏覽量呈階梯分布，越靠前的階梯之間的落差相對越大在這個階梯中的商品越少，越靠后商品瀏覽量階梯之間的落差相對越小，同階梯內的商品越多。
是否是用于淘寶流量分配規則的原因造成的？（假設淘寶的規則是給所有商品分配的初始流量是一樣的，后期這些商品中那些商品轉化率高就給哪些商品更多曝光。）
瀏覽量TOP100的商品所屬類目中，4756105、3607361、4357323三個類目瀏覽量遠超其他類目。
這個幾個類目商品類型是否是高頻剛需類型的呢？
6）用戶喜歡什么商品？
分析思路：
找高轉化率的商品（銷量高的有可能只是低價或者流量大）。
SQL提數：
查詢計算商品轉化率，升序排列，取前100個。
Excel可視化：
描述性分析：
從商品看：有17款商品轉化率超過了1。
是否是由于用戶直接從購物車或者商品收藏直接復購，未點擊商詳？
從類目看：這些商品所屬類目分布均勻，除965809、4801426、2735466、2640118、5063620、4789432、2945933這7個類目之外，其他類目都只有一個商品在轉化率TOP100的商品中。
是否是由于淘寶是根據“同一類目下的高轉化商品”給用戶做推薦的？
7）怎么判斷哪些是高價值用戶 ？
分析思路：
用戶價值分析常用的分析方式是RFM模型。
RFM模型是3個指標的縮寫，最近一次消費時間（R）、消費頻率（F）、消費金額（M）。
然后給這三個指標根據價值分5個等級 ，進行打分計算分值和平均值，然后根據分值與平均值對比，分出“高”“中”“低”，綜合進行用戶分層。
本次分析中的R,F,M具體定義（僅用于演示分析方法，無實際業務參考價值）：
- R：根據用戶最近一次的購買時間與2017年12月3日之間的差值，判斷用戶最近一次消費時間間隔；
- F：將數據集中用戶在2017年11月25日至2017年12月3日9天時間內的購買次數作為消費頻率；
- M：由于本數據集中未包含購買金額字段，暫時排除此指標。
SQL取數與分析：
建立打分標準：先計算R,F的值，并排序，根據R,F值最大值和最小值得區間設計本次得打分標準。
關于打分標準：不同業務的用戶消費頻率、消費金額、精細化運營策略與成本……都是不同，一般常用”分位數“建立打分標準；由于SQL并不是專業得統計分析工具，計算分位數較為復雜，本次僅使用最大值和最小值的區間初略建立規則。
分位數：是指在統計學中把所有數值由小到大排列并分成幾等份，取處于對應幾個分割點位置的數值。
查詢并計算R，F值創建視圖：
引用RF數值表，分別查詢R,F的最大值和最小值：
結合人工瀏覽的建立打分標準：
消費時間間隔：在1~8區間內四等分。
消費頻率：由于人工 瀏覽時發現很少有超過20次購買的，故消費頻率在20以內四等分。
給R，F按價值打分：
計算價值的平均值：
用平均值和用戶分類規則表比較得出用戶分類：
查詢各類用戶數量：
Excel可視化：
由于缺失了商品價格部分的數據，本模塊暫時沒有分析結論。
5. 診斷分析
通過描述性分析得到可視化的數據后，我們一般會先看一下是否符合業務常識，如：假設一個頁面的UV（瀏覽人數）比PV（瀏覽次數）還高，那這個數據質量肯定是有問題的。
如果符合常識接下來我們會通過與行業平均數據和本產品的同比環比對比看是否正常，如果不正常就要找原因，設計解決方案，如果正常那就看是否有可以優化的地方。
1）診斷分析結論
我們首先來看一下這些描述性分析是否符合業務常識和指標是否正常：
a. 活躍曲線整體為上升狀態，同為周六日，12月2號，3號相比11月25日，26日活躍度更高。
正常：結合描述分析4中的活躍用戶的增長。
b. 用戶在周六周日相比其他時間更活躍。
正常：周六周日為休息日，用戶有更多時間來刷淘寶，反映在數據上就是活躍度的增加。
c. 一天內用戶活躍的最高峰期為21點。
正常：用戶在這個時間段有空閑，996的都下班啦~
d. 從2017年11月15日致2017年12月3日，活躍用戶新增38%。
還需驗證：如果是由于新注冊用戶或者老用戶召回策略帶來的增長符合常識，具體還需結合新注冊用戶數據和用戶召回策略數據做驗證。
e. 從2017年11月15日致2017年12月3日，活躍用戶次日留存增長18.67%，當日的活躍用戶留存也在快速增長，第七日留存比次日留存高18.56%。
不符合常識：因為從長期來看用戶都是會流失的，只是生命周期長短問題，而從淘寶的用戶行為來看同批用戶的存留數據竟然隨著時間的增加而增加。
假設場景可能是這樣的：用戶小A注冊了淘寶APP，第二天就不再登錄了，而第三天收到了淘寶的推薦提醒（APP消息、短信……）；在消息中發現了自己喜歡的商品，而且還有優惠下單買了，第四天又收到了淘寶的消息，還是自己喜歡的。
這里的具體數據還需要結合用戶生命周期運營的策略和數據做驗證。
f. 用戶從瀏覽到購買整體轉化率2.3%。
正常，根據之前了解到的電商數據，多種客單價的商品（幾十~幾千）在一起，整體轉化率在2%~3%之間，當然具體還需要結合歷史的同比，環比數據取看。
g. 用戶從瀏覽到購買的路徑主要有4條，路徑越長轉化率越低。
正常：從流量的角度，每多一個步驟就會多一些用戶流失這個符合常識。
h. 瀏覽量top100的商品瀏覽量呈階梯分布，越靠前的階梯之間的落差相對越大在這個階梯中的商品越少，越靠后商品瀏覽量階梯之間的落差相對越小，同階梯內的商品越多。
待驗證：假設淘寶會給高轉化的爆款商品更多的曝光，商品瀏覽量呈金字塔分布是正常的。
i. 瀏覽量TOP100的商品所屬類目中，4756105、3607361、4357323三個類目瀏覽量遠超其他類目。
還需驗證：抽取購買購買次數判斷這個幾個類目商品類型是否是高頻剛需類型的呢？
j. 從商品看：有17款商品轉化率超過了1。
不正常：
還需驗證：是否是由于用戶直接從購物車或者商品收藏直接復購，未點擊商詳？
k. 從類目看：這些商品所屬類目分布均勻，除965809，4801426，2735466，2640118，5063620，4789432，2945933這7個類目之外，其他類目都只有一個商品在轉化率TOP100的商品中。
還需驗證：是否是由于淘寶是根據“同一類目下的高轉化商品”給用戶做推薦的？
2）假設與驗證
根據以上診斷分析我們梳理出了以下假設，做假設驗證。
假設1：這些商品中有高轉化率的爆款商品。
引用“商品轉化率視圖”查詢排名前5的商品轉化率：
對比同類目的其他商品轉化率：
對比瀏覽量TOP5的商品，發現這些商品轉化率在同一類目下并不高，假設不成立。
假設2：4756105，3607361，4357323三個類目屬于高頻剛需類目。
抽取這幾個類目的商品某買頻次數據驗證。
創建類目購買頻次表：
計算類目購買頻次平均值：
查詢4756105、3607361、4357323三個類目的購買頻次：
4756105、3607361、4357323三個類目的用戶購買頻次明顯高于平均值，假設成立。
假設3：有部分用戶是未點擊商詳直接從收藏和購物車購買的。
查詢轉化率超過1的商品的用戶行為數據：
用戶不是直接從收藏和購物車購買的，只是后續復購未點擊商詳，假設不成立。
假設4：淘寶推薦的商品主要是“同一類目下的高轉化商品”。
給瀏覽量TOP100的商品和轉化率TOP100的商品做匹配看其中重合的商品有多少。
用Excel對瀏覽量TOP100的商品ID和轉化率TOP100的商品ID進行去重，結果無重復值，假設不成立。
3）結論：
用戶活躍：用戶活躍曲線整體呈上升趨勢，在一周中周六，周日活躍度比平時更高；在一天中用戶活躍曲線從凌晨4點開始往上升，在中午12點和下午5~6點有兩個小低谷（吃飯），到晚上9點時活躍度達到頂峰。
用戶留存：從2017年11月15日致2017年12月3日的用戶留存數據來看，淘寶的用戶留存數據較好，活躍用戶次日留存增長18.67%；當日的活躍用戶留存也在快速增長，第七日留存比次日留存高18.56%。
用戶轉化：整體轉化2.3%，用戶從瀏覽到購買的路徑主要有4條，路徑越長轉化率越低。
- 路徑1：瀏覽→購買：轉化率1.45%；
- 路徑2：瀏覽→加購物車→購買：轉化率0.33；
- 路徑3：瀏覽→收藏→購買：轉化率0.11%；
- 路徑4：瀏覽→收藏→加購物車→購買：轉化率0.03%；
平臺推薦與用戶偏好：從數據集中的數據來看，排除用戶興趣偏好標簽，淘寶給用戶用戶推送的商品主要是高頻剛需的類目，促使用戶復購，流量回流平臺。
以上結論受數據量和數據類型的影響，并不一定準確，僅用來練習數據分析方法。
作者：小叮當，微信：zxxp153，公眾號：叮當的成長地圖
本文由 @小叮當v1.6 原創發布于人人都是產品經理。未經許可，禁止轉載。
題圖來自Unsplash，基于CC0協議
這就是埋點，增刪改查！
學習了
請問0基礎學sql可能嗎
還沒看完，我果斷登錄、關注、收藏、點贊
學習了
請問數據源有鏈接嗎
大牛
干貨，學習到了！??！
??