隨著移動(dòng)互聯(lián)網(wǎng)多年的快速發(fā)展，移動(dòng)互聯(lián)網(wǎng)已進(jìn)入下半場(chǎng) ，不再依靠用戶紅利來經(jīng)營，發(fā)展業(yè)務(wù)，告別粗糙的/高成本企業(yè)發(fā)展的方式，開始轉(zhuǎn)而精細(xì)化管理，結(jié)合市場(chǎng)、渠道、用戶行為等數(shù)據(jù)分析，對(duì)用戶展開有針對(duì)性的運(yùn)營活動(dòng)，提供個(gè)性化、差異化的運(yùn)營策略，以實(shí)現(xiàn)運(yùn)營目的行為。本文利用sql對(duì)淘寶用戶行為數(shù)據(jù)進(jìn)行分析，通過用戶行為分析業(yè)務(wù)問題，提供針對(duì)性的運(yùn)營策略。

一. 提出問題

本次分析的目的是想通過對(duì)淘寶用戶行為數(shù)據(jù)分析，為以下問題提供解釋和改進(jìn)建議：
1.分析用戶使用APP過程中的常見電商分析指標(biāo)，確定各個(gè)環(huán)節(jié)的流失率，找到需要改進(jìn)的環(huán)節(jié)
2.研究用戶在不同時(shí)間尺度下的行為規(guī)律，找到用戶在不同時(shí)間周期下的活躍規(guī)律
3.找到用戶對(duì)不同種類商品的偏好，找到針對(duì)不同商品的營銷策略
4.找出最具價(jià)值的核心付費(fèi)用戶群，對(duì)這部分用戶的行為進(jìn)行分析
為了分析這些問題，我們使用以下兩種模型進(jìn)行分析

1.基于AARRR漏斗模型分析用戶行為

本文通過常用的電商數(shù)據(jù)分析指標(biāo)，采用AARRR漏斗模型拆解用戶進(jìn)入APP后的每一步行為。AARRR模型是根據(jù)用戶使用產(chǎn)品全流程的不同階段進(jìn)行劃分的，針對(duì)每一環(huán)節(jié)的用戶流失情況分析出不同環(huán)節(jié)的優(yōu)化優(yōu)先級(jí)，主要通過以下個(gè)各階段來進(jìn)行分析：

01.png

2.基于RFM模型找出有價(jià)值的用戶

由于不同用戶對(duì)公司帶來的收益差別很大，而且根據(jù)二八定律20%的做有價(jià)值用戶能帶來80%的收益，因此需要對(duì)用戶進(jìn)行價(jià)值評(píng)價(jià)，找到最有價(jià)值的用戶群，并針對(duì)這部分用戶進(jìn)行差異化的營銷。
這里參考著名的 RFM 模型對(duì)用戶進(jìn)行評(píng)價(jià)：

02.png

R-Recency（最近一次購買時(shí)間）
R指用戶上一次消費(fèi)的時(shí)間，上一次購物時(shí)間距今最近的顧客通常在近期響應(yīng)營銷活動(dòng)的可能性也最大，對(duì)于APP而言，很久沒有購物行為可能意味著用戶放棄了APP的使用，重新喚起用戶也需要更多的成本。
F-Frequency（消費(fèi)頻率）
F指用戶在某段時(shí)間內(nèi)的購物次數(shù)，消費(fèi)頻率越高意味著這部分用戶對(duì)產(chǎn)品的滿意度最高，用戶粘性最好，忠誠度也最高。
M-Money（消費(fèi)金額）
M指用戶在某段時(shí)間內(nèi)的購物金額，這也是為公司帶來價(jià)值的最直接體現(xiàn)，而消費(fèi)金額較高的用戶在用戶總體中人數(shù)較少，卻能創(chuàng)造出更多價(jià)值，是需要重點(diǎn)爭(zhēng)取的對(duì)象。
這三個(gè)維度互相關(guān)聯(lián)，反映了每個(gè)用戶的現(xiàn)在價(jià)值和潛在價(jià)值，將每個(gè)維度分成5個(gè)區(qū)間進(jìn)行評(píng)分，通過計(jì)算分?jǐn)?shù)找到最有價(jià)值的用戶，并對(duì)用戶進(jìn)行分類，可以有針對(duì)性的不同類型用戶采用不同的營銷策略

