用户购物行为数据分析

用户购物行为数据分析

数据分析报告总结：

1.销售部门要增加用户感兴趣需求高的商品类型TOP3：4756105，4145813，2355072的投放需求量，增加该类商品广告引流。

2.运营部门在用户搜寻商品最活跃的时间段即下午6点至晚上11点对需求高商品类型的商品多策划一些营销活动，提高销售转化率。集中资源在这段时间增强营销推广力度，制定优惠策略促进客户下单购买，有效促进商品销量的提升。

3.销售部门可以根据用户行为整体转化率预测未来销售的大致转化或者流失情况，为未来的商品活动评估做出预测，及时有针对性地调整营销策略。

4.运营部门可以引导用户加购和收藏，比如制定优惠、促销活动等方式促进用户的购买转化率的提升，同时对接平台制定合理推送机制，降低用户点击后就直接流失的可能性

5.运营部门可以根据用户RFM价值分类名单，对于消费频率高但是近期没有消费的重要保持用户使用邮件推送、APP push的方法、活动短信提醒的方法召回。对于消费频率不高但是近期有消费的重要发展用户，应该用会员权益或者发放优惠券的形式促进消费次数。

本文利用SQL进行数据分析，用Excel进行可视化展示。

全文目录：

一、分析背景

二、分析目的

三、理解数据

四、明确问题

五、分析思路

六、清洗数据

七、构建模型及可视化

八、总结和建议

一、分析背景

UserBehavior是阿里巴巴提供的一个淘宝用户行为数据集，用于隐式反馈推荐问题的研究，为平台提供更精准的隐式反馈推荐。用户行为是淘宝针对具有隐式反馈的推荐问题的用户行为数据集。本数据集包含了2017年11月25日至2017年12月3日之间，有行为的约一百万随机用户的所有行为（行为包括点击、购买、加购、喜欢）数据。数据集的组织形式和MovieLens-20M类似，即数据集的每一行表示一条用户行为，由用户ID、商品ID、商品种类ID、行为类型和时间戳组成，并以逗号分隔。

该数据集来源于阿里云天池，数据集链接如下：

数据集-阿里云天池 (aliyun.com)https://tianchi.aliyun.com/dataset/dataDetail?dataId=649&userId=1#1

二、分析目的

数据分析是基于业务的，从业务出发，通过数据分析得出结论并提出改进业务的方式方法，促进业务的发展，提升业务的销售额和盈利，为公司创造价值。

通过用户行为数据挖掘出客户价值，提高用户的忠诚度和粘性，对不同的价值客户采取更有针对性的销售策略及广告推广策略，精准营销，促进用户的成功购买率，提高用户的成交转化率，进而提高商家的销售额和盈利。

注：由于淘宝用户行为数据集有1亿多条用户数据，数据量巨大，所以本次分析用Navicat对该数据集截取了200万条用户行为数据进行分析。

三、理解数据

查看部分数据如下：

3.1 字段理解 字段含义数据类型user_id用户ID整型item_id商品ID整型category_id商品类型整型behavior_type行为类型文本timestamp时间戳整型 3.2 行为字段理解  行为类型含义数据类型pv点击文本buy购买文本cart加购文本fav收藏文本

查看整体数据量：

 >>数据集涉及19544个用户的共有200万条用户行为数据（第1行数据被当作字段名）。

四、明确问题

淘宝用户对什么样的产品感兴趣？淘宝用户是如何流失？具体在哪个环节流失的？不同时间的用户需求量和购买量的关系，怎么根据时间制定策略？怎么根据不同RFM类型用户制定用户留存策略？

五、分析思路

因该数据集涉及商品及其类型、用户行为数据和时间记录，所以可以从用户行为、用户价值、及业务流程维度分别进行分析，采用多维度拆解分析方法对问题进行拆解，用假设检验分析法、对比分析法以及用户RFM模型分析法具体分析用户的购买喜好、用户的集中购买时间点以及用户购买的业务流程的转化情况等具体的业务指标中的问题，得出分析结论并提出改进办法促进业务的提升。

六、清洗数据

