淘寶APP使用者行為資料分析

一. 提出問題和應用模型

1.本次分析的目的是想通過對淘寶使用者行為進行資料分析，為以下問題提供解釋和改進建議：

（1）分析使用者使用APP過程中的常見電商分析名額，确定各個環節的流失率。

（2）利用假設檢驗思想分析流失原因，找到需要改進的環節。

（3）研究使用者在不同時間尺度下的行為規律，分析不同尺度下的轉化率。

（4）找到使用者對不同種類商品的偏好和針對不同商品的營銷政策。

（5）将使用者群按照價值劃分，并針對不同的使用者群提出營銷建議。

2.應用分析模型

（1）基于AARRR漏鬥模型分析使用者行為

本文通過常用的電商資料分析名額，采用AARRR漏鬥模型拆解使用者進入APP後的每一步行為。AARRR模型是根據使用者使用産品全流程的不同階段進行劃分的，針對每一環節的使用者流失情況分析出不同環節的優化優先級，主要通過以下個各階段來進行分析：

AARRR漏鬥模型

（2）基于RFM模型找出有價值的使用者

由于不同使用者對公司帶來的收益差别很大，而且根據二八定律(20%的有價值使用者能帶來80%的收益)，是以需要對使用者進行價值評價，找到最有價值的使用者群，并針對這部分使用者進行差異化營銷。這裡參考RFM 模型對使用者進行評價：

• R-Recency（最近一次購買時間）

R指使用者上一次消費的時間，上一次購物時間距今最近的顧客通常在近期響應營銷活動的可能性也最大，對于APP而言，很久沒有購物行為可能意味着使用者放棄了APP的使用，重新喚起使用者也需要更多的成本。

• F-Frequency（消費頻率）

F指使用者在某段時間内的購物次數，消費頻率越高意味着這部分使用者對産品的滿意度最高，使用者粘性最好，忠誠度也最高。

• M-Money（消費金額）

M指使用者在某段時間内的購物金額，這也是為公司帶來價值的最直接展現，而消費金額較高的使用者在使用者總體中人數較少，卻能創造出更多價值，是需要重點争取的對象。

這三個次元互相關聯，反映了每個使用者的現在價值和潛在價值，将每個次元分成R-5,F-5個區間進行評分，通過計算分數對使用者進行分類，可以有針對性地對不同類型使用者采用不同的營銷政策。

二. 了解資料

1. 資料來源

阿裡雲天池：tianchi.aliyun.com/data

資料集包含了2017年11月25日0:00至2017年12月4日0:00之間（共9天），淘寶APP移動端使用者行為資料。由于總記錄為1億條，數量過大，此處隻處理200萬條資料作為代表，這部分資料中包含19544名使用者（按user_id劃分）的行為記錄。

2.各字段含義

因為資料量太大，直接用excel打不開，這裡通過python輸出資料的前5行，檢視大體格式。

import pandas as pd
data=pd.read_csv('UserBehavior.csv')
print(data.head())      

輸出結果如下圖所示：

利用python檢視表頭資訊

這裡也可以看出此檔案是沒有字段名的，是以導入資料庫時需要自己建立表頭資訊。

資料解釋

3.資料導入

（1）建立資料庫userbehavior;(在navicat中完成)

（2）因為源資料沒有字段名，是以要先建立表頭資訊，設定主鍵

create table user (
id int not null,
item int not null,
behavior varchar(10) not null,
category int not null,
times int not null,
constraint id_behave primary key(id,item,times));      

三．資料清洗

1.列名重命名

建表時已經将原有列名簡化，id表示使用者ID，item表示商品編号，behavior 表示使用者行為，category表示商品類别，times表示時間資訊。使用rename函數也能改。

2.一緻化處理

時間資料中的日期和小時存在于一列中，為了友善研究每天和一天内每小時使用者的行為變化，将其按date和time拆分成兩列。

• 将時間戳轉換為日期類型

set sql_safe_updates=0;
 alter table user add column datetime timestamp(0) null;
 update user set datetime = from_unixtime(times);      

時間戳轉換為日期類型

• 将其按date和time拆分成兩列

alter table user add column date char(10) null;--增加date一列
update user set date = substring(datetime from 1 for 10);--取出年-月-天
alter table user add column time char(10) null;--增加time一列
update user set time = substring(datetime from 12 for 2);--取出小時      

年-月-天和小時分成兩列

3.異常值處理

• 删去11-25至12-03日以外的資料

