GA4公开数据集SQL日期解析报错排查求助

核心错误点 #

• t2中日期解析错误：GA4数据集的event_date是字符串类型（格式为YYYYMMDD），直接用CAST(event_date AS DATE)会触发日期转换失败，必须和t1一样使用PARSE_DATE('%Y%m%d', event_date)来转换。
• t2窗口函数逻辑冗余：AVG(COUNT(ecommerce.transaction_id)) OVER(PARTITION BY user_pseudo_id ORDER BY user_pseudo_id)中，排序字段和分区字段重复，且窗口范围是全量，实际可以简化为用户级聚合计算。
• 最终JOIN导致数据重复：t2原逻辑按user_pseudo_id和event_date分组，一个用户对应多行数据，和t4 JOIN后会导致统计数据被重复关联，需要先对t2做用户级聚合再JOIN。

修正后的完整SQL #

WITH t1 AS (
    SELECT
        PARSE_DATE('%Y%m%d', event_date) AS event_date,
        user_pseudo_id,
        COUNT(DISTINCT user_pseudo_id) AS users,
        MAX(CASE WHEN params.key = "ga_session_id" THEN params.value.int_value ELSE 0 END) AS sessionId,
        CASE WHEN event_name = "first_visit" THEN 1 ELSE 0 END AS newUsers,
        COUNT(ecommerce.transaction_id) AS conversions,
        SUM(ecommerce.purchase_revenue) AS totalRevenue
    FROM
        `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*` AS ga, 
        UNNEST (event_params) AS params
    WHERE 
        _table_suffix BETWEEN '20210101' AND '20210131'
    GROUP BY  event_date, user_pseudo_id, event_name, ecommerce.transaction_id
),
t2 AS (
    SELECT 
        user_pseudo_id,
        COUNT(ecommerce.transaction_id) AS total_conv,
        PARSE_DATE('%Y%m%d', MAX(event_date)) AS most_recent_purchase,
        PARSE_DATE('%Y%m%d', MIN(event_date)) AS first_purchase
    FROM
        `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*` AS ga, 
        UNNEST (event_params) AS params
    WHERE 
        _table_suffix BETWEEN '20210101' AND '20210131'
        AND ecommerce.transaction_id IS NOT NULL
    GROUP BY user_pseudo_id
),
t3 AS (
    SELECT 
        user_pseudo_id,
        DATE_DIFF(most_recent_purchase, first_purchase, DAY) / 30.0 AS day_in_between_purchases,
        total_conv AS conversions
    FROM t2
)
SELECT 
    t4.event_date,
    t4.newUsers,
    t4.conversions,
    t4.totalRevenue
FROM (
    SELECT
        user_pseudo_id,
        event_date,
        SUM(newUsers) AS newUsers,
        SUM(conversions) AS conversions,
        CONCAT('$', IFNULL(SUM(totalRevenue), 0)) AS totalRevenue
    FROM 
        t1
    GROUP BY user_pseudo_id, event_date
) AS t4
LEFT JOIN t3 ON t3.user_pseudo_id = t4.user_pseudo_id
LEFT JOIN t2 ON t2.user_pseudo_id = t4.user_pseudo_id
WHERE t2.total_conv > 0;

关键修改说明 #

1. t2日期处理：将CAST(MAX(event_date) AS DATE)替换为PARSE_DATE('%Y%m%d', MAX(event_date))，确保字符串格式的日期正确转换为DATE类型。
2. t2聚合逻辑：去掉冗余窗口函数，直接按user_pseudo_id聚合，统计用户总转化数、首次和末次转化日期，避免一个用户对应多行数据。
3. 过滤无效数据：在t2的WHERE条件中添加AND ecommerce.transaction_id IS NOT NULL，只统计有转化行为的用户，减少无效计算。
4. t3简化：直接使用t2中已计算好的总转化数，无需重复计算，逻辑更清晰。

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