一、项目背景

UserBehavior是阿里巴巴提供的一个淘宝用户行为数据集，用于隐式反馈推荐问题的研究。

构造推荐系统的关键资源是用户历史行为数据，具体可分为两类：显式反馈和隐式反馈。其中，隐式反馈是指不直接表现用户倾向的历史数据，如浏览网站、购买商品等。

二、分析思路

2.1 分析目的

通过对数据集中的用户数据、行为数据及商品数据进行分析，找出各个环节存在的问题，为提升销 量 提供合理的建议 。

2.2 明确问题

1. 了解获客情况和留存情况

2. 基于漏斗模型分析用户从浏览到购买整个过程的转化率情况

3. 观察用户四种行为的变化趋势（按天和按小时）

4. 分析浏览量、购买量前10的商品以及商品类目，优化产品销售

5. 基于RFM模型对用户价值进行分析

2.3 分析框架

三、数据说明

3.1 数据来源

阿里云天池 ： 数据集-阿里云天池 (aliyun.com)

3.2 理解数据

数据集包含了2017年11月25日至2017年12月3日之间，有行为的约一百万随机用户的所有行为（行为包括点击、购买、加购、喜欢）。数据集大小说明：用户数量（987,994），商品数量（4,162,024），用户数量（987,994），商品类目数量（9,439），总的淘宝用户行为记录数量为1亿条（100,150,807）。

数据集的每一行表示一条用户行为，由用户ID、商品ID、商品类目ID、行为类型和时间戳组成，并以逗号分隔。关于数据集中每一列的详细描述如下：

用户行为类型共有四种，它们分别是：

四、数据预处理

由于数据量级达到一亿，考虑到电脑性能问题，故本次选取其中的40万+的数据进行分析。

4.1 选择子集

数据集中五个字段都具有价值，全部保留。

4.2 列名重命名

原数据集没有表头，用sql语句创建表及5个字段，把淘宝用户行为数据导入MySQL数据库。

4.3 删除重复值

不存在重复值。

4.4 缺失值处理

不存在缺失值。

4.5 一致化处理

将时间戳整理成日期和小时：

①新增字段datetime，用函数from_unixtime 把整数型的timestamps改成日期型的timestamps

②新增字段dates，times，hours

4.6 异常值处理

检查日期是否在规定范围内：2017年11月25日至2017年12月3日。

一共删除异常值184条。

五、数据分析

5.1 获客情况

按日分析pv、uv、pv/uv：

• pv：页面浏览量
• uv：独立访客数
• pv/uv：浏览深度

#pv  uv  pv/uv
select dates, count(*) 'pv', count(distinct userid) 'uv',
round(count(*)/count(distinct userid),1) 'pv/uv'
from user_behavior
where behaviortype='pv'
group by dates
order by dates;

​​​​​输出结果（代码结果以及Excel图示）：

从上图可以看出，pv、uv 这两个指标的变化趋势几乎保持一致。在2017年11月25日至2017年12月1日之间，都是较为稳定的，而在2017年12月2日这天开始，pv和uv都不断增加。我们依此提出以下两个假设：

假设1 ：非工作日的pv和uv会得到大幅度提升。 验证假设1 ：通过查看日历发现，11月25日和11月26日也是非工作日，但pv和uv并没有得到大幅度的提升，所以假设1不成立。

假设 2 ：可能是双十二大促的影响。 验证假设2 ：12月2日和12月3日距离“双十二”活动较近，可能是双 12 活动的预热阶段，吸引了一部分新老用户，故假设2成立。

5.2 留存情况

5.2.1 留存率

留存率 是指在统计周期内，每日活跃用户数在第N日仍启动该APP的用户数占比的平均值。留存率常反映用户粘性，当N取值越大、留存率越高时，用户粘性越高。 （这里只统计次日留存率）

-- 留存数
select a.dates, 
count(if(datediff(b.dates, a.dates)=0, b.userid, null)) retention_0, 
count(if(datediff(b.dates, a.dates)=1, b.userid, null)) retention_1
from(select userid, dates from user_behavior
group by userid, dates) a,
(select userid, dates from user_behavior
group by userid, dates) b
where a.userid = b.userid and a.dates <= b.dates
group by a.dates;

