SQL语句 — 留存率

参考：https://zhuanlan.zhihu.com/p/143494489

一、什么是留存率？

      n日留存率 = 第n天还在登录的用户数/新增的用户数

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

二、SQL实现

1、数据说明

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

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

2、实现步骤

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

3、SQL实现 

SELECT log_day '日期',
	   count( user_id_day0 ) '新增数量',
	   count( user_id_day7 ) / count( user_id_day0 ) '7日留存率',
FROM(
	SELECT DISTINCT log_day, a.user_id_day0, d.user_id AS user_id_day7,
	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 ASC
			) a
		LEFT JOIN t_user_login d ON datediff(date( d.login_time ), a.log_day ) = 6 
		AND a.user_id_day0 = d.user_id
	) temp 
GROUP BY
	log_day

三、如何提高用户留存率？

这个问题就比较广泛了，比如说可以通过以下几种方式

1、活动运营，刺激用户欲望

活动运营是促活用户效果最好的方法之一，对于电商APP，利用节日进行促销活动、送券活动；对于金融理财类，通过活动促使用户进行绑卡、投资；对于教育APP，邀请大V线上讲课，开展线下沙龙活动等。

2、合理做好消息推送

过于频繁的push会造成用户流失，但合理适时进行push推送能起到唤醒用户的作用，push推送，要做到精准化，前提是你需要了解你的用户，给用户进行标签管理。针对不用标签的用户进行不同内容的推送，提高用户体验。

3、利用签到机制等提高用户粘性

签到功能，对于提高用户粘性有很大的帮助。签到可获取积分，连续签到，积分叠加等形式。

经典案例：淘宝签到获得淘金币、京东签到获得京豆、优惠券等等。

再有就是要不断提升用户体验等等。