電商用戶行為數據分析報告

時間：2023-03-15 23:34:01 | 來源：電子商務

時間：2023-03-15 23:34:01 來源：電子商務

一、背景目標

近兩年受疫情影響，居家辦公、居家隔離、足不出戶的情況屢見不鮮，且線上電子商務行業(yè)飛速發(fā)展，越來越多的用戶通過線上網購滿足需求。現使用MySQL提取關于淘寶在2017年11月25日至2017年12月3日的部分用戶數據，分析和研究其用戶行為，針對用戶流失情況提出建議，并通過RFM模型提出相應的運營策略。

二、數據處理

2.1 檢查重復值：未發(fā)現重復值。

SELECT * FROM UserBehavior_newGROUP BY user_id, item_id, cat_id, behavior, timestampsHAVING COUNT(*) > 1;2.2 檢查缺失值：未發(fā)現缺失值。

SELECT COUNT(user_id), COUNT(item_id), COUNT(cat_id), COUNT(behavior), COUNT(timestamps)FROM UserBehavior_newWHERE user_id IS NULLOR item_id IS NULLOR cat_id IS NULLOR behavior IS NULLOR timestamps IS NULL;2.3 預處理：

-- 1.將時間戳轉化為日期格式ALTER TABLE UserBehavior_new ADD COLUMN datetime VARCHAR(256);UPDATE UserBehavior_new SET datetime = FROM_UNIXTIME(timestamps);ALTER TABLE UserBehavior_new ADD COLUMN date VARCHAR(256);UPDATE UserBehavior_new SET date = FROM_UNIXTIME(timestamps, '%Y-%m-%d');ALTER TABLE UserBehavior_new ADD COLUMN time VARCHAR(256);UPDATE UserBehavior_new SET time = FROM_UNIXTIME(timestamps, '%H-%i-%s');-- 2.確保所有用戶行為發(fā)生在2017/11/25-2017/12/03期間SELECT date FROM UserBehavior_new WHERE date < '2017-11-25' or date > '2017-12-03';-- 刪除異常數據DELETE FROM UserBehavior_new WHERE date < '2017-11-25' or date > '2017-12-03';三、數據分析

3.1 用戶行為數據分析

-- 1.頁面瀏覽量（PV）：895636SELECT COUNT(behavior) AS PV FROM UserBehavior_new WHERE behavior = 'pv';-- 2.頁面訪客數（UV）：9739SELECT COUNT(DISTINCT user_id) AS UV FROM UserBehavior_new;-- 3.平均頁面訪客數 = PV/UV ：92-- 4.以日期為基準對各指標變化情況的分析CREATE VIEW UserBehavior_date AS SELECT date, SUM(CASE WHEN behavior = 'pv' THEN 1 ELSE 0 END) AS '瀏覽量',SUM(CASE WHEN behavior = 'cart' THEN 1 ELSE 0 END) AS '加購量',SUM(CASE WHEN behavior = 'fav' THEN 1 ELSE 0 END) AS '收藏量',SUM(CASE WHEN behavior = 'buy' THEN 1 ELSE 0 END) AS '購買量'FROM UserBehavior_newGROUP BY dateORDER BY date; 根據以上數據和圖表，可觀察到每日的瀏覽量、加購量和收藏量變化趨勢基本相同，結合用戶購買路徑，可說明加購量和收藏量的增加是由瀏覽量的增加促進的。如果要提升用戶的加購、收藏量，可以關注新增用戶方面；但是，購買量的變化趨勢有所不同，在周中相對高于其他、周末低于其他，說明用戶一般在周末瀏覽商品，在周中買商品的情況較多；周末各指標相對值都較高，說明用戶在周末的行為更活躍，可能與用戶的工作、學習、放假時間相關，可以在周末增加產品推薦策略、提高產品曝光量等，從而促進用戶購買行為。

2017年11月25日和12月1、2日都是周末時間。但對比11月25日的數據指標，12月2日的數據指標是明顯上漲，結合即將到來的雙十二活動，可能與雙十二預熱活動的營銷效果有關。

