我的项目背景：随着挪动互联网多年的疾速倒退，挪动互联网已进入下半场 ，不再依附用户红利来经营，倒退业务，辞别毛糙的/高老本企业倒退的形式，开始转而精细化治理，联合市场、渠道、用户行为等数据分析，对用户开展有针对性的经营流动，提供个性化、差异化的经营策略，以实现经营目标行为。本文利用SQL对淘宝用户行为数据进行剖析，通过用户行为剖析业务问题，提供针对性的经营策略。

剖析步骤：

  1. 提出问题
  2. 数据了解
  3. 数据荡涤
  4. 构建模型
  5. 总结与倡议

一、提出问题

1. 本次剖析的业务问题及实用指标
本次剖析的目标是想通过对淘宝用户行为数据分析，为以下问题提供解释和改良倡议：

  1. 用户从浏览到最终购买的整个过程的散失状况，确定夹点地位，提出改善转化率的意见。
  2. 在钻研的时间段里找出用户最沉闷的日期以及每天沉闷的时间段，理解用户的行为工夫模式。
  3. 什么产品以及产品类目标购买率最高，找出最受欢迎的产品，优化产品销售。
  4. 哪些用户购买次数最多，找出最外围的付费用户群，并且统计出这些用户购买的产品以及类目，针对这些用户的购买偏好推送个性化的产品销售计划。

针对下面的业务问题，上面是实用的业务指标：

2. 基于AARRR漏斗模型剖析用户行为
本我的项目通过罕用的电商数据分析业务指标，采纳AARRR漏斗模型拆解用户进入APP后的每一步行为。AARRR模型是依据用户应用产品全流程的不同阶段进行划分的，针对每一环节的用户散失状况剖析出不同环节的优化优先级，次要通过以下个各阶段来进行剖析：

二、数据了解

本我的项目数据来源于阿里云天池，可登陆阿里云天池下载数据，地址如下：User Behavior Data from Taobao for Recommendation
本数据集蕴含了2017年11月25日至2017年12月3日之间，有行为的约一百万随机用户的所有行为（行为包含点击、购买、加购、喜爱）。数据集的组织模式和MovieLens-20M相似，即数据集的每一行示意一条用户行为，由用户ID、商品ID、商品类目ID、行为类型和工夫戳组成，并以逗号分隔。对于数据集中每一列的详细描述如下：

留神到，用户行为类型共有四种，它们别离是：

对于数据集大小的一些阐明如下：

三、数据荡涤

1. 察看记录
原数据集数据记录达到1亿条，数据量宏大，为了不便剖析与效率，本我的项目将选取了从500万行至800万的300万条记录进行剖析。
2. 统一化解决
原数据工夫戳应用的是epoch&unix timestamp格局，须要转换为规范可读的日期工夫模式。在原数据表减少3个新字段datetime、dates、hours，把转换好的日期工夫放进去。

ALTER TABLE userbehavior ADD COLUMN datetime TIMESTAMP(0) NULL;UPDATE userbehavior SET datetime=FROM_UNIXTIME(timestamps);ALTER TABLE userbehavior ADD COLUMN date CHAR(10) NULL;UPDATE userbehavior SET date=SUBSTRING(datetime FROM 1 FOR 10);ALTER TABLE userbehavior ADD COLUMN hour CHAR(2) NULL;UPDATE userbehavior SET hour=SUBSTRING(datetime FROM 12 FOR 2);


3. 异样值解决
查看日期是否在规定范畴内（2017年11月25日至2017年12月3日），将不符合规定的数据删除。

SELECT MAX(timestamps),       MIN(timestamps),       MAX(datetime),       MIN(datetime)FROM userbehavior;

DELETE FROM userbehaviorWHERE datetime<'2017-11-25 00:00:00' OR datetime>='2017-12-04 00:00:00';

一共删除了1689行数据，再次验证日期工夫的准确性，上面后果满足要求：

四、构建模型

