Campaign Manager 移轉作業

Campaign Manager 的 BigQuery 資料移轉服務可讓您針對 Campaign Manager 的報表資料自動安排及管理週期性載入工作。

支援的報表

Campaign Manager 專用的 BigQuery 資料移轉服務 (過去稱為 DoubleClick Campaign Manager) 目前支援以下報表選項:

如需瞭解 Campaign Manager 報表如何轉換成 BigQuery 表格及資料檢視,請參閱 Campaign Manager 報表轉換

報表選項 支援
支援的 API 版本 2017 年 6 月 26 日
排程

每 8 小時 (根據建立時間)。

無法設定

更新期

過去 2 天

無法設定

補充作業時間上限

過去 60 天

Campaign Manager 最多會保留資料移轉檔案 60 天。超過 60 天後,Campaign Manager 就會刪除這些檔案。

事前準備

在您建立 Campaign Manager 移轉作業之前,請先完成下列事項:

  • 確認您已完成啟用 BigQuery 資料移轉服務的所有必要操作。
  • 建立 BigQuery 資料集以儲存 Campaign Manager 資料。
  • 確認貴機構可存取 Campaign Manager 資料移轉 2.0 版 (Campaign Manager DTv2) 檔案。 這些檔案會由 Campaign Manager 小組傳送至 Cloud Storage 值區。若要存取 Campaign Manager DTv2 檔案,下一步取決於您是否與 Campaign Manager 有直接合約關係。無論有無合約關係,您都可能需要支付額外費用。

    若您與 Campaign Manager 有合約關係,請洽詢 Campaign Manager 支援小組,讓對方協助您建立 Campaign Manager DTv2 檔案。

    若您與 Campaign Manager 沒有合約關係,您的代理商或 Campaign Manager 經銷商可能擁有 Campaign Manager DTv2 檔案的存取權。請與您的代理商或經銷商聯絡,以便取得這些檔案的存取權。

    完成上述步驟後,您會獲得類似下列字串的 Cloud Storage 值區:

    dcdt_-dcm_account123456

    Google Cloud 小組無法代表您產生或授予 Campaign Manager DTv2 檔案的存取權。請與 Campaign Manager 支援小組、您的代理商或 Campaign Manager 經銷商聯絡,詢問 Campaign Manager DTv2 檔案存取權事宜。

  • 確認建立移轉作業的人員具有以下必要權限:
    • Campaign Manager:可讀取儲存在 Cloud Storage 中 Campaign Manager DTv2 檔案的權限。存取權是由 Cloud Storage 值區來源實體所管理。
    • BigQuery:建立移轉作業的 bigquery.transfers.update 權限,以及目標資料集的 bigquery.datasets.update 權限。bigquery.admin 預先定義的專案層級身分與存取權管理角色包括 bigquery.transfers.updatebigquery.datasets.update 權限。若要進一步瞭解 BigQuery 中的身分與存取權管理 角色,請參閱存取權控管
  • 如果您想要使用傳統 BigQuery 網頁版 UI 建立移轉作業,請允許來自 bigquery.cloud.google.com 的彈出式視窗,以便查看權限視窗。為使移轉作業順利進行,您必須授權 BigQuery 資料移轉服務管理您的 Campaign Manager 廣告活動。
  • 移轉執行通知目前仍為 Alpha 版。若要設定移轉通知,您必須擁有 Cloud Pub/Sub 的 pubsub.topics.setIamPolicy 權限。如果您只是要設定電子郵件通知,就不需要 Cloud Pub/Sub 權限。詳情請參閱 BigQuery 資料移轉服務執行通知

設定 Campaign Manager 移轉作業

若要設定 Campaign Manager 移轉作業,您需要下列項目:

  • Cloud Storage 值區:存放 Campaign Manager DTv2 檔案的 Cloud Storage 值區 URI,如事前準備一節所述。值區名稱應如下所示:

      dcdt_-dcm_account123456
    
  • Campaign Manager ID:您的 Campaign Manager Network、廣告客戶或 Floodlight ID。Network ID 在階層中是父項。

尋找您的 Campaign Manager ID

