某電商平臺(tái)用戶行為數(shù)據(jù)分析

時(shí)間：2023-03-16 00:24:02 | 來(lái)源：電子商務(wù)

時(shí)間：2023-03-16 00:24:02 來(lái)源：電子商務(wù)

一、背景與分析目的

近些年來(lái)，隨著經(jīng)濟(jì)的增長(zhǎng)及科技的發(fā)展，電商行業(yè)發(fā)展迅速，運(yùn)營(yíng)過(guò)程中所產(chǎn)生的可供操作數(shù)據(jù)隨之爆炸性增長(zhǎng)，隨著行業(yè)競(jìng)爭(zhēng)加劇，純流量運(yùn)營(yíng)的時(shí)代已經(jīng)遠(yuǎn)去。為了盈利，各家電商公司越來(lái)越注重精細(xì)化的運(yùn)營(yíng)，注重對(duì)于數(shù)據(jù)的分析。

本文旨在通過(guò)對(duì)某電商平臺(tái)用戶的行為（用戶行為包括點(diǎn)擊、喜歡、購(gòu)買(mǎi)、加購(gòu)）數(shù)據(jù)進(jìn)行分析，進(jìn)而提出可以落地的業(yè)務(wù)建議，以促進(jìn)業(yè)務(wù)的增長(zhǎng)。

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

1、原始數(shù)據(jù)集介紹

數(shù)據(jù)集(UserBehavior)包含了2017年11月25日至2017年12月3日之間，某電商用戶的行為（行為包括點(diǎn)擊、購(gòu)買(mǎi)、加購(gòu)、喜歡）。

2、字段含義

字段	說(shuō)明
user_id	整數(shù)型，用戶ID
item_id	整數(shù)型，商品ID
category_id	整數(shù)型，商品品類ID
behavior_type	字符型，行為類型（'pv','buy','cart','fav'）
timestamp	整數(shù)型，時(shí)間戳

behavior_type（用戶行為）一共有四種，分別是：

行為類型	說(shuō)明
pv	page view,瀏覽
buy	購(gòu)買(mǎi)
cart	加入購(gòu)物車(chē)
fav	收藏

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

1、查詢是否有重復(fù)值

category_id字段指的是商品的大類名稱，item_id字段指的是是某個(gè)商品的唯一標(biāo)識(shí)，因此，查重時(shí)使用item_id字段已經(jīng)足夠了

select *from userbehaviorgroup by user_id,item_id,behavior_type,timestamphaving count(*) > 1;查找出來(lái)所有字段均為Null值，說(shuō)明該數(shù)據(jù)集中沒(méi)有重復(fù)值。

2、查詢是否存在空值

select *from userbehaviorwhere user_id is NULL or category_id is NULLor item_id is NULL or behavior_type is NULLor timestamp is NULL;查找出來(lái)所有字段均為Null值，說(shuō)明該數(shù)據(jù)集中沒(méi)有空值。

3、如何處理時(shí)間戳數(shù)據(jù)?

在計(jì)算機(jī)中，「時(shí)間戳」一般是指 Unix 時(shí)間戳，即自從 Unix 紀(jì)元（格林威治時(shí)間 1970 年 1 月 1 日 00:00:00）到當(dāng)前時(shí)間的秒數(shù)。時(shí)間戳不會(huì)跟著時(shí)區(qū)的改變而改變，所以不管在哪個(gè)時(shí)區(qū)，時(shí)間戳都是一致的。

為了后文分析方便，我們通過(guò)'timestamp'字段，又建立了一系列的時(shí)間字段，代碼實(shí)現(xiàn)如下：

-- 給表添加一列叫dates,用于記錄行為發(fā)生的日期，如'2017-11-25'為2017年11月25日alter table userbehavior add datesvarchar(255);-- 給新列dates賦值update userbehaviorset dates =from_unixtime(timestamp,'%Y-%m-%d');-- 給表添加一列叫hours,用于記錄發(fā)生的小時(shí)時(shí)間，如'21'為晚上9點(diǎn)alter table userbehavior add hoursvarchar(255);-- 給新列hours賦值-- 特別注意，此處的時(shí)間格式中,"小時(shí)“部分的字符需要用大寫(xiě)的H，才能轉(zhuǎn)為24小時(shí)制update userbehaviorset hours = hour(from_unixtime(timestamp,'%H:%i:%s'));-- 給表添加一列叫datetime，用于記錄行為發(fā)生的日期和時(shí)間，如'2017-11-25 21:30:00'為2017年11月25日晚上9點(diǎn)30分alter table userbehavior add datetimevarchar(255);-- 給新列datetime賦值update userbehaviorset datetime =from_unixtime(timestamp,'%Y-%m-%d %H:%i:%s');-- 給表添加一列叫weekday,用于記錄行為發(fā)生在星期幾，如'1'為星期一alter table userbehavior add weekdayvarchar(255);/*-mysql中，可以通過(guò)函數(shù)WEEKDAY()將日期轉(zhuǎn)化為星期幾，但是索引是從0開(kāi)始，即0表示星期一、1表示星期二.....-為了使該字段更符合閱讀習(xí)慣，可以將其進(jìn)行加1操作，結(jié)果即為1對(duì)應(yīng)星期一，2對(duì)應(yīng)星期二...*/update userbehaviorset weekday = WEEKDAY(datetime) + 1;接著，我們將超出分析范圍的時(shí)間范圍剔除掉：