delete from user where datetime<'2017-11-25 00:00:00' or datetime>'2017-12-04 00:00:00';      

四. AARRR模型分析

利用AARRR模型，分析使用者行為和留存、流失情況，此處資料主要涉及使用者刺激和購買轉化的環節，即使用者從浏覽到最終購買整個過程。

1.日新增使用者統計首先根據id分組，按照date組内排名，确定每個使用者第一次登入的時間，篩選出的排名為1的資料即作為使用者第一次登入的資料。再根據date分組，統計每日第一次登入的使用者數即為新增使用者數。

• 将每個使用者的登入時間排名

select *, row_number() over (partition by id order by date) as user_rank from user      

• 統計每日新增使用者數DNU

select date,count(id) as DNU from 
(select *, row_number() over (partition by id order by date) as user_rank from user) as A
where A.user_rank=1 group by date order by date asc;      

分析：新增使用者在11月25日為13927人，處于最高水準，後逐日遞減。推測11月25日前後可能存在促銷活動或拉新活動。

2．使用者留存率分析

第N日留存率=新增日之後的第N日再次登入的使用者數/第一天新增總使用者數。

• 依次算出每日留存人數

select date,count(distinct id) as 留存人數,
count(distinct id)/(select count(distinct id) from user where date='2017-11-25') as 留存率 from user 
where id in (select distinct id from user where date='2017-11-25')
group by date order by date asc;      

每日留存率

分析：由上表可知，11-25之後的8日内留存率均在75%以上，且12月2日-3日的留存率超過98%，說明客戶較為穩定，浏覽意願極為強烈。說明客戶較為穩定，浏覽意願極為強烈。可能是前期的拉新活動效果較好，每日打卡領金币調動了使用者的積極性，為沖刺雙十二做準備。

3.不同時間尺度下使用者行為模式分析

• 建立使用者行為視圖(按天)

create view user_behavior as 
SELECT date,
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 user
GROUP BY date
ORDER BY date      

每天使用者行為

分析：這9天中，11月25日至12月1日使用者浏覽量變化幅度小，範圍在18萬到20萬之間，12月2日-3日有較大幅度增加，對比同樣是周末的11月25日-26日，pv無明顯上漲，是以認為12月2-3日的上漲可能與周末的雙十二預熱營銷活動效果有關。

• 建立使用者每小時的行為視圖(按小時)

create view time_behavior as SELECT time,
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 user
GROUP BY time
ORDER BY time      

分析：

(1)1-6點使用淘寶的人數處于低峰，可能是因為此時大部分使用者都處于睡眠狀态有關；6 -10點使用人數逐漸增多，10-18點使用量趨于穩定狀态；18-21點使用者行為逐漸活躍，可能與使用者下班後有時間使用淘寶購物有關，晚間21-23點達到高峰值，這個時間段是大部分人群的睡前時間，和人們喜歡在睡前購物的心理有關。夜間23-1點（次日）可能是部分人群進入睡眠，導緻使用人數下降。

(2)晚間22-23點為加購峰值，商家可以此時在購物車頁面加大活動力度或發紅包補貼，刺激使用者消費欲望，促進購買轉轉化。

4．使用者流失分析

• 計算使用者總數

select count(distinct id) as 使用者總數 from user;      

• 計算跳失率:跳失率=隻點選一次浏覽的使用者數量/總使用者通路量

select count(*) as 隻浏覽一次就離開的人數 from 
(select id from user group by id having count(behavior)=1) as A      

分析：統計時間為9天，隻浏覽一次就離開的人數為1，總使用者通路量為9969，跳失率=1/9969=0.01%，占比幾乎可以忽略不計，說明淘寶擁有足夠的吸引力讓使用者停留在APP中。

• 使用者行為數漏鬥計算

select behavior,count(*) as 行為次數 from user group by behavior;      

分析：由于收藏和加入購物車都為浏覽和購買階段之間确定購買意向的使用者行為，且不分先後順序，是以将其算作一個階段，可以看到從浏覽到有購買隻有buy/pv=2.25%的轉化率，流失率很高。下面用假設檢驗分析方法的思路來驗證轉化率低的原因。

5．假設檢驗分析方法

為了進一步找出轉化率低的原因所在，這裡提出兩個假設：

• 假設一：使用者浏覽商品時未使用收藏加購功能，導緻産生購買意願時增加了搜尋成本，進而使購買意願下降。
• 假設二：商品熱搜産品與熱銷産品不比對造成的轉化率低。

5.1驗證假設一：未把商品收藏或加入購物車增加購買麻煩，降低購買意願

