背景
APP分析中經(jīng)常用到AARRR模型（海盜模型）用來分析APP的現(xiàn)狀，其中一個重要節(jié)點就是提高留存（Acquisition）促煮，而留存率這個指標(biāo)在這個階段可以說是核心指標(biāo)也不為過俱萍。那如何用SQL計算留存率呢？
留存率計算方法
假如今天新增了100名用戶颂翼，第二天登陸了50名，則次日留存率為50/100=50%慨灭，第三天登錄了30名朦乏，則第二日留存率為30/100=30%,以此類推。
用SQL的計算思路
-
用SQL調(diào)取出user_id和用戶login_time的表氧骤，獲得新增用戶登錄時間表呻疹。
根據(jù)user_id和login_time，增加一列first_day筹陵，此列存著每個用戶最早登錄時間刽锤。
有了最早登錄時間和所有的登錄時間镊尺，再增加一列by_day，這一列是用login_time - first_day 并思，得到0庐氮，1，2宋彼，3弄砍，4，5......宙暇，這就得到了某一天登錄離第一次登錄有多長時間输枯。
-
然后從表中提取數(shù)據(jù)，找到first_day對應(yīng)的with_first列中0有多少個占贫，1有多少個桃熄，一直到7以上。
根據(jù)此表型奥，就很容易計算出每天引流的留存率瞳收。
實際操作
數(shù)據(jù)：是我用excel隨便模擬的數(shù)據(jù)，與真實情況不符厢汹。
數(shù)據(jù)庫：MySQL
步驟一：從數(shù)據(jù)庫中提取出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對應(yīng)的最早登錄時間，然后匹配帶登錄時間的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;
步驟三：用登錄時間-最早登錄時間得到一列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
最后一步：提取字段作為列名
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
結(jié)語
根據(jù)最后得到的數(shù)據(jù)烫葬，我們直接用除法或者加一個SQL語句界弧，就能算出來留存率，之后的分析就是看自己了搭综。
參考博客
https://blog.treasuredata.com/blog/2016/07/22/rolling-retention-done-right-in-sql/
