BigQuery 教戰手冊

只有 Google Marketing Platform 隨附的 Analytics (分析) 360 可使用這項功能。
進一步瞭解 Google Marketing Platform。

本文以實例說明如何建構查詢，找出匯出至 BigQuery 的 Analytics (分析) 資料。我們提供了範本資料集，方便您利用本文中的查詢進行練習。

本文內容：

查詢最佳化

您執行的每項查詢都會計入每月資料處理額度。如果您選取了多餘的欄位，就會增加需要處理的資料量，導致額外佔用您的每月資料處理額度。最佳化查詢有助您善用每月資料處理額度。

進一步瞭解定價。

只選取必要項目

建立查詢時，請在 SELECT 陳述式中選擇相關的欄位。若未呼叫多餘的欄位，即可減少處理查詢需要的資料量和時間。

範例：避免使用萬用字元運算子

不當格式：使用萬用字元運算子
SELECT *
FROM [table name];

 

適當格式：使用欄位名稱避免不必要的處理
SELECT field1, field2
FROM [table name];

允許快取

請儘可能不要以函式做為欄位。函式 (如 NOW() 或 TODAY()) 會傳回變數結果，這會防止系統快取查詢，因此能加快傳回的速度。請改用特定時間和日期欄位。

無論是否已勾選「Use Cached Results」(使用快取結果)，目前使用萬用字元查詢多個資料表時，不支援快取結果。如果您執行相同的萬用字元查詢很多次，系統會針對每一筆查詢向您收費。瞭解詳情

使用中繼資料表進行常用子查詢

如果您發現您重複使用特定的查詢作為子查詢，可以將這個查詢儲存為中繼表格，方法是按一下查詢結果上方的 [儲存為表格]。接著，您就可以在查詢的 FROM 部分參照這份表格，這樣能減少需要處理的資料量及處理時間。

使用中繼表格
SELECT field1, field2
FROM [Dataset name.table name];

查詢偵錯

BigQuery 會在您建立程式碼時為您偵錯在組成視窗中，偵錯結果就顯示在查詢下方；您也可以透過使用 dryRun 標記的 API 取得偵錯結果。

有效查詢有綠色的指標，點擊指標即可看到查詢處理的資料量。這項功能讓您有機會在執行查詢以前將資料最佳化，以避免浪費資料處理。

Query Debugging - Success

 

無效查詢有紅色指標，點擊指標即可看到錯誤相關資訊，還能找出發生錯誤的行和欄位。在下方範例中，GROUP BY 陳述式為空白，且系統已標出錯誤。

Query Debugging - Error

 

提示與最佳做法

使用範例資料集

下例使用的是 Google Analytics (分析) 範例資料集。

如要在您自己的資料中使用查詢，只要將範例中的專案和資料集名稱改成您自己的即可。

使用標準 SQL 和舊版 SQL 的差異

BigQuery 支援兩種 SQL 語法：

如要瞭解這兩種語法的差異，請參閱「遷移至標準 SQL」一文。

如要查詢儲存在 BigQuery 中的資料，目前建議使用標準 SQL 語法。

請參閱「啟用標準 SQL」一文，瞭解如何透過 BigQuery UI、CLI、API 或任何您使用的介面啟用標準 SQL。

最簡單的著手方式是在標準 SQL 查詢的最上方加入「standardSQL」註解，如以下範例所示。

舊版 SQL 每天都會將 Google Analytics (分析) 360 資料傳送到新的資料表。如要一次查詢多份資料表，除了以半形逗號分隔資料表名稱外，還可以使用 TABLE_DATE_RANGE 表格萬用字元函式或多個以半形逗號隔開的 TABLE_DATE_RANGE 函式，如下列範例所示。

查詢多份表格

下列範例將說明同一筆資料的標準 SQL 和傳統 SQL 查詢有何差異。

最近 3 天

