mysql_留存率计算

作者：YXN-sql 阅读量：74 发布日期：2023-04-16

1、基本概念

留存是指通过各种推广渠道获得的用户，在一段时间后逐渐流失，剩下来的用户称为留存用户。推广渠道可以包括朋友圈广告、搜索引擎搜索、内容平台等，用户通过这些渠道了解并使用产品。留存用户是产品推广的重要指标，反映了用户对产品的持续使用和满意度。留存率的提高对于产品的发展和用户群体的稳定具有重要意义。

通过留存分析可以衡量各个推广渠道的质量，找出哪些渠道带来的用户留存率较高。针对留存率高的渠道，可以加大推广力度，提高用户获取效率；而对于留存率低的渠道，可以考虑减少资源投入或者调整推广策略，以降低资源损失。通过针对不同渠道的留存分析，可以优化推广策略，提高产品对用户的粘性，促进用户的持续使用和满意度。留存分析是评估产品推广效果和用户忠诚度的重要手段。

粘性指的是产品的留存率，即用户持续使用产品的程度。如果一个产品的留存率较低，说明其粘性不高，用户流失较多。因此，我们需要通过各种方式提高留存率，以增强产品的粘性。

留存率可以根据不同时间段来衡量，包括次日留存、第三日留存、第七日留存和第三十日留存等。次日留存指的是第一天新增用户中在第二天继续使用产品的用户数量占第一天新增用户总数的比例。比如，如果第一天新增了5个用户，第二天有2个用户继续使用产品，则次日留存率为2/5，即40%。类似地，第三日留存、第七日留存和第三十日留存的计算方法也类似。

留存率达到多少才算是好的留存呢？Facebook提出了一个40-20-10的留存法则，即新用户的次日留存率应达到40%，第七日留存率应达到20%，第三十日留存率应达到10%。如果产品的留存率能够满足这一标准，就可以认为是较好的留存表现。这种留存标准可以帮助衡量产品对用户的吸引力和粘性，对于产品的长期发展和用户忠诚度具有重要意义。

2、实际案例

假设有一张用户登录表login_info，包含三个字段：用户ID(user_id)、登录日期(login_date)和登录时间(login_time)。每次用户成功登录系统都会在表中记录一条数据，例如用户1001在2023年10月2日的18:20登录系统，那么会有一条对应的记录。

现在的问题是计算出10月2日的次日留存率。

测试数据：

CREATE TABLE `login_info` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `login_date` date DEFAULT NULL,
  `login_time` time DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=14 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `login_info` (`id`, `user_id`, `login_date`, `login_time`) VALUES (1, '1001', '2003-10-02', '16:20:00');
INSERT INTO `login_info` (`id`, `user_id`, `login_date`, `login_time`) VALUES (2, '1002', '2003-10-02', '19:30:00');
INSERT INTO `login_info` (`id`, `user_id`, `login_date`, `login_time`) VALUES (3, '1003', '2003-10-02', '08:20:00');
INSERT INTO `login_info` (`id`, `user_id`, `login_date`, `login_time`) VALUES (4, '1001', '2003-10-03', '19:00:00');
INSERT INTO `login_info` (`id`, `user_id`, `login_date`, `login_time`) VALUES (5, '1001', '2003-10-04', '20:20:00');
INSERT INTO `login_info` (`id`, `user_id`, `login_date`, `login_time`) VALUES (6, '1001', '2003-10-01', '16:20:00');
INSERT INTO `login_info` (`id`, `user_id`, `login_date`, `login_time`) VALUES (7, '1002', '2003-10-01', '19:30:00');
INSERT INTO `login_info` (`id`, `user_id`, `login_date`, `login_time`) VALUES (8, '1001', '2003-10-02', '08:20:00');
INSERT INTO `login_info` (`id`, `user_id`, `login_date`, `login_time`) VALUES (9, '1001', '2003-10-02', '19:00:00');
INSERT INTO `login_info` (`id`, `user_id`, `login_date`, `login_time`) VALUES (10, '1001', '2003-10-01', '06:20:00');
INSERT INTO `login_info` (`id`, `user_id`, `login_date`, `login_time`) VALUES (11, '1001', '2003-10-01', '16:25:00');
INSERT INTO `login_info` (`id`, `user_id`, `login_date`, `login_time`) VALUES (12, '1001', '2003-10-01', '09:30:00');
INSERT INTO `login_info` (`id`, `user_id`, `login_date`, `login_time`) VALUES (13, '1001', '2003-10-01', '05:20:00');

解题思路：

计算每个用户的第一次登录日期；
计算每个用户的登录日期，并按用户和登录日期分组；
通过用户ID进行左连接；
最后计算次日留存率。

具体代码：

SELECT
  count( t2.user_id )/ count( t1.user_id ) as day_1_retention_ratio
FROM
  ( 
    SELECT 
      user_id, min( login_date ) AS first_login 
    FROM 
      login_info GROUP BY user_id ) t1
    LEFT JOIN ( 
      SELECT 
        user_id, login_date FROM login_info 
      GROUP BY 
        user_id, login_date ) t2 
ON 
  t1.user_id = t2.user_id 
  AND date_add( t1.first_login, INTERVAL 1 DAY ) = t2.login_date 
WHERE
  t1.first_login = '2023-10-02';

如果你会计算次日留存率，那么第3日留存率、第7日留存率和第30日留存率都是同样的道理。

YXN-sql

2023-04-16