GA4导出数据SQL报错：SessionsPerUser中EVENT_TIMESTAMP标识符无效

错误信息 #

000904 (42000): SQL compilation error: error line 46 at position 11 invalid identifier 'EVENT_TIMESTAMP'

问题背景 #

编写从Google Analytics 4（GA4）导出数据提取聚合指标的SQL时，SessionsPerUser CTE出现上述编译错误。已确认event_timestamp字段在staging模型中存在，其他CTE均可正常使用该字段，怀疑问题出在GROUP BY语句或嵌套函数上。

完整SQL代码 #

WITH RECURSIVE DateDimension AS 
(
    SELECT '2024-01-01'::DATE AS date -- Start date
    UNION ALL
    SELECT DATEADD(day, 1, date)
    FROM DateDimension
    WHERE date < CURRENT_DATE() -- Automatically updates to include up to the current date
),
UserInfo AS 
(
    SELECT
        DATE(event_timestamp) AS event_date,
        user_pseudo_id,
        MAX(IFF(event_name IN ('first_visit', 'first_open'), 1, 0)) AS is_new_user,
        MAX(IFF(event_name = 'app_remove', 1, 0)) AS is_deletion,
        MAX(IFF(event_name = 'delete_account', 1, 0)) AS  is_account_deletion,
        MAX(IFF(param_exclusion = TRUE, 1, 0)) AS is_exclusion,
        SUM(param_ENGAGEMENT_TIME_MSEC) AS total_engagement_time,
        MAX(IFF(event_name = 'first_open', 1, 0)) AS is_download
    FROM 
        {{ ref('stg_google_analytics_events') }}
    GROUP BY 
        DATE(event_timestamp), user_pseudo_id
),
SessionInfo AS 
(
    SELECT
        DATE(event_timestamp) AS event_date,
        user_pseudo_id,
        param_ga_session_id,
        TIMESTAMPDIFF(SECOND, MIN(event_timestamp), MAX(event_timestamp)) AS session_duration
    FROM 
        {{ ref('stg_google_analytics_events') }}
    GROUP BY  
        DATE(event_timestamp), user_pseudo_id, param_ga_session_id
),
TotalSessions AS 
(
    SELECT
        DATE(event_timestamp) AS event_date,
        COUNT(DISTINCT param_ga_session_id) AS total_sessions
    FROM 
        {{ ref('stg_google_analytics_events') }}
    GROUP BY 
        DATE(event_timestamp)
),
SessionsPerUser AS 
(
    SELECT
        DATE(event_timestamp) AS event_date,
        AVG(session_count) AS sessions_per_user
    FROM 
        (SELECT
             DATE(event_timestamp) AS event_date,
             user_pseudo_id,
             COUNT(DISTINCT param_ga_session_id) AS session_count
         FROM 
             {{ ref('stg_google_analytics_events') }}
         GROUP BY 
             DATE(event_timestamp), user_pseudo_id) AS user_sessions
    GROUP BY 
        DATE(event_timestamp)
),
AggregatedMetrics AS 
(
    SELECT
        ui.event_date,
        COUNT(DISTINCT ui.user_pseudo_id) AS active_users,
        SUM(ui.is_new_user) AS signups,
        SUM(ui.is_deletion) AS deletions,
        SUM(ui.is_account_deletion) AS account_deletions,
        SUM(ui.is_exclusion) AS exclusions,
        SUM(ui.is_download) AS downloads,
        AVG(si.session_duration) AS average_session_duration,
        SUM(ui.total_engagement_time) / COUNT(DISTINCT si.param_ga_session_id) AS average_engagement_time,
        ts.total_sessions,
        spu.sessions_per_user
    FROM 
        UserInfo ui
    LEFT JOIN 
        SessionInfo si ON ui.user_pseudo_id = si.user_pseudo_id 
                       AND ui.event_date = si.event_date
    LEFT JOIN 
        TotalSessions ts ON ui.event_date = ts.event_date
    LEFT JOIN 
        SessionsPerUser spu ON ui.event_date = spu.event_date
    GROUP BY 
        ui.event_date, ts.total_sessions, spu.sessions_per_user
)
SELECT
    dd.date,
    COALESCE(am.downloads, 0) AS downloads,
    COALESCE(am.active_users, 0) AS active_users,
    COALESCE(am.signups, 0) AS signups,
    COALESCE(am.deletions, 0) AS deletions,
    COALESCE(am.account_deletions, 0) AS account_deletions,
    COALESCE(am.exclusions, 0) AS exclusions,
    COALESCE(am.total_sessions, 0) AS total_sessions,
    COALESCE(am.sessions_per_user, 0) AS sessions_per_user,
    COALESCE(am.average_session_duration, 0) AS average_session_duration,
    COALESCE(am.average_engagement_time, 0) AS average_engagement_time
FROM
    DateDimension dd
LEFT JOIN 
    AggregatedMetrics am ON dd.date = am.event_date
ORDER BY 
    dd.date

报错代码片段 #

SessionsPerUser AS 
(
    SELECT
      DATE(event_timestamp) AS event_date,
      AVG(session_count) AS sessions_per_user
    FROM (
        SELECT
          DATE(event_timestamp) AS event_date,
          user_pseudo_id,
          COUNT(DISTINCT param_ga_session_id) AS session_count
        FROM {{ ref('stg_google_analytics_events') }}
        GROUP BY DATE(event_timestamp), user_pseudo_id
    ) AS user_sessions
    GROUP BY DATE(event_timestamp)
),

解决思路与修复方案 #

问题根源 #

SessionsPerUser的外层查询中，直接调用DATE(event_timestamp)试图生成event_date，但外层查询只能访问内层子查询user_sessions返回的字段，无法直接访问原表的event_timestamp字段——子查询已经将DATE(event_timestamp)封装为event_date，原表字段在子查询外部不可见。

修复代码 #

将外层查询的DATE(event_timestamp)替换为子查询已生成的event_date字段，同时GROUP BY语句也改为使用event_date：

SessionsPerUser AS 
(
    SELECT
        event_date,
        AVG(session_count) AS sessions_per_user
    FROM 
        (SELECT
             DATE(event_timestamp) AS event_date,
             user_pseudo_id,
             COUNT(DISTINCT param_ga_session_id) AS session_count
         FROM 
             {{ ref('stg_google_analytics_events') }}
         GROUP BY 
             DATE(event_timestamp), user_pseudo_id) AS user_sessions
    GROUP BY 
        event_date
),

额外优化说明 #

内层子查询的GROUP BY可以保留原写法，也可以改为GROUP BY event_date, user_pseudo_id（部分SQL引擎支持直接使用子查询中的别名），两种写法均能正常运行，核心是确保外层查询仅使用子查询暴露的字段。

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