淘宝用户行为分析(用户生命周期-路径-消费偏好）

1 基于AARRR模型的用户生命周期分析

1.1 获客Aquisition-日新增用户数

# 每日新增用户数
select a.min_d,count(distinct a.user_id)
from
(select user_id,min(dates) as "min_d"
from userbehavior
where behavior="buy"
group by user_id) a
group by 1
order by 1

1.2转化-跳失率

用户跳失率=只浏览1次页面便离开的访问次数/总访问次数

# 只浏览一次，没有其他行为的用户数据
select user_id,item_id,count(*)
from userbehavior
where user_id not in (select distinct user_id from userbehavior where behavior="fav")
and user_id not in (select distinct user_id from userbehavior where behaivor="cart")
and user_id not in (select distinct user_id from userbehavior where behavior="buy")
group by user_id,item_id
having count(*)=1

#浏览总次数
select count(*)
from userbehavior
where bahavior="pv"

共16225条记录，总访问量为895622，跳失率=1.81%，低到可忽略，说明淘宝再用户停留方面有非常大的吸引力

1.3 用户活跃分析

1.3.1 PV、UV、PV\UV

select count(*) as "访问数",count(distinct user_id) as "访客数",
round(count(*)/ count(distinct user_id)) "人均访问数"
from userbehavior
where behavior="pv"

按日期：

select dates,count(*) "访问数", count(distinct user_id) "访客数",
round(count(*)/ count(distinct user_id)) "人均访问数"
from userbehavior
where behavior="pv"
group by dates

1.3.2 点击量、收藏量、加车量、购买量

select dates,sum(if(behavior="pv",1,0)) as "点击量",sum(if(behavior="fav",1,0)) as "收藏量",
sum(if(behavior="cart",1,0)) as "加车量",sum(if(behavior="buy",1,0)) as "购买量"
from userbehavior
group by dates
order by dates

从上图可知，浏览量PV和访客数UV的变化趋势大体相同
在给定的日期范围内，用户活跃度在工作日比较平稳，在周末12/2-12/3有大幅上升，而对比同为周末的11/25-26仍然有较大提升，查阅相关资料发现，12/2-3正值淘宝双十二预热，因此不管是点击量、收藏量、加车量、购买量都有较大涨幅。

按小时：

select dates,LEFT(time,2),sum(if(behavior="pv",1,0)) "点击量",
sum(if(behavior="fav",1,0)) "收藏量",
sum(if(behavior="cart",1,0)) "加车量",
sum(if(behavior="buy",1,0)) "购买量"
from userbehavior
group by 1,2

由图可知，用户各项行为数据每日的变化趋势基本一致，晚间20点附近出现高点

剔除周末的影响，观察每个时间段用户行为变化

select left(time,2) "时段",
count(*) "访问量",
count(distinct user_id) "访客量",
round(count(*)/count(distinct user_id)) "人均访问量"
from userbehavior
where behavior="pv"
and dates not in ("2017-11-25","2017-11-26","2017-12-02","2017-12-03")
group by 1

由图可知，用户活跃度有3个高峰期，分别是上午10点，下午13-16点，晚上20-23点，符合大部分上班族中午和晚上的作息规律，晚上18点开始的浏览量激增，20-21点达到顶峰，是用户最活跃的时刻，加车和收藏量的峰值出现在20-21点，购买量的峰值在13点和20-22点之间，用户更喜欢晚上加购和收藏，购买行为在中午和晚上都有

1.4 用户留存（无限定购买条件)

定义：有用户行为即可，并非严格限定购买行为
– 次日留存

