最佳做法

歡迎參考下列最佳做法提供的技巧，建構保護隱私權且成效卓越的查詢。

隱私權和資料準確性

針對沙箱資料建構查詢

最佳做法：僅在處於實際工作環境時查詢實際工作環境資料。

在建構查詢期間，盡可能使用沙箱資料。使用沙箱資料的工作，不會增加差異檢查篩除查詢結果的機會。此外，由於沒有隱私權檢查，沙箱查詢的執行速度會更快，因此能在查詢建構期間加速疊代。

如果您必須依據實際資料建構查詢 (例如使用對照表)，為了降低資料列重疊的機率，請為查詢的每次疊代，選擇不太可能重疊的日期範圍和其他參數。最後，請針對所需的資料範圍執行查詢。

深入研究歷來結果

最佳做法：降低近期執行的查詢之間結果組合重疊的機率。

請記住，不同查詢結果之間的變化率，可能會影響之後因隱私權檢查而略過結果的可能性。與最近傳回的結果組合非常相似的另一個結果組合，很有可能會遭到捨棄。

請改為修改查詢中的重要參數 (例如日期範圍或廣告活動 ID)，降低發生大幅重疊的可能性。

請勿查詢今天的資料

最佳做法：請勿在結束日期是今天的情況下多次執行查詢。

如果在結束日期是今天的情況下多次執行查詢，通常會導致資料列遭到篩除。在午夜過後立即針對昨日資料執行查詢，也會發生上述情況。

如果沒有必要，請勿反覆查詢相同資料

最佳做法：

  • 選取密切相關的開始和結束日期。
  • 與其查詢重疊期間，不如針對沒有交集的多個資料集執行查詢，然後在 BigQuery 中匯總結果。
  • 使用已儲存的結果，而非重新執行查詢。
  • 針對您要查詢的每個日期範圍建立臨時資料表。

廣告資料中心會限制查詢相同資料的總次數。因此，請盡量控制您存取特定資料的次數。

請勿在同一項查詢中使用不必要的匯總

最佳做法：

  • 盡量減少查詢中的匯總數量
  • 重新撰寫查詢，盡量合併匯總

廣告資料中心會將子查詢中使用的跨使用者匯總數量限制在 100 以內。因此，整體來說，我們建議在撰寫查詢時採用有針對性的分組鍵和簡單的匯總輸出更多資料列，而不要採用廣泛的分組鍵和複雜的匯總輸出更多資料欄。請避免使用下列模式：

SELECT
  COUNTIF(field_1 = a_1 AND field_2 = b_1) AS cnt_1,
  COUNTIF(field_1 = a_2 AND field_2 = b_2) AS cnt_2
FROM
  table

如果查詢是以相同欄位組合來計算事件，請使用 GROUP BY 陳述式重新撰寫查詢。

SELECT
  field_1,
  field_2,
  COUNT(1) AS cnt
FROM
  table
GROUP BY
  1, 2

在 BigQuery 中，您可以使用相同方式匯總結果。

如果查詢是從陣列建立資料欄，然後進行匯總，請重新撰寫查詢，合併這些步驟。

SELECT
  COUNTIF(a_1) AS cnt_1,
  COUNTIF(a_2) AS cnt_2
FROM
  (SELECT
     1 IN UNNEST(field) AS a_1,
     2 IN UNNEST(field) AS a_2,
   FROM
     table)

前一項查詢可重新撰寫為：

SELECT f, COUNT(1) FROM table, UNNEST(field) AS f GROUP BY 1

如果查詢在不同匯總中使用不同欄位組合，則可將這類查詢重新撰寫為多個更有針對性的查詢。

SELECT
  COUNTIF(field_1 = a_1) AS cnt_a_1,
  COUNTIF(field_1 = b_1) AS cnt_b_1,
  COUNTIF(field_2 = a_2) AS cnt_a_2,
  COUNTIF(field_2 = b_2) AS cnt_b_2,
FROM table

前一項查詢可拆分為：

SELECT
  field_1, COUNT(*) AS cnt
FROM table
GROUP BY 1

以及

SELECT
  field_2, COUNT(*) AS cnt
FROM table
GROUP BY 1

您可以將這些結果拆分為不同的查詢、在單一查詢中建立及彙整資料表，如果結構定義相容，也可使用 UNION 進行合併。

調整及瞭解彙整作業

最佳做法：使用 LEFT JOIN 取代 INNER JOIN，將點擊/轉換與曝光相互彙整。

