數(shù)據(jù)來源：https://tianchi.aliyun.com/dataset/dataDetail?dataId=46（阿里云天池）

一、分析目的與主要指標(biāo)

我們的目的是查看雙十一之后1個(gè)月的用戶購物情況，主要有以下指標(biāo)

1. 日常訪問量

2. 購物點(diǎn)擊總體情況：用戶總訪問量PV、 收藏次數(shù)、購物車加入數(shù)、購買次數(shù)；

3. 購物行為轉(zhuǎn)化率與行為轉(zhuǎn)化漏斗；

4. 用戶總數(shù)、下單購買人數(shù)與用戶轉(zhuǎn)化率；

5. 回頭客與復(fù)購情況；

6. 查看點(diǎn)擊與購買次數(shù)最多/最少的客戶情況

7. 查看用戶購物活躍 時(shí)間

8. 查看用戶對(duì)商品的喜好

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

表名：tianchi_user, 合計(jì)1048675行數(shù)據(jù)，共有6列/字段：

user_id：用戶編碼

item_id：商品編碼

behavior_type：用戶行為分類（1點(diǎn)擊，2收藏，3加入購物車，4下單購買四種）

user_geohash：地理位置（無效NULL值過多，無法有效分析，隱藏或刪除此列）

item_category：商品的類別代號(hào)

time：用戶行為發(fā)生的日期時(shí)間

三、數(shù)據(jù)整理

去重后，將該CSV文件導(dǎo)入Navicat

1.查看完整性，處理缺失值

除了user_geohash已經(jīng)被隱藏/刪除/或不被導(dǎo)入即可，導(dǎo)入檢查其他5列，確認(rèn)數(shù)據(jù)完整，無需填補(bǔ)。

2. 分列時(shí)間

表中time 字段日期和小時(shí)合在一起，不便分析，把該字段拆分為 2 個(gè)字段，一個(gè)日期+一個(gè)小時(shí)列。

-- 重命名 time 字段名稱為 date

ALTER TABLE ?tianchi_user CHANGE time date VARCHAR(255)?

-- 添加 time 列

ALTER TABLE tianchi_user ADD time VARCHAR(20);

-- 復(fù)制 date 列到 time 列

UPDATE ?tianchi_user SET time=date;

-- 在 date 列截?。?月-日）信息

UPDATE ?tianchi_user? SET date=REPLACE(date,date,SUBSTRING_INDEX(date,' ',1));

-- 在 time 列截取小時(shí)信息

UPDATE ?tianchi_user? SET time=REPLACE(time,time,SUBSTRING_INDEX(time,' ',-1));

拆分后表格如下：

3.把用戶行為behavior_type（1點(diǎn)擊，2收藏，3加入購物車，4下單購買四種）改為便于識(shí)別的代號(hào)

UPDATE tianchi_user

SET behavior_type=REPLACE(behavior_type,1,'PV');

UPDATE?tianchi_user

SET behavior_type=REPLACE(behavior_type,2,'Like');

UPDATE?tianchi_user

SET behavior_type=REPLACE(behavior_type,3,'Add');

UPDATE?tianchi_user

SET behavior_type=REPLACE(behavior_type,4,'Buy');

修改后如下：

四、數(shù)據(jù)查詢分析

1.日常點(diǎn)擊量

從圖中可以看出，雙十一之后那一個(gè)月每天的訪問量都是比較平穩(wěn)的，周末沒有明顯差別。一般維持在25000-30000之間，而雙12前一天開始增加，當(dāng)天則是翻倍的情況

2.總體情況：點(diǎn)擊量PV總訪問量、 購物車數(shù)量、收藏次數(shù)、購買次數(shù)

初步觀察：點(diǎn)擊量很大而加入購物的人數(shù)卻才到點(diǎn)擊量的3%，這里有待觀察，可以繼續(xù)看具體的行為轉(zhuǎn)化率。

SELECT behavior_type, COUNT(*)

FROM tianchi_user

GROUP BY behavior_type;

3.用戶行為轉(zhuǎn)化率情況：漏斗模型（tableau）

從下圖中可以看出，PV占了總行為數(shù)的94%，而添加購物車只占3%，而最后實(shí)際購買的在1%，所以從點(diǎn)擊到添加購物車這一步，轉(zhuǎn)化最低 ，流失很多。

