一、数据介绍

• 数据来源：https://www.kaggle.com/mkechinov/ecommerce-events-history-in-electronics-store
• 本项目数据集来源于大型电子产品在线商店从2019 年 10 月至 2020 年 2 月的用户行为数据。
• 数据集格式：共845041行，每一行对应一条用户行为数据，共包含9个字段，以下为字段详细说明：

二、分析目的及框架

• 分析目的：本文基于电商用户行为数据,研究用户活跃规律、了解各业务环节指标转化情况、按用户价值进行分类以便制定针对性策略。
• AARRR模型
• RFM用户价值模型
• 漏斗模型
• 假设检验分析

三、数据预处理

1. 重复值处理

1.1 重复值查询

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 删除重复值

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字段数据严重缺失，此处不予采用，应找相关部门确认缺失原因。将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 经查询可知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 把日期和时间单独取出以满足不同时间维度的分析

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 查询日期范围以及价格是否有负值

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

4.2 此数据源日期范围从2020-09-24到2021-02-28，因9月数据量太少不足以得出分析结果，所以在此处删除9月数据。商品价格无异常。

delete from sales where month(datetime)=9;

四、数据分析

1. 用户获取和激活

1.1 整体PV(页面浏览量)、UV(独立访客数)、PV/UV(人均页面浏览量)

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

• 平均每个用户访问页面不到2次，页面重复访问量低，用户粘性低。

1.2 每日活跃用户数、每日新增用户、每日活跃老用户（此处定义新增用户为自2020-10-01起第一次有动态的用户）

