進階查詢

此頁面上的進階查詢適用於以下位置的 BigQuery 事件匯出資料: Google Analytics如果您有通用 Analytics 專用 BigQuery 教戰手冊, 和通用 Analytics 的相同資源試試基本查詢 ,再試用進階版功能

下列查詢顯示消費者購買了其他產品 購買了特定產品本例並未假設產品 是在同一筆訂單中購買的。

最佳化的範例會使用 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 和工作階段號碼

下列查詢提供了最新 ga_session_id 和 最近 4 天以來的 ga_session_number。您可以提供 user_pseudo_id 清單或 user_id 清單。

user_pseudo_iduser_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);