如要擷取您的 Campaign Manager ID,您可以使用 Cloud Storage 主控台檢查 Google Ad Manager 資料移轉 Cloud Storage 值區中的檔案。Campaign Manager ID 可用來在您提供的 Cloud Storage 值區中比對檔案,且內嵌在檔案名稱 (而不是 Cloud Storage 值區名稱) 中。

例如:

  • 在名稱為 dcm_account123456_activity_* 的檔案中,ID 為 123456
  • 在名稱為 dcm_floodlight7890_activity_* 的檔案中,ID 為 7890
  • 在名稱為 dcm_advertiser567_activity_* 的檔案中,ID 為 567

(選用) 尋找檔案名稱的前置字串

在極少數的情況下,在您 Cloud Storage 值區中的檔案可能包含由 Google Marketing Platform 服務小組為您設定的非標準自訂檔案名稱。

例如:

  • 在名稱為 dcm_account123456custom_activity_* 的檔案中,前置字串為 dcm_account123456custom,也就是「_activity」之前的所有內容。

如果您需要協助,請聯絡 Campaign Manager 支援小組

若要為 Campaign Manager 建立 BigQuery 資料移轉作業,請按照以下步驟操作:

傳統版 UI

  1. 前往 BigQuery 網路 UI。

    前往 BigQuery 網頁版 UI

  2. 按一下 [Transfers] (移轉作業)

  3. 按一下 [Add Transfer] (新增移轉作業)。

  4. 在「New Transfer」(新移轉作業) 頁面上:

    • 針對「Source」(來源),選擇 [Campaign Manager (舊稱為 DCM)]
    • 在「Display name」(顯示名稱) 中,輸入移轉作業的名稱,例如 My Transfer。移轉作業名稱可以是任何容易辨識的值,方便您日後在必要時進行修改。
    • 針對「Destination dataset」(目的地資料集) 選擇適當的資料集。
    • 在「Cloud Storage Bucket」(Cloud Storage 值區) 部分,輸入儲存資料移轉 2.0 版檔案的 Cloud Storage 值區名稱。輸入值區名稱時,請不要加入 gs://
    • 針對「DoubleClick ID」,輸入適當的 Campaign Manager ID。
    • (選用) 若您的檔案中包含類似這些範例的標準名稱,請將 [File name prefix] (檔案名稱前置字串) 欄位留空。如果您的 Cloud Storage 值區中的檔案有自訂的檔案名稱 (類似這個範例),請填妥 [File name prefix] (檔案名稱前置字串) 欄位。

      頻道轉換

    • (選用) 展開「Advanced」(進階) 區段,然後設定移轉作業的執行通知。移轉執行通知目前仍為 Alpha 版。

      • 在「Cloud Pub/Sub topic」(Cloud Pub/Sub 主題) 中輸入您的主題名稱,例如 projects/myproject/topics/mytopic
      • 勾選「Send email notifications」(傳送電子郵件通知),讓系統在移轉執行失敗時,寄送電子郵件通知。
      • 設定移轉作業時,請勿勾選 [Disabled] (已停用)。如需停用現有的移轉作業,請參閱使用移轉一文。

        Cloud Pub/Sub 主題

  5. 按一下 [Add] (新增)

  6. 出現提示時,請按一下 [Allow] (允許) 以授予 BigQuery 資料移轉服務權限,以存取您的 Campaign Manager 報表資料,並管理 BigQuery 中的資料。您必須允許來自 bigquery.cloud.google.com 的彈出式視窗,才能查看權限視窗。

    允許移轉

指令列

輸入 bq mk 指令並提供轉換建立標記 — --transfer_config。以下標記亦需提供:

  • --data_source
  • --target_dataset
  • --display_name
  • --params

    bq mk --transfer_config --project_id=[PROJECT_ID] --target_dataset=[DATASET] --display_name=[NAME] --params='[PARAMETERS]' --data_source=[DATA_SOURCE]
    

