留存率是衡量用户质量的最重要指标之一,因此计算用户留存率是用户数据分析中必须掌握的技能之一。
留存率指标中,通常需要关注次日留存、3日留存、7日留存和月留存。对新增用户而言,需要关注更细颗粒度的数据,也就是7日内每天的留存率。
7日内每天的留存率,最笨的方式是一天一天计算,如:
select count(distinct user_id)
from table
where date = ‘2019-12-01’
and user_id in
(select distinct user_id
from table
where date = ‘2019-12-02’)
group by date
这样,计算7日内每天的留存率,需要查询7次,子查询中日期需要更改7次。有没有用一个程序计算出7日内每天留存率的方法呢?
答案当然是‘Yes’,只需要计算日期与初始日期的间隔gap,然后根gap group by就可以了。
假设有两张表:新增用户表table_new_users,活跃用户表table_active_users,查询2019-12-01日的新增用户在7日内的留存率。
下面我们分步骤展示程序写法,每个步骤中的代码包含了前一步骤的代码。
-
确定date之间的天数间隔gap
-
计算每天留存的用户数retention_num
-
计算7日内留存率
查询结果形式如下:
一个SQL程序搞定7日内留存率计算比一天一天计算的查询要快多了,尤其是计算连续多天用户的留存率时,就更可以节省时间了(计算连续多天用户的留存率可以借助python)。