一，理解数据

1.数据来源：

User Behavior Data from Taobao for Recommendation-数据集-阿里云天池tianchi.aliyun.com

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

2.数据集介绍

本数据集包含了2017年11月25日至2017年12月3日之间，有行为的约一百万随机用户的所有行为（行为包括点击、购买、加购、喜欢）。数据集的组织形式和MovieLens-20M类似，即数据集的每一行表示一条用户行为，由用户ID、商品ID、商品类目ID、行为类型和时间戳组成，并以逗号分隔。关于数据集中每一列的详细描述如下：

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

关于数据集大小的一些说明如下

本次只导入其中的10万条数据进行分析。

二，提出问题

问题1，分析一周用户的活跃度变化，以及一天中用户的活跃变度化。

问题2，统计用户的行为路径，并进一步分析通向购买的各个行为路径。

问题3，使用RFM分析方法对用户按价值进行分类。

三，数据清洗

先将数据用navicat导入mysql数据库。由于确认过本数据集没有命名列名的行，所以导入前先在mysql数据库里新建一个将来导入数据的表，列名依次命名为：用户ID，商品ID，商品类目ID，行为类型，时间戳。表名命名为userbehavior。

之所以把时间戳的类型设置为varchar，是因为设置为timestamp后，会全显示为“0000-00-00 00:00:00”，所以先设置为varchar，导入后再更改格式。

得到结果：

1.选择子集：本数据集所有的列都有用，所以全部保留。

2.列重命名：由于这个数据集没有列名，所以，导入前在mysql数据库里新建表时，就已经命名列名依次为：用户ID，商品ID，商品类目ID，行为类型，时间戳。无需再重命名。

3.删除重复值：不需要这一步操作。

4.缺失值处理：在导入前建立新表的时候，就把每个字段设置为了“不是null“，所以不会有缺失值。用以下sql语句进行再次确认，的确是有10万行非空数据。

5.一致化处理

由于时间戳的类型之前设置为“varchar”，导致显示不正确，现在用sql语句在原表中增加两列并分别提取日期和时间数据：

（需要注意的是，为了后面方便统计用户在一天内的活跃时间，必须要用大写H获得24小时制的时间。)

得到结果：

6.数据排序

对日期进行排序，sql语句如下：

结果如下：

7.异常值处理

数据集介绍里说明数据集包含了2017年11月25日至2017年12月3日之间的用户行为，但是从第6步对日期进行排序的结果来看，数据集里包含了2017-11-25之前的数据，这些数据要删除；同时经确认，没有2017-12-03之后的数据。所以，只对这些2017-11-25日期之前的数据进行删除。先统计这种异常数据的数量，为44条：

然后删除，sql语句如下：

（从运行结果可以看出，的确删除了44条记录）

用sql语句再次确认，剩余的的确是99956行数据：

最后，再对日期的最大最小值，以及时间的最大最小值进行查询，确认已没有异常值：

四，分析过程和数据可视化

问题1.一周用户的活跃度变化，以及一天中用户的活跃变度化。

1.1.统计一周用户的活跃度变化

这里分别用两种数据进行统计：第一种：以所有行为类型（即包括点击，加购，收藏，购买）作为活跃度指标进行统计；第二种：只用“购买”行为统计用户购买量的一周变化，毕竟最终目的是成交，所以这个统计结果也很有意义。

在所给的数据中选取完整的一周，即2017-11-27至2017-12-03的数据。

1.1.1 用所有行为类型计算活跃度

可视化：

从结果可以明显看出，周末两天的用户活跃度明显高于工作日，这与人们可支配的空闲时间成正相关。

1.1.2 仅用‘buy’行为来统计用户购买量的一周变化，SQL语句如下：

select 日期,count(行为类型) as 购买量
from (select 日期,行为类型
from userbehavior
where 日期>'2017-11-26')as 一周时间
where 行为类型='buy'
group by 日期;

运行结果：

可视化：