其中:

  • --project_id 是您的專案 ID。
  • --target_dataset 是移轉設定的目標資料集。
  • --display_name 是移轉設定的顯示名稱。移轉名稱可以是任何容易識別並且方便您後續修改的值。
  • --params 含有已建立移轉設定的 JSON 格式參數。例如:--params='{"param":"param_value"}'。對於 Campaign Manager 而言,您必須提供 bucket 以及 network_id 參數。bucket 是包含有 Campaign Manager DTv2 檔案的 Cloud Storage 值區。network_id 是您的聯播網、Floodlight 或廣告 ID。
  • --data_source 是資料來源:dcm_dt (Campaign Manager)。

您也可以提供 --project_id 標記,用以指定特定的專案。如未指定 --project_id,系統會使用預設的專案。

舉例來說,下列指令會使用 Campaign Manager ID 123456、Cloud Storage 值區 dcdt_-dcm_account123456 和目標資料集 mydataset,建立名為 My Transfer 的 Campaign Manager 移轉作業。file_name_prefix 是選用的參數,僅適用於罕見的自訂檔案名稱。

移轉作業是透過預設專案建立:

bq mk --transfer_config --target_dataset=mydataset --display_name='My Transfer' --params='{"bucket": "dcdt_-dcm_account123456","network_id": "123456","file_name_prefix":"YYY"}' --data_source=dcm_dt

執行指令後,您會收到如下的訊息:

[URL omitted] Please copy and paste the above URL into your web browser and follow the instructions to retrieve an authentication code.

請按照指示進行操作,並在指令列中貼上驗證碼。

API

使用 projects.locations.transferConfigs.create 方法並提供 TransferConfig 資源的執行個體。

排解 Campaign Manager 移轉設定問題

若您無法順利設定移轉作業,請參閱排解 BigQuery 資料移轉服務移轉設定問題說明中的 Campaign Manager 移轉問題

查詢資料

資料移轉至 BigQuery 時,系統會將資料寫入擷取時間分區資料表。詳情請參閱分區資料表簡介一文。

如果您要直接查詢資料表,而不要使用自動產生的資料檢視,您必須在查詢中使用 _PARTITIONTIME 虛擬資料欄。詳情請參閱查詢分區資料表

Campaign Manager 範例查詢

您可以使用下列 Campaign Manager 查詢範例來分析已移轉的資料。您還可以在 Cloud DatalabGoogle 數據分析等視覺化工具中使用查詢。這些查詢可協助您開始透過 BigQuery 查詢您的 Campaign Manager 資料。如果您對於這些報表的功能有其他問題,請洽詢您的 Campaign Manager 技術代表。

這些範例使用 BigQuery 的標準 SQL 支援。使用 #standardSQL 標記可讓 BigQuery 知道您要使用標準 SQL。如要進一步瞭解 #standardSQL 前置字串,請參閱設定查詢前置字串

在下列各項查詢中,請將 [DATASET] 改成您的資料集名稱,並將 [DOUBLECLICK_ID] 替換成您的 Campaign Manager ID。

近期廣告活動

下列範例查詢會擷取最近的廣告活動。

傳統版 UI

#standardSQL
SELECT Campaign, Campaign_ID FROM `[DATASET].match_table_campaigns_[DOUBLECLICK_ID]`
WHERE _DATA_DATE = _LATEST_DATE

指令列

bq query --use_legacy_sql=false '
SELECT Campaign, Campaign_ID FROM `[DATASET].match_table_campaigns_[DOUBLECLICK_ID]`
WHERE _DATA_DATE = _LATEST_DATE'

各個廣告活動的曝光次數和不重複使用者人數

以下範例查詢會分析過去 30 天內各個廣告活動的曝光次數和不重複使用者人數。

傳統版 UI

#standardSQL
# START_DATE = DATE_ADD(CURRENT_DATE(), INTERVAL -31 DAY)
# END_DATE = DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY)
SELECT Campaign_ID, DATA_DATE AS Date, count(*) AS count, count(distinct User_ID) AS du
FROM `[DATASET].impression[DOUBLECLICK_ID]` WHERE
  _DATA_DATE BETWEEN [START_DATE] AND [END_DATE]
GROUP BY Campaign_ID, Date

指令列