二.理解數(shù)據(jù)

1. 數(shù)據(jù)來源

阿里云天池： https://tianchi.aliyun.com/dataset/dataDetail?dataId=649
本數(shù)據(jù)集包含了2017年11月25日至2017年12月3日之間，約有一百萬隨機(jī)用戶的所有行為（行為包括點(diǎn)擊、購買、加購、喜歡）。數(shù)據(jù)集的每一行表示一條用戶行為，由用戶ID、商品ID、商品類目ID、行為類型和時(shí)間戳組成，并以逗號(hào)分隔。
時(shí)間跨度約為一個(gè)禮拜，從周六到下個(gè)周日

LOAD DATA LOCAL INFILE "E:\\C-05-數(shù)據(jù)分析實(shí)戰(zhàn)y\\taobao_analyse\\UserBehavior.csv"
INTO TABLE user
FIELDS TERMINATED BY ","
LINES TERMINATED BY "\n";

2.各字段含義

03.png

注意到，用戶行為類型共有四種，它們分別是

04.png

關(guān)于數(shù)據(jù)集大小的一些說明如下

05.png

三.數(shù)據(jù)清洗

1.觀察記錄

數(shù)據(jù)記錄達(dá)到一億條，為了方便分析及效率，源數(shù)據(jù)是已經(jīng)按照user_id排序好的，取5百萬行到8百萬行之間的3百萬行記錄進(jìn)行分析。

06.png

2.列名重命名

將原有的列名簡(jiǎn)化為user,item,category,behavior,date

3.刪除重復(fù)值

用戶的購買行為由于時(shí)間精確到小時(shí)，確實(shí)會(huì)存在少量用戶在一小時(shí)內(nèi)重復(fù)購買或?yàn)g覽統(tǒng)一商品的行為，因此不對(duì)此部分?jǐn)?shù)據(jù)進(jìn)行處理。

4. 一致化處理

時(shí)間數(shù)據(jù)中原為時(shí)間戳格式，需要將其分為兩列，以便研究每日和一段日期內(nèi)數(shù)據(jù)變化。

ALTER TABLE user ADD hour CHAR(20);
UPDATE user SET hour=date;
UPDATE user SET date=FROM_UNIXTIME( date,'%Y-%m-%d');
UPDATE user SET hour=FROM_UNIXTIME( hour,'%H');

完成數(shù)據(jù)清洗后的數(shù)據(jù)：

07.png

四. 構(gòu)建模型

1.分析用戶使用戶行為的漏斗模型

利用AARRR模型分析用戶行為，此處數(shù)據(jù)主要涉及用戶刺激和購買轉(zhuǎn)化的環(huán)節(jié)，通過用戶從瀏覽到最終購買整個(gè)過程的流失情況，包括瀏覽、收藏、加入購物車和購買環(huán)節(jié)，一個(gè)周內(nèi)的各項(xiàng)指標(biāo)如下：
訪問用戶總數(shù)（UV)：29233
頁面總訪問量（PV）：2685348
平均每人每周訪問量為91.8次頁面

SELECT COUNT(DISTINCT user) AS UV,
(SELECT COUNT(*) FROM user WHERE behavior='pv') AS PV,
(SELECT COUNT(*) FROM user WHERE behavior='pv')/COUNT(DISTINCT user) AS 'PV/UV'
FROM user;

08.png

跳失率=只點(diǎn)擊一次瀏覽的用戶數(shù)量/總用戶訪問量

SELECT COUNT(*)
FROM (SELECT user
FROM user GROUP BY user
HAVING COUNT(behavior)=1) AS a;

09.png

統(tǒng)計(jì)時(shí)間為一周，只有1個(gè)人瀏覽了一次就離開淘寶，分析記錄雖然僅為部分?jǐn)?shù)據(jù)，但仍可以看出淘寶擁有足夠的吸引力讓用戶停留app，這也符合日常觀察。

用戶總行為數(shù)漏斗計(jì)算

SELECT behavior,COUNT(*)
FROM user
GROUP BY behavior
order by behavior desc;

10.png

11.png

