用戶行為分析(SQL+Tableau+AARRR模型)

blank

用戶行為分析(SQL+Tableau+AARRR模型)

# 一、數據來源及含義

1、數據來源

阿里雲天池: tianchi.aliyun.com/data UserBehavior.csv

原始數據有100150806條,數據量過大,不方便使用Mysql進行操作,本例先使用python對數據進行處理,選取其中的999999條數據用於本次用戶行為分析。

2、字段含義

blankblank

user_id:用戶id item_id:商品Id category_id:商品類別id behavior_type:用戶行為類型timestamp:時間戳

其中behavior_type包括:

pv(點擊)

cart(加入購物車)

fav(收藏)

buy(購買)

二、數據清洗

1、添加輔助字段

① 時間戳轉日期型

ALTERTABLEuserbehavior_randomADDCOLUMNdatetimeTIMESTAMP(0)NULL;UPDATEuserbehavior_randomSETdatetime=FROM_UNIXTIME(timestamp);

② 提取年月日

ALTERTABLEuserbehavior_randomADDCOLUMNdatesCHAR(10)NULL;UPDATEuserbehavior_randomSETdates=SUBSTR(datetime,1,10);

③ 提取時分秒

ALTERTABLEuserbehavior_randomADDCOLUMNhoursCHAR(10)NULL;UPDATEuserbehavior_randomSEThours=SUBSTR(datetime,12,8);

④ 提取小時(Tableau中繪圖時使用)

ALTERTABLEuserbehavior_randomADDCOLUMNtimeCHAR(10)NULL;UPDATEuserbehavior_randomSETtime=SUBSTR(datetime,12,2);

進行上述處理後結果如下:

blankblank

2、異常值

① 查看時間的最大最小值

SELECTMAX(timestamp),MIN(timestamp),MAX(datetime),MIN(datetime)FROMuserbehavior_random;

blankblank

② 刪除異常值本次分析針對2017年11月25日至2017年12月3日數據,對異常值進行刪除,共刪除508行。

DELETEFROMuserbehavior_randomWHEREdates<'2017-11-25'ORdates>'2017-12-03';

③查看數據集

SELECT*FROMuserbehavior_random

目前還剩999491條數據

3、重複值

SELECTDISTINCT*FROMuserbehavior_random

顯示有999491條數據,無重複值。

4、缺失值

SELECTCOUNT(user_id),COUNT(item_id),COUNT(category_id),COUNT(behavior_type),COUNT(timestamp)FROMuserbehavior_random

blankblank

均有999491條數據,無缺失值。

三、模型構建

1、用戶流量分析

① 日活躍用戶(DAU)人數分佈

2017年11月25日-2017年12月1日活躍人數較為平穩,但也有增長趨勢,12月1日-12月3日增長相對較迅速,活躍用戶人數約增加26%。

blankblank

② PV、UV、PV/UV

SELECTCOUNT(DISTINCTuser_id)AS'UV',(SELECTCOUNT(*)FROMuserbehavior_randomWHEREbehavior_type='pv')AS'PV',(SELECTCOUNT(*)FROMuserbehavior_randomWHEREbehavior_type='pv')/(COUNT(DISTINCTuser_id))AS'PV/UV'FROMuserbehavior_random;

UV:獨立訪客數

PV:頁面流量次數

PV/UV:平均一個獨立訪問者所瀏覽的頁面訪問量

③ PV、UV每日變化趨勢

SELECTdates,SUM(CASEWHENbehavior_type='pv'THEN1ELSE0END)AS'日均访问量',COUNT(DISTINCTuser_id)AS'日均UV用户'FROMuserbehavior_randomGROUPBYdatesORDERBYdates;

將上述查詢所得表格導入Tableau中,繪製PV、UV每日變化趨勢圖。發現在2017年11月25日-2017年12月3日期間,PV與UV隨日期的變化趨勢相似,11月25日-12月1日較平穩,12月2日開始有明顯增長,增長率約為27%。

blankblank

④ PV、UV每時變化趨勢

將userbehavior_random表導入Tableau中,繪製PV、UV每時變化趨勢圖。可以發現18點到23點,uv波動不大,而pv波動較大,說明用戶開始頻繁訪問。

blankblank

⑤ 四種用戶行為每時變化趨勢

blankblank
blankblank

從上圖可以看出:

(1)四種行為的分佈趨勢基本一致。

