最近7日平均日活跃用户数计算实现
场景介绍:计算所选日期7日内的平均活跃用户数(Daily Active User)。
解决思路:计算七日平均DAU,包含两层聚合计算,需要先去重计算每一天的访问用户数,再把这七天的数字求平均。
实现方式:
A. ETL: 逻辑固定的情况下,例如把数据聚合到“日”颗粒度,不考虑其他因素,建议在ETL里使用分组聚合得到平均日活跃用户数,然后在ETL或者卡片里计算出最近7日平均日活跃用户数。缺点:不灵活,一旦要加入更多的维度筛选,ETL需要频繁修改,且容易影响依赖于同一个数据集的其他卡片。
B. 卡片/视图数据集:适用于需要灵活多变的筛选维度的场景,要求报表制作者熟悉SQL函数。缺点:数据计算量大时卡片加载会变慢。
期望效果:选择一个日期,一个指标卡直接显示出7日平均DAU,另一个表格显示出7日内每天的活跃用户数和7日平均DAU。本文案例在卡片里直接进行计算,效果参考下图。
一. 明细卡片实现步骤
1. 新建卡片,把日期字段放入维度栏。如果用来去重计数的“用户ID”字段是数值类型的话,直接拖到数值栏,聚合方式选“去重计数”;“用户ID”字段是文本类型的话,拖到数值栏后不能设置小计/总计,此时需要新建数值类型字段「用户数」:count(distinct[用户ID]),拖入数值栏,得到每天去重后的用户数。
2. 新建第二个字段「日期过滤」,类型为布尔型,从参数列表里引用一个日期类型的全局参数,最后把该字段拖入筛选栏(默认选TRUE,不用修改),取出包含参数日期当天以及前面6天,总共7天的日期。
公式:[日期]>=DATE_SUB([DYNAMIC_PARAMS.查询日期],6) AND [日期]<=[DYNAMIC_PARAMS.查询日期]
Note:如果表格显示无数据的话,可以在右侧临时修改「参数默认值」,保证有数据显示,同时验证数据准确性。
3. 找到右侧「可视化」-「小计/总计」,设置列总计的显示位置,再点击数值栏字段「用户数」,设置小计/总计计算方式为:以聚合数据计算 - 平均值。此时“总计”行显示的就是7日平均活跃用户数。
4. 回到页面上,新建日期筛选器,联动卡片里的全局参数即可。
二. 指标卡实现步骤
1. 参考明细表格里的布尔型字段「日期过滤」,在指标卡里新建一个一样的字段,把该字段拖入筛选栏(默认选TRUE,不用修改),取出包含参数日期当天以及前面6天,总共7天的日期。
公式:[日期]>=DATE_SUB([DYNAMIC_PARAMS.查询日期],6) AND [日期]<=[DYNAMIC_PARAMS.查询日期]
2. 新建数值型字段「日用户数/行数」。
公式:size(collect_set([用户ID]) over(partition by [日期]))/count([用户ID])over(partition by [日期])
逻辑:先用窗口函数计算出每天的去重用户数,因为窗口函数不会减少原表中的行数,所以需要除以每天数据的行数,把日去重用户数平摊到每一行上,这样才能正确进行下一步计算。
3. 再次新建数值类型字段「DAU」,拖入数值栏,聚合方式选“无处理”,保存卡片。
公式:sum([日用户数/行数])over(partition by 1)/7
逻辑:把上一步得到的「日用户数/行数」进行求和,得到日期范围内每天活跃用户数的总和,再除以天数,得到7日内的平均活跃用户数。如果有可能日期不连贯,不够7天,只需要统计有效天数的平均值的话,公式可以使用 sum([日用户数/行数])over(partition by 1)/size(collect_set([日期])over(partition by 1))。
4. 回到页面上,新建日期筛选器,联动卡片里的全局参数即可。筛选日期,指标卡和表格卡片计算的结果是一致的。 也可以根据需求添加其他筛选器。
疑问解答:
问:指标卡里,为什么不用 size(collect_set([用户ID]) over(partition by [日期])) 计算得到日活跃用户数后,聚合方式直接用“平均值”计算?
答:卡片里自带的聚合方式“平均值”,计算逻辑为,每行数据相加,再除以原表的数据行数。即使卡片里筛选了7天,并不代表数据行数被聚合成了7条。用 size(collect_set([用户ID]) over(partition by [日期])) 直接计算平均值,相当于(Day1*a+Day2*b+…+Day7*g)/(a+b+c+d+e+f+g) ,这样逻辑计算出来的平均值是不准确的。而上文里的计算逻辑是 (Day1/a*a+Day2/b*b+…+Day7/g*g)/7,先把数据缩小再累加扩大,等同于日活跃用户数相加后除以天数。两种方式计算结果对比如下图。