BigQuery SQL实现按周统计滚动首次激活新增用户数方法咨询
BigQuery 周度首次激活用户统计实现
现有SQL的统计口径与目标需求存在偏差:当前逻辑统计的是当周产生过激活行为的去重活跃用户数,会把历史周期已经激活、当周再次触发同事件的老用户重复计入,无法匹配「周度首次触发激活事件的新增用户规模」的统计要求。
要实现目标口径,核心是先为每个用户匹配其全生命周期的首次激活时间,再按周维度对该时间做聚合计数。
实现代码
WITH user_first_activation AS ( -- 计算每个用户的全周期首次激活时间 SELECT user_id, MIN(timestamp) AS first_activation_ts FROM `company.dataset.tracks` WHERE event = 'activation_event' GROUP BY user_id ) SELECT -- 按周截断首次激活时间,BigQuery默认周度起点为周一,可按需调整为WEEK(SUNDAY)等规则 DATE_TRUNC(EXTRACT(DATE FROM first_activation_ts), WEEK) AS week, COUNT(DISTINCT user_id) AS first_activated_user_count FROM user_first_activation WHERE first_activation_ts > TIMESTAMP('2020-01-01') GROUP BY week ORDER BY week
口径注意事项
如果需要避免因时间范围截断导致的首次激活时间偏差(比如用户在2020年1月之前已经激活,只是在2020年首次进入查询时间范围),可将外层的时间过滤条件上移到CTE的WHERE子句中,保证首次激活时间计算覆盖全量历史数据。
内容的提问来源于stack exchange,提问作者Bastien Beurier
相关产品推荐
相关产品推荐

