基于Google BigQuery中Firebase Analytics数据查找同日安装卸载App的用户

嘿，我来帮你搞定这个问题！你说之前能看到同一用户有安装和卸载记录，但修改查询后返回零结果，大概率是查询逻辑里的某个细节没处理好，我给你拆解一下可能的原因，再提供几个靠谱的查询方案。

先排查导致返回零结果的常见坑 #

• 事件名称搞错了：Firebase里的安装事件是app_install，卸载是app_uninstall，别把first_open当成安装事件（first_open是第一次打开应用，和安装不是一回事）。如果你的查询里用错了事件名，肯定查不到匹配的用户。
• 日期匹配的时区问题：Firebase的event_timestamp是UTC时间戳，如果你直接转成DATE(TIMESTAMP_MICROS(event_timestamp))得到的是UTC日期。如果你的用户在其他时区，比如国内用户，同一天的安装卸载可能被分到不同的UTC日期里，导致匹配失败。
• 用户标识不统一：确保你在安装和卸载事件里用的是同一个用户标识——app_instance_id是应用实例的唯一ID，比user_pseudo_id更准确（因为用户清除数据后重新安装会生成新的user_pseudo_id）。
• 错误的逻辑判断：比如你可能写了WHERE event_name = 'app_install' AND event_name = 'app_uninstall'，这显然不可能，一条记录只能有一个事件名称，这种条件会直接过滤掉所有数据。

靠谱的查询方案 #

方案1：用CTE分别获取安装/卸载记录再关联 #

这个方法逻辑清晰，容易排查问题：

WITH daily_installs AS (
  SELECT
    app_instance_id,
    -- 这里可以指定时区，比如替换成你的用户时区
    DATE(TIMESTAMP_MICROS(event_timestamp), 'Asia/Shanghai') AS event_date
  FROM
    `your-project.analytics_xxxxxx.events_*`
  WHERE
    event_name = 'app_install'
    -- 替换成你要查询的日期范围（表后缀是UTC日期）
    AND _TABLE_SUFFIX BETWEEN '20240101' AND '20240131'
),
daily_uninstalls AS (
  SELECT
    app_instance_id,
    DATE(TIMESTAMP_MICROS(event_timestamp), 'Asia/Shanghai') AS event_date
  FROM
    `your-project.analytics_xxxxxx.events_*`
  WHERE
    event_name = 'app_uninstall'
    AND _TABLE_SUFFIX BETWEEN '20240101' AND '20240131'
)
-- 关联同一天同一用户的安装和卸载记录
SELECT
  i.app_instance_id,
  i.event_date
FROM daily_installs i
INNER JOIN daily_uninstalls u
  ON i.app_instance_id = u.app_instance_id
  AND i.event_date = u.event_date

方案2：用窗口函数标记用户的安装/卸载状态 #

这个方法在一个查询里完成，效率更高：

SELECT
  app_instance_id,
  event_date
FROM (
  SELECT
    app_instance_id,
    DATE(TIMESTAMP_MICROS(event_timestamp), 'Asia/Shanghai') AS event_date,
    -- 标记当天是否有安装记录
    MAX(CASE WHEN event_name = 'app_install' THEN 1 ELSE 0 END) 
      OVER (PARTITION BY app_instance_id, DATE(TIMESTAMP_MICROS(event_timestamp), 'Asia/Shanghai')) AS has_install,
    -- 标记当天是否有卸载记录
    MAX(CASE WHEN event_name = 'app_uninstall' THEN 1 ELSE 0 END) 
      OVER (PARTITION BY app_instance_id, DATE(TIMESTAMP_MICROS(event_timestamp), 'Asia/Shanghai')) AS has_uninstall
  FROM
    `your-project.analytics_xxxxxx.events_*`
  WHERE
    event_name IN ('app_install', 'app_uninstall')
    AND _TABLE_SUFFIX BETWEEN '20240101' AND '20240131'
)
-- 筛选当天既有安装又有卸载的用户
WHERE has_install = 1 AND has_uninstall = 1
GROUP BY app_instance_id, event_date

排查步骤帮你定位问题 #

1. 单独验证数据：先分别运行daily_installs和daily_uninstalls的查询，看看各自有没有数据，并且有没有重叠的app_instance_id和日期。
2. 检查时区转换：如果你的用户不在UTC时区，一定要在DATE()函数里指定正确的时区，比如'Europe/Paris'或者'America/New_York'。
3. 核对表后缀范围：_TABLE_SUFFIX是按UTC日期命名的，如果你的查询日期范围是当地时间，要确保覆盖到对应的UTC日期。
4. 确认事件触发逻辑：有些情况下，app_uninstall事件可能会有延迟上报，或者在某些设备上无法触发，你可以先查看卸载事件的总量是否符合预期。

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