(2)用戶在晚上18點到23點會頻繁點擊瀏覽網頁,收藏與加入購物車數量也隨之增加

(3)其中購買曲線與收藏曲線趨勢更為接近,在一定程度上說明客戶對於收藏商品更為偏愛,而購物車裡的一部分商品可能只是作為一種比較或者是排除購買的作用,可以關注客戶購物車和收藏的商品進行精準推送。

⑥ 跳失率

跳失率=只有瀏覽行為的用戶數/總用戶數

SELECTSUM(IF(t.fav_num=0ANDt.cart_num=0ANDt.buy_num=0,1,0))/COUNT(t.user_id)*100AS'跳失率'FROM(SELECTuser_id,SUM(IF(behavior_type='fav',1,0))fav_num,SUM(IF(behavior_type='cart',1,0))cart_num,SUM(IF(behavior_type='buy',1,0))buy_numFROMuserbehavior_randomGROUPBYuser_id)t

可得跳失率約為23.98%,說明詳情頁對用戶吸引力扔有待提高。

2、用戶購買行為分析

① 日交易用戶人數分佈

2017年11月25日-2017年12月1日交易人數呈平穩狀態,並有少量增加,12月1日-12月3日交易用戶人數約增加32%。

blankblank

② 用戶复購

复購率= 購買次數兩次及其以上的人數/有購買行為的用戶總數

selectconcat(round((sum(casewhen购买量>=2then1else0end)/sum(casewhen购买量>0then1else0end))*100,2),'%')as复购率fromuserbehavior1;

3、建立用戶各行為轉化漏斗模型

①創建用戶行為視圖

createviewuserbehavior1asselectuser_id,count(behavior_type)as'用戶行為總數',SUM(casewhenbehavior_type='pv'then1else0end)as'點擊量',sum(casewhenbehavior_type='buy'then1else0end)as'購買量',sum(casewhenbehavior_type='fav'then1else0end)as'收藏數',sum(casewhenbehavior_type='cart'then1else0end)as'加購數'fromuserbehavior_randomgroupbyuser_id;

blankblank

②計算用戶各行為發生總數

selectsum(点击量)astotal_pv,sum(购买量)astotal_buy,sum(收藏数)astotal_fav,sum(加购数)astotal_cartfromuserbehavior1;

③計算各行為的轉化率

selectconcat(round(sum(點擊量)/sum(用戶行為總數)*100,2),'%')astotal_to_pv,concat(round((sum(收藏數)+sum(加購數))/sum(點擊量)*100,2),'%')aspv_to_favCart,concat(round(sum(購買量)/sum(點擊量)*100,2),'%')aspv_to_buyfromuserbehavior1;

使用Tableau繪製用戶漏斗模型,在2017年11月25日-2017年12月3日期間,用戶的點擊率為89.54%,收藏或加購的佔比為9.47%,最終購買率為2.22%;

blankblank

4、用戶留存分析

n日後留存率=(註冊後的n日後還登錄的用戶數)/第一天新增總用戶數

以2017-11-25作為第一天,計算次日、3日、7日留存率

--第一天新用戶數createtableliucunasselectcount(distinctuser_id)as第一天新用戶數fromuserbehavior_randomwheredates='2017-11-25';--第二天留存用戶數altertableliucunaddcolumn第二天留存用戶INT;updateliucunset第二天留存用戶=(selectcount(distinctuser_id)fromuserbehavior_randomwheredates='2017-11-26'anduser_idin(SELECTuser_idFROMuserbehavior_randomWHEREdates='2017-11-25'));--第三天留存用戶數altertableliucunaddcolumn第三天留存用戶INT;updateliucunset第三天留存用戶=(selectcount(distinctuser_id)fromuserbehavior_randomwheredates='2017-11-27'anduser_idin(SELECTuser_idFROMuserbehavior_randomWHEREdates='2017-11-25'));--第五天留存用戶數altertableliucunaddcolumn第五天留存用戶INT;updateliucunset第五天留存用戶=(selectcount(distinctuser_id)fromuserbehavior_randomwheredates='2017-11-29'anduser_idin(SELECTuser_idFROMuserbehavior_randomWHEREdates='2017-11-25'));altertableliucunaddcolumn第七天留存用戶INT;updateliucunset第七天留存用戶=(selectcount(distinctuser_id)fromuserbehavior_randomwheredates='2017-12-01'anduser_idin(SELECTuser_idFROMuserbehavior_randomWHEREdates='2017-11-25'));

