Campaign Manager transfers

The BigQuery Data Transfer Service for Campaign Manager allows you to automatically schedule and manage recurring load jobs for Campaign Manager reporting data.

Supported Reports

The BigQuery Data Transfer Service for Campaign Manager (formerly known as DoubleClick Campaign Manager) currently supports the following reporting options:

For information on how Campaign Manager reports are transformed into BigQuery tables and views, see Campaign Manager report transformations.

Reporting option Support
Supported API version June 26, 2017
Schedule

Every 8 hours, based on the creation time.

Not configurable

Refresh window

Last 2 days

Not configurable

Maximum backfill duration

Last 60 days

Campaign Manager retains Data Transfer files for up to 60 days. Files older than 60 days are deleted by Campaign Manager.

Before you begin

Before you create a Campaign Manager transfer:

  • Verify that you have completed all actions required to enable the BigQuery Data Transfer Service.
  • If you are creating the transfer by using the classic BigQuery web UI, allow pop-ups in your browser from bigquery.cloud.google.com so that you can view the permissions window. You must allow the BigQuery Data Transfer Service permission to manage your transfer.
  • Create a BigQuery dataset to store the Campaign Manager data.
  • Ensure that your organization has access to Campaign Manager Data Transfer v2 (Campaign Manager DTv2) files. These files are delivered by the Campaign Manager team to a Cloud Storage bucket. To gain access to Campaign Manager DTv2 files, your next step depends on if you have a direct contract with Campaign Manager. In both cases, additional fees might apply.

    • If you have a contract with Campaign Manager, contact Campaign Manager support to setup Campaign Manager DTv2 files.
    • If you do not have a contract with Campaign Manager, your agency or Campaign Manager reseller may have access to Campaign Manager DTv2 files. Contact your agency or reseller for access to these files.

    After completing this step, you will receive a Cloud Storage bucket name similar to the following:

    dcdt_-dcm_account123456

  • Transfer run notifications are currently in Alpha. If you intend to set up transfer run notifications for Cloud Pub/Sub, you must have pubsub.topics.setIamPolicy permissions. For more information, see BigQuery Data Transfer Service run notifications.

Required permissions

  • BigQuery: Ensure that the person creating the transfer has the following permissions in BigQuery:

    • bigquery.transfers.update permissions to create the transfer
    • bigquery.datasets.update permissions on the target dataset

    The bigquery.admin predefined Cloud IAM role includes bigquery.transfers.update and bigquery.datasets.update permissions. For more information on Cloud IAM roles in BigQuery, see Access control.

  • Campaign Manager: Read access to the Campaign Manager DTv2 files stored in Cloud Storage. Access is managed by the entity from which you received the Cloud Storage bucket.

Setting up a Campaign Manager transfer

Setting up a Campaign Manager transfer requires a:

  • Cloud Storage bucket: The Cloud Storage bucket URI for your Campaign Manager DTv2 files as described in Before you begin. The bucket name should look like the following:

    dcdt_-dcm_account123456

  • Campaign Manager ID: Your Campaign Manager Network, Advertiser, or Floodlight ID. Network ID is the parent in the hierarchy.

Finding your Campaign Manager ID

To retrieve your Campaign Manager ID, you can use the Cloud Storage console to examine the files in your Google Ad Manager Data Transfer Cloud Storage bucket. The Campaign Manager ID is used to match files in the provided Cloud Storage bucket. The ID is embedded in the file name, not the Cloud Storage bucket name.

For example:

  • In a file named dcm_account123456_activity_*, the ID is 123456.
  • In a file named dcm_floodlight7890_activity_*, the ID is 7890.
  • In a file named dcm_advertiser567_activity_*, the ID is 567.

(Optional) Finding your file name prefix

In rare cases, the files in your Cloud Storage bucket may have custom, nonstandard file names that were set up for you by the Google Marketing Platform services team.

For example:

  • In a file named dcm_account123456custom_activity_*, the prefix is dcm_account123456custom — everything before "_activity".

Contact Campaign Manager support if you need help.

