数据分析-淘宝用户行为分析

淘宝用户行为分析

数据分析在任何一个行业都有着重大的作用，数据多了就一定会有规律，人们就是从这些大量、动态、能持续的数据中，通过运用新系统、新工具、新模型的挖掘，从而获得具有洞察力和新价值的东西。而大数据时代的到来，也给各行各业带来了新的商机，我们可以通过数据分析从而预测经济走向，优化销售业绩等。作为一名网购爱好者，在这里想分析一下电商的代表之一——淘宝的用户行为数据。

项目背景和目的

项目数据来源于https://tianchi.aliyun.com/dataset/dataDetail?dataId=649&userId=1，该数据集的时间节点为：2017年11月25日到2017年12月3日。本项目的分析目的，是探索淘宝用户的行为模式，了解用户购买行为背后的特征和习惯，发现经营问题，找到提高销量的可行措施，为运营工作提供建议和意见。

分析步骤

利用SQL在MySQL关系型数据库管理系统中对淘宝用户行为进行数据分析。分析步骤如下：

提出问题——理解数据——数据清洗——构建模型——数据可视化

 提出问题                                                                                                                           

1、针对这次的淘宝用户行为分析，打算从四个维度进行分析：

      第一个维度：用户购物情况整体分析

      以PV、UV、跳失率等指标，分析用户最活跃的日期及活跃时段，了解用户行为习惯

      第二个维度：商品购买情况分析

      从成交量、人均购买次数、复购率等指标，探索用户对商品的购买偏好，了解商品的销售规律

      第三个维度：用户行为转化漏斗分析

      从收藏转化率、购物车转化率、成交转化率，对用户行为从浏览到购买进行漏斗分析

      第四个维度：对用户进行分类，找出有价值的用户

      参照RFM模型，进行用户分类，找出最具有价值的核心付费用户群

2、根据以上的分析思维，提出以下几个问题进行分析并提供给解释和建议：

        （1）在项目的时间段里找出用户最活跃的日期以及每天活跃时间段，了解用户的行为时间模式

        （2）找到用户对各种类商品的偏好，找到针对不同商品的营销策略

        （3）分析用户行浏览到购买的过程中的转化情况，分析各个环节的流失率，提出改善转化率的意见

        （4）进行用户分类，找出最具有价值的核心付费用户群

下图为电商数据分析指标的思维导图，供参考

 理解数据                                                                                                                           

 本数据集(UserBehavior.csv)包含了2017年11月25日到2017年12月3日之间约一百万随机用户的所有行为（行为包括点击、收藏、加购物车、支付）。由于数据量太大，本文选择前100万行记录来做分析。数据集的每一行表示一条用户行为，由用户ID、商品ID、商品类目ID、行为类型和时间戳组成，并以逗号分隔。关于数据集中每一列的详细描述如下：

理解商品数据集的字段信息：

    user_id: 用户id

    item_id: 商品id

    item_category:  品类id（商品所属的品类）

    behavior_type: 用户行为类型

    timestamp:  时间戳

用户行为类型又分为四种：

    pv：商品详情页pv，等价于点击

    buy：商品购买

    cart：商品加入购物车

    fav：收藏

  数据清洗                                                                                                                          

 在mysql库中创建user_behavior表，使用Navicat导入数据，缺失值处理、一致化处理、异常值处理，为了方便分析，将timestamp字段转化成时间字段，分别为date、day、hour、times

#检查重复值
select distinct * from user_behavior;
#检查缺失值
select * from user_behavior where userId is null;
#添加时间相关的列
alter table user_behavior add column datetime datetime;
update user_behavior set datetime =from_unixtime(timestamp, '%Y-%m-%d %H:%i:%s');
 
alter table user_behavior add column date char(10);
update user_behavior set date=from_unixtime(timestamp, '%Y-%m-%d');
 
alter table user_behavior add column day char(2);
update user_behavior set day=from_unixtime(timestamp, '%d');

alter table user_behavior add column times char(10);
update user_behavior set times=from_unixtime(timestamp, '%H:%i');
 
alter table user_behavior add column hour char(10);
update user_behavior set hour=from_unixtime(timestamp, '%H');

 构建模型和可视化                                                                                                                 

 1、用户购物情况整体分析

1.1 这9天内的PV（浏览量）：

select count(behavior_type) as 浏览量 from user_behavior where behavior_type=‘pv’；

 

 1.2 这9天内的UV（用户数）：

select count(distinct user_id) as 用户数 from user_behavior;

 

 由1.1和1.2的结果可以得出这九天内的用户平均访问量是：939536/10202=92.1，即每个用户平均访问了92个页面。

1.3 跳失率：只有点击行为的用户/总用户数