数据清洗按以下流程进行：

6.1 选择子集

本数据集各字段都具有分析价值，所以暂时不需要进行处理。

6.2 列名重命名

列名重命名以及数据类型在Navict上进行设计表 *** 作，将各列名进行重新命名 *** 作。

6.3 删除重复值

这里认为当所有字段都同时重复的时候才是重复值，查询所有字段都重复的记录，经查无重复值，故无需进行删除重复值处理。

select * from userbehavior
group by user_id,item_id,category_id,behavior_type,timestamp
having count(*)>1;

6.4缺失值处理

对所有字段进行计数，确认计数结果是否一致，经查所有字段计数结果均相同，所以无缺失值。

select count(user_id),count(item_id),count(category_id),count(behavior_type),count(timestamp) 
from userbehavior;

6.5一致化处理

对所有字段进行仔细观察，字段timestamp无法进行直接分析，为便于后续分析需要将其数据类型转为可进行分析的日期时间date_time、日期date和时间time格式。

alter table userbehavior add column date_time TIMESTAMP(0) null;
alter table userbehavior add column date varchar(10) null;
alter table userbehavior add column time varchar(20) null;

update userbehavior
set
date_time=FROM_UNIXTIME(timestamp),
date=FROM_UNIXTIME(timestamp,'%Y-%m-%d'),
time=substring(date_time from 12 for 2);

6.6异常值处理

由于数据集的背景要求是对2017年11月25日至2017年12月3日之间的淘宝用户的行为数据进行分析，所以对于不在这个时间段内的数据都认为是异常值，需要对该异常值进行删除处理。

-- 查看数据集所在的时间段
select min(date) as 最小日期,max(date) as 最大日期
from userbehavior;

 >>经查数据集日期已经超出分析要求的范围，所以需对不在时间段的日期进行删除处理；

注意：为防止误删，可以用select代替delete先将异常值删选出来检查确认一遍。

 >>确认是这些日期是异常值，显示共有991条异常值数据；

-- 注意，为防误删，先将异常值用select筛选出来
select * from userbehavior where date <'2017-11-25' or date >'2017-12-03';

-- 将确认的时间异常值进行删除 *** 作，此时将select *替换成delete即可
delete from userbehavior where date <'2017-11-25' or date > '2017-12-03';

-- 检查异常值是否删除干净，所以再次查询数据集的时间段
select min(date) as 最小日期,max(date) as 最大日期
from userbehavior;

 至此，经过以上的数据清洗步骤，得到的是干净的数据集，清洗后的数据集字段数量情况如下：

select count(distinct user_id) as 用户数,
       count(distinct item_id) as 商品数量,
       count(distinct category_id) as 商品种类数量,
       count(behavior_type) as 用户行为数
from userbehavior;

 

七、构建模型及可视化 7.1用户行为数据分析

7.1.1用户行为整体数据指标分析

Q1：淘宝用户喜欢什么样的商品？——对不同喜好的商品加以区分，淘汰用户不喜欢的商品

PV（访问次数，Page View）：一定时间内某个页面的浏览次数，用户每打开一个网页可以看作一个PV，在这里可以看作是点击次数。

UV（访问人数，Unique Visitor）：一定时间内访问某个页面的人数。

通过点击次数PV和访问人数UV可以衡量用户喜欢哪种类型的产品，进而对产品进行优化。

-- 用户行为数据：访问次数PV、访问人数UV、
select category_id,count(category_id) as 访问次数PV,count(distinct user_id) as 访问人数UV 
from userbehavior
where behavior_type='pv'
group by category_id
order by 访问次数PV desc
limit 6;

 >>可以看出淘宝用户比较喜欢的商品类型TOP3：4756105，4145813，2355072，说明用户对这些类型的商品需求比较大；

Q2：那在TOP3类型商品里面用户具体又是喜欢哪些商品？

select item_id,count(item_id) as 访问次数PV,count(distinct user_id) as 访问人数UV
from userbehavior
where behavior_type='pv' and category_id='4756105'
group by item_id
order by 访问次数PV desc
limit 3;

