随着电商平台数据量的不断积累,通过数据分析,挖掘消费者的潜在需求、消费偏好成为平台运营过程中的重要环节。
本项目基于淘宝用户行为数据,在MySQL关系型数据库与Tableau可视化平台中,探索用户规律,寻找高价值用户;分析商品特征,寻找高贡献商品;分析产品功能,优化产品路经。
本数据集包含了2014年11月18日到2014年12月18日之间,有行为的随机用户的所有行为(行为包括点击、购买、加购、喜欢)。数据集的每一行表示一条用户行为,由用户ID、商品ID、商品类目ID、用户地理位置、行为类型和行为时间组成。原数据集总共有1200万行数据,数据量太大,本次分析根据随机抽取导入约61万条数据。
关于数据集中每一列的详细描述如下:
字段 | 说明 |
---|---|
user_id | 序列化后的用户ID,整数类型 |
item_id | 序列化后的商品ID,整数类型 |
user_geohash | 地理位置,字符串类型 |
behavior_type | 用户行为类型(1,2,3,4),整数类型 |
item_category | 序列化后的商品所属类目ID,整数类型 |
time | 行为发生的时间 |
其中用户行为类型(behavior_type)有四种:
行为类型 | 说明 |
---|---|
1 | 点击商品详情页(页面浏览) |
2 | 收藏商品 |
3 | 将商品加入购物车 |
4 | 购买商品 |
通过对电商流量维度的分析,可以了解到用户流量的相关信息,比如用户活跃时间、用户流量漏斗转化率。
需要指标:uv、pv、商品平均访问量、跳出率、复购率、用户流量漏斗转化率…
通过对用户行为的分析,可以了解用户四种行为数据,用户购买路径偏好,用户留存率等。
通过对用户价值进行分析,使用RFM模型,对用户进行分类,找出有价值的用户,对用户进行精细化运营。
需要指标:行为数据、购买路径、用户留存率、RFM
通过对商品品类、商品成交量、商品点击次数、购买点击率等指标的分析,探索用户对商品及商品品类的偏好,了解商品销售的规律。
create table user_behavior(
user_id int,
item_id int,
behavior_type varchar(10),
user_geohash varchar(100),
item_category int,
time varchar(50)
);
查看数据:
user_geohash为用户地理位置,由于数据缺失严重,并且对本次分析影响不大,所以将其删除。
alter table user_behavior
drop user_geohash;
SELECT * FROM user_behavior
where user_id = null
or item_id = null
or behavior_type = null
or item_category = null
or time = null;
检查结果显示并无数据缺失。
update user_behavior
set behavior_type = case
when 1 then 'click'
when 2 then 'collect'
when 3 then 'cart'
when 4 then 'buy'
end;
因为原数据集的时间只精确在小时,为方便整理,在表中加入三列:
日期 date_time(%Y-%m-%d)
小时 date_hour(%H)
星期 weekdays (0 =星期一,1 =星期二,2 =星期三,3 =星期四,4 =星期五,5 =星期六,6 =星期日)
alter table user_behavior add date_time char(10),
add date_hour char(2),
add weekdays varchar(20);
update user_behavior
set date_time = date_format(time,'%Y-%m-%d'),
date_hour = date_format(time,'%H'),
weekdays = weekday(time)
运行完时间部分结果如下:
select min(date_time),max(date_time) from user_behavior; -- 起始及日期及结束日期
select count(distinct user_id) from user_behavior; -- 总用户数量
select count(distinct item_id) from user_behavior; -- 总商品数量
select count(distinct item_category) from user_behavior; -- 总商品品类数量
select count(*) from user_behavior
where behavior_type = 'click'; -- 总浏览量
select count(*) from user_behavior
where behavior_type = 'collect'; -- 总收藏量
select count(*) from user_behavior
where behavior_type = 'cart'; -- 总加购量
select count(*) from user_behavior
where behavior_type = 'buy'; -- 总购买量
select count(*) from user_behavior; -- 总数据量
统计结果如下:
维度 | 时间 |
---|---|
日期 | 2014.11.18 - 2014.12.18 |
用户数量 | 4948 |
商品数量 | 436765 |
商品种类数量 | 6387 |
用户行为——点击 | 577385 |
用户行为——收藏 | 12307 |
用户行为——加购 | 17207 |
用户行为——购买 | 5945 |
用户行为总数量 | 612844 |
根据表中数据统计出独立用户访问量、总页面浏览量、总点击量、总收藏量、总加购量、总购买量,以及“购买用户数”、“日均访问量”、“人均访问量”。
select
count(distinct user_id) uv,
count(user_id) pv,
sum(if(behavior_type = 'click',1,null)) click_n,
sum(if(behavior_type = 'collect',1,null)) collect_n,
sum(if(behavior_type = 'cart',1,null)) cart_n,
sum(if(behavior_type = 'buy',1,null)) buy_n,
count(distinct if(behavior_type = 'buy',user_id,null)) '购买用户数',
count(user_id)/count(distinct date_time) '日均访问量',
count(user_id)/count(distinct user_id) '人均访问量'
from user_behavior;
在2014.11.18日-2.14.12.18日期间,网站总访问量约61万次,总访客数4948人,日均访问量将近2万次,人均访问量约124次。
其中用户行为数据:商品点击次数约达到58万次,收藏此书约1.23万次,加购次数约1.72万次,购买次数约达6千次,购买用户量2525人,人均购买量约为2.3次。
2、日均流量分析
select
date_time,
weekdays,
count(distinct user_id) uv,
count(user_id) pv,
sum(if(behavior_type = 'click',1,null)) click_n,
sum(if(behavior_type = 'collect',1,null)) collect_n,
sum(if(behavior_type = 'cart',1,null)) cart_n,
sum(if(behavior_type = 'buy',1,null)) buy_n
from user_behavior
group by date_time;
数据可视化展示:
分析:
3、时均流量分析
select
date_hour,
count(distinct user_id) uv,
count(user_id) pv,
sum(if(behavior_type = 'click',1,null)) click_n,
sum(if(behavior_type = 'collect',1,null)) collect_n,
sum(if(behavior_type = 'cart',1,null)) cart_n,
sum(if(behavior_type = 'buy',1,null)) buy_n
from user_behavior
group by date_hour;
数据可视化展示:
分析:
用户行为有点击、收藏、加购、购买四种,默认用户需要先进行了点击行为才有后续的操作。
先计算四种行为分别的数量,再计算购买相对于收藏、加购的转化率,可分析有收藏、加购行为是否会促进购买,若是,可增加活动中出金用户的加购。
select
count(if(behavior_type = 'click',1,null)) click_n,
count(if(behavior_type = 'collect',1,null)) collect_n,
count(if(behavior_type = 'cart',1,null)) cart_n,
count(if(behavior_type = 'buy',1,null)) buy_n,
concat(round(count(if(behavior_type = 'collect',1,null))/count(if(behavior_type = 'click',1,null))*100,2),'%') '收藏/点击率',
concat(round(count(if(behavior_type = 'cart',1,null))/count(if(behavior_type = 'click',1,null))*100,2),'%') '加购/点击率',
concat(round(count(if(behavior_type = 'buy',1,null))/count(if(behavior_type = 'click',1,null))*100,2),'%') '购买/点击率',
concat(round(count(if(behavior_type = 'buy',1,null))/count(if(behavior_type = 'collect',1,null))*100,2),'%') '购买/收藏率',
concat(round(count(if(behavior_type = 'buy',1,null))/count(if(behavior_type = 'cart',1,null))*100,2),'%') '购买/加购率'
from user_behavior
可视化结果展示:
分析:
跳出率是指用户从相应的入口进入后,只访问了一个页面就离开的数量占从相应入口进入的总用户占比。可就此分析页面对于用户是否有吸引力。
-- 跳出率 = 只访问一个页面就离开的用户数 / 总用户数
select
count(a.user_id) '只访问一个页面就离开的用户数',
count(distinct u.user_id) '总用户数',
concat(count(a.user_id)/count(distinct u.user_id)*100,'%') '跳出率'
from (
select user_id,behavior_type from user_behavior
group by user_id
having count(if(behavior_type = 'click',1,null)) = 1 -- 只存在单次点击行为,没发生别的行为
and count(if(behavior_type = 'collect',1,null)) = 0
and count(if(behavior_type = 'cart',1,null)) =0
and count(if(behavior_type = 'buy',1,null)) =0
) a
right join user_behavior u
on a.user_id = u.user_id;
分析:
复购率是指消费者对该品牌或产品或者服务的重复购买次数,重叠度购买率越多,则反映出消费者对品牌的忠诚度就越高,反之则越低。
-- 复购率 = 有重复购买行为的用户数 / 有购买行为的用户数
select
count(distinct a. user_id) '重复购买的用户',
count(distinct u.user_id) '有购买行为的用户',
concat(count(distinct a.user_id)/count(distinct u.user_id)*100,'%') '复购率'
from (
select user_id,behavior_type,count(behavior_type)
from user_behavior
where behavior_type = 'buy'
group by user_id
having count(user_id) > 2
) a
right join user_behavior u
on a.user_id = u.user_id
where u.behavior_type = 'buy'
;
分析:
1、购买量top10用户
select
user_id,
count(behavior_type) '购买次数'
from user_behavior
where behavior_type = 'buy'
group by user_id
order by count(if(behavior_type = 'buy',1,null)) desc
limit 10;
2、用户购买频率直方图
select
count(distinct user_id) 用户数,
购买次数
from
(select user_id,count(behavior_type) 购买次数
from user_behavior
where behavior_type = 'buy'
group by user_id
order by count(behavior_type) desc) a
group by 购买次数
数据结果可视化展示:
分析:
次日留存率、三日留存率、七日留存率、十四日留存率,通过留存率分析用户粘性。
select
a.date_time,
count(distinct a.user_id) '当日活跃量',
count(if(datediff(b.date_time,a.date_time)=1,a.user_id,null)) '次日活跃量',
count(if(datediff(b.date_time,a.date_time)=2,a.user_id,null)) '三日活跃量',
count(if(datediff(b.date_time,a.date_time)=6,a.user_id,null)) '7日活跃量',
count(if(datediff(b.date_time,a.date_time)=13,a.user_id,null)) '14日活跃量',
count(if(datediff(b.date_time,a.date_time)=1,a.user_id,null))/count(distinct a.user_id) '次日活跃率',
count(if(datediff(b.date_time,a.date_time)=2,a.user_id,null))/count(distinct a.user_id) '三日活跃率',
count(if(datediff(b.date_time,a.date_time)=6,a.user_id,null))/count(distinct a.user_id) '7日活跃率',
count(if(datediff(b.date_time,a.date_time)=13,a.user_id,null))/count(distinct a.user_id) '14日活跃率'
from (
select distinct date_time,user_id
from user_behavior
order by date_time) a
left join (
select distinct date_time,user_id
from user_behavior
order by date_time) b
on a.date_time < b. date_time
and a.user_id = b.user_id
group by a.date_time
结果中后半部分时间出现0的数据,是时间长度不足无法计算的缘故,在此次分析中暂时不做参考。
分析:
用户有四种行为:点击、收藏、加购、购买。
为探索用户购买路径偏好,现对行为路径进行分析。
-- 统计每个用户对各个商品在购买前发生的其他行为
create or replace view behavior_count as
select
a.user_id,
a.item_id,
count(if(behavior_type='click',1,null)) click_n,
count(if(behavior_type='collect',1,null)) collect_n,
count(if(behavior_type='cart',1,null)) cart_n,
count(if(behavior_type='buy',1,null)) buy_n
from (
select
user_id,
item_id,
date_time
from user_behavior
where behavior_type = 'buy') a
left join (
select user_id,item_id,behavior_type,date_time
from user_behavior) b
on a.user_id = b.user_id
and a.item_id = b.item_id
and a.date_time >= b.date_time -- 除购买外的行为时间不能超过购买时间
group by user_id,item_id;
-- 连接购买前的行为路径,并统计该路径的数量
select behavior,count(behavior) behavior_n
from
(select
concat(if(click_n=0,'','click-'),
if(collect_n=0,'','collect-'),
if(cart_n=0,'','cart-'),
if(buy_n=0,'','buy')) behavior
from behavior_count) b
group by behavior
order by behavior_n desc
可视化展示
分析:
RFM模型是衡量客户价值与客户创利能力的工具
-- R(Recency):代表用户最近一次消费距离现在的时间,R越小,用户价值越高;
-- F(Frequency):用户在统计周期内购买商品的次数,F越大,用户交越频繁,用户价值越高;
--M(Monetary):用户在统计周期内的消费金额,M越大,用户价值越高。
由于原数据集中没有消费金额数据,所以本次分析暂时不考虑M维度,仅考虑RF维度将现有用户分为四类:
分别对R、F两个指标的用平均数划分,(RF)中更有价值的标记为1,更没价值的标记为0,进一步解释群体类型:
价值客户(11):最近消费时间近、消费频次很高,说明是有价值的客户。
保持客户(01):最近消费时间较远,但消费频次很高,说明这是个一段时间没来的忠诚客户。
发展客户(10):最近消费时间较近,但频次不高,忠诚度不高,很有潜力的用户,可重点发展。
挽留客户(00):最近消费时间较远、消费频次不高,可能是将要流失或者已经要流失的用户,应当给予挽留措施。
-- R维度
1、统计每个用户最近一次的购买时间与2014-12-19日的距离天数
2、计算用户最近一次的购买时间的平均值
3、根据用户的最近一次购买时间间隔是否超过平均值,给用户用0/1评分(超过平均数的为0,没超过为1)
4、创建视图以便后续查取
create or replace view r_value_table as -- 创建视图
with r_datediff_count as -- 创建r维度最近购买时间距离天数子查询表
(
select
user_id,
datediff('2014-12-19',maxbuydate) date_count
from
(select
user_id,
max(date_time) maxbuydate
from user_behavior
where behavior_type = 'buy'
group by user_id) a
)
select -- 在R维度天数表的基础上对比每位用户和最近一次购买时间间隔的平均数
user_id,
date_count,
(case
when date_count < (select avg(date_count) from r_datediff_count) then 1 else 0 end) r_value
from r_datediff_count;
-- F维度
1、统计每个用户统计周期内购买商品的次数
2、计算用户购买商品的次数的平均值
3、根据用户的购买商品的总次数是否超过平均值,给用户用0/1评分(超过平均数的为1,没超过为0)
4、创建视图以便后续查取
create or replace view f_value_table as -- 创建视图
with f_buy_count as -- 创建f维度用户购买次数表
(select
user_id,
count(behavior_type) buy_num
from user_behavior
where behavior_type = 'buy'
group by user_id)
select -- 在F维度次数表的基础上对比每位用户和所有用户购买的平均数
user_id,
buy_num,
case when buy_num > (select avg(buy_num) from f_buy_count) then 1 else 0 end f_value
from f_buy_count;
-- RF模型
--根据R、F两个维度的表将用户贴标签进行分类
with user_type_table as -- 创建用户分类虚拟视图
(
select
user_id,
(case
when r_value=1 and f_value=1 then '价值客户'
when r_value=0 and f_value=1 then '保持客户'
when r_value=1 and f_value=0 then '发展客户'
when r_value=0 and f_value=0 then '挽留客户'
end) user_type -- 给用户划分类别
from
(select
r.user_id,
r_value,
f_value
from r_value_table r,f_value_table f
where r.user_id = f.user_id) a -- 将前两个步骤中的表结合
)
select
user_type,
count(user_type) num
from user_type_table
group by user_type
order by num desc;
分析:
根据RFM用户价值分析中的RF维度将用户进行分类,分成了四类用户,对于不同价值的用户采用不同的运营策略。对比保持用户,挽留用户、发展用户、价值用户这三类占比差异不大,应提升价值用户的占比,降低挽留用户。
-- 先统计商品的数量以及商品品类数量
select
count(distinct item_id) item_num,
count(distinct item_category) item_category_num
from user_behavior
对于商品来说,也有四种属性:点击、收藏、加购、购买,通过点击量可以看出商品对用户是否具有吸引力,通过购买量可以看出用户对商品的需求,通过统计商品的购买点击率可以分析商品的转化情况。由于通过上述用户行为的分析,我们了解到大多数用户并无收藏与加购的习惯,所以在商品的维度暂时不对收藏于加购进行过多分析。
1、以购买量为主进行top10商品分析
select
item_id,
count(behavior_type) bought_n
from user_behavior
where behavior_type = 'buy'
group by item_id
order by bought_n desc
limit 10;
对购买量top10商品进行购买点击率分析
-- 购买点击率 = 购买量 / 点击量
with item_bought as
(
select
item_id,
count(behavior_type) bought_n
from user_behavior
where behavior_type = 'buy'
group by item_id
order by count(behavior_type) desc
limit 10
)
select
item_id,
count(if(behavior_type='click',behavior_type,null)) click_num,
count(if(behavior_type='collect',behavior_type,null)) collect_num,
count(if(behavior_type='cart',behavior_type,null)) cart_num,
count(if(behavior_type='buy',behavior_type,null)) buy_num,
concat(round(ifnull(count(if(behavior_type='buy',behavior_type,null))/count(if(behavior_type='click',behavior_type,null))*100,0),2),'%') 购买点击率
from user_behavior
where item_id in (select item_id from item_bought)
group by item_id
order by count(if(behavior_type='buy',behavior_type,null)) desc;
结果如下:
分析:
2、以点击量为主进行top10商品分析
create table click_number_top10 as -- 因为点击量数据太大跑的很慢,所以将统计出来的结果存到表里
with click_count as
(
select
item_id,
count(if(behavior_type='click',behavior_type,null)) click_n
from user_behavior
group by item_id
)
select
item_id,
click_n
from click_count
order by click_n desc
limit 10;
结果可视化如下:
对top10点击量商品进行购买点击率分析
-- 购买点击率 = 购买量 / 点击量
select
item_id,
count(if(behavior_type='click',behavior_type,null)) click_num,
count(if(behavior_type='collect',behavior_type,null)) collect_num,
count(if(behavior_type='cart',behavior_type,null)) cart_num,
count(if(behavior_type='buy',behavior_type,null)) buy_num,
concat(round(ifnull(count(if(behavior_type='buy',behavior_type,null))/count(if(behavior_type='click',behavior_type,null))*100,0),2),'%') 购买点击率
from user_behavior
where item_id in (select item_id from click_number_top10)
group by item_id
order by count(if(behavior_type='click',behavior_type,null)) desc;
分析:
select
item_category,
count(behavior_type) bought_n
from user_behavior
where behavior_type = 'buy'
group by item_category
order by bought_n desc
limit 10;
结果可视化展示如下:
分析:
select
item_category,
count(if(behavior_type='click',behavior_type,null)) click_num,
count(if(behavior_type='collect',behavior_type,null)) collect_num,
count(if(behavior_type='cart',behavior_type,null)) cart_num,
count(if(behavior_type='buy',behavior_type,null)) buy_num,
concat(round(ifnull(count(if(behavior_type='buy',behavior_type,null))/count(if(behavior_type='click',behavior_type,null))*100,0),2),'%') 购买点击率
from user_behavior
group by item_category
order by buy_num desc
limit 10;
分析:
统计期间,网站总访问量约61万次,日均访问量约2万次;总访客数为4948人,人均访问次数为124次。复购率约30.38%,接近三分之一的用户会选择回购,证明了产品对用户的价值,也进一步确认了平台的价值;跳出率约0.97%,接近于0,说明平台中的商品或广告对用户有吸引力,用户愿意在平台中花时间进行选购。
注:因期间有双十二大型促销活动,所以各项指标较平常周期可能有所提升。
1、总体用户的活跃时间具有规律性,每周五的用户购物频率及购物欲望会有所增加,在每天的18点-22点用户访问量开始增加,用户行为也显著上升,所以可在上述期间与时段内多进行运营活动(如直播带货) 或推送商品信息等
2、在流量转化方面,收藏、加购、购买相对于点击的转化率在2%左右,转化率较低;但是收藏与加购之后的购买率(34%~48%) 较其他转化率有显著提升,建议推出加购收藏领优惠券等活动,提高用户的加购收藏行为,从此促进购买转化。
1、老用户的留存较为稳定,可通过定时发送商品推送或优惠券等活动,增加用户浏览时长和深度,调查用户使用体验做出相应改进,提高用户的忠诚度;对于新用户,留存的提升空间较大,可通过连续签到有奖或限时秒杀活动等,提高用户粘性。
2、在用户的购买的路径中,用户多以直接购买为主,应引1导用户多使用加购与收藏功能。
3、根据rfm模型对用户进行了分类,对不同价值的用户采用不同的运营策略,进行精细化运营,提升重要价值客户的占比
点击量较高的商品普遍转化率较低,需注意是否是平台算法不合理或者广告较多,建议优化算法,增加类似商品信息比较功能,减少用户的浏览选择时间。
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。