GA4数据提取求助：BigQuery中缺失GA维度与指标

以下是你需要的原GA维度/指标在GA4 BigQuery中的替代实现方式：

• Users（用户数）
  GA4 BigQuery无直接Users字段，需通过用户唯一标识user_pseudo_id去重统计：

  SELECT COUNT(DISTINCT user_pseudo_id) AS users
  FROM `你的项目ID.analytics_xxxx.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '起始日期（如20240101）' AND '结束日期（如20240131）'
  

  若需统计已登录用户，可替换为user_id去重。

• Sessions（会话数）
  GA4不使用UA的totals.visits，需通过user_pseudo_id+session_id的组合去重统计有效会话：

  SELECT COUNT(DISTINCT CONCAT(user_pseudo_id, '.', CAST(session_id AS STRING))) AS sessions
  FROM `你的项目ID.analytics_xxxx.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '起始日期' AND '结束日期'
  

  如需对齐GA界面数据，可过滤仅包含有效交互事件（如page_view、purchase）的会话。

• TransactionsPerSession（每会话交易数）
  先统计总交易数与总会话数，再做除法计算：

  WITH session_stats AS (
    SELECT COUNT(DISTINCT CONCAT(user_pseudo_id, '.', CAST(session_id AS STRING))) AS total_sessions
    FROM `你的项目ID.analytics_xxxx.events_*`
    WHERE _TABLE_SUFFIX BETWEEN '起始日期' AND '结束日期'
  ),
  transaction_stats AS (
    SELECT COUNT(DISTINCT params.value.string_value) AS total_transactions
    FROM `你的项目ID.analytics_xxxx.events_*`,
    UNNEST(event_params) AS params
    WHERE _TABLE_SUFFIX BETWEEN '起始日期' AND '结束日期'
      AND event_name = 'purchase'
      AND params.key = 'transaction_id'
  )
  SELECT total_transactions / total_sessions AS transactions_per_session
  FROM session_stats, transaction_stats
  

• CountryIsoCode（国家ISO代码）
  GA4 BigQuery返回的geo.country是完整国名，可通过自定义映射表转换为ISO代码：

  WITH country_mapping AS (
    SELECT 'Spain' AS country_name, 'ES' AS country_code UNION ALL
    SELECT 'United States' AS country_name, 'US' AS country_code UNION ALL
    SELECT 'China' AS country_name, 'CN' AS country_code
    -- 按需补充其他国家映射
  )
  SELECT 
    cm.country_code AS country_iso_code,
    COUNT(DISTINCT user_pseudo_id) AS users
  FROM `你的项目ID.analytics_xxxx.events_*` e
  LEFT JOIN country_mapping cm ON e.geo.country = cm.country_name
  WHERE _TABLE_SUFFIX BETWEEN '起始日期' AND '结束日期'
  GROUP BY cm.country_code
  

• avgSessionDuration（平均会话时长）
  通过会话的首尾事件时间差计算单会话时长，再取平均值：

  WITH session_durations AS (
    SELECT
      CONCAT(user_pseudo_id, '.', CAST(session_id AS STRING)) AS session_key,
      -- 毫秒转秒计算会话时长
      (MAX(event_timestamp) - MIN(event_timestamp)) / 1000000 AS session_duration_sec
    FROM `你的项目ID.analytics_xxxx.events_*`
    WHERE _TABLE_SUFFIX BETWEEN '起始日期' AND '结束日期'
    GROUP BY session_key
  )
  SELECT AVG(session_duration_sec) AS avg_session_duration_sec
  FROM session_durations
  WHERE session_duration_sec > 0 -- 过滤无效短会话
  

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