# START_DATE = DATE_ADD(CURRENT_DATE(), INTERVAL -31 DAY)
# END_DATE = DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY)
bq query --use_legacy_sql=false '
SELECT Campaign_ID, _DATA_DATE AS Date, count(*) AS count, count(distinct User_ID) AS du
FROM `[DATASET].impression_[DOUBLECLICK_ID]` WHERE
  _DATA_DATE BETWEEN [START_DATE] AND [END_DATE]
GROUP BY Campaign_ID, Date'

將近期廣告活動依廣告活動和日期排序

以下範例查詢會分析過去 30 天內的廣告活動,並依廣告活動和日期排序。

傳統版 UI

#standardSQL
# START_DATE = DATE_ADD(CURRENT_DATE(), INTERVAL -31 DAY)
# END_DATE = DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY)
SELECT Campaign, Campaign_ID, Date FROM (
SELECT Campaign, Campaign_ID FROM `[DATASET].match_table_campaigns_[DOUBLECLICK_ID]`
  WHERE _DATA_DATE = _LATEST_DATE
), (
SELECT date AS Date
  FROM `bigquery-public-data.common_us.date_greg`
  WHERE Date BETWEEN [START_DATE] AND [END_DATE]
)
ORDER BY
  Campaign_ID, Date

指令列

# START_DATE = DATE_ADD(CURRENT_DATE(), INTERVAL -31 DAY)
# END_DATE = DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY)
bq query --use_legacy_sql=false '
SELECT Campaign, Campaign_ID, Date FROM (
SELECT Campaign, Campaign_ID FROM `[DATASET].match_table_campaigns_[DOUBLECLICK_ID]`
  WHERE _DATA_DATE = _LATEST_DATE
), (
SELECT date AS Date
  FROM `bigquery-public-data.common_us.date_greg`
  WHERE Date BETWEEN [START_DATE] AND [END_DATE]
)
ORDER BY
  Campaign_ID, Date'

特定日期範圍內各個廣告活動的曝光次數和不重複使用者人數

以下範例查詢會分析從 [START_DATE] 到 [END_DATE] 之間各個廣告活動的曝光次數和不重複使用者人數。

傳統版 UI

#standardSQL
# START_DATE = DATE_ADD(CURRENT_DATE(), INTERVAL -31 DAY)
# END_DATE = DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY)
SELECT
  base.*,
  imp.count AS imp_count,
  imp.du AS imp_du
FROM (
  SELECT
    *
  FROM (
    SELECT
      Campaign,
      Campaign_ID
    FROM
      `[DATASET].match_table_campaigns_[DOUBLECLICK_ID]`
    WHERE
      DATA_DATE = \_LATEST_DATE ),
    (
    SELECT
      date AS Date
    FROM
      `bigquery-public-data.common_us.date_greg`
    WHERE
      Date BETWEEN [START_DATE]
      AND [END_DATE] ) ) AS base
LEFT JOIN (
  SELECT
    Campaign_ID,
    _DATA_DATE AS Date,
    COUNT() AS count,
    COUNT(DISTINCT User_ID) AS du
  FROM
    `[DATASET].impression[DOUBLECLICK_ID]`
  WHERE
    _DATA_DATE BETWEEN [START_DATE]
    AND [END_DATE]
  GROUP BY
    Campaign_ID,
    Date ) AS imp
ON
  base.Campaign_ID = imp.Campaign_ID
  AND base.Date = imp.Date
WHERE
  base.Campaign_ID = imp.Campaign_ID
  AND base.Date = imp.Date
ORDER BY
  base.Campaign_ID,
  base.Date

指令列

# START_DATE = DATE_ADD(CURRENT_DATE(), INTERVAL -31 DAY)
# END_DATE = DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY)
bq query --use_legacy_sql=false '
SELECT
  base.*,
  imp.count AS imp_count,
  imp.du AS imp_du
FROM (
  SELECT
    *
  FROM (
    SELECT
      Campaign,
      Campaign_ID
    FROM
      `[DATASET].match_table_campaigns_[DOUBLECLICK_ID]`
    WHERE
      DATA_DATE = _LATEST_DATE ),
    (
    SELECT
      date AS Date
    FROM
      `bigquery-public-data.common_us.date_greg`
    WHERE
      Date BETWEEN [START_DATE]
      AND [END_DATE] ) ) AS base