blankblank

SELECTCONCAT(ROUND(100*第二天留存用戶/第一天新用戶數,2),'%')AS次日留存率,CONCAT(ROUND(100*第三天留存用戶/第一天新用戶數,2),'%')AS三日留存率,CONCAT(ROUND(100*第五天留存用戶/第一天新用戶數,2),'%')AS五日留存率,CONCAT(ROUND(100*第七天留存用戶/第一天新用戶數,2),'%')AS七日留存率fromliucun;

用戶留存率相對穩定,七日留存率略有回升,可能是因為當天12月1日是星期五,正好是客戶活躍度上升的一個轉折點,可能是有購物優惠活動。

四、總結

本項目運用MySQL+Tableau+python,從100150806條用戶行為數據中選取999999條數據進行分析,下面結合AARRR模型,對應用戶生命週期中的5個重要環節,對用戶行為進行總結。

1、Acquisition--用戶獲取由PV、UV每時變化趨勢圖可知18點到23點是用戶頻繁訪問的時間,也是獲取更多潛在客戶的黃金時間。可以用最小的獲客成本進行小遊戲邀請、熱門社交或小視頻平台合作推廣、app賣家推送等增加渠道曝光量進而增加渠道轉化率。本項目中跳失率約為23.98%,說明詳情頁對用戶吸引力扔有待提高,可以進行精准人群推廣、視覺優化等方式降低跳失率。

2、Activation--用戶激活本項目中2017年11月25日-2017年12月1日DAU較為平穩,12月1日-12月3日增長相對較迅速。本項目分析了用戶的四種行為,包括點擊、購買、加入購物車、收藏。點擊量佔總行為的89.54%,而收藏和加入購物車的用戶僅佔9.47%,最終購買的用戶只有2.22%,轉折點在加入購物車和收藏,要考慮為什麼用戶瀏覽了那麼多商品卻只有9.47 %的用戶加入購物或收藏,可能這中間用戶花了太多的時間卻沒有挑選到滿意的商品,那麼有一些客戶就流失掉了。可以採取問卷調查的方式分析用戶行為推送用戶可能感興趣的商品、優化購物界面使界面佈局更加人性化等方式更好的滿足用戶需求,激活更多用戶。

3、Retention--提高留存在本項目中用戶留存率相對穩定,七日留存率略有回升,可能是因為當天12月1日是星期五,正好是客戶活躍度上升的一個轉折點,可能是有購物優惠活動。為了提高留存,可以讓用戶養成習慣,在一定程度上增加用戶對產品的依賴性。 ①每日給用戶推送可能感興趣的商品;②一些簽到的小遊戲或者簽到領積分活動,增加用戶登錄的頻率,進而增加用戶瀏覽時長;③對於年購買量比較大的用戶,為了增加這些用戶的粘性,可以推出vip服務,讓這些用戶享受折扣和優惠券,進而提高高價值用戶的留存率和對平台的忠誠度;④大力推廣直播跟用戶互動,計算親密指數並進行排行,可以讓用戶與店主之間建立信任,進而增加客戶的忠誠度。

4、Revenue--增加收入為了增加收入要提高成交轉化率、复購率以及加購或收藏產品的購買率。 ①提高成交轉化率,優化用戶推薦系統,或者邀請明星做測評推廣;②用戶复購率為33.65%,說明這些用戶對該平台不太滿意,可以通過獎勵積分或者優惠券等方式鼓勵用戶重複購買;③通過分析找出價值用戶的購買偏好,產品和類目等,給價值用戶制定個性化的產品推薦,從而提高用戶體驗,進而提高購買率。

5、Referral--用戶傳播要想讓用戶自發傳播,那這個產品口碑一定要非常好,而且能夠充分滿足用戶的需求,甚至給用戶帶來期望的需求。所以,要好好打磨產品。可以採用一些增長策略,像現在比較普遍的老帶新裂變行銷,把握用戶心理策劃一些噱頭式的活動等。還可以拼團、分享得優惠券、分享到朋友圈額外送積分等方式,鼓勵用戶進行分享和傳播。

五、補充

1、電商數據分析指標

blankblank

2、AARRR模型

blankblank

What do you think?

Written by marketer

blank

Tableau項目-超市銷售數據分析

blank

3小時精通Tableau圖表製作(18類)