SQL数据分析--淘宝APP用户行为分析

一、提问

1、淘宝用户流失情况如何？造成这种流失的原因是什么？

2、淘宝APP用户行为的规律是什么？

理解数据

1、数据来源：

Tianchi:Data sets

这个数据集来自阿里云天池，淘宝 选择了数据APP 2017年11月25日至2017年12月3日，约100万随机用户的所有行为数据(包括点击、购买、购买和喜欢)。

2、数据导入SQL

第一步是选择导入导向

第2步，选择Excel文件数据源

第三步是选择需要导入的表

如果第四步是为源定义一些附加选项excel数据量大，可以在导入过程中设置分批导入。

第五步是选择将数据导入哪个表

若将数据导入到新数据库表中，则在下面的新表中打钩。

若将数据导入到现有的数据库表中，则无需在新建表中打钩。

第六步，定义源表（excel与目标表(数据库表)的对应关系

如果目标表（数据库表）设置了主键，则必须根据您的业务需要检查相应目标栏的主键，否则以后不能选择更新模式。

第七步是选择导入模式

我们可以根据具体情况选择不同的选项。如果是新手表，我们选择第一项-添加：将记录添加到目标表中。

步骤8：单击开始按钮执行导入命令

导入完成后，会有导入结果的消息提示，可以看导入是否成功，错误的原因是什么，相应的纠正。

3、表中的字段含义

user-id：用户编号

item-id：商品编号

behavior-type：用户行为类型(包括点击、收藏、购物车、购买四种行为，分别以数字1、2、3、4表示)

item-category：商品类别编号

time：发生用户行为的时间

三、数据清洗

通过数据库管理工具Navicat 将数据集导入 MySQL 数据库

1、选择子集

2、删除重复值

5主键定义为：用户id，商品id，保证用户行为发生时间不重复数据。

3、 缺失值处理

删除缺失值

delete from 表名 where 列名 is null;

经验证明，无缺失值

填充缺失值

select coalesce(列名，填充值) from 表名;

4、日期处理

由于 time 字段时间包括(年度)-月-日)和小时，为便于分析，将该字段分为 2个字段，一个日期列和一个时间列。

修改后的数据表是这样的:

5、删除重复数据

检查日期是否在规定范围内：2017年 11月 25日至 2017年 12月 3 日。

SELECT MAX(日期),MIN(日期)from 用户;

22017年9月11日至2017年12月3日发现数据集中的时间范围。因此，删除日期小于2017年11月25日的数据

删除44行数据

删除重复数据

经验证，无重复数据

四、构建模型

1.分析用户行为的漏斗模型 使用AARRR这里的数据主要涉及用户 ** 和购买转换环节，用户从浏览到最终购买的整个过程，包括浏览、收集、加入购物车和购买环节，9天内的指标如下：

1)uv代表独立访客人数：983人

2)pv代表点击量：8965次

3)pv/uv每个独立访客的平均浏览次数为91次/人

这表明，在2017年11月25日至2017年12月3日的9天内，每个访问者访问淘宝页面的平均数量为91次，每个人每天访问10个页面。数据显示，消费者使用淘宝的频率非常高，平均每天10次

用户行为漏斗分析：

包括用户行为pv(点击)、cart(购物车)，fav(收藏)和buy(购买)放入购物车比较点击次数5.45%，点击喜欢只占2.75%这表明用户平均浏览10个网页会产生收藏或添加到购物车中；最后，实际购买不到2.1%，说明平均浏览50个网页会产生实际购买行为。

我们猜测，这种现象可能发生的原因是用户花了很多时间在淘宝上找不到他们想要的产品，所以他们放弃了在淘宝平台上购买。

让我们从业务流程来分析用户是否真的从浏览界面中流失，转化率是多少？

从上图我们可以看到，用户平均每天点击50次才会有3.6二次收集或加入购物车动作和二次购买的转化率约为7%，进入购买的转化率约为4%。因此，从业务流程验证用户确实从浏览界面中流失，这也表明从浏览到收集和加入购物车的阶段是指标改进的关键环节。

基于以上判断，我们猜测用户从浏览界面流失有两个原因：

用户是否能从页面上找到淘宝平台推送的产品，以满足用户的需求

(1)用户想要找到的商品能否从页面上找到

衡量用户最想在淘宝平台上找到什么样的产品，最重要的指标是商品点击率。通过这个指标，我们可以最大限度地了解用户对哪些产品的需求更大，对哪些产品的需求更小。

从上图可以看出，商品类别ID淘宝4756105/3607361/415813/2355072/9826APP中点击率最高，说明用户最想在淘宝平台上找到这五种商品，在这五种类型下，各种中点击率排名前三的商品是：

