如何在ga4_obfuscated_sample_ecommerce公开数据集中查询N天不活跃用户

核心问题原因 #

你查询无结果的根源是示例数据集的时间范围为2020年11月到2021年1月，原代码使用CURRENT_TIMESTAMP()作为时间基准，计算出来的7天/2天时间窗口完全落在数据集的时间范围之外，没有匹配的事件数据。

调整要点 #

• 将时间锚点从当前时间替换为数据集内的最新事件时间，避免时间窗口偏移
• 匹配数据集实际的表后缀范围为20201101到20210131
• 保留你已经替换的user_pseudo_id字段（该数据集无业务侧上报的user_id，用匿名id是正确选择）

调整后完整可运行代码 #

/**
 * 适配GA4公开示例数据集的N天不活跃用户计算逻辑
 * N天不活跃用户定义：过去M天有活跃的用户中，过去N天没有产生有效 engagement 事件的用户，M>N
 */
WITH dataset_max_time AS (
  -- 取数据集内最新的事件时间作为锚点，替代当前时间
  SELECT MAX(event_timestamp) AS max_ts
  FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
)
SELECT
  COUNT(DISTINCT MDaysUsers.user_pseudo_id) AS n_day_inactive_users_count
FROM
  (
    SELECT
      DISTINCT user_pseudo_id
    FROM
      `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*` AS T
    CROSS JOIN
      T.event_params,
      dataset_max_time
    WHERE
      event_params.key = 'engagement_time_msec' AND event_params.value.int_value > 0
      -- 过去M=7天有活跃，基于数据集最新时间计算
      AND event_timestamp > UNIX_MICROS(TIMESTAMP_SUB(TIMESTAMP_MICROS(dataset_max_time.max_ts), INTERVAL 7 DAY))
      -- 匹配数据集实际表后缀范围
      AND _TABLE_SUFFIX BETWEEN '20201101' AND '20210131'
  ) AS MDaysUsers
LEFT JOIN
  (
    SELECT
      DISTINCT user_pseudo_id
    FROM
      `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`AS T
    CROSS JOIN
      T.event_params,
      dataset_max_time
    WHERE
      event_params.key = 'engagement_time_msec' AND event_params.value.int_value > 0
      -- 过去N=2天有活跃，基于数据集最新时间计算
      AND event_timestamp > UNIX_MICROS(TIMESTAMP_SUB(TIMESTAMP_MICROS(dataset_max_time.max_ts), INTERVAL 2 DAY))
      AND _TABLE_SUFFIX BETWEEN '20201101' AND '20210131'
  ) AS NDaysUsers
  ON MDaysUsers.user_pseudo_id = NDaysUsers.user_pseudo_id
WHERE
  NDaysUsers.user_pseudo_id IS NULL;

可选优化 #

你可以根据需要调整INTERVAL 7 DAY和INTERVAL 2 DAY的数值，自定义M和N的长度，计算不同周期的不活跃用户规模。

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