由于收藏和加入購物車都為瀏覽和購買階段之間確定購買意向的用戶行為，且不分先后順序，因此將其算作同一階段，可以看到從瀏覽到有購買意向只有9.51%的轉(zhuǎn)化率，當(dāng)然有部分用戶是直接購買而未通過收藏和加入購物車，但也說明大多數(shù)用戶瀏覽頁面次數(shù)較多，而使用購物車和收藏功能較少，而購買次數(shù)占使用購物車和收藏功能的23.24%，說明從瀏覽到進(jìn)行收藏和加入購物車的階段是指標(biāo)提升的重點(diǎn)環(huán)節(jié)。
獨(dú)立訪客漏斗模型計(jì)算：

SELECT behavior,COUNT(DISTINCT user) AS '用戶數(shù)'
FROM user
GROUP BY behavior
ORDER BY COUNT(DISTINCT user) DESC;

12.png

13.png

上面是每一步用戶行為的獨(dú)立用戶數(shù)，可以看到使用APP的用戶中PUR（付費(fèi)用戶占比）為68.2%，用戶付費(fèi)轉(zhuǎn)化率相當(dāng)高。

2， 不同時(shí)間尺度下用戶行為模式分析

1）分析一周內(nèi)每天的用戶行為

SELECT date,SUM(CASE WHEN behavior='pv' THEN 1 ELSE 0 END)AS '瀏覽數(shù)',
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 '購物車',
SUM(CASE WHEN behavior='buy' THEN 1 ELSE 0 END)AS '付費(fèi)數(shù)'
FROM user
GROUP BY date
ORDER BY date;

14.png

15.png

可以看到明顯不同，從左到右為周一到周日的數(shù)據(jù)，在平時(shí)，工作日時(shí)各項(xiàng)指標(biāo)平穩(wěn)，而到周末高漲，推測(cè)是上班族因工作逛淘寶的時(shí)間少，而周末有充足的精力，購買能力也增加。因此平日運(yùn)營可以將活動(dòng)集中在周末進(jìn)行。

2）分析一天內(nèi)用戶每小時(shí)的行為

此處取12月1號(hào)（周五）當(dāng)天的用戶數(shù)據(jù)

SELECT hour,SUM(CASE WHEN behavior='pv' THEN 1 ELSE 0 END)AS '瀏覽數(shù)',
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 '購物車',
SUM(CASE WHEN behavior='buy' THEN 1 ELSE 0 END)AS '付費(fèi)數(shù)'
FROM user
where date = "2017-12-01"
GROUP BY hour
ORDER BY hour

取11月28號(hào)（周二）當(dāng)天的用戶數(shù)據(jù)

16.png

取12月2號(hào)（周六）當(dāng)天的用戶數(shù)據(jù)

17.png

上兩張圖分別時(shí)周二和周六的用戶行為變化，波動(dòng)起伏接近，高峰都出現(xiàn)在晚上20點(diǎn)到23點(diǎn)，之后午夜進(jìn)入低谷，符合大部分人的作息時(shí)間。在晚上20點(diǎn)到23點(diǎn)，進(jìn)行各種直播活動(dòng)等互動(dòng)營銷手段，可能取得更大的收益。

3, 不同商品種類的用戶行為

1）統(tǒng)計(jì)瀏覽次數(shù)、收藏次數(shù)和加入購物車次數(shù)最多的商品。

SELECT item, COUNT(user) AS 'times'
FROM user WHERE behavior='buy'
GROUP BY item
ORDER BY times DESC
LIMIT 30

18.png

列出瀏覽量前30的商品，排第一的商品item_id為812879，為914次，那是否進(jìn)入銷量榜單呢， 對(duì)商品銷量和收藏前也進(jìn)行了統(tǒng)計(jì)：

19.png

20.png

在銷量榜單中并沒有看到瀏覽量第一和第二的商品， 說明這些吸引用戶更多注意力的商品沒有很好的轉(zhuǎn)化為實(shí)際銷量，僅更多的進(jìn)入收藏中。

21.png

同時(shí)瀏覽量前排的商品均能在收藏量前列中，說明瀏覽數(shù)與收藏的關(guān)系更為直接。

2）統(tǒng)計(jì)所用商品的購買次數(shù)

