如何筛选首次打开App的用户及其后续访问记录（附SQL片段）

嘿，我来帮你完善这个查询！你的原SQL已经筛选了目标事件，但还缺了锁定首次打开用户以及关联后续互动记录的核心逻辑。咱们一步步来补全：

首先，得先找出每个用户第一次触发first_open的记录，再基于这些用户去拉取他们的首次打开事件和后续几周内的user_engagement事件。下面是完整的可运行SQL：

WITH first_open_users AS (
    SELECT 
        user_dim.app_info.app_instance_id AS id,
        -- 记录用户首次打开的日期和对应的周周期
        MIN(PARSE_DATE('%Y%m%d', event.date)) AS first_open_date,
        MIN(FORMAT_DATE('%Y-%W', PARSE_DATE('%Y%m%d', event.date))) AS first_open_period
    FROM `database.app_events_*`,
         UNNEST(event_dim) AS event
    WHERE 
        event.name = "first_open"
        AND (_TABLE_SUFFIX BETWEEN '20180201' AND '20240101') -- 替换成你的目标日期范围
    GROUP BY id
)

SELECT 
    f.id,
    FORMAT_DATE('%Y-%W', PARSE_DATE('%Y%m%d', e.event.date)) AS period,
    e.event.name
FROM first_open_users f
JOIN `database.app_events_*`,
     UNNEST(event_dim) AS event e
ON f.id = e.user_dim.app_info.app_instance_id
WHERE 
    e.event.name IN ("first_open", "user_engagement")
    AND (_TABLE_SUFFIX BETWEEN '20180201' AND '20240101') -- 保持和子查询一致的日期范围
    -- 筛选首次打开后4周内的互动记录，可根据需求调整周数
    AND PARSE_DATE('%Y%m%d', e.event.date) <= DATE_ADD(f.first_open_date, INTERVAL 4 WEEK)
ORDER BY f.id, period;

关键逻辑拆解： #

• first_open_users 临时表：用MIN()函数精准定位每个用户的首次打开日期和对应周周期，确保我们只保留每个用户的第一次first_open记录，避免重复统计。
• 关联查询：将首次打开用户表和原事件表通过用户ID关联，筛选出这些用户的两类目标事件。
• 时间范围控制：通过DATE_ADD(f.first_open_date, INTERVAL 4 WEEK)限定后续互动记录的时间范围，你可以把4改成任意你需要的周数（比如2、8）。

小提示： #

• 如果你的用户唯一标识不是app_instance_id（比如用户登录后的账号ID），直接替换成对应的字段即可。
• FORMAT_DATE('%Y-%W')返回的是「年-周数」格式（比如2024-05），方便按周查看用户的互动趋势。
• 如果你需要更灵活的时间判断（比如按自然周计算后续几周，而非从首次打开日起算），可以改用DATE_DIFF(PARSE_DATE('%Y%m%d', e.event.date), f.first_open_date, WEEK) <= 4。

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