標準 SQL
3 天：使用 UNION ALL
#standardSQL
WITH ga_tables AS (
SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20160801`
GROUP BY date

UNION ALL

SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20160802`
GROUP BY date

UNION ALL

SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20160803`
GROUP BY date

)
SELECT
date,
visits,
pageviews,
transactions,
revenue,
FROM ga_tables
ORDER BY date ASC
舊版 SQL
3 天：使用逗號分隔表格名稱
SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM
[bigquery-public-data.google_analytics_sample.ga_sessions_20160801],
[bigquery-public-data.google_analytics_sample.ga_sessions_20160802],
[bigquery-public-data.google_analytics_sample.ga_sessions_20160803]
GROUP BY
date
ORDER BY
date ASC

 

過去 1095 天

標準 SQL
過去 1095 天：使用 _TABLE_SUFFIX
#standardSQL
SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 1095 DAY))
AND
FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
GROUP BY date
ORDER BY date ASC
舊版 SQL
過去 1095 天：使用 TABLE_DATE_RANGE
SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM
(TABLE_DATE_RANGE([bigquery-public-data.google_analytics_sample.ga_sessions_],
DATE_ADD(CURRENT_TIMESTAMP(), -1095, 'DAY'), DATE_ADD(CURRENT_TIMESTAMP(), -1, 'DAY')))
GROUP BY
date
ORDER BY
date ASC

 

過去 36 個月

標準 SQL
過去 36 個月：使用 _TABLE_SUFFIX
#standardSQL
SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 36 MONTH))
AND
FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
GROUP BY date
ORDER BY date ASC
舊版 SQL
過去 36 個月：使用 TABLE_DATE_RANGE
SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM
(TABLE_DATE_RANGE([bigquery-public-data.google_analytics_sample.ga_sessions_],
DATE_ADD(CURRENT_TIMESTAMP(), -36, 'MONTH'), DATE_ADD(CURRENT_TIMESTAMP(), -1, 'DAY')))
GROUP BY
date
ORDER BY
date ASC

 

過去 3 年

標準 SQL
過去 3 年：使用 _TABLE_SUFFIX
#standardSQL
SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 3 YEAR))
AND
FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
GROUP BY date
ORDER BY date ASC
舊版 SQL
過去 3 年：使用 TABLE_DATE_RANGE
SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM
(TABLE_DATE_RANGE([bigquery-public-data.google_analytics_sample.ga_sessions_],
DATE_ADD(CURRENT_TIMESTAMP(), -3, 'YEAR'), DATE_ADD(CURRENT_TIMESTAMP(), -1, 'DAY')))
GROUP BY
date
ORDER BY
date ASC

 

特定日期範圍

標準 SQL
特定日期範圍：使用 _TABLE_SUFFIX
#standardSQL
SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_TABLE_SUFFIX BETWEEN '20160801' AND '20170731'
GROUP BY date
ORDER BY date ASC
舊版 SQL
特定日期範圍：使用 TABLE_DATE_RANGE
SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM
(TABLE_DATE_RANGE([bigquery-public-data.google_analytics_sample.ga_sessions_],
TIMESTAMP('2016-08-01'), TIMESTAMP('2017-07-31')))
GROUP BY
date
ORDER BY
date ASC

過去 3 年加上今天的資料 (當日)

標準 SQL
過去 3 年加上今天的資料 (當日)：使用 UNION ALL & _TABLE_SUFFIX
注意：此查詢範例不適用於 Google Analytics (分析) 公開資料集，因為目前沒有當日資料表。
#standardSQL
WITH ga_tables AS ( SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 3 YEAR))
AND
FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
GROUP BY date
UNION ALL

SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
AND
FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 0 DAY))
GROUP BY date
)
SELECT
date,
visits,
pageviews,
transactions,
revenue,
FROM ga_tables
ORDER BY date ASC

舊版 SQL

過去 3 年加上今天的資料 (當日)：使用多個 TABLE_DATE_RANGE
注意：此查詢範例不適用於 Google Analytics (分析) 公開資料集，因為目前沒有當日資料表。
SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM
(TABLE_DATE_RANGE([bigquery-public-data.google_analytics_sample.ga_sessions_],
DATE_ADD(CURRENT_TIMESTAMP(), -3, 'YEAR'), DATE_ADD(CURRENT_TIMESTAMP(), -1, 'DAY'))),
(TABLE_DATE_RANGE([bigquery-public-data.google_analytics_sample.ga_sessions_intraday_],
DATE_ADD(CURRENT_TIMESTAMP(), -1, 'DAY'), CURRENT_TIMESTAMP()))
GROUP BY
date
ORDER BY
date ASC

 

 

基本查詢範例

本節會說明如何使用範例 Analytics (分析) 資料中的指標和維度建立基本查詢。

每 [維度] 總 [指標]？

以下範例指令碼旨在回答此問題：「2017 年 7 月每個裝置瀏覽器帶來的總交易次數是多少？」

標準 SQL

2017 年 7 月每裝置瀏覽器的總交易次數
#standardSQL
SELECT
device.browser,
SUM ( totals.transactions ) AS total_transactions
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_TABLE_SUFFIX BETWEEN '20170701' AND '20170731'
GROUP BY
device.browser
ORDER BY
total_transactions DESC

舊版 SQL

2017 年 7 月每裝置瀏覽器的總交易次數
SELECT
device.browser,
SUM ( totals.transactions ) AS total_transactions
FROM TABLE_DATE_RANGE( [bigquery-public-data.google_analytics_sample.ga_sessions_],
TIMESTAMP('2017-07-01'), TIMESTAMP('2017-07-31') )
GROUP BY
device.browser
ORDER BY
total_transactions DESC

 

 

每 [維度] 平均跳出率？

實際跳出率的定義為「單次網頁瀏覽的造訪次數百分比」。以下範例指令碼旨在回答此問題：「每流量來源的實際跳出率是多少？」

標準 SQL

2017 年 7 月每流量來源的實際跳出率
#standardSQL
SELECT
source,
total_visits,
total_no_of_bounces,
( ( total_no_of_bounces / total_visits ) * 100 ) AS bounce_rate
FROM (
SELECT
trafficSource.source AS source,
COUNT ( trafficSource.source ) AS total_visits,
SUM ( totals.bounces ) AS total_no_of_bounces
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_TABLE_SUFFIX BETWEEN '20170701' AND '20170731'
GROUP BY
source )
ORDER BY
total_visits DESC

舊版 SQL

2017 年 7 月每流量來源的實際跳出率
SELECT
source,
total_visits,
total_no_of_bounces,
( ( total_no_of_bounces / total_visits ) * 100 ) AS bounce_rate
FROM (
SELECT
trafficSource.source AS source,
COUNT ( trafficSource.source ) AS total_visits,
SUM ( totals.bounces ) AS total_no_of_bounces
FROM
TABLE_DATE_RANGE( [bigquery-public-data.google_analytics_sample.ga_sessions_],
TIMESTAMP('2017-07-01'), TIMESTAMP('2017-07-31') )
GROUP BY
source )
ORDER BY
total_visits DESC

 

 

按購買者類型劃分的平均產品網頁瀏覽量 (購買者與無購買意願的使用者)

以下範例指令碼旨在回答此問題：「2017 年 7 月，購買者的平均產品網頁瀏覽量次數是多少？」

標準 SQL

2017 年 7 月購買者的平均產品網頁瀏覽量
#standardSQL
SELECT
( SUM(total_pagesviews_per_user) / COUNT(users) ) AS avg_pageviews_per_user
FROM (
SELECT
fullVisitorId AS users,
SUM(totals.pageviews) AS total_pagesviews_per_user
FROM`bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_TABLE_SUFFIX BETWEEN '20170701' AND '20170731'
AND
totals.transactions >=1
GROUP BY
users )