SELECT times AS "購買次數(shù)" , COUNT(item)AS "商品數(shù)"
FROM (SELECT item, COUNT(user) AS 'times' FROM user WHERE behavior='buy' GROUP BY item) AS buy_times
GROUP BY times
ORDER BY COUNT(*);

商品購買次數(shù)分布：

22.png

根據(jù)本次分析的數(shù)據(jù)，只購買一次的商品有38248種，購買兩次的商品有5146種，本次分析的商品有45937種，購買一次的商品占到83.8%，說明商品售賣主要依靠長尾商品的累計(jì)效應(yīng)。而非爆款商品的帶動(dòng)。

4.基于RFM理論找出有價(jià)值的用戶

由于數(shù)據(jù)源僅僅為9天的數(shù)據(jù)，且沒有商品的金額，無法統(tǒng)計(jì)M值。
計(jì)算消費(fèi)間隔R值，最近消費(fèi)時(shí)間和最大時(shí)間（2017-12-03）間隔時(shí)間，最近購買時(shí)間的區(qū)間為0-8，將其分為3檔，0-2,3-5,6-8分別對(duì)應(yīng)的R評(píng)分0到2

CREATE VIEW pay_B AS
SELECT user, DATEDIFF('2017-12-03',MAX(date)) AS B FROM user
WHERE behavior='buy'
GROUP BY user;

SELECT user, (CASE WHEN B BETWEEN 0 AND 2 THEN 2
WHEN B BETWEEN 3 AND 5 THEN 1
WHEN B BETWEEN 6 AND 8 THEN 0
ELSE null END) AS R
FROM pay_B
ORDER BY R DESC

23.png

計(jì)算消費(fèi)頻率F值，付費(fèi)用戶中消費(fèi)次數(shù)從低到高為1-93次，將其分為6檔1-9，10-19，20-29，30-39，40-49，50以上，對(duì)應(yīng)F評(píng)分分別為0-5分

CREATE VIEW pay_times AS SELECT user,count( item ) AS times from user where behavior = "buy"
group by user order by count( item ) DESC;

SELECT user ,(CASE WHEN times between 1 AND 9 THEN 0
WHEN times BETWEEN 10 AND 19 THEN 1
WHEN times BETWEEN 21 AND 29 THEN 2
WHEN times BETWEEN 31 AND 39 THEN 3
WHEN times BETWEEN 41 AND 49 THEN 4
WHEN times >= 50 THEN 5 ELSE NULL END) AS F
FROM pay_times
ORDER BY F DESC

24.png

25.png

26.png

五. 結(jié)論與建議

本文分析了淘寶用戶行為數(shù)據(jù)共300萬條，從四個(gè)不同角度提出業(yè)務(wù)問題，使用AARRR模型和BFM模型分析數(shù)據(jù)給出如下結(jié)論和建議。

1.通過AARRR模型分析用戶使用的各個(gè)環(huán)節(jié)

a.獲取用戶
由于數(shù)據(jù)中沒有給出每個(gè)用戶第一次的登陸的時(shí)間，我們暫且把瀏覽行為視為用戶的獲取，
b.激活用戶
用戶行為包括點(diǎn)擊、放進(jìn)購物車、收藏以及購買，由于收藏和加入購物車都為瀏覽和購買階段之間確定購買意向的用戶行為，且不分先后順序，因此將其算作一個(gè)階段，從瀏覽到有購買意向只有9.51%的轉(zhuǎn)化率，當(dāng)然有一部分用戶是直接購買，但也說明大多數(shù)用戶以瀏覽頁面為主而購買轉(zhuǎn)化較少，此處為轉(zhuǎn)化漏斗中需要改善和提高的環(huán)節(jié)。
針對(duì)這一環(huán)節(jié)改善轉(zhuǎn)化率的建議有：
（1）優(yōu)化電商平臺(tái)的搜索匹配度和推薦策略，主動(dòng)根據(jù)用戶喜好推薦相關(guān)的商品，優(yōu)化商品搜索的準(zhǔn)確度和聚合能力，對(duì)搜索結(jié)果排序優(yōu)先級(jí)進(jìn)行優(yōu)化。
（2）在商品詳情頁的展示上突出用戶關(guān)注的重點(diǎn)信息，精簡(jiǎn)信息流的呈現(xiàn)方式，減少用戶尋找信息的成本
（3）優(yōu)化加入購物車和收藏按鍵的觸達(dá)，用戶在滑屏?xí)r也能方便觸達(dá)，增加功能使用的次數(shù)。
c.提高留存
淘寶APP的留存相對(duì)而言較為穩(wěn)定，讓用戶提高、保持使用淘寶電商平臺(tái)的頻率相對(duì)而言更加重要。
d.增加收入
使用APP的用戶中有61%的付費(fèi)用戶，付費(fèi)轉(zhuǎn)化率相當(dāng)高。
e.用戶推薦
淘寶本身用戶基數(shù)龐大，知名度高，個(gè)人認(rèn)為在一二線城市的用戶基本已經(jīng)達(dá)到飽和，傳播工作需要針對(duì)三四線城市的渠道下沉，在這些地區(qū)針對(duì)用戶價(jià)格敏感度高的特性開展類似拼多多的拼團(tuán)轉(zhuǎn)發(fā)和打折促銷活動(dòng)，擴(kuò)大這部分用戶的使用率。