select e.日期,e.日活跃用户数,g.每日新增用户,(e.日活跃用户数-g.每日新增用户)日活跃老用户数
from 
(select dates 日期,count(distinct user_id) 日活跃用户数
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

• 由图可知，日活的主要来源为新增用户，因此衡量新用户质量优劣至关重要，在下文“2.1用户留存”章节中可以看到相关分析。
• 可以看出每日新增用户与DAU的走势趋同，整体都在缓慢减少，且都在12月6日(周日)和12月31日(周四)存在两个异常点，由于数据不足，对这两处数据异常仅做分析思路描述，影响因素可分为内外两部分，我们可从不同角度提出假设分析来检验可能的原因：

在分析之前首先要确认取数和数值计算是否无误，另外进行环比/同比分析确认是否为周期性变化。

内部：可分为运营、产品、技术、市场四个部分，这也正好对应着公司的组织架构。

运营(用户)：最近是否上了新的活动；按渠道、用户群拆解用户 。
产品：查看最近是否有做过违反用户习惯的功能或者界面改动，导致用户大量流失。
技术：查看是否由于技术原因导致网站崩溃、用户无法登陆等情况。
市场：对比同期竞品网站是否有做过大型促销活动。

外部：PEST分析方法

政治：国家政策变化导致网站停运。
社会：临近重大节日，用户放假减少登录网站。

1.3 一天/周内用户活跃时间段

SELECT HOUR(times) 时间, count(distinct user_id) 小时活跃用户数
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 星期, 
工作日活跃用户数
from
(select weekday(datetime)星期, count(distinct user_id) 工作日活跃用户数
from sales
group by weekday(datetime)
order by weekday(datetime))as o;

• 一天中用户活跃时间段在7:00-19:00之间，这也与人们的作息时间相符。
• 工作日用户最活跃，到周末活跃度突降，这说明用户在休息日更喜欢购物以外的其他休闲方式。另外工作日中周一为流量顶点，猜测可能是用户在结束假期后未能进入工作状态，导致网站访问量最大。

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";

*跳出率过高表示点击进入的网页无法吸引用户的兴趣才会在第一次就退出。应注意优化商品照片和描述，将活动宣传链接放到显眼位置以吸引用户进一步点击。

2. 用户留存

2.1 月留存率

• 因为电子产品购买频率较低，所以本次分析采用周留存而不是日留存来衡量。

SELECT first_week 周_初始日期, week_gap 周_后续行为日期,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;

• 此电商网站留存率极低，第二周留存率基本都在2%以下，就算电子产品购买频率较低，在初次购买之后的四个月内都没有留存率的提升，这样的表现也是很糟糕的。可以通过发放优惠券、举办签到活动等方式来提升用户粘度。

3.增加收入

3.1 复购率

#筛选有购买行为用户,并将同一用户在同一天发生的交易合并为一次
create view repeat_purchase as
select dates,user_id,count(*) ct
from sales 
where event_type="purchase"
group by dates,user_id;

#复购率计算(有两次及以上购买记录的用户数/有购买记录的所有用户数)
select concat(round((100*count(user_id)/(select count(distinct user_id)from repeat_purchase)),2),"%") 复购率
FROM
(select user_id
from repeat_purchase
group by user_id
having count(dates)>=2)m

3.2 产品分析

#查询最畅销的10个产品
SELECT product_id 畅销商品top10,count(user_id) 购买次数
from sales
where event_type="purchase"
group by product_id
order by count(user_id) desc limit 10;
#查询浏览最多的10个产品
SELECT product_id 浏览商品top10,count(user_id) 浏览次数
from sales
where event_type="view"
group by product_id
order by count(user_id) desc limit 10;
#查询销售额最多的10个产品
SELECT product_id 销售额top10产品,sum(price)销售额
from sales
where event_type="purchase"
group by product_id
order by sum(price) desc limit 10;

• 将销售量和浏览量top10的产品做对比，针对3642540、4102739、287525和246841这四个成交量高但浏览量低的产品，需要加大其推广力度。
• 将销售金额和浏览量top10的产品做对比，发现有四个产品销售金额高但浏览量相对较低：4079420、4078916、4102739、4079565。进一步查询后得知这四种产品价格都在两百元以上，属于贵价商品。可以有针对性的推送这些产品给重要价值客户，以进一步提升贵价商品的销售额。

4. 用户行为漏斗图

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

点击——加购转化率低原因分析(假设检验分析)：
此处可以按照从用户进入网站到加购的业务流程提出假设

假设一：网站商品数量和种类太少，对用户缺乏吸引力

select count(distinct product_id)商品数量,count(distinct category_id)种类数量 from sales;

假设二：用户搜不到感兴趣的的商品

select concat(round(count(distinct product_id)*100/(select count(distinct product_id) from sales),2),"%") 加购商品占总商品数比重 from sales where event_type="cart";;

假设三：网站推荐不符合用户的口味
通过对比浏览量最高和购买量最高的10个商品，来观察网站的推荐是否精准，具体可见“3.2产品分析”这一章节。

• 此电商网站商品共分716大类，其中包含商品52608个，有足够的商品供用户选择，所以假设一不成立。
• 在52608个商品中，只有18.12%的商品曾被用户加入到购物车中，可以发现商品对用户的吸引力较低，所以假设二成立。
• 此电商网站浏览和销售top10的商品中只有6项是重合的，也就是说网站推送的精确度仅为60%，所以假设三成立。

5.RFM用户价值分析

create view RFM as
select r.user_id,r.Recency,f.Frequency,m.Monetary from
# R 
# 此处计算截至2021年2月28日的消费间隔时间
(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

# 按各项中位数对最近1次消费时间间隔(R),消费频率(F),消费金额(M)进行分类，大于中位数的记为”高“，低于中位数的记为”低“
select 客户类别,count(user_id) from
(select *,
(case when Recency="高" and Frequency="高" and Monetary="高" then "重要价值用户"
     when Recency="高" and Frequency="低" and Monetary="高" then "重要发展用户"
         when Recency="低" and Frequency="高" and Monetary="高" then "重要保持用户"
         when Recency="低" and Frequency="低" and Monetary="高" then "重要挽留用户"
         when Recency="高" and Frequency="高" and Monetary="低" then "一般价值用户"
         when Recency="高" and Frequency="低" and Monetary="低" then "一般发展用户"
         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 客户类别

五、结论：

• 在用户构成方面，平台的主要用户为新用户，但同时新用户留存率极低，建议通过开展签到有奖活动、增加折扣消息通知来提升用户粘度。

• 复购率为7.67%，针对长时间不消费的老用户，可采取VIP积分抵现、发送优惠券等方式刺激重复购买。同时可以通过问卷调查探寻老用户流失原因。

• 7:00-19:00是一天内用户最活跃的时间，且相比周末用户更喜欢在工作日访问购物网站，运营需可在黄金时间段进行新品发布和折扣消息推送。

• 对于销售量极高但是浏览量低的商品3642540、4102739、287525和246841，需要加大推广度，提高其曝光率；对于销售额高但是浏览量低的贵价商品4079420、4078916、4102739和4079565，要将其有针对性的推送给高消费金额用户。

• 此电商平台浏览-加购转化表现不佳，为6.86%，同时跳出率高达65.24%，这是由用户对商品缺乏兴趣以及推送与用户需求不匹配造成的，建议网站应通过丰富商品详情页面来吸引用户，优化推荐算法、增加畅销商品曝光率来提升销售量。

• 通过RFM模型分析可知，此电商平台用户群体最大的为一般挽留用户(消费间隔时间长、消费频率低、消费金额小)，运营需要提防客户流失，并根据不同客户价值分类施行针对性营销策略。