Campaign Manager 转移作业

借助适用于 Campaign Manager 的 BigQuery Data Transfer Service,您可以自动安排和管理 Campaign Manager 报告数据的定期加载作业。

支持的报告

适用于 Campaign Manager(以前称为 DoubleClick Campaign Manager)的 BigQuery Data Transfer Service 目前支持以下报告选项:

如需了解 Campaign Manager 报告如何转换为 BigQuery 表和视图,请参阅 Campaign Manager 报告转换

报告选项 支持
支持的 API 版本 2017 年 6 月 26 日
时间安排

每 8 小时(基于创建时间)。

无法配置

刷新时段

过去 2 天

无法配置

最大回填时长

过去 60 天

Campaign Manager 最多可将数据传输文件保留 60 天。超过 60 天的文件会由 Campaign Manager 删除。

准备工作

在创建 Campaign Manager 传输之前,请先完成以下操作:

  • 确认您已完成启用 BigQuery Data Transfer Service 所需的全部操作。
  • 创建 BigQuery 数据集,以用于存储 Campaign Manager 数据。
  • 确保您的组织有权访问 Campaign Manager Data Transfer v2 (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 预定义的项目级别 IAM 角色包括 bigquery.transfers.updatebigquery.datasets.update 权限。如需详细了解 BigQuery 中的 IAM 角色,请参阅访问权限控制
  • 如果您打算使用经典版 BigQuery 网页界面创建转移,请允许显示来自 bigquery.cloud.google.com 的弹出式窗口,这样才能查看权限窗口。您必须向 BigQuery Data Transfer Service 授予管理 Campaign Manager 广告系列的权限才能成功转移。
  • 转移作业运行通知目前为 Alpha 版。如果您打算设置传输通知,那么您必须拥有 Cloud Pub/Sub 的 pubsub.topics.setIamPolicy 权限。如果您只是设置电子邮件通知,则无需 Cloud Pub/Sub 权限。如需了解详情,请参阅 BigQuery Data Transfer Service 运行通知

设置 Campaign Manager 转移

设置 Campaign Manager 转移需要提供以下信息:

  • Cloud Storage 存储分区:Campaign Manager DTv2 的文件的 Cloud Storage 存储分区 URI(如准备工作中所述)。存储分区名称应如下所示:

      dcdt_-dcm_account123456
    
  • Campaign Manager ID:您的 Campaign Manager 网络、广告客户或 Floodlight ID。网络 ID 在层次结构中为父级。

查找 Campaign Manager ID

要检索 Campaign Manager ID,您可以使用 Cloud Storage 控制台检查 Google Ad Manager Data Transfer Cloud Storage 存储分区中的文件。Campaign Manager ID 可用于在提供的 Cloud Storage 存储分区中匹配文件。该 ID 嵌入在文件名中,而不是 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 数据转移,请执行以下操作:

经典版界面

  1. 转到 BigQuery 网页界面。

    转到 BigQuery 网页界面

  2. 点击 Transfers

  3. 点击 Add Transfer

  4. 新传输 (New Transfer) 页面上,执行以下操作:

    • 来源 (Source) 部分中,选择 Campaign Manager(以前称为 DCM)[Campaign Manager (formerly DCM)]。
    • Display name 部分,输入转移的名称,例如 My Transfer。传输名称可以是任何容易辨识的值,方便以后需要时对传输进行修改。
    • Destination dataset 部分,选择相应的数据集。
    • Cloud Storage bucket 部分,输入存储数据转移 V2.0 文件的 Cloud Storage 存储分区的名称。当您输入存储分区名称时,请不要包含 gs://
    • DoubleClick ID 部分中,输入相应的 Campaign Manager ID。
    • (可选)如果文件具有类似于这些示例的标准名称,请将 File name prefix 字段留空。只有当 Cloud Storage 存储分区中的文件具有类似于此示例的自定义文件名时,才需要填写 File name prefix

      频道传输

    • (可选)展开 Advanced 部分,并为转移配置运行通知。转移作业运行通知目前为 Alpha 版

      • Cloud Pub/Sub topic 部分,输入主题名称,例如 projects/myproject/topics/mytopic
      • 勾选 Send email notifications,让系统在转移作业运行失败时发送电子邮件通知。
      • 设置传输时请勿勾选 Disabled。请参阅处理传输作业以停用现有传输。

        Cloud Pub/Sub 主题

  5. 点击 Add

  6. 出现提示时,点击 Allow 以向 BigQuery Data Transfer Service 授予相应权限,允许其访问您的 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"}'。对于 Camapaign Manager,您必须提供 bucketnetwork_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 Data Transfer Service 转移设置问题中的 Campaign Manager 转移问题

查询数据

当数据传输到 BigQuery 时,数据会写入提取时间分区表。如需了解详情,请参阅分区表简介

如果您要直接查询表,而不是使用自动生成的视图,那么必须在查询中使用 _PARTITIONTIME 伪列。如需了解详情,请参阅查询分区表

Campaign Manager 示例查询

您可以使用以下 Campaign Manager 查询示例来分析已传输的数据。此外,您也可以在 Cloud DatalabGoogle Data Studio 等可视化工具中使用这些查询。这些查询旨在帮助您开始使用 BigQuery 来查询 Campaign Manager 数据。如果您对这些报告的功能有其他问题,请联系您的 Campaign Manager 技术代表。

这些示例利用了 BigQuery 对标准 SQL 的支持。请使用 #standardSQL 标记告知 BigQuery 您要使用标准 SQL。如需详细了解 #standardSQL 前缀,请参阅设置查询前缀

在以下每个查询中,将 [DATASET] 替换为您的数据集名称,将 [DOUBLECLICK_ID] 替换为您的 Campaign Manager ID。

最新广告系列

以下查询示例会检索最新广告系列。

经典版界面

#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 天内按广告系列划分的展示次数和不同用户数。

经典版界面

#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 天内按广告系列和日期排序的最新广告系列。

经典版界面

#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] 范围内按广告系列划分的展示次数和不同用户数。

经典版界面

#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 天内按广告系列划分的展示次数、点击次数、活动数和不同用户数。

经典版界面

#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 广告系列。

经典版界面

#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'
此页内容是否有用?请给出您的反馈和评价:

发送以下问题的反馈:

此网页
需要帮助?请访问我们的支持页面