1.分析背景
淘寶、京東、亞馬遜等主流電商平臺逐漸成為了我們日常生活中經常接觸的平臺，而用戶的購買行為的數據則成為了分析用戶習慣、特征的最可靠的數據來源。本文主要通過Mysql對龐雜的用戶數據進行分析，探討用戶行為體現出的購買趨勢，并通過AARRR漏斗模型等方法對數據進行研究，結合excel進行可視化分析，為商家提供有理論、數據支撐的運營建議和策略參考。
2.研究問題
本文將對以下研究問題進行分析探討：
商品方面：
哪些商品銷量最好?分析熱銷商品的類別和具體的商品ID。
這些商品的購買數據有何特征?哪些商品的轉化率較高、哪些較低?
用戶方面：
各階段用戶的轉化率如何?是什么原因導致了各階段的轉化率較高/較低?
核心付費用戶占比多少?核心付費用戶更喜愛購買哪些商品?
用戶的購買時間有什么規律?從每天、每周的角度考慮是否存在顯著的規律?
本文中涉及、使用的指標體系如下圖所示：
3. 數據介紹
3.1.數據來源
本文中使用的淘寶用戶數據來源于天池，包含2017年11月25日至2017年12月3日之間，有行為的約一百萬隨機用戶的所有行為(行為包括點擊、購買、加購、喜歡)。
User Behavior Data from Taobao for Recommendation-數據集-阿里云天池?tianchi.aliyun.com/dataset/dataDetail?dataId=649&userId=1
3.2.數據介紹
在本文中，我們選取了前10萬條數據，詳細的字段信息，數據大小如下表所示：
其中，用戶的行為類型共4種，分別為：pv(瀏覽商品詳情頁)、buy(購買商品)、cart(將商品放入購物車)、fav(收藏商品)
3.3 數據處理
3.3.1 日期數據處理
首先我們需要將epoch格式的時間戳數據分開處理為“日期”和“時間”兩列，使用的sql語句如下所示：
#在userbehavior表增加date和time兩列字段：
alter table userbehavior add date char(10) not null;
alter table userbehavior add time char(10) not null;
#將date和time設置來源為時間戳數據并給定數據格式：
update userbehavior set date=from_unixtime(timestamps,'%y-%m-%d');
update userbehavior set time=from_unixtime(timestamps,'%H:%i:%s');
結果如圖所示：
3.3.2 缺失值、異常值處理
(1)首先我們檢查是否有不在給定日期范圍的數據：
SELECt
*
FROM
userbehavior
WHERe
date < '17-11-25' OR date > '17-12-03';
結果顯示共有44條記錄符合給定的sql查詢語句，之后我們將這些記錄刪除。
如上圖所示，成功刪去了不在給定時間范圍的異常值;
(2)使用如下語句，通過更改列名排查是否存在含有空值的列：
SELECt
*
FROM
userbehavior
WHERe
user_id = NULL;
結果顯示剩余的記錄中不存在含有空值的列;
(3)由于原始數據中包含用戶的所有相關行為，所以同一用戶id下可能包含有多條信息，因此這里我們不對重復值進行處理。
4.AARRR模型指標分析
每日活躍點擊量PV
首先我們通過如下sql語句對每日活躍點擊量進行統計
SELECt
date,
count(*) AS PV
FROM
userbehavior
WHERe
behavior = 'PV'
GROUP BY
date
ORDER BY
date ASC;
結果導入excel并繪成柱狀圖后如下所示
從中我們可以發現12月2日、3日的瀏覽量較高，結合下圖日期對應的星期數我們預測這兩日的較高數值是因為正值周末，主要購買群體上班族、學生都有足夠的時間瀏覽、購買。之后我們會結合每日訪客量UV和平均訪問量一同分析。
每日訪客量UV
我們使用如下語句對每日有記錄的用戶id進行統計：
SELECt
date,
count( DISTINCT user_id ) AS UV
FROM
userbehavior
GROUP BY
date
ORDER BY
date ASC;
結果導入excel并繪成柱狀圖后如下所示：
平均訪問量PV/UV
通過計算每日的PV/UV的值，我們得到了如下平均訪問量的折線圖：
對應到UV,平均訪問量中我們可以發現2017年12月2日、3日的各項指標同樣較為突出，我們首先懷疑這是由于周末節假日用戶的瀏覽、購買意愿較強，但是同樣是周末的11月25日、26日相較于其他日期并不突出，受限于數據的有效時間范圍，我們沒有更多的日期數據以供分析，但是通過查詢對應日期的信息，我們發現，2017年12月1日-6日正值淘寶“服飾煥新”活動，再次觀察數據，我們發現不管是UV,PV,還是平均訪問量，我們都可以看到12月1日之后各項指標的增長，因此，盡管周末假期可能是導致指標增長的原因，我們更傾向于是節日活動導致了數據的增長。
每小時點擊量
為分析不同時間段點擊量是否存在差異，我們將24小時的點擊量通過如下語句分別匯總：
SELECt
sum( CASE WHEN time BETWEEN '00:00:00' AND '00:59:59' THEN 1 ELSE 0 END ) AS '00',
sum( CASE WHEN time BETWEEN '01:00:00' AND '01:59:59' THEN 1 ELSE 0 END ) AS '01',
sum( CASE WHEN time BETWEEN '02:00:00' AND '02:59:59' THEN 1 ELSE 0 END ) AS '02',
sum( CASE WHEN time BETWEEN '03:00:00' AND '03:59:59' THEN 1 ELSE 0 END ) AS '03',
sum( CASE WHEN time BETWEEN '04:00:00' AND '04:59:59' THEN 1 ELSE 0 END ) AS '04',
sum( CASE WHEN time BETWEEN '05:00:00' AND '05:59:59' THEN 1 ELSE 0 END ) AS '05',
sum( CASE WHEN time BETWEEN '06:00:00' AND '06:59:59' THEN 1 ELSE 0 END ) AS '06',
sum( CASE WHEN time BETWEEN '07:00:00' AND '07:59:59' THEN 1 ELSE 0 END ) AS '07',
sum( CASE WHEN time BETWEEN '08:00:00' AND '08:59:59' THEN 1 ELSE 0 END ) AS '08',
sum( CASE WHEN time BETWEEN '09:00:00' AND '09:59:59' THEN 1 ELSE 0 END ) AS '09',
sum( CASE WHEN time BETWEEN '10:00:00' AND '10:59:59' THEN 1 ELSE 0 END ) AS '10',
sum( CASE WHEN time BETWEEN '11:00:00' AND '11:59:59' THEN 1 ELSE 0 END ) AS '11',
sum( CASE WHEN time BETWEEN '12:00:00' AND '12:59:59' THEN 1 ELSE 0 END ) AS '12',
sum( CASE WHEN time BETWEEN '13:00:00' AND '13:59:59' THEN 1 ELSE 0 END ) AS '13',
sum( CASE WHEN time BETWEEN '14:00:00' AND '14:59:59' THEN 1 ELSE 0 END ) AS '14',
sum( CASE WHEN time BETWEEN '15:00:00' AND '15:59:59' THEN 1 ELSE 0 END ) AS '15',
sum( CASE WHEN time BETWEEN '16:00:00' AND '16:59:59' THEN 1 ELSE 0 END ) AS '16',
sum( CASE WHEN time BETWEEN '17:00:00' AND '17:59:59' THEN 1 ELSE 0 END ) AS '17',
sum( CASE WHEN time BETWEEN '18:00:00' AND '18:59:59' THEN 1 ELSE 0 END ) AS '18',
sum( CASE WHEN time BETWEEN '19:00:00' AND '19:59:59' THEN 1 ELSE 0 END ) AS '19',
sum( CASE WHEN time BETWEEN '20:00:00' AND '20:59:59' THEN 1 ELSE 0 END ) AS '20',
sum( CASE WHEN time BETWEEN '21:00:00' AND '21:59:59' THEN 1 ELSE 0 END ) AS '21',
sum( CASE WHEN time BETWEEN '22:00:00' AND '22:59:59' THEN 1 ELSE 0 END ) AS '22',
sum( CASE WHEN time BETWEEN '23:00:00' AND '23:59:59' THEN 1 ELSE 0 END ) AS '23'
FROM
userbehavior
WHERe
behavior = 'PV';
將結果導出并整理后我們得到了如下所示的折線圖：
在對分時點擊量進行分析時，我們可以看到每日3點為最低值，21點左右為最高值。這與通常人作息習慣大致相同，21點左右夜間消費者的瀏覽欲望最強，而在3點左右，基本的用戶群體，不論是學生、上班族還是中老年人基本在這個時間段已經開始休息。因此我們繼續了解了淘寶的推薦機制，常見的推廣方式包括直通車、鉆展、交換友情鏈接、寶貝類目優化、社交平臺推廣、自然搜索排名等，絕大部分都可以設置開始推送的時間，并且針對包含一定共同特征的群體進行定向推廣，因此參考上文的結論，我們可以提出如下建議，在上架、下架時間上應考慮到瀏覽集中密集的時段，在設定目標群體時，若主要客戶群體為上班族，應考慮在周末，以及工作日晚九點左右進行推廣。
漏斗模型
首先，使用如下sql語句對4種不同的用戶行為進行計數統計：
SELECt
behavior,
count(*)
FROM
userbehavior
GROUP BY
behavior
ORDER BY
count(*) DESC;
將結果轉化為漏斗圖并計算相應占比后得到如下圖表：
其中，圖片左側為對應的用戶行為，模型中白色數字為具體行為數，白色數字左側占比為該類行為占所有行為總數的比重，右側占比為該列行為占上列行為的比重，即我們通常說的轉化率，從圖中我們可以看出，購買行為占整體比重約為2.1%，占瀏覽行為的比重約為2.34%，根據調查，排除行業因素對轉化率的影響，大型電商平臺的轉化率一般都在1%-3%左右，但是考慮到電商平臺相較于實體店鋪的特殊性，即用戶很難在購買前實際體驗商品，并且電商平臺為用戶提供了更多的商品選擇，這一結果大致是合理的，因此我們認為針對數據中2%左右的轉化率，商家應該關注于自身店鋪的各項方面以提高對顧客的吸引力，比如商家應注意自身店鋪的包裝、宣傳、關于免運費的設定、店鋪宣傳圖、商品詳情頁的構建等方面。
獨立用戶行為統計
注意到上文我們并沒有統計獨立用戶個數，而是統計了所有用戶行為，某一用戶可能既瀏覽，又繼續收藏、喜愛，最后購買。因此我們繼續研究相應的獨立用戶行為統計，sql查詢語句如下所示：
SELECt
behavior,
count( DISTINCT user_id )
FROM
userbehavior
GROUP BY
behavior
ORDER BY
count( DISTINCT user_id ) DESC;
將得到的結果導出并處理后，我們得到了如下圖表：
從獨立用戶數目統計來看，有購買行為的用戶占總用戶數的占比約為24.51%，占瀏覽用戶數的占比約為68.47%，這證明了絕大部分用戶(大于50%)都有或多或少的購買行為，這說明用戶并非只在淘寶平臺瀏覽而并不購買，因此我們認為，平臺若要提升轉化率，應進一步完善商品搜索機制和商品排序，提升用戶的搜索體驗，提供更智能的匹配結果。
跳失率
在獨立用戶行為統計的基礎上，我們需進一步通過如下語句統計只有瀏覽的用戶數：
SELECt
count( DISTINCT user_id )
FROM
userbehavior
WHERe
user_id NOT IN ( SELECt DISTINCT user_id FROM userbehavior WHERe behavior = 'fav' )
AND user_id NOT IN ( SELECt DISTINCT user_id FROM userbehavior WHERe behavior = 'cart' )
AND user_id NOT IN ( SELECt DISTINCT user_id FROM userbehavior WHERe behavior = 'buy' );
結果證明共有69個用戶僅有瀏覽行為，考慮到用戶總數為983.我們可以計算出對應的跳失率=只有瀏覽行為的用戶數/用戶總數 = 7.02%，之后我們進一步統計了復購率，有購買行為的用戶數為671.有多次購買的用戶數為442.復購率為65.87%，百分之六十五的用戶存在復購行為，可見轉化率低并不是用戶黏性降低導致的。
熱銷商品類別和商品ID分析
a.熱銷商品類別、用戶群體分析：
首先我們進一步通過如下語句對商品銷售數量進行了研究：
SELECt
category_id,
count(category_id)
FROM
userbehavior
GROUP BY
category_id
ORDER BY
count(category_id) DESC;
在將結果導出并整理后我們得到了如下所示的商品銷售數量前十的商品類別id以及對應的銷量：
第二，我們使用了如下語句對用戶的購買數量同樣進行了統計排序，相應的語句，圖標如下所示：
SELECt
user_id,
count(user_id)
FROM
userbehavior
WHERe
behavior = 'buy'
GROUP BY
user_id
ORDER BY
count(user_id) DESC
第三，我們還可以分析購買次數較多的用戶主要購買哪類商品：
SELECt
category_id,
count( category_id )
FROM
userbehavior
WHERe
behavior = 'buy'
AND user_id IN ( '1003983', '1003901', '100101', '1000488', '1000723', '1002031', '1001305', '1001866', '100134', '100116' )
GROUP BY
category_id
ORDER BY
count( category_id ) DESC;
總結：我們需要額外重視ID為3002561的商品類別，雖然該類商品在總計商品銷量排名中位于第十位，但是該類商品卻是銷量較多用戶購買數量最多的商品，可見該類商品的用戶黏性較強，復購率較高。我們還應注意4145813、2355072這兩類商品，也為在“商品銷量排名前十”和“較多購買用戶購買的商品前十”兩表中重復出現的商品類別。除此以外，如果有關于用戶的具體信息，還可以進一步對用戶群體進行畫像，為下一步精準推送提供幫助，該處更多用戶信息有待補充。
b. 熱銷商品分析
首先我們使用如下語句對各種商品的瀏覽次數進行統計：
SELECt
item_id,
count( item_id )
FROM
userbehavior
WHERe
behavior = 'PV'
GROUP BY
item_id
ORDER BY
count( item_id ) DESC;
將得到的結果導出到EXCEL并繪制成圖標后我們得到下圖：
之后我們使用如下語句對購買量前十的商品ID同樣進行統計并繪圖：
SELECt
item_id,
count( item_id )
FROM
userbehavior
WHERe
behavior = 'buy'
GROUP BY
item_id
ORDER BY
count( item_id ) DESC;
通過比較上述語句得到的圖表，我們可以看出瀏覽量較高的商品并沒有出現在購買數量前十商品中，因此我們需要進一步分析是什么原因導致了這些商品瀏覽量高但是購買量并不出色，以及購買數量較高的商品，其轉化率較高的原因，這里我們推測瀏覽量較多的商品應該商品詳情頁，商品縮略圖較為精致，但是可能價格偏高，售后，商品質量存在問題，這些推斷有待更多詳細的數據以進一步分析。
5. 結論
a.用戶方面：從瀏覽到收藏、喜愛的轉化率為9.13%，從瀏覽到購買的轉化率為2.34%，但是主要瀏覽、購買的時間大多為工作日的睡覺前和周末，因此我們認為商家應關注商品介紹頁面的完善，多參與淘寶的各項活動、多渠道增加自身產品推廣，并且最好將推廣時間設定為用戶瀏覽最多的時段，以提高自身產品的吸引力，增強用戶的購買欲望。絕大部分用戶都有復購行為，但是用戶行為以瀏覽為主，轉化率并不高，因此除商家外，我們認為對于平臺也應進一步完善競價排名和商品推薦機制。
b.商品方面，我們注意到核心用戶群體(用戶ID分別為 '1003983', '1003901', '100101', '1000488', '1000723', '1002031', '1001305', '1001866', '100134', '100116' )喜愛的3002561、4145813和2355072這三類商品也同時出現在商品銷售量前十的圖表中，因此我們認為接下來的研究可以進一步這三類商品進行分析，探討其購買率高的原因。而對于具體的商品，1910706、4395247、667682、855191也是值得研究的對象。
6.建議
建議賣家注重以下幾個方面：商品縮略圖、商品詳情頁、商品評價的維護、商品質量的保證、退換商品的及時處理，從而增強產品自身的吸引力，提高用戶從瀏覽到購買的轉化率。
建議商家在購買推廣時最好選擇每日的晚間和每周周末進行，這些時段是用戶瀏覽的高峰期，我們認為可以有助于提升購買率;
平臺也應關注于用戶群體的意見和看法，積極完善平臺的搜索、推薦機制，避免用戶花費大量時間瀏覽;
商家應注意3002561、4145813和2355072這三類商品以及1910706、4395247、667682、855191這四種具體商品的推廣方法和運營細則，以降低跳失率。
商家應注意參加平臺組織的各種促銷活動，數據證明了這些活動的強大引流能力。
通過后續對用戶資料的研究，商家還應關注于 '1003983', '1003901', '100101', '1000488', '1000723', '1002031', '1001305', '1001866', '100134', '100116' 這十位用戶ID的共同特征，為核心群體畫像提供參考。
快消品貨源網提供超市貨源信息,超市采購進貨渠道。超市進貨網提供成都食品批發,日用百貨批發信息、微信淘寶網店超市采購信息和超市加盟信息.打造國內超市采購商與批發市場供應廠商搭建網上批發市場平臺,是全國批發市場行業中電子商務權威性網站。
本文來源： 淘寶用戶數據分析報告