-- 將超出指定時(shí)間范圍的數(shù)據(jù)剔除掉DELETEfrom userbehaviorwhere dates < '2017-11-25'or dates > '2017-12-03';經(jīng)過(guò)處理后，數(shù)據(jù)集如下（以下給出部分?jǐn)?shù)據(jù)）：

四、明確問(wèn)題

1、整體認(rèn)識(shí)：描述性統(tǒng)計(jì)分析

首先，我們要對(duì)業(yè)務(wù)有個(gè)整體性的認(rèn)識(shí)，本案例主旨是分析用戶行為，所以要知道總共有多少會(huì)員。

select count(distinct user_id) as 會(huì)員數(shù)from userbehavior;即在2017年11月25日至2017年12月3日之間，總共記錄了983名會(huì)員的行為數(shù)據(jù)。

2、AIPL漏斗分析

知道整體有多少個(gè)會(huì)員后，接下來(lái)要進(jìn)一步對(duì)他們的行為進(jìn)行觀察，分析的是電商用戶行為，在電商領(lǐng)域我們比較常用的方法是阿里提出的AIPL營(yíng)銷模型：

A（Awareness）認(rèn)知：廣告曝光、頁(yè)面瀏覽人群
I（Interested）興趣：關(guān)注互動(dòng)、瀏覽店鋪主頁(yè)、瀏覽產(chǎn)品詳情頁(yè)、收藏加購(gòu)
P（Purchase）購(gòu)買(mǎi)：支付
L（Loyalty）忠誠(chéng)：復(fù)購(gòu)、評(píng)論、分享

AIPL營(yíng)銷模型可以將會(huì)員行為與相應(yīng)的運(yùn)營(yíng)周期聯(lián)系起來(lái)。

【AI】部分就是運(yùn)營(yíng)中的拉新，處于此階段時(shí)，瀏覽、收藏、加購(gòu)的用戶數(shù)越多，運(yùn)營(yíng)拉新指標(biāo)就完成地越好，但此部分同樣意味著拉新成本的支出。

【PL】則對(duì)應(yīng)著運(yùn)營(yíng)的收割行為，通俗地講，即是將前期推廣引進(jìn)的新客變現(xiàn)，以此回收拉新成本，提升ROI。

回到本案例，將會(huì)員的瀏覽、收藏、加購(gòu)、購(gòu)買(mǎi)消費(fèi)劃入到AIPL的各個(gè)流程中，便于我們根據(jù)不同的流轉(zhuǎn)情況，對(duì)運(yùn)營(yíng)現(xiàn)狀有整體的把握。

接下來(lái)，利用CASE表達(dá)式對(duì)表結(jié)構(gòu)進(jìn)行【行列互換】，此操作通過(guò)【創(chuàng)建視圖】來(lái)完成：

create view behavior ASselect user_id,datetime,dates,hours,max(case behavior_type when 'pv' then 1 else 0 end) as view,max(case behavior_type when 'fav' then 1 else 0 end) as favor,max(case behavior_type when 'cart' then 1 else 0 end) as cart,max(case behavior_type when 'buy' then 1 else 0 end) as buyfrom userbehaviorgroup by user_id,datetime;創(chuàng)建出的視圖如下所示（給出部分?jǐn)?shù)據(jù)）：

計(jì)算處A、I、P的值：

對(duì)A、I、P階段的行為進(jìn)行統(tǒng)計(jì)，代碼實(shí)現(xiàn)如下：

select sum(view) as 'A',sum(favor)+sum(cart) as 'I',sum(buy) as 'P'from behavior;計(jì)算L的值：

對(duì)于會(huì)員復(fù)購(gòu)行為，計(jì)算思路是：

• 通過(guò)【窗口函數(shù)】統(tǒng)計(jì)會(huì)員的每一次消費(fèi)動(dòng)作，即記錄第n次消費(fèi)
• 有了每個(gè)會(huì)員的第n次消費(fèi)記錄字段后，即可將第一次消費(fèi)列為首次消費(fèi)，而第二次開(kāi)始的皆為【復(fù)購(gòu)消費(fèi)】
• 最后統(tǒng)計(jì)【復(fù)購(gòu)消費(fèi)】行為，即為“L”即可

第一步，查詢每位會(huì)員第N次消費(fèi)的記錄（并給出部分?jǐn)?shù)據(jù)）

select user_id,datetime,buy,dense_rank() over (partition by user_id order by datetime) as n_consumefrom behaviorwhere buy = 1order by user_id,datetime;第二步，剔除掉用戶第一次購(gòu)買(mǎi)的記錄并計(jì)算復(fù)購(gòu)次數(shù)

select sum(buy) as Lfrom(select user_id,datetime,buy,dense_rank() over (partition by user_id order by datetime asc) as n_consumefrom behaviorwhere buy = 1) as twhere n_consume > 1;3、總結(jié)