Create a BigQuery data transfer for Campaign Manager

Console

  1. Go to the BigQuery web UI in the GCP Console.

    Go to the GCP Console

  2. Click Transfers.

  3. Click Create Transfer.

  4. On the Create Transfer page:

    • In the Source type section, for Source, choose Campaign Manager.

      Transfer source

    • In the Transfer config name section, for Display name, enter a name for the transfer such as My Transfer. The transfer name can be any value that allows you to easily identify the transfer if you need to modify it later.

      Transfer name

    • In the Schedule options section, for Schedule, leave the default value (Start now) or click Start at a set time.

      • For Repeats, choose an option for how often to run the transfer.

        • Daily (default)
        • Weekly
        • Monthly
        • Custom
        • On-demand

        If you choose an option other than Daily, additional options are available. For example, if you choose Weekly, an option appears for you to select the day of the week.

      • For Start date and run time, enter the date and time to start the transfer. If you choose Start now, this option is disabled.

        Transfer schedule

    • In the Destination settings section, for Destination dataset, choose the dataset you created to store your data.

      Transfer dataset

    • In the Data source details section:

      • For Cloud Storage bucket, enter or browse for the name of the Cloud Storage bucket that stores your Data Transfer V2.0 files. When you enter the bucket name, do not include gs://.
      • For DoubleClick ID, enter the appropriate Campaign Manager ID.
      • (Optional) If your files have standard names like these examples, leave the File name prefix field blank. Complete File name prefix only if the files in your Cloud Storage bucket have custom file names like this example.

        Campaign Manager source details

    • (Optional) In the Notification options section:

      • Click the toggle to enable email notifications. When you enable this option, the transfer administrator receives an email notification when a transfer run fails.
      • For Select a Cloud Pub/Sub topic, choose your topic name or click Create a topic. This option configures Cloud Pub/Sub run notifications for your transfer. Transfer run notifications are currently in alpha.
  5. Click Save.

Classic UI

  1. Go to the BigQuery web UI.

    Go to the BigQuery web UI

  2. Click Transfers.

  3. Click Add Transfer.

  4. On the New Transfer page:

    • For Source, choose Campaign Manager (formerly DCM).
    • For Display name, enter a name for the transfer such as My Transfer. The transfer name can be any value that allows you to easily identify the transfer if you need to modify it later.
    • For Destination dataset, choose the appropriate dataset.
    • For Cloud Storage bucket, enter the name of the Cloud Storage bucket that stores your Data Transfer V2.0 files. When you enter the bucket name, do not include gs://.
    • For DoubleClick ID, enter the appropriate Campaign Manager ID.
    • (Optional) If your files have standard names like these examples, leave the File name prefix field blank. Complete File name prefix only if the files in your Cloud Storage bucket have custom file names like this example.

      Campaign Manager transfer

    • (Optional) Expand the Advanced section and configure run notifications for your transfer. Transfer run notifications are currently in Alpha.

      • For Cloud Pub/Sub topic, enter your topic name, for example, projects/myproject/topics/mytopic.
      • Check Send email notifications to allow email notifications of transfer run failures.
      • Don't check Disabled when setting up a transfer. See Working with transfers to disable existing transfers.

        Cloud Pub/Sub topic

  5. Click Add.

  6. When prompted, click Allow to give the BigQuery Data Transfer Service permission to access your Campaign Manager reporting data and to manage the data in BigQuery. You must allow pop-ups from bigquery.cloud.google.com to view the permissions window.

    Allow transfer

CLI

Enter the bq mk command and supply the transfer creation flag — --transfer_config. The following flags are also required:

  • --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

Where:

  • project_id is your project ID.
  • dataset is the target dataset for the transfer configuration.
  • name is the display name for the transfer configuration. The transfer name can be any value that allows you to easily identify the transfer if you need to modify it later.
  • parameters contains the parameters for the created transfer configuration in JSON format. For example: --params='{"param":"param_value"}'. For Campaign Manager, you must supply the bucket and network_id, parameters. bucket is the Cloud Storage bucket that contains your Campaign Manager DTv2 files. network_id is your network, floodlight, or advertiser ID.
  • data_source is the data source — dcm_dt (Campaign Manager).