1. 流量与用户行为转化剖析
解决问题：用户从浏览到最终购买的整个过程的散失状况，确定夹点地位，提出改善转化率的意见。

1）访客数UV、访问量PV、均匀访问量PV/UV：

SELECT    COUNT(DISTINCT user_id) AS 'UV',     (SELECT COUNT(*) FROM userbehavior WHERE behavior='pv') AS 'PV',    (SELECT COUNT(*) FROM userbehavior WHERE behavior='pv')/(COUNT(DISTINCT user_id)) AS 'PV/UV'FROM userbehavior;

2）跳失率(只有点击行为的用户/总用户数)：

SELECT COUNT(DISTINCT user_id)FROM userbehaviorWHERE user_id NOT IN(SELECT DISTINCT user_id FROM userbehavior WHERE behavior = 'fav')    AND user_id NOT IN(SELECT DISTINCT user_id FROM userbehavior WHERE behavior = 'cart')    AND user_id NOT IN(SELECT DISTINCT user_id FROM userbehavior WHERE behavior = 'buy');
结果显示只有点击行为却没有珍藏、退出购物车以及购买行为的用户数是1628，除以总用户数29233，则跳失率为为5.57%。

3）用户总行为漏斗：

SELECT behavior,COUNT(*)FROM userbehaviorGROUP BY behavior;


因为珍藏和退出购物车都为浏览和购买阶段之间确定购买动向的用户行为，且不分先后顺序，因而将其算作同一阶段。能够看到从浏览到有购买动向只有9.50%的转化率，当然也有局部用户是间接购买而未通过珍藏和退出购物车，然而这仍阐明大多数用户浏览页面次数较多，而应用退出购物车和珍藏性能较少。另外，购买次数占退出购物车和珍藏性能的23.53%左右，阐明从浏览到珍藏和退出购物车的阶段是指标晋升的重点环节。

4）独立访客行为漏斗：

SELECT behavior,        COUNT(DISTINCT user_id) AS DIS_userFROM userbehaviorGROUP BY behavior;


上图展现的是每一步用户行为的独立访客数的散布状况，能够看出应用APP的用户中PUR约为68.2%，用户付费成交转化率相当高，阐明用户的购买欲望还是挺大的。

2. 用户行为模式分析
解决问题：在钻研的时间段里找出用户最沉闷的日期以及每天沉闷的时间段，理解用户的行为工夫模式。

1)每日沉闷点击量：

SELECT date,COUNT(*) as pvFROM userbehaviorWHERE behavior='pv'GROUP BY dateORDER BY date;


从上图能够看出11月25日-12月1日保持稳定的程度，12/2开始呈现较为显著的增长，点击量陡增，增长率约为26.4%。揣测是上班族因工作逛淘宝的工夫少，而周末(12月2日-12月3日)有短缺的精力和有较多闲暇工夫拜访淘宝。因而素日经营能够将流动集中在周末进行。

2)每时段的沉闷点击量：

SELECT `hour`,COUNT(*)/9FROM userbehavior WHERE behavior = 'pv' GROUP BY `hour` ORDER BY `hour`;


在数据集察看的9天里，从18点开始点击量稳步回升，到21点达到高峰，22点稍有回落，到23点显著降落，阐明大部分用户会在早晨18点到22点时段频繁点击浏览网页，合乎大部分人的作息时间。

3. 产品销售剖析
解决问题1：什么产品以及产品类目标购买率最高，找出最受欢迎的产品，优化产品销售。
解决问题2：哪些用户购买次数最多，找出最外围的付费用户群，并且统计出这些用户购买的产品以及类目，针对这些用户的购买偏好推送个性化的产品销售计划。

1）浏览次数、珍藏次数、退出购物车次数以及购买次数最多的商品：

SELECT    item_id,    count(user_id) AS times_pv FROM    userbehavior WHERE    behavior='pv' GROUP BY    item_id ORDER BY    times_pv DESC;




