计算新访uv次日留存率

最新推荐文章于 2022-12-01 16:33:01 发布

原创最新推荐文章于 2022-12-01 16:33:01 发布 · 2.9k 阅读

0 ·

CC 4.0 BY-SA版权

Datawhale之MySQL 专栏收录该内容

43 篇文章

订阅专栏

本文介绍如何计算每天新访独立用户（UV）的次日留存率，着重讨论这一指标对于理解用户行为的重要性，同时指出这种方法不包含老用户的次日留存情况。

select
	first_date
	,count(distinct case when by_day=0 then yxuserid else null end) as day_0
	,count(distinct case when by_day=1 then yxuserid else null end) as day_1
	,count(distinct case when by_day=2 then yxuserid else null end) as day_2
	,count(distinct case when by_day=3 then yxuserid else null end) as day_3
	,count(distinct case when by_day=4 then yxuserid else null end) as day_4
	,count(distinct case when by_day=5 then yxuserid else null end) as day_5
	,count(distinct case when by_day=6 then yxuserid else null end) as day_6
	,count(distinct case when by_day>=7 then yxuserid else null end) as day_7plus
from
	(
	select 
		a.yxuserid 
		,log_date 
		,first_date 
		,datediff(log_date,first_date) as by_day
	from 
		(
		select  
			yxuserid 
			,to_date(from_unixtime(floor(createtime/1000))) as log_date 
		from  
			rdb.ods_db_acthd1912shop_tb_yx_common_task_p 
		where 
			ds='2019-12-02' 
			and createtime>=1575043200000
		)a
	left join 
		(
		select  
			yxuserid 
			,min(to_date(from_unixtime(floor(createtime/1000)))) as first_date 
		from  
			rdb.ods_db_acthd1912shop_tb_yx_common_task_p 
		where 
			ds='2019-12-02' 
			and createtime>=1575043200000
        group by yxuserid
		)b
	on 
		a.yxuserid=b.yxuserid
	) as t
group by first_date

参考连接:

注意：这样算出来的是每天新访uv随之而来的留存率，并没有老用户

老用户次日留存参考

select 
	count(distinct a.yxuserid)
	,count(distinct b.yxuserid)
	,count(distinct b.yxuserid)/count(distinct a.yxuserid) 
from 
	(
	select  
		yxuserid 
	from  
		rdb.ods_db_acthd1912shop_tb_yx_common_task_p 
	where 
		ds='2019-12-02' 
		and to_date(from_unixtime(floor(createtime/1000)))='2019-12-02'
	)a
left join 
	(
	select  
		yxuserid 
	from  
		rdb.ods_db_acthd1912shop_tb_yx_common_task_p 
	where 
		ds='2019-12-02' 
		and to_date(from_unixtime(floor(createtime/1000)))='2019-12-03'
	)b
on 
	a.yxuserid=b.yxuserid