用SQL分析《淘宝用户行为》

一、提出问题
本次分析试图解决如下业务问题：

1. 绘制淘宝用户从浏览到购买的转化率漏斗，确定夹点位置，提出改善转化率的建议。
2. 探索用户行为的时间规律，为改善服务提供依据。
3. 了解高销量的商品和商品类目，为营销提供指导。
4. 找出核心付费用户，统计核心付费用户的购买商品类目信息，为其提供定制化的服务。
   针对本次业务问题，适用的业务指标见下图。
   

二、理解数据
2.1 数据介绍
阿里巴巴天池：https://tianchi.aliyun.com/dataset/dataDetail?dataId=649&userId=1
本数据集(UserBehavior.csv)包含了2017年11月25日至2017年12月3日之间，有约一百万随机用户的所有行为。
数据集大小情况为：用户数量约100万（987,994），商品数量约410万（4,162,024），商品类目数量约1万（9,439），用户行为数量约为1亿条（100,150,807）。
用户行为类型共四种：点击（pv），加入购物车（cart），收藏（fav），购买（buy）。

2.2 字段含义

2.3 样本取样
本次研究选取了大约300万（2,999,900）条行为记录，其中用户数量约为3万（29,178），商品数量约为80万（805051），商品类目数量约7千（6868）。

三、清洗数据
3.1 创建表格，导入数据
用SQL创建表格userbehavior，导入淘宝用户行为数据。

3.2 重复值处理
使用联合主键（userid，itemid，timestamps），数据导入后没有重复数据。
3.3 缺失值处理
约束条件设定为NOT NULL，数据导入后没有缺失数据
3.4 一致化处理
把时间戳改为人类可读时间形式。

在原数据表增加3个新字段datentime，dates，hours，把转换好的日期时间放进去，SQL语句执行结果如下：

3.5 异常值处理
检查日期是否在规定范围内：2017年11月25日至2017年12月3日。


将不符合规定的数据删除。

总共删去1467行。

四、建构模型

1. 绘制淘宝用户从浏览到购买的转化率漏斗，确定夹点位置，提出改善转化率的建议。
   1.1 计算基本的流量指标：访问量PV，访客数UV，平均访问量PV/UV。
   
   
   可知，在分析时段内，访问量是2684339，访客数29178，平均访客量92。

1.2 在此基础上，绘制行为类型和访客数的漏斗图。


用户行为包括点击、加入购物车、收藏和购买，根据计算结果和漏斗图可知：
1） 点击行为占比90%，加入购物车行为占比6%，收藏行为占比3%，购买行为占比2%。其中，从点击到加入购物车，转化率陡降（-93.33%）。可以确认加入购物车环节是夹点位置，该环节较低的转化率不利于激活用户。
2） 四个环节的访客数占比依次是：100%，76%，40%，68%。其中，收藏转化率偏低，但购买转化率很高，购买用户占比高达68%。

2. 探索用户行为的时间规律，为改善服务提供依据。
   2.1 在分析时段内，计算各种行为的日活量。
   
   
   其中，cart-fav-buy日活量图如下：
   
   2017年11月25日、26日，2017年12月2日、3日都是周末，分别设定为周末1和周末2，计算两个周末和中间工作日的四类行为均值，计算三个时段均值的增幅：
   增幅1=（工作日-周末1）/周末1，
   增幅2=（周末2-工作日）/工作日，
   增幅3=（周末2-周末1）/周末1.
   结果如表：
   
   于是可知：
   1）第二个周末与工作日比，pv,cart,fav 三类行为平均日活量增幅都达到了30%左右，buy行为在第二个周末的增幅达到了15%左右。可知周末效应显著。
   2）第二个周末与第一个周末比，四类行为平均日活量增幅均达到了30%左右。可知周末效应尤为显著。
   3）在分析的三个时段内，buy行为平均日活量一直在增长，不像其他三类行为呈倒“U”型。

2.2 以小时为单位，计算1天内各种行为时活量。


其中，cart-fav-buy时活量图如下：

其中，buy时活量图如下：

于是可知：
1） 四类行为时活量有两个显著的增长区间：6点—10点，18点—22点。
2） 10点—17点是一个相对平缓的增长区间。
3） Pv,cart,fav,buy时活量，从晚上18点开始迅速上升，在晚上21或22点达到了一天的峰值。
4） 峰值（晚上）相对于10点—17点（白天）的时活量均值而言，pv,cart,fav时活量增幅较大，均超过了50%；buy时活量增幅较小，不到20%。

3. 了解高销量的商品和商品类目，为营销提供指导。
   3.1 了解销量最高的TOP10商品和商品类目。
   
   
   
   于是可知：
   1） TOP10商品的标识和购买次数。
   2） TOP10商品类目的标识和购买次数。