LEFT JOIN (
  SELECT
    Campaign_ID,
    _DATA_DATE AS Date,
    COUNT() AS count,
    COUNT(DISTINCT User_ID) AS du
  FROM
    `[DATASET].impression[DOUBLECLICK_ID]`
  WHERE
    _DATA_DATE BETWEEN [START_DATE]
    AND [END_DATE]
  GROUP BY
    Campaign_ID,
    Date ) AS imp
ON
  base.Campaign_ID = imp.Campaign_ID
  AND base.Date = imp.Date
WHERE
  base.Campaign_ID = imp.Campaign_ID
  AND base.Date = imp.Date
ORDER BY
  base.Campaign_ID,
  base.Date'

各個廣告活動的曝光次數、點擊次數、活動數和不重複使用者人數

以下範例查詢會分析過去 30 天內,各個廣告活動的曝光次數、點擊次數、活動數和不重複使用者人數。

傳統版 UI

#standardSQL
# START_DATE = DATE_ADD(CURRENT_DATE(), INTERVAL -31 DAY)
# END_DATE = DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY)
SELECT
  base.*,
  imp.count AS imp_count,
  imp.du AS imp_du,
  click.count AS click_count,
  click.du AS click_du,
  activity.count AS activity_count,
  activity.du AS activity_du
FROM (
  SELECT
    *
  FROM (
    SELECT
      Campaign,
      Campaign_ID
    FROM
      `[DATASET].match_table_campaigns_[DOUBLECLICK_ID]`
    WHERE
      DATA_DATE = _LATEST_DATE ),
    (
    SELECT
      date AS Date
    FROM
      `bigquery-public-data.common_us.date_greg`
    WHERE
      Date BETWEEN DATE_ADD(CURRENT_DATE(), INTERVAL -31 DAY)
      AND DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY) ) ) AS base
LEFT JOIN (
  SELECT
    Campaign_ID,
    _DATA_DATE AS Date,
    COUNT() AS count,
    COUNT(DISTINCT User_ID) AS du
  FROM
    `[DATASET].impression[DOUBLECLICK_ID]`
  WHERE
    DATA_DATE BETWEEN DATE_ADD(CURRENT_DATE(), INTERVAL -31 DAY)
    AND DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY)
  GROUP BY
    Campaign_ID,
    Date ) AS imp
ON
  base.Campaign_ID = imp.Campaign_ID
  AND base.Date = imp.Date
LEFT JOIN (
  SELECT
    Campaign_ID,
    DATA_DATE AS Date,
    COUNT() AS count,
    COUNT(DISTINCT User_ID) AS du
  FROM
    `[DATASET].click[DOUBLECLICK_ID]`
  WHERE
    _DATA_DATE BETWEEN DATE_ADD(CURRENT_DATE(), INTERVAL -31 DAY)
    AND DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY)
  GROUP BY
    Campaign_ID,
    Date ) AS click
ON
  base.Campaign_ID = click.Campaign_ID
  AND base.Date = click.Date
LEFT JOIN (
  SELECT
    Campaign_ID,
    _DATA_DATE AS Date,
    COUNT() AS count,
    COUNT(DISTINCT User_ID) AS du
  FROM
    `[DATASET].activity[DOUBLECLICK_ID]`
  WHERE
    _DATA_DATE BETWEEN DATE_ADD(CURRENT_DATE(), INTERVAL -31 DAY)
    AND DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY)
  GROUP BY
    Campaign_ID,
    Date ) AS activity
ON
  base.Campaign_ID = activity.Campaign_ID
  AND base.Date = activity.Date
WHERE
  base.Campaign_ID IN [CAMPAIGN_LIST]
  AND (base.Date = imp.Date
    OR base.Date = click.Date
    OR base.Date = activity.Date)
ORDER BY
  base.Campaign_ID,
  base.Date

指令列