select item_id,count(item_id) as 访问次数PV,count(distinct user_id) as 访问人数UV
from userbehavior
where behavior_type='pv' and category_id='4145813'
group by item_id
order by 访问次数PV desc
limit 3;

select item_id,count(item_id) as 访问次数PV,count(distinct user_id) as 访问人数UV
from userbehavior
where behavior_type='pv' and category_id='2355072'
group by item_id
order by 访问次数PV desc
limit 3;

 

 

 

 >>从以上图表展开来看，用户对于4756105类型的商品比较喜欢，尤其是对812879商品感兴趣，其他类型商品同理，说明这种类型的商品是用户痛点，商家对于这些类型的商品应该更有针对性地倾斜，挖掘用户喜欢的商品，加大宣传力度，要求平台增加曝光度。

 7.1.2用户购买次数的维度分析

提出假设：用户购买次数最多的商品也是访问次数TOP的商品；

-- 购买次数TOP6商品类型
select category_id as 商品类型,count(category_id) as 购买次数
from userbehavior
where behavior_type='buy'
group by category_id
order by 购买次数 desc
limit 10;

得出结论：假设不成立

>>淘宝用户点击次数TOP5里面也仅有4756105，4145813两种类型的商品排进了购买前十，对于点击次数高的商品用户购买欲望并不高，虽然用户对商品感兴趣但并没有为此买单，这说明商品还不够好，还不足以让淘宝用户产生购买冲动，商家可以对用户进行问卷调查，弄清影响用户下单的因素，并根据用户的反馈对商品加以改进，促进用户购买率，提升销量。

7.1.3 用户行为的时间维度分析—对比分析方法和假设检验方法

Q2：淘宝用户行为与时间有关系吗？——根据不同时间的用户行为采取不同的营销策略

提出假设：淘宝用户在周末的浏览量比平时更高。

收集证据：

select date,dayname(date) as 日期,category_id as 商品类型,count(item_id) as 访问次数PV,count(distinct user_id) as 访问人数UV 
from userbehavior
where behavior_type='pv' and category_id='4756105'
group by date
union
select date,dayname(date),category_id,count(item_id) as 访问次数PV,count(distinct user_id) as 访问人数UV
from userbehavior
where behavior_type='pv' and category_id='4145813'
group by date
union
select date,dayname(date),category_id,count(item_id) as 访问次数PV,count(distinct user_id) as 访问人数UV
from userbehavior
where behavior_type='pv' and category_id='2355072'
group by date
order by date;

得出结论：假设成立

 >>周末的PV和UV会相对平时高一些，客户在周末时更为活跃，购买热情更高，所以可以重点关注周末的销售，有针对性地在周末进行宣传促销活动。由于时间段比较短，只有九天，所以判断会相对比较浅，若有更长的时间段的更多的数据进行对比分析就会更为直观，判断也会更为准确。

提出假设：淘宝用户在一天中的晚上时间点击量会比其他时间多。

收集证据：

select date,time as 时间,category_id as 商品类型,count(item_id) as 访问次数PV,count(distinct user_id) as 访问人数UV 
from userbehavior
where behavior_type='pv' and category_id='4756105'
group by time
union
select date,time,category_id,count(item_id) as 访问次数PV,count(distinct user_id) as 访问人数UV
from userbehavior
where behavior_type='pv' and category_id='4145813'
group by time
union
select date,time,category_id,count(item_id) as 访问次数PV,count(distinct user_id) as 访问人数UV
from userbehavior
where behavior_type='pv' and category_id='2355072'
group by time;

得出结论：假设成立

 >>以上的PV和UV图均显示在凌晨三四点的时候用户最不活跃，点击量最低，晚上八点半左右的点击量都达到了顶峰，在六点到十一点这段时间，用户在大量浏览商品，说明晚上的这段时间用户最活跃，最有购买意向，所以可以集中资源在这段时间增强营销推广力度，制定优惠策略促进客户下单购买，可以有效提升商品销量。

 7.2 业务流程维度分析

7.2.1用户整体行为转化分析

Q3：淘宝用户的整体及环节购买转化率如何？