AIPL漏斗圖如下圖所示，

進(jìn)行整體分析的目的是為了找出用戶運(yùn)營(yíng)存在的問(wèn)題或特點(diǎn)，并提出接下來(lái)的分析目標(biāo)。一般情況下，要用到到對(duì)比分析來(lái)判斷用戶運(yùn)營(yíng)的優(yōu)劣，比如同比和環(huán)比，因?yàn)榘咐腥鄙傧嚓P(guān)的數(shù)據(jù)，故無(wú)法使用對(duì)比分析，僅從業(yè)務(wù)經(jīng)驗(yàn)出發(fā)進(jìn)行判斷。

接下來(lái)，根據(jù)業(yè)務(wù)經(jīng)驗(yàn)按輕重緩急列出問(wèn)題的重要性排序，有序進(jìn)行分析，這樣業(yè)務(wù)才能在有限資源的條件下選擇分析結(jié)論進(jìn)行落地實(shí)踐：

1）【A→I】拉新環(huán)節(jié)，環(huán)節(jié)轉(zhuǎn)化率為9.12%，相對(duì)來(lái)說(shuō)較差，可能拉新完成率較低，值得注意。將該問(wèn)題列為優(yōu)先級(jí)：重要緊急P(pán)0

2）【P→L】收割（促進(jìn)客戶復(fù)購(gòu)），環(huán)節(jié)轉(zhuǎn)化率為64.31%，做得較好，可以據(jù)此總結(jié)業(yè)務(wù)經(jīng)驗(yàn)或復(fù)購(gòu)規(guī)律，進(jìn)一步推廣應(yīng)用。將該事項(xiàng)列為優(yōu)先級(jí)：重要不緊急P(pán)1

3）【I→P】收割（促進(jìn)客戶首次購(gòu)買(mǎi)），環(huán)節(jié)轉(zhuǎn)化率為23.02%，效果還可以，可以進(jìn)一步挖掘用戶購(gòu)買(mǎi)轉(zhuǎn)化路徑。將該事項(xiàng)列為優(yōu)先級(jí)：不重要不緊急P(pán)2

五、數(shù)據(jù)分析

【P0：A→I】環(huán)節(jié)的轉(zhuǎn)化率較低，此案例應(yīng)用【人貨場(chǎng)】方法進(jìn)行分析

【P1：P→L】環(huán)節(jié)中用戶復(fù)購(gòu)表現(xiàn)教好，通過(guò)【用戶復(fù)購(gòu)分析】，挖掘、總結(jié)復(fù)購(gòu)特征

【P2：I→P】環(huán)節(jié)目的在于進(jìn)一步透析用戶購(gòu)買(mǎi)行為，采用【AIPL+RFM】分析方法進(jìn)行分析

1、【P0：A→I】

此環(huán)節(jié)計(jì)劃通過(guò)【人貨場(chǎng)】分析，解決A用戶認(rèn)知到I興趣用戶行為轉(zhuǎn)化率低的問(wèn)題。

1）【人】

該部分主要在于觀察用戶行為特點(diǎn)，以總結(jié)經(jīng)驗(yàn)。此案例中，數(shù)據(jù)維度有限，會(huì)員自身行為相關(guān)的數(shù)據(jù)除了商品外就是時(shí)間，商品計(jì)劃在【貨】部分進(jìn)行分析，【人】部分旨在分析用戶A到I高效轉(zhuǎn)化的時(shí)間特征。

首先，計(jì)算各時(shí)段AI的轉(zhuǎn)化分布：

計(jì)算思路：

• 第一步，查詢出各時(shí)段會(huì)員的瀏覽次數(shù)
• 第二步，查詢出每個(gè)小時(shí)會(huì)員收藏與加入購(gòu)物車(chē)行為的次數(shù)
• 第三步，將前兩步的查詢結(jié)果進(jìn)行聯(lián)結(jié)，查詢出各時(shí)段A→I的轉(zhuǎn)化率情況

#第一步，查詢出每個(gè)小時(shí)會(huì)員的瀏覽次數(shù)select hour(datetime) as '小時(shí)',count(*) as 'A-瀏覽'from behaviorwhere view = 1group by hour(datetime)order by hour(datetime) ASC;#第二步，查詢出每個(gè)小時(shí)會(huì)員進(jìn)行收藏與加入購(gòu)物車(chē)行為的次數(shù)select hour(datetime) as '小時(shí)',count(*) as 'I-興趣'from behaviorwhere favor = 1 or cart = 1group by hour(datetime)order by hour(datetime) asc;#第三步，根據(jù)前兩步計(jì)算出A→I轉(zhuǎn)化率的均值select a.`小時(shí)`,avg(`A-瀏覽`) as `A-瀏覽 均值`,avg(`I-興趣`) as `I-興趣 均值`,concat(round(avg(`I-興趣`)/avg(`A-瀏覽`),3)*100,'%') as 'A→I轉(zhuǎn)化率'from(select hour(datetime) as '小時(shí)',count(*) as 'A-瀏覽'from behaviorwhere view = 1group by hour(datetime)) as aleft join(select hour(datetime) as '小時(shí)',count(*) as 'I-興趣'from behaviorwhere favor = 1 or cart = 1group by hour(datetime)) as ion a.`小時(shí)` = i.`小時(shí)`group by a.`小時(shí)`order by a.`小時(shí)`;可以看到，在查詢結(jié)果中，有很多不同的值，根據(jù)對(duì)比分析方法，我們需要【比較基準(zhǔn)】才知道這些轉(zhuǎn)化率哪些高，哪些低，才可進(jìn)一步判斷。這里，以常見(jiàn)的“平均值”作為對(duì)比的基準(zhǔn)，代碼實(shí)現(xiàn)如下（即上一步去掉分組的步驟）：

