随着网络的不断发展，网络购物成为一种时尚。而作为消费者，通常只会去关注淘宝里面的商品内容和是否有活动等，而从来都不会去关心淘宝内部是如何运作，或这些数据是怎样的一种存在。为了满足作为好奇宝宝的我，心血来潮，想探究究竟。终于在'天池'这个平台上找到了由阿里巴巴发布的淘宝用户行为数据，以此分析淘宝数据。该数据集的时间节点为：2014年11月22日到2014年12月18日。
分析步骤
利用SQL对淘宝用户行为进行数据分析,在MySQL关系型数据库管理系统中进行。分析步骤如下：
- 提出问题
- 理解问题
- 数据清洗
- 构建模型
- 数据可视化
问题的提出
本项目基于淘宝用户行为数据，探索淘宝用户的行为模式，寻找高购买率背后用户行为特征，具体指标包括：日PV和日UV分析，支付率分析，复购行为分析，漏斗转化率分析和用户价值RFM分析。
本次分析的目的是想通过对淘宝用户行为数据分析，为以下问题提供解释和改进建议：
1.分析用户常见电商分析指标，确定各个环节的流失率，找到需要改进的环节
2.研究用户在不同时间尺度下的行为规律，找到用户在不同时间周期下的活跃规律
3.找到用户对不同种类商品的偏好，找到针对不同商品的营销策略
4.找出最具价值的核心付费用户群，对这部分用户的行为进行分析
为了分析这些问题，我们使用以下两种模型进行分析
本次分析的目的是想通过对淘宝用户行为的数据分析，为以下问题提供解释和改进建议：
- 分析用户从浏览到购买整个过程中，常见的电商分析指标，确定各个环节的流失率，提出改善转化率的意见
- 在研究的时间段里找出用户最活跃的日期以及每天活跃时间段，了解用户的行为时间模式
- 找到用户对不同种类商品的偏好，找到针对不同商品的营销策略(什么产品以及产品类目的购买率最高，找出最受欢迎的产品，优化产品销售)
- 找出最具有价值的核心付费用户群，并且统计出这些用户购买的产品以及类目，针对这些用户的购买偏好推送个性化的产品销售方案
为分析这些问题，我们常使用两种模型分析用户行为：(AARRR, RFM模型)：
- AARRR漏斗模型分析用户行为
该漏斗模型是将用户进入app后的每一步行为进行拆解。它是根据用户使用产品全流程的不同阶段进行划分，针对每一环节的用户流失情况分析出不同环节的优化优先级：
- RFM模型找出有价值的用户
由于不同的用户对公司带来的收益差别很大，而且根据二八定律20%的做有价值用户能带来80%的收益，因此需要对用户进行价值评价，找到最优价值的用户群，并针对这部分用户进行差异化营销
R-Recency(最近一次购买时间)
R指用户上一次消费时间，上一次购物时间距离现在最近的客户，该客户群体通常在近期响应营销活动的可能性也就越大，而对于很久没有购物行为也意味着放弃了使用app等,重新唤起用户也需要更多的成本
F-Frequency(消费频率)
F指用户在某段时间内的购物次数，消费频率越高意味着这部分用户对产品的满意度最高，用户粘性最好，忠诚度也最高。
M-Money(消费金额)
M指用户在某段时间内的购物金额，这也是为公司带来价值的最直接体现，而消费金额较高的用户在用户总体中人数较少，却能创造出更多价值，是需要重点争取的对象。
这三个维度互相关联，反映了每个用户的现在价值和潜在价值，将每个维度分成5个区间进行评分，通过计算分数找到最有价值的用户，并对用户进行分类，可以有针对性的不同类型用户采用不同的营销策略。
理解数据
数据来源
本次数据是基于阿里巴巴M-Commerce平台上的真实用户 - 商品行为数据。同时，它提供移动时代的典型位置信息。参与者需要构建面向移动商务的商品推荐模型。他们还希望深入了解数据背后的含义，并在适当的时间和地点为移动用户推荐合适的商品。
天池数据集：https://tianchi.aliyun.com/dataset/dataDetail?dataId=46
本数据集(UserBehavior.csv)包含了2014年11月22日至2017年12月18日之间，有行为的约一百万随机用户的所有行为（行为包括点击、收藏、加购物车、支付）。数据集的组织形式和MovieLens-20M类似，即数据集的每一行表示一条用户行为，由用户ID、商品ID、商品类目ID、行为类型和时间戳组成，并以逗号分隔。关于数据集中每一列的详细描述如下：
理解商品数据集的字段信息：
item_id: 用户id
item_geohash: 商品id
item_category: 品类id（商品所属品类）
用户数据集：
user_id: 用户id
item_id: 商品id
behavior_type: 用户行为类型（分别用1、2、3、4表示点击、收藏、加购物车、支付四种状态）
user_geohash: 用户地理位置
item_category: 品类id（商品所属的品类）
time: 用户行为发生时间
导入数据
在MySQL中创建user表，使用Navicat导入数据
create table user(
用户id int not null ,
商品id varchar(255),
用户行为 enum('点击','收藏','加购物车','支付'),
用户地理位置 varchar(255),
商品品类 varchar(255),
时间 varchar(255))engine=innodb default charset=UTF8;
数据预处理与数据清洗
通过navicat将数据集导入MySQL库。共有12256906条数据
缺失值处理：
通过观察发现用户地理位置多为空值，经统计共有8334824条，为防止对结果造成误导，故该维度特征不做分析。
用户发生行为时间数据一致化处理如上图
由于 time 字段的时间包含（年-月-日）和小时，为了方便分析，将该字段分成 2 个字段，一个日期列和一个小时列。如上表所示。
构建模型和可视化分析
用户总的浏览量(PV)
PV(访问量)：即Page View, 具体是指网站的是页面浏览量或者点击量，页面被刷新一次就计算一次。
UV(独立访客)：即Unique Visitor,访问您网站的一台电脑客户端为一个访客。
日均访问量
SELECT 日期, COUNT(`用户行为`) AS '日均访问量' FROM USER WHERE `用户行为` = '点击' GROUP BY 日期 ORDER BY 日期;
由上图可知，在12月12日当天，访问量急剧增加，有明显的峰值，这个与双十二活动有关。因此，在活动前几天或者当天可以加大商品的投放量。
用户行为分析
用户总是(UV)
总的用户在四个行为下的记录情况
使用查询语句分别输出用户点击，收藏，加入购物车以及支付的用户人数
SELECT COUNT(DISTINCT `用户id`) AS '点击用户数量' FROM user WHERE `用户行为` = '点击'; SELECT COUNT(DISTINCT `用户id`) AS '收藏用户数量' FROM user WHERE `用户行为` = '收藏'; SELECT COUNT(DISTINCT `用户id`) AS '加购物车用户数量' FROM user WHERE `用户行为` = '加购物车'; SELECT COUNT(DISTINCT `用户id`) AS '支付用户数量' FROM user WHERE `用户行为` = '支付';
结果为：
单个用户行为情况
CREATE VIEW 单个用户行为 AS SELECT `用户id`, count(`用户行为`), SUM(CASE WHEN `用户行为`='点击' THEN 1 ELSE 0 END) AS '点击次数', SUM(CASE WHEN `用户行为`='收藏' THEN 1 ELSE 0 END) AS '收藏次数', SUM(CASE WHEN `用户行为`='加购物车' THEN 1 ELSE 0 END) AS '加购物车数', SUM(CASE WHEN `用户行为`='支付' THEN 1 ELSE 0 END) AS '购买次数' FROM user GROUP BY `用户id` ORDER BY COUNT(`用户行为`) DESC;
查询结果为：
查看单个用户的复购率：
SELECT CONCAT(ROUND(SUM(case when `支付次数`>=2 then 1 else 0 end) / SUM(case when 支付次数>0 then 1 else 0 end) * 100), '%') AS '复购率' FROM 单个用户行为;
用户购买行为数据漏斗图
用户购买行为，收藏和加购物车两个环节是并行的，不存在上下承接关系
SELECT SUM(点击次数) AS '总点击数', SUM(收藏次数) AS '收藏总数', SUM(加购物车次数) AS '加购物车总数', SUM(支付次数) AS '支付总数' FROM 单个用户行为;
用户购买行为转化率
SELECT CONCAT(ROUND(SUM(点击次数)/(SELECT COUNT(*) from `user`)*100,2),'%') AS '点击-浏览转化率', CONCAT(ROUND((SUM(收藏次数)+SUM(加购物车次数))/SUM(点击次数)*100,2),'%') AS '收藏加购物车-点击转化率', CONCAT(ROUND(SUM(支付次数)/(SUM(收藏次数)+SUM(`加购物车次数`))*100,2),'%') AS '购买-收藏加购物车转化率' FROM 单个用户行为;
SELECT CONCAT(ROUND(SUM(点击次数)/(SELECT COUNT(*) from `user`)*100,2),'%') AS '点击-浏览转化率', CONCAT(ROUND(SUM(收藏次数)/SUM(点击次数)*100,2),'%') AS '收藏-点击转化率', CONCAT(ROUND(SUM(加购物车次数)/SUM(点击次数)*100,2),'%') AS '购物车-点击转化率', CONCAT(ROUND(SUM(支付次数)/SUM(点击次数)*100,2),'%') AS '购买-点击转化率' FROM 单个用户行为;
查看购买率和购买次数之间的关系
### 降序排序 SELECT 用户id, 点击次数, 收藏次数, 加购物车次数, 支付次数, CONCAT(ROUND(支付次数/点击次数*100, 2),'%') AS '购买率' FROM `单个用户行为` GROUP BY `用户id` ORDER BY 支付次数 DESC; ### 升序排序 SELECT 用户id, 点击次数, 收藏次数, 加购物车次数, 支付次数, CONCAT(ROUND(支付次数/点击次数*100, 2),'%') AS '购买率' FROM `单个用户行为` GROUP BY `用户id` ORDER BY 支付次数 ASC;
由上表可以发现，购买率高并不代表购买次数就高，由此可以推断，点击次数少，收藏也少但是购买次数越高的人是有目的的购物。这类用户更喜欢看准目标就直接下单，此类用户目标明确，一般都是缺啥买啥，鲜少会为了促销而冲动购物，为理智购物型
购买率为0用户：此类用户可以认为是等待型或克制型用户，该类用户在一个月里购物欲望为0，如果点击次数较少，可能是不太会购物的或者不喜欢上网的用户；如果点击次数较多，加购或收藏较多，有可能考虑是为商家活动做准备，该类用户，下单欲望较少且自制力较强，思虑多或者不会支付，购物难度较大。
基于时间维度了解用户的行为习惯
SELECT 时间, COUNT(`用户行为`), SUM(CASE WHEN `用户行为`='点击' THEN 1 ELSE 0 END) AS '点击次数', SUM(CASE WHEN `用户行为`='收藏' THEN 1 ELSE 0 END) AS '收藏次数', SUM(CASE WHEN `用户行为`='加购物车' THEN 1 ELSE 0 END) AS '加购数', SUM(CASE WHEN `用户行为`='支付' THEN 1 ELSE 0 END) AS '购买次数' FROM user GROUP BY `时间` ORDER BY `时间`;
可以看出，每日0点到5点用户活跃度快速降低，降到一天中的活跃量最低值，6点到10点用户活跃度快速上升，10点到18点用户活跃度较平稳，18点到23点用户活跃度快速上升，达到一天中的最高值。与用户产生互动的运营行为可以参考用户比较活跃的时间段。
一周中用户活跃时间段分布
SELECT DATE_FORMAT(日期,'%W') AS weeks, COUNT(`用户行为`), SUM(CASE WHEN `用户行为`='点击' THEN 1 ELSE 0 END) AS '点击次数', SUM(CASE WHEN `用户行为`='收藏' THEN 1 ELSE 0 END) AS '收藏次数', SUM(CASE WHEN `用户行为`='加购物车' THEN 1 ELSE 0 END) AS '加购数', SUM(CASE WHEN `用户行为`='支付' THEN 1 ELSE 0 END) AS '购买次数' FROM user where 日期 BETWEEN '2014-11-23' AND '2014-12-13' GROUP BY weeks ORDER BY DATE_FORMAT(日期,'%w');
每周用户活跃度较稳定，每周五活跃度会有小幅降低，但是周末会慢慢回升。其中第四周周五用户活跃度突增是由双十二大促活动引起。
不同商品种类的用户行为
select count(商品id) FROM (SELECT 商品id, count(用户id) as 'times' FROM user where 用户行为 = '支付' GROUP BY 商品id) as buy_times GROUP BY times ORDER BY COUNT(*) desc;
在本次统计的数据中，只购买一次的产品有77586种，购买两次的产品有9910种，本次分析的商品中用户购买的共有92753种商品，却没有出现购买用户数量非常集中的商品，购买一次的商品占到83.65%，说明商品售卖主要依靠长尾商品的累积效应，而非爆款商品的带动。
用户RFM 模型找出有价值的用户
我们首先统计出用户购买商品的最大值和最小值，数据源中没有相关的金额数据，因此通过 R 和 F 的数据对客户价值进行打分
SELECT r.用户id,f.frequency,recent_rank,freq_rank, CONCAT( CASE WHEN recent_rank<=(4330)/4 THEN '4' WHEN recent_rank>(4330)/4 AND recent_rank<=(4330)/2 THEN '3' WHEN recent_rank>(4330)/2 AND recent_rank<=4330/4*3 THEN '2' ELSE '1' END, CASE WHEN freq_rank<=(4330)/4 THEN '4' WHEN freq_rank>(4330)/4 AND freq_rank<=(4330)/2 THEN '3' WHEN freq_rank>(4330)/2 AND freq_rank<=4330/4*3 THEN '2' ELSE '1' END )AS user_value FROM (SELECT a.*,(@rank:=@rank+1) AS recent_rank FROM ( SELECT `用户id`, DATEDIFF('2014-12-19',max(日期)) AS recent FROM user AS t1 WHERE `用户行为`='支付' GROUP BY `用户id` ORDER BY recent )AS a,(SELECT @rank:=0) AS b) AS r, (SELECT *,(@rank2:=@rank2+1) AS freq_rank FROM (SELECT `用户id`,count(*) AS frequency FROM user WHERE `用户行为`='支付' GROUP BY `用户id` ORDER BY frequency DESC) AS a,(SELECT @rank2:=0)AS b) AS f WHERE r.用户id=f.用户id ORDER BY user_value DESC;
通过打分可以了解每位顾客的特性，从而实现差异化营销。比如对于客户价值高的用户，为重点用户需要关注；对于客户价值中等且忠诚度高而购买能力不足的，可以可以适当给点折扣或捆绑销售来增加用户的购买频率。对于客户价值低且忠诚度不高而购买能力强的，需要关注他们的购物习性做精准化营销，对于客户价值低且忠诚度不高购买能力不强的客户可作为潜在客户。
结论
本次分析利用Mysql语句执行，数据集大约有1000万条淘宝用户行为数据，针对用户行为问题我们可以使用AARRR漏斗模型进行业务分析，结合上述分析的业务指标，提出修改建议。
第一环节获取客户（Acquisition）：关键点是语言市场匹配、渠道产品匹配。
2014年12月12日那一天是双十二，点击量陡增，怀疑是那一天淘宝平台有大促销促销活动。一天的不同时段中，点击量从19点开始点击量稳步上升，到22点到达顶峰，23点稍有回落，到24点明显下降，大部分用户会在晚上时段购物，平台开展活动获取客户首选这个时间段进行。
淘宝是电商第一平台，用户基数大，可以利用用户转发的方式获取新客户，比如在晚间时段做促销活动，邀请朋友拼团享受优惠来增加用户数，适合利用口碑渠道获取新客户。
第二环节激活用户(Activation)：摸清楚产品的“啊哈”时刻，用户从浏览到最终购买整个过程的流失情况，确定夹点位置，提出改善转化率的建议。
用户行为包括点击、放进购物车、收藏以及购买，点击量占总行为的94%，而放进购物车只占2.97%，最后实际购买跌至1.04%，夹点在放进购物车这一环节上。可能出现的原因是用户花了大量时间寻找合适的产品。
根据数据分析结果改善转化率的建议有：（1）优化电商平台的筛选功能，增加关键词的准确率，让用户可以更容易找到合适产品；（2）给客户提供同类产品比较的功能，让用户不需要多次返回搜索结果反复查看，便于用户确定心怡产品；（3）精简下单步骤，提供一键下单服务，比如只包含点击-购买-支付三个环节，缩短购买流程，提高用户体验。
第三个环节提高留存（Retention）：让用户养成使用习惯。
让用户保持使用淘宝电商平台的习惯是提高留存率的关键，可采用的方案可能有：（1）按照使用频率和购买次数积攒积分，每天上线点击量达到某个数值即可自动领取积分，到月末换取购物礼券；（2）对于年购买次数和金额达到规定量的客户推出VIP服务，享受全场不限时9.5折优惠，购买次数同比上升之后相应福利也上升，利用这种方法提高高价值用户的留存率和对平台的忠诚度。
第四个环节增加收入（Revenue）:提高成交转化率、复购率及产品和类目的购买率情况。
独立用户从点击到最后购买的转化率约为68%，用户购买诚意还是很足的，所以通过合理优化电商平台的筛选功能可以提高最终购买的转化率。
有购买行为的用户中，大概有92%的用户会重复购买。我们可以通过复购率、购买金额（本次数据集没有提供）等来确定价值用户，通过分析找出价值用户的购买偏好，产品和类目等，给价值用户制定个性化的产品推荐，从而提高用户体验和电商平台销售情况。
以上数据显示淘宝平台的最大优势是产品种类和类目丰富，用户可选择的范围非常广，吸引不同类型的客户群，所以应该继续保持这个优势。
可能合适的提高方案有：（1）内容营销：“没有找不到的产品，只有想不到的产品”来宣传平台购物种类丰富，让用户形成“只要买东西上淘宝一定有”的思维习惯；（2）针对前面确定的价值用户提供个性化产品推荐，比如最关心的产品类目和种类，上新之后定时推送给用户；（3）针对复购率，可以推出3个月内复购优惠活动，让客户保持购买频率。
第五个环节推荐（Refer）:用户推荐给其他人，关注转发率、转化率和K因子。
针对淘宝平台，让用户推荐给其他人的方案有：（1）产品在购买的时候提供拼团服务，让用户主动推荐给其他人；（2）每当推出新功能，比如前面提到的一键下单，让体验过的用户转发和分享领取优惠券，快速实现新功能推广；（3）当用户使用优惠券购物或者通过某种行为积分购物之后提供朋友圈打卡功能，分享给好友，实现传播功能。
在实行以上方案之后需要关注转发率，也既用户分享的比例，转化率，通过用户分享链接点击购买的用户比例以及K因子（ = 感染率*转化率）来检测提出方案的有效性。