从购买量指标可以看出，它并没有像活跃度的一周变化那样呈现同样的趋势，而是呈现了另一种特点，购买量高点分别分布在周一、周三和周日，尤其周六反而成为一周销量的一个极小值点。对比活跃度在周六达到一周的顶点，似乎周六可被理解是顾客大量浏览商品并进行挑选的一个时间，然后在周日下单，使得周日成为购买量的一个极大值点；而周一似乎因为工作的压力而购物可以减轻压力，所以反而出现了一个销量的极大值；周三作为工作日的中场，似乎也成了成交量增加的一个时间段。

1.2 分时统计用户一天的活跃度变化。

同样用两种数据进行统计：第一种：以所有行为类型（即包括点击，加购，收藏，购买）作为活跃度指标进行统计；第二种：只用“购买”行为统计购买量变化

首先把一天分成24个时段来统计。

1.2.1 用所有行为类型来统计活跃度，sql语句如下：

select
sum(case when 时间 between '00:00:00' and '00:59:59' then 1 else  end) as '[0-1]',
sum(case when 时间 between '01:00:00' and '01:59:59' then 1 else  end) as '[1-2]',
sum(case when 时间 between '02:00:00' and '02:59:59' then 1 else  end) as '[2-3]',
sum(case when 时间 between '03:00:00' and '03:59:59' then 1 else  end) as '[3-4]',
sum(case when 时间 between '04:00:00' and '04:59:59' then 1 else  end) as '[4-5]',
sum(case when 时间 between '05:00:00' and '05:59:59' then 1 else  end) as '[5-6]',
sum(case when 时间 between '06:00:00' and '06:59:59' then 1 else  end) as '[6-7]',
sum(case when 时间 between '07:00:00' and '07:59:59' then 1 else  end) as '[7-8]',
sum(case when 时间 between '08:00:00' and '08:59:59' then 1 else  end) as '[8-9]',
sum(case when 时间 between '09:00:00' and '09:59:59' then 1 else  end) as '[9-10]',
sum(case when 时间 between '10:00:00' and '10:59:59' then 1 else  end) as '[10-11]',
sum(case when 时间 between '11:00:00' and '11:59:59' then 1 else  end) as '[11-12]',
sum(case when 时间 between '12:00:00' and '12:59:59' then 1 else  end) as '[12-13]',
sum(case when 时间 between '13:00:00' and '13:59:59' then 1 else  end) as '[13-14]',
sum(case when 时间 between '14:00:00' and '14:59:59' then 1 else  end) as '[14-15]',
sum(case when 时间 between '15:00:00' and '15:59:59' then 1 else  end) as '[15-16]',
sum(case when 时间 between '16:00:00' and '16:59:59' then 1 else  end) as '[16-17]',
sum(case when 时间 between '17:00:00' and '17:59:59' then 1 else  end) as '[17-18]',
sum(case when 时间 between '18:00:00' and '18:59:59' then 1 else  end) as '[18-19]',
sum(case when 时间 between '19:00:00' and '19:59:59' then 1 else  end) as '[19-20]',
sum(case when 时间 between '20:00:00' and '20:59:59' then 1 else  end) as '[20-21]',
sum(case when 时间 between '21:00:00' and '21:59:59' then 1 else  end) as '[21-22]',
sum(case when 时间 between '22:00:00' and '22:59:59' then 1 else  end) as '[22-23]',
sum(case when 时间 between '23:00:00' and '23:59:59' then 1 else  end) as '[23-24]'
from userbehavior;

结果：

可视化时，将一个小时内的活跃度值作为这个时段结束的整点时间对应的值：

从结果可以看出，在1-9点时段内用户活跃度较低，这符合人们的作息规律；10-19点时段内活跃度有小幅上升；过了19点，活跃度快速上升，一直到24点前的这段时间是一天中最活跃的时间段，刚好对应人们的晚饭后到睡觉前的休闲时间。

1.2.2，仅用购买行为来统计购买量，只要在上面的sql语句后再加上一条where子句：where 行为类型='buy'