select a.`小時(shí)`,avg(`A-瀏覽`) as `A-瀏覽 均值`,avg(`I-興趣`) as `I-興趣 均值`,concat(round(avg(`I-興趣`)/avg(`A-瀏覽`),3)*100,'%') as 'A→I轉(zhuǎn)化率'from(select hour(datetime) as '小時(shí)',count(*) as 'A-瀏覽'from behaviorwhere view = 1group by hour(datetime)) as aleft join(select hour(datetime) as '小時(shí)',count(*) as 'I-興趣'from behaviorwhere favor = 1 or cart = 1group by hour(datetime)) as ion a.`小時(shí)` = i.`小時(shí)`order by a.`小時(shí)`;每個(gè)時(shí)段的瀏覽人數(shù)都不同，選擇瀏覽值大于平均值3733的時(shí)段來(lái)觀察，在這些時(shí)段中，我們選擇轉(zhuǎn)化率大于平均值9.1%的時(shí)段。

這些時(shí)段既是瀏覽人數(shù)多，又是轉(zhuǎn)化率高的時(shí)段，即11點(diǎn)、15點(diǎn)-17點(diǎn)、19點(diǎn)、22-23點(diǎn)。其中，23點(diǎn)為轉(zhuǎn)化率大于10%，為最大值，可作為最佳投放時(shí)間。

2）【貨】

【貨】分析部分需要分析【人貨匹配】情況，進(jìn)而針對(duì)性地將貨品根據(jù)會(huì)員行為進(jìn)行調(diào)整。

分析之前，先查詢出AI階段涉及的商品種類的總數(shù)量：

select count(distinct item_id) as 'A'from userbehaviorwhere behavior_type = 'pv';結(jié)果如圖所示，共有60592款商品被有效瀏覽。

接下來(lái)，計(jì)算會(huì)員感興趣的商品：

select count(distinct item_id) as 'I'from userbehaviorwhere behavior_type in ('favor','cart');接下來(lái)，結(jié)合【假設(shè)檢驗(yàn)分析方法】，針對(duì)有效瀏覽與產(chǎn)生興趣的商品數(shù)差異較大的情況，提出以下的假設(shè)。

假設(shè)：用戶偏好的商品主要集中在少數(shù)商品，而大部分長(zhǎng)尾商品被錯(cuò)誤地推薦給用戶。

驗(yàn)證思路：

將被瀏覽最多的前100款商品以及用戶最感興趣（即加入購(gòu)物車(chē)、收藏）的前100款商品進(jìn)行交叉匹配，如果交叉出的商品數(shù)較少，則假設(shè)成立，反之，則假設(shè)不成立。

計(jì)算思路如下：

• 第一步，查詢出被瀏覽最多的前100款商品，按降序排列
• 第二步，查詢出用戶最感興趣的前100款商品，按降序排列
• 第三步，將前兩步的查詢結(jié)果進(jìn)行交叉匹配，查看交叉數(shù)量

#第一步，查詢出被瀏覽最多的前100款商品，按降序排列select item_id,count(*) as 'A'from userbehaviorwhere behavior_type = 'pv'group by item_idorder by A DESClimit 100;查詢結(jié)果如下（給出部分?jǐn)?shù)據(jù)預(yù)覽）：

#第二步，查詢出用戶最感興趣的前100款商品，按降序排列select item_id,count(*) as 'I'from userbehaviorwhere behavior_type in ('favor','cart')group by item_idorder by I DESClimit 100;查詢結(jié)果如下（給出部分?jǐn)?shù)據(jù)預(yù)覽）：

#第三步，計(jì)算交叉數(shù)量select count(*) as 'AI交叉商品數(shù)'from(select item_id,count(*) as 'A'from userbehaviorwhere behavior_type = 'pv'group by item_idorder by A DESClimit 100) as ACinner JOIN(select item_id,count(*) as 'I'from userbehaviorwhere behavior_type in ('favor','cart')group by item_idorder by I DESClimit 100) as ICon AC.item_id = IC.item_id;結(jié)果顯示，僅有5款商品既是高瀏覽量，也是高收藏/加購(gòu)的，

假設(shè)成立：用戶偏好的商品主要集中在少數(shù)商品，而大部分長(zhǎng)尾商品則是被錯(cuò)誤地推薦到用戶。進(jìn)而可判斷：【人貨匹配】效率低。