舊版 SQL

2017 年 7 月購買者的平均產品網頁瀏覽量
SELECT
( SUM(total_pagesviews_per_user) / COUNT(users) ) AS avg_pageviews_per_user
FROM (
SELECT
fullVisitorId AS users,
SUM(totals.pageviews) AS total_pagesviews_per_user
FROM
TABLE_DATE_RANGE( [bigquery-public-data.google_analytics_sample.ga_sessions_],
TIMESTAMP('2017-07-01'), TIMESTAMP('2017-07-31') ) WHERE
totals.transactions >=1
GROUP BY
users )

 

 

以下範例指令碼旨在回答此問題：「2017 年 7 月，未購買者的平均產品網頁瀏覽量為何？」

標準 SQL

2017 年 7 月未購買者的平均產品網頁瀏覽量
#standardSQL
SELECT
( SUM(total_pagesviews_per_user) / COUNT(users) ) AS avg_pageviews_per_user
FROM (
SELECT
fullVisitorId AS users,
SUM(totals.pageviews) AS total_pagesviews_per_user
FROM`bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_TABLE_SUFFIX BETWEEN '20170701' AND '20170731'
AND
totals.transactions IS NULL
GROUP BY
users )

舊版 SQL

2017 年 7 月未購買者的平均產品網頁瀏覽量
SELECT
( SUM(total_pagesviews_per_user) / COUNT(users) ) AS avg_pageviews_per_user
FROM (
SELECT
fullVisitorId AS users,
SUM(totals.pageviews) AS total_pagesviews_per_user
FROM
TABLE_DATE_RANGE( [bigquery-public-data.google_analytics_sample.ga_sessions_],
TIMESTAMP('2017-07-01'), TIMESTAMP('2017-07-31') ) WHERE
totals.transactions IS NULL
GROUP BY
users )

 

 

每位購買者的平均交易次數

以下範例指令碼旨在回答此問題：「2017 年 7 月，每位購買者的平均總交易次數是多少？」

標準 SQL

2017 年 7 月每位購買者的平均交易次數
#standardSQL
SELECT
(SUM (total_transactions_per_user) / COUNT(fullVisitorId) ) AS avg_total_transactions_per_user
FROM (
SELECT
fullVisitorId,
SUM (totals.transactions) AS total_transactions_per_user
FROM
`bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_TABLE_SUFFIX BETWEEN '20170701' AND '20170731'
AND totals.transactions IS NOT NULL
GROUP BY
fullVisitorId )