-- 留存率
select a.dates, 
count(if(datediff(b.dates, a.dates)=1, b.userid, null))/count(if(datediff(b.dates, a.dates)=0, b.userid, null)) retention
from(select userid, dates from user_behavior
group by userid, dates) a,
(select userid, dates from user_behavior
group by userid, dates) b
where a.userid = b.userid and a.dates <= b.dates
group by a.dates;

当日活跃人数和次日活跃人数结果如下：

次日留存率结果如下：

5.2.2 跳失率

跳失率是指在统计周期内，仅访问APP一次的用户数量占总用户浏览量的比例。

select count(*) '跳失用户数' from
(select userid from user_behavior
group by userid
having count(behaviortype)=1) a

结果如下：

查询可得在统计周期内，仅访问APP一次的用户数量为0，也就是说跳失率为 0 ，说明淘宝有足够的吸引力让用户停留。

5.3 用户行为漏斗分析

5.3.1 独立访客行为漏斗

select behaviortype, count(distinct userid) '用户数'
from user_behavior 
group by behaviortype
order by userid desc;

结果如下：

从独立访客为分析维度，可计算得到，用户并没有在点击以后大量流失，APP约有 69.69% 的付费用户， 用户付费转化率相当高 ，说明APP可以满足大多数用户的需求。

5.3.2 用户总行为漏斗

select behaviortype, count(*) '行为数'
from user_behavior
group by behaviortype
order by 行为数 desc;

得出结果：

此时我们不能直接将 购买/收藏加购 作为 收藏加购到购买的转化率 。 因为 这里的购买量有些是浏览后直接购买的，有些是浏览后加购收藏再购买的，如果我们直接 购买/收藏加购 得出转化率是不够准确的，所以这里我们对这段时间 用户购买行为的行为路径 进行分析。为此，我们需要对 用户id和商品id 进行分组， 统计两者之间的发生的各类行为的数量 ，并保存为视图以便后面调用。

----用户行为路径
create view user_behavior_view as 
select userid, itemid,
count(if(behaviortype='pv', behaviortype, null)) 'pv',
count(if(behaviortype='fav', behaviortype, null)) 'fav',
count(if(behaviortype='cart', behaviortype, null)) 'cart',
count(if(behaviortype='buy', behaviortype, null)) 'buy'
from user_behavior
group by userid, itemid;

结果如下：

为了更好的统计，先将用户行为标准化，将分组统计的结果进一步处理。

----用户行为标准化
create view user_behavior_standard as
select userid, itemid, 
(case when pv>0 then 1 else 0 end) '浏览了',
(case when fav>0 then 1 else 0 end) '收藏了',
(case when cart>0 then 1 else 0 end) '加购了',
(case when buy>0 then 1 else 0 end) '购买了'
from user_behavior_view;

----形成购买路径类型
create view user_behavior_path as
select *, concat(浏览了,收藏了,加购了,购买了) '购买路径类型'
from user_behavior_standard as a
where a.购买了>0;

----统计各类购买路径的数量
create view path_count as
select 购买路径类型, count(*) '数量'
from user_behavior_path
group by 购买路径类型
order by 数量 desc;

---- 建表为购买路径类型添加注释
create table zhushi(
path_type char(4),
descriptions varchar(40));
insert into zhushi values
('0001','直接购买了'),
('1001','浏览后购买了'),
('0101','收藏后购买了'),
('0011','加购后购买了'),
('1101','浏览收藏后购买了'),
('1011','浏览加购后购买了'),
('0111','收藏加购后购买了'),
('1111','浏览收藏加购后购买了');

select * from zhushi;

创建视图存储 各路径行为的数据 ：

-- 创建视图存数据
create view path_result as
select * from path_count p
join zhushi z
on p.购买路径类型 = z.path_type
order by 数量 desc;

分析漏斗转化率：

在购物环节中收藏和加入购物车都是确定购物意向的行为，没有先后之分，所以将这两个环节合并为购物环节中的一步。

-- 统计没有收藏加购就购买的购买行为数量
select sum(buy) from user_behavior_view
where buy>0 and fav=0 and cart=0;
----- 得出结果 6541