3）【場(chǎng)】

【場(chǎng)】廣義上來(lái)說(shuō)指的是與用戶的觸點(diǎn)，在線上電商，則是在線店鋪、平臺(tái)、投放渠道等。

從這個(gè)角度，分析此段時(shí)間淘寶平臺(tái)的活動(dòng)情況：

• 結(jié)合業(yè)務(wù)經(jīng)驗(yàn)，數(shù)據(jù)時(shí)段在雙十一之后，雙十二之前，是兩個(gè)大促的中間節(jié)點(diǎn)。
• 查詢2017年度雙十二的相關(guān)信息如下：

活動(dòng)：天貓雙12年終品牌盛典
預(yù)熱時(shí)間：2017年12月7日00:00:00-2017年12月11日23:59:59；
上線時(shí)間：2017年12月12日00:00:00-2017年12月12日23:59:59。

• 通過(guò)上述活動(dòng)時(shí)間可知，分析的時(shí)間段正處于雙十二活動(dòng)預(yù)熱時(shí)間前夕的低潮期，客觀上，營(yíng)銷效果整體比較平淡，進(jìn)而影響AI轉(zhuǎn)化率。

4）【人貨場(chǎng)】分析總結(jié)

• 人：為了提升AI轉(zhuǎn)化率，可選擇在瀏覽值大于3733且轉(zhuǎn)化率大于9.1%的時(shí)間段加大投放力度；其中，23點(diǎn)轉(zhuǎn)化率大于10%，為最佳投放時(shí)間。
• 貨：人貨匹配的效率低，平臺(tái)推薦的商品大部分為長(zhǎng)尾商品，不能很好地吸引用戶興趣，需要進(jìn)一步針對(duì)用戶優(yōu)化商品投放信息。
• 場(chǎng)：從平臺(tái)活動(dòng)周期角度來(lái)說(shuō)，分析時(shí)間段正處于大促活動(dòng)預(yù)熱前的低潮期，此客觀因素也在一定程度上導(dǎo)致轉(zhuǎn)化率低。

2、【P1：P→L】

此部分我們進(jìn)行【用戶復(fù)購(gòu)分析】進(jìn)而挖掘復(fù)購(gòu)行為特征。

結(jié)合具體的業(yè)務(wù)，復(fù)購(gòu)分析的目的是為了優(yōu)化用戶觸達(dá)策略，即何時(shí)何地向哪些已購(gòu)用戶推送什么商品可提升復(fù)購(gòu)率。此案例中，將通過(guò)分析用戶【復(fù)購(gòu)周期】以解決業(yè)務(wù)中觸達(dá)時(shí)間的問(wèn)題。

對(duì)于此，現(xiàn)在分析用戶復(fù)購(gòu)周期問(wèn)題，從業(yè)務(wù)角度主要包括何時(shí)觸達(dá)和觸達(dá)多久兩部分。

1）何時(shí)觸達(dá)

可通過(guò)計(jì)算用戶平均回購(gòu)周期，即可在用戶發(fā)生購(gòu)買(mǎi)行為后，在平均回購(gòu)周期內(nèi)對(duì)其進(jìn)行營(yíng)銷觸達(dá)。

計(jì)算步驟：

第一步，建立一張視圖，記錄了所有用戶的購(gòu)買(mǎi)記錄

#創(chuàng)建一個(gè)視圖，記錄所有用戶的購(gòu)買(mǎi)記錄CREATE view consumeas select `user_id`,`dates`,dense_rank() OVER (PARTITION BY `user_id` ORDER BY `dates`) AS `consume_num` from `behavior` where `buy` = 1group by `user_id`,`dates` order by `user_id`,`dates`;結(jié)果如下（給出部分?jǐn)?shù)據(jù)預(yù)覽）：

第二步，根據(jù)上一步的視圖查詢出所有用戶每次消費(fèi)的回購(gòu)周期：

-- 計(jì)算出每個(gè)用戶每次消費(fèi)的回購(gòu)周期select a.user_id,a.dates,a.consume_num,datediff(a.dates,b.dates) as '回購(gòu)周期（天）'FROM(select * from consume) as ainner join(select * from consume) as bon a.user_id = b.user_idand b.consume_num = a.consume_num - 1;結(jié)果如下（給出部分?jǐn)?shù)據(jù)預(yù)覽）：

第三步，基于以上查詢結(jié)果，可以將每人次的回購(gòu)周期進(jìn)行平均，取得最終的平均回購(gòu)周期

-- 將每人次的回購(gòu)周期進(jìn)行平均，取得最終的平均回購(gòu)周期select avg(datediff(a.dates,b.dates)) as '平均回購(gòu)周期（天）'from(select * from consume) as ainner join(select * from consume) as bwhere a.user_id = b.user_idand b.consume_num = a.consume_num - 1;結(jié)論：根據(jù)計(jì)算結(jié)果，預(yù)計(jì)在用戶購(gòu)買(mǎi)行為發(fā)生后的2-3天內(nèi)對(duì)其進(jìn)行觸達(dá)會(huì)有較好的效果。

2）觸達(dá)多久