( -- 4756105类商品点击前三名SELECT商品类别ID, 商品ID, COUNT(商品ID) AS 点击次数 FROM 用户 WHERE 用户行为类型= 'pv' AND 商品类目ID = 4756105 GROUP BY 商品ID ORDER BY点击次数 DESC LIMIT 3)UNION --366商品SELECT 商品类别ID, 商品ID, COUNT(商品ID) AS 点击次数 FROM用户WHERE 用户行为类型= 'pv' AND 商品类目ID = 3607361 GROUP BY 商品类别ID, 商品ID ORDER BY 点击次数 DESC LIMIT 3 )UNION --对411商品进行点击SELECT 商品类别ID,            商品ID, COUNT(商品ID) AS次数FROM用户WHERE用户行为类型= 'pv' AND 商品类目ID = 4145813 GROUP BY商品类别ID, 商品ID ORDER BY点击次数 DESC LIMIT 3 )UNION -- 235SELECT商品类别ID, 商品ID, COUNT(商品ID) AS次数FROM用户WHERE用户行为类型= 'pv' AND 商品类目ID = 2355072 GROUP BY商品类别ID, 商品ID ORDER BY点击次数 DESC LIMIT 3 )UNION --982商品点击前)SELECT商品类别ID,            商品ID, COUNT(商品ID) AS次数FROM用户WHERE用户行为类型= 'pv' AND 商品类目ID = 982926 GROUP BY商品类别ID, 商品ID ORDER BY点击次数 DESC LIMIT 3 )

从图中可以看出，4756105类商品需求量较高，前三类商品平均点击量在31以上；4145813类和235072类商品需求量相对较小，平均点击量为13.6。

(2)淘宝平台推送的产品是否满足用户需求

在分析了用户想要寻找的产品之后，需要知道淘宝平台筛选推送的产品是否满足了用户的需求。

首先从商品数量占比上分析这五类商品是否在淘宝平台上足够多，以供用户选择。

SELECT 商品类目ID,COUNT(DISTINCT 商品ID) AS 商品数量，商品数量/COUNT(DISTINCT 商品ID)FROM 用户where 商品类目ID=4756105 or 商品类目ID=3607361 or 商品类目ID=4145813 or 商品类目ID=982926 or 商品类目ID=2355072GROUP BY 商品类目ID;

   从以上结果可以看出，淘宝平台共有 ** 440种商品种类繁多，需求量最大的前五种商品占商品数量的比例14.8说明淘宝平台为这五类商品提供了足够的选择。

在选择足够多的情况下，需要分析淘宝对这五类商品的推送机制是否合理，即用户搜索某类商品后出现的商品是否满足用户的需求。

从上图可以看出，购买次数前5的类别与点击次数前5的类别不一致，表明平台推荐机制不合理。淘宝平台推送给用户的五种产品不符合用户的要求，即用户点击查看此类产品后，发现自己不想放弃加入购物车，导致转化率下降。

   2.用户行为模式在不同时间尺度下的分析

分析用户在统计范围内和一天内的购买行为，找出用户活跃的规律。

统计范围(9天)用户对这五种商品的需求

SELECT 日期,COUNT(商品类目ID)FROM 用户WHERE 用户行为类型='pv' AND 商品类目ID=4756105GROUP BY 时间ORDER BY 时间 desc;SELECT 日期，商品类别ID,cOUNT(商品ID) as 点击次数FROM 用户WHERE 用户行为类型='pv' and 商品类目ID=3607361GROUP BY 日期，商品类别IDORDER BY 日期 desc;SELECT 日期，商品类别ID,cOUNT(商品ID) as 点击次数FROM 用户WHERE 用户行为类型='pv' and 商品类目ID=4145813GROUP BY 日期，商品类别IDORDER BY 日期 desc;SELECT 日期，商品类别ID,cOUNT(商品ID) as 点击次数FROM 用户WHERE 用户行为类型='pv' and 商品类目ID=2355072GROUP BY 日期,商品类目IDORDER BY 日期 desc;SELECT 日期，商品类别ID,cOUNT(商品ID) as 点击次数FROM 用户WHERE 用户行为类型='pv' and 商品类目ID=982926GROUP BY 日期，商品类别IDORDER BY 日期 desc;

从图中可以看出，随着时间越来越接近双十二，前五类商品的总点击量在9天内显著增加；

SELECT 日期,SUM(CASE WHEN 用户行为类型 = 'pv' THEN 1 ELSE 0 END ) AS '点击次数', SUM(CASE WHEN 用户行为类型 = 'fav' THEN 1 ELSE 0 END ) AS '收藏次数', SUM(CASE WHEN 用户行为类型 = 'cart' THEN 1 ELSE 0 END) AS '加入购物车', SUM(CASE WHEN 用户行为类型 = 'buy' THEN 1 ELSE 0 END) AS '购买次数' FROM 用户 WHERE 商品类别ID=4756105 or 商品类目ID=3607361 or 商品类目ID=4145813 or 商品类目ID=982926 or 商品类目ID=2355072 group by 日期 ORDER BY 日期

9当前五类商品的总点击量显著增加 时，收购次数也显著增加，购买次数增长缓慢，具体原因需要更多的数据支持分析。

我们使用时间因素hour()判断函数。

