使用SQL挖掘电商⽤户⾏为数据

1 分析流程和方法

1.1 数据类型分析

• 当没有清晰的数据看板时我们需要先清洗杂乱的数据，基于分析模型做可视化，搭建描述性的数据看板。
• 基于描述性的数据挖掘问题，提出假设做优化，或者基于⽤户特征数据进⾏预测分析找规律，基于规律设计策略。

1.2 数据分析的两个典型场景

在数据分析中有两个典型的场景：

⼀种是有数据，没有问题：需要先整体分析数据，然后再根据初步的描述分析，挖掘问题做诊断性分析，提出假设，设计策略解决问题。

另⼀种是已经发现了问题，或者已经有了假设，这种做数据分析更偏向于验证假设。

2 淘宝用户行为分析

本次是对“淘宝⽤户⾏为数据集”进⾏分析，在分析之前我们并不知道有什么问题，所以需要先进⾏描述性分析，分析数据挖掘问题。

2.1 解读元数据

数据来源：阿⾥天池实验室-淘宝⽤户⾏为数据集

https://t ianchi.aliyun.com/dataset/dataDetail?dataId=649&userId=1

数据集包含了2017年11⽉25⽇⾄2017年12⽉3⽇有⾏为的约⼀百万随机⽤户的所有⾏为,数据集的每⼀⾏表示⼀条⽤户⾏为，由⽤户 ID、商品ID、商品类⽬ID、⾏为类型和时间戳组成，并以逗号分隔。 本数据集包含：⽤户数量987994；商品数量4162024；商品类⽬数量9439；所有⾏为数量100150807

• 数据集字段说明
  
• 行为类型说明
  

2.2 选择分析方法

2.3 数据清洗

2.3.1 数据预处理

使用Navicat导入.csv文件

①创建一个数据库，一张表，右键点击表，选择导入向导

②在跳出的弹窗中选择.CSV文件，点击下一步

③选择文件来源和编码规格，点击下一步

④选择分隔符，一般这里直接默认就好了，点击下一步

⑤这一步是设置的关键步骤，如果要导入的数据（不含表头）是从第二行开始的，那我们的数据起始行要填“2”。如果只导入部分数据，还可以设置最后一行。

⑥选择目标表，如果有对应的表就直接选择哪个表，没有表的话就选择新建，点击下一步

⑦修改目标表的数据类型，一般默认就好

⑧选择将数据直接添加到目标表中，如果表中有数据，可以选择copy，下一步

⑨点击开始就可以了，看到Success就表示导入数据成功了，刷新表就可以看到导入的数据了

2.3.2 缺失值处理

2.3.3 一致化处理

timestamps字段是时间戳字符类型，⽽后⾯要做留存分析和⽤户活跃时间段需要⽤到时间戳中的⽇期字段和时间字段，在这⾥需要提前分下列。

/*设计思路： 
 alter table <表名> ：要添加列的表（SQL修改语句） 
 add <列名> <列的字段类型>：要添加列的列名&格式 
 update <表名>：要更新的表名（SQL更新语句）
 set <列名>=<时间戳处理函数（时间戳字段,年-月-日）>：给列插入来源于时间戳处理后的字段
 */ 
ALTER TABLE userbehavior ADD `日期` VARCHAR (255); 
UPDATE userbehavior 
SET `日期` = FROM_UNIXTIME(`时间戳`, '%Y-%m-%d' ); 

ALTER TABLE userbehavior ADD `时间` VARCHAR ( 255 );
UPDATE userbehavior 
SET `时间` = FROM_UNIXTIME(`时间戳`, '%h:%i:%s' );

#查看表内容
SELECT *
FROM userbehavior;

2.3.4 重复值处理

并把⽤户ID,商品ID，时间戳设置为主键

/* 
SELECT 查询表 
FROM 从用户行表中 
WHERE 用in子查询，以用户ID,商品ID,时间戳为主键，用count函数统计重复数据数量>1的） 
 */ 
 SELECT *
 FROM userbehavior
 WHERE (用户ID,商品ID,时间戳) IN ((select 用户ID,商品ID,时间戳 from
 userbehavior group by 用户ID,商品ID,时间戳 having count(*) > 1));

2.3.5 异常值处理

查询并删除2017年11⽉25⽇⾄2017年12⽉3⽇之外的数据

 #查看数据时间区间
