如何修改BigQuery中session_start百分位分析：筛选未卸载且活跃用户

核心改动点 #

• 反转用户筛选逻辑：从「包含卸载用户」改为「排除卸载用户」
• 新增时间范围限制：确保session_start事件发生在目标30天内，保证统计的是这段时间内实际使用过应用的用户
• 可选优化：把硬编码日期改成动态计算，避免每次手动调整

修改后的代码 #

with removed as (
  -- 先找出过去30天内有卸载行为的用户
  SELECT distinct user_pseudo_id as removed_users
  from `rayn-deen-app.analytics_317927526.events_*`
  where event_name = "app_remove"
    and DATE(TIMESTAMP_MICROS(event_timestamp)) BETWEEN '2023-04-22' AND '2023-05-21'
),
active_users as (
  -- 筛选过去30天内有session_start、且未卸载的用户
  select 
    user_pseudo_id,  
    count(event_name) as session_count
  from `rayn-deen-app.analytics_317927526.events_*`
  where event_name = 'session_start'
    -- 限制session_start发生在目标时间范围内
    and DATE(TIMESTAMP_MICROS(event_timestamp)) BETWEEN '2023-04-22' AND '2023-05-21'
    -- 排除卸载用户
    and user_pseudo_id not in (select removed_users from removed)
  group by user_pseudo_id
)

-- 计算session次数的百分位数
select
  percentiles[offset(25)] as p25,
  percentiles[offset(50)] as p50,
  percentiles[offset(75)] as p75
from (
  select approx_quantiles(session_count, 100) percentiles
  from active_users
)

额外优化建议 #

如果不想每次手动修改日期，可以把固定日期换成动态计算，自动适配「过去30天」的范围：

-- 将两处日期条件替换为：
and DATE(TIMESTAMP_MICROS(event_timestamp)) 
  BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY) 
  AND CURRENT_DATE()

内容的提问来源于stack exchange，提问作者analyst92