问题及分析
根据用户记录,按一定时间周期,计算用户留存量、留存率。
留存率 = 留存量 / 初始量 * 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 分成多列展示。
计算留存率
观察上面平摊表,从新客户开始逐年的留存率的计算就很直观了。
如果对按右表整列求和,得到按年的所有新客户、逐年总留存率,可视为多年的新客户整体留存率,也是有意义的。虽然不太直观。
甚至,还可以对右表整列求和,并计算留率,可视为每年的所有客户(包括新客与留存客户)在之后每年的留存率。