-- 5.以時間為基準對各指標變化情況的分析CREATE VIEW UserBehavior_time AS SELECT time,SUM(CASE WHEN behavior = 'PV' THEN 1 ELSE 0 END) AS '瀏覽量',SUM(CASE WHEN behavior = 'cart' THEN 1 ELSE 0 END) AS '加購量',SUM(CASE WHEN behavior = 'fav' THEN 1 ELSE 0 END) AS '收藏量',SUM(CASE WHEN behavior = 'buy' THEN 1 ELSE 0 END) AS '購買量'FROM UserBehavior_newGROUP BY timeORDER BY time; 根據以上數據和圖表，可觀察到每小時的各指標變化基本相同。0-7點使用淘寶的用戶處于低峰，符合人們正常作息時間規(guī)律；10-16點的用戶活躍度相對較高，且購買量相比其他指標較高，可能處于工作學習狀態(tài)的用戶沒有時間進行瀏覽，故對瀏覽過的、已經加入購物車的或收藏的商品進行購買；20-22點達到一天的高峰值，可能與用戶行為習慣或睡前購物心理有關，這個時間大多是用戶已在家休息放松、有時間逛淘寶，可以在這個時段推廣運營活動、推送促銷商品、發(fā)送商品相關信息等刺激用戶購買。

-- 6.用戶整體行為SELECT SUM(總量) AS 總量, SUM(瀏覽量) AS 瀏覽量, SUM(加購量) AS 加購量,SUM(收藏量) AS 收藏量, SUM(購買量) AS 購買量 FROM UserBehavior_view;SELECT CONCAT(ROUND(SUM(瀏覽量)/SUM(總量)*100,2),'%') AS 瀏覽轉化率,CONCAT(ROUND((SUM(加購量)+SUM(收藏量))/SUM(總量)*100,2),'%') AS '加購/收藏轉化率',CONCAT(ROUND(SUM(購買量)/SUM(總量)*100,2),'%') AS 購買轉化率FROM UserBehavior_view; 根據用戶整體行為，點擊率為89.61%，而加購/收藏轉化率8.36%、購買轉化率2.04%，說明用戶對網站內容感興趣，但轉化到下一行為的用戶數較少，需要進一步分析用戶流失原因。

-- 7.用戶各環(huán)節(jié)行為路徑SELECT SUM(CASE WHEN 瀏覽量 > 0 THEN 1 ELSE 0 END) AS '瀏覽量',SUM(CASE WHEN 瀏覽量 > 0 AND 購買量 > 0 AND 加購量 = 0 AND 收藏量 = 0 THEN 1 ELSE 0 END) AS '瀏覽→購買',SUM(CASE WHEN 瀏覽量 > 0 AND (加購量 > 0 OR 收藏量 > 0) THEN 1 ELSE 0 END) AS '瀏覽→加購/收藏',SUM(CASE WHEN 瀏覽量 > 0 AND 購買量 = 0 AND 加購量 = 0 AND 收藏量 = 0 THEN 1 ELSE 0 END) AS '瀏覽→流失',SUM(CASE WHEN 瀏覽量 > 0 AND (加購量 > 0 OR 收藏量 > 0) AND 購買量 > 0 THEN 1 ELSE 0 END) AS '瀏覽→加購/收藏→購買',SUM(CASE WHEN 瀏覽量 > 0 AND (加購量 > 0 OR 收藏量 > 0) AND 購買量 = 0 THEN 1 ELSE 0 END) AS '瀏覽→加購/收藏→流失'FROM UserBehavior_view; 根據以上數據和行為路徑圖，用戶通過瀏覽網站后無下一行為的流失占比僅為5.84%，說明平臺對用戶具有很大吸引力；"用戶瀏覽→加購/收藏"的轉化率較高，說明用戶會先將商品加入購物車或收藏、再考慮是否購買，可以提高促銷力度（如滿減、折扣活動）或者增強產品交互界面效果等方面引導用戶加購或收藏；"加購/收藏→購買"的引導轉化中仍有25.52%的用戶流失，需進一步分析用戶流失原因。

8.基于用戶行為路徑的流失分析

假設一：推薦的商品不是用戶想購買的？

