如何在BigQuery中按URL统计GA4有机搜索的用户数与会话数

原查询统计的是事件次数，要统计用户数或会话数，需要基于GA4数据中的用户伪ID和会话ID做去重计算，以下是两种场景的示例查询：

统计用户数（按URL拆分） #

该查询会统计每个URL下，来自Organic Search渠道的唯一用户数量：

WITH
prep as (
    SELECT
        (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS page_location,
        user_pseudo_id,
        CASE
            WHEN
                regexp_contains(traffic_source.source, r'^(google|bing|yahoo|baidu|duckduckgo|yandex|ask)$')
                OR traffic_source.medium = 'organic'
            THEN 'Organic Search'
        ELSE
            'others'
        END AS default_channel_group
    FROM
        `bigquery-ga4-xxxxxxxxxxxxxx`
    WHERE
        _TABLE_SUFFIX BETWEEN '20230801' AND '20230831'
        AND (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') IS NOT NULL
)

SELECT
    page_location,
    COUNT(DISTINCT CASE WHEN default_channel_group = 'Organic Search' THEN user_pseudo_id ELSE NULL END) AS organic_search_users
FROM
    prep
GROUP BY
    page_location

说明 #

• 在prep阶段加入user_pseudo_id（GA4中标识唯一用户的伪ID），同时过滤掉无有效page_location的行；
• 聚合时使用COUNT(DISTINCT)对符合条件的用户ID去重，得到每个URL的独立用户数。

统计会话数（按URL拆分） #

该查询会统计每个URL下，来自Organic Search渠道的唯一会话数量：

WITH
prep as (
    SELECT
        (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS page_location,
        user_pseudo_id,
        (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS session_id,
        CASE
            WHEN
                regexp_contains(traffic_source.source, r'^(google|bing|yahoo|baidu|duckduckgo|yandex|ask)$')
                OR traffic_source.medium = 'organic'
            THEN 'Organic Search'
        ELSE
            'others'
        END AS default_channel_group
    FROM
        `bigquery-ga4-xxxxxxxxxxxxxx`
    WHERE
        _TABLE_SUFFIX BETWEEN '20230801' AND '20230831'
        AND (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') IS NOT NULL
        AND (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') IS NOT NULL
)

SELECT
    page_location,
    COUNT(DISTINCT CASE WHEN default_channel_group = 'Organic Search' THEN CONCAT(user_pseudo_id, '.', session_id) ELSE NULL END) AS organic_search_sessions
FROM
    prep
GROUP BY
    page_location

说明 #

• 会话由user_pseudo_id和ga_session_id共同唯一标识，因此在prep阶段提取这两个字段，并过滤掉缺失值；
• 通过CONCAT(user_pseudo_id, '.', session_id)生成唯一会话标识，再用COUNT(DISTINCT)统计每个URL下的独立会话数。

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