SQL數據分析淘寶用戶分析實操
- 2019 年 12 月 9 日
- 筆記
sql也能做分析？
常見的數據清洗，預處理，數據分類，數據篩選，分類匯總，以及數據透視等操作，用SQL一樣可以實現（除了可視化，需要放到Excel里呈現）。SQL不僅可以從資料庫中讀取數據，還能通過不同的SQL函數語句直接返回所需要的結果，從而大大提高了自己在客戶端應用程式中計算的效率。
但是，這個過程需要很熟練掌握SQL！
本文就利用提取MySQL的數據，通過寫SQL的數據處理方式，來對一份淘寶數據進行用戶分析。
01. 數據來源及說明
本文從數據集中選取包含了2014年11月18日至2014年12月18日之間，8477名隨機用戶共1048575條行為數據，數據集的每一行表示一條用戶行為，共6列。
列欄位包含以下：
user_id：用戶身份 item_id：商品ID behavior_type：用戶行為類型（包含點擊、收藏、加購物車、購買四種行為，分別用數字1、2、3、4表示） user_geohash：地理位置（有空值） item_category：品類ID（商品所屬的品類） time：用戶行為發生的時間
02. 提出問題
1. 整體用戶的購物情況
pv（總訪問量）、日均訪問量、uv（用戶總數）、有購買行為的用戶數量、用戶的購物情況、復購率分別是多少？
2. 用戶行為轉化漏斗
點擊— 加購物車— 收藏— 購買各環節轉化率如何？購物車遺棄率是多少，如何提高？
3. 購買率高和購買率為 0 的人群有什麼特徵
4. 基於時間維度了解用戶的行為習慣
5. 基於RFM模型的用戶分析
03. 數據清洗
1. 導入數據
由於數據量有100多萬，通過資料庫管理工具 Navicat 將數據集導入 MySQL 資料庫會表較慢，我這裡使用ETL工具kettle進行導數，能夠提高導數效率，也方便後續實現報表自動化處理，資料庫的表名為user。
2. 缺失值處理
item_category 列表示地理位置資訊，由於數據存在大量空值，且位置資訊被加密處理，難以研究，因此後續不對item_category列進行分析。
3. 數據一致化處理
由於 time 欄位的時間包含（年-月-日）和小時，為了方便分析，將該欄位分成 2 個欄位，一個日期列（date）和一個小時列（time）。
{!– PGC_COLUMN –}
由於 behavior_type 列的四種行為類型分別用 1，2，3，4 表示點擊、收藏、加購物車、購買四種行為，為了方便查看數據，將1，2，3，4替換為 『pv'、』fav『，』cart'，『buy' 。
通過查詢表結構，可以看到 date 列日期列不是日期類型：
將date 列改成 date 類型：
04. 構建模型和分析問題
1. 總體用戶購物情況
- pv（總訪問量）
- 日均訪問量
- uv（用戶總數）
- 有購買行為的用戶數量
- 用戶的購物情況
- 復購率：產生兩次或兩次以上購買的用戶占購買用戶的比例
2. 用戶行為轉化漏斗
在購物環節中收藏和加入購物車兩個環節沒有先後之分，所以將這兩個環節可以放在一起作為購物環節的一步。最終得到用戶購物行為各環節轉化率，如下：
不同的行業轉化率會有差異，據2012年的一項研究表明，在整個互聯網範圍內，平均轉化率為2.13%（數據來源於《精益數據分析》），圖中所示購買行為的轉化率為1.04%，與行業平均值存在較大差異，淘寶移動端用戶行為的轉化率還有很大的增長空間。
3. 購買率高和購買率為低的人群有什麼特徵
購買率高用戶特徵：
由以上結果可以看出，購買率高的用戶點擊率反而不是最多的，這些用戶收藏數和加購物車的次數也很少，一般不點擊超過5次就直接購買，由此可以推斷出這些用戶為理智型消費者，有明確的購物目標，屬於缺啥買啥型，很少會被店家廣告或促銷吸引。
購買率為低用戶特徵：
由以上結果可以看出，購買率為低用戶分為兩類，
第一類是點擊次數少的，一方面的原因是這類用戶可能是不太會購物或者不喜歡上網的用戶，可以加以引導，另一方面是從商品的角度考慮，是否商品定價過高或設計不合理；
第二類用戶是點擊率高、收藏或加購物車也多的用戶，此類用戶可能正為商家的促銷活動做準備，下單慾望較少且自制力較強，思慮多或者不會支付，購物難度較大。
4. 基於時間維度了解用戶的行為習慣
(1)一天中用戶的活躍時段分布
可以看出，每日0點到5點用戶活躍度快速降低，降到一天中的活躍量最低值，6點到10點用戶活躍度快速上升，10點到18點用戶活躍度較平穩，17點到23點用戶活躍度快速上升，達到一天中的最高值。
（2）一周中用戶活躍時段分布
由於第一周和第五周的數據不全，因此這兩周的數據不考慮到此次數據分析中。
由以上結果可以看出，每周用戶活躍度較穩定，每周五活躍度會有小幅降低，但是周末會慢慢回升。其中周五用戶活躍度突增，這是由雙十二電商大促銷活動引起。
5. 基於 RFM 模型找出有價值的用戶
RFM模型是衡量客戶價值和客戶創利能力的重要工具和手段，其中由3個要素構成了數據分析最好的指標，分別是：
- R-Recency（最近一次購買時間）
- F-Frequency（消費頻率）
- M-Money（消費金額）
由於數據源沒有相關的金額數據，暫且通過 R 和 F 的數據對客戶價值進行打分。
（1）計算R-Recency
由於數據集包含的時間是從2014年11月18日至2014年12月18日，這裡選取2014年12月19日作為計算日期，統計客戶最近發生購買行為的日期距離2014年12月19日間隔幾天，再對間隔時間進行排名，間隔天數越少，客戶價值越大，排名越靠前。
（2）計算F-Frequency
先統計每位用戶的購買頻率，再對購買頻率進行排名，頻率越大，客戶價值越大，排名越靠前。
（3）對用戶進行評分
對4330名有購買行為的用戶按照排名進行分組，共劃分為四組，對排在前四分之一的用戶打4分，排在前四分之一到四分之二（即二分之一）的用戶打3分，排在前四分之二到前四分之三的用戶打2分，剩餘的用戶打1分，按照這個規則分別對用戶時間間隔排名打分和購買頻率排名打分，最後把兩個分數合併在一起作為該名用戶的最終評分。
計算腳本如下：
''' SELECT r.user_id,r.recent,r.recent_rank,f.frequency,f.freq_rank, CONCAT( -- 對客戶購買行為的日期排名和頻率排名進行打分 CASE WHEN r.recent_rank <= (4330/4) THEN '4' WHEN r.recent_rank > (4330/4) AND r.recent_rank <= (4330/2) THEN '3' WHEN r.recent_rank > (4330/2) AND r.recent_rank <= (4330/43) THEN '2' ELSE '1' END, CASE WHEN f.freq_rank <= (4330/4) THEN '4' WHEN f.freq_rank > (4330/4) AND f.freq_rank <= (4330/2) THEN '3' WHEN f.freq_rank > (4330/2) AND f.freq_rank <= (4330/43) THEN '2' ELSE '1' END ) AS user_value FROM -- 對每位用戶最近發生購買行為的間隔時間進行排名（間隔天數越少，客戶價值越大） (SELECT a.,(@rank := @rank + 1) AS recent_rank FROM -- 統計客戶最近發生購買行為的日期距離'2014-12-19'間隔幾天 (SELECT user_id,DATEDIFF('2014-12-19',MAX(date)) AS recent FROM user WHERE behavior_type = 'buy' GROUP BY user_id ORDER BY recent) AS a, (SELECT @rank := 0) AS b) AS r, -- 對每位用戶的購買頻率進行排名（頻率越大，客戶價值越大） (SELECT a.,(@rank2 := @rank2 + 1) AS freq_rank FROM -- 統計每位用戶的購買頻率 (SELECT user_id,COUNT(behavior_type) AS frequency FROM user WHERE behavior_type = 'buy' GROUP BY user_id ORDER BY frequency DESC) AS a, (SELECT @rank2 := 0) AS b) AS f WHERE r.user_id = f.user_id; '''
通過打分可以了解每位顧客的特性，從而實現差異化營銷。比如對於 user_value = 44 的用戶，為重點用戶需要關注；對於user_value = 41 這類忠誠度高而購買能力不足的，可以可以適當給點折扣或捆綁銷售來增加用戶的購買頻率。
（4）kettle製作報表自動化
為了實現監測每個月用戶的評分變化，推測客戶消費的異動狀況，結合資料庫使用ETL工具kettle實現定時輸出評分結果：
這裡kettle實現過程不做詳細描述。
05. 結論
1. 總體轉化率只有 1%，用戶點擊後收藏和加購物車的轉化率在 5% ，需要提高用戶的購買意願，可通過活動促銷、精準營銷等方式。
2. 購買率高且點擊量少的用戶屬於理智型購物者，有明確購物目標，受促銷和廣告影響少；而購買率低的用戶可以認為是等待型或克制型用戶群體，下單慾望較少且自制力較強，購物難度較大。
3. 大部分用戶的主要活躍時間在10點到23點，在19點到23點達到一天的頂峰。每周五的活躍度有所下降，但周末開始回升。可以根據用戶的活躍時間段精準推送商家的折扣優惠或促銷活動，提高購買率。
4. 通過 R 和 F 的數據對用戶行為進行打分，對每位用戶進行精準化營銷，還可以通過對R 和 F 的數據監測，推測客戶消費的異動狀況，挽迴流失客戶。
End.
作者：數據分析不是個事兒 來源：簡書