• 间隔日期可以用 datediff(大日期，小日期）=天数 或 小日期=date_sub(大日期，interval 天数 day)

select t1.dates,count(t1.user_id) as "日新增用户", count(t2.user_id) ad "次日留存人数",
count(t2.user_id)/ count(t1.user_id) as "次日留存率"
(select dates,user_id
from userbehavior
group by dates,user_id) t1
left join (select dates,user_id
from userbehavior
group by dates,user_id) t2
on t1.user_id=t2.user_id
and t1.dates=date_sub(t2.dates,interval 1 day)
group by t1.dates
order by t1.dates

– 3日留存

select t1.dates "日期",count(t1.user_id) as "日新增用户",count(t2.user_id) as "3日留存人数",
count(t2.user_id) /count(t1.user_id) as "3日留存率"
from(select dates,user_id
from userbehavior
group by dates,user_id) t1
left join (select dates,user_id
from userbehavior
group by dates,user_id) t2
on t1.user_id=t2.user_id
and t1.dates=date_sub(t2.dates,interval 3 day)
group by t1.dates
order by t1.dates

– 7日留存

select t1.dates "日期",count(t1.user_id) as "日新增用户",count(t2.user_id) as "7日留存人数",
count(t2.user_id) /count(t1.user_id) as "7日留存率"
from(select dates,user_id
from userbehavior
group by dates,user_id) t1
left join (select dates,user_id
from userbehavior
group by dates,user_id) t2
on t1.user_id=t2.user_id
and t1.dates=date_sub(t2.dates,interval 7 day)
group by t1.dates
order by t1.dates

留存率基本保持在75%以上，且周末12/2-3迎来留存高峰，上涨至98%以上，与双十二预热活动带来的用户关注度相吻合

1.5限定购买的用户留存

select t1.dates "日期",count(t1.user_id) as "日新增用户",count(t2.user_id) as "次日留存人数",
count(t2.user_id) /count(t1.user_id) as "次日留存率"
from (select dates,user_id
from userbehavior
where behavior="buy"
group by dates,user_id) t1
left join (select dates,user_id
from userbehavior
where behavior="buy"
group by dates,user_id) t2
on t1.user_id=t2.user_id
and datediff(t2.dates,t1.dates)=1

同上

同上

3日和7日的用户留存率基本处在20%左右，较为稳定

1.6 再变现-复购率

• 复购率=购买次数2次以上/购买次数1次以上

select concat(round(sum(case when a.购买次数>=2 then 1 else 0 end)/sum(case when a.购买次数>=1 then 1 else 0 end)*100,2),"%") as "复购率"
from (select distinct user_id,count(user_id) as "购买次数"
from userbehavior
where behavior="buy"
group by user_id) a

• 复购频次的用户数分布

select a.freq as "frequency",count(distinct a.user_id) as "用户数"
from(select distinct user_id, count(*) as freq
from userbehavior
where behavior="buy"
group by 1
having count(*)>1) a
group by 1
order by 1 

91%的用户复购次数低于7次，高复购次数的用户逐渐减少

2 用户行为路径分析

2.1用户行为漏斗

select concat(round(sum(pv)/sum(pv),2)*100,"%") "pv-pv",
concat(round((sum(cart)+sum(fav))/sum(pv)*100,2),"%") as "pv-cart/fav",
concat(round(sum(buy)/sum(pv)*100,2),"%") as "pv-buy"
from
(select user_id, count(behavior),
sum(if(behavior="pv"),1,0) as "pv",
sum(if(behavior="cart"),1,0) as "cart",
sum(if(behavior="fav"),1,0) as "fav",
sum(if(behavior="buy"),1,0) as "buy"
from userbehavior
group by 1) as a

从浏览点击到加购/收藏的转化率只有9.3%，到最终购买的转化率仅有2.29%
为什么从点击到最后的购买转化率这么低呢？
下面进行假设检验进行分析：
假设1：多数用户只是逛淘宝而不购买

select  behavior,count(distinct user_id) as user_num
from userbehavior
group by 1
order by 2 desc

经计算购买的用户占比为24.2%，高于转化率2.29%，所以拒绝原假设，说明用户是有大量购买行为的，并非只是浏览商品

假设2：只有进行收藏/加购的用户才有可能购买

select 

3 用户消费偏好分析

• 不同购买次数的商品种类

select a.buy_time as "购买次数", count(category_id) "商品种类"
from (select *,count(*) as "buy_time"
from userbehavior
where behavior="buy"
group by item_id) as a
group by 1
order by 1

购买一次的商品有15737种，占用户购买商品总数的88.56%，说明商品售卖主要依靠长尾商品的累计效应，而非爆款商品的推动

• 1）商品销售的前20名

select item_id,sum(if(behavior="buy"),1,0) as "buy",
sum(if(behavior="pv"),1,0) as "pv",
sum(if(behavior="buy"),1,0) as "buy",
sum(if(behavior="cart"),1,0) as "cart"
from userbehavior
group by 1
order by buy desc
limit 20

寻找畅销原因
假设1：热卖商品的浏览量也高

select item_id,sum(if(behavior="pv"),1,0) as "pv"
from userbehavior
group by item_id
order by pv desc
limit 10

浏览量前10和销量前20并没有重合的用户，说明假设1不成立，淘宝的推荐商品并非用户愿意购买的

假设2：畅销商品的商品转化率也高

select * FROM(select item_id,sum(if(behavior="buy",1,0)) as "buy"
from userbehavior
group by item_id
order by buy DESC
limit 10) as a
inner join (select item_id,concat(round(sum(if(behavior="buy",1,0))/ sum(if(behavior="pv",1,0))*100,2),"%") as "转化率"
from userbehavior group by item_id order by 2 desc limit 10) as b
using(item_id)

收藏量前10的商品中，没有一个进入销量前10的，说明用户收藏后购买的转化率不高，但是这些商品被收藏，说明用户是喜欢的，需要追加一些营销活动来刺激用户的购买行为