一溯壶、數(shù)據(jù)介紹

• 數(shù)據(jù)來(lái)源：https://www.kaggle.com/mkechinov/ecommerce-events-history-in-electronics-store
• 本項(xiàng)目數(shù)據(jù)集來(lái)源于大型電子產(chǎn)品在線商店從2019 年 10 月至 2020 年 2 月的用戶行為數(shù)據(jù)匪傍。
• 數(shù)據(jù)集格式：共845041行，每一行對(duì)應(yīng)一條用戶行為數(shù)據(jù)，共包含9個(gè)字段干旁，以下為字段詳細(xì)說(shuō)明：

二泛烙、分析目的及框架

• 分析目的：本文基于電商用戶行為數(shù)據(jù),研究用戶活躍規(guī)律、了解各業(yè)務(wù)環(huán)節(jié)指標(biāo)轉(zhuǎn)化情況翘紊、按用戶價(jià)值進(jìn)行分類以便制定針對(duì)性策略蔽氨。
• AARRR模型
• RFM用戶價(jià)值模型
• 漏斗模型
• 假設(shè)檢驗(yàn)分析

三、數(shù)據(jù)預(yù)處理

1. 重復(fù)值處理

1.1 重復(fù)值查詢

SELECT *,count(*) num
FROM events
GROUP BY event_time,event_type,product_id,category_id,
category_code,brand,price,user_id,user_session
HAVING COUNT(*)>1;

1.2 刪除重復(fù)值

create table sales
select distinct * from events;

2. 缺失值處理

2.1 缺失值查詢

select 
sum(case when event_time is null then 1 else 0 end)Nevent_time,
sum(case when event_type is null then 1 else 0 end)Nevent_type,
sum(case when product_id is null then 1 else 0 end)Nproduct_id,
sum(case when category_id is null then 1 else 0 end)Ncategory_id,
sum(case when category_code is null then 1 else 0 end)Ncategory_code,
sum(case when brand is null then 1 else 0 end)Nbrand,
sum(case when price is null then 1 else 0 end)Nprice,
sum(case when user_id is null then 1 else 0 end)Nuser_id,
sum(case when user_session is null then 1 else 0 end)Nuser_session
from sales;

2.2 brand和category_code字段數(shù)據(jù)嚴(yán)重缺失帆疟，此處不予采用鹉究，應(yīng)找相關(guān)部門確認(rèn)缺失原因。將user_session中的缺失值替換為 no exists踪宠。

update sales set user_session="no exists" where user_session is null;
alter table sales drop column category_code;
alter table sales drop column brand;

3. 一致化處理

3.1 經(jīng)查詢可知datetime字段為字符類型自赔，此處將其改為日期類型

desc sales;
alter table sales add column datetime datetime after event_time;
update sales set datetime=STR_TO_DATE(left(event_time,length(event_time)-4),"%Y-%m-%d %H:%i:%S");
alter table sales drop column event_time;

3.2 把日期和時(shí)間單獨(dú)取出以滿足不同時(shí)間維度的分析

alter table sales add column dates date after datetime;
update sales set dates=date(datetime);   
alter table sales add column times time after dates;
update sales set times=time(datetime);

4. 異常值處理

4.1 查詢?nèi)掌诜秶约皟r(jià)格是否有負(fù)值

select min(datetime),max(datetime),min(price),max(price) from sales;

4.2 此數(shù)據(jù)源日期范圍從2020-09-24到2021-02-28，因9月數(shù)據(jù)量太少不足以得出分析結(jié)果柳琢，所以在此處刪除9月數(shù)據(jù)绍妨。商品價(jià)格無(wú)異常。

delete from sales where month(datetime)=9;

四柬脸、數(shù)據(jù)分析

1. 用戶獲取和激活

1.1 整體PV(頁(yè)面瀏覽量)他去、UV(獨(dú)立訪客數(shù))、PV/UV(人均頁(yè)面瀏覽量)

select count(*) pv,count(distinct user_id) uv,(count(*)/count(distinct user_id)) pv_uv from sales where event_type="view";

• 平均每個(gè)用戶訪問(wèn)頁(yè)面不到2次倒堕，頁(yè)面重復(fù)訪問(wèn)量低灾测，用戶粘性低。

1.2 每日活躍用戶數(shù)涩馆、每日新增用戶行施、每日活躍老用戶（此處定義新增用戶為自2020-10-01起第一次有動(dòng)態(tài)的用戶）

select e.日期,e.日活躍用戶數(shù),g.每日新增用戶,(e.日活躍用戶數(shù)-g.每日新增用戶)日活躍老用戶數(shù)
from 
(select dates 日期,count(distinct user_id) 日活躍用戶數(shù)
from sales
group by dates) e
left join 
(select f.first_date 日期,count(user_id) 每日新增用戶
from
(select user_id,min(dates) first_date from sales group by user_id) f
group by first_date) g
on e.日期=g.日期;