# START_DATE = DATE_ADD(CURRENT_DATE(), INTERVAL -31 DAY)
# END_DATE = DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY)
bq query --use_legacy_sql=false '
SELECT
  base.*,
  imp.count AS imp_count,
  imp.du AS imp_du,
  click.count AS click_count,
  click.du AS click_du,
  activity.count AS activity_count,
  activity.du AS activity_du
FROM (
  SELECT
    *
  FROM (
    SELECT
      Campaign,
      Campaign_ID
    FROM
      `[DATASET].match_table_campaigns_[DOUBLECLICK_ID]`
    WHERE
      DATA_DATE = _LATEST_DATE ),
    (
    SELECT
      date AS Date
    FROM
      `bigquery-public-data.common_us.date_greg`
    WHERE
      Date BETWEEN DATE_ADD(CURRENT_DATE(), INTERVAL -31 DAY)
      AND DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY) ) ) AS base
LEFT JOIN (
  SELECT
    Campaign_ID,
    _DATA_DATE AS Date,
    COUNT() AS count,
    COUNT(DISTINCT User_ID) AS du
  FROM
    `[DATASET].impression[DOUBLECLICK_ID]`
  WHERE
    DATA_DATE BETWEEN DATE_ADD(CURRENT_DATE(), INTERVAL -31 DAY)
    AND DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY)
  GROUP BY
    Campaign_ID,
    Date ) AS imp
ON
  base.Campaign_ID = imp.Campaign_ID
  AND base.Date = imp.Date
LEFT JOIN (
  SELECT
    Campaign_ID,
    DATA_DATE AS Date,
    COUNT() AS count,
    COUNT(DISTINCT User_ID) AS du
  FROM
    `[DATASET].click[DOUBLECLICK_ID]`
  WHERE
    _DATA_DATE BETWEEN DATE_ADD(CURRENT_DATE(), INTERVAL -31 DAY)
    AND DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY)
  GROUP BY
    Campaign_ID,
    Date ) AS click
ON
  base.Campaign_ID = click.Campaign_ID
  AND base.Date = click.Date
LEFT JOIN (
  SELECT
    Campaign_ID,
    _DATA_DATE AS Date,
    COUNT() AS count,
    COUNT(DISTINCT User_ID) AS du
  FROM
    `[DATASET].activity[DOUBLECLICK_ID]`
  WHERE
    _DATA_DATE BETWEEN DATE_ADD(CURRENT_DATE(), INTERVAL -31 DAY)
    AND DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY)
  GROUP BY
    Campaign_ID,
    Date ) AS activity
ON
  base.Campaign_ID = activity.Campaign_ID
  AND base.Date = activity.Date
WHERE
  base.Campaign_ID IN [CAMPAIGN_LIST]
  AND (base.Date = imp.Date
    OR base.Date = click.Date
    OR base.Date = activity.Date)
ORDER BY
  base.Campaign_ID,
  base.Date'

廣告活動

以下查詢範例會分析過去 30 天內的廣告活動。在這個查詢中,請將 [CAMPAIGN_LIST] 改為逗號分隔的清單,其中包含查詢範圍內所有相關的 Campaign Manager 廣告活動。

傳統版 UI

#standardSQL
# START_DATE = DATE_ADD(CURRENT_DATE(), INTERVAL -31 DAY)
# END_DATE = DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY)
SELECT
  base.*,
  activity.count AS activity_count,
  activity.du AS activity_du
FROM (
  SELECT
    *
  FROM (
    SELECT
      Campaign,
      Campaign_ID
    FROM
      `[DATASET].match_table_campaigns_[DOUBLECLICK_ID]`
    WHERE
      DATA_DATE = _LATEST_DATE ),
    (
    SELECT
      mt_at.Activity_Group,
      mt_ac.Activity,
      mt_ac.Activity_Type,
      mt_ac.Activity_Sub_Type,
      mt_ac.Activity_ID,
      mt_ac.Activity_Group_ID
    FROM
      `[DATASET].match_table_activity_cats[DOUBLECLICK_ID]` AS mt_ac
    JOIN (
      SELECT
        Activity_Group,
        Activity_Group_ID
      FROM
        `[DATASET].match_table_activity_types_[DOUBLECLICK_ID]`
      WHERE
        _DATA_DATE = _LATEST_DATE ) AS mt_at
    ON
      mt_at.Activity_Group_ID = mt_ac.Activity_Group_ID
    WHERE
      _DATA_DATE = _LATEST_DATE ),
    (
    SELECT
      date AS Date
    FROM
      `bigquery-public-data.common_us.date_greg`
    WHERE
      Date BETWEEN [START_DATE]
      AND [END_DATE] ) ) AS base