select
sum(case when 时间 between '00:00:00' and '00:59:59' then 1 else  end) as '[0-1]',
sum(case when 时间 between '01:00:00' and '01:59:59' then 1 else  end) as '[1-2]',
sum(case when 时间 between '02:00:00' and '02:59:59' then 1 else  end) as '[2-3]',
sum(case when 时间 between '03:00:00' and '03:59:59' then 1 else  end) as '[3-4]',
sum(case when 时间 between '04:00:00' and '04:59:59' then 1 else  end) as '[4-5]',
sum(case when 时间 between '05:00:00' and '05:59:59' then 1 else  end) as '[5-6]',
sum(case when 时间 between '06:00:00' and '06:59:59' then 1 else  end) as '[6-7]',
sum(case when 时间 between '07:00:00' and '07:59:59' then 1 else  end) as '[7-8]',
sum(case when 时间 between '08:00:00' and '08:59:59' then 1 else  end) as '[8-9]',
sum(case when 时间 between '09:00:00' and '09:59:59' then 1 else  end) as '[9-10]',
sum(case when 时间 between '10:00:00' and '10:59:59' then 1 else  end) as '[10-11]',
sum(case when 时间 between '11:00:00' and '11:59:59' then 1 else  end) as '[11-12]',
sum(case when 时间 between '12:00:00' and '12:59:59' then 1 else  end) as '[12-13]',
sum(case when 时间 between '13:00:00' and '13:59:59' then 1 else  end) as '[13-14]',
sum(case when 时间 between '14:00:00' and '14:59:59' then 1 else  end) as '[14-15]',
sum(case when 时间 between '15:00:00' and '15:59:59' then 1 else  end) as '[15-16]',
sum(case when 时间 between '16:00:00' and '16:59:59' then 1 else  end) as '[16-17]',
sum(case when 时间 between '17:00:00' and '17:59:59' then 1 else  end) as '[17-18]',
sum(case when 时间 between '18:00:00' and '18:59:59' then 1 else  end) as '[18-19]',
sum(case when 时间 between '19:00:00' and '19:59:59' then 1 else  end) as '[19-20]',
sum(case when 时间 between '20:00:00' and '20:59:59' then 1 else  end) as '[20-21]',
sum(case when 时间 between '21:00:00' and '21:59:59' then 1 else  end) as '[21-22]',
sum(case when 时间 between '22:00:00' and '22:59:59' then 1 else  end) as '[22-23]',
sum(case when 时间 between '23:00:00' and '23:59:59' then 1 else  end) as '[23-24]'
from userbehavior
where 行为类型='buy';

查询结果：

用同样的方法可视化：

对比一天内的活跃度变化，购买量变化表现出了不同的趋势和特点。相同的地方是1-9点都是低活跃和低购买时段，而高购买量区间中的19-24点与高活跃度时段是重合的。但不同于活跃度，购买量另外出现好几个高点和区间，比如在13-14点出现了一个明显的购买量提升。

问题2：统计用户的行为路径，并进一步分析通向购买的各个行为路径。

用户所有可能的行为路径有8种路径，分别为'点击'，'点击-收藏'，'点击-收藏-加购'，'点击-收藏-加购-购买','点击-加购','点击-购买','点击-收藏-购买','点击-加购-购买'。

首先以用户ID和商品ID为唯一性分别统计四种行为（点击，加购，收藏，购买）的数量，为了方便后面使用，就创建一个视图。sql语句如下：

/*创建视图，以用户ID和商品ID为唯一性分别统计四种行为的数量*/
create view 统计行为类型 as 
select 用户ID,商品ID,sum(case when 行为类型='pv' then 1 else  end) as 'pv',
sum(case when 行为类型='cart' then 1 else  end) as 'cart',
sum(case when 行为类型='fav' then 1 else  end) as 'fav',
sum(case when 行为类型='buy' then 1 else  end) as 'buy'
from userbehavior
group by 用户ID,商品ID;