select count(distinct user_id) from user_behavior 
where user_id not in 
(select user_id from user_behavior where behavior_type='fav' ) 
and user_id not in 
(select user_id from user_behavior where behavior_type='cart' ) 
and user_id not in 
(select user_id from user_behavior where behavior_type='buy');

 

  由结果可知只有点击行为的用户数为589，故跳失率为：589/10202=0.0577，由此可知跳失率不高，说明店铺的商品详情页面排版布局合理（清晰明了），导航功能完善好用，详情页中商品的解说逻辑清晰有力等等，可继续维持和改善。

1.4 每天访问量

select date,count(behavior_type) as 访问量 from user_behavior 
where behavior_type='pv' 
GROUP BY date;

 

 1.5 每天访问用户

select date,count(distinct user_id) as 访客数 from user_behavior GROUP BY date;

 

 1.6 每个时间段的访问量和访客数

select a.hour,a.访问量,b.访客数 from
(select hour,count(behavior_type)as 访问量 from user_behavior
where behavior_type='pv'
group by hour)a
inner join 
(select hour,count(distinct user_id) as 访客数 from user_behavior
group by hour)b
on a.hour=b.hour;

  

 由1.1-1.6可以看出12月2号和3号这两天访客数量和访问量最多，猜测是因为这两天是周末，人们都比较有空闲进行网上购物。而在一天内，在19-22时间段内访客数和访问量达到峰值，可能是因为这个时间段是下班时间，人们有比较多的时间行进网上购物，而在1-7时数量处于超低值，可能是这个时间段大多数人处于睡眠状态。

 2、商品购买情况分析

 2.1 成交量

select date,count(behavior_type) as 成交量 from user_behavior
where behavior_type='buy'
group by date;

 

 2.2 人均购买次数

select count(behavior_type)as 订单量,count(distinct user_id)as 用户数,count(behavior_type)/count(distinct user_id)as 人均购买次数
from user_behavior
where behavior_type='buy';

 2.3 复购率

select count(user_id) as 复购人数 from (select user_id,count(user_id) from user_behavior 
where behavior_type='buy' 
GROUP BY user_id 
HAVING count(user_id)>1) p

 

 由2.2可知总购买人数为7000，2.3可知购买两次以上的人数为4639，所以复购率=4639/7000*100%=66%

#购买次数排前十位的商品
select item_id,count(behavior_type)被购买次数 from user_behavior
where behavior_type='buy'
group by item_id
order by count(behavior_type) desc
limit 10;
#重复购买次数最多的前十位客户
select user_id,count(user_id)as 购买次数 from user_behavior
where behavior_type='buy'
group by user_id
order by count(user_id) desc
limit 10;

                    

由以上结果可以看出3122135这个商品被购买了17次，这个可能是比较热门的商品，可对此商品加大库存并作其他加大销量的营销手段，而用户107932购买次数达到72次，对于这类忠实客户需要开发用户信息库，建立完善的用户资料库，记录客户购物情况。

3、用户行为转化漏斗分析

#各种用户行为的转化率
select behavior_type,count(behavior_type),concat(round(count(behavior_type)/(select count(*) from user_behavior)*100,2),'%') as '转化率' from user_behavior
group by behavior_type
order by count(behavior_type) desc;

 由上转化率可看出真正转化为购买的只有2%，用户在浏览商品详情页后出现了大量的流失。

因此，我们需要知道从浏览到购买，是哪一个环节出现了问题导致如此低的购买转化率。由于加入购物车和收藏商品没有行为的先后性，所以说购物路线可以有两条：

第一条路线：浏览—加入购物车—购买

第二条路线：浏览—添加收藏—购买

3.1 第一条路线

按照第一条路线来购买的话，需要计算浏览到加入购物车到购买之间的转化率。

加入购物车转化率=加入购物车的用户数/浏览的用户数

购买转化率=加入购物车后购买的用户数/加入购物车的用户数

#浏览的用户数
select count(distinct user_id) as 浏览的用户数 from user_behavior where behavior_type='pv';
#加入购物车的用户数和加入后购买的用户数
select count(distinct a.user_id)as 加入购物车用户数,count(distinct b.user_id)as 加入后购买用户数 from
(select user_id,item_id,item_category, timestamp from user_behavior where behavior_type='cart')a
left join
(select user_id,item_id,item_category,timestamp from user_behavior where behavior_type='buy')b
on a.user_id=b.user_id and a.item_id=b.item_id and a.item_category=b.item_category and a.timestamp<b.timestamp;

     

由查询结果可得，

加入购物车的转化率为：7665/10169 = 75.38%

购买转化率为：2346/7665 = 30.61%

 从图表可以看出，从用户点击浏览到加入购物车的转化率是很高的，达到了75.5%，说明大部分用户在浏览后有购买意向，加入了购物车；但是，在加入购物车之后，只有30%左右的用户是真正进行购买的，而70%的用户是没有进一步购买的。

 3.2 第二条路线

