淘宝用户行为研究分析

数据清洗

SELECT * FROM userbehavior

– 数据清洗
– 删除重复值
SELECT * FROM userbehavior
GROUP BY user_id,item_id,category_id,behavior_type,timestamp
HAVING COUNT(*)>1;

– 处理缺失值
select
count(user_id),
count(item_id),
count(category_id),
count(behavior_type),
count(timestamp)
FROM userbehavior;

– 时间序列处理：
– Timestamp列无法直接进行分析，需要对其列转化为三列，分别为时间，日期，小时
alter table userbehavior add column date_time timestamp(0) null;
– FROM_UNIXTIME：
update userbehavior set date_time = FROM_UNIXTIME(timestamp,’%Y-%m-%d %H:%i:%s’);

ALTER TABLE userbehavior ADD COLUMN Date char(10) NULL;
UPDATE userbehavior SET Date = FROM_UNIXTIME(Timestamp,’%Y-%m-%d’);

ALTER TABLE userbehavior ADD COLUMN Time char(10) null;
update userbehavior set Time = SUBSTRING(Date_time FROM 12 FOR 2);

SELECT * FROM userbehavior

– 异常值处理
– 由于项目背景是要通过对2017年11月25日至2017年12月3日之间的淘宝用户行为数据集进行隐式反馈推荐问题的研究，
– 所以要对这个时间段外的数据进行删除处理。
select max(date_time),min(date_time) from userbehavior;

– 把不在2017-11-25至2017-12-03这个时间段内的异常时间值删除
delete from userbehavior where date >‘2017-12-03’ or date < ‘2017-11-25’;
– 再次检查是否存在不在2017-11-25至2017-12-03这个时间段内的异常值
select max(date_time),min(date_time) from userbehavior;

SELECT * FROM userbehavior;

SELECT count(DISTINCT User_ID) as 用户数,
count(DISTINCT Item_ID) as 商品数量,
count(DISTINCT Category_ID) as 商品类目数量,
count(Behavior_type) as 行为数量
FROM userbehavior;

数据分析概览

SELECT * FROM userbehavior;
select count(distinct behavior_type) FROM userbehavior;
select distinct behavior_type FROM userbehavior;

– 总体UV,PV,人均浏览次数，成交量
select count(distinct user_id) as ‘独立访客数’,
sum(case when behavior_type=‘pv’ then 1 else 0 end) as ‘点击数’,
sum(case when behavior_type=‘pv’ then 1 else 0 end)/count(distinct user_id) as ‘人均浏览次数’,
sum(case when behavior_type=‘BUY’ then 1 else 0 end) as ‘成交量’
from userbehavior;

– 日均UV,PV,人均浏览次数，成交量
select Date,
COUNT(DISTINCT User_ID) AS ‘日访客数’,
sum( case when Behavior_type=‘pv’ then 1 else 0 END) as ‘日点击数’,
sum( case when Behavior_type=‘pv’ then 1 else 0 END)/COUNT(DISTINCT User_ID) as ‘日人均浏览次数’,
sum(case when Behavior_type =‘buy’ then 1 else 0 END)as ‘日成交量’
from userbehavior
GROUP BY Date
ORDER BY Date;

– 用户整体行为数据
create view 用户行为数据 AS
select User_ID,
COUNT(Behavior_type) as ‘用户行为数’,
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 userbehavior
GROUP BY User_ID
ORDER BY 用户行为数 DESC;

– 用户的复购率和跳失率
– 复购用户、复购率
SELECT
sum(case when 购买>1 then 1 else 0 end ) as 复购次数,
sum(case when 购买>0 then 1 else 0 end ) as 购买次数,
CONCAT(ROUND(sum(case when 购买>1 then 1 else 0 end)*100/ sum(case when 购买>0 then 1 else 0 end),2),’%’) as 复购率
from 用户行为数据;

– 跳失率=只浏览了一个页面的访客数/总访客数
select COUNT(*) from (
SELECT User_ID from userbehavior
GROUP BY User_ID HAVING COUNT(Behavior_type)=1)as a;

select User_id
from 用户行为数据
where ‘用户行为数’ = “0”;

– 当统计时间为9天时，用户的复购率为65.87%且跳失率为0，说明淘宝拥有你足够的吸引力让用户停留且用户的忠诚度非常高。可以进一步培养用户的忠诚度，鼓励用户更高频次的消费。

用户行为数据转化分析

– 流量行为转化漏斗分析
select sum(点击) as 点击,
sum(收藏) as 收藏,
sum(加购) as 加购,
sum(购买) as 购买
from 用户行为数据;

select count(User_ID)from 用户行为数据;
select * from 用户行为数据 where 点击 = 0;

– 用户留存分析
select
sum(case when 点击>0 then 1 else 0 end) as 点击用户数,
sum(case when 收藏>0 then 1 else 0 end) as 收藏用户数,
sum(case when 加购>0 then 1 else 0 end) as 加购用户数,
sum(case when 购买>0 then 1 else 0 end) as 购买用户数
from 用户行为数据;