You can also supply the --project_id flag to specify a particular project. If --project_id isn't specified, the default project is used.

For example, the following command creates a Campaign Manager transfer named My Transfer using Campaign Manager ID 123456, Cloud Storage bucket dcdt_-dcm_account123456, and target dataset mydataset. The parameter file_name_prefix is optional and used for rare, custom file names only.

The transfer is created in the default project:

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

After running the command, you receive a message like the following:

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

Follow the instructions and paste the authentication code on the command line.

API

Use the projects.locations.transferConfigs.create method and supply an instance of the TransferConfig resource.

Troubleshooting Campaign Manager transfer setup

If you are having issues setting up your transfer, see Campaign Manager transfer issues in Troubleshooting BigQuery Data Transfer Service transfer setup.

Querying your data

When your data is transferred to BigQuery, the data is written to ingestion-time partitioned tables. For more information, see Introduction to partitioned tables.

If you query your tables directly instead of using the auto-generated views, you must use the _PARTITIONTIME pseudo-column in your query. For more information, see Querying partitioned tables.

Campaign Manager sample queries

You can use the following Campaign Manager sample queries to analyze your transferred data. You can also use the queries in a visualization tool such as Cloud Datalab or Google Data Studio. These queries are provided to help you get started on querying your Campaign Manager data with BigQuery. For additional questions on what you can do with these reports, contact your Campaign Manager technical representative.

In each of the following queries, replace the variables like dataset with your values.

Latest campaigns

The following sample query retrieves the latest campaigns.

Console

SELECT
  Campaign,
  Campaign_ID
FROM
  `dataset.match_table_campaigns_doubleclick_id`
WHERE
  _DATA_DATE = _LATEST_DATE

CLI

bq query --use_legacy_sql=false \
'SELECT
   Campaign,
   Campaign_ID
 FROM
   `dataset.match_table_campaigns_doubleclick_id`
 WHERE
   _DATA_DATE = _LATEST_DATE'

Impressions and distinct users by campaign

The following sample query analyzes the number of impressions and distinct users by campaign over the past 30 days.

Console

# 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.impressiondoubleclick_id`
  WHERE
    _DATA_DATE BETWEEN start_date
    AND end_date
  GROUP BY
    Campaign_ID,
    Date

CLI

# 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.impressiondoubleclick_id`
WHERE
  _DATA_DATE BETWEEN start_date
  AND end_date
GROUP BY
  Campaign_ID,
  Date'

Latest campaigns ordered by campaign and date

The following sample query analyzes the latest campaigns in the past 30 days, ordered by campaign and date.

Console

# 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

CLI

# 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'

Impressions and distinct users by campaign within a date range

The following sample query analyzes the number of impressions and distinct users by campaign between start_date and end_date.

Console

# 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.impressiondoubleclick_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

CLI

# 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.impressiondoubleclick_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'

Impressions, clicks, activities and distinct users by campaign

The following sample query analyzes the number of impressions, clicks, activities, and distinct users by campaign over the past 30 days. In this query, replace the variables like campaign_list with your values. For example, replace campaign_list with a comma separated list of all the Campaign Manager campaigns of interest within the scope of the query.

Console

# 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.impressiondoubleclick_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.clickdoubleclick_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.activitydoubleclick_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

CLI

# 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.impressiondoubleclick_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.clickdoubleclick_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.activitydoubleclick_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'

Campaign activity

The following sample query analyzes campaign activity over the past 30 days. In this query, replace the variables like campaign_list with your values. For example, replace campaign_list with a comma separated list of all the Campaign Manager campaigns of interest within the scope of the query.

Console

# 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_catsdoubleclick_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

CLI

# 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_catsdoubleclick_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'
Czy ta strona była pomocna? Podziel się z nami swoją opinią:

Wyślij opinię na temat...

Potrzebujesz pomocy? Odwiedź naszą stronę wsparcia.