本分析主要使用MySQL和Tableau对淘宝用户的行为进行分析。数据为阿里云天池中提供的数据。

数据集-阿里云天池​tianchi.aliyun.com/dataset/dataDetail?dataId=649

一、数据说明

本数据集包含了2017年11月25日至2017年12月3日之间，有行为的约一百万随机用户的所有行为（行为包括点击、购买、加购、喜欢）。关于数据集中每一列的详细描述如下：

注意到，用户行为类型共有四种，它们分别是

二、分析目标

三、数据清理

由于源文件数据较多，本文选取前10万行数据分析。使用Navicat将数据导入MySQL数据库，并按照数据说明，设置对应字段名称。

1 缺失值和重复值处理

#查看有无重复数据 SELECT COUNT(*) FROM (SELECT DISTINCT * FROM userbehavior) a;

结果显示无重复数据。

#查看各字段有无缺失值 SELECT COUNT(用户id),COUNT(商品id),COUNT(商品类目id),COUNT(行为类型),COUNT(时间戳) FROM userbehavior;

结果显示各字段无缺失值。

2 时间类型转换

为便于分析，字段”时间戳“需转换时间类型。

#增加日期列 ALTER TABLE userbehavior ADD COLUMN 日期 VARCHAR(10); UPDATE userbehavior SET 日期=FROM_UNIXTIME(`时间戳`,%y-%m-%d); #增加时段列 ALTER TABLE userbehavior ADD COLUMN 时段 VARCHAR(10); UPDATE userbehavior SET 时段=FROM_UNIXTIME(`时间戳`,%H);

3 处理异常值

数据集日期应在2017年11月25日至2017年12月3日以内。检查数据时间范围，若有异常数据，将其删除。

#统计不在日期范围内的数据 SELECT COUNT(日期) FROM userbehavior WHERE 日期 NOT BETWEEN 17-11-25 AND 17-12-03;#删除日期异常的数据 DELETE FROM userbehavior WHERE 日期 NOT BETWEEN 17-11-25 AND 17-12-03;

四，流量分析

1 PV/UV随天变化趋势

SELECT 日期,COUNT(行为类型) AS PV,COUNT(DISTINCT 用户id) AS UV FROM userbehavior WHERE 行为类型=pv GROUP BY 日期;

2 PV/UV随小时变化趋势

SELECT 时段,COUNT(行为类型) AS PV,COUNT(DISTINCT 用户id) AS UV FROM userbehavior WHERE 行为类型=pv GROUP BY 时段 ORDER BY 时段;

3 分析

PV与UV的每日变化趋势大致相同，11月25日至12月1日流量较为稳定，趋于低值。而12月第一个周末（12-2和12-3）相较于11月最后一个周末（11-25和11-26）的pv与uv均有较大幅度地提升，研究可发现双12的各项预热活动带来了该周末的访问量增长。

PV与UV的小时变化中，凌晨1-6点是用户访问的低值时段，晚上20-22点是用户访问的活跃时段，早上6-10时和晚饭后18点-21点是用户访问淘宝app的增长迅速时段，符合人的正常作息。建议可在用户活跃时段推送新品及促销活动，提高购买率；若有活动日，则可在用户活跃时段和增长迅速时段分时段投放活动信息和优惠券。

同时流量分析通常与营销活动、广告投放等结合起来评估站内站外活动和投放的效果。

五 用户分析

1 用户行为漏斗分析

行为漏斗转化分析有两种分析维度，一种按行为计数，偏重看有多少次浏览行为、购买行为等以及对应的转化率；一种按uv(独立访客)计数，偏重看有多少用户浏览、加购并转化发生了购买行为。实际工作中根据业务需求从不同的维度进行分析。这里选择按行为计数进行计算。

计算浏览数，收藏数，加购数，购买数

SELECT 行为类型,COUNT(行为类型) AS 访问量 FROM userbehavior GROUP BY 行为类型 ORDER BY 访问量;

结果导入Tableau绘制百分比漏斗图，其中左侧为当前行为占流量总量的百分比，右侧为当前行为占上一行为的百分比。

2 复购

（1）复购率

复购率统计口径：有复购行为的用户数 / 有购买行为的用户数

创建包含所有有购买行为的用户id和购买次数的视图

CREATE VIEW 购买次数 AS SELECT 用户id, COUNT(*) AS 购买次数 FROM userbehavior WHERE 行为类型=buy GROUP BY 用户id;

计算复购率