SELECT MIN(`日期`) AS '起始时间' ,
       MAX(`日期`) AS '结束时间'
FROM userbehavior;

查询并删除⼩于2017-11-25的数据

select* 
from userbehavior
where `日期` < '2017-11-25';
DELETE from userbehavior 
WHERE `日期` < '2017-11-25';

验证数据

SELECT MIN(`日期`) AS '起始时间' ,
       MAX(`日期`) AS '结束时间'
FROM userbehavior;

2.4 描述分析

2.4.1 数据集中用户日活跃和周活跃时间规律

——分析思路：
从“时间戳“字段中抽取出“⽇期”和“小时”的数据，创建⼀个“活跃时间”字段，并从“行为类型”中⽤分组⽅式把⽤户的“浏览”“收藏”“加购物⻋”“购买”⾏为抽离出来，组成⼀个视图表，导出到Excel中⽤透视表分析⽤户的⽇活和周活规律。

——SQL提数：
增加活跃时间字段

// 创建“活跃时间”字段从时间戳中获取日期和小时数据(把后面的时间省略）
ALTER TABLE userbehavior ADD `活跃时间` VARCHAR ( 255 ); 
UPDATE userbehavior 
SET `活跃时间` = from_unixtime(`时间戳`,'%Y-%m-%d %H');
SELECT * from userbehavior;

查询⽤户 活跃时间分布，并创建视图

# 将数据集中的数据按不同时间维度进行分组，获得用户行为漏斗的时间分布数据
Create view 用户活跃时间表 as
select `活跃时间`,
sum(case when `行为类型`='pv' then 1 else 0 end) as `浏览`,
sum(case when `行为类型`='cart' then 1 else 0 end) as `加购物车`,
sum(case when `行为类型`='fav' then 1 else 0 end) as `收藏`,
sum(case when `行为类型`='buy' then 1 else 0 end) as `购买`
from userbehavior
group by `活跃时间`;

select * 
from 用户活跃时间表;

———Excel可视化

分析：
活跃曲线整体为上升状态，同为周六⽇，12⽉2号，3号相⽐11⽉25⽇，26⽇活跃度更⾼。

是否是⽤户增⻓带来的？

⽤户在周六周⽇相⽐其他时间更活跃（周六周⽇为休息⽇，⽤户有更多时间）

分析：
⼀天内⽤户活跃的最⾼峰期为21-22点（⽤户在这个时间段空闲较多）

正常职场打工人的睡前时间

2.4.2 在当日活跃的用户次日、三日、四日…还有多少活跃

——分析思路：

⽤户留存分析可以分为“新⽤户留存”和“活跃⽤户留存”
新⽤户存留⼀般指：新注册⽤户在⼀定时间周期内还会不会再登录
活跃⽤户存留需要根据产品类型和⽤户场景选择“关键⾏为”和选择“时间周期”

-关键⾏为：淘宝作为购物⽹站，⽤户浏览，收藏，加购，购买商品与交易⾏为⾼度相关都可作为关键⾏为。
-时间周期：淘宝拥有海量的SKU，基本可以满⾜⽤户各⽅⾯的需求，理论上⽤户每天都有购买需求，时间周期可以按天。

这个问题就是在求，数据集第⼀⽇在APP有关键⾏为的⽤户在第⼆天，第三天……还会继续在APP中有关键⾏为的⽤户占⽐
需要先列出每⽤户每天及当天后⾯⼜活跃的⽇期，⽤于后⾯求次⽇留存，三⽇留存……
之后按⽇期对⽤户进⾏分组，并抽取之后9天依然活跃的⽤户数量
最后⽤活跃⽤户表中后续活跃⽤户除⾸⽇活跃数量乘100加%号

——SQL提数：
列出每个⽤户每天及当天后⾯⼜活跃的⽇期，并创建“活跃时间间隔表”⽤于后⾯求次⽇存留，三 ⽇存留……

/* 用userbehavior进行自连接，并用左外连接定义左表为a表，右表为b表，b表日期大于等于a表日期。
（列出每用户每天及当天后面有活跃的日期，用于后面求次日存留，三日存留……） */
CREATE OR REPLACE view 活跃时间间隔表 as
SELECT 
	a.`用户ID`,
	a.`日期`,
	b.`用户ID` AS ID,
	b.`日期` AS `活跃日期`