原因猜想：

1）推薦的商品不到位用戶沒興趣，推薦算法有待改進(jìn)，

2）頁面不明確，分類不清，用戶難以到達(dá)自己想要的商品子類別，

3）商品搜索關(guān)鍵字關(guān)聯(lián)度不足，用戶難以搜到自己想要的商品。

4.凈點(diǎn)擊用戶量UV，凈購買用戶數(shù)量，客戶轉(zhuǎn)化率

由下圖，總客戶有8500，最后購買的有4300，總體轉(zhuǎn)化率接近51%，沒有行業(yè)對(duì)比，暫時(shí)不清楚情況是好是差。

SELECT COUNT(DISTINCT user_id) AS '用戶總數(shù)' FROM tianchi_user;

#查詢下單購買用戶量

SELECT COUNT(DISTINCT user_id) AS '下單購買用戶量' FROM tianchi_user

WHERE behavior_type = 'Buy';

創(chuàng)建視圖

CREATE VIEW behavior_temp?AS

SELECT user_id, count(behavior_type) as '行為次數(shù)小計(jì)',

SUM(CASE WHEN behavior_type='PV' THEN 1 ELSE 0 END) AS '點(diǎn)擊次數(shù)',

SUM(CASE WHEN behavior_type='Like' THEN 1 ELSE 0 END) AS '收藏次數(shù)',

SUM(CASE WHEN behavior_type='Add' THEN 1 ELSE 0 END) AS '購物車數(shù)',

SUM(CASE WHEN behavior_type='Buy' THEN 1 ELSE 0 END) AS '購買次數(shù)'

FROM tianchi_user

GROUP BY user_id

ORDER BY COUNT(behavior_type) DESC;

-- DROP VIEW behavior_temp

#查看用戶購物情況視圖

SELECT * FROM behavior_temp

5.查詢購買用戶數(shù)量、重復(fù)購買用戶數(shù)量以及復(fù)購率

回頭客近50%，也還需要對(duì)比，才知道是好是差。

#查詢用戶復(fù)購率

SELECT SUM(CASE WHEN 購買次數(shù)>=2 THEN 1 ELSE 0 END) AS '復(fù)購用戶',

SUM(CASE WHEN 購買次數(shù)>0 THEN 1 ELSE 0 END) AS '總購用戶',

CONCAT(ROUND(SUM(CASE WHEN 購買次數(shù)>=2 THEN 1 ELSE 0 END)/SUM(CASE WHEN 購買次數(shù)>0 THEN 1 ELSE 0 END)*100), '%') AS '復(fù)購率'

FR?OM behavior_temp

6.#查看購買次數(shù)多的用戶情況，及其購買率

1）點(diǎn)擊量不大、購買次數(shù)多的用戶，購物目的相對(duì)比較明確，一般是剛需，這種客戶點(diǎn)擊次數(shù)/收藏次數(shù)都較少而購買率自然就上去了，這種大概適合繼續(xù)維護(hù)，不易開發(fā)

2）點(diǎn)擊量大，購買次數(shù)多的用戶，一般是理性偏熱衷購物的用戶，這大概是我們的重點(diǎn)維護(hù)，適當(dāng)開發(fā)的對(duì)象

SELECT user_id, 點(diǎn)擊次數(shù), 收藏次數(shù), 購物車數(shù), 購買次數(shù),

CONCAT(ROUND(購買次數(shù)/點(diǎn)擊次數(shù)*100, 2),'%') AS '購買率'

FROM behavior_temp

GROUP BY user_id

ORDER BY 購買次數(shù) DESC;

3）購買次數(shù)少點(diǎn)擊少的用戶，我們琢磨不透建議繼續(xù)觀察或放棄；

4）購買次數(shù)少，點(diǎn)擊次數(shù)多的用戶可能是選擇困難癥或是十分精明克制的客戶，這種是否可以通過優(yōu)惠活動(dòng)或精準(zhǔn)營銷推送吸引其關(guān)注，重點(diǎn)開發(fā)。

SELECT user_id, 點(diǎn)擊次數(shù), 收藏次數(shù), 購物車數(shù), 購買次數(shù),

