00:00
好,那接下来我们把用户留存率的需求实现一下啊,那实际上这个需求呢,它并不难,但是有一点我们必须得明确啊,哪一点呢?哎,我们刚才提到了啊,我们说在做这个需求的时候呢,咱每天都需要去计算七个留存率,对吧?啊,那咱要明确的一点就是这七个留存率指的是哪七个留存率?啊,那说的具体一点呢,就是哪一天的几日留存,那现在我们把这个问题思考一下啊,那假如说我们现在拿到的最新的一天的数据是2020年6月14号的啊,那你说我们现在能够计算的留存应该是哪七个呀?是不是应该是这七个呀,大家看一下啊,应该分别是6月13号的一日留存,哎,6月12号的两日留存,6月11号的三日留存,那按照这个规律呢,一直到6月7号的七日留存。啊,OK,那大家可以想一想,就是为什么是这七个留存呀,来我们分析一下啊,那我们先看第一个6月13号的一日留存率啊,它应该需要哪一些数据啊,它是不是需要6月13号的新增,以及6月13号的一日留存税啊,对吧?这个6月13号的一日留存数指的就是啥?是不是指的就是6月13号新增,并且在一天之后,也就是6月14号活跃的那部分人啊。
01:18
没错吧,啊,那接下来我们继续看下一个啊,那下一个呢,是6月12号的两日留存啊,那这个留存率它需要的数据应该是啥?是不是需要的是6月12号的新增,以及6月12号的两日留存税啊,对吧?在这个留存数具体指的是什么?是不是具体指的就是6月12号新增,并且在两天之后,也就是6月14号活跃的那部分人。那所以说在这儿呢,我们应该能够总结出一个规律来啊,就是说这七个留存率呢,他们是不是都需要用到6月14号的活跃用户啊。没错吧,那所以说拿到6月14号的数据之后呢,我们能够计算的留存率就是这样的七个,好,那只要咱把这个问题想明白了,诶,那剩下的工作其实就容易了啊。
02:04
好,那接下来咱就开始写三扣啊,那在真正开始之前呢,我们需要再明确一下这个具体的思路是什么啊啊,那就我们去计算一个留存率的时候呢,咱是不是一共需要两个值,没错吧,一个是新增用户数量,一个是留存用户数量,没错吧,比如说我现在要计算的就是2020年6月13号的一日留存率。没错吧,那我在这儿是不是需要先计算一个6月13号的新增用户数,没错吧,还得计算一个啥。是不是还得计算一个6月13号的一日留存用户数啊,对吧?那这俩值应该怎么求啊?哎,我们先看新增用户啊,那咱们怎么去寻找6月13号的新增用户啊,啊,这个其实比较简单啊,咱是不是可以利用DWT层的用户主题表当中的首次活跃日期去找,没错吧,只要首次活跃日期等于6月13号,那是不是就表明他是一个13号的新增用户啊?没错吧,这个比较简单啊,那6月13号的一日留存用户又应该怎么找呢?
03:03
啊,这个其实也比较简单啊,6月13号的一日留存指的就是啥,是不是就是在6月13号新增,并且在6月14号啊,也就是6月13号的一天之后,对吧?诶活跃的那部分人。没错吧,那说白了就是啥,是不是就是首次活跃日期等于六月13,末次活跃日期等于六月14。没错吧,那这样一来呢,我们就能找到6月13号的一日留存用户了。那只要把这俩值求出来了,哎,那剩下的是不是就容易了对吧?它俩一除是不是就能得到留存率啊,OK,那这就是我们的具体思路啊,那当然呢,我们这儿一共要计算七个留存率啊,那这七个留存率呢,咱总不能一个一个去算对吧?那在这咱应该怎么算呢?诶,我们应该诶分组去统计啊,咱可以怎么分组统计呢?首先哎,我们先从DWT层啊,把这七天的新增用户全部找到啊,那找到之后呢,按谁分组,是不是就按照新增日期分组就可以了。
04:00
没错吧,那正好分七组,我们就能够得到七个留存率了,OK,那接下来咱就正式开始写三个,那首先呢,我们先拿到这七天的新增用户,没错吧?那来select select select from应该哪张表是不是DWT层的?诶,User topic呀,那分区就是最近一天的分区啊,2020杠零六杠诶,幺四没错吧?然后呢,咱是不是得根据首次活跃日期去过滤最近七天的新增用户啊,对吧?那怎么过滤啊啊,应该是login data first,它得大于等于data I把当天日期先放进去,2020杠零六杠幺四没错吧,应该是几啊。啊,最最早的一些是应是七号对吧,所以这应该是减减七没错吧,减七啊OK,然后呢,还得再来一条啊,And login date,诶,First它应该怎么样?诶小于对吧,小于当天的日期啊2020杠零六杠幺四啊好,那这样一来呢,我们就把这七天的新增用户全部找到了,那那接下来呢,我们再按照新增日期进行分组,Group by,谁login data first,那好,那接来我们就可以依次选择咱们所需的每个字段了啊好,我们把这个每个字段拿一下啊来CTRLC。
05:12
来给它盯在这儿啊,我们照着选一下,首先第一个字段DT这个比较简单,我们就写一个常量值就可以对吧,2020杠零六杠幺四,那这个搞定,然后下一个下一个是啥?下一个呢是credit date对吧,也就是用户新增日期啊,那其实就是logging data first吧啊所以在这儿呢,咱直接选就可以,Logging data first好,那就来下一个,下一个是啥?是retention day对吧?这个指的什么?是留存天数啊,其实所谓留存天数指的就是啥?是不是指的就是新增日期距6月14号的这个天数啊,没错吧,那所以在这儿呢,我们是把这俩日期减一下就可以,那用谁是不是可以用data dif这个函数啊,Dif啊,然后呢,我们先把当前日期放进去,零六杠幺四没错吧,再把谁再把login date first放进去,那这样一来我们得到的就是诶,Retention day就是留存天数啊好,那我们继续往下进行,那再往下呢,是啥?是不是就是这个re penion count了呀。
06:08
那这个指的是啥呀?这就是留存用户数量,没错吧,那留存用数量指的是什么?那所谓的留存用户呢,指的是不是就是在这里边的某一天新增,然后呢,在6月14号活跃的用户啊。啊,没错吧,啊,那大家回忆一下,我们这儿是不是已经把这七天的新增用户都找到了,对吧?然后呢,按照新增日期进行了分组啊,那所以说现在每一组里边是不是就是当天的新增用户啊,对吧?啊,那现在我们是不是只需要在保证一点就可以了,就是他在6月14号活跃了就行,没错吧,那所以在这儿呢,我们是不是可以使用some if去做啊来咱们写一下,那也就是some if。If里边写啥?那如果它的login data last,也就是末次活跃日期等于2020杠零六杠幺四,那这就表明它是不是在今天活跃了,没错吧,那它就是一个对应的留存用户啊,那这时候呢,我们就记一,否则就记零啊,那现在我们就得到了留存用户数了,那接下来下一个是啥?下一个是new user count是不是就是新增用户数啊对吧?那这个就比较简单了,我们是不是直接count就可以Co count芯啊,就完事了啊,这就是新增用户数啊,那最后一个就是留存率了,对不对?那它就更简单了,是不是用这俩值比一下就可以了,对吧?我们先拿到留存用户数量来CTRLZ,拿过来CTRLV,那后边呢,再来一个CR星啊,那这样一来我们就得到了留存率了啊OK,那到目前为止呢,诶,咱这个留存率所需的所有字段就全部拿到了啊,那完之后呢,我们再给它补上一个insert insert orright table,应该是ADS retention,对吧?那后边呢,来一个select星from ADS user retention,再来一个union。
07:48
就完事了,OK,那现在用户留存率的需求就全部完成了。
我来说两句