#用户整体行为转化分析-漏斗转化模型
select behavior_type as 用户行为类型,count(user_id) as 用户行为总数
from userbehavior
group by behavior_type
order by 用户行为总数 desc;

 

 

 >>从以上图表得知，一方面，用户行为整体转化率是从整体上评估各环节用户占初始环节用户的比例，这样就可以根据一定的比例，去预测未来的大致转化或者流失情况，为未来的商品活动评估做出预测，及时有针对性地调整营销策略。

另一方面，从用户行为转化漏斗-整体转化率的情况来看，从用户点击到用户的加购、收藏、购买的整体转化率都非常低，说明用户对点击后的商品不满意，点击后并没有找到想要购买的商品。

用户行为漏斗的环节转化率可以评估各业务环节之间的转化情况，通过比较各环节转化率，从而寻找业务瓶颈点，也就是找到最低转化率对应的业务环节。

从用户行为转化漏斗-环节转化率的情况来看，从用户点击到加购的转化率是最低的仅仅只有6.2%，而用户加购到收藏和收藏到购买的转化率都超过50%以上，说明点击到加购环节出现问题，那么具体是哪个环节出问题了呢？下面将详细分析各用户购买转化路径的环节转化情况。

7.2.2用户的转化路径分析

Q4：淘宝用户是在哪个环节流失最严重？——找出用户购买转化高及低的路径并加以引导

提出假设：不同购买路径的环节转化率差别大

从不同购买路径的环节转化率分析，分析用户是在哪个环节流失严重，也找出最佳环节转化率的购路径并促进用户往该路劲发展，由于收藏和加购无法分先后，所以用户从点击到购买的路径共有以下四条及其说明：

路径1：点击-购买（未收藏未加购）

路径2：点击-加购-购买（未收藏）

路径3：点击-收藏-购买（未加购）

路径4：点击-收藏-加购-购买（加购收藏均有，不分先后）

#用户环节行为转化分析
-- 创建视图-用户行为总数
create view 用户行为总数 as 
select user_id,item_id,
sum(case when behavior_type='pv' then 1 else 0 end) as 点击数,
sum(case when behavior_type='cart' then 1 else 0 end) as 加购数,
sum(if(behavior_type='fav',1,0)) as 收藏数,
sum(if(behavior_type='buy',1,0)) as 购买数
from userbehavior
group by user_id,item_id;

#不同购买路径的用户情况
-- 路径1：点击-购买
select count(user_id) as '点击用户数'
from 用户行为总数
where 点击数>0;

select count(user_id) as '点击-购买'
from 用户行为总数
where 点击数>0 and 收藏数=0 and 加购数=0 and 购买数>0;

select count(user_id) as '点击-流失'
from 用户行为总数
where 点击数>0 and 收藏数=0 and 加购数=0 and 购买数=0;

-- 路径2：点击-加购-购买
select count(user_id) as '点击-加购'
from 用户行为总数
where 点击数>0 and 加购数>0;

select count(user_id) as '点击-加购-购买'
from 用户行为总数
where 点击数>0 and 收藏数=0 and 加购数>0 and 购买数>0;

select count(user_id) as '点击-加购-流失'
from 用户行为总数
where 点击数>0 and 加购数>0 and 收藏数=0 and 购买数=0;

-- 路径3：点击-收藏-购买
select count(user_id) as '点击-收藏'
from 用户行为总数
where 点击数>0 and 收藏数>0;

select count(user_id) as '点击-收藏-购买'
from 用户行为总数
where 点击数>0 and 收藏数>0 and 加购数=0 and 购买数>0;

select count(user_id) as '点击-收藏-流失'
from 用户行为总数
where 点击数>0 and 收藏数>0 and 加购数=0 and 购买数=0;

-- 路径4：点击-加购-收藏-购买
select count(user_id) as '点击-加购与收藏'
from 用户行为总数
where 点击数>0 and 加购数>0 and 收藏数>0;

select count(user_id) as '点击-加购-收藏-购买'
from 用户行为总数
where 点击数>0 and 收藏数>0 and 加购数>0 and 购买数>0;