select
count(case when 点击>0 then 1 else 0 end) as 点击用户数
from 用户行为数据;
– 点击-购买
select
sum(case when 购买>0 then 1 else 0 end) as 购买用户数
from 用户行为数据
where 收藏 = 0 and 加购 = 0;

– 点击-加购-购买
select
sum(case when 加购>0 then 1 else 0 end) as 加购用户数,
sum(case when 购买>0 then 1 else 0 end) as 购买用户数
from 用户行为数据
where 收藏 = 0 and 加购 <> 0;

– 点击-收藏-购买
select
sum(case when 收藏>0 then 1 else 0 end) as 收藏用户数,
sum(case when 购买>0 then 1 else 0 end) as 购买用户数
from 用户行为数据
where 收藏 <> 0 and 加购 = 0;

– 点击-收藏-加购-购买、
select
sum(case when 加购>0 then 1 else 0 end) as 加购用户数,
sum(case when 收藏>0 then 1 else 0 end) as 收藏用户数,
sum(case when 购买>0 then 1 else 0 end) as 购买用户数
from 用户行为数据
where 加购<>0 and 收藏<>0;

用户行为习惯分析

create view 用户行为习惯分布（天） AS
select
Date,
COUNT(Behavior_type) as ‘用户行为数’,
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 userbehavior
GROUP BY Date
ORDER BY Date;

– 用户行为习惯分布（时）
create view 用户行为习惯分布（时） AS
select Time,COUNT(Behavior_type) as ‘用户行为数’,
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 userbehavior
GROUP BY Time
ORDER BY Time;

create view 用户行为习惯分布（天） AS
select
Date,
COUNT(Behavior_type) as ‘用户行为数’,
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 userbehavior
GROUP BY Date
ORDER BY Date;

– 用户行为习惯分布（时）
create view 用户行为习惯分布（时） AS
select Time,COUNT(Behavior_type) as ‘用户行为数’,
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 userbehavior
GROUP BY Time
ORDER BY Time;

用户价值分析

– 最近一次购买
select User_ID,
(case when Rtime >7 then 1
when Rtime between 5 and 7 then 2
when Rtime BETWEEN 3 and 4 then 3
when Rtime between 0 and 2 then 4
else null end ) as 购买时间评级
FROM(select User_ID,DATEDIFF(‘2017-12-03’,max(Date)) as Rtime
from userbehavior
where Behavior_type=‘buy’
GROUP BY User_ID) as 购买时间
ORDER BY 购买时间评级 DESC;

– 购买次数
select User_ID,count(Behavior_type)as 购买次数
from userbehavior
where Behavior_type=‘buy’
group by User_ID
ORDER BY 购买次数 DESC;

– 对用户的购买次数进行排名后，发现购买次数范围在1-43之间，故我们按照以下规则设立等级：

– 购买次数在1-10之间设为1
– 购买次数在11-20之间设为2
– 购买次数在21-30之间设为3
– 购买次数>30次设为4

– 购买频率
SELECT User_ID,
(case when 购买次数 between 1 and 10 then 1
when 购买次数 between 11 and 20 then 1
when 购买次数 between 21 and 30 then 2
when 购买次数 >=31 then 4
else null end ) as 购买频率等级
FROM(
select User_ID,count(Behavior_type)as 购买次数
from userbehavior
where Behavior_type=‘buy’
group by User_ID) as 购买频率
ORDER BY 购买频率等级 DESC;

分析结论：
用户行为的转化分析：用户从点击到购买的转化率还是比较高的，可以通过引导用户收藏并加购来提高用户从收藏/加购到购买的转化率；而从点击到购买的转化率仅有2.3%，针对这一环节的建议有：

● 优化电商平台的搜索匹配度和推荐策略，提高筛选精确度，并对搜索和筛选的结果排序的优先级进行优化；
● 可以给客户提供同类产品比较的功能，让用户不需要多次返回搜索结果进行点击查看，方便用户确定心仪产品，增加点击到后续行为的转化；
● 优化收藏到购买的操作过程，增加用户收藏并加购的频率，以提高购买转化率。

通过对用户的商品偏好分析，可以看出商品销量主要是依靠长尾效应，而非爆款商品的带动。但是通过对商品品类的分析可以看出能吸引用户注意力的商品购买转化率并不高，是一个提高销量的突破口。针对用户关注度高但销量不高的这部分产品，可以从以下几个方面着手：
● 商家在详情页上的展示突出用户重点关注的信息，优化信息的呈现方式，减少用户的时间成本；
● 增加这些产品的质量管控力度，加强对用户反馈建议如评论的管理，认真采纳并根据自身的优劣势进行商品优化；
● 此外，对于购买top20的商品，可以在电商首页对这些品类的商品优先进行展现，以满足用户的购买需求。

通过RFM模型对客户群进行划分，可以对不同的用户群体采用不同的管理策略，达到对不同的客户群进行精准营销的目的：

● 对于重要价值用户，需要重点关注并保持， 应该提高满意度，增加留存；
● 对于重要发展客户和重要保持用户，可以适当给点折扣或捆绑销售来增加用户的购买频率；
● 对于重要挽留客户，需要关注他们的购物习性做精准化营销，以唤醒他们的购买意愿。