alter table 用户 add 时间点 varchar(255);#从时间戳中5:04等时间戳中抽取时间点update 用户set 时间点=hour(时间);SELECT 时间点,SUM(CASE WHEN 用户行为类型 = 'pv' THEN 1 ELSE 0 END ) AS '点击次数', SUM(CASE WHEN 用户行为类型 = 'fav' THEN 1 ELSE 0 END ) AS '收藏次数', SUM(CASE WHEN 用户行为类型 = 'cart' THEN 1 ELSE 0 END) AS '加入购物车', SUM(CASE WHEN 用户行为类型 = 'buy' THEN 1 ELSE 0 END) AS '购买次数'FROM 用户 WHERE 商品类目ID=4756105 or 商品类目ID=3607361 or 商品类目ID=4145813 or 商品类目ID=982926 or 商品类目ID=2355072group by 时间点ORDER BY 时间点

可以看出，这五类商品的用户行为数从每日4点到5点快速降低，5点左右降到一天中的行为数最低值，6点--7点开始用户行为数快速上升，9点左右达到一天中的最高值，10点以后用户行为数又开始降落至平均水平。

综合上述分析可知，9-11点是用户点击、收藏和购买的高峰期，平台在这个时间段推送成交率将更高

因为数据集有限，只能大致分析出淘宝用户想要寻找哪几类商品。若要更细致地分析出用户想要什么样的商品，还需知道用户使用的搜索高频词，利用该项数据建立用户搜索画像，并结合商品点击数数据，建立搜索点击率指标，分析总结出点击率高的搜索高频词和点击率低的搜索高频词。从而更精确地总结出用户在淘宝平台最想要寻找什么商品。

3.不同商品种类的用户行为

统计所有商品的购买次数，同时找到购买次数和加入购物车次数最多的商品。

select 次数,COUNT(商品ID) as 商品ID数from(SELECT 商品ID, COUNT(用户ID) AS '次数' FROM 用户 WHERE 用户行为类型 = 'buy'GROUP BY 商品ID) as AGROUP BY 次数

在本次统计的数据中，只购买一次的产品有1881种，购买两次的产品有93种，本次分析的商品中用户购买的共有1984种商品，却没有出现购买用户数量非常集中的商品，购买一次的商品占到94.8%，说明商品售卖主要依靠长尾商品的累积效应，而非爆款商品的带动。

SELECT 商品ID ,count( 商品ID) as 次数 FROM 用户 WHERE 用户行为类型 = 'buy' GROUP BY 商品ID order by 次数 desc limit 20SELECT 商品ID ,count( 商品ID) as 次数 FROM 用户 WHERE 用户行为类型 = 'pv' GROUP BY 商品ID order by 次数 desc limit 20

列出销量前20位的商品，商品ID为855191/4395247/1910706的商品销量最高，均为4次，那么是否浏览次数最高的商品销量也最高呢，对商品浏览量也进行了统计： 我们看到浏览数最高的商品为3027414，没有进入销量前20，说明这些吸引用户更多注意力的商品没有很好的转化为实际销量。

五、结论与建议

本文选取淘宝APP用户行为数据共10万条，从三个不同角度对数据进行分析，现给出如下结论和建议。

1.AARRR模型提升转化率：

由于数据中没有给出每个用户第一次的登陆的时间，我们暂且把浏览行为视为用户的获取。 ，用户行为包括点击、放进购物车、收藏以及购买。由于收藏和加入购物车都为浏览和购买阶段之间确定购买意向的用户行为，且不分先后顺序，因此将其算作一个阶段。 从浏览到有购买意向只有2%的转化率，即使有一部分用户是直接购买，但也说明大多数用户以浏览页面为主而购买转化较少，此处为转化漏斗中需要改善和提高的环节。

针对这一环节改善转化率的建议有：

优化电商平台的搜索匹配度和推荐策略，主动根据用户喜好推荐相关的商品，优化商品搜索的准确度和聚合能力，对搜索结果排序优先级进行优化。 在商品详情页的展示上突出用户关注的重点信息，精简信息流的呈现方式，减少用户寻找信息的成本 优化加入购物车和收藏按键的触达，用户在滑屏时也能方便触达，增加功能使用的次数。

2.用户行为活跃高峰期推送：

用户在不同时间周期下的活跃规律 9天中的消费活动随时间越来越接近双十二而增加明显；一天内高峰期出现在9点-11点左右。

针对高峰期的建议：进行营销活动收益最高，此时使用人数最多，活动容易触达用户，营销活动的形式可以通过促销、拼团等形式进行。

3.针对不同商品采取不同营销策略：

商品售卖主要依靠长尾商品的累积效应，而非爆款商品的带动。销量最高的商品浏览数没有进入销量前20，说明这些吸引用户更多注意力的商品没有很好的转化为实际销量。

建议：针对浏览量高而销量不高的这部分商品，需要提高的是用户从点击进入商品详情页到最终购买的体验。作为商家端可以从以下几个方面提高销售额： 商品详情页的实际价格是否相比展示价格偏差过大，有的商家为了吸引用户点击在商品展示页投放的价格具有较强吸引力，但实际价格偏高，在用户心中反而引起反感 详情页的信息流展示是否合理，是否将用户最想看到的部分置于容易看到的位置，便于信息的获取 优化商品展示的形式，利用视频等方式给用户更直观的感受，提高照片的美观程度 评论区评价管理，尤其对于差评区的用户反馈进行认真对待，提高自身服务质量