00:00
好,那我们继续看下一个需求,叫做品牌复购率啊,那什么叫做品牌复购率啊,其实很简单来看一下啊,所有的品牌复购率呢,是指在一段时间内重复购买某品牌的人数啊与购买过该品牌的人数的比值啊,那重复购买如何定义,购买过又如何定义呢?啊,其实很简单啊,所谓的宠物购买呢?诶,就是购买次数大于等于二,没错吧,那所谓的购买过呢?诶,其实就是购买次数大于等于一。没错吧,啊,那当然了,这儿提到了说是一段时间内对吧,那这个一段时间到底是多久呢?啊,那此处呢,让我们分别统计最近一天,最近七天和最近30天内的各品牌的复购率。啊,这是咱这个需求的具体要求啊,那我给大家举一个例子啊,那假如说现在有一个品牌A,然后呢,在一段时间之内购买过A品牌的人数有100个,那所谓购买过是不是就是购买次数大于等于一啊,对吧?诶大于等于一的有100个人,那重复购买过品牌A的人数呢?诶有50人,那所谓重复购买是不是就购买次数大于等于二啊,对吧?啊OK,那这时候呢,我们就说品牌A在这段时间内的复购率就是50%,诶这就是所谓的复购率。
01:17
啊好,那接下来呢,我们再来看一下它的建表语句啊,建表语句也比较简单,我们来一起看一下啊,重点看一下它的字段就可以啊来先看第一个DT,这就是一个统计日期,不用多说,再来往下走。哎,你看啊,下边是不是recent days啊,对吧?啊,同样是最近N天啊,这里边呢,一七三十分别代表最近一天,最近七天,最近30天啊,那当然这个字段的含义呢,那就是让我们去分别统计最近一期30天内的各品牌的复购率,没错吧?啊OK,我们再继续往下走啊,那下边两个字段分别是品牌ID和品牌名称,那最后一个字段呢,就是复购率啊OK,那这就是咱们品牌复购率的需求说明,那紧接着我们就来把这个需求实现一下啊啊,其实这个不难想到啊,我们要想求得一个品牌的复购率对吧?啊,那我们只需要求得两个人数就可以了,这两个人数分别是啥呀?是不是分别是购买过这个品牌一次以上的人数以及两次以上的人数啊?
02:16
没错吧,那只要把这两个人数求出来啊,那复购率是一除就OK了啊,那OK,那关键是怎样才能得到这样的两个人数啊,因为我们怎么去求购买过这个品牌一次以上的人数啊,以及两次以上的人数怎么去求。啊,其实很简单,我们要想知道这样的两个人数,那我们首先得知道每个人购买过这个品牌的次数。没错啊,咱们得先知道啊,比如张三购买A品牌两次,李四购买A品牌诶三次啊,王五购买A品牌一次啊OK,那我们知道每个人购买这个品牌的次数之后呢,咱是不是就可以根据次数去统计相应的人数了呀,对吧,对吧?那这时候呢,我们就能够去统计购买次数大于等于一的有几人,购买次数大于等于二的又有几人了,那所以说我们现在的首要任务就是先去统计啊每个人购买这个品牌的次数,但是大家要注意一点啊,这个需求呢,让我们统计的是各品牌的复购率,而不是这一个品牌的复购率,没错吧?那所以说我们的首要任务应该是啥呀?诶,应该是先统计每个用户购买每个品牌的次数啊,那求出来之后呢,我们再进行分组统计,按谁分组呢?那肯定是按品牌分组对吧?啊,再按品牌分组,哎,去分别计算购买次数大于一的人数和购买次数大于二的人数啊都求出来之后呢,诶再一除就能得到我们所需的每个。
03:39
和品牌的复购率了啊好,那这就是品牌复购率这个需求的思路啊,那接下来呢,我们把它实现一下啊,那我们现在呢,同样是先不考虑recent this这个字段啊,我们现在只统计最近一天内的各品牌复购率啊,也就是咱现在的首要任务呢,就是去统计最近一天内每个用户购买每个品牌的次数。
04:02
啊,没错吧?啊,但大家仔细想一想啊,你会发现我们没有任何一张表当中直接存储了用户购买品牌的记录。哎,没有,哎,但是有一张表当中,哎存储了用户购买SKU商品的记录,哪张表啊,是不是就是订单明细事实表啊,没错吧,那那张表当中一行数据代表的是不是就是一个用户对一个SKU的一次购买记录啊?没错吧?啊,那所以咱这儿可以怎么做啊,我们可以先统计出来每个用户购买每个SKU的次数啊,然后呢,我们再获取SKU的品牌信息对吧?我们再按照用户加上品牌进行分组聚合,那这样一来我是不是就能够得到每个用户购买每个品牌的次数了呀?好,那接下来呢,我们就把这个思路实现一下,那我们就先去统计每个用户购买每个SKU的次数啊来,首先select from应该是哪张表,DW do detail对吧?那我们现在只统计最近一日的啊,那就是YDT等于2020杠零六杠幺四对吧?那现在我们就已经拿到了6月14号这个相当于也就是最近一日的全部的订单明细记录,没错吧?那接下来呢,接下来咱是不是得分组统计按谁分组,是不是就按照user ID加上SKUID分组啊,那这样一来我们会把同一个用户购买同一个SKU的记录分到一组里,没事吧,那我。
05:30
我们现在要统计是啥?是每个用户购买每个SKU的次数,没错吧?那所有这个次数咱们就看芯是不就可以啊好,那现在我们把所需的字段选择出来,那第一个呢,是U的ID,第二个呢是SQID,那第三个呢,那就是count count1下来,好,那现在我们就已经得到了每个用户购买每个SKU的次数了啊那接来呢,接下来我们是不是得去获取SKU的品牌信息啊,对吧?然后再按照用户加品牌进行分组,那我们就能得到每个用户购买每个品牌的次数,没错吧?诶,那SKU的品牌信息应该从哪获取啊,很简单,肯定是从商品维度表里获取,对吧?那接下来我们去获取一下啊,来,我们select,然后呢,From哪张表应该是demom SQ in four对吧?那这个呢,我们也获取最新一天的分区,2020杠零六杠幺四啊没错吧,在这儿呢,我们需要先拿到ID,也就是SQID对吧,因为一会儿要做关联,然后呢,是不是需要拿到它的品牌ID和品牌名称啊,对吧,也是t m midd和T。
06:31
M name OK,那SKU的品牌信息咱就拿到了啊,那接下来呢,我们需要把它俩join起来对吧?来,先分别起个别名来,第一个啊,来CTRLX放括号里,那这个呢,给它起名为OD啊,O detail啊好,下一个来,CTRLX放到括号里,CTRLV,那这个呢,我们就给它起名为sa啊,SQ in for对吧?啊,Sa啊,好,那现在它俩就可以做drawone了,对吧?在这儿我们使用left draw啊,那连接条件呢,应该是啥?是不是应该是on od.skuid等于si.id啊,对吧?啊,好,那现在我们就可以进行分组聚合了,没错吧?那现在我们select from,然后下边是不是得有分组啊,对吧?那按谁分组啊?那group by,谁应该是TM,应该是user midd,再加上t m midd,再加上TM name,没错吧?啊,OK,那上面呢,我们就可以去选择哎,User idt m ID啊,然后t m name没错吧,那最后呢,我们再来一个啥。
07:31
是不是再来一个sum求和就行了,对谁进行求和,是不是对每个用户购买每个SKU的次数进行求和呀?没错吧,那在这儿呢,我们给它起一个别名叫做order count con啊在这儿呢,我们再进行诶萨姆求和order count,那现在我们得到的就是啥?是不是就是每个用户购买每个品牌的次数了呀?没错吧,那在这儿呢,我们也给它起一个别名,叫做欧count。好,那接下来呢,我们是不是就可以按照品牌去进行分组啊,然后分别去统计购买次数大于等于一的有几个人,购买次数大于等于二的又有几个人了呀,对吧?那接下来我们继续把它再作为一个子查询来给它放到括号里啊,CTRLX。
08:16
走,那这个呢,往前推一下。那给他起个别名,咱就叫T1吧啊,来,我们select from t1啊。第,那这次我们是不是又得分组统计对吧?按谁分组,这次呢,我们就按照品牌分组了啊,一个是t m midd,一个是t m name啊那上边我们就可以选择咱所需的t m midd以及t m name了,那完了之后咱们怎么去统计这个人数啊啊在这呢,我们使用sum e是不是就可以了?没错吧,来,那现在我们sum if。好啊,意味什么?如果older count它大于等于一,那我就记个一,否则我就记个零,那来说我现在是不是就是得到了购买次数大于一的有几个人了呀,没错吧?那接下来呢,我们再按照相同的方式去获取购买次数大于二的有几个人?没错吧?那那那个怎么获取啊,是不是也是萨if服对吧?来sum if应该是o countt应该是大于等于二对吧?大于等于二我就一,否则我就记零啊,那这个是不是就是所谓的重复购买的人数啊,对吧?那我们要求的是啥?是复购率没错吧?那是不是它俩应该比一下,那我们把它剪切一下啊,CTRLX,然后再放在上边对吧?啊,然后再除一下就完事了,OK,那这就是我们得到的复购率啊,OK,那当然了,我们现在只统计出来了最近一日的各品牌复购率,对吧?啊在这儿呢,我们需要分别去统计最近一日,最近七日和最近三十日的品牌复购率啊,对吧?那这应该怎么处理啊,诶跟之前的处理方式是不是一样的呀,对吧?那仍然是呢,把我们的数据给它来扩张为。
09:47
三份,然后这三份呢,诶,每份分别选择最近一天的数据,最近七天的数据,还有最近30天的数据,然后呢,是不是再分组统计就可以啊,OK,那接下来我们把这一步也去做一下啊好,我们往下走啊,首先我们需要先在这个位置,诶是不是做一个处理啊,对吧?在这我们来一个let review you explorelo的e prod啊往里边呢放一个aray a里边呢再放一个一放一个七,再放一个30,没错吧,后边来一个TPS recent s,没错吧?好,那现在呢,我们再把下边的过滤条件是不是也得改一下对吧?诶,大于等于什么呀?应该是ctrl X data data,塔艾把当前日期放进去,后边来一个减days加一,那现在呢,我们就得到咱们期望的三份数据了啊,这三份数据分别是最近一天的、最近七天的和最近30天的数据。
10:36
没错吧?啊,OK,那接下来我们是不是就可以按照recent days进行分组统计来,对吧?那我们最终的结果当中是不是期望出现一个recent days啊,啊,没错吧,那当然这儿要想有,那哪儿就得有,是不是下边就得有啊,对吧?因为这有分组嘛,对吧?所以在这儿呢,我们需要诶给它也加一个分组字段啊,那当然我要想在这儿对recent date进行分组,那是不是得保证T1的子查询里边得有recent date啊,对吧?所以说在这呢,我们需要给它选择出来在这儿啊,来CTRV,那当然这儿就享有,那就得保证啥,是不是保证OD或者是SI这个子查询当中得有rein this啊,对吧?那咱们应该出自于哪张表,是不是肯定出自于OD啊,对吧?来,给它拿过来,然后大家要注意啊,啊,咱们从OD以及SI这个子查询当中选择数据的时候,是不是也有分组啊,对吧?所以分组字段当中是不是得包含recent。
11:27
没错吧,然后还有一个点要注意啊,哪呢,就是这个位置啊,我们从DW do detail表当中选择数据的时候,是不是也有分组啊,对吧?那所以在这儿呢,我们也需要把recent death是不是放到它的分组字段当中?OK,那到现在为止呢,我们才算是把recent this给它加上了啊好了,那现在我们就只剩下最后一个DT字段了,对吧?那现在我们给它直接写一个常量值就可以,2020杠零六杠诶幺四啊,OK,那现在我们就得到了咱所需的所有字段了啊,那最终呢,我们再把数据给它insert一下就可以了,来insert orright table应该是ADS层的repeat purchase对吧?啊,后边呢,我们需要诶再来一个select星from ADS啊,应该是repeat,对吧?啊,后边呢,再来一个union啊好,那到现在为止呢,品牌复购率这个需求我们就全部完成了。
我来说两句