背景
APP分析中經(jīng)常用到AARRR模型（海盜模型）用來分析APP的現(xiàn)狀，其中一個重要節(jié)點就是提高留存（Acquisition），而留存率這個指標在這個階段可以說是核心指標也不為過蹬屹。那如何用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ù)
數(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;
獲得數(shù)據(jù)
步驟二：增加一列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;
得到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
最終數(shù)據(jù)
結(jié)語
根據(jù)最后得到的數(shù)據(jù)，我們直接用除法或者加一個SQL語句舟肉，就能算出來留存率修噪，之后的分析就是看自己了。
參考博客
https://blog.treasuredata.com/blog/2016/07/22/rolling-retention-done-right-in-sql/