通常情況下，我們需要計(jì)算出不同時(shí)段購(gòu)買(mǎi)的客戶的最長(zhǎng)消費(fèi)周期，供業(yè)務(wù)觸達(dá)時(shí)參考，但由于本案例中時(shí)間間隔較短，因此此項(xiàng)數(shù)據(jù)參考意義不大，不計(jì)算。

3、【P2：I→P】

此前AIPL漏斗分析顯示，平臺(tái)用戶復(fù)購(gòu)率高達(dá)63%，也就是說(shuō)，只要成功促進(jìn)用戶發(fā)生首次購(gòu)買(mǎi)行為，接下來(lái)就由較高的概率復(fù)購(gòu)，此部分的重點(diǎn)放在如何提升AI→P上。

1）用戶首購(gòu)特征

為了提高AI客戶的購(gòu)買(mǎi)轉(zhuǎn)化率，可以通過(guò)高購(gòu)買(mǎi)率客戶與低購(gòu)買(mǎi)率客戶對(duì)比，挖掘高購(gòu)買(mǎi)率客戶特征，進(jìn)而指導(dǎo)業(yè)務(wù)上的運(yùn)營(yíng)策略。

先創(chuàng)建一個(gè)視圖，統(tǒng)計(jì)每個(gè)客戶的購(gòu)買(mǎi)率及其他數(shù)據(jù)，代碼實(shí)現(xiàn)如下：

#創(chuàng)建視圖，統(tǒng)計(jì)每個(gè)客戶的購(gòu)買(mǎi)率與其他數(shù)據(jù)create view user_featureASselectuser_id,sum(view) as '瀏覽',sum(favor) as '收藏',sum(cart) as '加購(gòu)',sum(buy) as '購(gòu)買(mǎi)',(sum(cart) + sum(favor))/sum(view) as '加購(gòu)率',sum(buy)/(sum(view) + sum(cart) + sum(favor)) as '購(gòu)買(mǎi)率',dense_rank() over (order by sum(buy)/(sum(view) + sum(cart) + sum(favor)) desc) as '購(gòu)買(mǎi)率排序'from behaviorgroup by user_id;創(chuàng)建完的視圖如下所示（給出部分?jǐn)?shù)據(jù)預(yù)覽）：

select *from user_featureorder by 購(gòu)買(mǎi)率 desc;計(jì)算完每個(gè)客戶的購(gòu)買(mǎi)率之后，接著挖掘高購(gòu)買(mǎi)率客戶與低購(gòu)買(mǎi)率客戶的特征。

結(jié)合【二八原則】進(jìn)行判斷，可以認(rèn)為前20%為高購(gòu)買(mǎi)率客戶，最后20%為低購(gòu)買(mǎi)率客戶。

第一步，查看購(gòu)買(mǎi)率排序的名次情況（可能出現(xiàn)幾位客戶在同一名詞的情況）

select count(distinct 購(gòu)買(mǎi)率排序) as 總名次from user_feature;根據(jù)387 * 20% ≈ 77，高購(gòu)買(mǎi)率客戶可以被定義為前77名的客戶，而根據(jù)387 - 77 = 310，低購(gòu)買(mǎi)率客戶可以被定義為310名之后的客戶。

第二步，查詢出高購(gòu)買(mǎi)率客戶的特征

select avg(瀏覽) as '平均瀏覽數(shù)',avg(收藏 + 加購(gòu)) as '平均興趣數(shù)',avg(加購(gòu)率) as '平均加購(gòu)率'from user_featurewhere '購(gòu)買(mǎi)率排序' <= 77;select count(distinct category_id) as '購(gòu)買(mǎi)品類集中度' from userbehaviorwhere behavior_type ='buy'and user_id in (select distinct user_id from user_featurewhere `購(gòu)買(mǎi)率排序` <= 77);第三步，查詢出低購(gòu)買(mǎi)率客戶的特征

select avg(`瀏覽`) as '平均瀏覽數(shù)',avg(`收藏`+`加購(gòu)`) as '平均興趣數(shù)',avg(`加購(gòu)率`) as '平均加購(gòu)率'from user_featurewhere `購(gòu)買(mǎi)率排序` > 310 and `購(gòu)買(mǎi)率` > 0;select count(distinct category_id) as '購(gòu)買(mǎi)品類集中度' from userbehaviorwhere behavior_type ='buy'and user_id in (select distinct user_id from user_featurewhere `購(gòu)買(mǎi)率排序` > 310);總結(jié)：

• 高購(gòu)買(mǎi)率客戶瀏覽商品數(shù)91遠(yuǎn)低于低購(gòu)買(mǎi)率客戶瀏覽商品數(shù)195；
• 高購(gòu)買(mǎi)率客戶平均加購(gòu)率11%略大于低購(gòu)買(mǎi)率客戶平均加購(gòu)率10%；
• 高購(gòu)買(mǎi)率客戶品類集中度399遠(yuǎn)大于低購(gòu)買(mǎi)率客戶品類集中度96；

綜上所述，我們得到的人群畫(huà)像：