• 建立購買流程視圖

create view 購買流程as 
select id,item,category,
sum(case when behavior='pv' then 1 else 0 end) as pv,
sum(case when behavior'fav' then 1 else 0 end) as fav,
sum(case when behavior='cart' then 1 else 0 end) as cart,
sum(case when behavior='buy' then 1 else 0 end) as buy 
from user
group by id,item,category;--分别按照id,item,category分組      

• 不同購買流程分析

本資料集使用者行為類型包括點選pv、收藏fav、加入購物車cart、購買buy。由之前分析可知，使用者購買流程可分為：

• 點選後直接購買：pv-buy

select count(1) as 浏覽人數 from 購買流程 where pv>0;      

select count(1) as 浏覽後直接購買人數 from 購買流程 
where pv>0 and fav=0 and cart=0 and buy>0 ;      

• 點選、收藏後購買：pv-fav-buy

select count(1) as 浏覽後收藏人數 from 購買流程 where pv>0 and fav>0;      

select count(1) as 浏覽後收藏再購買人數 from 購買流程 where pv>0 and fav>0 and cart=0 and buy>0;      

• 點選、加購後購買：pv-cart-buy

select count(1) as 浏覽後加購再購買人數 from 購買流程 where pv>0 and cart>0 ;      

select count(1) as 浏覽後加購再購買人數 from 購買流程 where pv>0 and fav=0 and cart>0 and buy>0;      

• 點選、收藏并加購後購買：pv-fav、cart-buy

select count(1) as 浏覽收藏加購人數 from 購買流程 where pv>0 and cart>0 and fav>0 ;      

select count(1) as 浏覽收藏加購再購買人數 from 購買流程 where pv>0 and fav>0 and cart>0 and buy>0;      

分析：通過上述流程轉化分析，發現在pv—fav—buy、pv—cart—buy以及pv——fav、cart—buy中的購買轉化率明顯大于pv—buy中的購買轉化率。這是因為未把商品收藏或加入購物車，使用者想要購買時就需要重新搜尋，增加了搜尋成本和麻煩，進而使得使用者購買意願下降。假設一成立。這種情況下可以通過以下方法鼓勵使用者使用購物車和收藏功能：

（1）收藏商品再下單可以領取優惠券；

（2）将商品加入購物車可以不定時享受折扣。

5.2驗證假設二：熱搜-熱銷商品比對度低

統計所有商品的購買次數，同時找到購買次數、浏覽次數、收藏次數和加入購物車次數最多的商品。取銷售排名前50的為熱銷商品，取浏覽+加購+收藏排名前50的為熱搜商品，觀察商品id重合情況。

• 提取排名前50的熱銷商品

select * from (select item,count(1) as 熱銷商品,
row_number()over(order by count(1) desc )as 熱銷排名 from user
where behavior='buy' group by item)as A where A.熱銷排名<51;      

• 提取排名前50的熱銷商品

select * from (SELECT item, COUNT(1) AS 熱搜商品 ,
row_number() over(order by COUNT(1) desc) as 熱搜排名 FROM user
WHERE behavior='pv' or behavior='fav' or behavior='cart' GROUP BY item) as A where A.熱搜排名<51;      

• 熱銷商品與熱搜商品比對度分析

select A.item,A.熱銷商品,A.熱銷排名,B.熱搜商品,B.熱搜排名 from 
(select * from (select item,count(1) as 熱銷商品,row_number()over(order by count(1) desc )as 熱銷排名 from user where behavior='buy' group by item)as A where A.熱銷排名<51) as A
inner join 
(select * from (SELECT item, COUNT(1) AS 熱搜商品 ,row_number() over(order by COUNT(1) desc) as 熱搜排名 FROM user WHERE behavior='pv' or behavior='fav' or behavior='cart' GROUP BY item) as A where A.熱搜排名<51) as B
on A.item=B.item      

分析：

(1)由此可看出，在銷量最高的前50名商品中，熱搜商品隻有5種，比對率為10%，說明熱銷産品和熱搜産品的比對度不高。假設二成立。

(2)比對度低可能有以下兩種情況：a.推薦算法效果不佳，詳情頁的資訊流展示可能不太合理，導緻首頁推薦的大部分商品可能未精準比對使用者需求，大多數人隻是點選後就離開，并沒有購買意願。針對這種情況建議淘寶APP優化推薦算法，做到精準營運。b.熱搜商品優惠力度不夠。可能商家為了吸引使用者點選在商品展示頁投放的價格具有較強吸引力，但商品詳情頁的實際價格可能遠高于展示價格，進而降低了使用者的購買意願。這時建議商家将熱搜排名靠前而熱銷排名靠後的商品加大優惠力度，實行團購打折等手段提升購買轉化率。