收藏转化率=添加收藏的用户数/浏览的用户数

购买转化率=添加收藏后购买的用户数/添加收藏的用户数

#从收藏到购买的转化：
select count(distinct a.user_id)as 收藏用户数,count(distinct b.user_id)as 收藏后购买用户数 from 
(select user_id,item_id,item_category,timestamp from user_behavior where behavior_type='fav')a
left join
(select user_id,item_id,item_category,timestamp from user_behavior where behavior_type='buy')b
on a.user_id=b.user_id and a.item_id=b.item_id and a.item_category=b.item_category and a.timestamp<b.timestamp;

由查询结果可得，

加入购物车的转化率为：4070/10169 = 40%

购买转化率为：884/4070 = 21.72%

 与线路一对比，用户在浏览后，相对于收藏这个行为，会更倾向于添加到购物车。而购物车的转化率为30%，收藏转化率为21%左右，也就是说用户更偏向与购物车购买。原因可能是加入购物车后可以直接下单购买，而加入收藏后并没有可以下单的页面，如果需要购买必须重新点击商品进入详情页才能下单，多了一个步骤，所以，在这个步骤里可能用户就流失了部分。也可能是收藏的时候购物的欲望没那么强烈，时间越久，购买欲望就越低，转化率就会降低。

不管是路线一还是路线二，从收藏和加入购物车再到转化成购买的转化率都比直接购买的转化率要高，说明从浏览到进行收藏和加入购物车的阶段是指标提升的重点环节。

查看购买率和购买次数之间的关系

#单个用户行为情况
CREATE VIEW 单个用户行为
AS
SELECT `user_id`, count(`behavior_type`),
SUM(CASE WHEN `behavior_type`='pv' THEN 1 ELSE 0 END) AS '点击次数',
SUM(CASE WHEN `behavior_type`='fav' THEN 1 ELSE 0 END) AS '收藏次数',
SUM(CASE WHEN `behavior_type`='cart' THEN 1 ELSE 0 END) AS '加购物车数',
SUM(CASE WHEN `behavior_type`='buy' THEN 1 ELSE 0 END) AS '购买次数'
FROM user_behavior
GROUP BY `user_id`
ORDER BY COUNT(`behavior_type`) DESC;

### 降序排序
SELECT user_id, 点击次数, 收藏次数, 加购物车数, 购买次数, 
CONCAT(ROUND(购买次数/点击次数*100, 2),'%') AS '购买率'
FROM `单个用户行为`
GROUP BY `user_id`
ORDER BY 购买次数 DESC;

### 升序排序
SELECT user_id, 点击次数, 收藏次数, 加购物车数, 购买次数, 
CONCAT(ROUND(购买次数/点击次数*100, 2),'%') AS '购买率'
FROM `单个用户行为`
GROUP BY `user_id`
ORDER BY 购买次数 ASC;

          

 由上图可以发现，购买率高并不代表购买次数就高，因此，点击次数少，收藏也不多但是购买次数却高的人是有目的地购物的。这类用户更喜欢看准目标就直接下单，此类用户目标明确，极少会为促销而冲动购物，为理智型购物。

而购买率为零的，可认为是等待型或克制型用户，该类用户在这九天内的购物欲望为9，如果点击次数少可能是很少在网上购物的用户；如果点击次数较多，加购和收藏也多的，有可能是想等有优惠活动在下单，该类用户为勤俭型购物。

4、对用户进行分类，找出有价值的用户

RFM模型是衡量客户价值和客户创利能力的重要工具。该模型通过客户的最近交易行为（Recency）、交易频率（Frequency）以及交易金额（Monetary）三项指标来描述该客户的价值状况。一般来说，会将这三项指标分成几个区间进行评分，通过计算评分找到有价值的用户，并对用户进行分类。

最近一次消费(Recency)：是指最近一次消费距离上一次消费之间的时间长短。它反映了客户对产品的态度以及对品牌价值的信任度，它关乎消费者的存留状况。

消费频率(Frequency)：是指某个特定时间内消费的次数。它直接反映了客户的忠诚度，消费频率越高，忠诚度就越高；忠诚度越高的客户数量越多，公司的竞争优势越强，市场份额越大。

消费金额(Monetary)：是指一定时间内消费某产品金额。它反映的是客户的价值，价值越高，给公司创造的利益就更大。

因为数据源里没有金额相关的信息，所以只通过R和F来对客户价值进行评分

R(Recency)最近的购买行为：

本次数据集里时间范围是11月25到12月2，所以把最早的11月25日设为参数对照日期，然后把与11月25日的距离划分区间，把0-2天、3-4天、5-6天、7-8天，分别对应为1-4分。