-- 根據cat_id創(chuàng)建視圖CREATE VIEW pv_top10_cat ASSELECT cat_id, COUNT(*) AS 瀏覽量TOP10 FROM UserBehavior_newWHERE behavior = 'pv'GROUP BY cat_idORDER BY COUNT(*) DESCLIMIT 10;CREATE VIEW buy_top10_cat ASSELECT cat_id, COUNT(*) AS 購買量TOP10 FROM UserBehavior_newWHERE behavior = 'buy'GROUP BY cat_idORDER BY COUNT(*) DESCLIMIT 10;-- 根據item_id創(chuàng)建視圖CREATE VIEW pv_top10_item ASSELECT item_id, COUNT(*) AS 瀏覽量TOP10 FROM UserBehavior_newWHERE behavior = 'pv'GROUP BY item_idORDER BY COUNT(*) DESCLIMIT 10;CREATE VIEW buy_top10_item ASSELECT item_id, COUNT(*) AS 購買量TOP10 FROM UserBehavior_newWHERE behavior = 'buy'GROUP BY item_idORDER BY COUNT(*) DESCLIMIT 10; 驗證分析：根據caty_id對比分析瀏覽量TOP10和購買量TOP10，重合部分商品種類占比60%，重合度不高；根據item_id對比瀏覽量TOP10和購買量TOP10，重合度為0。綜上所述，用戶高瀏覽量不能帶來高購買量，即平臺推薦的商品不是用戶想買的，假設一成立。

假設二：提供的商品種類少？

SELECT COUNT(DISTINCT cat_id) AS 商品類目數量,COUNT(DISTINCT item_id) AS 商品數量FROM UserBehavior_new; 根據以上數據可知，一共有5793類商品，且涵蓋了398972種商品，可供用戶選擇的商品非常多，故假設二不成立。

假設三：推薦的商品不吸引用戶？

SELECT a.購買次數, COUNT(a.item_id) AS 商品數,CONCAT(ROUND(COUNT(a.item_id) / (SELECT COUNT(DISTINCT item_id) FROM UserBehavior_new WHERE behavior = 'buy') *100, 2), '%') AS 占比FROM (SELECT item_id, COUNT(*) AS 購買次數 FROM UserBehavior_new WHERE behavior = 'buy' GROUP BY item_id) AS aGROUP BY a.購買次數ORDER BY a.購買次數; 根據以上數據，購買1次的商品占比88.45%，即用戶選擇相同商品進行復購的情況很少，故商品對用戶的吸引力不強，假設三成立。

綜上所述，用戶流失的主要環(huán)節(jié)在“加購/收藏”中，其流失的原因與“推薦的商品不是用戶想購買的”、“推薦的商品不吸引用戶”有關，對此應優(yōu)化平臺的推薦機制，根據用戶的需求不斷迭代，吸引更多用戶進行復購。

3.2 產品數據分析

-- 1.總訂單量：20359-- 人均購買量 = 總訂單量/總用戶數 : 2.09SELECT COUNT(behavior) FROM UserBehavior_newWHERE behavior = 'buy';-- 2.復購率CREATE VIEW buy_f_view(user_id, frequency) ASSELECT user_id, COUNT(behavior) AS frequency FROM UserBehavior_newWHERE behavior = 'buy'GROUP BY user_idORDER BY frequency; 根據以上數據和圖表，產品銷售量主要分布在購買1~3次的用戶，其中購買次數為1次的用戶占比34%，該類客戶有較大發(fā)展空間；整體復購率為66%，可說明用戶與商品的粘性度較高。其中購買次數為2~3次的用戶占比40%，該類用戶有助于提高轉化率。

3.3 基于RFM模型的用戶價值分析

由于數據源未給出用戶消費相關金額，故本次只分析"R"和"F"兩個指標，即最近一次消費時間和購買頻率。再通過客戶相關指標與均值的大小關系將客戶分組。