舊版 SQL

2017 年 7 月每位購買者的平均交易次數
SELECT
(SUM (total_transactions_per_user) / COUNT(fullVisitorId) ) AS avg_total_transactions_per_user
FROM (
SELECT
fullVisitorId,
SUM (totals.transactions) AS total_transactions_per_user
FROM
TABLE_DATE_RANGE( [bigquery-public-data.google_analytics_sample.ga_sessions_],
TIMESTAMP('2017-07-01'), TIMESTAMP('2017-07-31') )
WHERE
totals.transactions IS NOT NULL
GROUP BY
fullVisitorId )

 

 

單次工作階段平均花費的金額

以下範例指令碼旨在回答此問題：「2017 年 7 月單次工作階段平均花費的金額是多少？」

標準 SQL

2017 年 7 月單次工作階段平均花費的金額
#standardSQL
SELECT
( SUM(total_transactionrevenue_per_user) / SUM(total_visits_per_user) ) AS
avg_revenue_by_user_per_visit
FROM (
SELECT
fullVisitorId,
SUM( totals.visits ) AS total_visits_per_user,
SUM( totals.transactionRevenue ) AS total_transactionrevenue_per_user
FROM
`bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_TABLE_SUFFIX BETWEEN '20170701' AND '20170731'
AND
totals.visits > 0
AND totals.transactions >= 1
AND totals.transactionRevenue IS NOT NULL
GROUP BY
fullVisitorId )

舊版 SQL

2017 年 7 月單次工作階段平均花費的金額
SELECT
( SUM(total_transactionrevenue_per_user) / SUM(total_visits_per_user) ) AS
avg_revenue_by_user_per_visit
FROM (
SELECT
fullVisitorId,
SUM( totals.visits ) AS total_visits_per_user,
SUM( totals.transactionRevenue ) AS total_transactionrevenue_per_user
FROM
TABLE_DATE_RANGE( [bigquery-public-data.google_analytics_sample.ga_sessions_],
TIMESTAMP('2017-07-01'), TIMESTAMP('2017-07-31') )
WHERE
totals.visits > 0
AND totals.transactions >= 1
AND totals.transactionRevenue IS NOT NULL
GROUP BY
fullVisitorId )

 

 

命中的順序

以下範例指令碼旨在回答此問題：「針對已瀏覽的網頁，使用者的瀏覽順序為何？」

標準 SQL

2017 年 7 月使用者的網頁瀏覽順序
#standardSQL
SELECT
fullVisitorId,
visitId,
visitNumber,
hits.hitNumber AS hitNumber,
hits.page.pagePath AS pagePath
FROM
`bigquery-public-data.google_analytics_sample.ga_sessions_*`,
UNNEST(hits) as hits
WHERE
_TABLE_SUFFIX BETWEEN '20170701' AND '20170731'
AND
hits.type="PAGE"
ORDER BY
fullVisitorId,
visitId,
visitNumber,
hitNumber

舊版 SQL

2017 年 7 月使用者的網頁瀏覽順序
SELECT
fullVisitorId,
visitId,
visitNumber,
hits.hitNumber AS hitNumber,
hits.page.pagePath AS pagePath
FROM
TABLE_DATE_RANGE( [bigquery-public-data.google_analytics_sample.ga_sessions_],
TIMESTAMP('2017-07-01'), TIMESTAMP('2017-07-31') )
WHERE
hits.type="PAGE"
ORDER BY
fullVisitorId,
visitId,
visitNumber,
hitNumber

在這項查詢中，請將命中類型限制為 PAGES，避免出現事件或交易互動。產生的每一行都代表一次瀏覽，且會按照 SELECT 陳述式中預設的欄位順序顯示。

 

 

在匹配或工作階段層級的多個自訂維度

命中層級的自訂維度
SELECT fullVisitorId, visitId, hits.hitNumber, hits.time,
MAX(IF(hits.customDimensions.index=1,
hits.customDimensions.value,
NULL)) WITHIN hits AS customDimension1,
FROM [tableID.ga_sessions_20150305]
LIMIT 100

 

工作階段層級的自訂維度
SELECT fullVisitorId, visitId,
MAX(IF(customDimensions.index=2,
customDimensions.value,
NULL)) WITHIN RECORD AS customDimension2,
FROM [tableID.ga_sessions_20150305]
LIMIT 100

在每筆查詢中：

SELECT 陳述式會查詢相關的維度和指標欄位。

MAX 函式：

  • 傳回自訂維度做為新欄位。若要傳回多個自訂維度做為新欄位，您可以重複這個函式。
  • WITHIN hits 和 WITHIN RECORD 會評估 BigQuery 重複欄位中的條件。
  • 系統會評估每個自訂維度中 MAX 的條件，但如果是 index=1 (hits) 或 index=2 (sessions) 以外的條件，函式會傳回 NULL。
  • 傳回最大值，命中的自訂維度值是 1，工作階段的自訂維度值則是 2，任何其他值則為 NULL。

進階查詢範例

熟悉查詢的基本概念後，現在您就可以使用 BigQuery 提供的進階函式和功能來建立查詢。

購買了產品 A 的客戶購買的產品 (傳統型電子商務)

下方是此問題的基本架構指令碼：購買了產品 A 的客戶還購買了什麼其他產品？

購買產品 A 的客戶還購買了哪些產品 (傳統型電子商務)
SELECT hits.item.productName AS other_purchased_products, COUNT(hits.item.productName) AS quantity
FROM [‘Dataset Name’ ]
WHERE fullVisitorId IN (
  SELECT fullVisitorId
  FROM [‘Dataset Name’ ]
  WHERE hits.item.productName CONTAINS 'Product Item Name A'
   AND totals.transactions>=1
  GROUP BY fullVisitorId )
 AND hits.item.productName IS NOT NULL
 AND hits.item.productName != 'Product Item Name A'
GROUP BY other_purchased_products
ORDER BY quantity DESC;
  1. 在第一行中，您選取某位使用者購買的所有其他項目，且使用彙整函式 COUNT() 計算其他各品項的購買數量。結果會顯示在標為 quantity 的欄位中，而產品欄位中的相關項目會標為 other_purchased_products。
  2. 在灰色子查詢中，您只選取已進行交易 (totals.transactions>=1)，且在交易期間購買了產品 A (WHERE hits.item.productName CONTAINS 'Product Item Name A') 的不重複使用者 (fullVisitorId)。

當 hits.item.productName 中的值為 Null 且包含產品 A，頂層查詢 (綠色) 中的規則 (WHERE 和 AND 陳述式) 就不會採用這些值。

以下範例說明這項查詢：購買了 Brighton Metallic Pens 鋼筆 (4 號) 的客戶，還購買了哪些其他產品？

2013 年 6 月 24 日購買「Brighton Metallic Pens 鋼筆 (4 號)」的客戶還購買了哪些產品
SELECT hits.item.productName AS other_purchased_products, COUNT(hits.item.productName) AS quantity
FROM [GoogleStore.ga_sessions_20130624]
WHERE fullVisitorId IN (
  SELECT fullVisitorId
  FROM [GoogleStore.ga_sessions_20130624]
  WHERE hits.item.productName CONTAINS 'Brighton Metallic Pens - Set of 4'
   AND totals.transactions>=1
  GROUP BY fullVisitorId )
 AND hits.item.productName IS NOT NULL
 AND hits.item.productName !='Brighton Metallic Pens - Set of 4'
GROUP BY other_purchased_products
ORDER BY quantity DESC;

在 Dremel/BigQuery 中，使用 WHERE expr IN 會觸發一個 JOIN，且有大小限制；特別是 JOIN 右側的大小 (在這裡是指訪客數量) 不得超過 8 MB。Dremel 中的這個項目稱為 broadcast JOIN。如果大小超過 8 MB，您就必須使用 JOIN EACH 語法觸發 shuffled JOIN。雖然不能使用 IN 觸發，但可以用 JOIN 重新撰寫相同的查詢。

購買了產品 A 的客戶購買的產品 (加強型電子商務)

這與前述的架構查詢類似，但可用於加強型電子商務，這個指令碼也會使用 TABLE_DATE_RANGE 查詢多日的資料。

購買產品 A 的客戶還購買了哪些產品 (加強型電子商務)
SELECT hits.product.productSKU AS other_purchased_products, COUNT(hits.product.productSKU) AS quantity
FROM SELECT fullVisitorId, hits.product.productSKU, hits.eCommerceAction.action_type FROM TABLE_DATE_RANGE([73156703.ga_sessions_], TIMESTAMP('2015-07-01'), TIMESTAMP('2015-07-14')))
WHERE fullVisitorId IN (
  SELECT fullVisitorId
FROM TABLE_DATE_RANGE([73156703.ga_sessions_], TIMESTAMP('2015-07-01'), TIMESTAMP('2015-07-14'))
WHERE hits.product.productSKU CONTAINS '10 15103'
AND hits.eCommerceAction.action_type = '6'
GROUP BY fullVisitorId )
AND hits.product.productSKU IS NOT NULL
AND hits.product.productSKU !='10 15103'
AND hits.eCommerceAction.action_type = '6'
GROUP BY other_purchased_products
ORDER BY quantity DESC;

購買前的平均使用者互動次數

這是使用 JOIN() [...] ON 指令的查詢範例，其中僅使用 Analytics (分析) 資料。

下列基本架構指令碼的目的在於回答：購買前的平均使用者互動次數是多少？

購買前的使用者互動次數
SELECT one.hits.item.productSku AS ProductSku, ( sum_of_hit_number / total_hits ) AS avg_hit_number
FROM (
 SELECT hits.item.productSku, SUM(hits.hitNumber) AS sum_of_hit_number
 FROM [‘GA Dataset Name’ ]
 WHERE hits.item.productSku IS NOT NULL
  AND totals.transactions>=1
 GROUP BY hits.item.productSku ) AS ‘Alias_Name_1’
JOIN (
 SELECT hits.item.productSku, COUNT( fullVisitorId ) AS total_hits
 FROM [‘GA Dataset Name’ ]
 WHERE hits.item.productSku IS NOT NULL
  AND totals.transactions>=1
 GROUP BY hits.item.productSku ) AS ‘Alias_Name_2’
ON Alias_Name_1.hits.item.productSku = Alias_Name_2.hits.item.productSku;
  1. 第一行執行的主要數學運算式，主要是找出每產品的平均使用者互動次數，且這項查詢可讓名為 ‘Alias_Name_1’ 和 ‘Alias_Name_2’ 的兩個子查詢互相參照。
  2. ‘Alias_Name_1’ 的作用是產生使用彙整函式 SUM() 的欄位，統計特定產品的所有命中數記錄。
  3. ‘Alias_Name_2’ 會使用 COUNT() 函式，找出每項產品由使用者產生的命中數。
  4. 最後一行顯示加入中兩個資料集共用的一般欄位 (hits.item.productSku)。

以下範例說明這項查詢：2013 年 9 月 10 日，購買前的平均使用者互動次數是多少？

2013 年 9 月 10 日，購買前的使用者互動次數
SELECT one.hits.item.productSku AS ProductSku, ( sum_of_hit_number / total_hits ) AS avg_hit_number
FROM (
 SELECT hits.item.productSku, SUM(hits.hitNumber) AS sum_of_hit_number
 FROM [GoogleStore.ga_sessions_20130728]
 WHERE hits.item.productSku IS NOT NULL
  AND totals.transactions>=1
 GROUP BY hits.item.productSku  ) AS one
JOIN (
 SELECT hits.item.productSku, COUNT( fullVisitorId ) AS total_hits
 FROM [GoogleStore.ga_sessions_20130728]
 WHERE hits.item.productSku IS NOT NULL
  AND totals.transactions>=1
 GROUP BY hits.item.productSku ) AS two
ON one.hits.item.productSku = two.hits.item.productSku;

每產品售出存貨百分比

這個查詢範例不僅使用 Analytics (分析) 資料，也使用非 Analytics (分析) 資料。結合兩個資料集，您就能開始以更細緻的劃分方式掌握使用者行為。您可以將非 Analytics (分析) 資料匯入 BigQuery，但請注意，您必須負擔增加的每月資料儲存費用。

下方是此問題的基本架構指令碼：每項產品售出的存貨百分比是多少？

每產品售出存貨百分比
SELECT AnalyticsImport.product_data_20130728.productId, ((( one.quantity_sold ) / ( AnalyticsImport.product_data_20130728.productstock + one.quantity_sold )) * 100 ) AS percentage_of_stock_sold
FROM [ ‘Imported_DataSet’ ]
JOIN (
  SELECT hits.item.productSku, SUM( hits.item.itemQuantity ) AS quantity_sold
  FROM [‘GA Dataset’ ]
  WHERE hits.item.productSku IS NOT NULL
   AND totals.transactions>=1
  GROUP BY hits.item.productSku ) AS ‘Alias_Name’
ON Imported_DataSet.’productId_field’ = Alias_Name.hits.item.productSku;
  1. 第一行會產生兩個欄位，一個欄位包含所有產品 ID；另一個欄位是數學運算式，顯示該產品 ID 的售出存貨百分比。
  2. 這項查詢需要兩個資料集，因此您必須使用 JOIN() ... ON 函式。這項指令以兩個資料集的共同欄位為基礎，結合其中的資料列。在本例中，這兩個資料集分別是 [ ‘Imported_DataSet’ ] 和 ‘Alias_Name’。
  3. [ ‘Imported_DataSet’ ] 是非 Analytics (分析) 資料。這個資料集內含顯示剩餘庫存量的指標欄位 (Imported DataSet.’stock_left_field’)，以及產品 ID 維度欄位 (Imported_DataSet.’productId_field’)。
  4. ‘Alias_Name’ 是指派給灰色子查詢傳回資料的名稱。這項子查詢使用 Analytics (分析) 資料來找出每項商品售出的項目總數量。
  5. 最後一行使用 ON 陳述式，顯示兩個資料集的共同欄位及彙整部分。

在這項查詢中，許多變數的前置字元都是採用所屬資料集的名稱 (如 Imported_DataSet.’productId_field’, Alias_Name.quantity_sold)，這樣不論是已選取的欄位或欄位所屬的資料集，全都一目瞭然。

以下範例說明這項查詢：2013 年 7 月 28 日每項產品售出的存貨百分比是多少？

2013 年 7 月 28 日每產品售出存貨百分比
SELECT AnalyticsImport.product_data_20130728.productId, ( ( ( one.quantity_sold ) / ( AnalyticsImport.product_data_20130728.productstock + one.quantity_sold ) ) * 100 ) AS percentage_of_stock_sold
FROM AnalyticsImport.product_data_20130728
JOIN (
  SELECT hits.item.productSku, SUM( hits.item.itemQuantity ) AS quantity_sold
  FROM GoogleStore.ga_sessions_20130728
  WHERE hits.item.productSku IS NOT NULL
   AND totals.transactions>=1
  GROUP BY hits.item.productSku ) AS one
ON AnalyticsImport.product_data_20130728.productId = one.hits.item.productSku
ORDER BY percentage_of_stock_sold DESC;

每項產品的獲利能力

下方是此問題的基本架構指令碼：每項產品的獲利能力為何？

產品獲利
SELECT Alias_Name.hits.item.productSku, ( Imported_DataSet.’product profit field’ * Alias_Name.quantity ) AS profit
FROM (
  SELECT Alias_Name.hits.item.productSku, Imported_DataSet.’product profit field’
  FROM [ ‘Imported Data Set’ ]
  JOIN (
    SELECT hits.item.productSku, SUM( hits.item.itemQuantity ) AS quantity
    FROM [ ‘GA Dataset Name’ ]
    WHERE hits.item.productSku IS NOT NULL
    GROUP BY hits.item.productSku ) AS ‘Alias_Name’
  ON Imported_DataSet.productId = Alias_Name.hits.item.productSku );
  1. 第一行包含計算每項產品總獲利的數學運算式。
  2. 灰色子查詢使用非 Analytics (分析) 資料，當產品售出時，系統就會收集獲利的相關資料。
  3. 紅色子查詢使用 Analytics (分析) 資料，查詢時會加入非 Analytics (分析) 資料，計算出每項產品售出的項目數量。
  4. 最後一行使用 ON 陳述式標明兩個資料集共用的欄位，本例中是指產品編號。

以下範例說明這項查詢：2013 年 7 月 28 日每項產品的獲利能力是多少？

2013 年 7 月 28 日的產品獲利
SELECT two.hits.item.productSku, ((AnalyticsImport.product_data_20130728.productprice-AnalyticsImport.product_data_20130728.productcost)*two.quantity) AS profit
FROM (
  SELECT two.hits.item.productSku, AnalyticsImport.product_data_20130728.productcost, two.quantity, AnalyticsImport.product_data_20130728.productprice
  FROM AnalyticsImport.product_data_20130728
  JOIN (
    SELECT hits.item.productSku, SUM( hits.item.itemQuantity ) AS quantity
    FROM GoogleStore.ga_sessions_20130728
    WHERE hits.item.productSku IS NOT NULL
    GROUP BY hits.item.productSku ) AS two
  ON AnalyticsImport.product_data_20130728.productId = two.hits.item.productSku );

獲利的計算方式即找出產品售出價格與生產成本的差額。這項資訊儲存在非 Google Analytics (分析) 資料集中。

每項產品的實際獲利能力 (將退款納入考量)

下方是此問題的基本架構指令碼：每項產品的實際獲利能力為何？

產品實際獲利
SELECT Alias_Name.hits.item.productSku, (gross_profit-total_refund_revenue) AS real_profit
FROM (
  SELECT Alias_Name.hits.item.productSku, ( ( Imported_DataSet.productprice - Imported_DataSet.productcost ) * Alias_Name.quantity ) AS gross_profit, ( ( Imported_DataSet.refunddeliveryprice + Imported_DataSet.productprice ) * Imported_DataSet.refundquantity ) AS total_refund_revenue
  FROM (
    SELECT Alias_Name.hits.item.productSku, Imported_DataSet.productcost, Alias_Name.quantity, Imported_DataSet.productprice, Imported_DataSet.refunddeliveryprice, Imported_DataSet.refundquantity
    FROM [ ‘Imported DataSet Name’ ] AS 'Imported_DataSet'
    JOIN (
      SELECT hits.item.productSku, SUM( hits.item.itemQuantity ) AS quantity
      FROM [‘GA Dataset Name’ ]
      WHERE hits.item.productSku IS NOT NULL
       AND totals.transactions >=1
      GROUP BY hits.item.productSku ) AS 'Alias_Name'
    ON Imported_DataSet.productId = Alias_Name.hits.item.productSku ) );
  1. 這個查詢與「每項產品的獲利能力為何？」非常相似，唯一的差異只有灰色子查詢中的非 Analytics (分析) 資料，以及第一行中數學運算式計算的實際獲利。
  2. 非 Analytics (分析) 資料集也會計算退款支出總額 (在紅色子查詢的 SELECT 陳述式)。
  3. 接下來，第一行執行的數學運算式會從您的毛利中扣除退款支出，算出實際獲利。

如要進一步瞭解查詢，請參閱「每項產品的獲利能力」一節。

以下範例說明這項查詢：2013 年 7 月 28 日，每項產品的實際獲利能力是多少？

2013 年 7 月 28 日的產品實際獲利
SELECT two.hits.item.productSku, (gross_profit-total_refund_revenue) AS real_profit
FROM (
  SELECT two.hits.item.productSku, ( ( AnalyticsImport.product_data_20130728.productprice - AnalyticsImport.product_data_20130728.productcost ) * two.quantity ) AS gross_profit, ( ( AnalyticsImport.product_data_20130728.refunddeliveryprice + AnalyticsImport.product_data_20130728.productprice ) * AnalyticsImport.product_data_20130728.refundquantity ) AS total_refund_revenue
  FROM (
    SELECT two.hits.item.productSku, Analytics.product_data_20130728.productcost, two.quantity, AnalyticsImport.product_data_20130728.productprice, AnalyticsImport.product_data_20130728.refunddeliveryprice, AnalyticsImport.product_data_20130728.refundquantity
    FROM AnalyticsImport.product_data_20130728
    JOIN (
      SELECT hits.item.productSku, SUM( hits.item.itemQuantity ) AS quantity
      FROM GoogleStore.ga_sessions_20130728
      WHERE hits.item.productSku IS NOT NULL
       AND totals.transactions >=1
      GROUP BY hits.item.productSku ) AS two
    ON AnalyticsImport.product_data_20130728.productId = two.hits.item.productSku ) );

計算實際獲利時，會一併考量扣除退款後的產品獲利能力。計算產品總退款收益的方法如下：

產品總退款收益 = (產品價格 + 產品退款運送價格) * 退款產品數量

這對您有幫助嗎？

我們應如何改進呢？

還有其他問題嗎？

嘗試以下步驟：

張貼到產品討論社群 向社群成員尋求答案
與我們聯絡 進一步說明相關資訊，我們會盡力協助您
true
learning path
選擇自己的學習路徑

歡迎使用 google.com/analytics/learn 這項新資源，瞭解如何發揮 Google Analytics (分析) 4 的最大效益。新版網站提供了影片、文章、引導式流程等多種資源，以及 Google Analytics (分析) Discord、網誌、YouTube 頻道和 GitHub 存放區的連結。

立即開始學習！

搜尋
清除搜尋內容
關閉搜尋
主選單
7600791724036731266
true
搜尋說明中心
true
true
true
true
true
69256
false
false