GA4 BigQuery着陆页新用户与购买量统计SQL查询异常问题

原查询的核心问题是：first_visit事件与标记着陆页的entrances=1事件（属于page_view事件）并非同一条记录。GA4中，用户首次访问会触发first_visit事件，而着陆页信息存储在该用户首个会话的page_view事件的entrances参数中。直接将两类事件混在同一张表分组统计，无法匹配有效数据，导致new_users始终为0。

要统计每个着陆页的新用户数，需先关联用户首次访问与其首个会话的着陆页，具体实现如下：

WITH user_first_landing AS (
  -- 获取每个用户首个会话的着陆页
  SELECT
    user_pseudo_id,
    FIRST_VALUE(
      (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location')
    ) OVER (
      PARTITION BY user_pseudo_id
      ORDER BY event_timestamp ASC
      ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS landing_page
  FROM `my-websire-ga4.analytics_345406046.events_*`
  -- 筛选标记着陆页的page_view事件
  WHERE event_name = 'page_view'
    AND (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'entrances') = 1
),
new_users AS (
  -- 提取所有新用户（触发first_visit事件的唯一用户）
  SELECT DISTINCT user_pseudo_id
  FROM `my-websire-ga4.analytics_345406046.events_*`
  WHERE event_name = 'first_visit'
)
-- 关联统计每个着陆页的新用户数
SELECT
  u.landing_page,
  COUNT(n.user_pseudo_id) AS new_users
FROM user_first_landing u
JOIN new_users n ON u.user_pseudo_id = n.user_pseudo_id
GROUP BY u.landing_page
ORDER BY new_users DESC;

• user_first_landing CTE：筛选所有标记为着陆页的page_view事件，通过窗口函数FIRST_VALUE获取每个用户最早的着陆页（即首次访问的着陆页）。
• new_users CTE：提取触发过first_visit事件的唯一用户ID，这些就是新用户。
• 最后关联两个CTE，按着陆页分组统计新用户数量。

如果需要同时统计购买量，可以扩展查询如下：

WITH user_first_landing AS (
  SELECT
    user_pseudo_id,
    FIRST_VALUE(
      (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location')
    ) OVER (
      PARTITION BY user_pseudo_id
      ORDER BY event_timestamp ASC
      ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS landing_page
  FROM `my-websire-ga4.analytics_345406046.events_*`
  WHERE event_name = 'page_view'
    AND (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'entrances') = 1
),
new_users AS (
  SELECT DISTINCT user_pseudo_id
  FROM `my-websire-ga4.analytics_345406046.events_*`
  WHERE event_name = 'first_visit'
),
purchases AS (
  -- 统计每个用户的购买次数
  SELECT
    user_pseudo_id,
    COUNT(*) AS purchase_count
  FROM `my-websire-ga4.analytics_345406046.events_*`
  WHERE event_name = 'purchase'
  GROUP BY user_pseudo_id
)
SELECT
  u.landing_page,
  COUNT(n.user_pseudo_id) AS new_users,
  COALESCE(SUM(p.purchase_count), 0) AS total_purchases
FROM user_first_landing u
LEFT JOIN new_users n ON u.user_pseudo_id = n.user_pseudo_id
LEFT JOIN purchases p ON u.user_pseudo_id = p.user_pseudo_id
GROUP BY u.landing_page
ORDER BY new_users DESC;

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