LEFT JOIN (
  SELECT
    Campaign_ID,
    Activity_ID,
    _DATA_DATE AS Date,
    COUNT() AS count,
    COUNT(DISTINCT User_ID) AS du
  FROM
    `[DATASET].activity_[DOUBLECLICK_ID]`
  WHERE
    _DATA_DATE BETWEEN DATE_ADD(CURRENT_DATE(), INTERVAL -31 DAY)
    AND DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY)
  GROUP BY
    Campaign_ID,
    Activity_ID,
    Date ) AS activity
ON
  base.Campaign_ID = activity.Campaign_ID
  AND base.Activity_ID = activity.Activity_ID
  AND base.Date = activity.Date
WHERE
  base.Campaign_ID IN [CAMPAIGN_LIST]
  AND base.Activity_ID = activity.Activity_ID
ORDER BY
  base.Campaign_ID,
  base.Activity_Group_ID,
  base.Activity_ID,
  base.Date

指令列

# START_DATE = DATE_ADD(CURRENT_DATE(), INTERVAL -31 DAY)
# END_DATE = DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY)
bq query --use_legacy_sql=false '
SELECT
  base.*,
  activity.count AS activity_count,
  activity.du AS activity_du
FROM (
  SELECT
    *
  FROM (
    SELECT
      Campaign,
      Campaign_ID
    FROM
      `[DATASET].match_table_campaigns_[DOUBLECLICK_ID]`
    WHERE
      DATA_DATE = _LATEST_DATE ),
    (
    SELECT
      mt_at.Activity_Group,
      mt_ac.Activity,
      mt_ac.Activity_Type,
      mt_ac.Activity_Sub_Type,
      mt_ac.Activity_ID,
      mt_ac.Activity_Group_ID
    FROM
      `[DATASET].match_table_activity_cats[DOUBLECLICK_ID]` AS mt_ac
    JOIN (
      SELECT
        Activity_Group,
        Activity_Group_ID
      FROM
        `[DATASET].match_table_activity_types_[DOUBLECLICK_ID]`
      WHERE
        _DATA_DATE = _LATEST_DATE ) AS mt_at
    ON
      mt_at.Activity_Group_ID = mt_ac.Activity_Group_ID
    WHERE
      _DATA_DATE = _LATEST_DATE ),
    (
    SELECT
      date AS Date
    FROM
      `bigquery-public-data.common_us.date_greg`
    WHERE
      Date BETWEEN [START_DATE]
      AND [END_DATE] ) ) AS base
LEFT JOIN (
  SELECT
    Campaign_ID,
    Activity_ID,
    _DATA_DATE AS Date,
    COUNT() AS count,
    COUNT(DISTINCT User_ID) AS du
  FROM
    `[DATASET].activity_[DOUBLECLICK_ID]`
  WHERE
    _DATA_DATE BETWEEN DATE_ADD(CURRENT_DATE(), INTERVAL -31 DAY)
    AND DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY)
  GROUP BY
    Campaign_ID,
    Activity_ID,
    Date ) AS activity
ON
  base.Campaign_ID = activity.Campaign_ID
  AND base.Activity_ID = activity.Activity_ID
  AND base.Date = activity.Date
WHERE
  base.Campaign_ID IN [CAMPAIGN_LIST]
  AND base.Activity_ID = activity.Activity_ID
ORDER BY
  base.Campaign_ID,
  base.Activity_Group_ID,
  base.Activity_ID,
  base.Date'
本頁內容對您是否有任何幫助?請提供意見:

傳送您對下列選項的寶貴意見...

這個網頁