4. 找出核心付费用户，统计核心付费用户的购买商品类目信息，为其提供定制化的服务。
   4.1 找出购买次数最多的TOP10用户。
   原数据集没有成交金额之类的数据，于是用购买次数的多少来标的核心付费用户。
   
   
   于是可知：TOP10用户的标识和购买次数。
   4.2 以107932用户为例，计算该用户购买的商品类目结构。
   
   
   于是可知：107932用户的商品类目结构。

五、结论建议
5.1 绘制淘宝用户从浏览到购买的转化率漏斗，确定夹点位置，提出改善转化率的建议。
5.1.1 用户行为转化分析
结论是，加入购物车环节是夹点位置。
激活用户的关键是减少通往“啊哈时刻”的摩擦。在点击加入购物车之前，用户会花大量的时间去搜寻并排序商品，对比相似商品的若干参数，查看店铺好评率，查看宝贝评价，与客服沟通等等，其中每一个步骤都可能造成不必要的摩擦。
优化建议如下：
1） 学习中关村在线的产品对比功能，可以一次性对比中意商品的不同维度上的参数，而不必花大量的时间在相似商品间不断地折返查看。
2） 优化算法，提高“你可能还喜欢”的精准度，投用户之所好，减少用户从点击到加入购物车的时间。

5.1.2 独立访客转化分析
结论是，收藏转化率偏低，但购买转化率很高：平均10个独立访客中大约有7个会购买商品。这对淘宝平台来说意味着比较高的收益。
为了进一步提高独立访客的购买转化率，提高每位用户带来的收益，优化建议如下：
1） 对注册会员进行分组，为高阶用户提供更多的权益或折扣，给予更醒目的荣誉标识，刺激高阶用户的购买欲望和购买行为。
2） 对于低阶用户，可以运用“翻转漏斗”的方法，在某个时限内给予用户特定的权益或折扣，让低阶用户得以体验到高质量的“啊哈体验”，进而刺激其购买欲望和购买行为。

5.2 探索用户行为的时间规律，为改善服务提供依据。
5.2.1 日活量规律分析
第二个周末（2017年12月2日、3日）的“周末效应”尤为显著。可能是“双11”之后新一轮促销活动产生的效应。
点击、加入购物、收藏日活量呈倒“U”型，购买日活量则呈线性增长趋势。假设存在促销活动的话，这对促销团队来说是利好的信息。根据购买日活量增长趋势看，未来的购买行为会进一步增多。
建议：
1） 在周末，增加客服支持力度，保证业务量增大的条件下，服务质量不下降。
2） 在周末，技术团队要保证服务器功能的稳定输出，尽量减少流量增大时的页面访问延时，避免页面崩溃，保证用户的购物体验。

5.2.2 时活量规律分析
查看四类行为的时活量，时间规律是：0点—6点，是一个明显的低谷，其时人们在休息；6点—10点，是第一个显著增长区间，其时人们开始新一天的工作和生活；10点—18点，日活量大体平稳，临近18点时有所下降；18点—22点，是第二个显著增长区间，且起点较高，大约在21点或22点达到了一天流量的峰值。
可以说，18点—22点是电商的黄金时段，是获客、激活、留存、变现和推荐的关键时期。
在这个黄金时段，购买日活量的增幅相对小于点击、加入购物车和收藏的增幅，这意味着巨大的变现潜力。
建议：
1） 把营销团队和技术团队的服务精力集中在18点—22点这个黄金时段。
2） 18点—22点这个黄金时段是爱人、亲朋和好友相聚生活的时段，把促销活动重点安排在这个时段。在促销策划上，可以以“亲情送”、“爱情送”等形式为用户智能推荐相关商品，提高商品连带率，同时提供相应的折扣，在完整的购买流程中设计一些交流感情和表达感情的环节，以刺激用户的购买欲望和购买行为。
3） 6点—10点也是一个显著的流量增长区间，在这个时段活跃的社会阶层很可能是自雇者（因为一般的受雇者要忙着打卡上班）或弹性工作的受雇者。假如此假设被验证的话，可以进一步对自雇者或弹性工作的受雇者进行用户画像，根据其特点和需求，针对性地展开促销活动，或优化算法，智能推荐精准度高的商品给特定用户。

5.3 了解高销量的商品和商品类目，为营销提供指导。
了解了TOP10商品和商品类目，可以：
1） 可以完善进货-库存管理，对销量较高的商品多进货，并准备相应的库存，以应对用户需求。
2） 为进一步提高TOP10商品的销量，可以从用户好评中进行提词，不断优化广告语，提高商品的知名度和好评度，刺激用户购买。

5.4 找出核心付费用户，统计核心付费用户的购买商品类目信息，为其提供定制化的服务。
知晓了购买次数TOP10用户，可以：
1）把团队服务精力集中在TOP10用户身上，不断地试验各种增长黑客的方法，提高单位用户的收益。
2）可以对TOP10用户推荐其所需要的相关商品，提供服务的个性化程度，提高用户粘性。