CONCAT(ROUND(購買次數(shù)/點(diǎn)擊次數(shù)*100, 2),'%') AS '購買率'

FROM behavior_temp

GROUP BY user_id

ORDER BY 購買次數(shù) ASC;

7.#查看每天用戶活躍時(shí)段

晚上8點(diǎn)到10點(diǎn)是用戶購物活躍的高峰，所以在流量容量的分配，以及晚上客服的排班上可以有所擴(kuò)張。在這個(gè)時(shí)段有效運(yùn)用活動(dòng)推廣。

SELECT time, count(behavior_type) as '行為次數(shù)小計(jì)',

SUM(CASE WHEN behavior_type='PV' THEN 1 ELSE 0 END) AS '點(diǎn)擊次數(shù)',

SUM(CASE WHEN behavior_type='Like' THEN 1 ELSE 0 END) AS '收藏次數(shù)',

SUM(CASE WHEN behavior_type='Add' THEN 1 ELSE 0 END) AS '購物車數(shù)',

SUM(CASE WHEN behavior_type='Buy' THEN 1 ELSE 0 END) AS '購買次數(shù)'

FROM tianchi_user

GROUP BY time

ORDER BY COUNT(behavior_type) DESC;

7.#查看用戶對(duì)商品的喜好，以點(diǎn)擊量為基準(zhǔn)

#查看用戶點(diǎn)擊最多的商品類別

SELECT item_category, COUNT(item_category) AS '商品類別點(diǎn)擊量'

FROM tianchi_user

WHERE behavior_type = 'PV'

GROUP BY item_category

ORDER BY 商品類別點(diǎn)擊量 DESC;

點(diǎn)擊最多的類別為1863，前6類商品的點(diǎn)擊量均已超過2萬，后續(xù)直線下降。

#查看用戶點(diǎn)擊最多商品名稱

SELECT item_category, item_id, COUNT(item_id) AS '商品點(diǎn)擊量'

FROM tianchi_user

WHERE behavior_type = 'PV'

GROUP BY item_id

ORDER BY 商品點(diǎn)擊量 DESC;

點(diǎn)擊量最多的商品為112921337，可以看到點(diǎn)擊量最多的商品并不在點(diǎn)擊最多的商品類別里面，這很正常，可以猜測用戶在尋找這幾個(gè)商品時(shí)都很明確。

五、總結(jié)問題與改善建議

1、整體上用戶行為轉(zhuǎn)化率近1%，主要在用戶點(diǎn)擊后加入購物車或收藏的這一環(huán)節(jié)轉(zhuǎn)換率過低，可通過精準(zhǔn)營銷或作活動(dòng)引流，增強(qiáng)商品關(guān)鍵字搜索關(guān)聯(lián)性等等手段提高用戶轉(zhuǎn)化。

2、購買次數(shù)多的用戶，購買率較高，同時(shí)點(diǎn)擊量并不高，說明他們購物目標(biāo)明確，很有可能是剛需，這些用戶需要保持就好；購買次數(shù)多，點(diǎn)擊量也多的用戶可能是理性消費(fèi)用戶需要重點(diǎn)維護(hù)，適當(dāng)開發(fā)。

購買次數(shù)少點(diǎn)擊少的用戶，我們琢磨不透建議繼續(xù)觀察或放棄；購買次數(shù)少，點(diǎn)擊次數(shù)多的用戶可能是選擇困難癥或是十分精明克制的客戶，這種是否可以通過活動(dòng)或精準(zhǔn)營銷推送吸引其關(guān)注，重點(diǎn)開發(fā)。

3、用戶日?；钴S度通常都比較平穩(wěn)，維持在25000-30000之間。在一天中，用戶在晚上8--11點(diǎn)最為活躍，這個(gè)時(shí)段網(wǎng)站流量，促銷活動(dòng)，客服支持等等可以向此傾斜。

4、用戶點(diǎn)擊最多的6類商品均已突破2萬，初步預(yù)測那六類商品用戶需求較大，而點(diǎn)擊最多的具體商品112921337，用戶點(diǎn)擊前4的商品對(duì)應(yīng)的類別不在點(diǎn)擊最多的商品類別里面，說明這個(gè)類別的商品，用戶尋找的目的比較明確。