FROM userbehavior a 
LEFT JOIN userbehavior b 
ON a.`用户ID` = b.`用户ID`
WHERE b.`日期` >= a.`日期`
ORDER BY a.`用户ID`;

SELECT * from `活跃时间间隔表`;

对“活跃时间间隔表视图”引⽤进⾏分组统计，计算每⽇存留⼈数并创建视图

/*
按日期对用户进行分组，并抽取之后9天依然活跃的用户数量
SELECT 查询结果：日期，首日留存率，次日留存率，三日留存率……（计算9日之内的存留）
FROM 从视图：活跃时间间隔
GROUP BY分组:按日期
*/
Create view 每日留存人数表 as
SELECT 日期,
COUNT(DISTINCT 用户ID) AS 首日用户数,
COUNT(DISTINCT IF(DATEDIFF(活跃日期,日期)=1 ,用户ID, NULL )) AS 第二日用户数,
COUNT(DISTINCT IF(DATEDIFF(活跃日期,日期)=2 ,用户ID, NULL )) AS 第三日用户数,
COUNT(DISTINCT IF(DATEDIFF(活跃日期,日期)=3 ,用户ID, NULL )) AS 第四日用户数,
COUNT(DISTINCT IF(DATEDIFF(活跃日期,日期)=4 ,用户ID, NULL )) AS 第五日用户数,
COUNT(DISTINCT IF(DATEDIFF(活跃日期,日期)=5 ,用户ID, NULL )) AS 第六日用户数,
COUNT(DISTINCT IF(DATEDIFF(活跃日期,日期)=6 ,用户ID, NULL )) AS 第七日用户数,
COUNT(DISTINCT IF(DATEDIFF(活跃日期,日期)=7 ,用户ID, NULL )) AS 第八日用户数,
COUNT(DISTINCT IF(DATEDIFF(活跃日期,日期)=8 ,用户ID, NULL )) AS 第九日用户数
FROM 活跃时间间隔表
GROUP BY 日期;
select * from `活跃时间间隔表`;

对留存⼈数表进⾏计算，统计活跃⽤户留存率

/*
每日留存人数表中后续活跃用户/首日活跃数量*100加%号
*/
ELECT 日期,首日用户数,
CONCAT(TRUNCATE ( ( 第二日用户数 / 首日用户数 ) * 100,2) ,"%") AS 次日留存率,
CONCAT(TRUNCATE ( ( 第三日用户数 / 首日用户数 ) * 100,2) ,"%") AS 二日留存率,
CONCAT(TRUNCATE ( ( 第四日用户数 / 首日用户数 ) * 100,2) ,"%") AS 三日留存率,
CONCAT(TRUNCATE ( ( 第五日用户数 / 首日用户数 ) * 100,2) ,"%") AS 四日留存率,
CONCAT(TRUNCATE ( ( 第六日用户数 / 首日用户数 ) * 100,2) ,"%") AS 五日留存率,
CONCAT(TRUNCATE ( ( 第七日用户数 / 首日用户数 ) * 100,2) ,"%") AS 六日留存率,
CONCAT(TRUNCATE ( ( 第八日用户数 / 首日用户数 ) * 100,2) ,"%") AS 七日留存率
FROM `每日留存人数表`;

——Excel可视化：

分析：

• ⽤户增⻓：从2017年11⽉15⽇至2017年12⽉3⽇，活跃⽤户新增38%
• 留存增⻓：从2017年11⽉15⽇至2017年12⽉3⽇，活跃⽤户次⽇留存增⻓18.67%，
  当⽇的活跃⽤户留存也在 快速增⻓，第七⽇留存⽐次⽇留存⾼18.56%。

假设随时间增⻓的留存率提升来源于新日活用户提升策略的优化，后续留存的提升来源于召回策略的优化。

2.4.3 用户从浏览到购买的整体转化率

——分析思路：
将数据集中按不同⽤户，不同商品维度进⾏分组获得某⼀⽤户⾏为对某⼀商品不同⾏为的数据，然后对“⽤户⾏为漏⽃表”中的浏览，加购物⻋，收藏，购买⾏为进⾏分组统计。
——SQL提数：
把各种⽤户⾏为分离出来并创建视图⽅便后续查询⽤户⾏为数据

