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