-- 求转化率
select 8602-6541 as '收藏加购后购买的行为数';
----- 得出结果2061
select 10449+23065 as '收藏加购总数';
----- 得出结果33514
select 2061/33514;
----- 收藏加购后购买/收藏加购总数=收藏加购到购买的转化率 6.15% 
select 33514/374309;
----- 收藏加购总数/浏览总数=浏览到收藏加购的转化率 8.95%

我们统计出没有收藏加购就购买的购买行为数量为6541，将所有的购买行为数（所有的购买行为数前面已求出，为8602）减去6541，就可以求出 收藏加购后再购买的行为数量 为 2061 。根据计算我们可以得出以下结果：

我们只对 左半边 进行Excel可视化得到漏斗图如下：

我们已得出，从浏览量到直接购买转化率是 1.75% ，而浏览后有加购收藏行为的购买转化率是 6.15% ，所以 顾客有收藏加购的行为之后，会提升转化率 ，所以我们需要从产品交互界面、营销机制等方面让用户去多加购，多收藏。

从用户的行为转化漏斗图来看，用户在浏览商品后，进入到加入购物车或收藏商品环节的比例为 8.95% ，在加购收藏商品后进入购买商品的转化率为 6.15% ； 转化率都很低 。

针对现状，这里提出 两个假设 ： 假设1 . 用户只是浏览商品而不购买商品； 假设2 . 平台推荐的产品不精准，并非用户想购买的产品，导致用户浏览后没有成功转化。

对于 假设1 ：从独立访客的分析维度，69.69%的用户是有购买行为的，与浏览到直接购买的转化率1.75%和浏览加购收藏到购买的转化率6.15%相比高很多，因此不存在大量用户只逛不购买的行为，所以假设1不成立。

对于 假设2 ，我们在商品维度分析给出验证。

5.4 从时间维度分析用户行为

5.4.1 按日分析用户的行为习惯

select dates,
count(if(behaviortype='pv', behaviortype, null)) 'pv',
count(if(behaviortype='cart', behaviortype, null)) 'cart',
count(if(behaviortype='fav', behaviortype, null)) 'fav',
count(if(behaviortype='buy', behaviortype, null)) 'buy'
from user_behavior
group by dates
order by dates;

从以上数据来看，从11月25日-12月1日之间，各项指标都是比较平稳，而在12月2日-3日之间急剧上升，并达到了最大值。可能是受双十二预热活动的影响，导致用户数急剧上升。

5.4.2 按小时分析用户的行为习惯

select hours,
count(if(behaviortype='pv', behaviortype, null)) 'pv',
count(if(behaviortype='cart', behaviortype, null)) 'cart',
count(if(behaviortype='fav', behaviortype, null)) 'fav',
count(if(behaviortype='buy', behaviortype, null)) 'buy'
from user_behavior
group by hours
order by hours;

经过对一天中用户行为分布的可视化，可以发现每日0点至4点用户活跃度快速降低，降到一天中活跃度最低值，5点至10点用户活跃度快速上升，10点至18点用户活跃度较平稳，但分别在12点及17点略有下降，18点后用户活跃度开始快速上升，并在20-22时达到一天中用户活跃度的最高值，这也符合大部分人的作息规律。所以，可以考虑在20-22点这个时间段进行一些促销活动和商品推荐以提高转化率。同时，白天10-13点这个时间段的购买比率也是比较高的，浏览量高的在晚上21点左右，可能是大家觉得就算晚上购买商品也是第二天发货，所以有些用户也会选择第二天白天进行下单购买。

5.5 从商品维度分析用户行为

5.5.1 按热度分类

统计浏览量前十和购买量前十的商品品类（热门品类）：

-- 浏览量前十的品类
select categoryid, 
count(if(behaviortype='pv', behaviortype, null)) '品类浏览量'
from user_behavior
group by categoryid
order by 2 desc
limit 10;

-- 购买量前十的品类
select categoryid, 
count(if(behaviortype='buy', behaviortype, null)) '品类购买量'
from user_behavior
group by categoryid
order by 2 desc
limit 10;

结果如下：

从图中发现淘宝推荐的品类中有 40% 的商品类目是人们感兴趣想购买的。