#按购买天数评分：
create view score_1 as
select user_id,
(case when 购买天数 between 0 and 2 then 1
when 购买天数 between 3 and 4 then 2
when 购买天数 between 5 and 6 then 3
when 购买天数 between 7 and 8 then 4 else 0 end
)as 购买得分
from
(select user_id,datediff(max(date),'2017-11-25')as 购买天数
from user_behavior
where behavior_type='buy'
group by user_id)as a
order by 购买得分;

F(Frequency）购买频率：

由上面对商品购买情况的分析可知，有购买行为的用户最高购买频率可达72次，因此，将1-72从低到高划分为4个档次，1-18、19-36、37-54、55-72分别对应为1-4分。

#按购买频率评分：
create view score_2 as
select user_id,
(case when 购买次数 between 1 and 18 then 1
when 购买次数 between 19 and 36 then 2
when 购买次数 between 37 and 54 then 3
when 购买次数 between 55 and 72 then 4 else 0 end 
)as 购买频率得分
from
(select user_id,count(behavior_type)as 购买次数
from user_behavior
where behavior_type='buy'
group by user_id)b
order by 购买频率得分 desc;

上面两个步骤分别给用户购买时间及购买频率进行了评分。但还需要一个值来作为评比分数的标准，这里拿这两项的平均值作为这个标准。比如如果两项分值都高于平均值，那这个客户就是重要价值用户。

以下是客户类型的分类标准表：

R	F	客户类型
高	低	重要价值用户
高	低	重要保持用户
低	高	重要发展用户
低	高	一般价值用户

#求评分的均值 
select avg(购买得分) from score_1;
select avg(购买频率得分)from score_2;

  

 将两项评分分别和以上求出的均值进行比较，对客户进行分类：

#对用户进行分类：
create view users_classify as
select user_id,
(case when R>3 and F>1 then '重要价值用户'
when R>3 and F<=1 then '重要保持用户'
when R<=3 and F>1 then '重要发展用户'
when R<=3 and F<=1 then '一般价值用户' else 0 end 
)as 用户类型
from
(select a.user_id,a.购买得分 as R,b.购买频率得分 as F
from score_1 as a inner join score_2 as b
on a.user_id=b.user_id)c;

计算各用户类型的数量：

select 
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 users_classify

 

对于重要价值用户，他们是优质的用户，需要重点关注并保持， 应该提高满意度，增加留存；

对于重要保持用户，他们最近有购买，但购买频率不高，可以通过活动等提高其购买频率；

对于重要发展用户，他们虽然最近没有购买，但以往购买频率高，可以做触达，以防止流失；

对于一般价值用户，他们最近没有购买，以往购买频率也不高，特别容易流失，所以应该赠送优惠券或推送活动信息，唤醒购买意愿。

  结论及建议                                                                                                                           

1、12月2日与12月3日，由于是周末的原因，相对其他日期流量增长明显，这两天可以做一些上新或者促销。

2、大部分用户在19-22时间段内访客数和访问量达到峰值，在21到22时，是一天当中最活跃的时段，成交量也是最高。建议可以在用户活跃的时段进行推广以使运营效果最大化。

3、这九天内，人均购买次数为3，复购率达到66%，说明店铺产品对用户吸引力比较大。

4、商品3122135是被购买得最多次的，对于复购率高的商品，建议可以建立自己的忠实粉丝群，实行更精准的人群管理。

5、对于类似107932这种购买次数较多的用户，建议要更全面地了解，开发用户信息库，建立详实的用户资料数据库，追踪记录顾客的交易情况。可以线上组建VIP客户微信群等，针对这些忠实客户的购买偏好推送更精准的销售方案。

6、用户行为转化率只有2%，有98%的用户行为是没有转化为成交的，用户在浏览商品详情页后出现了大量的流失。建议通过活动、优惠券、产品详情页的改进等提高转化。

7、但从加入购物车到真正购买的有30%的转化率，比用户转化率2%高出许多，说明从浏览到进行收藏和加入购物车的阶段是指标提升的重点环节。可以把这两个功能优化，诱导用户更多使用这两个功能。而想提高加入购物车和调价收藏后到真正购买的转化率，建议在用户加入购物车后能有促进用户下单的利益“诱导”，如赠送优惠券或采用倒计时购物车增加客户购买紧近感。

8、从收藏到购买的转化率为21%。相对于购物车30%的转化率，收藏转化率稍低。建议在用户添加收藏后能提示优惠或促销等时限信息，促使用户尽早下单。

9、用RFM模型对用户进行分类后，可知重要价值用户比较少，用户类型主要还是集中于重要保持用户和一般价值用户。建议根据用户类型，进行有针对性的精准营销。