高级查询

本页面中的高级查询适用于 Google Analytics(分析)4 的 BigQuery 事件导出数据。如果您在寻找适用于 Universal Analytics 的同类资源,不妨参阅适用于 Universal Analytics 的 BigQuery 实战宝典。请先尝试基本查询,然后再试用高级查询。

以下查询会显示已购买特定产品的客户还购买了哪些其他产品。本示例并不假设客户是在同一订单中购买的这些产品。

优化版查询示例依靠 BigQuery 脚本功能来定义变量,用以声明要过滤的商品。虽然这并不能提高性能,但与使用 WITH 子句创建单值表相比,这种定义变量的方法可读性更高。简化版查询使用的就是前一种方法,即使用 WITH 子句。

简化版查询会创建一个单独的“产品 A 买家”名单,并与该数据联接。优化版查询则使用 ARRAY_AGG 函数创建一个列表,其中包含某位用户在各个订单中购买的所有商品。然后,使用外部 WHERE 子句,针对 target_item 过滤所有用户的购买列表,最后仅显示相关商品。

-- Example: Products purchased by customers who purchased a specific product.
--
-- `Params` is used to hold the value of the selected product and is referenced
-- throughout the query.

WITH
  Params
AS (
   
-- Replace with selected item_name or item_id.
   
SELECT 'Google Navy Speckled Tee' AS selected_product
 
),
  PurchaseEvents
AS (
   
SELECT
      user_pseudo_id
,
      items
   
FROM
     
-- Replace table name.
     
`bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
   
WHERE
     
-- Replace date range.
      _TABLE_SUFFIX
BETWEEN '20201101' AND '20210131'
     
AND event_name = 'purchase'
 
),
  ProductABuyers
AS (
   
SELECT DISTINCT
      user_pseudo_id
   
FROM
      Params
,
      PurchaseEvents
,
      UNNEST
(items) AS items
   
WHERE
     
-- item.item_id can be used instead of items.item_name.
      items
.item_name = selected_product
 
)
SELECT
  items
.item_name AS item_name,
  SUM
(items.quantity) AS item_quantity
FROM
  Params
,
  PurchaseEvents
,
  UNNEST
(items) AS items
WHERE
  user_pseudo_id
IN (SELECT user_pseudo_id FROM ProductABuyers)
 
-- item.item_id can be used instead of items.item_name
 
AND items.item_name != selected_product
GROUP BY 1
ORDER BY item_quantity DESC;
-- Optimized Example: Products purchased by customers who purchased a specific product.

-- Replace item name
DECLARE target_item STRING DEFAULT 'Google Navy Speckled Tee';

SELECT
  IL
.item_name AS item_name,
  SUM
(IL.quantity) AS quantity
FROM
 
(
   
SELECT
      user_pseudo_id
,
      ARRAY_AGG
(STRUCT(item_name, quantity)) AS item_list
   
FROM
     
-- Replace table
     
`bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`, UNNEST(items)
   
WHERE
     
-- Replace date range
      _TABLE_SUFFIX
BETWEEN '20201201' AND '20201210'
     
AND event_name = 'purchase'
   
GROUP BY
     
1
 
),
  UNNEST
(item_list) AS IL
WHERE
  target_item
IN (SELECT item_name FROM UNNEST(item_list))
 
-- Remove the following line if you want the target_item to appear in the results
 
AND target_item != IL.item_name
GROUP BY
  item_name
ORDER BY
  quantity
DESC;

用户每次购买会话的平均支出金额

以下查询会显示每位用户每次会话的平均支出金额。此查询仅考虑用户完成了购买的会话。

-- Example: Average amount of money spent per purchase session by user.

WITH
  events
AS (
   
SELECT
      session
.value.int_value AS session_id,
     
COALESCE(spend.value.int_value, spend.value.float_value, spend.value.double_value, 0.0)
       
AS spend_value,
      event
.*

   
-- Replace table name
   
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*` AS event
   
LEFT JOIN UNNEST(event.event_params) AS session
     
ON session.key = 'ga_session_id'
   
LEFT JOIN UNNEST(event.event_params) AS spend
     
ON spend.key = 'value'

   
-- Replace date range
   
WHERE _TABLE_SUFFIX BETWEEN '20201101' AND '20210131'
 
)
SELECT
  user_pseudo_id