DAU.png

• 由圖可知，日活的主要來(lái)源為新增用戶魂那，因此衡量新用戶質(zhì)量?jī)?yōu)劣至關(guān)重要蛾号，在下文“2.1用戶留存”章節(jié)中可以看到相關(guān)分析。
• 可以看出每日新增用戶與DAU的走勢(shì)趨同涯雅，整體都在緩慢減少鲜结，且都在12月6日(周日)和12月31日(周四)存在兩個(gè)異常點(diǎn)，由于數(shù)據(jù)不足，對(duì)這兩處數(shù)據(jù)異常僅做分析思路描述精刷，影響因素可分為內(nèi)外兩部分拗胜，我們可從不同角度提出假設(shè)分析來(lái)檢驗(yàn)可能的原因：

在分析之前首先要確認(rèn)取數(shù)和數(shù)值計(jì)算是否無(wú)誤，另外進(jìn)行環(huán)比/同比分析確認(rèn)是否為周期性變化怒允。

內(nèi)部：可分為運(yùn)營(yíng)埂软、產(chǎn)品、技術(shù)纫事、市場(chǎng)四個(gè)部分勘畔，這也正好對(duì)應(yīng)著公司的組織架構(gòu)。

運(yùn)營(yíng)(用戶)：最近是否上了新的活動(dòng)丽惶；按渠道炫七、用戶群拆解用戶 。
產(chǎn)品：查看最近是否有做過(guò)違反用戶習(xí)慣的功能或者界面改動(dòng)钾唬，導(dǎo)致用戶大量流失万哪。
技術(shù)：查看是否由于技術(shù)原因?qū)е戮W(wǎng)站崩潰、用戶無(wú)法登陸等情況抡秆。
市場(chǎng)：對(duì)比同期競(jìng)品網(wǎng)站是否有做過(guò)大型促銷活動(dòng)奕巍。

外部：PEST分析方法

政治：國(guó)家政策變化導(dǎo)致網(wǎng)站停運(yùn)。
社會(huì)：臨近重大節(jié)日琅轧，用戶放假減少登錄網(wǎng)站伍绳。

1.3 一天/周內(nèi)用戶活躍時(shí)間段

SELECT HOUR(times) 時(shí)間, count(distinct user_id) 小時(shí)活躍用戶數(shù)
from sales
group by HOUR(times) 
order by HOUR(times);

select  
(case 星期
when 0 then "周一" 
when 1 then "周二" 
when 2 then "周三" 
when 3 then "周四" 
when 4 then "周五" 
when 5 then "周六" 
else "周日" end)as 星期, 
工作日活躍用戶數(shù)
from
(select weekday(datetime)星期, count(distinct user_id) 工作日活躍用戶數(shù)
from sales
group by weekday(datetime)
order by weekday(datetime))as o;

• 一天中用戶活躍時(shí)間段在7:00-19:00之間，這也與人們的作息時(shí)間相符乍桂。
• 工作日用戶最活躍冲杀，到周末活躍度突降，這說(shuō)明用戶在休息日更喜歡購(gòu)物以外的其他休閑方式睹酌。另外工作日中周一為流量頂點(diǎn)权谁，猜測(cè)可能是用戶在結(jié)束假期后未能進(jìn)入工作狀態(tài)，導(dǎo)致網(wǎng)站訪問(wèn)量最大憋沿。

1.4 跳出率(只瀏覽了一次就離開的用戶占總用戶的比例)

select concat(round(count(*)/(select count(distinct user_id) from sales)*100,2),"%") 跳出率 from sales where user_id in
(select user_id
from sales
group by user_id
having count(event_type)=1 ) and event_type="view";

*跳出率過(guò)高表示點(diǎn)擊進(jìn)入的網(wǎng)頁(yè)無(wú)法吸引用戶的興趣才會(huì)在第一次就退出旺芽。應(yīng)注意優(yōu)化商品照片和描述，將活動(dòng)宣傳鏈接放到顯眼位置以吸引用戶進(jìn)一步點(diǎn)擊辐啄。

2. 用戶留存

2.1 月留存率

• 因?yàn)殡娮赢a(chǎn)品購(gòu)買頻率較低采章，所以本次分析采用周留存而不是日留存來(lái)衡量。

SELECT first_week 周_初始日期, week_gap 周_后續(xù)行為日期,count(*) 周留存
from
(SELECT distinct m.user_id, date_format(m.first_day,"%X-%V")first_week,concat(TIMESTAMPDIFF(week,m.first_day,s.dates))week_gap  
(select user_id,min(dates) as first_day
from sales
group by user_id)m
left join sales s 
on m.user_id=s.user_id)p
group by first_week,week_gap
order by first_week,week_gap;

