1 用户留存率

1.1 定义

在互联网行业中，用户在某段时间内开始使用应用，经过一段时间后，仍然继续使用该应用的用户，被认作是留存用户。这部分用户占当时新增用户的比例即是留存率，会按照每隔1单位时间（例日、周、月）来进行统计。顾名思义，留存指的就是“有多少用户留下来了”。留存用户和留存率体现了应用的质量和保留用户的能力。(来自 知乎专栏)

1.2 计算逻辑

摘自 https://zhuanlan.zhihu.com/p/123292172

留存率：新增用户中登录用户数/新增用户数*100%（一般统计周期为天）

新增用户数：在某个时间段（一般为第一整天）新登录应用的用户数；

登录用户数：登录应用后至当前时间，至少登录过一次的用户数；

第N日留存率：指的是新增用户日之后的第N日依然登录的用户占新增用户的比例

第1日留存率（即“次留”）：（当天新增的用户中，新增日之后的第1天还登录的用户数）/第一天新增总用户数；

第3日留存率：（当天新增的用户中，新增日之后的第3天还登录的用户数）/第一天新增总用户数；

第7日留存率：（当天新增的用户中，新增日之后的第7天还登录的用户数）/第一天新增总用户数；

第30日留存率：（当天新增的用户中，新增日之后的第30天还登录的用户数）/第一天新增总用户数

2 指标开发

现在假设有如下两个行为表:

--用户注册, 注册即新增createtable s_register (user_id bigint, reg_date date);--用户登录createtable s_login (user_id bigint, login_date date);

2.1 传统纯SQL开发

2.1.1 定义

1. 注册时间

注册事件本身就是用户粒度的明细事实, 注册时间是这个事实的一个属性. 有的系统可能注册流程有很多状态的流转, 也可能会有脏数据, 保险的做法是做成用户粒度的一个聚合:

--隔日调度运行, 每日的增量统计, 即 1d 统计, 结果表为 dws_user_1dinsert overwrite table dws_user_1d partition (ds ='${bizdate}')select user_id,       min(reg_date)as reg_date 
from s_register_delta --注册的日增量表where ds ='${bizdate}'--日期分区groupby user_id
--隔日调度运行, 历史累计统计, 即 td 统计, 结果表为 dws_user_tdinsert overwrite table dws_user_td partition (ds ='${bizdate}')select user_id,       min(reg_date)as reg_date 
from dws_user_1d --不限定分区, 此处可以通过增量累计实现, 当前写法为示例groupby user_id

2. 最近一天登录次数

--隔日调度运行, 每日的增量统计, 即 1d 统计, 结果表为 dws_user_1d(先不考虑两个行为的合并插入同一个表的问题)insert overwrite table dws_user_1d partition (ds ='${bizdate}')select user_id,count(login_date)as login_cnt --登录次数from s_login_delta --登录的日增量表where ds ='${bizdate}'--日期分区groupby user_id

2.1.2 整体粒度指标

留存率指标特别的地方在于, 当天的留存率当天是不知道的, 要根据未来的留存来计算. 这里就需要引入"延时调度"了：

--延迟7日调度运行, 每日的增量统计, 即 1d 统计, 结果表为 dws_all_1dinsert overwrite table dws_all_1d partition (ds ='${yyyymmdd - 7}')--yyyymmdd-7 表示T-7select round(remain_cnt / reg_cnt *100,2)--7日留存率from(selectcount(*)as reg_cnt,--注册(新增)人数count(case when reg_diff =7and login_cnt >0 then td.user_id end)as remain_cnt --留存人数from(select user_id,                       reg_date,                       datediff('${bizdate}', reg_date,'dd')as reg_diff --注册距今天数from dws_user_td 
where ds ='${bizdate}') td
               left outer join(select user_id,                       login_cnt 
from dws_user_1d 
where ds ='${bizdate}') d1
on td.user_id= d1.user_idwhere reg_date ='${yyyymmdd -7}') t --yyyymmdd-7 表示T-7

2.2 Dataphin规范建模方式开发

假设已有一个用户维度逻辑表:

• dim_user

两个事实逻辑表:

• fct_reg_di, 关联dim_user, 其来源物理表为 s_register_delta, 字段与来源表一致
• fct_login_di, 关联dim_user, 其来源物理表为 s_login_delta, 字段与来源表一致

当前的规范建模, 无法直接创建一个"留存率"的原子指标, 以及基于此来实现不同统计周期(7日留存, 15日留存等)的派生指标. 是需要参照传统SQL方式拆解为不同的指标来实现。

2.2.1 原子指标

注: Dataphin目前不支持基于汇总逻辑表做规范建模(规划支持中), 当前可以通过将派生指标通过SQL引入字段方式挂载到维度逻辑表(dim_user)作为行为属性来实现派生指标的二次建模。

2.2.2 业务限定

2.2.3 派生指标

2.2.4 衍生指标（规划支持中）

衍生指标是Dataphin后续版本中即将引入的一个功能, 衍生指标是使用同一个汇总逻辑表内的若干个派生指标通过多则运算(非聚合)生成的新指标。

留存率即一个衍生指标, 可以基于以上统计粒度为all的汇总逻辑表(dws_all)中的派生指标计算获得:

user_cnt_1d_remain7 / user_cnt_1d

2.2.5 当前最优解

从以上规范建模过程来看:

1. Dataphin二次建模能力目前支持场景优先, 可以先将派生指标作为行为属性上挂维度逻辑表, 有序版本针对此问题进行体验优化
2. Dataphin暂时不支持延时调度
3. 衍生指标规划支持中

当前最优的解法, 是使用规范建模生成上表中非黄色标记的指标, 黄色部分通过SQL方式来加工(参考传统SQL方式)。

3 支持计划

Dataphin在将来版本中, 规划支持留存率的快捷配置, 只要给出新增行为与留存行为的逻辑表, 自动生成留存率，敬请期待。