MySQL项目2 - 淘宝用户分析

来源：淘宝用户分析

目的：通过实战案例，掌握电商数据分析逻辑和对应的 SQL 语句实例应用。

从数据到信息的这个过程，就是数据分析，目的是解决我们现实中的某个问题或者满足现实中的某个需求。数据本身并没有什么价值，有价值的是我们从数据中提取出来的信息。

• 四大思维模式
  • 【对照】单独看一个数据是不会有感觉的，必需跟另一个数据做对比才会有感觉
  • 【拆分】拆分后的结果，相对于拆分前会清晰许多，便于分析，找细节
  • 【降维】当数据维度太多的时候不可能每个维度都拿来分析，有一些有关联的指标，从中筛选出代表的维度即可
  • 当前的维度不能很好地解释问题时，就需要在数据中间做运算，增加指标

【增维】和【降维】是对应的，有降必有增。必须对数据的意义有充分了解后，为了方便进行分析而有目的地对数据进行转换运算。

数据集：UserBehavior是阿里巴巴提供的一个淘宝用户行为数据集，本数据集包含了2017年11月25日至2017年12月3日之间，有行为的约一百万随机用户的所有行为（行为包括点击、购买、加购、喜欢），本案例选取了其中20万条数据进行分析，其中包含 5 个数据字段：

列名	说明
用户ID	INT，序列化后的用户ID
商品ID	INT，序列化后商品ID
商品类目ID	INT，序列化后的商品所属类目ID
行为类型	CHAR，枚举类型，包括 'pv', 'buy', 'cart', 'fav'
时间戳	行为发生的时间戳

---

电商总体运营指标（主要分为8个类指标）

来源：电商数据分析指标体系

总体运营指标：从流量、订单、总体销售业绩、整体指标进行把控，起码对运营的电商平台有个大致了解，到底运营的怎么样，是亏是赚。

1. 网站流量指标：即对访问你网站的访客进行分析，基于这些数据可以对网页进行改进，以及对访客的行为进行分析等
   

2. 销售转化指标：分析从下单到支付整个过程的数据，帮助你提升商品转化率。也可以对一些频繁异常的数据展开分析。
   

3. 客户价值指标：这里主要就是分析客户的价值，可以建立 RFM 价值模型，找出那些有价值的客户，精准营销等
   

4. 商品类指标：主要分析商品的种类，那些商品卖得好，库存情况，以及可以建立关联模型，分析那些商品同时销售的几率比较高，而进行捆绑销售，有点像啤酒喝尿布的故事
   

5. 市场营销活动指标：主要监控某次活动给电商网站带来的效果，以及监控广告的投放指标
   

6. 风控类指标：分析卖家评论，以及投诉情况，发现问题，改正问题
   

7. 市场竞争指标：主要分析市场份额以及网站排名，进一步进行调整
   

分析思路与问题提出

针对以上分析思路，提出以下几个问题：

1. 页面流量质量如何？是否有开发新客户的潜力？可以通过哪些方式吸引新客户？

2. 访客的转化率如何？是否存在需要进一步优化提高客户留存率？若需要优化，哪些方面需要作出改善？

3. 用户有哪些行为习惯或消费偏好值得注意？topN 用户需要如何维护他们的忠诚度？

数据清洗

1. 选择子集
   选取数据集中20万条数据，进行分析。

2. 列名重命名
   原数据集中没有列名，在创建表时，可先设置好列名，再进行导入。

3. 删除重复值
   创建表格时，设置 customerid, goodsid, category 为主键。

4. 缺失值处理
   在创建表格，设置了所有字段都是 “IS NOT NULL”,所以导入的数据集中没有缺失值。

5. 一致化处理
   原数据中时间戳的格式 epoch & unix timestamp 格式表达，需要转换格式。

6. 异常值处理
   数据集中的数据是2017年11月25日至2017年12月3日，检查是否存在不在这一时间范围内的数据。

• 建议先用 SELECT ... FROM ... 语句查找需要删除的数据，可以避免因为格式不对而误删掉所有数据
  • 经检验，发现数据子集中存在不符合时间范围的数据，需要删除这部分数据
• 删除数据的 SQL 语句运行后，利用 MIN()/MAX() 函数再次验证是否有异常
  • 发现目前所有数据符合时间范围

模型构建及可视化

根据电商分析指标，对数据作出以下分析：

1. 流量指标

1.1 人均页面访问量 PV/UV

1.2 跳出率

跳出率 = 仅点击页面用户数/总用户数UV

结论：从用户行为中找出点击页面却没有收藏、加入购物车以及购买行为的用户为132，得跳出率为6.69%，说明平台对用户的吸引力不错，平台的用户流量质量较高。

2. 用户行为分析

2.1 用户行为整体情况分析

结论：对用户所有行为进行汇总分析，从页面点击、收藏、加入购物车，最后到购买整个流程中，最后进行购买的用户数仅约占总体的2.2%，而点击行为约占总体的90%。说明平台有产品堆砌展示的问题，使得用户花费极多的时间在寻找和筛选商品上，因此需要优化平台产品推荐功能，把握个性化需求，更为精准化地推荐符合用户偏好的产品。

2.2 独立访客转化率

结论：对独立访客的行为进行分析，从一开始的点击页面到最终购买的转化率约为69.6%，说明独立ip用户购买欲望充足，可以通过适当的销售活动进一步引导用户购买。

3. 用户行为模式分析

3.1 每日用户点击量

结论：从11月25日至12月3日的 pv 数据显示，12月2日用户点击量激增，推测12月2日平台进行了营销活动。进而对营销活动的效果进行简单的监测分析，如下：