select count(user_id) as '点击-加购与收藏-流失'
from 用户行为总数
where 点击数>0 and 加购数>0 and 收藏数>0 and 购买数=0;

用导图将以上筛选出来的用户环节转化的数据情况汇总如下图所示： 

得出结论：假设成立

 >>由上面导图汇总的不同路径的用户环节转化情况可知，用户在点击后的流失情况最为严重，购买转化率最低，流失率高达93%，再次说明用户大量点击了不喜欢的商品，或者平台推送的机制不合理。用户在加购或者收藏后的购买转化率都相对较好，特别是加购与收藏都有行为的用户购买转化率最高，说明可以通过引导用户加购或者收藏，比如制定优惠、促销活动等方式促进用户的购买转化率的提升，同时对接平台制定合理推送机制，降低用户点击后就直接流失的可能性。

 7.3用户RFM价值模型分析

Q5：哪些淘宝用户的价值较高？——利用RFM价值模型对用户进行价值分类实现精准营销

根据用户RFM价值模型找出不同价值用户，有针对性地对不同价值用户采取不同的销售及运营策略，实现针对不同用户的精准营销。

RFM相关指标的解释说明：

指标含义意义

R(Recency)

最近一次消费时间

用户最近一次消费时间与当前时间的间隔

R越大，用户越久没发生交易；

R越小，用户越近有交易发生

F(Frequency)

用户消费频率

用户在固定时间内的购买次数

F越大，表示用户交易越频繁；

F越小，表示用户越不够活跃

M(Monetary)

用户消费金额

用户在固定时间内的消费金额

M越大，表示用户价值越高；

M越小，表示用户价值越低

R：用户最近一次的购买时间间隔，由于数据集只有九天，最后一天是2017年12月3日，这里取最后一天为当前时间，那么R值就是用户最近一次的购买时间到2017年12月3日的差值。

F：用户在时间段内的消费频率，这里计算用户在九天时间里的购买的次数为F值；

M：用户消费金额，由于数据集中不包括该值，所以不考虑该指标。

7.31 计算最近一次购买时间-R值 

-- 计算R值并创建视图-R值
create view R值 as
select user_id as 用户ID,datediff('2017-12-03',max(date)) as R值
from userbehavior
group by user_id
order by R值 desc;

7.32 计算时间段内的购买频率-F值 

-- 计算F值并创建视图-F值
create view F值 as
select user_id as 用户ID,sum(case when behavior_type='buy' then 1 else 0 end) as F值
from userbehavior
group by user_id
order by F值 desc;

将查询的F值数据导出到Excel进行描述统计分析，得到的F值的统计情况如上图所示。

7.33 对R值和F值进行评分

根据以上的R值和F值的分布情况，对R值和F值进行评分，由于R值分布为0~8，F值分布为0~72，所以简单地对R值和F值评分如下，共分4个等级，1~4分，注意R值与评分值成反比。

-- 给R值和F值进行评分
-- 给R值进行评分
select 用户ID,R值,
(case when R值 between 0 and 1 then 4
      when R值 between 2 and 3 then 3
	  when R值 between 4 and 5 then 2
	  when R值 between 6 and 8 then 1 else 0 end) as R_score
from R值
group by 用户ID
order by R_score desc;

-- 给F值进行评分
select 用户ID,F值,
(case when F值 between 0 and 17 then 1
      when F值 between 18 and 35 then 2
	  when F值 between 36 and 53 then 3
	  when F值 between 54 and 72 then 4 else 0 end) as F_score
from F值
group by 用户ID
order by F_score desc;

7.34求出R值和F值的平均评分值

-- 计算R值与F值的平均评分值
select avg(R_score) from R_score;
select avg(F_score) from F_score;

>>得出R值分值的平均值为3.99，F值分值的平均值为1

7.34 对用户进行价值分类

根据以上得出的R值和F值的评分及其平均值，对用户进行价值分类。