/*
将数据集中按不同用户，不同商品，不同类目维度进行分组获得某一用户行为对某一商品不同行为的数据
 */ 
 Create view 用户行为漏斗表 as 
 select 用户ID,商品ID,类目ID, 
 sum(case when 行为类型='pv' then 1 else 0 end) as '浏览', 
 sum(case when 行为类型='cart' then 1 else 0 end) as '加购物车', 
 sum(case when 行为类型='fav' then 1 else 0 end) as '收藏',
 sum(case when 行为类型='buy' then 1 else 0 end) as '购买'
 from userbehavior
 group by 用户ID,商品ID,类目ID;

查询整体数据漏⽃

/*
对“用户行为漏斗表”中的浏览，加购物车，收藏，购买行为进行分组统计 
 */ 
select 
	sum(浏览) as 浏览,
	sum(收藏) as 收藏,
	sum(加购物车) as 加购物车,
	sum(购买) as 购买
from `用户行为漏斗表`;

——Excel可视化：

分析：
⽤户从浏览到购买整体转化率2.3%，具体主要在哪个环节流失还需要再细分⽤户路径分析

2.4.4 用户从浏览到购买的路径

——分析思路：
穷举所有可能的⽤户路径，引⽤“⽤户⾏为漏⽃表”视图，计在数据中点击⾏为⼤于0，购买⾏为⼤于0，其他两项为0，
则判定本⽤户购买路径为：点击—购买，其他路径同理，多次查询并⽤Excel表记录查询数据，⽤户PowerBI桑基图做可视化。

——SQL提数：

/*
计在数据中点击行为大于0，购买行为大于0，其他两项为0，则判定本用户购买路径为：点击—购买其他路径同理，多次查询并用Excel表记录查询数据*/ 
-- 点击-流失:66222
select count(用户ID) as '点击-流失' 
from `用户行为漏斗表` 
where 浏览>0 and 加购物车=0 and 收藏=0 and 购买=0;

select count(用户ID) as '点击-购买' 
from `用户行为漏斗表`
where 浏览>0 and 加购物车=0 and 收藏=0 and 购买>0; 

select count(用户ID) as '点击-收藏' 
from `用户行为漏斗表`
where 浏览>0 and 加购物车=0 and 收藏>0 and 购买=0;  

select count(用户ID) as '点击-收藏-加购' 
from `用户行为漏斗表` 
where 浏览>0 and 加购物车>0 and 收藏>0 and 购买=0; 

select count(用户ID) as '点击-收藏-加购-购买' 
from `用户行为漏斗表` 
where 浏览>0 and 加购物车>0 and 收藏>0 and 购买>0; 

select count(用户ID) as '点击-收藏-购买' 
from `用户行为漏斗表` 
where 浏览>0 and 加购物车=0 and 收藏>0 and 购买>0; 

select count(用户ID) as '点击-收藏-流失' 
from `用户行为漏斗表` 
where 浏览>0 and 加购物车=0 and 收藏>0 and 购买=0;  

select count(用户ID) as '点击-加购' 
from `用户行为漏斗表` 
where 浏览>0 and 加购物车>0 and 收藏=0 and 购买=0; 

select count(用户ID) as '点击-加购-流失' 
from `用户行为漏斗表` 
where 浏览>0 and 加购物车>0 and 收藏=0 and 购买=0;

——PowerBI可视化：

分析：
⽤户从浏览到购买的路径主要有4条，路径越⻓转化率越低
路径1：浏览→购买：转化率1.45%
路径2：浏览→加购物⻋→购买：转化率0.33
路径3：浏览→收藏→购买：转化率0.11%
路径4：浏览→收藏→加购物⻋→购买：转化率0.03% 以上转化率等于起始路径到购买的转化
以上转化率等于起始路径到购买的转化

2.4.5 平台主要给用户推送什么商品

——分析思路：
虽然我们没法直接从数据中找到平台推送的数据，但作为平台流量倾斜的商品，浏览量⼀般都会⽐其他商品的浏览量⾼⼀些，我们可以引⽤“⽤户⾏为漏⽃表”视图统计浏览量前100的商品及其类⽬。
——SQL提数：

/*
引用“用户行为漏斗表”查询商品ID，类目ID，并对浏览进行求和之后升序排列，取前100位 
 */ 
 SELECT 商品ID,`类目ID`,SUM(`浏览`) AS 商品排名
 FROM 用户行为漏斗表 
 group BY `商品ID`,`类目ID` 
 ORDER BY 商品排名 DESC 
 LIMIT 100;

