BigQuery 教戰手冊

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

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

本文內容:

查詢最佳化

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

進一步瞭解計費方式。

只選取必要項目

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

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

不適用的格式形式:使用萬用字元運算子
SELECT *
FROM [表格名稱];

 

適用的格式:使用欄位名稱來避免額外處理
SELECT field1, field2
FROM [表格名稱];

允許快取

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

使用中繼表格進行常用子查詢

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

使用中繼表格
SELECT field1, field2
FROM [資料集名稱.表格名稱];

為查詢偵錯

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 hitsWITHIN 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 [‘資料集名稱’ ]
WHERE fullVisitorId IN (
  SELECT fullVisitorId
  FROM [‘資料集名稱’ ]
  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 時,頂層查詢 (呈綠色) 中的規則 (WHEREAND 陳述式) 就不會採用這些值。

以下範例說明這項查詢:購買了 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 [‘Google Analytics (分析) 資料集名稱’ ]
 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 [‘Google Analytics (分析) 資料集名稱’ ]
 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 [‘Google Analytics (分析) 資料集’ ]
  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. 第一行會產生兩個欄位:一個欄位包含所有產品編號;另一個欄位是數學運算式,會顯示該產品編號的售出存貨百分比。
  2. 這項查詢需要兩個資料集,因此您必須使用 JOIN() ... ON 函式。這項指令根據兩個資料集的一般欄位,結合了兩個資料集的資料列。在本例中,兩個資料集分別是 [ ‘Imported_DataSet’ ]‘Alias_Name’
  3. [ ‘Imported_DataSet’ ] 是非 Analytics (分析) 資料。這個資料集包含顯示剩餘庫存量的指標欄位 (Imported DataSet.’stock_left_field’),以及產品編號維度欄位 (Imported_DataSet.’productId_field’)。
  4. ‘Alias_Name’ 是為灰色子查詢傳回的資料所指定的名稱。 is the name assigned to the data returned by the gray subquery. 這項子查詢使用 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 [ ‘匯入的資料集’ ]
  JOIN (
    SELECT hits.item.productSku, SUM( hits.item.itemQuantity ) AS quantity
    FROM [ ‘Google Analytics (分析) 資料集名稱’ ]
    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 [ ‘匯入的資料集名稱’ ] AS 'Imported_DataSet'
    JOIN (
      
SELECT hits.item.productSku, SUM( hits.item.itemQuantity ) AS quantity
      FROM [‘Google Analytics (分析) 資料集名稱’ ]
      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 )
);

計算實際獲利時,系統會將產品扣除退款後的獲利能力納入考量。計算產品總退款收益的方法如下:

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

這對您有幫助嗎?
我們應如何改進呢?