结论：从每日客户点击到购买的比率来看，虽然在12月2日和12月3日的点击量较以往有明显的上升，但最终购买量上升微弱，成交率不升反降。其中原因可能有以下几种：
a. 本次的营销活动方式、渠道、营销的产品等对用户的吸引力不够；
b. 本次营销活动针对的用户群体范围不广，且平台本身对用户个性化需求的抱把握不够。需要对可能存在的各影响因素进行监测排查，对营销活动方案提出优化建议。

3.2 各时段用户点击量统计

结论：根据统计数据显示，用户点击量在晚上21点至22点这个时段表现为峰值。且很明显，用户在19点至23点的活跃度最高。以各时段用户点击量为主轴，将各时段购买量与之对照分析如下：

结论：购买量的波峰也集中在19点至23点。因此，平台可以在这个时间段进行营销活动或可提高用户的点击量，进而也提高这个时段成交量。另外在10-11，13-14，16-17这三个时段的购买量也出现峰值。因此，这些时段用户的购买意愿更高，可以在这些时段通过一定的营销活动促成更多成交量。

4. 商品指标

4.1 不同商品的购买情况

结论：从商品购买次数和商品种类统计分析来看，只购买一次的商品种类最多。在用户购买的商品种类共4007种，用户复购的商品种类约占总体的6.7%。说明存在较强竞争性的产品很少。购买次数不少于5次的商品只有商品编号为4157341和1542908的商品比较受欢迎。

4.2 不同商品的购买情况

结论：在1312个不同类型的商品中，用户会重复购买的商品类型比例为（1312-678）/1312≈48.3%，其中用户购买次数超过60次、受用户欢迎的商品类型有以下四个，其中商品类型为1464116的复购次数最多为71次，最受用户欢迎。

5. 用户价值

5.1 高价值用户-忠诚度

结论：从不同购买次数对应的用户数量统计来看,有购买行为的独立访客量为1369，则在平台上会多次购买的用户占比（1369-459）/1369≈66.5%。而在平台上有多次购买行为的用户中customerid=1008380的用户，其在平台上购买次数最多为57次，其次是customerid为1003983的43次和1003901的31次。

结论：平台中购买次数越多的用户，其忠诚度越高，这些用户的行为也更具有产品营销参考价值。对度忠诚度排前三的用户行为进行分析，观察他们的购物偏好：

结论：在这三个用户中最受欢迎的商品是goodsid=1347509，最受欢迎的商品类型是category=4700878。因此，平台可以给这类忠诚度最高的用户推荐相应的产品。

结论及建议

通过上文的分析，结合 AARR R漏斗模型作出以下总结及建议：

Step1. 获取客户

• 现象

  • 平台用户跳出率为6.69%的数据就可看出，淘宝本身的用户粘性不错，平台的用户流量质量较高

  • 从2017年12月2日和3日 pv 陡增的现象可见，平台的营销活动能有效吸引用户眼球

  • 根据统计数据显示，用户点击量在晚上21点至22点这个时段表现为峰值。且很明显，用户在19点至23点的活跃度最高

• 结论及建议

  • 淘宝作为国内第一大电商平台，用户基数大。商家在19-23点进行各类营销活动的效果更好。商家通过新会员领取优惠券、像美团拼团优惠等活动可以获取更多新用户

Step2. 激活客户

• 现象

  • 对用户行为进行整体分析时，从页面点击、收藏、加入购物车，虽然页面点击率高，但最后购买的用户数仅约占2.2%

  • 虽然12月2至3日点击量或因营销活动陡增，但进一步分析发现，从每日客户点击到购买的比率来看，虽然在12月2日和12月3日的点击量较以往有明显的上升，但最终购买量上升微弱，成交率不升反降

• 结论及建议

  • 平台有产品堆砌展示的问题，使得用户花费极多的时间在寻找和筛选商品上，因此需要优化平台产品推荐功能，把握个性化需求。同类产品的推荐数量可以减少，缩小同类商品选择范围，减少购买决策的时间

  • 根据用户画像，调整推广渠道，使得产品和用户更加匹配

Step3. 留存用户

目前淘宝平台本身用户粘性较高，有像每日打卡领淘金币等提高用户留存的方式。对于很多淘宝商家也通过建立老客户群群内打卡满七天领优惠券、群内新品发布意见调查领周边、在本店购满十件免单一件等方式来留存客户，对于平台商户来说，这些体验良好的vip用户服务，提高了客户粘性，进而促成好口碑，形成良性循环，也间接提高了整个平台的用户留存率。

Step4. 增加收入

• 现象

  • 对独立访客的行为进行分析，从一开始的点击页面到最终购买的转化率约为69.6%

  • 从不同购买次数对应的用户数量统计来看,在平台上有多次购买行为的用户占比约66.5%，其在平台上购买次数最多为57次，

  • 在用户购买的商品种类共4007种，用户复购的商品种类约占总体的6.7%； 在1312个不同类型的商品中，用户会重复购买的商品类型比例为48.3%，其中复购次数最多为71次

• 结论及建议

  • 淘宝平台的商品类目众多，在用户心中早已形成“淘宝啥都有的“心理暗示；2.虽然独立访客的转化率为69.6%，但商品的复购率低。构建用户画像，针对高价值的用户，对于他们购买频率高的产品进行复购优惠活动，以及具有互补性等有关产品的产品页面、优惠活动推送

Step5. 推荐

目前淘宝自身通过“组队叠猫猫”、”瓜分双十一红包“等方式留存用户。此外，也可以通过像京东平台邀请朋友砍价、分享领红包领和优惠券等方式，让用户主动分享和推荐，提高商品的曝光度。