在销量榜单中并没有看到浏览量第一第二的商品，阐明这些吸引用户更多注意力的商品并没有很好的转化为理论销量，仅更多的加入收藏中（浏览量前排的商品均能在珍藏量前列中，阐明浏览量与珍藏的关系更为间接）。

2）产品销售排名：

-- 计算不同购买次数下的产品种类数SELECT    a.buy_num AS buy_count,    COUNT(a.item_id) AS item_num FROM    ( SELECT item_id, COUNT(user_id) AS buy_num FROM userbehavior WHERE behavior='buy' GROUP BY item_id ) AS a GROUP BY    a.buy_num ORDER BY    item_num DESC;

从上图能够看出只被购买一次的产品有38248种，被购买两次的产品有5146种，本次剖析的产品（item_id）有45931种，只被购买一次的产品占到83.3%，意味着并没有销售十分集中的产品。为了看清楚这一部分，咱们来看看产品种类的累计销售状况。

3）产品种类的累计销售状况：

从上图能够看出83.3%的产品奉献了64.5%的销售量，不合乎传统零售业的二八法令，阐明电商靠长尾实践累计销售，而不是制作爆款商品带动销量。

4）产品类目标累计销售状况：

-- 计算不同购买次数下的商品类目数量SELECT    a.cat_buytimes,    COUNT(category_id) AS cat_type_count FROM  -- 每种商品类目标购买次数    ( SELECT category_id,COUNT(user_id) AS cat_buytimes FROM UserBehavior WHERE behavior='buy' GROUP BY category_id ) AS aGROUP BY    a.cat_buytimesORDER BY    a.cat_buytimes;


从上表能够更分明的看出27.7%的产品类目奉献了1.6%的销售量，69.6%的产品类目奉献了11.5%的销售量，不合乎传统零售业的二八法令，同样能够阐明其依附长尾实践累计销售。

5）复购率：

-- 统计不同购买次数下的用户数SELECT    a.buy_times,    COUNT(user_id) AS '人数' FROM  -- 有购买行为的用户各自的购买次数    ( SELECT user_id,COUNT(behavior) AS buy_times FROM userbehavior WHERE behavior='buy' GROUP BY user_id ) AS aGROUP BY    a.buy_timesORDER BY  a.buy_times;

从上图能够得悉整体复购率为(59329-6787)/59329=88.6%，即有购买行为的用户中大略有88.6%的用户会反复购买。下面是复购状况的可视图，能够看出大部分买家还是只购买一次。

6）找出复购率最高的用户以及他们购买的产品：

SELECT  user_id,    COUNT(behavior) AS buy_timesFROM  userbehaviorWHERE  behavior='buy'GROUP BY  user_idORDER BY  buy_times DESC;

从下面SQL语句的执行后果能够看到用户user_id=337305购买次数最多，高达93次。上面以复购率最高的用户user_id=337305为例钻研阐明。
SELECT    category_id,    COUNT(*) FROM    UserBehavior WHERE    behavior='buy'     AND user_id=337305 GROUP BY    category_id ORDER BY    COUNT(*) DESC;

能够看出复购率最高用户user_id=337305购买的商品类目次要集中在下面表格中的前3大类，能够参考这些商品类目标id来确定产品种类。

这种针对某些用户做的剖析能够更好地理解和发现价值用户，如果数据集提供产品价格信息，就能够通过下面的数据分析很容易地找到高价值用户。理解高价值用户的购买行为，比方购买工夫、购买产品以及品类等等以推出有针对性的产品举荐，通过个性化的举荐进步产品销售状况。

五.总结与倡议

本次剖析利用MySQL语句执行，数据集大概有300万条淘宝用户行为数据，针对用户行为问题咱们应用AARRR漏斗模型进行业务剖析，联合上述剖析的业务指标，上面提出批改倡议：
1. 获取客户(Acquisition)：关键点是语言市场匹配和渠道产品匹配。
每天晚上16点到22点是用户频繁拜访的工夫，也是获取更多潜在客户的黄金时间，平台发展流动获取客户应首选这个时间段进行。
淘宝是电商第一平台，用户基数大，能够利用用户转发的形式获取新客户，比方在晚间时段做促销流动，邀请敌人拼团享受优惠来减少用户数，适宜利用口碑渠道获取新客户。也能够进行小游戏邀请、KOL推广、热门社交或小视频平台单干推广、淘宝app卖家推送等。

