淘宝用户购物行为分析

一、项目背景

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

二、项目目标

1、分析目的

为客户提供更精准的隐式反馈推荐；提供用户忠诚度，帮助用户快速找到商品；提高网站交叉销售能力，提高成交转化率。

2、分析思路

    用户分析：以pv、uv等指标，找出用户最活跃的日期以及每天最活跃的时间段

                      分析用户从浏览到购买整个过程中，各个环节的转化率如何  

                      通过RFM模型，将用户进行分类，并计算占比情况

    商品分析：高流量和高销量的商品和品类有哪些，其转化率如何

三、数据收集与整理

1.数据获取

       数据来源：淘宝用户购物行为数据集_数据集-阿里云天池

       由于数据量级很大，所以可通过kettle进行导入，本次分析选取200万条记录进行分析

2.数据清洗和预处理

        ①查看字段名：

desc user_behavior

        ②检查空值：

select * from user_behavior where user_id is null;
select * from user_behavior where item_id is null;
select * from user_behavior where category_id is null;
select * from user_behavior where behavior_type is null;
select * from user_behavior where timestamps is null;

      没有空值    

        ③检查重复值：

select user_id,item_id,timestamps 
from user_behavior
group by user_id,item_id,timestamps
having count(*)>1;

       三个主键字段均无重复值

        ④一致化处理：

             目前时间列可读性差，增加三个字段：date、time、hour

alter table user_behavior add datetimes TIMESTAMP(0);
update user_behavior set datetimes=FROM_UNIXTIME(timestamps);

alter table user_behavior add dates char(10);
alter table user_behavior add times char(8);
alter table user_behavior add hours char(2);

update user_behavior set dates=substring(datetimes,1,10);
update user_behavior set times=substring(datetimes,12,8);
update user_behavior set hours=substring(datetimes,12,2);

        ⑤去异常：

                检查日期是否都在需要分析的时间范围内：

select max(datetimes),min(datetimes) 
from user_behavior;

               剔除时间范围外的数据：

delete from user_behavior
where datetimes < '2017-11-25 00:00:00'or datetimes > '2017-12-03 23:59:59';

四、数据分析

        这一环节的所有结果均为先使用sql分析数据，获得分析结果，然后将分析结果导入到tableau中进行数据可视化。

（一）用户分析

     1.获客情况

         ①页面浏览量

select dates,count(*) as 'pv'    
from user_behavior   
where behavior_type='pv'    
GROUP BY dates    
order by dates;

         ② 独立访客数

select dates,count(distinct user_id) as 'uv'    
from user_behavior  
where behavior_type='pv'    
GROUP BY dates    
order by dates;

         ③浏览深度