——Excel可视化：

——描述性分析：
浏览量top100的商品浏览量呈阶梯分布，越靠前的阶梯之间的落差相对越⼤在这个阶梯中的商品越少，越靠后商品浏览量阶梯之间的落差相对越⼩，同阶梯内的商品越多。

是否是⽤于淘宝流量分配规则的原因造成的？（假设淘宝的规则是给所有商品分配的初始流量是⼀样的，后期这些商品中那些商品转化率⾼就给哪些商品更多曝光。）

浏览量TOP100的商品所属类⽬中，4756105，3607361，4357323三个类⽬浏览量远超其他类⽬。

这个⼏个类⽬商品类型是否是⾼频刚需类型的呢？

2.4.6 用户喜欢什么商品

——分析思路：
找⾼转化率的商品（销量⾼的有可能只是低价或者流量⼤）
——SQL提数：
查询计算商品转化率，升序排列，取前100个

/* 
引用“用户行为漏斗表”查询商品ID，类目ID，并计算商品转化率之后升序排列，取前100位 
 */ 
create view 商品转化率 as 
SELECT 商品ID,类目ID,ROUND(购买/浏览,3) AS 转化率 
FROM 用户行为漏斗表 
group BY 商品ID,类目ID,转化率 
ORDER BY 转化率 DESC 
LIMIT 100;
select * from 商品转化率;

——Excel可视化：

——描述性分析：
从商品看：有17款商品转化率超过了1。

是否是由于⽤户直接从购物⻋或者商品收藏直接复购，未点击商详？

从类⽬看：这些商品所属类⽬分布均匀，除965809，4801426，2735466，2640118， 5063620，4789432，2945933这7个类⽬之外，其他类⽬都只有⼀个商品在转化率 TOP100的商品中。

是否是由于淘宝是根据“同⼀类⽬下的⾼转化商品”给⽤户做推荐的？

2.4.7 如何判断哪些是高价值用户

——分析思路：
⽤户价值分析常⽤的分析⽅式是RFM模型

RFM模型是3个指标的缩写，最近⼀次消费时间（R），消费频率（F），消费⾦额（M） 然后给这三个指标根据价值分5个等级 ，进⾏打分计算分值和平均值，然后根据分值与平 均值对⽐，分出“⾼”“中”“低”，综合进⾏⽤户分层。

• R：根据⽤户最近⼀次的购买时间与2017年12⽉3⽇之间的差值，判断⽤户最近⼀次消费时间间隔。
• F：将数据集中⽤户在2017年11⽉25⽇⾄2017年12⽉3⽇9天时间内的购买次数作为消费频率
• M：由于本数据集中未包含购买⾦额字段，暂时排除此指标。

——SQL取数与分析：
1）建⽴打分标准：先计算R,F的值，并排序，根据R,F值最⼤值和最⼩值的区间设计本次得打分标准

关于打分标准：不同业务的⽤户消费频率，消费⾦额，精细化运营策略与成本……都是不同，
⼀般常⽤”分位数“建⽴打分标准，由于SQL并不是专业得统计分析⼯具，计算分位数较为复杂，
本次仅使⽤最⼤值和最⼩值的区间初略建⽴规则。

分位数：是指在统计学中把所有数值由⼩到⼤排列并分成⼏等份，取处于对应⼏个分割点 位置的数值。

-查询并计算R，F值创建视图

/*
根据用户最近一次购买时间与2017年12月3日之间的差值计算R值 
将用户在2017年11月25日至2017年12月3日9天时间内的购买次数作为消费频率 
*/
Create or replace view RF数值表 as 
select 
	用户ID,
	DATEDIFF('2017-12-03',max(日期)) as R,
	sum(case when 行为类型 ='buy' then 1 else 0 end) as F
from userbehavior 
where 行为类型='buy' 
group by 用户ID;
select * from RF数值表;

-引⽤RF数值表，分别查询R,F的最⼤值和最⼩值

select min(R),max(R)
from rf数值表;

select min(F),max(F)
from rf数值表;

-结合⼈⼯浏览的建⽴打分标准

消费时间间隔：在1~8区间内四等分

消费频率：由于⼈⼯ 浏览时发现很少有超过20次购买的，故消费频率在20以内四等分