並非所有曝光都與點擊或轉換相關聯。因此，如果您使用 INNER JOIN，以曝光為條件彙整點擊或轉換，系統就會從結果中篩除與點擊或轉換無關的曝光。

圖片：透過文氏圖表呈現多種彙整類型

在 BigQuery 中彙整部分最終結果

最佳做法：避免使用彙整匯總結果的廣告資料中心查詢。請改為撰寫 2 項不同的查詢，然後在 BigQuery 中彙整結果。

系統會從結果中篩除不符合匯總需求條件的資料列。因此，如果您的查詢將匯總程度不足的資料列與匯總程度充足的資料列相互彙整，系統就會篩除產生的資料列。此外，包含多項匯總的查詢在廣告資料中心內成效較差。

您可以在 BigQuery 中彙整多項匯總查詢 (來自廣告資料中心) 的結果。使用常見查詢計算出的結果，會採用相同的最終結構定義。

以下查詢會取得個別廣告資料中心結果 (campaign_data_123 和 campaign_data_456)，然後在 BigQuery 中進行彙整：

SELECT t1.campaign_id, t1.city, t1.X, t2.Y
FROM `campaign_data_123` AS t1
FULL JOIN `campaign_data_456` AS t2
USING (campaign_id, city)

使用篩除資料列摘要

最佳做法：在查詢中加入篩除資料列摘要。

篩除資料列摘要會匯總因隱私權檢查而篩除的資料。篩除資料列中的資料在加總後會加進綜合資料列。雖然篩除後的資料無法進一步分析，但可以歸納出從結果中篩除的資料量。

計入 User ID 為 0 的情況

最佳做法：計入結果中 User ID 為 0 的情況。

使用者的 ID 設為 0 的可能原因有幾個，包括：停用廣告個人化功能、法規原因等。因此，多位使用者的資料對應的 user_id 為 0。

如要瞭解資料總數 (例如曝光總數或點擊總數)，建議您納入這類事件。不過，這些資料對於產生客戶洞察不會有幫助，如果您要進行這類分析，應該要篩除這些資料。

您可以在查詢中加入 WHERE user_id != "0"，將這些資料從結果中排除。

成效

避免重新匯總

最佳做法：避免跨使用者進行多層匯總。

如果查詢合併經過匯總的結果 (例如查詢包含多個 GROUP BY，或巢狀匯總)，就需要更多資源來處理。

在一般情況下，可以對包含多層匯總的查詢進行細分，以改善成效。您在處理時應試著將資料列保留在事件或使用者層級，然後再透過單一匯總的方式進行合併。

請避免下列模式：

SELECT SUM(count)
FROM
  (SELECT campaign_id, COUNT(0) AS count FROM ... GROUP BY 1)

使用多層匯總的查詢應改寫為使用單層匯總。

(SELECT ... GROUP BY ... )
JOIN USING (...)
(SELECT ... GROUP BY ... )

如果查詢易於細分，則應進行細分。您可以在 BigQuery 中彙整結果。

針對 BigQuery 進行最佳化

一般來說，查詢時需要完成的工作越少，成效就會越好。評估查詢成效時，所需的工作數量取決於下列因素：

如果執行查詢的效能不符合服務水準協議，或是因資源用盡或逾時而發生錯誤，請考慮以下做法：

  • 使用先前查詢的結果，而非重新計算。舉例來說，如要取得每週總計，可以計算 BigQuery 中 7 天匯總查詢的總和。
  • 將查詢分解成符合邏輯的子查詢 (例如將多項彙整拆分為多項查詢)，或是限制要處理的資料集。您可以將個別工作的結果合併至 BigQuery 中的單一資料集。雖然這或許有助於解決資源耗盡的問題，但可能會拖慢查詢速度。
  • 如果您在 BigQuery 中遇到資源超出上限的錯誤，不妨使用臨時資料表將查詢拆分為多項 BigQuery 查詢。
  • 減少在單一查詢中參照的資料表數量，如果數量太多，就會占用大量記憶體，甚至導致查詢失敗。
  • 重新編寫查詢，減少要彙整的使用者資料表數量。
  • 重新編寫查詢，避免重複彙整相同的資料表。

查詢建議工具

如果 SQL 有效但可能觸發過度篩選，查詢建議工具會在查詢建構過程中顯示可行的建議，避免產生不適當的結果。

觸發條件含括以下模式：

如何使用查詢建議工具：