Hive 实战之留存分析(49)-一一网

留存分析

留存的定义其实很简单，那就是如果用户昨天使用了我们的平台，今天也使用了那说就是留存用户，也就是一日留存，同理如果明天还来的话，那就是两日留存，举个例子如果用户在5月1日第一次使用我们的产品。如果5月2日他还使用了,那么5月1日的“一日留存”加一，同理5月3日他又使用率，5月1日的“两日留存”加一。

开始之前我们先说一下为什么要进行留存分析，留存分析可以反映一个应用的潜力或者是健康程度,为什么这么说呢，如果说一个应用的留存很低的话，那说明用户来了一次就不来了，我们举个例子如果一家公司每天的新增还可以，但是如果用户的留存很低的话，那么其实说明了一件事，那就是用户可能都是用钱砸出来的，例如广告或者是引流，然后用户注册之后对应用并不感兴趣或者是应用做的很差，用不不满意然后就不来了，当然还有一种可能就是数据都是假的，我以前也经历过这样的公司，每天新增用户三万左右，用户留存很差然后就果断走了，前后呆了不到一个月，后来听说那个公司倒闭了。

还有一点就是用户留存率一般是面向新增用户的概念,是指某一天注册后的几天还是否活跃,是以每天为单位进行计算的。一般收到的需求都是一个时间段内的新增用户的几天留存或者是某一天的新增用户的留存，当然这也不是绝对的活跃用户的留存也是很重要的活着是老用户的留存也是很重要的

当然到底是一天还是一段时间是取决于公司的业务，例如有一段时间用户开辟了一个新的广告渠道，那么它就有可能关注这个渠道或者是这段时间新增用户的留存，从而更好地衡量渠道的ROI

虽然留存分析很重要，但是它的计算却不是很复杂，接下来我们分析一下它怎么计算

新用户的计算

前面我们说了留存分析我们一般针对的是新用户，所以我们首先要做的一件事就是计算新用户，至于新用户的计算这里我们提供两个思路

直接从业务库同步新增用户，因为业务系统肯定是有用户表的，那么我们可以直接进行同步
数据平台进行维护，我们可以把出现在平台上的用户维护下来，那么在平台上出现过的用户就是新用户

我们主要介绍的是第一种，至于第二种我们在后面单独介绍，以及对比一下这二者的不同。

活跃用户留存

首先我们看一下留存的定义是什么，我们认为今天如果在平台上出现的用户，那么它明天还在平台上出现那么我们将其称之为一日留存，同理如果30天后还出现在了平台上，我们称之为30留存，需要注意的是我们这里并没有要求连续，这里注意一下。

这里我们先不考虑新用户，任何用户都可以其实就是活跃用户，假设我们有一张用户行为日志表dwd_jyxd_patient_page_view_di ,然后我们希望计算过去30的留存

select
  a.ds,
  count(distinct a.union_id) as cnt,
  sum(if(datediff(to_date(a.ds,'yyyymmdd'),to_date(b.ds,'yyyymmdd'))=1,1,0))as cnt1,
  sum(if(datediff(to_date(a.ds,'yyyymmdd'),to_date(b.ds,'yyyymmdd'))=2,1,0))as cnt2,
  sum(if(datediff(to_date(a.ds,'yyyymmdd'),to_date(b.ds,'yyyymmdd'))=3,1,0))as cnt3,
  sum(if(datediff(to_date(a.ds,'yyyymmdd'),to_date(b.ds,'yyyymmdd'))=11,1,0))as cnt11,
  sum(if(datediff(to_date(a.ds,'yyyymmdd'),to_date(b.ds,'yyyymmdd'))=14,1,0))as cnt14,
  sum(if(datediff(to_date(a.ds,'yyyymmdd'),to_date(b.ds,'yyyymmdd'))=15,1,0))as cnt15,
  sum(if(datediff(to_date(a.ds,'yyyymmdd'),to_date(b.ds,'yyyymmdd'))=21,1,0))as cnt21,
  sum(if(datediff(to_date(a.ds,'yyyymmdd'),to_date(b.ds,'yyyymmdd'))=28,1,0))as cnt28,
  sum(if(datediff(to_date(a.ds,'yyyymmdd'),to_date(b.ds,'yyyymmdd'))=30,1,0))as cnt30
