【DA】留存率SQL实现

留存率&＃xff1a;是用户分析的核心指标之一。它也是经典的AARRR模型&＃xff08;海盗模型&＃xff09;中就有一个重要节点——留存&＃xff08;Acquisition&＃xff09;。留存率的计算也是用户分析模型的计算基础&＃xff0c;那么如何在数据库中用SQL实现呢&＃xff1f;

常见的留存率有次日留存、三日留存、7日留存、14日留存、30日留存、90日留存等等&＃xff0c;不同产品用户行为的频率是有差别的&＃xff0c;留存率的设定也应该视不同产品而定&＃xff0c;有些低频的产品用周或月的颗粒度就够了。

留存率计算逻辑&＃xff1a;

假如某日新增了100个用户&＃xff0c;第二天登录了50个&＃xff0c;则次日留存率为50/100&＃61;50%&＃xff0c;第三天登录了30个&＃xff0c;则第二日留存率为30/100&＃61;30%&＃xff0c;以此类推&＃xff0c;第7天登录了10个用户&＃xff0c;则7日留存率就是10/100&＃61;10%。

以12月1日的新增用户为例

• 如果12月2日也登录了&＃xff0c;就算做次日留存&＃xff1b;
• 如果12月3日又登录了&＃xff0c;就算做三日留存&＃xff1b;
• 12月6日再次登录&＃xff0c;就算作7日留存

数据说明

计算留存率只需要2个字段&＃xff1a;用户ID (user_id) 和 登录日期 (login_time)

• t_user_login&＃xff1a;表名
• user_id: 用户id&＃xff0c;也可用设备ID等
• login_time&＃xff1a;登录日期时间&＃xff0c;例如&＃xff1a;2020-05-25 16:03:05

实现步骤

• 步骤一&＃xff1a;从数据库中提取user_id和login_time, 并计算 first_day, 用于存储每个用户ID最早登录日期&＃xff08;最小日期&＃xff09;&＃xff1b;
• 步骤二&＃xff1a;用登录日期-最早登录日期&＃xff0c;得到每个登录日期距离最早登录日期的时间间隔&＃xff0c;即留存日期&＃xff1b;
• 步骤三&＃xff1a;对不同留存日期的user_id进行汇总就是留存人数&＃xff0c;除以首日登录人数&＃xff0c;就得到了不同留存时间的留存率。

SQL实现

SELECTlog_day &＃39;日期&＃39;,count( user_id_day0 ) &＃39;新增数量&＃39;,count( user_id_day1 ) / count( user_id_day0 ) &＃39;次日留存率&＃39;,count( user_id_day2 ) / count( user_id_day0 ) &＃39;3日留存率&＃39;,count( user_id_day7 ) / count( user_id_day0 ) &＃39;7日留存率&＃39;,count( user_id_day30 ) / count( user_id_day0 ) &＃39;30日留存率&＃39;
FROM(SELECT DISTINCTlog_day,a.user_id_day0,b.user_id AS user_id_day1,c.user_id AS user_id_day3,d.user_id AS user_id_day7,e.user_id AS user_id_day30 FROM( SELECT DISTINCT Date( login_time ) AS log_day, user_id AS user_id_day0 FROM t_user_login GROUP BY user_id ORDER BY log_day ) aLEFT JOIN t_user_login b ON DATEDIFF( DATE( b.login_time ), a.log_day ) &＃61; 1 AND a.user_id_day0 &＃61; b.user_idLEFT JOIN t_user_login c ON DATEDIFF( date( c.login_time ), a.log_day ) &＃61; 2 AND a.user_id_day0 &＃61; c.user_idLEFT JOIN t_user_login d ON datediff( date( d.login_time ), a.log_day ) &＃61; 6 AND a.user_id_day0 &＃61; d.user_idLEFT JOIN t_user_login e ON datediff( date( e.login_time ), a.log_day ) &＃61; 29 AND a.user_id_day0 &＃61; e.user_id ) temp
GROUP BYlog_day


Reference&＃xff1a;七日留存率-SQL实现