背景
APP分析中经常用到AARRR模型(海盗模型)用来分析APP的现状,其中一个重要节点就是提高留存(Acquisition),而留存率这个指标在这个阶段可以说是核心指标也不为过。那如何用SQL计算留存率呢?
留存率计算方法
假如今天新增了100名用户,第二天登陆了50名,则次日留存率为50/100=50%,第三天登录了30名,则第二日留存率为30/100=30%,以此类推。
用SQL的计算思路
用SQL调取出user_id和用户login_time的表,获得新增用户登录时间表。
根据user_id和login_time,增加一列first_day,此列存着每个用户最早登录时间。
有了最早登录时间和所有的登录时间,再增加一列by_day,这一列是用login_time - first_day ,得到0,1,2,3,4,5......,这就得到了某一天登录离第一次登录有多长时间。
获得一个这样的表
然后从表中提取数据,找到first_day对应的with_first列中0有多少个,1有多少个,一直到7以上。
最后获得这个表
根据此表,就很容易计算出每天引流的留存率。
实际操作
数据:是我用excel随便模拟的数据,与真实情况不符。
模拟数据
数据库:MySQL
步骤一:从数据库中提取出user_id和login_time并排序
select
user_id,
str_to_date(login_time,'%Y/%m/%d') login_time
from user_info
group by 1,2;
获得数据
步骤二:增加一列first_day,存储每个用户ID最早登录时间
SELECT
b.user_id,
b.login_time,
c.first_day
FROM
(select
user_id,
str_to_date(login_time,'%Y/%m/%d') login_time
from user_info
group by 1,2) b
LEFT JOIN
(SELECT ---找到user_id对应的最早登录时间,然后匹配带登录时间的user_id
user_id,
min(login_time) first_day
FROM
(select
user_id,
str_to_date(login_time,'%Y/%m/%d') login_time
from user_info
group by 1,2) a
group by 1) c
on b.user_id = c.user_id
order by 1,2;
得到first_day列
步骤三:用登录时间-最早登录时间得到一列by_day
SELECT
user_id,
login_time,
first_day,
DATEDIFF(login_time,first_day) as by_day
FROM
(SELECT
b.user_id,
b.login_time,
c.first_day
FROM
(SELECT
user_id,
str_to_date(login_time,'%Y/%m/%d') login_time
FROM user_info
GROUP BY 1,2) b
LEFT JOIN
(SELECT
user_id,
min(login_time) first_day
FROM
(select
user_id,
str_to_date(login_time,'%Y/%m/%d') login_time
from user_info
group by 1,2) a
group by 1) c
on b.user_id = c.user_id
order by 1,2) e
order by 1,2
得到by_day
最后一步:提取字段作为列名
SELECT
first_day,
sum(case when by_day = 0 then 1 else 0 end) day_0,
sum(case when by_day = 1 then 1 else 0 end) day_1,
sum(case when by_day = 2 then 1 else 0 end) day_2,
sum(case when by_day = 3 then 1 else 0 end) day_3,
sum(case when by_day = 4 then 1 else 0 end) day_4,
sum(case when by_day = 5 then 1 else 0 end) day_5,
sum(case when by_day = 6 then 1 else 0 end) day_6,
sum(case when by_day >= 7 then 1 else 0 end) day_7plus
FROM
(SELECT
user_id,
login_time,
first_day,
DATEDIFF(login_time,first_day) as by_day
FROM
(SELECT
b.user_id,
b.login_time,
c.first_day
FROM
(SELECT
user_id,
str_to_date(login_time,'%Y/%m/%d') login_time
FROM user_info
GROUP BY 1,2) b
LEFT JOIN
(SELECT
user_id,
min(login_time) first_day
FROM
(select
user_id,
str_to_date(login_time,'%Y/%m/%d') login_time
FROM
user_info
group by 1,2) a
group by 1) c
on b.user_id = c.user_id
order by 1,2) e
order by 1,2) f
group by 1
order by 1
最终数据
结语
根据最后得到的数据,我们直接用除法或者加一个SQL语句,就能算出来留存率,之后的分析就是看自己了。
参考博客