from
(
  select
  union_id,ds
  from
  dwd_jyxd_patient_page_view_di
  where
  ds='${ds}'
  group by
  union_id,ds
) a
left  join
(
  select
  union_id,ds
  from
  dwd_jyxd_patient_page_view_di
  where
  ds>=to_char(date_sub(to_date('${ds}','yyyymmdd'),30),'yyyymmdd')
  group by
  union_id,ds
) b
on
a.union_id=b.union_id
and a.ds>=b.ds
group by
a.ds
;
复制代码

这里我们简单介绍一下这个代码

我们的驱动表是a,需要注意的是我们是按照用户去重了的，ds是业务日期，所以是按照用户进行去重的
第二张表b我们是获取了过去30天的数据，然后按照日期和用户两个维度进行了去重
然后我们用第一张表关联了第二张表，如果两张表的ds 之差等与1则说明是一日留存用户，同理等于30则说明是30日留存用户，最后我们进行了求和，你也可以在if 判断的时候返回union_id然后使用count 函数。
需要注意的时按照我们留存的定义，30日留存指的是29天前的那一天的登陆的用户，在今天还有多少人登陆，也就是说我们应该是用30天前的数据关联今天的数据计算，但是因为是等值关联inner 关联，所以我们反过来用当前数据去关联历史数据也是一样的。

下面是计算结果

ds  cnt   cnt1   cnt2   cnt3   cnt11  cnt14  cnt15  cnt21  cnt28  cnt30
+---+-----+-----+-----+-----+------+------+------+------+------+------+
20210621   3933   1462   1416   1436   1262   1201   1166   0  0  0
复制代码

新增用户的留存

接下来我们引入新用户的逻辑，按照我们前面说的我们可以直接从业务库直接获取新增用户，然后进行计算。

select
  a.ds,
  count(distinct a.union_id) as cnt,
  sum(if(datediff(to_date(a.ds,'yyyymmdd'),to_date(b.ds,'yyyymmdd'))=0,1,0))as cnt0,
  sum(if(datediff(to_date(a.ds,'yyyymmdd'),to_date(b.ds,'yyyymmdd'))=1,1,0))as cnt1,
  sum(if(datediff(to_date(a.ds,'yyyymmdd'),to_date(b.ds,'yyyymmdd'))=2,1,0))as cnt2,
  sum(if(datediff(to_date(a.ds,'yyyymmdd'),to_date(b.ds,'yyyymmdd'))=3,1,0))as cnt3,
  sum(if(datediff(to_date(a.ds,'yyyymmdd'),to_date(b.ds,'yyyymmdd'))=11,1,0))as cnt11,
  sum(if(datediff(to_date(a.ds,'yyyymmdd'),to_date(b.ds,'yyyymmdd'))=14,1,0))as cnt14,
  sum(if(datediff(to_date(a.ds,'yyyymmdd'),to_date(b.ds,'yyyymmdd'))=15,1,0))as cnt15,
  sum(if(datediff(to_date(a.ds,'yyyymmdd'),to_date(b.ds,'yyyymmdd'))=21,1,0))as cnt21,
  sum(if(datediff(to_date(a.ds,'yyyymmdd'),to_date(b.ds,'yyyymmdd'))=28,1,0))as cnt28,
  sum(if(datediff(to_date(a.ds,'yyyymmdd'),to_date(b.ds,'yyyymmdd'))=30,1,0))as cnt30
from
(
  select
  ds,
  union_id
  from
  ods_lo_applet_user_df
  where
  ds='${ds}'
  and date(create_time)='2021-06-01'
) a
left join
(
  select
  union_id,ds
  from
  dwd_jyxd_patient_page_view_di
  where
  ds>=to_char(date_sub(to_date('${ds}','yyyymmdd'),30),'yyyymmdd')
  group by
  union_id,ds
) b
on
a.union_id=b.union_id
and a.ds>=b.ds
group by
a.ds
;
复制代码

这里我们我们直接使用2021-06-01 的新用户来计算，它的30天的留存，解释一下因为系统上线没有30天所以没有30天的留存，这里我还特意算了cnt0，你会发现它不等于新增用户数，这说明了用户当天注册之后并没有到平台上来。