高購(gòu)買(mǎi)率人群瀏覽商品數(shù)量少，但加購(gòu)率高，大概率為決策型顧客，看對(duì)了商品就著手購(gòu)買(mǎi)；

低購(gòu)買(mǎi)率人群更多是糾結(jié)型顧客，需要反復(fù)瀏覽商品來(lái)確認(rèn)最終想要的。

此外，低購(gòu)買(mǎi)率人群品類集中度很高，建議列出這些品類，有目的地優(yōu)化品類信息，減少用戶的瀏覽跳失率。

2）AIPL+RFM分析

最后，為了促進(jìn)用戶購(gòu)買(mǎi)，需要進(jìn)行精細(xì)化的用戶運(yùn)營(yíng)，即可通過(guò)RFM模型分析方法實(shí)現(xiàn)，由于數(shù)據(jù)限制，并沒(méi)有用戶購(gòu)買(mǎi)金額數(shù)據(jù)，故此處與AIPL模型相結(jié)合，重新定義R、F、M：

R：客戶最近一次購(gòu)買(mǎi)離分析日期的距離，用來(lái)判斷購(gòu)買(mǎi)用戶活躍狀態(tài)，間隔越短，分值越高

F：客戶收藏、加購(gòu)行為的次數(shù)，次數(shù)越多，分值越高

M：客戶購(gòu)買(mǎi)行為次數(shù)，次數(shù)越多，分值越高

第一步，計(jì)算每位用戶最近一次購(gòu)買(mǎi)行為的間隔天數(shù)

#第一步，計(jì)算每位用戶最近一次購(gòu)買(mǎi)行為的間隔天數(shù)select user_id,datediff('2017-12-03',dates) as 'R'from consume as awhere consume_num = (select max(consume_num)from consume as bwhere a.user_id = b.user_id);查詢結(jié)果如下（給出部分?jǐn)?shù)據(jù)）：

第二步，計(jì)算每位用戶收藏、加購(gòu)的行為次數(shù)以及購(gòu)買(mǎi)的行為次數(shù)

select user_id,(sum(favor) + sum(cart)) as 'F',sum(buy) as 'M'from behaviorgroup by user_id;查詢結(jié)果如下（給出部分?jǐn)?shù)據(jù)）：

第三步，將前兩步的查詢結(jié)果按user_id字段進(jìn)行合并，創(chuàng)建出RFM的視圖

create view RFMasselect r.user_id,R,F,Mfrom(select user_id,datediff('2017-12-03',dates) as 'R'from consume as awhere consume_num = (select max(consume_num)from consume as bwhere a.user_id = b.user_id)) as rleft join (select user_id,(sum(favor) + sum(cart)) as 'F',sum(buy) as 'M'from behaviorgroup by user_id) as fmon r.user_id = fm.user_id;查詢結(jié)果如下（給出部分?jǐn)?shù)據(jù)）：

第四步，計(jì)算出RFM的比較基準(zhǔn)，這里使用的是均值，但具體業(yè)務(wù)應(yīng)具體分析：

#第四步，計(jì)算出RFM的閾值(即比較基準(zhǔn)）select avg(R),avg(F),avg(M)from RFM;第五步，根據(jù)RFM的比較基準(zhǔn)以及RFM的定義，將每位可以劃分到不同的用戶價(jià)值組中

create view user_RFM asselect user_id, case when R < 2.4501 and F > 9.3115 and M > 2.8018 then '重要價(jià)值用戶' when R < 2.4501 and F > 9.3115 and M < 2.8018 then '一般價(jià)值用戶' when R < 2.4501 and F < 9.3115 and M > 2.8018 then '重要發(fā)展用戶' when R < 2.4501 and F < 9.3115 and M < 2.8018 then '一般發(fā)展用戶' when R > 2.4501 and F > 9.3115 and M > 2.8018 then '重要喚回用戶' when R > 2.4501 and F > 9.3115 and M < 2.8018 then '一般喚回用戶' when R > 2.4501 and F < 9.3115 and M > 2.8018 then '重要挽留用戶' when R > 2.4501 and F < 9.3115 and M < 2.8018 then '一般挽留用戶' end as '用戶價(jià)值族群'from RFM;結(jié)果如下所示（給出部分?jǐn)?shù)據(jù)預(yù)覽）：

第六步，統(tǒng)計(jì)各組用戶數(shù)及用戶占比，用于對(duì)用戶運(yùn)營(yíng)現(xiàn)狀進(jìn)行分析，并給出運(yùn)營(yíng)策略方向。

select `用戶價(jià)值族群`, count(user_id) '用戶數(shù)', concat(round(count(user_id)/983*100,3),'%') as '用戶數(shù)占比'from user_RFMgroup by `用戶價(jià)值族群`order by `用戶價(jià)值族群`;結(jié)果如下：

上圖所示為RFM模型分組結(jié)果，對(duì)比分析往往需要和對(duì)照組比對(duì)分析，但因數(shù)據(jù)限制，此處結(jié)合業(yè)務(wù)經(jīng)驗(yàn)進(jìn)行判斷：