2.研究用戶在不同時(shí)間尺度下的行為規(guī)律，找到用戶在不同時(shí)間周期下的活躍規(guī)律

以一周為周期進(jìn)行波動(dòng)，一周中的高峰期在周末，符合上班族作息時(shí)間中的空閑時(shí)期。而平時(shí)一天中，在晚十點(diǎn)后出現(xiàn)高峰期。針對(duì)高峰期進(jìn)行營銷活動(dòng)收益最高，此時(shí)使用人數(shù)最多，活動(dòng)容易觸達(dá)用戶，營銷活動(dòng)的形式可以通過促銷、拼團(tuán)、直播等形式進(jìn)行。

3.找到用戶對(duì)不同種類商品的偏好，找到針對(duì)不同商品的營銷策略

商品售賣主要依靠長尾商品的累積效應(yīng)，而非爆款商品的帶動(dòng)。而瀏覽次數(shù)前列的商品甚至沒有進(jìn)入銷量前30，說明這些吸引用戶更多注意力的商品沒有很好的轉(zhuǎn)化為實(shí)際銷量。
針對(duì)瀏覽量高而銷量不高的這部分商品，需要提高的是用戶從點(diǎn)擊進(jìn)入商品詳情頁到最終購買的體驗(yàn)。作為商家端可以從以下幾個(gè)方面提高銷售額：
（1） 商品詳情頁的實(shí)際價(jià)格是否相比展示價(jià)格偏差過大，有的商家為了吸引用戶點(diǎn)擊在商品展示頁投放的價(jià)格具有較強(qiáng)吸引力，但實(shí)際價(jià)格偏高，在用戶心中反而引起反感
（2）詳情頁的信息流展示是否合理，是否將用戶最想看到的部分置于容易看到的位置，便于信息的獲取
（3）優(yōu)化商品展示的形式，利用視頻等方式給用戶更直觀的感受，提高照片的美觀程度
（4）評(píng)論區(qū)評(píng)價(jià)管理，尤其對(duì)于差評(píng)區(qū)的用戶反饋進(jìn)行認(rèn)真對(duì)待，提高自身服務(wù)質(zhì)量

4.通過RFM模型找出最具價(jià)值的核心付費(fèi)用戶群，對(duì)這部分用戶的行為進(jìn)行分析

R和F評(píng)分都很高的用戶是體系中的最有價(jià)值用戶，需要重點(diǎn)關(guān)注，并且活動(dòng)投放時(shí)需謹(jǐn)慎對(duì)待，綜合評(píng)分大于4的用戶屬于活躍的成熟客戶，可以積極推送營銷信息，對(duì)于綜合評(píng)分為4~2的用戶，可以通過傳遞新品，通知店鋪活動(dòng)信息，對(duì)于評(píng)分小于2的用戶，運(yùn)營活動(dòng)可以重點(diǎn)針對(duì)這部分用戶，提高用戶使用產(chǎn)品的頻率，可以通過拼團(tuán)打折、積分兌換等活動(dòng)喚起用戶注意力。