• 此電商網(wǎng)站留存率極低壶辜，第二周留存率基本都在2%以下悯舟，就算電子產(chǎn)品購(gòu)買頻率較低，在初次購(gòu)買之后的四個(gè)月內(nèi)都沒(méi)有留存率的提升砸民，這樣的表現(xiàn)也是很糟糕的抵怎》芫龋可以通過(guò)發(fā)放優(yōu)惠券、舉辦簽到活動(dòng)等方式來(lái)提升用戶粘度反惕。

3.增加收入

3.1 復(fù)購(gòu)率

#篩選有購(gòu)買行為用戶,并將同一用戶在同一天發(fā)生的交易合并為一次
create view repeat_purchase as
select dates,user_id,count(*) ct
from sales 
where event_type="purchase"
group by dates,user_id;

#復(fù)購(gòu)率計(jì)算(有兩次及以上購(gòu)買記錄的用戶數(shù)/有購(gòu)買記錄的所有用戶數(shù))
select concat(round((100*count(user_id)/(select count(distinct user_id)from repeat_purchase)),2),"%") 復(fù)購(gòu)率
FROM
(select user_id
from repeat_purchase
group by user_id
having count(dates)>=2)m

3.2 產(chǎn)品分析

#查詢最暢銷的10個(gè)產(chǎn)品
SELECT product_id 暢銷商品top10,count(user_id) 購(gòu)買次數(shù)
from sales
where event_type="purchase"
group by product_id
order by count(user_id) desc limit 10;
#查詢?yōu)g覽最多的10個(gè)產(chǎn)品
SELECT product_id 瀏覽商品top10,count(user_id) 瀏覽次數(shù)
from sales
where event_type="view"
group by product_id
order by count(user_id) desc limit 10;
#查詢銷售額最多的10個(gè)產(chǎn)品
SELECT product_id 銷售額top10產(chǎn)品,sum(price)銷售額
from sales
where event_type="purchase"
group by product_id
order by sum(price) desc limit 10;

• 將銷售量和瀏覽量top10的產(chǎn)品做對(duì)比尝艘，針對(duì)3642540、4102739姿染、287525和246841這四個(gè)成交量高但瀏覽量低的產(chǎn)品背亥，需要加大其推廣力度。
• 將銷售金額和瀏覽量top10的產(chǎn)品做對(duì)比盔粹，發(fā)現(xiàn)有四個(gè)產(chǎn)品銷售金額高但瀏覽量相對(duì)較低：4079420隘梨、4078916、4102739舷嗡、4079565。進(jìn)一步查詢后得知這四種產(chǎn)品價(jià)格都在兩百元以上嵌莉，屬于貴價(jià)商品进萄。可以有針對(duì)性的推送這些產(chǎn)品給重要價(jià)值客戶锐峭，以進(jìn)一步提升貴價(jià)商品的銷售額中鼠。

4. 用戶行為漏斗圖

select event_type,count(*)
from sales
group by event_type;

點(diǎn)擊——加購(gòu)轉(zhuǎn)化率低原因分析(假設(shè)檢驗(yàn)分析)：
此處可以按照從用戶進(jìn)入網(wǎng)站到加購(gòu)的業(yè)務(wù)流程提出假設(shè)

假設(shè)一：網(wǎng)站商品數(shù)量和種類太少，對(duì)用戶缺乏吸引力

select count(distinct product_id)商品數(shù)量,count(distinct category_id)種類數(shù)量 from sales;

假設(shè)二：用戶搜不到感興趣的的商品

select concat(round(count(distinct product_id)*100/(select count(distinct product_id) from sales),2),"%") 加購(gòu)商品占總商品數(shù)比重 from sales where event_type="cart";;

假設(shè)三：網(wǎng)站推薦不符合用戶的口味
通過(guò)對(duì)比瀏覽量最高和購(gòu)買量最高的10個(gè)商品沿癞，來(lái)觀察網(wǎng)站的推薦是否精準(zhǔn)援雇，具體可見“3.2產(chǎn)品分析”這一章節(jié)。

• 此電商網(wǎng)站商品共分716大類椎扬，其中包含商品52608個(gè)惫搏，有足夠的商品供用戶選擇，所以假設(shè)一不成立蚕涤。
• 在52608個(gè)商品中筐赔，只有18.12%的商品曾被用戶加入到購(gòu)物車中，可以發(fā)現(xiàn)商品對(duì)用戶的吸引力較低揖铜，所以假設(shè)二成立茴丰。
• 此電商網(wǎng)站瀏覽和銷售top10的商品中只有6項(xiàng)是重合的，也就是說(shuō)網(wǎng)站推送的精確度僅為60%天吓，所以假設(shè)三成立贿肩。

