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上的统计';
-- 数据表见附件
每个客户cust在每个年份yr上的统计

每个客户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 
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
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 分成多列展示。

计算留存率

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

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

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