电商用户行为分析（一）
一、项目背景介绍：
2014年是阿里巴巴集团移动电商业务快速发展的一年，例如2014双11大促中移动端成交占比达到42.6%，超过240亿元。相比PC时代，移动端网络的访问是随时随地的，具有更丰富的场景数据，比如用户的位置信息、用户访问的时间规律等。
通过数据分析，能够挖掘数据背后丰富的内涵，为移动用户在合适的时间、合适的地点精准推荐合适的内容。
本案例的目标是从该数据进行随机抽样，并用mysql进行分析，提高自己对电商指标体系的认识。
二、数据说明
1、字段说明
字段说明
三、分析思路
1、提出问题：
1）分析用户购物过程中的常见监控指标，了解运营现状，查看各环节的漏斗转化情况，并找到需要改进的节点；
2）研究用户在不同维度下的行为规律，了解用户行为特征，优化运营策略；
3）利用RFM模型对用户进行分类，指导运营针对不同价值用户进行精细化运营；
4）了解用户生命周期，针对不同周期的用户采取不同的运营策略。
2、指标和字段解读
通过用户和用户行为路径可以分析PV、UV、PV/UV、跳失率、总订单量等运营指标；
通过用户行为和时间可以分析用户的购物行为特征；
通过用户和商品、商品类别可以分析用户的购买商品偏好；用户和时间可以分析用户的购买时间偏好，便于对不同商品和时间偏好的用户采取个性化时间推荐（push、短信等推送）；
通过商品类别和用户行为可以分析不同商品类别受欢迎程度，指导运营进行商品的上新或下架；
通过商品类别和时间可以分析不同商品类别的热销时间段，便于做活动的推广；
结合AARRR模型，可以分析用户的生命周期，划分不同用户所处的周期阶段，采用差异化运营；
通过时间和用户的生命周期字段，结合RFM模型可以给用户做价值分类，对客户进行差异化管理。
四、数据清洗
（查看数据清洗流程：https://www.jianshu.com/p/adb82624df14)
将csv导入mysql的方法：
切换命令行菜单：https://jingyan.baidu.com/article/f00622280752dbfbd3f0c815.html
导入数据：https://blog.csdn.net/qq_25504271/article/details/78911151
1）选择子集
导入之前已选择好
2）列名重命名
无需更改列名
3）数据类型转换
可以在设计表菜单栏更好数据类型
4）数据去重
查询重复值：
select *,count(1)
from user1
group by user_id,item_id,behavior_type,item_id,time
having COUNT(user_id)>1;
存在重复值
存在重复值，但由于同一用户同一个行为在一小时内是可能存在多次的，因此这里不做去重处理。
5）缺失值处理
查询缺失值：
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 '时间字段的非空比例'
FROM USER1
WHERE user_id IS NOT NULL;
无缺失值
经查询，无缺失值
6）关联数据
由于只有一个表格，无需做表关联（如果需要关联，可以到分析过程中有需要时进行关联，这样会提高MySQL的性能。
7）异常值处理
检查时间是否异常
SELECT MIN(time), MAX(time)
FROM USER1;
检查用户行为类别是否异常
SELECT DISTINCT behavior_type
FROM USER1;
时间数据
行为数据
无需处理异常值
8）数据标准化整理
日期数据整理：
为方便后续的分析，将日期数据分为日期和时间两个维度
1、新增一列date，用于储存日期值
-- 新增列：alter table 表名 add column 列名 varchar(20);
alter table user1 add column date varchar(20) NOT NULL AFTER time;
2、复制time列的数据到date列
-- 更新列：UPDATE table_name SET field1=new-value1, field2=new-value2 [WHERE Clause]
UPDATE user1
SET date=time
3、将date转化为年月日
UPDATE USER1
SET date = DATE_FORMAT(date, '%Y-%m-%d');
4、将time转化为小时
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');（截取时间）
方法二：
UPDATE USER1
SET time = left(SUBSTRING_INDEX(time,' ',-1),2);
有多种方法可以实现数据截取操作（时间函数和字符串函数都可以操作，使用时间函数的时候需要注意截取后的数据格式类型需要转化，否则会报错，截取时间后的格式不能是datetime）
时间数据整理
行为数据整理：
将用户行为数据进行替换： 1：pv 2：fav 3：cart 4：buy
将behavior_type列进行更改：
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)
需要注意字符串要加引号
整理后数据
以上就完成了数据的整理工作。
五、指标的构建
1、总体运营指标：
1）流量指标：
计算页面访客数(pv)、独立访客数(uv)、人均点击数(uv/pv)
1）计算页面访客数(pv)、独立访客数(uv)、人均点击数(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"
访客数据
页面访客：987911次、独立访客数：8474位、人均点击次数116.58次。
95/30≈3.89次，日人均点击次数大概为3.89次/人/天
2）每日流量指标变化趋势
日流量数据
通过Navicat导出数据进行可视化处理：
pv、uv 趋势图
pv/uv 趋势图
pv、uv指标呈正相关性；
三个指标在大部分时间走势平稳，由于双十二的影响，从2014-12-11开始上升，到2014-12-12达到峰值，2014-12-13结束回到正常水平。
2、AARRR漏斗转化率
1）按照页面访客计算漏斗转化率
1）按照页面访客计算漏斗转化率
SELECT
behavior_type
,count(*)
FROM user1
GROUP BY behavior_type
用户行为漏斗
由于在购物环节中，收藏和加入购物车行为没有严格的先后之分，可将两个个步骤作为同一步，最终得到用户购物行为各环节转化率，如下：
给环节转化率
转化率
从整体转化率来看：浏览- 收藏/加购转化率仅为5%，总体购买转化率为1%，说明有大部分的用户在浏览后未进行下一步操作，平时“逛街看看”成为一种习惯；
从节点转化率来看：浏览-加购/收藏环节转化率很低，收藏/加购-购买的转化率也只有20%，说明有相当一部分用户是喜欢“囤货”，可能是为了等节假日购买？由于整体的节点并不是最细的不可分割节点，整体的数据比较粗糙，如果需要进一步的深入分析，需要有更细的转化率数据（由于操作路径每个环节都会损失一部分，因此如果能近量的较少客户购买时所需要的操作步骤，对提升整体转化率应该会有很好的提高）。
2）独立访客计算漏斗转化率
用户行为的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'
独立访客转化率
用户每个环节的转化率差不多，需要更多数据才能发现用户流失原因。
3)、跳失率
浏览页跳失率： = 只访问一次就离开的人数/总用户数
只浏览用户
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')
浏览页面流失人数
只加收藏、购物车人数
只加收藏、购物车
SELECT
count(DISTINCT user_id) 只加收藏、购物车用户数
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)、订单指标：
1）总成交量和人均购买次数：
总成交量和人均购买次数：
SELECT
count(behavior_type)总成交量
,count(DISTINCT user_id)总购买用户数
,count(behavior_type)/count(DISTINCT user_id)人均购买次数
FROM user1
WHERE behavior_type='buy'
总成交量
2）每日总成交和人均成交情况:
每日总成交和人均成交情况
SELECT
date
,count(behavior_type)总成交量
,count(DISTINCT user_id)总购买用户数
,count(behavior_type)/count(DISTINCT user_id)人均购买次数
FROM user1
WHERE behavior_type='buy'
GROUP BY date
ORDER BY date;
每日成交情况
每日成交量
（4）复购率:
1)总体复购率
1)总体复购率
购买人数
SELECT
count(DISTINCT user_id) as 成交人数
FROM user1
WHERE behavior_type='buy'
购买次数大于1
SELECT
user_id
,count(behavior_type) as 购买次数
FROM user1
WHERE behavior_type='buy'
GROUP BY user_id
HAVING count(behavior_type)>1
复购人数
SELECT
count(DISTINCT user_id)
FROM
(SELECT
user_id
,count(behavior_type) as 购买次数
FROM user1
WHERE behavior_type='buy'
GROUP BY user_id
HAVING count(behavior_type)>1
) as a
复购率=复购人数/购买人数=2295/4330=53%
2)商品品类销量排名(商品复购率)
2)商品销售排名
SELECT
item_category
,COUNT(behavior_type)购买次数
FROM USER1
WHERE behavior_type='BUY'
GROUP BY item_category
ORDER BY COUNT(behavior_type) DESC
LIMIT 10;
商品销售排名
商品排名
如果有更多数据，可根据商品品类属性进行研究和下钻，优化商品结果，但这里因为数据脱敏无法进行下钻分析
3)用户复购排名
3)用户复购排名
SELECT
user_id
,count(behavior_type)购买次数
FROM user1
WHERE behavior_type='BUY'
GROUP BY user_id
ORDER BY count(behavior_type) DESC
LIMIT 10;
购买次数
以上用户对平台的忠诚度比较高，对平台的销售贡献度也高（利润贡献情况还需要具体分析），对不同的客户可以收集相应的用户画像，并对用户进行分层管理和营销，从而达到精细化运营
2、用户行为特征分析
（1）、用户行为时间的特征
1）、按日期维度
1）、按日期维度
SELECT
date
,count(1)点击次数
,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)加购次数
,SUM(CASE WHEN behavior_type='buy' THEN 1 ELSE 0 END)购买次数
,COUNT(DISTINCT USER_ID)每日用户数
,count(*)/count(DISTINCT user_id)人均行为次数
,concat(ROUND(SUM(CASE WHEN behavior_type='PV' THEN 1 ELSE 0 END)/count(1)*100,2),'%')浏览数占比
,concat(ROUND(SUM(CASE WHEN behavior_type='BUY' THEN 1 ELSE 0 END)/count(1)*100,2),'%')购买数占比
FROM user1
-- where date != '2014-12-12'
GROUP BY date
ORDER BY date;
日期维度
日维度
用户活跃度与总体点击数是正相关的，走势平稳，不过在双十二电商大促这天各项指标暴增，且当天点击数占比有所下降(用户的点击更有针对性，前期已经选好商品，就等双十二当天直接购买的客户数量比较多？), 成交数占比大幅上升。
2）、周维度
2）、周维度
查询数据发现2014-11-18到2014-11-23,2014-12-15到2014-12-18均不满一周，因此，只取完整的三周进行分析：
SELECT
DATE_FORMAT(date,'%W')星期
,count(1)点击次数
,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)加购次数
,SUM(CASE WHEN behavior_type='buy' THEN 1 ELSE 0 END)购买次数
,COUNT(DISTINCT USER_ID)每日用户数
,count(*)/count(DISTINCT user_id)人均行为次数
,concat(ROUND(SUM(CASE WHEN behavior_type='PV' THEN 1 ELSE 0 END)/count(1)*100,2),'%')浏览数占比
,concat(ROUND(SUM(CASE WHEN behavior_type='BUY' THEN 1 ELSE 0 END)/count(1)*100,2),'%')购买数占比
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');
周维度
周维度
一周中的大部分时间用户活跃度都比较平稳，周五比较特殊，出现了增长（ 查看数据发现双十二正好是周五，属于特殊活动日，如果进行详细分析时应该将双十二的日期排除分析有更有意义）。
3）、小时维度
3）、小时维度
SELECT
hour
,count(1)点击次数
,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)加购次数
,SUM(CASE WHEN behavior_type='buy' THEN 1 ELSE 0 END)购买次数
,COUNT(DISTINCT USER_ID)每日用户数
,count(*)/count(DISTINCT user_id)人均行为次数
,concat(ROUND(SUM(CASE WHEN behavior_type='PV' THEN 1 ELSE 0 END)/count(1)*100,2),'%')浏览数占比
,concat(ROUND(SUM(CASE WHEN behavior_type='BUY' THEN 1 ELSE 0 END)/count(1)*100,2),'%')购买数占比
FROM user1
-- where date != '2014-12-12'
GROUP BY hour
ORDER BY hour;
小时维度
小时维度
晚间用户较为活跃，但用户行为倾向于浏览；白天尤其是中午左右的时段，购买行为的比率相对一天中最高，此时购买的目的性最强（浏览数占比与购买数占比进行关联分析得出结论）。
(2)用户商品偏好特征：
(2)用户商品偏好特征：
SELECT
item_category
,COUNT(*)点击次数
,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)加购次数
,SUM(CASE WHEN behavior_type='buy' THEN 1 ELSE 0 END)购买次数
,concat(ROUND(SUM(CASE WHEN behavior_type='PV' THEN 1 ELSE 0 END)/count(behavior_type)*100,2),'%')浏览数占比
,concat(ROUND(SUM(CASE WHEN behavior_type='BUY' THEN 1 ELSE 0 END)/count(1)*100,2),'%')购买数占比
FROM user1
-- where date != '2014-12-12'
GROUP BY item_category
ORDER BY 浏览次数 DESC
商品维度
按照商品品类区分（矩阵分析），根据点击次数和购买次数两个维度将所有商品划分到四个象限：
浏览与购买分布散点图
点击数高，购买数高。说明此类产品刚需比较强，品牌多且种类丰富，用户在较高的需求下有很多的选择；
点击数低购买数高。用户的购买决策十分果断，且对于该类产品的需求量也是很大的，说明该类产品选择性比较小，可能形成几个品牌垄断的情况，或者产品的差异性较小，用户不愿花费过多的精力去挑选。
点击数低购买数低，绝大多数产品都集中在这个象限，这种产品存在很多的替代品，用户很难集中在某个子类进行大量购买，而是跳跃式选购。
点击数高购买数低，这类产品的需求弹性较大，用户购买存在随机性。
(3)、用户购买路径特征：
用户购买商品分为以下几类过程：
直接购买
浏览后购买
加购物车购买
浏览加购物车购买
收藏购买
浏览收藏购买
(3)、用户购买路径特征：
1)创建用户行为路径表视图(对用户、商品、日期进行分组，表结构：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)加购物车
,SUM(CASE WHEN behavior_type='buy' THEN 1 ELSE 0 END)购买
FROM user1
GROUP BY user_id,item_id,date;
2)将用户行为标准化(存在该类行为则标记为1，否则为0)
CREATE VIEW 用户行为标准化表 AS
SELECT user_id
,(CASE WHEN 浏览>0 THEN 1 ELSE 0 END)浏览
,(CASE WHEN 收藏>0 THEN 1 ELSE 0 END)收藏
,(CASE WHEN 加购物车>0 THEN 1 ELSE 0 END)加购物车
,(CASE WHEN 购买>0 THEN 1 ELSE 0 END)购买
FROM 用户行为
3)建立标准化指标表(user_id,购买路径类型)
--选取user_i作为分组依据，不会出现user_id重复的情况，但是会导致存在多种路径的用户数据的缺失（需要跟业务统一好计算口径）
CREATE VIEW 标准化指标表1 AS
SELECT user_id
,concat(浏览,收藏,加购物车,购买) as 购买路径类型
FROM 用户行为标准化表 as a
WHERE a.购买>0
GROUP BY user_id;
4)统计各指标用户数
SELECT 购买路径类型
,COUNT(DISTINCT user_id)用户数
FROM 标准化指标表
GROUP BY 购买路径类型;
3.2)
选取user_id和路径作为分组依据，因此会出现user_id重复的情况（需要统一计算口径，跟前面的只把user_id作为分组的结果进行对比）
CREATE VIEW 标准化指标表 AS
SELECT user_id
,concat(浏览,收藏,加购物车,购买) as 购买路径类型
FROM 用户行为标准化表 as a
WHERE a.购买>0
GROUP BY user_id,concat(浏览,收藏,加购物车,购买)
4.2)统计各指标用户数
SELECT 购买路径类型
,COUNT(DISTINCT user_id)用户数
FROM 标准化指标表1
GROUP BY 购买路径类型
计算结果
计算结果
结果显示，直接够买的用户远远多于浏览后加购或者收藏再购买的用户，说明大部分购买者都是喜欢直接购买商品的，这个跟周围朋友的习惯也是符合的，基本上都是在浏览的阶段都不会马上购买，而是等到过段时间想买的时候就直接下单，几个步骤之间相当于是分开的。
(4)、用户复购率特征：
(4)、用户复购率特征：
客户购买次数
SELECT
DISTINCT user_id
,COUNT(user_id)'购买次数'
FROM user1
WHERE behavior_type='BUY'
GROUP BY user_id
ORDER BY COUNT(user_id) DESC
不同购买次数的客户分布
SELECT
a.购买次数
,count(DISTINCT user_id)用户数
FROM
(SELECT
DISTINCT user_id
,COUNT(user_id)购买次数
FROM user1
WHERE behavior_type='BUY'
GROUP BY user_id
ORDER BY COUNT(user_id) DESC
) as a
GROUP BY a.购买次数
ORDER BY a.购买次数
客户购买次数分布
购买次数分布
购买次数分布
用户的购买次数大部分集中在5次以内，购买频率都还挺高的