2）给R，F按价值打分

/*
通过case表达式对照打分规则表给用户价值打分，并创建视图 
*/ 
Create view 用户RF价值表 as 
select 用户ID,
(case when R between 0 and 2 then 4 
			when R between 3 and 4 then 3 
			when R between 5 and 6 then 2 
			when R between 7 and 8 then 1
else 0 end) as R价值,
(case when F between 1 and 5 then 1 
			when F between 6 and 10 then 2
			when F between 11 and 15 then 3 
			when F >=15 then 4
else 0 end) as F价值 
from `rf数值表` 
group by 用户ID;
select * from 用户RF价值表;

3）计算价值的平均值

select avg(R价值),avg(F价值) 
from 用户rf价值表

4）⽤平均值和⽤户分类规则表⽐较得出⽤户分类

/*
根据用户分类规则表创建用户价值分类表 
 */ 
Create view 用户价值分类表 as 
select 用户ID,
  (case when R价值>3.2846 and F价值>1.1595 then "重要价值用户" 
				when R价值>3.2846 and F价值<1.1595 then "重要发展用户" 
				when R价值<3.2846 and F价值>1.1595 then "重要保持用户"
				when R价值<3.2846 and F价值<1.1595 then "重要挽留用户"end) as 用户分类 

from 用户rf价值表;
select * from 用户价值分类表;

-查询各类⽤户数量

select 用户分类, count(用户ID) as 用户数量
from 用户价值分类表
group by 用户分类;

——Excel可视化

2.5 诊断分析

通过描述性分析得到可视化的数据后我们⼀般会先看⼀下是否符合业务常识

如：假设⼀个⻚⾯的UV（浏览⼈数）⽐PV（浏览次数）还⾼，那这个数据质量肯定是有问题的

如果符合常识接下来我们会通过与⾏业平均数据和本产品的同⽐环⽐对⽐看是否正常，如果不正常就要找原因，设计解决⽅案，如果正常那就看是否有可以优化的地⽅。

2.5.1 诊断分析

⾸先来看⼀下这些描述性分析是否符合业务常识和指标是否正常：

1.活跃曲线整体为上升状态，同为周六⽇，12⽉2号，3号相⽐11⽉25⽇，26⽇活跃度更⾼。

正常：结合描述分析4中的活跃⽤户的增⻓。

2.⽤户在周六周⽇相⽐其他时间更活跃

正常：周六周⽇为休息⽇，⽤户有更多时间来刷淘宝，反映在数据上就是活跃度的增加。

3.⼀天内⽤户活跃的最⾼峰期为21点-10点之间

正常：⽤户在这个时间段有空闲，996的都下班啦~

4.从2017年11⽉15⽇至2017年12⽉3⽇，活跃⽤户新增38%

还需验证：
如果是由于新注册⽤户或者⽼⽤户召回策略带来的增⻓符合常识，具体还需结合新注册⽤ 户数据和⽤户召回策略数据做验证。

5.从2017年11⽉15⽇至2017年12⽉3⽇，活跃⽤户次⽇留存增⻓18.67%，当⽇的活跃⽤户 留存也在快速增⻓，第七⽇留存⽐次⽇留存⾼18.56%。

不符合常识：因为从⻓期来看⽤户都是会流失的，只是⽣命周期⻓短问题，⽽从淘宝的⽤ 户⾏为来看同批⽤户的存留数据竟然随着时间的增加⽽增加。

假设场景可能是这样的：⽤户⼩A注册了淘宝APP，第⼆天就不再登录了，⽽第三天收到了 淘宝的推荐提醒（APP消息，短信……）在消息中发现了⾃⼰喜欢的商品，⽽且还有优惠 下单买了，第四天⼜收到了淘宝的消息，还是⾃⼰喜欢的……

这⾥的具体数据还需要结合⽤户⽣命周期运营的策略和数据做验证。

6.⽤户从浏览到购买整体转化率2.3%

正常，根据之前了解到的电商数据，多种客单价的商品（⼏⼗~⼏千）在⼀起，整体转化率 在2%~3%之间，当然具体还需要结合历史的同⽐，环⽐数据取看。

7.⽤户从浏览到购买的路径主要有4条，路径越⻓转化率越低。

正常：从流量的⻆度，每多⼀个步骤就会多⼀些⽤户流失这个符合常识。

