淘寶電商用戶數據分析
一、分析背景
電商野蠻生長的時代已經過去，由“流量為王”的玩法逐漸轉變為“用戶至上”的運營思路。本文就利用提取MySQL的數據，通過寫SQL的數據處理方式，對用戶的消費行為特點進行分析。
一、數據來源和數據理解
User Behavior Data from Taobao for Recommendation-數據集-阿里云天池?tianchi.aliyun.com/dataset/dataDetail?dataId=649&userId=1
本數據集包含了2017年11月25日至2017年12月3日之間，約五百萬隨機用戶的所有行為(行為包括點擊、購買、加購、收藏)。
數據集的列字段包含以下：
user_id：用戶身份
item_id：商品ID
behavior_type：用戶行為類型(包含點擊、收藏、加購物車、購買四種行為，分別用數字1、2、3、4表示)
user_geohash：地理位置(有空值)
item_category：品類ID(商品所屬的品類)
time：用戶行為發生的時間
二、提出問題
用戶角度：
通過AARRR模型分析各個購物環節的流失率，有什么需要改進的地方?;
不同時間維度下用戶的購物行為特點和活躍規律是什么樣的?
有高價值的用戶都有哪些?
產品角度：
1.熱銷產品有哪些?
2.差銷產品有哪些?
三、數據清洗
1.選擇子集
我們選擇導入10萬條數據集合來進行數據分析，所選數據皆為有用子集
2.列重命名
導入的原數據集沒有列名，因此給數據添加對應的列名。
3.刪除重復值
SELECt DISTINCT
user_id,
item_id,
category_id,
behavior,
timestamps
FROM
userbehavior;
查詢結果中沒有發現重復值
4.缺失值處理
查詢每列是否有空值
SELECt
count( userid ),
count( itemid ),
count( categoryid ),
count( behavior ),
count( timestamps )
FROM
userbehavior
WHERe
userid IS NULL
OR itemid IS NULL
OR categoryid IS NULL
OR behavior IS NULL
OR timestamps IS NULL;
查詢結果中沒有發現重復值
5.一致化處理
將timestamps列的數據類型由字符型轉換成時間格式，并創建date列和hour列分別存放對應的日期和小時
# 將timestamps列轉換為日期格式
UPDATe userbehavior SET timestamps=FROM_UNIXTIME(timestamps,'%Y-%m-%d %H:%i:%s');
# 新建date列,從timestamp列截取日期
ALTER TABLE userbehavior add date varchar(10);
UPDATe userbehavior SET date=SUBSTRINg(timestamps,1.10);
# 新建hour列,從timestamp列截取小時
ALTER TABLE userbehavior add hours varchar(10);
UPDATE userbehavior SET hours=SUBSTRINg(timestamps,12.2);
6.異常值處理
導入的數據選取的時間是2017年11月25日至2017年12月3日之間。
(1)通過查詢date列，看是否存在異常值
(2)刪除制定日期之外的異常值
# 刪除指定日期之外的異常值
DELETE FROM userbehavior
WHERe date > '2017-12-03' or date < '2017-11-25';
# 查詢日期列最小值和最大值檢查是否處理異常值
SELECt min(date),max(date) FROM userbehavior;
四.分析問題
用戶角度
（一）用戶的整體購物情況
對總用戶數，商品數，商品類別數，用戶行為數進行分析匯總
# 統計數據集中所有行為數據，總用戶數，商品數，商品類別數，用戶行為數
SELECt
count( * ) AS 行為數,
count( DISTINCT userid ) AS 用戶數,
count( DISTINCT itemid ) AS 商品數,
count( DISTINCT categoryid ) AS 商品類別數,
count( DISTINCT behavior ) AS 用戶行為數
FROM
userbehavior
由上可知，數據集中總共有99956條，一共包含983位用戶，64440個商品，3128個類別，4種用戶行為。
(二)用戶行為分析
(1)用戶總行為數漏斗
由于收藏和加購都為瀏覽和購買階段之間確定購買意向的用戶行為，且不分先后順序，因此將其算作一個階段。
SELECt
behavior,
count(*)
FROM
userbehavior
GROUP BY
behavior;
從上圖可以看出，從瀏覽到有購買意向只有9%的轉化率。只有少量用戶是直接購買而未通過收藏和加入購物車，從頁面瀏覽到收藏/加購的轉化率偏低，該階段的轉化率應重點提升。
(2)獨立訪客數漏斗
SELECt
behavior,
count(distinct userid) AS '用戶數'
FROM
`userbehavior`
GROUP BY
behavior
如圖所示，約有68%的付費用戶，用戶付費轉化率相當高。
(3)用戶行為路徑分析
分析用戶購買行為路徑中主要考慮以下幾種：
計算過程如下：
select sum(case when pv>0 then 1 else 0 end) as 'pv',
sum(case when pv>0 and buy>0 and cart=0 and fav=0 then 1 else 0 end) as 'pv-buy',
sum(case when pv>0 and (cart>0 or fav>0) then 1 else 0 end) as 'pv-cart/fav',
sum(case when pv>0 and (cart>0 or fav>0) and buy>0 then 1 else 0 end) as 'pv-cart/fav-buy',
sum(case when pv>0 and (cart>0 or fav>0) and buy=0 then 1 else 0 end) as 'pv-cart/fav-lost',
sum(case when pv>0 and cart=0 and fav=0 and buy=0 then 1 else 0 end) as 'pv-lost'
from userbehavior;
結果如下：
從以上結果可以看出：
1) 用戶點擊后流失率為7%，這說明平臺對用戶具有很大吸引力。
2) 用戶點擊-收藏/加購-購買的轉化率比點擊-購買的轉化率高，用戶更喜歡把商品收藏或加入購物車后購買,可以從產品交互界面和營銷機制等方面積極引導用戶把商品加入購物車或收藏
3) 從流失率分析，用戶點擊收藏/加購
(4)用戶流失原因
這里運用假設檢驗分析的方法,從產品和用戶兩個維度，收集相關數據進行分析。
假設一：平臺推薦商品不是用戶想買商品？
收集數據：想要驗證平臺推薦的商品是不是用戶想買的，可以分析用戶瀏覽量和用戶購買量是否一致
I.創建視圖統計瀏覽量TOP10商品種類和購買量TOP10商品種類
# 創建瀏覽量TOP10商品種類視圖
create view pv_top10_category as
select categoryid,count(*) as 瀏覽量top10
from userbehavior
WHERe behavior='pv'
GROUP BY categoryid
ORDER BY count(*) desc
limit 10;
# 創建購買量TOP10商品種類視圖
create view buy_top10_category as
select categoryid,count(*) as 購買量top10
from userbehavior
WHERe behavior='buy'
GROUP BY categoryid
ORDER BY count(*) desc
limit 10;
根據categoryid對比分析瀏覽量TOP10和購買量TOP10重合部分商品種類
# 瀏覽量TOP10商品種類
SELECt * FROM pv_top10_category as a
left join buy_top10_category as b
USING(categoryid);
# 購買量TOP10商品種類
SELECt * FROM buy_top10_category as a
left join pv_top10_category as b
USING(categoryid);
結果如下表所示：
由上表可以看出，瀏覽量TOP10商品種類和購買量TOP10商品種類中僅有50%的商品種類重合，重合度并不高。
II.創建視圖統計瀏覽量TOP10商品和購買量TOP10商品
# 創建瀏覽量TOP10商品視圖
create view pv_top10_item as
select itemid,count(*) as 瀏覽量top10
from userbehavior
WHERe behavior='pv'
GROUP BY itemid
ORDER BY count(*) desc
limit 10;
# 創建購買量TOP10商品視圖
create view buy_top10_item as
select itemid,count(*) as 購買量top10
from userbehavior
WHERe behavior='buy'
GROUP BY itemid
ORDER BY count(*) desc
limit 10;
根據itemid對比分析瀏覽量TOP10和購買量TOP10重合部分商品
# 瀏覽量TOP10商品
SELECt * FROM pv_top10_item as a
left join buy_top10_item as b
USING(itemid);
# 購買量TOP10商品
SELECt * FROM buy_top10_item as a
left join pv_top10_item as b
USING(itemid);
結果如下表所示：
由上表結果可以看出，瀏覽量TOP10商品和購買量TOP10商品中沒有一個重復的商品ID，重合度為0.
得出結論：用戶高瀏覽量并不能帶來高購買量，即平臺推薦商品不是用戶想買商品。
假設二：商品種類太少？
收集數據：通過統計商品類目數量和涵蓋的商品數量來驗證商品種類是否太少
# 整體商品類目與對應的商品數量
SELECt
count( DISTINCT categoryid ) AS 商品類目數量,
count( DISTINCT itemid ) AS 商品數量
FROM
userbehavior;
結果如下表所示：
得出結論：數據集共有3128中商品類目涵蓋64440中商品，供用戶選擇的商品種類很多。
假設三：商品沒有吸引力？
收集數據：通過分析商品購買次數的占比，從而驗證商品是否有吸引力。
統計每個商品的購買次數
# 每個商品的購買次數
SELECt itemid,count(*) AS 購買次數
FROM userbehavior
WHERe behavior = 'buy'
GROUP BY itemid
ORDER BY count(*) DESC;
結果如下表所示下：
# 購買次數占比
SELECt
a.購買次數,
count( a.itemid ) AS 商品數,
concat(
round( count( a.itemid ) / ( SELECT count( DISTINCT itemid ) FROM userbehavior WHERe behavior = 'buy' ) * 100. 2 ),
'%'
) AS 占比
FROM
( SELECt itemid, count( * ) AS 購買次數 FROM userbehavior WHERe behavior = 'buy' GROUP BY itemid ) AS a
GROUP BY
a. 購買次數;
結果如下表所示：
得出結論：商品購買次數最多不超過4次，且購買1次的商品占比94.81%，說明產品復購率低，商品對用戶沒有足夠多的吸引力，假設三成立。
(4)用戶粘性分析
1)跳出率
跳出率：跳出率：用戶只產生pv行為后沒有后續其他行為的次數占所有訪問次數的比例
select concat(round(sum(pv)/select count(*) from userbehavior)*100.2)，'%')as 跳出率
from(
select userid,sum(case behavior when 'pv' then 1 else 0 end ) as pv
sum(case behavior when 'fav' then 1 else 0 end ) as 'cart'
sum(case behavior when 'cart' then 1 else 0 end ) as 'cart'
sum(case behavior when 'buy' then 1 else 0 end ) as 'buy'
from userbehavior
groupby userid) as a
where 'pv'>0 and fav=0 and cart=0 and buy=0
結果如下表：
得出結論：頁面的跳出率為2.51%，用戶的跳出率低，說明平臺對用戶的吸引力較大。
2)用戶復購率
復購用戶：產生兩次或兩次以上購買的用戶并且購買日期不在同一天
用戶復購率=復購用戶數/總購買用戶數
復購用戶數
select count(*) as 用戶復購數 from (select userid,count(*) as 用戶復購數 from userbehavior
where behavior='buy'
group by userid
having count(distinct date)>=2) as a
總購買用戶數
select count(distinct userid)
from userbehavior
where behavior='buy'
得出結論：用戶復購率=復購用戶數/總購買用戶數=365/671=54.40%。用戶的復購率超過50%，用戶具有較高的忠誠度。
3)復購用戶購買次數占比
# 復購用戶購買次數
SELECt sum(購買次數) as 復購用戶購買次數
FROM # 查找復購用戶
(SELECt userid,count(*) as 購買次數 FROM `userbehavior`
WHERe behavior='buy'
GROUP BY userid
HAVINg count(DISTINCT date)>=2) as a;
# 總購買次數
SELECt count(*) as 總購買次數
FROM userbehavior
WHERe behavior='buy';
得出結論：用戶的復購率超過了50%，同時復購用戶購買次數占比達到了79.40%，說明平臺用戶粘性很高，且購買積極性也高。
4)留存率
把每天新增的用戶進行分組，可以通過每天新增用戶的留存率來判斷平臺對用戶的吸引力，由于數據集僅有九天的數據，這里使用2017年11月25日的所有用戶作為第一天新增用戶，僅計算這九天的新增用戶留存率。
# 計算每日留存率
select a.date, count(distinct a.userid) as 日新增用戶數,
concat(round(100*count(distinct if(datediff(b.date,a.date)=1. b.userid, null))/count(distinct a.userid),2),'%') as 次日留存率,
concat(round(100*count(distinct if(datediff(b.date,a.date)=2. b.userid, null))/count(distinct a.userid),2),'%') as 二日留存率,
concat(round(100*count(distinct if(datediff(b.date,a.date)=3. b.userid, null))/count(distinct a.userid),2),'%') as 三日留存率,
concat(round(100*count(distinct if(datediff(b.date,a.date)=4. b.userid, null))/count(distinct a.userid),2),'%') as 四日留存率,
concat(round(100*count(distinct if(datediff(b.date,a.date)=5. b.userid, null))/count(distinct a.userid),2),'%') as 五日留存率,
concat(round(100*count(distinct if(datediff(b.date,a.date)=6. b.userid, null))/count(distinct a.userid),2),'%') as 六日留存率,
concat(round(100*count(distinct if(datediff(b.date,a.date)=7. b.userid, null))/count(distinct a.userid),2),'%') as 七日留存率,
concat(round(100*count(distinct if(datediff(b.date,a.date)=8. b.userid, null))/count(distinct a.userid),2),'%') as 八日留存率
from #統計每日新增的用戶名單
(select d1.*
from lading as d1 left join lading as d2 on d1.userid=d2.userid and d1.date>d2.date
where d2.date is null) as a
left join
lading as b on a.userid=b.userid
group by a.date;
得出結論:每日的留存率都處在較高的水平，這說明用戶的忠誠度較高。
綜上所述：從用戶的跳出率、復購率和留存率可以看出，平臺用戶粘性很高，忠誠度高且購買積極性也高。
(三)用戶購物行為分析
(1)每天用戶訪問變化情況
UV(unique visitor)，代表獨立訪客人數。
PV/UV：就是平均一個獨立訪問者所瀏覽的頁面訪問量，引申含義就是頁面重復訪問量，反映用戶購買意向，比值越大反映用戶購買意向強烈。
SELECt date as 日期,
sum(case when behavior='pv' then 1 else 0 end) as 點擊量,
count(DISTINCT userid) as 用戶數,
(sum(case when behavior='pv' then 1 else 0 end)/count(DISTINCT userid)) as 重復訪問量
FROM userbehavior
GROUP BY date
結果如下表所示：
數據可視化圖表如下：
得出結論：由以上結果可以看出，在11-30日至12-3日期間，點擊量和用戶數呈明顯上升趨勢，都在12-2日到達峰值，而重復訪問量先快速攀升而后又迅速下降，在12-1日達到峰值(12-1日為周五)。
(2)每天用戶購買行為的特點
SELECt date as '日期',count(behavior) as 用戶行為總量,
sum(case when behavior='pv' then 1 else 0 end) as 點擊量,
sum(case when behavior='fav' then 1 else 0 end) as 收藏數,
sum(case when behavior='cart' then 1 else 0 end) as 加購數,
sum(case when behavior='buy' then 1 else 0 end) as 購買數
FROM userbehavior
GROUP BY date;
結果如下表所示：
數據可視化圖表如下：
由以上結果可以看出，在11-30日至12-3日期間用戶購買行為整體呈上升趨勢，綜合(1)、(2)的結果，通過查詢可知是由于雙十二大促的預售活動導致訪問量的激增，用戶表現強烈的購買意愿，將商品收藏、加入購物車為雙十二當天購買做準備。
(3)一天中用戶的活躍時段分布
SELECt hours as '時段',count(behavior) as 用戶行為總量,
sum(case when behavior='pv' then 1 else 0 end) as 點擊量,
sum(case when behavior='fav' then 1 else 0 end) as 收藏數,
sum(case when behavior='cart' then 1 else 0 end) as 加購數,
sum(case when behavior='buy' then 1 else 0 end) as 購買數
FROM userbehavior
GROUP BY hours
ORDER BY hours;
結果如下表所示：
數據可視化圖表如下：
從結果可以看出，每日0點到4點用戶活躍度快速降低，降到一天中的活躍量最低值，符合人們正常作息時間規律，5點到10點用戶活躍度快速上升，12點到15點用戶活躍度緩慢回升，18點到21點用戶活躍度快速上升，達到一天中的最高值，在此時段用戶最活躍可推送促銷活動或產品直播，刺激消費者購買。
(4)一周中用戶活躍時段分布
SELECt DATE_FORMAT(date,'%W') as '星期',
count(behavior) as 用戶行為總量,
sum(case when behavior='pv' then 1 else 0 end) as 點擊量,
sum(case when behavior='fav' then 1 else 0 end) as 收藏數,
sum(case when behavior='cart' then 1 else 0 end) as 加購數,
sum(case when behavior='buy' then 1 else 0 end) as 購買數
FROM userbehavior
GROUP BY DATE_FORMAT(date,'%W')
ORDER BY WEEKDAY(date);
結果如下表所示：
數據可視化圖表如下：
從結果可以看出，每周一至周四用戶活躍度較穩定，周五活躍度會有小幅上升，周末活躍度大幅度攀升達到最大值，這是周末雙十二大促銷活動引起，可見在周末推送促銷活動更能刺激消費者購買。
(四)用戶價值分析
運用RFM分析方法，對用戶按價值分層，找出最有價值用戶。
由于數據源沒有相關的金額數據，暫且通過 R 和 F 的數據對客戶價值進行分類。
1)計算R、F 值
由于數據集包含的時間是從2017年11月25日至2017年12月3日，這里選取2017年12月3日作為計算日期，統計客戶最近產生交易的日期距離2017年12月3日間隔天數和交易的次數。
# 最近一次消費時間間隔R和消費頻率F
SELECt userid,DATEDIFF('2017-12-04',max(date)) AS R,count(*) as F
FROM userbehavior
WHERe behavior = 'buy'
GROUP BY userid;
結果如下表所示
2)給R、F 值按價值打分
根據上面計算得到的用戶R、F值，定義一個打分規則，如下圖：
打分計算過程如下：
# 對最近一次消費時間間隔(R)和消費頻率(F)進行評分，并創建視圖rfm
create view rfm as
SELECt a.*,(case
when R>20 then 1
when R between 10 and 20 then 2
when R between 5 and 10 then 3
when R between 3 and 5 then 4
when R<=3 then 5
else 0 end) as Rscore,
(case
when F<=2 then 1
when F between 2 and 6 then 2
when F between 6 and 8 then 3
when F between 10 and 20 then 4
when F>20 then 5
else 0 end) as Fscore
FROM
(SELECt userid,DATEDIFF('2017-12-04',max(date)) AS R,count(*) as F
FROM userbehavior
WHERe behavior = 'buy'
GROUP BY userid) as a;
結果如下表所示：
3)計算打分平均值
# 計算打分平均值
SELECt avg(Rscore) as avg_R, avg(Fscore) as avg_F
FROM rfm;
結果如下表所示：
4) 用戶分類
按照R值和F值的平均分對用戶進行分類
# 用戶分類
select userid,Rscore,Fscore,
(case when Rscore>4.158 then '高' else '低' end) as 'R值高低',
(case when Fscore>1.5484 then '高' else '低' end) as 'F值高低'
from rfm;
結果如下表所示：
因為我們這里僅考慮R、F因素，把R、F值都低的用戶定義為一般價值用戶，因此按下面用戶分類規則進行分類：
對用戶進行標記：
#對用戶進行標記
select userid,R值高低,F值高低,
(case
when R值高低='高' and F值高低='高' then '重要價值用戶'
when R值高低='低' and F值高低='高' then '重要保持用戶'
when R值高低='高' and F值高低='低' then '重要發展用戶'
when R值高低='低' and F值高低='低' then '一般價值用戶'
else 0
end) as '用戶分類'
from
(select userid,Rscore,Fscore,
(case when Rscore>4.158 then '高' else '低' end) as 'R值高低',
(case when Fscore>1.5484 then '高' else '低' end) as 'F值高低'
from rfm) as a;
結果如下表所示：
5) 對用戶分類進行統計
# 對用戶分類統計
select 用戶分類,count(*) as 用戶數
FROM
(select userid,R值高低,F值高低,
(case
when R值高低='高' and F值高低='高' then '重要價值用戶'
when R值高低='低' and F值高低='高' then '重要保持用戶'
when R值高低='高' and F值高低='低' then '重要發展用戶'
when R值高低='低' and F值高低='低' then '一般價值用戶'
else 0
end) as '用戶分類'
from
(select userid,Rscore,Fscore,
(case when Rscore>4.158 then '高' else '低' end) as 'R值高低',
(case when Fscore>1.5484 then '高' else '低' end) as 'F值高低'
from rfm) as a) as b
GROUP BY 用戶分類;
結果如下表所示：
對數據結果進行可視化：
由以上結果可知：
1)重要價值客戶占比23.70%，這部分用戶應積極維護獎勵(免費贈與VIP會員資格)，使其變成我們的忠實用戶。
2)重要發展用戶占比21.46%，這部分的用戶應積極引導刺激消費(發放優惠券)，提升用戶的忠誠度和粘性。
3)重要保持用戶占比19.08%，這部分用戶可根據過往用戶消費記錄進行大數據分析，進行個性化推薦精準營銷，以提高復購率;
4)一般價值用戶占比35.77%，這部分用戶大概率已經流失，要想辦法主動聯系客戶，調查清楚哪里出了問題，通過郵件推送、活動短信提醒等方法挽回用戶。
產品角度
商品購買次數
統計所有商品的購買次數，同時找到購買次數、瀏覽次數、收藏次數和加入購物車次數最多的商品。
SELECt product_buytimes, COUNT(*) AS product_type_count
FROM
(SELECt COUNT(user_id) AS product_buytimes
FROM User
WHERe behavior_type = 'buy'
GROUP BY item_id) AS product_buypool
GROUP BY product_buytimes
ORDER BY product_buytimes ASC;
本次分析的商品共有64440中，用戶購買的商品共有16743種，購買數量非常集中的商品比較少;在本次統計的數據中，只購買一次的商品有56154種，占用戶購買商品數的79.26%，說明商品售賣主要依靠長尾商品的累積效應，而非爆款商品的帶動。
商品類目銷售情況
SELECt categoryid , COUNT(*) AS cat_count
FROM user
WHERe behavior = 'buy'
GROUP BY category_id
ORDER BY cat_count DESC;
如圖所示，商品類目銷售情況中有較為明顯的集中趨勢，可根據暢銷類目優化商品展示、加強商品捆綁，進而提高銷量。
六、總結與建議
總結：
1.平臺對用戶吸引力巨大，用戶在平臺的行為路徑中通過點擊-收藏/加購-購買的轉化率最高，應積極引導用戶把商品加入購物車或收藏
2. 用戶流失的環節主要在收藏/加購，流失的原因主要是平臺推薦的商品并非用戶喜歡的商品，同時平臺也缺乏足夠吸引的爆款產品
3 .優化平臺推薦機制，把更多流量給到顧客愿意購買的商品，通過打造爆款產品，吸引更多用戶復購
4.用戶在平臺的跳出率為2.51%、商品復購率超過了50%，每日新增用戶的留存率都比較高，說明用戶粘性很高，忠誠度高且購買積極性也高。
5.從用戶的消費行為習慣看，用戶在周末的時候PV、UV較高，活躍度大幅上升，且購買意愿強烈，在一天中的18點到21點是活躍高峰期，可以根據用戶的活躍時間段進行廣告投放、精準推送商家的折扣優惠或促銷活動，同時可以利用新媒體平臺進行微信、抖音等來獲取更多新用戶。
6. 通過對分用戶行為路徑分析及各個環節轉化率的分析，用戶在點擊-收藏/加購-購買的轉化率最高，應從產品交互界面、營銷機制等方面積極引導用戶把商品加入購物車或收藏。
7.從流失率分析，用戶把商品收藏或加入購物車后流失率達28.9%，這部分用戶流失的主要原因是平臺推薦商品不是用戶想買商品以及商品沒有吸引力，通過優化平臺推薦機制，把更多流量給到顧客愿意購買的商品，通過打造爆款產品，吸引更多用戶復購。
8. 通過RFM模型對用戶進行價值分類，發現用戶主要集中在一般價值用戶，占比高達35.77%，這一部分的用戶極有可能已流失，建議通過APP定向推送、短信和郵件等形式進行精準發放有償問卷主動聯系用戶，調查清楚哪里出了問題，制定相應的挽回策略。
9.對銷量高的商品類目，應該主動加大這類商品的推廣力和供應力度。
樂發網超市批發網提供超市貨源信息,超市采購進貨渠道。超市進貨網提供成都食品批發,日用百貨批發信息、微信淘寶網店超市采購信息和超市加盟信息.打造國內超市采購商與批發市場供應廠商搭建網上批發市場平臺,是全國批發市場行業中電子商務權威性網站。
本文來源： 淘寶電商用戶數據分析