2. 激活用户(Activation)：摸清楚产品的“啊哈”时刻，用户从浏览到最终购买整个过程的散失状况，确定夹点地位，提出改善转化率的倡议。
用户行为包含点击、退出购物车、珍藏以及购买，点击量占总行为的89.5%，而退出购物车和珍藏只占6%，最初理论购买跌至2%，夹点地位在珍藏和退出购物车环节上，可能呈现的起因是用户花了大量工夫寻找适合的产品。依据数据分析后果改善转化率的倡议有：
（1）优化电商平台的筛选性能，减少关键词的准确率，让用户能够更容易找到适合产品；
（2）给客户提供同类产品比拟的性能，让用户不须要屡次返回搜寻后果重复查看，便于用户确定心怡产品；
（3）精简下单步骤，提供一键下单服务，比方只蕴含点击-购买-领取三个环节，缩短购买流程，进步用户体验。

3. 第三个环节进步留存（Retention）：让用户养成应用习惯。
让用户放弃应用淘宝电商平台的习惯是进步留存率的要害，可采纳的计划可能有：
（1）依照应用频率和购买次数积攒积分，每天上线点击量达到某个数值即可主动支付积分，到月末换取购物礼券；
（2）对于年购买次数和金额达到规定量的客户推出VIP服务，享受全场不限时9.5折优惠，购买次数同比回升之后相应福利也回升，利用这种办法进步高价值用户的留存率和对平台的忠诚度。

4. 第四个环节增加收入（Revenue）：进步成交转化率、复购率及产品和类目标购买率状况。
独立用户从点击到最初购买的转化率约为68.2%，用户购买诚意还是很足的，所以通过正当优化电商平台的筛选性能能够进步最终购买的转化率。
有购买行为的用户中，大略有88.6%的用户会反复购买。在独立用户中，最高的复购次数是93次，咱们能够通过复购率、购买金额（本次数据集没有提供）等来确定价值用户，通过剖析找出价值用户的购买偏好，产品和类目等，给价值用户制订个性化的产品举荐，从而进步用户体验和电商平台销售状况。
83.3%的产品奉献了64.5%的销售量，27.7%的产品类目奉献了1.6%的销售量，69.6%的产品类目奉献了11.5%的销售量，不合乎传统零售业的二八法令，电商靠长尾实践累计销售。
以上数据显示淘宝平台的最大劣势是产品种类和类目丰盛，用户可抉择的范畴十分广，吸引不同类型的客户群，所以应该持续放弃这个劣势。可能适合的进步计划有：
（1）内容营销：应用“没有找不到的产品，只有想不到的产品”来宣传平台购物品种丰盛，让用户造成“只有买货色上淘宝肯定有”的思维习惯；
（2）针对后面确定的价值用户提供个性化产品举荐，比方最关怀的产品类目和品种，上新之后定时推送给用户；
（3）针对复购率，能够推出3个月内复购优惠活动，让客户放弃购买频率。

5. 第五个环节举荐（Refer）:用户举荐给其他人，关注转发率、转化率和K因子。
针对淘宝平台，让用户举荐给其他人的计划有：
（1）产品在购买的时候提供拼团服务，让用户被动举荐给其他人；
（2）每当推出新性能，比方后面提到的一键下单，让体验过的用户转发和分享支付优惠券，疾速实现新性能推广；
（3）当用户应用优惠券购物或者通过某种行为积分购物之后提供朋友圈打卡性能，分享给好友，实现流传性能。
在履行以上计划之后须要关注转发率、转化率、通过用户分享链接点击购买的用户比例以及K因子来检测提出计划的有效性。