6.商品複購分析

• 按被購買次數劃分商品

SELECT A.`被購買次數`,COUNT(item) as '商品數' from 
(SELECT item, COUNT(id) AS '被購買次數' FROM user WHERE behavior='buy' GROUP BY item) as A 
GROUP BY A.`被購買次數`
order by A.`被購買次數` asc;      

分析：在本次統計的資料中，複購次數集中在1-4次，呈長尾分布，該階段使用者消費欲望不大。其中隻購買一次的産品有27611種，購買兩次的産品有3418種，本次分析的商品中使用者購買的共有32485種商品，19544名使用者中，被購買次數最多的商品僅為35次，沒有出現購買使用者數量非常集中的商品，而被購買一次的商品占到27611/32485=85%，說明商品售賣主要依靠長尾商品的累積效應，而非爆款商品的帶動。建議多開展營銷活動，比如淘寶的達成金主的條件限制，鼓勵使用者複購。對于使用者消費習慣的分析，通過了解使用者消費的集中時段集中實施各種營銷活動，提高投入産出比。

7.轉化率分析

• 不用時間尺度下的轉化率
• 每日付費轉化率

select date,浏覽數,付費數,付費數/浏覽數 as '轉化率' from user_behavior      

分析：11.25-12.03的9天内，淘寶APP的付費轉化率再2%-2.6%之間，其中，在11.26-11.27日轉化率呈現較大幅度增長，于11.27達到最高值2.53%，11.27-11.28發生小幅度下降，11.28-11.29呈小幅升高，11.29以後呈下降趨勢。

• 每小時付費轉化率

select time,浏覽數,付費數,付費數/浏覽數 as 轉化率 from time_behavior;      

分析：由上圖可以看出，一天内夜間3：00轉化率最低且浏覽量也很低，可能是大部分人處于睡眠狀态，上午10：00-11：00轉化率最高，其次下午15-17點的轉化率也較高，之後呈逐漸下降趨勢。雖然APP使用量和浏覽量在晚間21：00-23：00達到高峰，但此階段的付費轉化率并不高，建議商家可以在這個時間段将優惠力度加大，例如發放一些優惠券等，提升使用者的購買欲望，促進購買轉化。

• 不同類别商品的付費轉化率

SELECT category,
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 user
GROUP BY category
order by 浏覽數 desc      

分析：由上圖可以看出，商品商品類别為2735466和2885642的兩類商品的轉化率最高，分别為3.27%和3.26%，類别為154040的商品轉化率最低，為0.31%，對于商品浏覽量高，而轉化率非常低的，建議商家對商品購買流程使用轉化漏鬥進行分析并進行改善。

五、基于RFM理論找出有價值的使用者

M-消費金額，由于資料源中沒有相關的金額資料，不計入評分。

R-最近購買時間

使用者資料的時間範圍為9天，最近購買時間的區間為0-9，将其分為5檔，0-1,1-2,3-4，5-6，7-8分别對應評分1到5。

• 建立使用者購買時間到12-04日的距離視圖

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

• 建立R得分視圖

create view RR as 
SELECT id, 
(CASE WHEN B BETWEEN 7 AND 8 THEN 1 
WHEN B BETWEEN 5 AND 6 THEN 2
WHEN B BETWEEN 3 AND 4 THEN 3
WHEN B BETWEEN 1 AND 2 THEN 4
WHEN B BETWEEN 0 AND 1 THEN 5
ELSE null END) AS R
FROM pay_B ORDER BY R DESC      

F-消費頻率

• 檢視使用者購買次數

SELECT id,count(id)as '次數' from user where behavior='buy' group by id order by 次數 desc;      

分析：付費使用者中消費次數從低到高為1-72次，将其分為5檔，1-15，16-30，31-45，46-60,61-72分别對應評分0到4。

• 建立使用者購買次數視圖

CREATE VIEW pay_F AS SELECT id, COUNT(*) AS A FROM user WHERE behavior='buy' GROUP BY id;      

• 建立F得分視圖

create view FF as 
SELECT id, (CASE WHEN A BETWEEN 1 AND 15 THEN 1 
WHEN A BETWEEN 16 AND 30 THEN 2
WHEN A BETWEEN 31 AND 45 THEN 3
WHEN A BETWEEN 46 AND 57 THEN 4
WHEN A BETWEEN 58 AND 72 THEN 5 ELSE 0 END) as F
FROM pay_F ORDER BY F DESC;      