SELECT (SELECT COUNT(用户id) FROM 购买次数 WHERE 购买次数>1)/COUNT(*) AS 复购率 FROM 购买次数;

（2）复购频数分布

SELECT 购买次数,COUNT(用户id) AS 人数 FROM 购买次数 GROUP BY 购买次数 ORDER BY 人数 DESC;

3 留存

计算该时间段内次日、三日和七日留存数及留存率

SELECT c.日期,COUNT(DISTINCT c.用户id) AS 活跃用户数, COUNT(DISTINCT CASE WHEN 时间间隔=1 THEN 用户id ELSE null END) AS 次日留存数, COUNT(DISTINCT CASE WHEN 时间间隔=1 THEN 用户id ELSE null END)/COUNT(DISTINCT c.用户id) AS 次日留存率, COUNT(DISTINCT CASE WHEN 时间间隔=3 THEN 用户id ELSE null END) AS 三日留存数, COUNT(DISTINCT CASE WHEN 时间间隔=3 THEN 用户id ELSE null END)/COUNT(DISTINCT c.用户id) AS 三日留存率, COUNT(DISTINCT CASE WHEN 时间间隔=7 THEN 用户id ELSE null END) AS 七日留存数, COUNT(DISTINCT CASE WHEN 时间间隔=7 THEN 用户id ELSE null END)/COUNT(DISTINCT c.用户id) AS 七日留存率 FROM (SELECT a.用户id,a.日期,b.日期 AS 日期2,DATEDIFF(b.日期,a.日期) AS 时间间隔 FROM userbehavior AS a LEFT JOIN userbehavior AS b ON a.用户id = b.用户id) AS c GROUP BY c.日期;

4 分析

从行为转化漏斗看，浏览到加购的总体转化率为6.07%，从浏览到购买的总体转化率为2.34%；从收藏到购买的相对转化率为76.57%，从加购到收藏的相对转化率为50.39%。需结合具体类目商品转化率进行评估分析。若转化率低于正常水平，则可通过用户行为途径寻找原因，比如就目前数据PV转化率低，则可从商品价格、页面信息等因素入手深挖原因。

该时间段内复购率为65.8%，较为正常。通常时间周期越长，复购率越高，在实际业务中，更常看季复购率、半年复购率甚至年复购率。不同类目的复购率也会有差异。

留存率：以11.25日为首日统计日，则次日留存率为79.6%，三日留存率为78.3%，七日留存率为98.1%。双12活动预热使得用户在周末的留存率有所上涨。

六 商品分析

1 按照商品类目分析

创建商品类目视图

CREATE VIEW 商品类目 AS SELECT 商品类目id, SUM(CASE WHEN 行为类型=pv THEN 1 ELSE 0 END) AS 浏览量, SUM(CASE WHEN 行为类型=buy THEN 1 ELSE 0 END) AS 购买量, CONCAT(ROUND(SUM(CASE WHEN 行为类型=buy THEN 1 ELSE 0 END)*100/SUM(CASE WHEN 行为类型=pv THEN 1 ELSE 0 END),1),%) AS 购买转化率 FROM userbehavior GROUP BY 商品类目id;

（1）浏览量TOP10商品类目

SELECT * FROM 商品类目 ORDER BY 浏览量 DESC LIMIT 10;

（2）购买量TOP10商品类目

SELECT * FROM 商品类目 ORDER BY 购买量 DESC LIMIT 10;

2 按商品分析

创建商品视图

CREATE VIEW 商品 AS SELECT 商品id, SUM(CASE WHEN 行为类型=pv THEN 1 ELSE 0 END) AS 浏览量, SUM(CASE WHEN 行为类型=buy THEN 1 ELSE 0 END) AS 购买量, CONCAT(ROUND(SUM(CASE WHEN 行为类型=buy THEN 1 ELSE 0 END)*100/SUM(CASE WHEN 行为类型=pv THEN 1 ELSE 0 END),1),%) AS 购买转化率 FROM userbehavior GROUP BY 商品id;

（1）浏览量TOP10商品

SELECT * FROM 商品 ORDER BY 浏览量 DESC LIMIT 10;

（2）购买量TOP10商品

SELECT * FROM 商品 ORDER BY 购买量 DESC LIMIT 10;

3 分析

部分购买量高的类目/商品浏览量低，而部分浏览量高的类目/商品转化率低，需结果具体商品/类目的特性进行分析。

对于热销的类目/商品，可多推出一些和该类目/商品相关的其他类目/商品捆绑交叉销售，提高销量。

若有新商品以及参与活动的引流商品等，也需要单独看此类商品表现。