统计浏览量前十和购买量前十的商品（热门商品）：

-- 浏览量前十的商品
create view popular_items_pv as
select itemid, 
count(if(behaviortype='pv', behaviortype, null)) '商品浏览量'
from user_behavior
group by itemid
order by 2 desc
limit 10;
select * from popular_items_pv;

-- 购买量前十的商品
create view popular_items_buy as
select itemid, 
count(if(behaviortype='buy', behaviortype, null)) '商品购买量'
from user_behavior
group by itemid
order by 2 desc
limit 10;

结果如下：

浏览量前十的商品的购买量均没有前十 ，同时，排名前十的商品购买量都在 10以下 ，也就是说高浏览量和高购买量是两类不同的商品，所以平台给予的流量顾客的点击是高的，但是这个产品所产生的销售没有，说明平台推荐的商品有些问题，重点推荐的商品并不是用户最想买的商品。故 验证了前面的假设2 ，即 平台推荐的产品不精准，并非用户想购买的产品，导致用户浏览后没有成功转化 。 我们发现2735466、1464116、4145813这三类商品品类购买量比较高，在高需求量的基础上我们考虑淘宝调整推送机制，增加对这几类商品的推送，以满足用户需求，同时 优化用户搜索商品的推荐算法，提升用户转化率。

5.5.2 商品销量分析

-- 商品总数
select count(distinct itemid)
from user_behavior;
------ 211168

-- 有购买量的商品数
select count(distinct itemid)
from user_behavior
where behaviortype = 'buy';
------ 7811

--- 各商品的购买量
select itemid, count(userid) '商品购买量'
from user_behavior
where behaviortype = 'buy'
group by itemid
order by 2 desc;

--- 商品购买量的对应的商品数量，即购买1次的商品有多少...
select 商品购买量, count(*) '商品数'
from (select itemid, count(userid) '商品购买量'
from user_behavior
where behaviortype = 'buy'
group by itemid) as a
group by 商品购买量
order by 商品购买量;

经统计，本次分析的商品共有211168种，用户购买的商品共有7811种； 只购买一次 的商品有 7175 种，占用户购买商品数的 91.86% ，说明商品售卖主要依靠长尾商品的累积效应，而非爆款商品的带动。

5.6 基于RFM模型的用户价值分析

RFM是最近一次消费时间间隔（Recency），消费频率（Frequency），消费金额（Monetary）这三个指标的缩写。接下来基于RFM模型对用户分类，由于我们的数据没有消费金额这一指标，所以我们只考虑R和F。

分析思路 是 对用户按照最近一次消费和消费次数分别给予R值和F值的分值，用用户的平均R值和平均F值将用户分成四大类。即① 计算最近购买时间以及购买次数；② 建表rfm_model，并将①步得到的数据插入；③ 根据购买次数、最近购买时间对用户进行分层：新增两列，对刚才得到的购买次数以及最近购买时间给用户进行打分；④ 定义两个变量存储r和f值的平均值，用它们的平均值将用户分成四象限；⑤ 将④求出的结果class列插入rfm_model；⑥ 统计各区的用户数。

# RFM模型
-- 1.计算购买次数，最近购买时间
select userid, count(userid) '购买次数', max(dates) '最近购买时间'
from user_behavior
where behaviortype = 'buy'
group by userid
order by 2 desc, 3 desc;

-- 2.建表存储数据
drop table if exists rfm_model;
create table rfm_model(
userid int,
frequency int,
recent char(10));

insert into rfm_model
select userid, count(userid) '购买次数', max(dates) '最近购买时间'
from user_behavior
where behaviortype = 'buy'
group by userid
order by 2 desc, 3 desc;

给R和F按价值打分：

用户分类：

-- 3.根据 购买次数、最近购买时间 对用户进行分层
-- 新增一列fscore
alter table rfm_model add column fscore int;

update rfm_model
set fscore = case
when frequency between 20 and 57 then 5
when frequency between 15 and 19 then 4
when frequency between 10 and 14 then 3
when frequency between 5 and 9 then 2
else 1
end;

-- 新增一列rscore
alter table rfm_model add column rscore int;