,
  COUNT
(DISTINCT session_id) AS session_count,
  SUM
(spend_value) / COUNT(DISTINCT session_id) AS avg_spend_per_session_by_user
FROM events
WHERE event_name = 'purchase' and session_id IS NOT NULL
GROUP BY user_pseudo_id

用户的最新会话 ID 和会话编号

以下查询提供用户列表在过去 4 天中最新 ga_session_id 和 ga_session_number 的列表。您可以提供 user_pseudo_id 列表或 user_id 列表。

-- Get the latest ga_session_id and ga_session_number for specific users during last 4 days.

-- Replace timezone. List at https://en.wikipedia.org/wiki/List_of_tz_database_time_zones.
DECLARE REPORTING_TIMEZONE STRING DEFAULT 'America/Los_Angeles';

-- Replace list of user_pseudo_id's with ones you want to query.
DECLARE USER_PSEUDO_ID_LIST ARRAY<STRING> DEFAULT
 
[
   
'1005355938.1632145814', '979622592.1632496588', '1101478530.1632831095'];

CREATE TEMP FUNCTION GetParamValue(params ANY TYPE, target_key STRING)
AS (
 
(SELECT `value` FROM UNNEST(params) WHERE key = target_key LIMIT 1)
);

CREATE TEMP FUNCTION GetDateSuffix(date_shift INT64, timezone STRING)
AS (
 
(SELECT FORMAT_DATE('%Y%m%d', DATE_ADD(CURRENT_DATE(timezone), INTERVAL date_shift DAY)))
);

SELECT DISTINCT
  user_pseudo_id
,
  FIRST_VALUE
(GetParamValue(event_params, 'ga_session_id').int_value)
   
OVER (UserWindow) AS ga_session_id,
  FIRST_VALUE
(GetParamValue(event_params, 'ga_session_number').int_value)
   
OVER (UserWindow) AS ga_session_number
FROM
 
-- Replace table name.
 
`bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE
  user_pseudo_id
IN UNNEST(USER_PSEUDO_ID_LIST)
 
AND RIGHT(_TABLE_SUFFIX, 8)
   
BETWEEN GetDateSuffix(-3, REPORTING_TIMEZONE)
   
AND GetDateSuffix(0, REPORTING_TIMEZONE)
WINDOW UserWindow
AS (PARTITION BY user_pseudo_id ORDER BY event_timestamp DESC);

-- Get the latest ga_session_id and ga_session_number for specific users during last 4 days.

-- Replace timezone. List at https://en.wikipedia.org/wiki/List_of_tz_database_time_zones.
DECLARE REPORTING_TIMEZONE STRING DEFAULT 'America/Los_Angeles';

-- Replace list of user_id's with ones you want to query.
DECLARE USER_ID_LIST ARRAY<STRING> DEFAULT ['<user_id_1>', '<user_id_2>', '<user_id_n>'];

CREATE TEMP FUNCTION GetParamValue(params ANY TYPE, target_key STRING)
AS (
 
(SELECT `value` FROM UNNEST(params) WHERE key = target_key LIMIT 1)
);

CREATE TEMP FUNCTION GetDateSuffix(date_shift INT64, timezone STRING)
AS (
 
(SELECT FORMAT_DATE('%Y%m%d', DATE_ADD(CURRENT_DATE(timezone), INTERVAL date_shift DAY)))
);

SELECT DISTINCT
  user_pseudo_id
,
  FIRST_VALUE
(GetParamValue(event_params, 'ga_session_id').int_value)
   
OVER (UserWindow) AS ga_session_id,
  FIRST_VALUE
(GetParamValue(event_params, 'ga_session_number').int_value)
   
OVER (UserWindow) AS ga_session_number
FROM
 
-- Replace table name.
 
`bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE
  user_id
IN UNNEST(USER_ID_LIST)
 
AND RIGHT(_TABLE_SUFFIX, 8)
   
BETWEEN GetDateSuffix(-3, REPORTING_TIMEZONE)
   
AND GetDateSuffix(0, REPORTING_TIMEZONE)
WINDOW UserWindow
AS (PARTITION BY user_pseudo_id ORDER BY event_timestamp DESC);