生成视图：

在这个视图基础上统计出8种用户行为路径各自的数量：

--行为路径统计
select
sum(case when pv> and fav= and cart= and buy= then 1 else  end) as '点击',
sum(case when pv> and fav> and cart= and buy= then 1 else  end) as '点击-收藏',
sum(case when pv> and fav> and cart> and buy= then 1 else  end) as '点击-收藏-加购',
sum(case when pv> and fav> and cart> and buy> then 1 else  end) as '点击-收藏-加购-购买',
sum(case when pv> and fav= and cart> and buy= then 1 else  end) as '点击-加购',
sum(case when pv> and fav= and cart= and buy> then 1 else  end) as '点击-购买',
sum(case when pv> and fav> and cart= and buy> then 1 else  end) as '点击-收藏-购买',
sum(case when pv> and fav= and cart> and buy> then 1 else  end) as '点击-加购-购买'
from 统计行为类型

结果：

可视化：

其中，通向购买的行为路径有四种，分别为：'点击-购买'，'点击-加购-购买'，'点击-收藏-购买'，'点击-收藏-加购-购买'。

可视化：

可见，在4种购买路径中，“点击-购买”路径占比最大，占到所有成交量的75.55%。它和“点击-加购-购买”路径一起共同占到了成交量的92.8%。

可进一步查找“点击-购买”路径成交的销量top 10的商品ID，sql语句如下：

select 商品ID,sum(buy) as 购买量
from 统计行为类型
where pv> and fav= and cart= and buy>
group by 商品ID
order by 购买量 desc
limit 10;

结果：

这些商品为什么成为“点击-购买”这一路径成交量最大的商品，可供进一步分析，从而优化其他产品设置。

问题3.使用RFM分析方法对用户按价值进行分类，以便对不同价值用户实行不同营销和管理策略。现有的这个数据集对做RFM分析，缺少了消费金额M的字段，但是退而求其次，也可以就目前有的数据，只进行“RF”分析。

第一步：计算R，F值：（创建视图"RF原始值"）

--创建视图"RF原始值"
create view RF原始值 as
select a.用户ID,a.R值,b.F值
from (select 用户ID,datediff('2017-12-04',日期) as R值
from userbehavior
where 行为类型='buy'
group by 用户ID) as a
 inner join (select 用户ID,count(行为类型) as F值
from userbehavior
where 行为类型='buy'
group by 用户ID) as b
on a.用户ID=b.用户ID

结果：

第二步：给R、F按价值打分

此处先建一个R、F的打分表（根据具体业务灵活调整）

SQL语句：

--给R、F按价值打分
select *,
(case when R值 between 8 and 9 then 1
when R值 between 6 and 7 then 2
when R值 between 4 and 5 then 3
when R值 between 2 and 3 then 4
when R值=1 then 5
end) as R打分,
(case when F值=1 then 1
when F值 between 2 and 3 then 2
when F值 between 4 and 5 then 3
when F值 between 6 and 8 then 4
when F值>=9 then 5
end) as F打分
from RF原始值

结果：

第三步：计算R、F的平均分

--计算R、F的平均分
select avg(R打分),avg(F打分)
from (select *,
(case when R值 between 8 and 9 then 1
when R值 between 6 and 7 then 2
when R值 between 4 and 5 then 3
when R值 between 2 and 3 then 4
when R值=1 then 5
end) as R打分,
(case when F值=1 then 1
when F值 between 2 and 3 then 2
when F值 between 4 and 5 then 3
when F值 between 6 and 8 then 4
when F值>=9 then 5
end) as F打分
from RF原始值) as c

结果：

第四步：将第二步得到的值与第三步得到的值做对比，大于相应平均值被认为相当于”高“，小于则被认为相当于”低“。根据下面的表格，据此将用户分为四类。

1）先分别统计各类用户的数量：

