一、留存率介绍
以某一段时间首次访问或登录系统的用户人数为分母，在之后的某一段时间内，再次登录/访问系统的用户人数为分子，得出的比例，称为留存率
通常用到的留存率有日留存、周期内留存率、周期留存率，日留存率又分为次日、七日、30日等。周期内留存率分为7天留存率、30天留存率等。周期留存率分为周留存率、月留存率等
下面分别按照这几种留存率的定义，以及一张用户登录日期表，来介绍用sql实现的方法
表名：login_logs
sql文件：login_logs.sql
示例数据：
二、日留存率
1、次日留存率
以某一天d1首次访问或登录系统的用户人数为分母，在第二天d2中，再次登录系统的用户人数为分子，得出的比例，称为d1的次日留存率
1.1、获取2025年4月1日的次日留存率
步骤1：获取2025年4月1日首次登录的用户
步骤2：获取2025年4月2日登录的用户
步骤3：关联两份数据，得出4月1日的次日留存率
# 获取第2025年4月1日首次登录的用户
with user_first_login as
(
select user_id,min(login_date) login_date from login_logs group by user_id
having min(login_date) = '2025-04-01'
),
# 获取到2025年4月2日登录的用户
second_login as
(
select * from login_logs where login_date = '2025-04-02'
)
# 关联两份数据，得出4月1日的次日留存率
select u.login_date,count(u.user_id) as first_login_number,count(s.user_id) as second_login_number,
round(100*count(s.user_id) /count(u.user_id),2) as retention_ratio
from user_first_login u
left join second_login s
on u.user_id = s.user_id group by u.login_date
1.2、获取2025年4月1日到4月10日的次日留存率
步骤1：获取2025年4月1日到4月10日首次登录的用户
步骤2：获取2025年4月2日到4月11日登录的用户
步骤3：关联两份数据，得出4月1日到4月10日的次日留存率
# 获取在2025年4月1日到4月10日首次登录的用户
with user_first_login as
(
select user_id,min(login_date) as login_date from login_logs group by user_id
having min(login_date) between '2025-04-01' and '2025-04-10'
),
# 获取到2025年4月2日到4月11日登录过的用户
second_login as
(
select * from login_logs where login_date between '2025-04-02' and '2025-04-11'
)
# 关联两份数据，得出4月1日到4月10日的次日留存率
select u.login_date,count(u.user_id) as fisrt_login_number,count(s.user_id) as second_login_number,
round(100*count(s.user_id) /count(u.user_id),2) as retention_ratio,
json_arrayagg(u.user_id) as first_login_user,json_arrayagg(s.user_id) as second_login_user
from user_first_login u
left join second_login s
on u.user_id = s.user_id
and s.login_date = DATE_ADD(u.login_date,INTERVAL 1 DAY)
group by u.login_date2、7日留存率
以某一天d1首次访问或登录系统的用户人数为分母，在第7天d7中，再次登录系统的用户人数为分子，得出的比例，称为d1的七日留存率
2.1、获取2025年4月1日到4月3日的七日留存率
与次日留存率主要的区别就是对比的日期从d1 vs d2改成了d1 vs d7，sql改动很小
步骤1：获取2025年4月1日到4月3日首次登录的用户
步骤2：获取2025年4月7日到4月9日登录的用户（分别对应1～3号的第7天）
步骤3：关联两份数据，得出4月1日到4月3日的七日留存率
# 获取在2025年4月1日到4月3日首次登录的用户
with user_first_login as
(
select user_id,min(login_date) as login_date from login_logs group by user_id
having min(login_date) between '2025-04-01' and '2025-04-03'
),
# 获取到2025年4月7日到4月9日登录的用户（分别对应1～3号的第7天）
second_login as
(
select * from login_logs where login_date between '2025-04-07' and '2025-04-09'
)
# 关联两份数据，得出4月1日到4月3日的七日留存率
select u.login_date,count(u.user_id) as fisrt_login_number,count(s.user_id) as second_login_number,
round(100*count(s.user_id) /count(u.user_id),2) as retention_ratio,
json_arrayagg(u.user_id) as first_login_user,json_arrayagg(s.user_id) as second_login_user
from user_first_login u
left join second_login s
on u.user_id = s.user_id
and s.login_date = DATE_ADD(u.login_date,INTERVAL 6 DAY)
group by u.login_date三、周期留存率
以d1~d7首次访问或登录系统的总用户人数为分母，在d8~d14中，再次登录系统的总用户人数为分子，得出的比例，称为d1~d7的周留存率
月留存率同理
1、周留存率
1.1、获取2025年4月1日开始，之后10周范围内，每一周登录用户在下一周的留存率
步骤1：获取2025年4月1日到之后10周（一共70天）范围内，每一周首次登录的用户
步骤2：获取2025年4月8日到之后10周（一共70天）范围内，每周登录的用户（分别对应4月1日之后10周中每一周的下一周）
步骤3：关联两份数据，得出每一周登录用户在下一周的留存率
# 获取在2025年4月1日到之后69天范围内，每一周首次登录的用户。以及每一周的开始日期和结束日期
with week_n_login as (
select
t.*,
floor((UNIX_TIMESTAMP(t.login_date) - UNIX_TIMESTAMP('2025-04-01'))/(24*3600*7))+1 as week,
DATE_ADD('2025-04-01',INTERVAL 7*FLOOR((UNIX_TIMESTAMP(login_date) - UNIX_TIMESTAMP('2025-04-01'))/(24*3600*7)) DAY) as begin_time,
DATE_ADD('2025-04-01',INTERVAL 7*(1+FLOOR((UNIX_TIMESTAMP(login_date) - UNIX_TIMESTAMP('2025-04-01'))/(24*3600*7)))-1 DAY) as end_time
from
(
select user_id,min(login_date) login_date from login_logs group by user_id having min(login_date) between '2025-04-01' and DATE_ADD('2025-04-01',INTERVAL 69 DAY)
) t
),
# 获取到2025年4月8日到之后10周范围内，每周登录的用户（分别对应4月1日之后10周中每一周的下一周）
week_n1_login as (
select distinct user_id,login_date,
FLOOR((UNIX_TIMESTAMP(login_date) - UNIX_TIMESTAMP('2025-04-01'))/(24*3600*7))+1 as week
from login_logs where login_date between '2025-04-08' and DATE_ADD('2025-04-01',INTERVAL 69 DAY)
)
# 关联两份数据，得出每一周登录用户在下一周的留存率
select a.week,count(a.user_id) as basic_num,count(b.user_id) as second_week_num,
round(100*count(b.user_id)/count(a.user_id),2) as week_ratio,any_value(begin_time) begin,any_value(end_time) end_time
from day_n_login a left join day_n1_login b on a.user_id = b.user_id and a.week = b.week-1 group by a.week2、月留存率
以m1（某一个月）首次访问或登录系统的总用户人数为分母，在m2（第2个月，或者后续某个月）中，再次登录系统的总用户人数为分子，得出的比例，称为m1的月留存率
2.1、获取2025年4月、5月的次月留存率
步骤1：获取2025年分别在4月和5月首次登录的用户
步骤2：获取2025年5月和6月登录的用户
步骤3：关联两份数据，得出2025年4月、5月的次月留存率
# 获取在2025年4月和5月分别首次登录的用户
with user_first_login AS
(
select user_id,DATE_FORMAT(min(login_date),'%Y-%m') as login_month from login_logs group by user_id
having min(login_date) between '2025-04-01' and LAST_DAY('2025-05-01')
),
# 获取到2025年5月和6月登录的用户
second_login as
(
select distinct user_id,DATE_FORMAT(login_date,'%Y-%m') as login_month from login_logs
where login_date between '2025-05-01' and LAST_DAY('2025-06-01')
)
# 关联两份数据，得出2025年4月、5月的次月留存率
select u.login_month,count(u.user_id) as first_login_number,count(s.user_id) as second_login_number,
round(100 * count(s.user_id) / count(u.user_id),2) as retention_ratio
from user_first_login u left join second_login s on u.user_id = s.user_id
and s.login_month = DATE_FORMAT(DATE_ADD(concat(u.login_month,'-01'),INTERVAL 1 MONTH),"%Y-%m")
group by u.login_month