SQL计算用户留存率，原理及流程拆解

内容索引

问题及分析

根据用户记录，按一定时间周期，计算用户留存量、留存率。

留存率 = 留存量 / 初始量 * 100%

从某一个时间段作为起点，作为初始用户，一年后这些用户还有多少，两年后还有多少… 以此即可做计算。这里有个问题，就是这个起点的用户，是否也是从更早以前即存在、并留存到现在的；如果把本身即是“留存”而来的，留存率是不准确的。所以，通常要以用户首次出现开始计算。

简单来说，就是从新用户开始，计算其N一个时间段后留存；而每个时间段，都有新用户，所以这是个斜三角形的表。

以客户的销售记录为例，分步拆解计算原理。

已有原始数据表，包含了每个客户的销售记录，包括销售时间、销售数量、销售金额等。按年计算客户留存。

第一步，计算出初级表。

按年(yr)计算每个客户(cust)的统计信息，销量(sales)、销售金额(amount)。逻辑上，该结果表有个惟一键 (yr+cust)，后续所有计算都从该表出发。

表结构如下

CREATE TABLE `yrept` (
  `cust` int(11) NOT NULL DEFAULT 0 COMMENT '客户',
  `yr` int(11) NOT NULL DEFAULT 0 COMMENT '年份',
  `sales` int(11) NOT NULL DEFAULT 0 COMMENT '销量',
  `amount` decimal(30,2) NOT NULL DEFAULT 0.00 COMMENT '金额',
  UNIQUE KEY `cust_yr` (`cust`,`yr`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
 COMMENT='每个客户cust在每个年份yr上的统计';
-- 数据表见附件

从原始数据表计算出一张每个时间段里每个用户的统计信息，

第二步，计算每个客户首次出现的年份。

SELECT cust,min(yr) as yr FROM `yrept` GROUP BY `cust`;

第三步，从原表yrept入手，扩展表，联入每个行对应客户的首次出现年份，预览数据。

原表yrept是每客户在各年份上的统计，那为该表增加一个字段，该行客户cust的首次出现的年份yr_1st ，语句如下

SELECT a.*,'|' as s,b.*,'|' as p, a.yr-b.yr_1st AS `later_yr`
FROM `yrept` a INNER JOIN (
    SELECT cust,min(yr) as yr_1st FROM `yrept` GROUP BY `cust`
) b ON a.cust=b.cust
WHERE 1
ORDER BY b.cust, b.yr_1st
;

另加一个计算列，相对于客户首次出现后的年数 later_yr。

汇总统计

上步结果表，关注其中的 cust, yr_1st, yr 三列，即是客户cust首次出现起，之后每年留存的客户信息（有对应记录即是留存）。对 yr_1st + yr 做汇总统计，即是每个年份新增客户数目、及之后每个年份的留存统计。

SELECT b.yr_1st,a.yr AS in_yr,count(*) as cnt
  ,count(DISTINCT a.cust) as unq_cust, SUM(a.sales) as sales 
  -- ,SUM(a.amount) as amount
FROM yrept a INNER JOIN (
    SELECT cust,min(yr) as yr_1st FROM yrept GROUP BY cust 
) b ON a.cust=b.cust 
WHERE 1 
GROUP BY b.yr_1st,in_yr 
ORDER BY b.yr_1st,in_yr
;

换个形式，以将 yr 换成later_yr做统计，即客户cust首次出现起，N年后的留信息。事实这两种形式是等价的。

SELECT b.yr_1st,a.yr-b.yr_1st AS later_yr,count(*) as cnt
  ,count(DISTINCT a.cust) as unq_cust, SUM(a.sales) as sales
  -- ,SUM(a.amount) as amount
FROM yrept a INNER JOIN (
    SELECT cust,min(yr) as yr_1st FROM yrept GROUP BY cust
) b ON a.cust=b.cust
WHERE 1
GROUP BY b.yr_1st,later_yr
ORDER BY b.yr_1st,later_yr
;

以上两个结果，其中的 cnt 与unq_cust 两列，事实上也是等价的，就是惟一客户人数。上两条语句中，还有注释掉的一行是对总金额的计算，可以按销售金额来计算留存，有需要可选用。

按需要平摊成需要的形式

比如以yr_1st为行、按later_yr或in_year 分成多列展示。

计算留存率

观察上面平摊表，从新客户开始逐年的留存率的计算就很直观了。

如果对按右表整列求和，得到按年的所有新客户、逐年总留存率，可视为多年的新客户整体留存率，也是有意义的。虽然不太直观。

甚至，还可以对右表整列求和，并计算留率，可视为每年的所有客户（包括新客与留存客户）在之后每年的留存率。