5.RFM用戶價(jià)值分析

create view RFM as
select r.user_id,r.Recency,f.Frequency,m.Monetary from
# R 
# 此處計(jì)算截至2021年2月28日的消費(fèi)間隔時(shí)間
(SELECT user_id,datediff(date("2021-02-28"),max(dates)) Recency
from sales
where event_type= "purchase"
group by user_id)r
left join 
# F 
(SELECT user_id,count(dates) Frequency
from sales
where event_type= "purchase"
group by user_id)f
on r.user_id=f.user_id
left join
# M
(SELECT user_id,sum(price) Monetary
from sales 
where event_type= "purchase"
group by user_id)m
on r.user_id=m.user_id

# 按各項(xiàng)中位數(shù)對(duì)最近1次消費(fèi)時(shí)間間隔(R),消費(fèi)頻率(F),消費(fèi)金額(M)進(jìn)行分類，大于中位數(shù)的記為”高“龄寞，低于中位數(shù)的記為”低“
select 客戶類別,count(user_id) from
(select *,
(case when Recency="高" and Frequency="高" and Monetary="高" then "重要價(jià)值用戶"
     when Recency="高" and Frequency="低" and Monetary="高" then "重要發(fā)展用戶"
         when Recency="低" and Frequency="高" and Monetary="高" then "重要保持用戶"
         when Recency="低" and Frequency="低" and Monetary="高" then "重要挽留用戶"
         when Recency="高" and Frequency="高" and Monetary="低" then "一般價(jià)值用戶"
         when Recency="高" and Frequency="低" and Monetary="低" then "一般發(fā)展用戶"
         when Recency="低" and Frequency="高" and Monetary="低" then "一般保持用戶"
         when Recency="低" and Frequency="低" and Monetary="低" then "一般挽留用戶"
         else 0 end) 客戶類別
from
(select user_id,
(case when Recency<=69 then "高" else "低" end)Recency,
(case when Frequency<=1 then "低" else "高" end)Frequency,
(case when Monetary<=93.48 then "低" else "高" end)Monetary
from RFM)a)b 
group by 客戶類別

五汰规、結(jié)論：

• 在用戶構(gòu)成方面，平臺(tái)的主要用戶為新用戶萄焦，但同時(shí)新用戶留存率極低控轿，建議通過(guò)開展簽到有獎(jiǎng)活動(dòng)冤竹、增加折扣消息通知來(lái)提升用戶粘度。

• 復(fù)購(gòu)率為7.67%茬射，針對(duì)長(zhǎng)時(shí)間不消費(fèi)的老用戶鹦蠕，可采取VIP積分抵現(xiàn)、發(fā)送優(yōu)惠券等方式刺激重復(fù)購(gòu)買在抛。同時(shí)可以通過(guò)問(wèn)卷調(diào)查探尋老用戶流失原因钟病。

• 7:00-19:00是一天內(nèi)用戶最活躍的時(shí)間，且相比周末用戶更喜歡在工作日訪問(wèn)購(gòu)物網(wǎng)站刚梭，運(yùn)營(yíng)需可在黃金時(shí)間段進(jìn)行新品發(fā)布和折扣消息推送肠阱。

• 對(duì)于銷售量極高但是瀏覽量低的商品3642540、4102739朴读、287525和246841屹徘，需要加大推廣度，提高其曝光率衅金；對(duì)于銷售額高但是瀏覽量低的貴價(jià)商品4079420噪伊、4078916、4102739和4079565氮唯，要將其有針對(duì)性的推送給高消費(fèi)金額用戶鉴吹。

• 此電商平臺(tái)瀏覽-加購(gòu)轉(zhuǎn)化表現(xiàn)不佳，為6.86%惩琉，同時(shí)跳出率高達(dá)65.24%豆励，這是由用戶對(duì)商品缺乏興趣以及推送與用戶需求不匹配造成的，建議網(wǎng)站應(yīng)通過(guò)豐富商品詳情頁(yè)面來(lái)吸引用戶瞒渠，優(yōu)化推薦算法良蒸、增加暢銷商品曝光率來(lái)提升銷售量。

• 通過(guò)RFM模型分析可知在孝，此電商平臺(tái)用戶群體最大的為一般挽留用戶(消費(fèi)間隔時(shí)間長(zhǎng)诚啃、消費(fèi)頻率低、消費(fèi)金額小)私沮，運(yùn)營(yíng)需要提防客戶流失始赎，并根據(jù)不同客戶價(jià)值分類施行針對(duì)性營(yíng)銷策略。