一、項(xiàng)目背景介紹：
2014年是阿里巴巴集團(tuán)移動(dòng)電商業(yè)務(wù)快速發(fā)展的一年，例如2014雙11大促中移動(dòng)端成交占比達(dá)到42.6%，超過(guò)240億元。相比PC時(shí)代，移動(dòng)端網(wǎng)絡(luò)的訪問(wèn)是隨時(shí)隨地的，具有更豐富的場(chǎng)景數(shù)據(jù)，比如用戶的位置信息、用戶訪問(wèn)的時(shí)間規(guī)律等。
通過(guò)數(shù)據(jù)分析，能夠挖掘數(shù)據(jù)背后豐富的內(nèi)涵，為移動(dòng)用戶在合適的時(shí)間、合適的地點(diǎn)精準(zhǔn)推薦合適的內(nèi)容。
本案例的目標(biāo)是從該數(shù)據(jù)進(jìn)行隨機(jī)抽樣，并用mysql進(jìn)行分析，提高自己對(duì)電商指標(biāo)體系的認(rèn)識(shí)。
二、數(shù)據(jù)說(shuō)明
1、字段說(shuō)明
三、分析思路
1、提出問(wèn)題：
1）分析用戶購(gòu)物過(guò)程中的常見(jiàn)監(jiān)控指標(biāo)，了解運(yùn)營(yíng)現(xiàn)狀，查看各環(huán)節(jié)的漏斗轉(zhuǎn)化情況，并找到需要改進(jìn)的節(jié)點(diǎn)；
2）研究用戶在不同維度下的行為規(guī)律，了解用戶行為特征，優(yōu)化運(yùn)營(yíng)策略；
3）利用RFM模型對(duì)用戶進(jìn)行分類，指導(dǎo)運(yùn)營(yíng)針對(duì)不同價(jià)值用戶進(jìn)行精細(xì)化運(yùn)營(yíng)；
4）了解用戶生命周期，針對(duì)不同周期的用戶采取不同的運(yùn)營(yíng)策略。
2、指標(biāo)和字段解讀
通過(guò)用戶和用戶行為路徑可以分析PV、UV、PV/UV、跳失率、總訂單量等運(yùn)營(yíng)指標(biāo)；
通過(guò)用戶行為和時(shí)間可以分析用戶的購(gòu)物行為特征；
通過(guò)用戶和商品、商品類別可以分析用戶的購(gòu)買商品偏好；用戶和時(shí)間可以分析用戶的購(gòu)買時(shí)間偏好，便于對(duì)不同商品和時(shí)間偏好的用戶采取個(gè)性化時(shí)間推薦（push、短信等推送）；
通過(guò)商品類別和用戶行為可以分析不同商品類別受歡迎程度，指導(dǎo)運(yùn)營(yíng)進(jìn)行商品的上新或下架；
通過(guò)商品類別和時(shí)間可以分析不同商品類別的熱銷時(shí)間段，便于做活動(dòng)的推廣；
結(jié)合AARRR模型，可以分析用戶的生命周期，劃分不同用戶所處的周期階段，采用差異化運(yùn)營(yíng)；
通過(guò)時(shí)間和用戶的生命周期字段，結(jié)合RFM模型可以給用戶做價(jià)值分類，對(duì)客戶進(jìn)行差異化管理。
四、數(shù)據(jù)清洗
（查看數(shù)據(jù)清洗流程：http://www.itdecent.cn/p/adb82624df14)
將csv導(dǎo)入mysql的方法：
切換命令行菜單：https://jingyan.baidu.com/article/f00622280752dbfbd3f0c815.html
導(dǎo)入數(shù)據(jù)：https://blog.csdn.net/qq_25504271/article/details/78911151
1）選擇子集
導(dǎo)入之前已選擇好
2）列名重命名
無(wú)需更改列名
3）數(shù)據(jù)類型轉(zhuǎn)換
可以在設(shè)計(jì)表菜單欄更好數(shù)據(jù)類型
4）數(shù)據(jù)去重
查詢重復(fù)值：
select *,count(1)
from user1
group by user_id,item_id,behavior_type,item_id,time
having COUNT(user_id)>1;
存在重復(fù)值，但由于同一用戶同一個(gè)行為在一小時(shí)內(nèi)是可能存在多次的，因此這里不做去重處理。
5）缺失值處理
查詢?nèi)笔е担?SELECT
COUNT(user_id)/(SELECT COUNT(*) FROM USER1) AS '用戶ID字段的非空比例'
,COUNT(item_id)/(SELECT COUNT(1) FROM USER1) AS '商品ID字段的非空比例'
,COUNT(behavior_type)/(SELECT COUNT(1) FROM USER1) AS '行為路徑字段的非空比例'
,COUNT(item_category)/(SELECT COUNT(1) FROM USER1) AS '品類字段的非空比例'
,COUNT(time)/(SELECT COUNT(1) FROM USER1) AS '時(shí)間字段的非空比例'
FROM USER1
WHERE user_id IS NOT NULL;
經(jīng)查詢，無(wú)缺失值
6）關(guān)聯(lián)數(shù)據(jù)
由于只有一個(gè)表格，無(wú)需做表關(guān)聯(lián)（如果需要關(guān)聯(lián)，可以到分析過(guò)程中有需要時(shí)進(jìn)行關(guān)聯(lián)，這樣會(huì)提高M(jìn)ySQL的性能。
7）異常值處理
檢查時(shí)間是否異常
SELECT MIN(time), MAX(time)
FROM USER1;
檢查用戶行為類別是否異常
SELECT DISTINCT behavior_type
FROM USER1;
無(wú)需處理異常值
8）數(shù)據(jù)標(biāo)準(zhǔn)化整理
日期數(shù)據(jù)整理：
為方便后續(xù)的分析，將日期數(shù)據(jù)分為日期和時(shí)間兩個(gè)維度
1、新增一列date，用于儲(chǔ)存日期值
-- 新增列：alter table 表名 add column 列名 varchar(20);
alter table user1 add column date varchar(20) NOT NULL AFTER time;
2、復(fù)制time列的數(shù)據(jù)到date列
-- 更新列：UPDATE table_name SET field1=new-value1, field2=new-value2 [WHERE Clause]
UPDATE user1
SET date=time
3、將date轉(zhuǎn)化為年月日
UPDATE USER1
SET date = DATE_FORMAT(date, '%Y-%m-%d');
4、將time轉(zhuǎn)化為小時(shí)
alter table user1 add column hour varchar(20) NOT NULL AFTER date;（增加列）
方法一：
UPDATE user1
SET hour=time （賦值）
UPDATE USER1
SET hour = DATE_FORMAT(hour,'%H');（截取時(shí)間）
方法二：
UPDATE USER1
SET time = left(SUBSTRING_INDEX(time,' ',-1),2);
有多種方法可以實(shí)現(xiàn)數(shù)據(jù)截取操作（時(shí)間函數(shù)和字符串函數(shù)都可以操作，使用時(shí)間函數(shù)的時(shí)候需要注意截取后的數(shù)據(jù)格式類型需要轉(zhuǎn)化，否則會(huì)報(bào)錯(cuò)，截取時(shí)間后的格式不能是datetime）
行為數(shù)據(jù)整理：
將用戶行為數(shù)據(jù)進(jìn)行替換： 1：pv 2：fav 3：cart 4：buy
將behavior_type列進(jìn)行更改：
UPDATE user1
SET behavior_type=(case when behavior_type=1 then' PV'
when behavior_type=2 then 'fav'
when behavior_type=3 then 'cart'
when behavior_type=4 then 'buy'
end)
需要注意字符串要加引號(hào)
以上就完成了數(shù)據(jù)的整理工作。
五、指標(biāo)的構(gòu)建
1、總體運(yùn)營(yíng)指標(biāo)：
1）流量指標(biāo)：
計(jì)算頁(yè)面訪客數(shù)(pv)、獨(dú)立訪客數(shù)(uv)、人均點(diǎn)擊數(shù)(uv/pv)
1）計(jì)算頁(yè)面訪客數(shù)(pv)、獨(dú)立訪客數(shù)(uv)、人均點(diǎn)擊數(shù)(uv/pv)
SELECT
COUNT(*) AS 'pv'
,COUNT(DISTINCT user_id) AS 'uv'
,COUNT(*)/COUNT(DISTINCT user_id) AS '月uv/pv'
,COUNT(*)/COUNT(DISTINCT user_id)/30 AS '日uv/pv'
FROM user1
WHERE behavior_type="PV"
頁(yè)面訪客：987911次、獨(dú)立訪客數(shù)：8474位、人均點(diǎn)擊次數(shù)116.58次。
95/30≈3.89次，日人均點(diǎn)擊次數(shù)大概為3.89次/人/天
2）每日流量指標(biāo)變化趨勢(shì)
通過(guò)Navicat導(dǎo)出數(shù)據(jù)進(jìn)行可視化處理：
pv、uv指標(biāo)呈正相關(guān)性；
三個(gè)指標(biāo)在大部分時(shí)間走勢(shì)平穩(wěn)，由于雙十二的影響，從2014-12-11開(kāi)始上升，到2014-12-12達(dá)到峰值，2014-12-13結(jié)束回到正常水平。
2、AARRR漏斗轉(zhuǎn)化率
1）按照頁(yè)面訪客計(jì)算漏斗轉(zhuǎn)化率
1）按照頁(yè)面訪客計(jì)算漏斗轉(zhuǎn)化率
SELECT
behavior_type
,count(*)
FROM user1
GROUP BY behavior_type
由于在購(gòu)物環(huán)節(jié)中，收藏和加入購(gòu)物車行為沒(méi)有嚴(yán)格的先后之分，可將兩個(gè)個(gè)步驟作為同一步，最終得到用戶購(gòu)物行為各環(huán)節(jié)轉(zhuǎn)化率，如下：
從整體轉(zhuǎn)化率來(lái)看：瀏覽- 收藏/加購(gòu)轉(zhuǎn)化率僅為5%，總體購(gòu)買轉(zhuǎn)化率為1%，說(shuō)明有大部分的用戶在瀏覽后未進(jìn)行下一步操作，平時(shí)“逛街看看”成為一種習(xí)慣；
從節(jié)點(diǎn)轉(zhuǎn)化率來(lái)看：瀏覽-加購(gòu)/收藏環(huán)節(jié)轉(zhuǎn)化率很低，收藏/加購(gòu)-購(gòu)買的轉(zhuǎn)化率也只有20%，說(shuō)明有相當(dāng)一部分用戶是喜歡“囤貨”，可能是為了等節(jié)假日購(gòu)買？由于整體的節(jié)點(diǎn)并不是最細(xì)的不可分割節(jié)點(diǎn)，整體的數(shù)據(jù)比較粗糙，如果需要進(jìn)一步的深入分析，需要有更細(xì)的轉(zhuǎn)化率數(shù)據(jù)（由于操作路徑每個(gè)環(huán)節(jié)都會(huì)損失一部分，因此如果能近量的較少客戶購(gòu)買時(shí)所需要的操作步驟，對(duì)提升整體轉(zhuǎn)化率應(yīng)該會(huì)有很好的提高）。
2）獨(dú)立訪客計(jì)算漏斗轉(zhuǎn)化率
用戶行為的uv
SELECT behavior_type, COUNT(DISTINCT user_id) as behavior_count
FROM USER1
GROUP BY behavior_type
ORDER BY behavior_count DESC;
fav和cart去重：
SELECT
DISTINCT user_id
FROM USER1
WHERE behavior_type='fav' or behavior_type='cart'
用戶每個(gè)環(huán)節(jié)的轉(zhuǎn)化率差不多，需要更多數(shù)據(jù)才能發(fā)現(xiàn)用戶流失原因。
3)、跳失率
瀏覽頁(yè)跳失率： = 只訪問(wèn)一次就離開(kāi)的人數(shù)/總用戶數(shù)
只瀏覽用戶
SELECT
count(DISTINCT user_id) 只瀏覽用戶
FROM user1
WHERE user_id in (
SELECT DISTINCT user_id
FROM user1
WHERE behavior_type='pv')
AND user_id not in (
SELECT DISTINCT user_id
FROM user1
WHERE behavior_type='fav')
AND user_id not in (
SELECT DISTINCT user_id
FROM user1
WHERE behavior_type='cart')
AND user_id not in (
SELECT DISTINCT user_id
FROM user1
WHERE behavior_type='buy')
只加收藏、購(gòu)物車人數(shù)
只加收藏、購(gòu)物車
SELECT
count(DISTINCT user_id) 只加收藏、購(gòu)物車用戶數(shù)
FROM user1
WHERE user_id in (
SELECT DISTINCT user_id
FROM user1
WHERE behavior_type='fav')
OR user_id in (
SELECT DISTINCT user_id
FROM user1
WHERE behavior_type='cart')
AND user_id not in (
SELECT DISTINCT user_id
FROM user1
WHERE behavior_type='buy')
（3)、訂單指標(biāo)：
1）總成交量和人均購(gòu)買次數(shù)：
總成交量和人均購(gòu)買次數(shù)：
SELECT
count(behavior_type)總成交量
,count(DISTINCT user_id)總購(gòu)買用戶數(shù)
,count(behavior_type)/count(DISTINCT user_id)人均購(gòu)買次數(shù)
FROM user1
WHERE behavior_type='buy'
2）每日總成交和人均成交情況:
每日總成交和人均成交情況
SELECT
date
,count(behavior_type)總成交量
,count(DISTINCT user_id)總購(gòu)買用戶數(shù)
,count(behavior_type)/count(DISTINCT user_id)人均購(gòu)買次數(shù)
FROM user1
WHERE behavior_type='buy'
GROUP BY date
ORDER BY date;
（4）復(fù)購(gòu)率:
1)總體復(fù)購(gòu)率
1)總體復(fù)購(gòu)率
購(gòu)買人數(shù)
SELECT
count(DISTINCT user_id) as 成交人數(shù)
FROM user1
WHERE behavior_type='buy'
購(gòu)買次數(shù)大于1
SELECT
user_id
,count(behavior_type) as 購(gòu)買次數(shù)
FROM user1
WHERE behavior_type='buy'
GROUP BY user_id
HAVING count(behavior_type)>1
復(fù)購(gòu)人數(shù)
SELECT
count(DISTINCT user_id)
FROM
(SELECT
user_id
,count(behavior_type) as 購(gòu)買次數(shù)
FROM user1
WHERE behavior_type='buy'
GROUP BY user_id
HAVING count(behavior_type)>1
) as a
復(fù)購(gòu)率=復(fù)購(gòu)人數(shù)/購(gòu)買人數(shù)=2295/4330=53%
2)商品品類銷量排名(商品復(fù)購(gòu)率)
2)商品銷售排名
SELECT
item_category
,COUNT(behavior_type)購(gòu)買次數(shù)
FROM USER1
WHERE behavior_type='BUY'
GROUP BY item_category
ORDER BY COUNT(behavior_type) DESC
LIMIT 10;
如果有更多數(shù)據(jù)，可根據(jù)商品品類屬性進(jìn)行研究和下鉆，優(yōu)化商品結(jié)果，但這里因?yàn)閿?shù)據(jù)脫敏無(wú)法進(jìn)行下鉆分析
3)用戶復(fù)購(gòu)排名
3)用戶復(fù)購(gòu)排名
SELECT
user_id
,count(behavior_type)購(gòu)買次數(shù)
FROM user1
WHERE behavior_type='BUY'
GROUP BY user_id
ORDER BY count(behavior_type) DESC
LIMIT 10;
以上用戶對(duì)平臺(tái)的忠誠(chéng)度比較高，對(duì)平臺(tái)的銷售貢獻(xiàn)度也高（利潤(rùn)貢獻(xiàn)情況還需要具體分析），對(duì)不同的客戶可以收集相應(yīng)的用戶畫(huà)像，并對(duì)用戶進(jìn)行分層管理和營(yíng)銷，從而達(dá)到精細(xì)化運(yùn)營(yíng)
2、用戶行為特征分析
（1）、用戶行為時(shí)間的特征
1）、按日期維度
1）、按日期維度
SELECT
date
,count(1)點(diǎn)擊次數(shù)
,SUM(CASE WHEN behavior_type='PV' THEN 1 ELSE 0 END)瀏覽次數(shù)
,SUM(CASE WHEN behavior_type='fav' THEN 1 ELSE 0 END)收藏次數(shù)
,SUM(CASE WHEN behavior_type='cart' THEN 1 ELSE 0 END)加購(gòu)次數(shù)
,SUM(CASE WHEN behavior_type='buy' THEN 1 ELSE 0 END)購(gòu)買次數(shù)
,COUNT(DISTINCT USER_ID)每日用戶數(shù)
,count(*)/count(DISTINCT user_id)人均行為次數(shù)
,concat(ROUND(SUM(CASE WHEN behavior_type='PV' THEN 1 ELSE 0 END)/count(1)*100,2),'%')瀏覽數(shù)占比
,concat(ROUND(SUM(CASE WHEN behavior_type='BUY' THEN 1 ELSE 0 END)/count(1)*100,2),'%')購(gòu)買數(shù)占比
FROM user1
-- where date != '2014-12-12'
GROUP BY date
ORDER BY date;
用戶活躍度與總體點(diǎn)擊數(shù)是正相關(guān)的，走勢(shì)平穩(wěn)，不過(guò)在雙十二電商大促這天各項(xiàng)指標(biāo)暴增，且當(dāng)天點(diǎn)擊數(shù)占比有所下降(用戶的點(diǎn)擊更有針對(duì)性，前期已經(jīng)選好商品，就等雙十二當(dāng)天直接購(gòu)買的客戶數(shù)量比較多？), 成交數(shù)占比大幅上升。
2）、周維度
2）、周維度
查詢數(shù)據(jù)發(fā)現(xiàn)2014-11-18到2014-11-23,2014-12-15到2014-12-18均不滿一周，因此，只取完整的三周進(jìn)行分析：
SELECT
DATE_FORMAT(date,'%W')星期
,count(1)點(diǎn)擊次數(shù)
,SUM(CASE WHEN behavior_type='PV' THEN 1 ELSE 0 END)瀏覽次數(shù)
,SUM(CASE WHEN behavior_type='fav' THEN 1 ELSE 0 END)收藏次數(shù)
,SUM(CASE WHEN behavior_type='cart' THEN 1 ELSE 0 END)加購(gòu)次數(shù)
,SUM(CASE WHEN behavior_type='buy' THEN 1 ELSE 0 END)購(gòu)買次數(shù)
,COUNT(DISTINCT USER_ID)每日用戶數(shù)
,count(*)/count(DISTINCT user_id)人均行為次數(shù)
,concat(ROUND(SUM(CASE WHEN behavior_type='PV' THEN 1 ELSE 0 END)/count(1)*100,2),'%')瀏覽數(shù)占比
,concat(ROUND(SUM(CASE WHEN behavior_type='BUY' THEN 1 ELSE 0 END)/count(1)*100,2),'%')購(gòu)買數(shù)占比
FROM user1
-- where date != '2014-12-12'
WHERE (date BETWEEN '2014-11-24' AND '2014-12-14')
-- and date !='2014-12-12'
GROUP BY DATE_FORMAT(date,'%W')
ORDER BY DATE_FORMAT(date,'%W');
一周中的大部分時(shí)間用戶活躍度都比較平穩(wěn)，周五比較特殊，出現(xiàn)了增長(zhǎng)（ 查看數(shù)據(jù)發(fā)現(xiàn)雙十二正好是周五，屬于特殊活動(dòng)日，如果進(jìn)行詳細(xì)分析時(shí)應(yīng)該將雙十二的日期排除分析有更有意義）。
3）、小時(shí)維度
3）、小時(shí)維度
SELECT
hour
,count(1)點(diǎn)擊次數(shù)
,SUM(CASE WHEN behavior_type='PV' THEN 1 ELSE 0 END)瀏覽次數(shù)
,SUM(CASE WHEN behavior_type='fav' THEN 1 ELSE 0 END)收藏次數(shù)
,SUM(CASE WHEN behavior_type='cart' THEN 1 ELSE 0 END)加購(gòu)次數(shù)
,SUM(CASE WHEN behavior_type='buy' THEN 1 ELSE 0 END)購(gòu)買次數(shù)
,COUNT(DISTINCT USER_ID)每日用戶數(shù)
,count(*)/count(DISTINCT user_id)人均行為次數(shù)
,concat(ROUND(SUM(CASE WHEN behavior_type='PV' THEN 1 ELSE 0 END)/count(1)*100,2),'%')瀏覽數(shù)占比
,concat(ROUND(SUM(CASE WHEN behavior_type='BUY' THEN 1 ELSE 0 END)/count(1)*100,2),'%')購(gòu)買數(shù)占比
FROM user1
-- where date != '2014-12-12'
GROUP BY hour
ORDER BY hour;
晚間用戶較為活躍，但用戶行為傾向于瀏覽；白天尤其是中午左右的時(shí)段，購(gòu)買行為的比率相對(duì)一天中最高，此時(shí)購(gòu)買的目的性最強(qiáng)（瀏覽數(shù)占比與購(gòu)買數(shù)占比進(jìn)行關(guān)聯(lián)分析得出結(jié)論）。
(2)用戶商品偏好特征：
(2)用戶商品偏好特征：
SELECT
item_category
,COUNT(*)點(diǎn)擊次數(shù)
,SUM(CASE WHEN behavior_type='PV' THEN 1 ELSE 0 END)瀏覽次數(shù)
,SUM(CASE WHEN behavior_type='fav' THEN 1 ELSE 0 END)收藏次數(shù)
,SUM(CASE WHEN behavior_type='cart' THEN 1 ELSE 0 END)加購(gòu)次數(shù)
,SUM(CASE WHEN behavior_type='buy' THEN 1 ELSE 0 END)購(gòu)買次數(shù)
,concat(ROUND(SUM(CASE WHEN behavior_type='PV' THEN 1 ELSE 0 END)/count(behavior_type)*100,2),'%')瀏覽數(shù)占比
,concat(ROUND(SUM(CASE WHEN behavior_type='BUY' THEN 1 ELSE 0 END)/count(1)*100,2),'%')購(gòu)買數(shù)占比
FROM user1
-- where date != '2014-12-12'
GROUP BY item_category
ORDER BY 瀏覽次數(shù) DESC
按照商品品類區(qū)分（矩陣分析），根據(jù)點(diǎn)擊次數(shù)和購(gòu)買次數(shù)兩個(gè)維度將所有商品劃分到四個(gè)象限：
點(diǎn)擊數(shù)高，購(gòu)買數(shù)高。說(shuō)明此類產(chǎn)品剛需比較強(qiáng)，品牌多且種類豐富，用戶在較高的需求下有很多的選擇；
點(diǎn)擊數(shù)低購(gòu)買數(shù)高。用戶的購(gòu)買決策十分果斷，且對(duì)于該類產(chǎn)品的需求量也是很大的，說(shuō)明該類產(chǎn)品選擇性比較小，可能形成幾個(gè)品牌壟斷的情況，或者產(chǎn)品的差異性較小，用戶不愿花費(fèi)過(guò)多的精力去挑選。
點(diǎn)擊數(shù)低購(gòu)買數(shù)低，絕大多數(shù)產(chǎn)品都集中在這個(gè)象限，這種產(chǎn)品存在很多的替代品，用戶很難集中在某個(gè)子類進(jìn)行大量購(gòu)買，而是跳躍式選購(gòu)。
點(diǎn)擊數(shù)高購(gòu)買數(shù)低，這類產(chǎn)品的需求彈性較大，用戶購(gòu)買存在隨機(jī)性。
(3)、用戶購(gòu)買路徑特征：
用戶購(gòu)買商品分為以下幾類過(guò)程：
直接購(gòu)買
瀏覽后購(gòu)買
加購(gòu)物車購(gòu)買
瀏覽加購(gòu)物車購(gòu)買
收藏購(gòu)買
瀏覽收藏購(gòu)買
(3)、用戶購(gòu)買路徑特征：
1)創(chuàng)建用戶行為路徑表視圖(對(duì)用戶、商品、日期進(jìn)行分組，表結(jié)構(gòu)：user_id,pv,fav,cart,buy)
CREATE VIEW 用戶行為 as
SELECT user_id
,SUM(CASE WHEN behavior_type='PV' THEN 1 ELSE 0 END)瀏覽
,SUM(CASE WHEN behavior_type='fav' THEN 1 ELSE 0 END)收藏
,SUM(CASE WHEN behavior_type='cart' THEN 1 ELSE 0 END)加購(gòu)物車
,SUM(CASE WHEN behavior_type='buy' THEN 1 ELSE 0 END)購(gòu)買
FROM user1
GROUP BY user_id,item_id,date;
2)將用戶行為標(biāo)準(zhǔn)化(存在該類行為則標(biāo)記為1，否則為0)
CREATE VIEW 用戶行為標(biāo)準(zhǔn)化表 AS
SELECT user_id
,(CASE WHEN 瀏覽>0 THEN 1 ELSE 0 END)瀏覽
,(CASE WHEN 收藏>0 THEN 1 ELSE 0 END)收藏
,(CASE WHEN 加購(gòu)物車>0 THEN 1 ELSE 0 END)加購(gòu)物車
,(CASE WHEN 購(gòu)買>0 THEN 1 ELSE 0 END)購(gòu)買
FROM 用戶行為
3)建立標(biāo)準(zhǔn)化指標(biāo)表(user_id,購(gòu)買路徑類型)
--選取user_i作為分組依據(jù)，不會(huì)出現(xiàn)user_id重復(fù)的情況，但是會(huì)導(dǎo)致存在多種路徑的用戶數(shù)據(jù)的缺失（需要跟業(yè)務(wù)統(tǒng)一好計(jì)算口徑）
CREATE VIEW 標(biāo)準(zhǔn)化指標(biāo)表1 AS
SELECT user_id
,concat(瀏覽,收藏,加購(gòu)物車,購(gòu)買) as 購(gòu)買路徑類型
FROM 用戶行為標(biāo)準(zhǔn)化表 as a
WHERE a.購(gòu)買>0
GROUP BY user_id;
4)統(tǒng)計(jì)各指標(biāo)用戶數(shù)
SELECT 購(gòu)買路徑類型
,COUNT(DISTINCT user_id)用戶數(shù)
FROM 標(biāo)準(zhǔn)化指標(biāo)表
GROUP BY 購(gòu)買路徑類型;
3.2)
選取user_id和路徑作為分組依據(jù)，因此會(huì)出現(xiàn)user_id重復(fù)的情況（需要統(tǒng)一計(jì)算口徑，跟前面的只把user_id作為分組的結(jié)果進(jìn)行對(duì)比）
CREATE VIEW 標(biāo)準(zhǔn)化指標(biāo)表 AS
SELECT user_id
,concat(瀏覽,收藏,加購(gòu)物車,購(gòu)買) as 購(gòu)買路徑類型
FROM 用戶行為標(biāo)準(zhǔn)化表 as a
WHERE a.購(gòu)買>0
GROUP BY user_id,concat(瀏覽,收藏,加購(gòu)物車,購(gòu)買)
4.2)統(tǒng)計(jì)各指標(biāo)用戶數(shù)
SELECT 購(gòu)買路徑類型
,COUNT(DISTINCT user_id)用戶數(shù)
FROM 標(biāo)準(zhǔn)化指標(biāo)表1
GROUP BY 購(gòu)買路徑類型
結(jié)果顯示，直接夠買的用戶遠(yuǎn)遠(yuǎn)多于瀏覽后加購(gòu)或者收藏再購(gòu)買的用戶，說(shuō)明大部分購(gòu)買者都是喜歡直接購(gòu)買商品的，這個(gè)跟周圍朋友的習(xí)慣也是符合的，基本上都是在瀏覽的階段都不會(huì)馬上購(gòu)買，而是等到過(guò)段時(shí)間想買的時(shí)候就直接下單，幾個(gè)步驟之間相當(dāng)于是分開(kāi)的。
(4)、用戶復(fù)購(gòu)率特征：
(4)、用戶復(fù)購(gòu)率特征：
客戶購(gòu)買次數(shù)
SELECT
DISTINCT user_id
,COUNT(user_id)'購(gòu)買次數(shù)'
FROM user1
WHERE behavior_type='BUY'
GROUP BY user_id
ORDER BY COUNT(user_id) DESC
不同購(gòu)買次數(shù)的客戶分布
SELECT
a.購(gòu)買次數(shù)
,count(DISTINCT user_id)用戶數(shù)
FROM
(SELECT
DISTINCT user_id
,COUNT(user_id)購(gòu)買次數(shù)
FROM user1
WHERE behavior_type='BUY'
GROUP BY user_id
ORDER BY COUNT(user_id) DESC
) as a
GROUP BY a.購(gòu)買次數(shù)
ORDER BY a.購(gòu)買次數(shù)
用戶的購(gòu)買次數(shù)大部分集中在5次以內(nèi)，購(gòu)買頻率都還挺高的