從【用戶RFM轉(zhuǎn)化路徑】角度來(lái)看：重要價(jià)值用戶占比較少，一般由一般價(jià)值用戶、重要發(fā)展用戶、一般發(fā)展用戶轉(zhuǎn)化而來(lái)。

一般價(jià)值價(jià)值用戶占比較少，重要發(fā)展用戶，一般發(fā)展用戶占比較多，可以有限對(duì)這兩個(gè)人群進(jìn)行“升級(jí)”

六、提出建議

首先，通過(guò)AIPL營(yíng)銷模型（漏斗分析方法），對(duì)用戶現(xiàn)狀有整體的認(rèn)識(shí)，并分析、總結(jié)出三個(gè)分析目標(biāo)。現(xiàn)對(duì)分析結(jié)論結(jié)合業(yè)務(wù)場(chǎng)景總結(jié)經(jīng)驗(yàn)及提出建議。

1、【A→I】【人貨場(chǎng)】業(yè)務(wù)建議

--從“認(rèn)知”到“興趣”階段的轉(zhuǎn)化率過(guò)低

通過(guò)【人貨場(chǎng)】分析，可得以下業(yè)務(wù)建議：

• 推廣部門(mén)：優(yōu)化投放策略，在A→I轉(zhuǎn)化率高的時(shí)間點(diǎn)，尤其是23點(diǎn)加大投放力度，提升AI轉(zhuǎn)化率，進(jìn)而提升整體轉(zhuǎn)化
• 產(chǎn)品部門(mén)：針對(duì)【人貨匹配】效率低的問(wèn)題，及時(shí)處理低轉(zhuǎn)化率的商品，總結(jié)高轉(zhuǎn)化率產(chǎn)品特性，開(kāi)發(fā)新品
• 運(yùn)營(yíng)部門(mén)：針對(duì)【人貨匹配】效率低的問(wèn)題，參考高轉(zhuǎn)化商品描述，優(yōu)化產(chǎn)品標(biāo)題、內(nèi)容描述；針對(duì)大促前低潮期，提出吸引行的促銷政策，提高此階段銷售

2、【P→L】【復(fù)購(gòu)分析】業(yè)務(wù)建議

--從“購(gòu)買(mǎi)”到“忠誠(chéng)”階段的轉(zhuǎn)化率高。

通過(guò)【用戶復(fù)購(gòu)分析】后，可得以下建議：

• 用戶部門(mén)：針對(duì)用戶復(fù)購(gòu)周期特性，客戶消費(fèi)后2-3天內(nèi)結(jié)合回饋手段即使進(jìn)行觸達(dá)

3、【I→P】【購(gòu)買(mǎi)特征+RFM】業(yè)務(wù)建議

--表現(xiàn)較好

通過(guò)分析，可得建議如下。

1）針對(duì)【高購(gòu)買(mǎi)率用戶特征】

• 用戶部門(mén)：針對(duì)高購(gòu)買(mǎi)率客戶，即決策型客戶，可以搭配精準(zhǔn)推薦模型，觸達(dá)優(yōu)質(zhì)商品，提升購(gòu)買(mǎi)率，針對(duì)低購(gòu)買(mǎi)率客戶，即糾結(jié)型客戶，可能更多為【熱門(mén)商品領(lǐng)域】，觸達(dá)熱門(mén)商品，提高購(gòu)買(mǎi)率

2）使用【AIPL + RFM】分析方法

• P0優(yōu)先級(jí)：

重要價(jià)值用戶（R高F高M(jìn)高）維護(hù)：用戶處于購(gòu)買(mǎi)活躍期，感興趣的商品多，購(gòu)買(mǎi)次數(shù)也多 → 針對(duì)興趣產(chǎn)品，保持一定頻率的觸達(dá)，持續(xù)維護(hù)。

一般價(jià)值用戶（R高F高M(jìn)低）：用戶處于購(gòu)買(mǎi)活躍期，感興趣的商品多，購(gòu)買(mǎi)次數(shù)少 → 針對(duì)該部分客戶，目的就是要讓他們購(gòu)買(mǎi)，所以可以和運(yùn)營(yíng)部門(mén)溝通，降低利潤(rùn)，給出有力折扣促進(jìn)購(gòu)買(mǎi)，進(jìn)而轉(zhuǎn)為重要價(jià)值用戶。

P1優(yōu)先級(jí)：

一般發(fā)展用戶（R高F低M低）：用戶處于購(gòu)買(mǎi)活躍期，感興趣的商品少，購(gòu)買(mǎi)次數(shù)少 → 可通過(guò)商品組合推薦，即通過(guò)連帶提升該人群購(gòu)買(mǎi)頻次，進(jìn)而轉(zhuǎn)為重要發(fā)展用戶。

重要發(fā)展用戶（R高F低M高）：用戶處于購(gòu)買(mǎi)活躍期，感興趣的商品少，購(gòu)買(mǎi)次數(shù)多 → 通過(guò)關(guān)聯(lián)算法，找出與興趣商品關(guān)聯(lián)高的商品進(jìn)行推薦觸達(dá)，提高用戶收藏、加購(gòu)數(shù)，進(jìn)而轉(zhuǎn)為重要價(jià)值用戶。