-- 1.計算R、F值，并按價值打分CREATE VIEW rf AS SELECT a.*,(CASE WHEN R > 8 THEN 1WHEN R BETWEEN 7 AND 8 THEN 2WHEN R BETWEEN 5 AND 6 THEN 3WHEN R BETWEEN 3 AND 4 THEN 4WHEN R < 3 THEN 5 ELSE 0 END) AS R_scores,(CASE WHEN F < 2 THEN 1WHEN F BETWEEN 2 AND 4 THEN 2WHEN F BETWEEN 5 AND 10 THEN 3WHEN F BETWEEN 11 AND 20 THEN 4WHEN F > 20 THEN 5 ELSE 0 END) AS F_scoresFROM (SELECT user_id, DATEDIFF('2017-12-03', MAX(date)) AS R, COUNT(*) AS FFROM UserBehavior_newWHERE behavior = 'buy'GROUP BY user_id) AS a;-- 2.計算打分的平均值SELECT AVG(R_SCORES) AS avg_r, AVG(F_scores) AS avg_f FROM rf;-- 3.按照R值和F值的平均分對用戶進行分類，并統(tǒng)計SELECT 用戶分類, COUNT(*) AS 用戶數 FROM(SELECT user_id, R值, F值,(CASE WHEN R值='高' AND F值='高' THEN '重要價值客戶'WHEN R值='高' AND F值='低' THEN '重要發(fā)展客戶'WHEN R值='低' AND F值='高' THEN '重要保持展客戶'WHEN R值='低' AND F值='低' THEN '一般價值客戶' ELSE 0 END) AS 用戶分類FROM (SELECT user_id, R_scores, F_scores,(CASE WHEN R_scores > 4.2625 THEN '高' ELSE '低' END) AS R值,(CASE WHEN F_scores > 1.8710 THEN '高' ELSE '低' END) AS F值FROM rf) AS a) AS bGROUP BY 用戶分類ORDER BY 用戶數 DESC; 根據以上數據，重要價值客戶占44%，這部分客戶可能會成為忠實客戶，應加強與用戶的交互程度，比如提供會員卡等；重要保持客戶占22%，這部分客戶的購買頻次高，可采取促銷活動、商品折扣等方式刺激用戶消費；一般價值客戶占20%，這部分客戶如果沒有進行維護，可能會造成流失，可以通過活動短信、站內信息等方式喚回用戶；重要發(fā)展客戶占13%，這部分客戶最近有購買記錄，即購買意向較高，可以根據用戶的以往消費記錄進行個性化推薦，促進用戶復購。

四、結論與建議

1. 基于日期的行為分析，用戶一般在周末瀏覽商品，在周中購買商品的情況較多，且用戶在周末的行為更活躍。

建議在周末增加產品推薦策略、提高產品曝光量等，從而促進用戶購買行為。

2. 基于時間的行為分析，用戶在0-7點較少使用淘寶，在10-16點的活躍度相對較高，且購買量相比其他指標較高；用戶在20-22點的行為量是一天的峰值。

建議在20-22時段推廣運營活動、推送促銷商品、發(fā)送商品相關信息等刺激用戶購買；在10-16時段可發(fā)送短信、站內消息提醒用戶在購物車或收藏夾的商品；在其他時段減少商品的廣告投放量，以節(jié)約成本。

3. 基于用戶購買路徑，"用戶瀏覽→加購/收藏"的轉化率較高；

建議提高促銷力度（如滿減、折扣活動）或者增強產品交互界面效果等方面引導用戶加購或收藏。

4. 基于用戶行為路徑，用戶流失的主要環(huán)節(jié)在“加購/收藏”中，其流失的原因與“推薦的商品不是用戶想購買的”、“推薦的商品不吸引用戶”有關。

建議優(yōu)化平臺的推薦機制，根據用戶的需求不斷迭代，吸引更多用戶進行復購。

5. 重要價值客戶占44%，這部分客戶可能會成為忠實客戶；一般價值客戶占20%，這部分客戶如果沒有進行維護，可能會造成流失。

對于重要價值客戶，建議加強與用戶的交互程度，比如提供會員卡等；

對于重要保持客戶，建議采取促銷活動、商品折扣等方式刺激用戶消費；

對于重要發(fā)展客戶，建議根據用戶的以往消費記錄進行個性化推薦，促進用戶復購；

對于一般價值客戶，建議通過活動短信、站內信息等方式喚回用戶、激發(fā)用戶購買欲望。

數據源：