-- 根据R和F的评分对用户进行价值分类
-- 统计不同价值的用户数量
select 用户价值分类,count(user_id) as 用户数量
from (select user_id,
(case when R_score>3.98 and F_score>1.004 then '重要价值客户'
      when R_score>3.98 and F_score<1.004 then '重要发展客户'
	  when R_score<3.98 and F_score>1.004 then '重要保持客户'
	  when R_score<3.98 and F_score<1.004 then '一般价值客户' end) as 用户价值分类
from (select user_id,R值,
(case when R值 between 0 and 1 then 4
      when R值 between 2 and 3 then 3
	  when R值 between 4 and 5 then 2
	  when R值 between 6 and 8 then 1 else 0 end) as R_score,F值,
(case when F值 between 0 and 17 then 1
      when F值 between 18 and 35 then 2
	  when F值 between 36 and 53 then 3
	  when F值 between 54 and 72 then 4 else 0 end) as F_score
from (select user_id,datediff('2017-12-03',max(date)) as R值,
sum(case when behavior_type='buy' then 1 else 0 end) as F值
from userbehavior where behavior_type='buy'
group by user_id) as A) as B) as C
group by 用户价值分类
order by 用户数量 desc;

 >>从结果得知一般价值客户最多，重要发展客户其次，重要价值客户和重要保持客户并不多，所以可以想办法将重要发展客户转化为重要价值客户，对重要发展用户给予优惠政策，重点可以通过发优惠券、折扣券等方式促进重要发展客户消费升级。

7.35用户价值分类名单

将不同价值的用户名单导出，供营销人员对不同的用户采取不同的营销策略，实现精准营销。

-- 用户价值分类名单
select user_id as 用户ID,用户价值分类
from (select user_id,
(case when R_score>3.98 and F_score>1.004 then '重要价值客户'
      when R_score>3.98 and F_score<1.004 then '重要发展客户'
	  when R_score<3.98 and F_score>1.004 then '重要保持客户'
	  when R_score<3.98 and F_score<1.004 then '一般价值客户'  end) as 用户价值分类
from (select user_id,R值,
(case when R值=0 then 4
      when R值=1 then 3
	  when R值 between 2 and 4 then 2
	  when R值 between 5 and 8 then 1 else 0 end) as R_score,F值,
(case when F值 between 0 and 17 then 1
      when F值 between 18 and 35 then 2
	  when F值 between 36 and 53 then 3
	  when F值 between 54 and 72 then 4 else 0 end) as F_score
from (select user_id,datediff('2017-12-03',max(date)) as R值,
sum(case when behavior_type='buy' then 1 else 0 end) as F值
from userbehavior where behavior_type='buy'
group by user_id) as A) as B) as C
group by 用户ID;

>>根据每位用户的RFM价值分类名单进行用户精细化运营。

八、总结和建议

1.从淘宝用户的点击量PV和浏览量UV出发，用户比较喜欢感兴趣的商品类型TOP3：4756105，4145813，2355072，说明用户对这些类型的商品需求比较大；所以针对以上需求最高的商品类型，销售部门要增加此类商品的投放需求量，增加该类商品广告引流。

2.淘宝的用户搜寻商品最活跃的时间段主要在下午6点至晚上11点。建议运营部门在这个时间段对这些需求高商品类型的商品多策划一些营销活动，提高销售转化率。可以集中资源在这段时间增强营销推广力度，制定优惠策略促进客户下单购买，有效促进商品销量的提升。

3.销售部门可以根据用户行为整体转化率预测未来销售的大致转化或者流失情况，为未来的商品活动评估做出预测，及时有针对性地调整营销策略。

4.通过用户购买转化路径分析，用户在加购或者收藏后的购买转化率较高，特别是加购与收藏都有行为的用户购买转化率最高，所以运营部门可以引导用户加购和收藏，比如制定优惠、促销活动等方式促进用户的购买转化率的提升，同时对接平台制定合理推送机制，降低用户点击后就直接流失的可能性

5.运营部门可以根据用户RFM价值分类名单，对于消费频率高但是近期没有消费的重要保持用户使用邮件推送、APP push的方法、活动短信提醒的方法召回。对于消费频率不高但是近期有消费的重要发展用户，应该用会员权益或者发放优惠券的形式促进消费次数。