• 建立RFM得分視圖

create view RR_FF as SELECT RR.id,RR.R,FF.F from RR left join FF on RR.id=FF.id      

• 計算R、F平均值

select avg(R) as R平均值,avg(F) as F平均值 from RR_FF;      

• 按得分與平均分的關系進行使用者分類

select 使用者分類,count(使用者ID) as 人數
from (select 使用者ID,
(case when R >3.5544 and F >1.3249 then "重要價值客戶"
when R >3.5544 and F <1.3249 then "重要發展客戶"
when R <3.5544 and F >1.3249 then "重要保持客戶"
when R <3.5544 and F <1.3249 then "重要挽留客戶"
else 0 end) as 使用者分類
from RR_FF) as a group by 使用者分類;      

分析：

（1）重要價值客戶是指R,F得分都在平均分以上的使用者，此類使用者的購買力和粘性較高，且近期極有可能再次購買；重要發展客戶是指R得分較高，而F得分較低的使用者，此類使用者近期買過但粘性不高；重要保持客戶是指F得分較高，但R值較低的使用者，此類使用者粘性較好但近期購買行為較少；重要挽留客戶是指R，F得分都在平均分以下，粘性不高且近期很少購買，此類使用者如果不進行挽留将會成為流失使用者。

（2）根據使用者分類結果，使用者最主要集中在重要發展客戶和重要挽留客戶，應針對不用的客戶實行不同的營運政策。

六. 結論與建議

本文分析了淘寶APP使用者行為資料共200萬條，删去991條異常資料後，從五個不同角度提出業務問題，使用AARRR模型和RFM模型分析資料給出如下結論和建議。

1.一天内使用者最活躍的時間段是21-23點，尤其是22點-23點的這一小時。

建議：把握該黃金時段，集中進行營銷活動提高使用者購買轉化率，例如平台帶貨直播、分會場促銷、限時搶購等。且更多選擇熱搜熱銷的商品類目和商品，迎合使用者需求。

2. 使用者這9日内的留存率均在75%以上，且12月2日-3日的留存率超過98%，說明客戶較為穩定，浏覽意願極為強烈。這可能是因為存在新使用者打卡活動等，調動了使用者的登入積極性。

建議：多舉辦老使用者拉活階梯式獎勵、新使用者浏覽集積分等活動，培養使用者登入習慣，調動浏覽熱情。發放新使用者滿減優惠券、首單直減等活動，提高新使用者的購買轉化率。

3. 使用者在點選→收藏→購買、點選→加入購物車→購買、點選→收藏和加購→購買這三個流程的購買轉化率都明顯大于點選→購買中的購買轉化率。這可能是因為收藏或加購能提供購買便捷，提高使用者購買意願。

建議：增加跨店滿減、收藏加購送商品贈品、送福利等活動，引導使用者收藏加購行為，進而提高使用者購買轉化率。

4.商品熱搜和熱銷并不比對，大部分商品可能未精準比對使用者需求或優惠力度不夠，并沒有促使使用者進行購買。

建議：

（1）優化推薦算法，優先推薦熱搜、熱銷排名靠前的商品，降低使用者搜尋成本。

（2）優化搜尋功能，根據使用者畫像更精準推薦商品，使熱搜和熱銷更為比對。

（3）加大熱搜商品的優惠力度，提高熱搜商品的購買轉化率。

（4）針對浏覽量高而購買轉化率低的商品，應改善商品頁、詳情頁以及評論區管理。

5. 使用者最主要集中在重要發展客戶和重要挽留客戶。

建議：針對不用客戶實行不同營運政策。

（1）對于占比最大的重要發展客戶，消費頻率低但最近一次消費時間間隔短，可以通過發放滿減優惠券等方法提高消費頻率；

（2）對于占比第二大的重要挽留客戶，消費頻率低且最近一次消費間隔長，面臨使用者流失風險，可以通過電話郵件等形式主動聯系客戶，調查問題所在，并有針對地進行挽回；

（3）對于占比較大的重要價值客戶，應提供專屬的VIP服務，通過服務品質進一步調高其粘性；

（4）對于占比較低的重要保持客戶，消費頻次高但最近一次消費時間間隔長，可以通過短信郵件等方式提醒上新、邀請參加老使用者回報活動等提高複購率。

​