select sum(重要价值用户),sum(重要发展用户),sum(重要保持用户),sum(重要挽留用户)
from (select 用户ID,
(case when R值>2.3532 and F值>2.1207 then 1 else  end) as 重要价值用户,
(case when R值>2.3532 and F值<2.1207 then 1 else  end) as 重要发展用户,
(case when R值<2.3532 and F值>2.1207 then 1 else  end) as 重要保持用户,
(case when R值<2.3532 and F值<2.1207 then 1 else  end) as 重要挽留用户
from (select *,
(case when R值 between 8 and 9 then 1
when R值 between 6 and 7 then 2
when R值 between 4 and 5 then 3
when R值 between 2 and 3 then 4
when R值=1 then 5
end) as R打分,
(case when F值=1 then 1
when F值 between 2 and 3 then 2
when F值 between 4 and 5 then 3
when F值 between 6 and 8 then 4
when F值>=9 then 5
end) as F打分
from RF原始值) as RF打分表) as 用户分类

结果：

可视化：

从结果可以看出，有购买行为的客户中，大多数都是重要价值用户和重要发展用户。

2）然后可进一步查询出每个类别的用户ID都是哪些。

为方便查询每个用户类别，首先创建一个视图：用户分类表

--创建视图：用户分类表
create view 用户分类表 as
select 用户ID,
(case when R值>2.3532 and F值>2.1207 then 1 else  end) as 重要价值用户,
(case when R值>2.3532 and F值<2.1207 then 1 else  end) as 重要发展用户,
(case when R值<2.3532 and F值>2.1207 then 1 else  end) as 重要保持用户,
(case when R值<2.3532 and F值<2.1207 then 1 else  end) as 重要挽留用户
from (select *,
(case when R值 between 8 and 9 then 1
when R值 between 6 and 7 then 2
when R值 between 4 and 5 then 3
when R值 between 2 and 3 then 4
when R值=1 then 5
end) as R打分,
(case when F值=1 then 1
when F值 between 2 and 3 then 2
when F值 between 4 and 5 then 3
when F值 between 6 and 8 then 4
when F值>=9 then 5
end) as F打分
from RF原始值) as RF打分表

视图如下：

接下来比如想要查询”重要价值用户“的用户ID是哪些，用SQL语句：

--查询"重要价值用户"是哪些
select 用户ID
from 用户分类表
where 重要价值用户=1;

结果：

查询出的结果可以导出到excel表格里。

类似的可以方便的查找出”重要发展用户“，”重要保持用户“，”重要挽留用户“的用户ID都是哪些。

五，结论和建议

一，因为用户的活跃度和购买量在同一时段（分别统计一周和一天）变化的趋势不一致，可以在活跃度大的时间段比如周六进行商品宣传和推出促销活动，或者发布新品；而在购买量大的时间，比如周一、周三、周日，以及一天中的13-14点，19-24点，发送限当时时段使用的优惠券或红包，以促成交易提升销量。

二：通过对用户购买的4种路径占总成交量的百分比分析得出，“点击-购买”所占比例最大，占到了约75%，可见，第一印象很重要，顾客对商品表现出了buy it now or never的倾向，所以可进一步分析这个路径成交的TOP 10的商品ID，分析它商品详情页设计的优点（比如商品图片，商品介绍文案、价格设置、产品套餐设计）以及它的用户评价等会影响购买行为的因素，来优化别的商品。总之要抓住用户点击的机会，让用户有立即购买的冲动，以达到提升销量的目的；同时，为了提高”点击-加购-购买“路径的实现率，要设置优惠活动，鼓励用户把商品加入购物车.

三，从RFM的分析结果可以看出，有购买行为的客户中，“重要价值用户”和“重要发展用户”占了大部分，说明用户质量还是不错的。同时可以看出“重要发展用户”的占比最大，所以除了维护好“重要价值用户”外，要将重点放在将“重要发展用户”转化为“重要价值用户”上。

ITPub博客

从零学会sql--第八关项目实战

最新文章

ITPub博客

从零学会sql--第八关 项目实战

最新文章

从零学会sql--第八关项目实战