用 SQL 分析不同用户群组留存率

一、前言

用户增长对企业非常重要，主要包括： 新增、留存、召回 。留存率就是用户在某段时间内，开始使用某网站或APP，经过一段时间后仍然继续使用，这类群体就认为是留存用户，留存用户的占比即为留存率。N日留存率=N日留存用户数/新增用户数*100%，反应了网站或APP的质量和保留用户的能力。

本文介绍一个实用的分析留存率的方法： Cohort Analysis （群组分析）。就是利用用户分层&用户建模的方法，将新增用户分成一个一个小组，可以按照获客日期来分，或者按照获客渠道来分，或者按照特定用户行为来分。APP的下载量越高并不是说产品就越好，产品若没有很好的留存，花费大量时间和资源来挖掘新客户也将是竹篮打水一场空。群组分析有利于衡量用户在每个漏斗的用户流失节点、用户流失数据，从而帮助你发现产品的用户的行为模式和用户增长点。从本质上讲，群组分析Cohort Analysis为你提供了一种数据驱动的方法，用群组分析Cohort Analysis方法，可以来帮助提高用户留存率。

二、数据介绍

数据来自某网站2009年至2010年的用户登录数据（activity.csv）和用户的注册信息（user.csv）。
为了方便计算我做了修改，部分数据表预览如下：（特殊说明：数据已脱敏）

1、User表 用户的注册信息
列UID代表用户的唯一标志（账户），
列JOINDATE代表用户的注册日期。

2、Activity表 用户登录数据
列ACTION_ID代表每个用户每一次登录的标志，
列DAY代表用户登录的日期。

三、留存分析

1、计算在t1时刻处于活动状态的用户，然后计算在t2时刻留存的用户数

在SQL中可以用selfjoin方法，以次日留存为例：

select * from activity
left join activity as future_info 
on  activity.UID = future_info.UID
and activity.DAY= subdate(future_info.DAY,1);

Join之后的表格每一行都包含了t1和t2时刻的登录情况，t2没有留存则为null。

2、以次日留存为例，计算去重后的活跃用户数、留存用户数、留存率

根据以上join之后的表格计算：

select
  	activity.DAY, 
  	count(distinct activity.UID) as active_user, # 某日活跃用户，去重
	count(distinct future_info.UID) as retained_user,  # 次日留存用户，去重
 	round(count(distinct future_info.UID)/count(distinct activity.UID),2) as retention_rate #留存率
from activity
left join  activity as future_info
on activity.UID = future_info.UID
and activity.DAY=subdate(future_info.DAY,1)     # 指定留存周期，这里是次日留存所以取相差一天的活动
group by activity.DAY
order by activity.DAY
limit