update rfm_model
set rscore = case
when recent = '2017-12-03' then 5
when recent in ('2017-12-01','2017-12-02') then 4
when recent in ('2017-11-29','2017-11-30') then 3
when recent in ('2017-11-27','2017-11-28') then 2
else 1
end;

-- 4.定义两个用户变量存储r和f值的平均值
set @f_avg = null;
set @r_avg = null;
select avg(fscore) into @f_avg from rfm_model;
select avg(rscore) into @r_avg from rfm_model;

select *, (case
when fscore > @f_avg and rscore > @r_avg then '价值用户'
when fscore > @f_avg and rscore < @r_avg then '保持用户'
when fscore < @f_avg and rscore > @r_avg then '发展用户'
when fscore < @f_avg and rscore < @r_avg then '挽留用户'
end) class
from rfm_model;

-- 5.将结果class列插入rfm_model表中
alter table rfm_model add column class varchar(40);
update rfm_model 
set class = case
when fscore > @f_avg and rscore > @r_avg then '价值用户'
when fscore > @f_avg and rscore < @r_avg then '保持用户'
when fscore < @f_avg and rscore > @r_avg then '发展用户'
when fscore < @f_avg and rscore < @r_avg then '挽留用户'
end;

-- 6.统计各区的用户数
select class, count(userid)
from rfm_model
group by class;

结果如下：

发展用户和挽留用户占比最大，分别占43.18%和39.12%

六、结论与建议

（1）从用户行为路径中发现，用户浏览后直接购买的转化率较低，而加购收藏后再购买的转化率有所上升。

建议 ：需积极引导顾客加购物车或者收藏宝贝，同时收集用户画像，进行精准推荐；对于界面设计应考虑如何交互能够让顾客更愿意点击。

（2）从日期维度来看，每天的各项指标（pv、cart、fav、buy）都是比较平稳的，而在12月2日和12月3日急速上升，可能是受到双十二预热的影响；从时间维度来看，用户的各种行为活跃度高峰期都在晚间的21点左右，同时白天10-13点的购买比率也是比较高的。

建议 ：可以考虑在20-22点这个时间段进行一些促销活动和商品推荐，此时使用人数最多， 活动容易触达用户 。 平台可以利用这个时间段投放广告，加大力度宣传产品，可以根据个性化推荐算法，向用户推送用户喜欢的新品及畅销品，同时推送商家折扣优惠、直播带货及促销活动信息， 进而达到促进用户尽快购买的效果 ；

（3）从热门品类看，发现淘宝推荐的品类中有 40% 的商品类目是人们感兴趣想购买的；从热门商品看，浏览量排名前十的商品均没有在购买量前十，说明选择引流的商品不是用户喜欢的；从商品销售情况分析，只购买过一次的商品数量有7175种，占用户购买商品总数的91.86%，说明商品售卖主要依靠长尾商品的累积效应。

建议 ：我们发现2735466、1464116、4145813这三类商品品类购买量比较高，在高需求量的基础上我们考虑淘宝调整推送机制，增加对这几类商品的推送，以满足用户需求，同时 优化用户搜索商品的推荐算法，提升用户转化率。 根据购买量高的商品，可重新分配流量以及多推出一些和该类目/商品相关的其他类目/商品捆绑交叉销售，提高销量。

（4）从用户价值看，对用户进行分类，发现发展用户和挽留用户占比最大，分别占43.18%和39.12%。

​​​​​​​ 建议 ：针对各类用户进行对应的营销。对于 发展用户 ，其消费频率低，但最近消费距离现在时间较短，因此要想办法提高他的消费频率，例如 可以通过发放优惠券 、 上新消息送达等，提高用户的粘性。 ​​​​​​​对于 挽留用户 ，最近消费时间距离现在较远、消费频率低。这种用户有即将流失的危险，应采取挽留措施，主动联系用户，明确流失原因，了解用户需求。对于 价值用户 ，消费频率高且最近消费距离现在时间短，可以其提供VIP服务，提升用户忠诚度。对于 保持用户 ，最近消费距离现在时间较远，也就是R值低，但是消费频次高。这样的用户，是一段时间没来的忠实客户，可以采取邮件推送、APP推送提醒、促销活动时短信提醒等方式主动和他们保持联系，提高其复购率。