ds	cnt	cnt0	cnt1	cnt2	cnt3	cnt11	cnt14	cnt15	cnt21	cnt28	cnt30
+---+----+-----+-----+-----+-----+------+------+------+------+------+------+
20210621	792	21	23	25	19	30	39	48	0	0	0
复制代码

同理如果我们要计算’2021-06-01’到’2021-06-10′ 这段时间内的新用户留存，我们只需要改新用户的逻辑就可以了date(create_time) between'2021-06-01'and '2021-06-10'

留存率

其实关于留存率其实和留存本质上没有啥大的不同，但是不一样的是它们反馈的信息不同，留存只是一个具体的数字，例如我告诉你3日留存是500，那你能获得什么信息吗，不能因为你没有参考，不能判断出来到底是多少用户留存了500，所以我们引出了留存率的概念。

其实这里有一个问题就是我们计算上面新增用户留存的时候注意到，我们的新用户并没有全部到平台上来，也就是注册之后没有登陆，所以你这个时候要弄清楚你的需求方是怎么看这个问题的，要不要以第一天登陆的用户作为基础用户来算留存率或者还是有其他计算方法，这里我们还是以注册用户数来算。

select
  a.ds,
  count(distinct a.union_id) as cnt,
  round(sum(if(datediff(to_date(a.ds,'yyyymmdd'),to_date(b.ds,'yyyymmdd'))=0,1,0))/count(distinct a.union_id), 2)as rate0,
  round(sum(if(datediff(to_date(a.ds,'yyyymmdd'),to_date(b.ds,'yyyymmdd'))=1,1,0))/count(distinct a.union_id), 2)as rate1,
  round(sum(if(datediff(to_date(a.ds,'yyyymmdd'),to_date(b.ds,'yyyymmdd'))=2,1,0))/count(distinct a.union_id), 2)as rate2,
  round(sum(if(datediff(to_date(a.ds,'yyyymmdd'),to_date(b.ds,'yyyymmdd'))=3,1,0))/count(distinct a.union_id), 2)as rate3,
  round(sum(if(datediff(to_date(a.ds,'yyyymmdd'),to_date(b.ds,'yyyymmdd'))=11,1,0))/count(distinct a.union_id),2) as rate11,
  round(sum(if(datediff(to_date(a.ds,'yyyymmdd'),to_date(b.ds,'yyyymmdd'))=14,1,0))/count(distinct a.union_id),2) as rate14,
  round(sum(if(datediff(to_date(a.ds,'yyyymmdd'),to_date(b.ds,'yyyymmdd'))=15,1,0))/count(distinct a.union_id),2) as rate15,
  round(sum(if(datediff(to_date(a.ds,'yyyymmdd'),to_date(b.ds,'yyyymmdd'))=21,1,0))/count(distinct a.union_id),2) as rate21,
  round(sum(if(datediff(to_date(a.ds,'yyyymmdd'),to_date(b.ds,'yyyymmdd'))=28,1,0))/count(distinct a.union_id),2) as rate28,
  round(sum(if(datediff(to_date(a.ds,'yyyymmdd'),to_date(b.ds,'yyyymmdd'))=30,1,0))/count(distinct a.union_id),2) as rate30
from
(
  select
  ds,
  union_id
  from
  ods_lo_applet_user_df
  where
  ds='${ds}'
  and date(create_time)='2021-06-01'
) a
left join
(
  select
  union_id,ds
  from
  dwd_jyxd_patient_page_view_di
  where
  ds>=to_char(date_sub(to_date('${ds}','yyyymmdd'),30),'yyyymmdd')
  group by
  union_id,ds
) b
on
a.union_id=b.union_id
and a.ds>=b.ds
group by
a.ds
;
复制代码

ds        cnt    rate0  rate1  rate2  rate3  rate11 rate14 rate15 rate21 rate28 rate30
+-------+----+------+------+------+------+-------+-------+-------+-------+-------+-------+
20210621  792    0.03   0.03   0.03   0.02   0.04   0.05   0.06   0.0    0.0    0.0
复制代码

这里我们就可以看到跟多的信息了，我们看到20210621 日注册用户792人，当日留存率0.03，15日留存率 0.06，这下这个数字就清楚多了。