select dates,
count(*) as 'pv',
count(distinct user_id) as 'uv',
round(count(*)/count(distinct user_id),1) as 'pv/uv'    
from user_behavior  
where behavior_type='pv'    
GROUP BY dates    
order by dates

     

        页面浏览量和访客数在12.2-12.3迎来明显增长 

     2.留存情况

        ①留存率(因为只有9天的数据，所以主要计算次日留存率）

-- 列出用户id和日期
select user_id,dates    
from user_behavior   
group by user_id,dates;

-- 自连接 筛选
select *    
from    
(select user_id,dates    
from user_behavior    
group by user_id,dates) as a    
inner join    
(select user_id,dates    
from user_behavior    
group by user_id,dates) as b    
on a.user_id=b.user_id 
where a.dates<=b.dates;

-- 留存数
select a.dates,count(if(DATEDIFF(b.dates,a.dates)=0,b.user_id,null)) as ren_0 ,
               count(if(DATEDIFF(b.dates,a.dates)=1,b.user_id,null)) as ren_1   
from    
(select user_id,dates    
from user_behavior    
group by user_id,dates) as a    
inner join    
(select user_id,dates    
from user_behavior    
group by user_id,dates) as b    
on a.user_id=b.user_id
where a.dates<=b.dates
group by a.dates;

-- 留存率
select a.dates,       
 count(if(DATEDIFF(b.dates,a.dates)=1,b.user_id,null))/count(if(DATEDIFF(b.dates,a.dates)=0,b.user_id,null)) as num
from    
(select user_id,dates    
from user_behavior    
group by user_id,dates) as a    
inner join    
(select user_id,dates    
from user_behavior    
group by user_id,dates) as b    
on a.user_id=b.user_id
where a.dates<=b.dates
group by a.dates

       

       ②跳失率(只点击一次的用户数量/总浏览用户量）

select count(*)
from(select user_id
	 from user_behavior
     group by user_id
     having count(behavior_type)=1) a

    跳失率为0，说明商品对用户有比较强的吸引力。

     3.行为情况

        ①时间序列分析

-- 统计日期-小时的行为
select dates,hours,
       count(if(behavior_type="pv",behavior_type,null)) pv,
       count(if(behavior_type="cart",behavior_type,null)) cart,
       count(if(behavior_type="fav",behavior_type,null)) fav,
       count(if(behavior_type="buy",behavior_type,null)) buy
from user_behavior
group by dates,hours
order by dates,hours

       

       在研究日期范围内，用户的活跃度都比较稳定，12月2日及12月3日有相对明显的增幅，由于该日期与11月25及26日同为周末，故由于周末的原因导致用户活跃度提升的原因较小，而且12月2日及3日浏览、收藏及加购都有较明显增长，但购买并未有明显增幅，故推测最可能原因是由于双十二预热活动导致的流量上升，用户开始大量浏览商品，加入购物车及收藏是双十二批量购买时的前置动作。故收藏和加购的行为同样出现了增长，这也符合常规预期。

       每日0点至4点用户活跃度快速降低，在4点达到最低值，4点至10点用户活跃度快速上升，10点至18点用户活跃度较平稳，18点后用户活跃度开始快速上升，并在21-22时达到一天中用户活跃度的最高值，这也符合大部分人的作息规律。所以，可以考虑在20-22点这个时间段进行一些促销活动和商品推荐以提高转化率。

        虽然晚上的浏览量最大，但11点左右购买率最大，可见，在这一时间段的用户使用淘宝下单购买商品的意向是最高的。收藏加购率最大的是在5点和23点。

        ②用户转化率分析

                                                                                             

        有些人是直接购买；有些人是收藏加购后购买；有的是收藏加购后，下一次直接购买。
所以你想要得到收藏加购后购买很难，这里采取的做法是，直接算浏览后购买的，就是直接购买，然后再算收藏加购后购买。

-- 统计各类行为用户数
select behavior_type,count(distinct user_id)
from user_behavior
group by behavior_type
order by behavior_type desc;

-- 统计各类行为的数量
select behavior_type,count(*)
from user_behavior
group by behavior_type
order by behavior_type desc

           购买过商品的用户的比例：13330/19463=68.5%

           购买率：40243/1790225=2.25%

           收藏加购率：（111015+57526）/1790225=9.41%

       用户并未在点击后就大量流失,而且有68.5%的付费用户，用户的购买转化率还是很不错的。

       购买率仅有2.25%，可能是用户要对不同店铺的同种产品进行比较。所以针对大部分的用户行为还是对商品点击详情页访问的情况，研究如何提高用户点击后到其他行为的转化是一个重点。APP可以优化商品的搜索以及推荐等功能，使用户不用浏览那么多的网页，用更少的选择获得心怡的商品。

        ③行为路径分析

create view user_behavior_view as
select user_id,item_id
,count(if(behavior_type='pv',behavior_type,null)) 'pv'
,count(if(behavior_type='fav',behavior_type,null)) 'fav'
,count(if(behavior_type='cart',behavior_type,null)) 'cart'
,count(if(behavior_type='buy',behavior_type,null)) 'buy'
from user_behavior
group by user_id,item_id;

-- 用户行为标准化
create view user_behavior_standard as
select user_id,item_id
,(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
where 购买了>0;

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

-- 人话表
create table renhua(
path_type char(4),
description varchar(40));

insert into renhua 
values('0001','直接购买了'),
('1001','浏览后购买了'),
('0011','加购后购买了'),
('1011','浏览加购后购买了'),
('0101','收藏后购买了'),
('1101','浏览收藏后购买了'),
('0111','收藏加购后购买了'),
('1111','浏览收藏加购后购买了');

create table path_result(
path_type char(4),
description varchar(40),
num int);

insert into path_result
select path_type,description,数量 
from path_count p
join renhua r on p.购买路径类型=r.path_type
order by 数量 desc;

-- 购买，但没收藏加购的购买数量
select sum(buy)
from user_behavior_view
where buy>0 and fav=0 and cart=0

     4.用户定位

          RFM模型

-- 最近购买时间 
select user_id,max(dates) '最近购买时间'
from user_behavior
where behavior_type='buy'
group by user_id
order by 2 desc;

-- 购买次数 
select user_id,count(user_id) '购买次数'
from user_behavior
where behavior_type='buy'
group by user_id
order by 2 desc;

-- 统一 
select user_id,count(user_id) '购买次数',max(dates) '最近购买时间'
from user_behavior
where behavior_type='buy'
group by user_id
order by 2 desc,3 desc;

-- 存储
create table rfm_model(
user_id int,
frequency int,
recent char(10)
);

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

-- 根据购买次数对用户进行分层 
alter table rfm_model add column fscore int;

update rfm_model
set fscore = case
when frequency between 41 and 72 then 5
when frequency between 21 and 40 then 4
when frequency between 11 and 20 then 3
when frequency between 6 and 10 then 2
else 1
end;

-- 根据最近购买时间对用户进行分层 
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;

-- 分层
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;

-- 插入
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;

-- 统计各分区用户数
select class,count(user_id) from rfm_model
group by class

       价值用户是最重要的用户群体，但数量较少，只有9.75%，挽留用户占比40.45%，需根据用户群体特点针对性采取运营措施。

       价值用户：是最优质的用户群体，应重点关注，既要保持其粘性，又要继续引导消费，可为其提供优质专属服务，提高其消费体验，增加客户忠诚度。

       发展用户：可以进行定向推广或提供价格优惠，提高其消费频次。

       保持用户：最近消费的时间较为久远，可能存在流失，但以前的消费频率较高，需了解具体长时间未消费的原因，针对性提供对策。

       挽留用户：长时间未进行消费且消费频率低，可主动联系或举行老客户召回活动，尽可能挽留。

（二）商品分析

      1.商品按热度分类

-- 统计商品的热门品类、热门商品、热门品类热门商品
select category_id,count(if(behavior_type='pv',behavior_type,null)) '品类浏览量'
from user_behavior
GROUP BY category_id
order by 2 desc
limit 10;

select item_id,count(if(behavior_type='pv',behavior_type,null)) '商品浏览量'
from user_behavior
GROUP BY item_id
order by 2 desc
limit 10;

select category_id,item_id,
品类商品浏览量 from
(
select category_id,item_id
,count(if(behavior_type='pv',behavior_type,null)) '品类商品浏览量'
,rank()over(partition by category_id order by count(if(behavior_type='pv',behavior_type,null)) desc) r
from user_behavior
GROUP BY category_id,item_id
order by 3 desc
) a
where a.r = 1
order by a.品类商品浏览量 desc
limit 10;

--统计高销量商品和品类
select category_id
,count(if(behavior_type='buy',behavior_type,null)) '品类购买量'
from user_behavior
GROUP BY category_id
order by 2 desc
limit 10;

select item_id
,count(if(behavior_type='buy',behavior_type,null)) '商品购买量'
from user_behavior
GROUP BY item_id
order by 2 desc
limit 10;

       浏览量前十的商品类目与购买量前十的商品类目符合度为40%，而进一步对商品进行分析发现浏览量前十的商品与用户喜欢购买的前十商品符合度为10%，说明购买量与浏览量的相关性比较差，大量的浏览并没有提高特别多的购买率。

      2.商品转化率分析

-- 特定商品转化率
select item_id
,count(if(behavior_type='pv',behavior_type,null)) 'pv'
,count(if(behavior_type='fav',behavior_type,null)) 'fav'
,count(if(behavior_type='cart',behavior_type,null)) 'cart'
,count(if(behavior_type='buy',behavior_type,null)) 'buy'
,count(distinct if(behavior_type='buy',user_id,null))/count(distinct user_id) 商品转化率
from user_behavior
group by item_id
order by 商品转化率 desc;

-- 品类转化率
select category_id
,count(if(behavior_type='pv',behavior_type,null)) 'pv'
,count(if(behavior_type='fav',behavior_type,null)) 'fav'
,count(if(behavior_type='cart',behavior_type,null)) 'cart'
,count(if(behavior_type='buy',behavior_type,null)) 'buy'
,count(distinct if(behavior_type='buy',user_id,null))/count(distinct user_id) 品类转化率
from user_behavior
group by category_id
order by 品类转化率 desc；

      3.商品特征分析

        点击量高，购买量高，说明此类产品为刚需产品，而且品类多，种类丰富，用户在较高的需求下，有很多的选择；点击量低，购买量高，说明用户购买十分果断，且需求量也很大，可能产品选择性很小，或者存在几个品牌垄断的情况，或者产品差异性较小，用户不愿花费太多的时间去挑选；点击量低，购买量低，此类产品应该是存在很多替代品，用户很难集中在某个子类进行大量购买，而是跳跃式地购买；点击量高，购买量低，此类产品需求弹性较大，用户购买存在随机性。

五、结论

1.日uv与pv在11月25日-12月1日保持稳定，在12月2日以后出现明显的增幅，推测最可能的原因是     由于双十二预热活动导致的流量上升，说明双十二活动引流促销效果明显，后续可以结合更多节     假日时机复制双十二活动，制造流量热点。

   用户活跃时间在18点-22点之间，可以在此期间多进行推送、直播带货、促销活动等。

2.从浏览到购买的转化率仅有2.25%，针对这一问题，可以优化平台的搜索匹配度和推荐策略，提     高筛选精确度，并对搜索和筛选的结果排序的优先级进行优化；同时可以给客户提供同类产品比     较的功能，让用户不需要多次返回搜索结果进行点击查看，方便用户确定心仪产品，增加点击到     后续行为的转化。

   在用户的购买路径中，浏览后购买以及直接购买是主要的购买方式。收藏加购率为9.41%，转化     为购买的比例仅占0.55%，针对用户收藏加购的商品要加大力度发放优惠券、推送相关促销活         动，并在收藏和购物车页面配合提醒功能等，以促进用户对收藏加购商品的购买。

   根据RFM模型对用户进行分类，针对不同价值的用户采取不同的运营策略，进行更为精细化的       运营，提升价值用户占比，减少挽留用户占比。

3.从商品来看，浏览量前十的商品与购买量前十的商品并不匹配，针对浏览量高但销量不高的商       品，可以考虑优化商品详情，增加商品的质量管控力度，根据用户反馈并结合自身产品的优劣势     进行商品优化。此外，对于销量高的商品，可以在平台首页对这些品类的商品优先进行展现，以     满足用户的购买需求。