8.浏览量top100的商品浏览量呈阶梯分布，越靠前的阶梯之间的落差相对越⼤在这个阶梯中 的商品越少，越靠后商品浏览量阶梯之间的落差相对越⼩，同阶梯内的商品越多。

待验证： 假设淘宝会给⾼转化的爆款商品更多的曝光，商品浏览量呈⾦字塔分布是正常的。

9.浏览量TOP100的商品所属类⽬中，4756105，3607361，4357323三个类⽬浏览量远超 其他类⽬。

还需验证：
抽取购买购买次数判断这个⼏个类⽬商品类型是否是⾼频刚需类型的呢？

10.从商品看：有17款商品转化率超过了1。

不正常：
还需验证：是否是由于⽤户直接从购物⻋或者商品收藏直接复购，未点击商详？

11.从类⽬看：这些商品所属类⽬分布均匀，除965809，4801426，2735466，2640118， 5063620，4789432，2945933这7个类⽬之外，其他类⽬都只有⼀个商品在转化率 TOP100的商品中。

还需验证： 是否是由于淘宝是根据“同⼀类⽬下的⾼转化商品”给⽤户做推荐的？

2.5.2 假设与验证

根据以上诊断分析我们梳理出了以下假设，做假设验证。

假设1：这些商品中有⾼转化率的爆款商品

引⽤“商品转化率视图”查询排名前5的商品转化率

SELECT 商品ID, 类目ID , 转化率 
FROM `商品转化率` 
WHERE 商品ID IN (3027414,4657130,812879,2331370,3006793) 
GROUP BY 商品ID;

• 对⽐同类⽬的其他商品转化率
  
  对⽐浏览量TOP5的商品，发现这些商品转化率在同⼀类⽬下并不⾼，假设不成⽴

假设2：4756105，3607361，4357323三个类⽬属于⾼频刚需类⽬

抽取这⼏个类⽬的商品某买频次数据验证

-创建类目购买频次表

create or replace view 类目购买频次 as
select 类目ID,sum(case when 行为类型='buy' then 1 else 0 end )`购买频次`
from userbehavior
WHERE 行为类型='buy'
group by 类目ID;
select * FROM 类目购买频次;

-计算类目购买频次平均值

select avg(购买频次)
from 类目购买频次;

-查询4756150,3607361,4357323三个类目的购买次数

select 类目ID,购买频次
from 类目购买频次
where 类目ID in (4756150,3607361,4357323);

假设3：有部分⽤户是未点击商详直接从收藏和购物⻋购买的。

查询转化率超过1的商品的⽤户⾏为数据

⽤户不是直接从收藏和购物⻋购买的，只是后续复购未点击商详，假设不成⽴

假设4：淘宝推荐的商品主要是“同⼀类⽬下的⾼转化商品”

给浏览量TOP100的商品和转化率TOP100的商品做匹配看其中重合的商品有多少。

⽤Excel对浏览量TOP100的商品ID和转化率TOP100的商品ID进⾏去重，结果⽆重复值，假设不成⽴

3.结论：

1）⽤户活跃：⽤户活跃曲线整体呈上升趋势，在⼀周中周六，周⽇活跃度⽐平时更⾼，在⼀ 天中⽤户活跃曲线从凌晨4点开始往上升，在中午12点和下午5~6点有两个⼩低⾕（吃饭）， 到晚上9点时活跃度达到顶峰。

2）⽤户留存：从2017年11⽉15⽇至2017年12⽉3⽇的⽤户留存数据来看，淘宝的⽤户留存数据较好，活跃⽤户次⽇留存增⻓18.67%，当⽇的活跃⽤户留存也在快速增⻓，第七⽇留存⽐次⽇留存⾼18.56%。

3）⽤户转化：整体转化2.3%，⽤户从浏览到购买的路径主要有4条，路径越⻓转化率越低。 路径1：浏览→购买：转化率1.45% 路径2：浏览→加购物⻋→购买：转化率0.33 路径3：浏览→收藏→购买：转化率0.11% 路径4：浏览→收藏→加购物⻋→购买：转化率0.03%

4）平台推荐与⽤户偏好：从数据集中的数据来看，排除⽤户兴趣偏好标签，淘宝给⽤户推送的商品主要是⾼频刚需的类⽬，促使⽤户复购，流量回流平台。