淘宝平台用户行为分析
一、项目背景
随着电子商务的迅速发展，淘宝、京东等电商平台的用户基数也是在迅速扩大，同时用户的交易也是越来越频繁，这使得交易平台上每时每刻都会产生海量的数据，而挖掘这些数据背后的价值，分析数据中隐含的规律，为商家提供针对性的建议，帮助商家扩大销售、提高利润，是一个数据分析师必要的职责。本文通过SQL对淘宝平台用户的行为数据进行数据分析，以期发现淘宝平台用户的一些消费规律，为商家提出可行性的建议。
二、提出问题
本文通过产品、用户行为、用户价值三个维度提出研究问题：
（1）哪些品类产品更受用户偏爱，用户的偏好性是否一致，如果不一致是什么原因导致的；
（2）用户的活跃程度有什么特征，用户不同行为间的转化率如何；
（3）根据用户消费行为确定用户价值
三、分析思路及内容
本文分析内容主要分为三个部分：
（1）利用MySQL数据库工具，选取浏览量前十和购买量前十的产品品类，分析用户对哪些产品品类更为喜爱，同时假设检验法来检验用户对产品品类的偏好是否一致，即高浏览量品类和高购买量品类是否相同。
（2）利用时间序列分析法去分析用户行为在不同时间类型中的特点，同时通过构建漏斗转化模型分析用户不同行为间的转化率。
（3）依据用户消费行为分析，构建RFM用户价值模型，对用户进行分类。
四、数据说明
本文数据来自阿里天池，User Behavior Data on Taobao App数据集，数据集记录了不同用户在2017年11月25日至2017年12月3日之间的消费行为，其中数据集各字段含义如下：
用户ID（user_id）：序列化后的用户ID，整数类型
商品ID（item_id）:序列化后的商品ID，整数类型
商品品类ID（category_id）:序列化后的商品所属类目ID，整数类型
行为类型（behavior_id）:字符串，包括四种类型，分别是‘‘pv’’（商品详情页浏览行为）、‘‘fav’’（收藏商品行为）、‘‘cart’’（加入购物车行为）、‘‘buy’’（商品购买行为）
时间戳：行为发生的时间戳，整数类型
整个数据集中，用户数量有接近100万个（987,994），商品数量约有416万（4162,024），商品类目数量接近1万种（9,439），全部用户行为数据超过1亿条（100,150,807）。
五、数据处理
原数据集中数据量过大，受限于性能，因此截取前100万条数据作为分析样本导入MySQL数据库中，并将样本数据表命名为sample_ub。
5.1检查是否有缺失项
利用SQL查询语言：
SELECT
COUNT( user_id ),
COUNT( item_id ),
COUNT( category_id ),
COUNT( behavior_id ),
COUNT( `timestamp` )
FROM
sample_ub；
检查各字段是否有缺失值，查询结果如下：
可以看出各字段中没有缺失值。
5.2检查是否有重复项
利用SQL查询语言：
SELECT *
FROM sample_ub
GROUP BY user_id,，item_id,，`timestamp`
HAVING COUNT( user_id )>1
将user_id，item_id，timestamp三个字段组合起来，查询数据集中是否存在重复值。
可以看出原数据集中不存在重复值。
5.3转换时间数据
原数据集中时间数据是以时间戳形式保存的，本文通过SQL中的FROM_UNIXTIME函数进行数据格式转化，SQL查询语言如下：
UPDATE sample_ub
SET date=FROM_UNIXTIME(`timestamp`,'%Y-%m-%d'),
time=FROM_UNIXTIME(`timestamp`,'%k:%i:%s'),
weekday=FROM_UNIXTIME(`timestamp`,'%a')
原数据表更新为：
5.4查询异常值
根据原数据集的介绍，数据时间范围是2017年11月25日至2017年12月3日之间，在这个时间段之外的数据都应该视为异常数据，进行删除。
通过如下SQL查询语言：
SELECT
COUNT(*)
FROM
sample_ub
WHERE
date < '2017-11-25' ORdate > '2017-12-03'
发现异常数据470条。
通过SQL查询语句删除：
DELETE
FROM sample_ub
WHERE date < '2017-11-25' OR date > '2017-12-03'
5.6查看数据情况
通过如下SQL语句查询各字段样本数量：
SELECT
COUNT( DISTINCT user_id )AS用户数,
COUNT( DISTINCT item_id )AS商品数量,
COUNT( DISTINCT category_id) AS商品品类数量,
SUM( CASE WHEN behavior_id= 'pv' THEN 1 ELSE 0 END ) AS浏览次数,
SUM( CASE WHEN behavior_id= 'fav' THEN 1 ELSE 0 END ) AS收藏次数,
SUM( CASE WHEN behavior_id= 'cart' THEN 1 ELSE 0 END ) AS加入购物车次数,
SUM( CASE WHEN behavior_id= 'buy' THEN 1 ELSE 0 END ) AS购买次数,
COUNT(*) AS数据总量
FROM
sample_ub
查询结果如下：
六、产品维度数据分析
在产品维度分析中，我们先分别选出浏览量前十和购买量前十的商品品类，之后再验证用户偏好是否一致。
6.1高浏览量商品品类
通过如下SQL语句查询浏览量前十的商品品类：
SELECT
category_id,
behavior_id,
COUNT(*)
FROM
sample_ub
WHERE
behavior_id = 'pv'
GROUP BY
category_id
ORDER BY
COUNT(*) DESC
LIMIT 10
从图表中可以看出用户浏览次数前十的商品品类分别为：4756105、4145813、2355072、3607361、982926、2520377、4801426、1320293、2465336、3002561。
6.2购买量前十的商品品类
通过如下SQL语句查询购买量前十的商品品类：
SELECT
category_id,
behavior_id,
COUNT(*)
FROM
sample_ub
WHERE
behavior_id = 'buy'
GROUP BY
category_id
ORDER BY
COUNT(*) DESC
LIMIT 10
从查询结果中可以看出，购买量排在前十的商品品类分别为：2735466、1464116、4145813、2885642、4801426、4756105、982926、1320293、2640118、3002561。
6.3检验用户偏好一致性
本文假设用户偏好是一致的，即浏览量高的商品品类与购买量高的商品品类应该一致。然而通过对比浏览量前十和购买量前十的商品品类，我们发现两者之间并不是非常一致，浏览量前十的商品品类只有6类排在购买量前十的名单里，其中排在浏览量名单第3、4、6、9的2355072、3607361、2520377、24653364类商品并没有排在购买量前十的名单中。另一方面，在浏览量排名前三位的商品品类4756105、4145813、2355072，在购买量前十排名中分别排在第6、3名，而2355072类商品的购买量没有排进前十，这进一步说明用户对商品的偏好不一致。
造成用户偏好出现分歧的主要原因可能是因为淘宝平台的商品推送机制不合理导致的。淘宝平台首页推送的大量商品吸引了大量的用户点击浏览，形成了巨大的商品浏览量，但是商品并不是用户满意的，因此很难转化成购买量。
七、用户行为分析
通过日期、星期、小时三种时间段分析用户行为的时间特征，同时构建用户漏斗转化模型分析用户不同行为间的转化率。
7.1用户行为的时间特征分析
通过SQL语句查询不同日期的用户活跃情况：
SELECT behavior_id,，date,，COUNT(*)
FROM sample_ub
GROUP BY date,，behavior_id
ORDER BY behavior_id,，date
从图中可以看出浏览次数和加入购物车次数的变化趋势十分相似，在2017年11月25日到2017年12月3日间呈现下降-持平-上升的趋势，而加入收藏夹次数和购买次数则呈现出先持平，再上升的趋势。这可能是因为在此之前淘宝平台已经举办了“11.11”全平台性质的促销活动，用户的消费热情和消费需求得到了极大地释放，因此用户的浏览次数、加入购物车次数以及加入收藏夹次数均有不同程度地下降，用户的购物次数也处于一个持平的阶段，而进入12月之后，淘宝平台以及各个商家开始积极筹备“12.12”、圣诞节、元旦节等大型促销活动，前期的一些预热活动开始重新激发了用户的消费兴趣，因此用户的不同行为在12月1日之后均有了不同幅度的提升。
通过如下SQL语句查询用户行为在不同星期的特征：
SELECT behavior_id,weekday,COUNT(*)
FROM sample_ub
GROUP BY weekday,behavior_id
ORDER BYbehavior_id,FIELD(weekday,'Mon','Tue','Wed','Thu','Fir','Sat','Sun')
从图中可以看出，周一到周五用户的活跃程度较低，趋势平稳，而周六、周天，用户活跃程度有了巨大地提升，说明淘宝用户的消费行为主要集中在周末休息时间，用户将网上购物当做一种休闲放松的手段。
通过如下SQL语句查询不同时点的用户活跃程度：
SELECT behavior_id,`hour`,COUNT(*)
FROM sample_ub
GROUP BY behavior_id,`hour`
ORDER BY behavior_id,`hour`
从图中可以看出，每天的3点到6点用户活跃程度下降至最低点，从7点之后用户活跃程度开始上升，并维持在一个相对平稳的区间，18点之后用户活跃程度开始上升，在22到23点达到最高点，这基本与人们的作息工作时间相吻合，同时22点到23点用户活跃度达到一天中最高点也说明睡前刷淘宝已经成为用户的一种生活习惯。
7.2用户转化率分析
构建不同情况的用户消费行为模型：
（1）用户只是进行了浏览；
（2）用户浏览之后，添加了收藏夹或添加了购物车，但没有进行下单；
（3）用户浏览之后直接进行了下单；
（4）用户浏览之后，添加了收藏夹或添加了购物车，最后进行了下单。
构建用户行为特征：
SELECT
`sample_ub`.`user_id` AS `user_id`,
`sample_ub`.`item_id` AS `item_id`,
sum((CASE WHEN ( `sample_ub`.`behavior_id` = 'pv' ) THEN 1ELSE 0 END )) AS `PV`,
sum((CASE WHEN ( `sample_ub`.`behavior_id` = 'fav' ) THEN 1ELSE 0 END )) AS `FAV`,
sum((CASE WHEN ( `sample_ub`.`behavior_id` = 'cart' ) THEN 1ELSE 0 END )) AS`CART`,
sum((CASE WHEN ( `sample_ub`.`behavior_id` = 'buy' ) THEN 1ELSE 0 END )) AS `BUY`
FROM `sample_ub`
GROUP BY `sample_ub`.`user_id`,`sample_ub`.`item_id`
计算用户行为总次数：
SELECT COUNT( user_id ) AS '用户行为总次数'
FROM user_feature
WHERE PV>0
[if !vml]
[endif]
查询结果表明用户行为总次数为707479次。
计算不同情况消费行为发生次数：
a.用户只进行了浏览
SELECT COUNT( user_id ) AS '只进行了浏览'
FROM user_feature
WHERE PV > 0 AND FAV = 0 AND CART = 0 AND BUY =0
查询结果表明，只进行了浏览的用户行为数为660800；
b.用户浏览之后，添加了收藏夹或添加了购物车，但没有进行下单
SELECT COUNT( user_id ) AS '浏览-添加收藏、购物车-没有下单'
FROM user_feature
WHERE (FAV > 0 OR CART > 0) AND PV>0 AND BUY=0
查询结果表明，进行了浏览，然后添加收藏或购物车，但最后没有下单的行为数有33681次
c.用户浏览之后直接进行了下单
SELECT COUNT( user_id ) AS '浏览-下单'
FROM user_feature
WHERE PV>0 AND FAV=0 AND CART=0 AND BUY>0
查询结果表明，浏览之后直接进行下单的行为数有9313次。
d. 用户浏览之后，添加了收藏夹或添加了购物车，最后进行了下单
SELECT COUNT( user_id ) AS '浏览-添加收藏、购物车-下单'
FROM user_feature
WHERE PV>0 AND (FAV>0 OR CART>0) AND BUY>0
各消费行为转化率如图所示：
可以看出浏览之后的用户流失率高达91.4%，绝大部分用户在进行了浏览之后就流失了，说明平台的商品推送机制仍需加强，应该尽可能地掌握用户的喜好，吸引用户进行下单。另一方面，用户在添加收藏或购物车之后，购买率有了明显地提升，从1.3%上升到9.9%，这说明添加收藏或购物车这一行为可以更好地提高用户的购买转化率，因此商家应该完善关于店铺收藏或者商品加购物车的促销推广活动，提高用户的购买率。
八、用户价值维度分析
8.1根据用户消费行为，构建RFM用户价值模型：
根据RFM模型建立用户分类表：
8.2查询时间间隔和消费频次，并划分价值高低
查询用户购买时间：
SELECT
`sample_ub`.`user_id` AS`user_id`,
`sample_ub`.`behavior_id`AS `behavior_id`,(
to_days( '2017-12-03' ) -to_days( `sample_ub`.`date` )) AS `时间间隔`
FROM
`sample_ub`
WHERE
( `sample_ub`.`behavior_id`= 'buy' )
GROUP BY
`sample_ub`.`user_id`
ORDER BY
(
to_days( '2017-12-03' ) -to_days( `sample_ub`.`date` ))
查询结果表明，用户购买时间间隔最小为0天，最大为8天，根据模型定义，购买时间间隔越小，用户价值越大。因此，我们划分区间：0-4天，高价值用户；5-8天，低价值用户。
查询用户消费频次：
SELECT
`user_feature`.`user_id` AS`user_id`,
sum( `user_feature`.`BUY` )AS `消费频次`
FROM
`user_feature`
WHERE
( `user_feature`.`BUY` >0 )
GROUP BY
`user_feature`.`user_id`
ORDER BY `消费频次`
查询结果表明，用户消费频次最高为72次，最低为1次，用户消费频次越高，价值越高，，因此我们划分区间：1-3次，低价值用户；3次以上，高价值用户。
8.3对用户进行分类
构建视图，将用户消费时间间隔和消费频次联结在一起：
SELECT
`a`.`user_id` AS `user_id`,
`a`.`时间间隔` AS `时间间隔`,
`b`.`消费频次` AS `消费频次`
FROM
(
`recency` `a`
JOIN `frequency` `b` ON ((
`a`.`user_id` =`b`.`user_id`
)))
在这个视图的基础上，查询不同价值用户的数量：
SELECT A.`用户分类`,COUNT(*)
FROM (SELECT B.user_id,
(CASE WHEN B.R='高' AND B.F='高' THEN '价值用户'
WHEN B.R='高' AND B.F='低' THEN '发展用户'
WHEN B.R='低' AND B.F='高' THEN '保持用户'
WHEN B.R='低' AND B.F='低' THEN '挽留用户'
ELSE NULL
END) AS 用户分类
FROM (select `user_value`.`user_id` AS `user_id`,(case when
(`user_value`.`时间间隔` between 0 and 4) then '高' else '低' end) AS `R`,(case when (`user_value`.`消费频次`
between 1 and 3) then '低' else '高' end) AS `F` from `user_value`) AS B) AS A
GROUP BY A.`用户分类`
查询结构表明，用户主要集中在发展用户和挽留用户上，同时保持用户也占据了一部分比例。
发展用户主要特征是消费时间间隔短，但消费频次低，商家应该想办法提高这部分用户的消费频次，比如发放减免券、优惠券等，吸引用户进行多次消费。
挽留用户消费间隔长，消费频次低，有即将流失的危险，因此商家应该主动联系用户，推出一些老用户回归活动，吸引挽留用户重新消费。
保持用户属于消费频次高，但消费间隔较长的用户，这类用户一般属于忠实用户，商家应该积极利用邮件、短信等方式推送新品信息、促销信息，提高用户复购率。
