DoubleClick for Publishers Transfers

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

Supported Reports

The BigQuery Data Transfer Service for DoubleClick for Publishers currently supports the following reporting options:

Reporting option Support
Supported API version v201708
Schedule

Every 8 hours, based on the creation time.

Not configurable

Refresh window

Last 2 days

Not configurable

Maximum backfill duration

Last 60 days

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

Before you begin

Before you create a DoubleClick for Publishers transfer:

  • Verify that you have completed all actions required to enable the BigQuery Data Transfer Service.
  • Create a BigQuery dataset to store the DoubleClick data.
  • Ensure that your organization has access to DoubleClick for Publishers Data Transfer (DFP DT) files. These files are delivered by the DFP team to a Google Cloud Storage bucket. To gain access to DFP DT files, complete the following steps. Additional charges from the DoubleClick for Publishers team might apply.

    • Review the Overview of Data Transfer reports.
    • In the upper right corner of the page, click Contact Us.
    • Click Reports.
    • Email or chat with a representative and ask about access to DoubleClick for Publishers Data Transfer files.

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

    gdfp-12345678

    The Google Cloud team does NOT have the ability to generate or grant access to DFP DT files on your behalf. Contact, DoubleClick for Publishers support, for access to DFP DT files.

  • Enable API access to your DoubleClick for Publishers network.
  • Ensure that you have the following required permissions:
    • DoubleClick for Publishers: Read access to the DFP DT files stored in Google Cloud Storage. Permissions for DFP DT files are managed by the DoubleClick for Publishers team.
    • Google Cloud Platform: bigquery.transfers.update permission. The bigquery.admin predefined IAM role includes bigquery.transfers.update permissions.For more information on IAM roles in BigQuery, see Access Control.

Setting up a DoubleClick for Publishers transfer

Setting up a BigQuery data transfer for DoubleClick for Publishers requires a:

  • GCS Bucket: The Google Cloud Storage bucket URI for your DFP DT files as described in Before you Begin. The bucket name should look like the following:

    gdfp-12345678

  • Network Code: You'll find the DoubleClick for Publishers network code in the URL when you are logged into your network. For example, in the URL https://www.google.com/dfp/2032576#delivery, 2032576 is your network code. For more information, see Get started with DoubleClick for Publishers.

To create a BigQuery data transfer for DoubleClick for Publishers:

Web 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 DFP Data Transfer.
    • For Destination, choose the appropriate dataset.
    • 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 GCS Bucket, enter the name of the Cloud Storage bucket that stores your Data Transfer files. When you enter the bucket name, do not include gs://.
    • For Network Code, enter your network code.

      Channel transfer

  5. Click Add.

  6. When prompted, click Allow to give the BigQuery Data Transfer Service permission to manage your DoubleClick campaigns. You must allow pop-ups from bigquery.cloud.google.com to view the permissions window.

    Allow transfer

Command-line

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.
  • --target_dataset is the target dataset for the transfer configuration.
  • --display_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.
  • --params contains the parameters for the created transfer configuration in JSON format. For example: --params='{"param":"param_value"}'. For DoubleClick for Publishers, you must supply the bucket and network_code, parameters. bucket is the Cloud Storage bucket that contains your DFP DT files. network_code is your network code.
  • --data_source is the data source — dfp_dt (DoubleClick for Publishers).

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 DoubleClick for Publishers transfer named My Transfer using network code 12345678, Cloud Storage bucket gdfp-12345678, and target dataset mydataset. The transfer is created in the default project:

bq mk --transfer_config --target_dataset=mydataset --display_name='My Transfer' --params='{"bucket": "gdfp-12345678","network_code": "12345678"}' --data_source=dfp_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.

When your data is transferred to BigQuery, the data is written to date-partitioned tables. For more information, see Partitioned Tables.

If you change the schema of a report, all files on that day must have the same schema, or the transfer for the entire day will fail.

Troubleshooting DoubleClick for Publishers transfer setup

If you are having issues setting up your transfer, see DoubleClick for Publishers transfer issues in Troubleshooting BigQuery Data Transfer Service Transfer Setup.

DoubleClick for Publishers sample queries

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

These samples use BigQuery’s support for standard SQL. Use the #standardSQL tag to let BigQuery know you want to use standard SQL. For more information about the #standardSQL prefix, see Setting a query prefix.

In each of the following queries, replace [DATASET] with your dataset name. Replace [NETWORK_CODE] with your DoubleClick network code.

Impressions and unique users by city

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

Web UI

#standardSQL
# START_DATE = DATE_ADD(CURRENT_DATE(), INTERVAL -31 DAY)
# END_DATE = DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY)
SELECT
  City,
  _DATA_DATE AS Date,
  count(*) AS imps,
  count(distinct UserId) AS uniq_users
FROM `[DATASET].NetworkImpressions[NETWORK_CODE]`
WHERE
  _DATA_DATE BETWEEN [START_DATE] AND [END_DATE]
GROUP BY City, Date

Command-line

# 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
  City,
  _DATA_DATE AS Date,
  count(*) AS imps,
  count(distinct UserId) AS uniq_users
FROM `[DATASET].NetworkImpressions_[NETWORK_CODE]`
WHERE
  _DATA_DATE BETWEEN [START_DATE] AND [END_DATE]
GROUP BY City, Date'

Impressions and unique users by line item type

The following sample query analyzes the number of impressions and unique users by line item type over the past 30 days.

Web UI

#standardSQL
# START_DATE = DATE_ADD(CURRENT_DATE(), INTERVAL -31 DAY)
# END_DATE = DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY)
SELECT
  MT.LineItemType AS LineItemType,
  DT._DATA_DATE AS Date,
  count(*) AS imps,
  count(distinct UserId) AS uniq_users
FROM `[DATASET].NetworkImpressions[NETWORK_CODE]` AS DT
LEFT JOIN `[DATASET].MatchTableLineItem_[NETWORK_CODE]` AS MT
ON
  DT.LineItemId = MT.Id
WHERE
  DT._DATA_DATE BETWEEN [START_DATE] AND [END_DATE]
GROUP BY LineItemType, Date
ORDER BY Date desc, imps desc

Command-line

# 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
  MT.LineItemType AS LineItemType,
  DT._DATA_DATE AS Date,
  count(*) AS imps,
  count(distinct UserId) AS uniq_users
FROM `[DATASET].NetworkImpressions_[NETWORK_CODE]` AS DT
LEFT JOIN `[DATASET].MatchTableLineItem_[NETWORK_CODE]` AS MT
ON
  DT.LineItemId = MT.Id
WHERE
  DT._DATA_DATE BETWEEN [START_DATE] AND [END_DATE]
GROUP BY LineItemType, Date
ORDER BY Date desc, imps desc'

Impressions by ad unit

The following sample query analyzes the number of impressions by ad unit over the past 30 days.

Web UI

#standardSQL
# START_DATE = DATE_ADD(CURRENT_DATE(), INTERVAL -31 DAY)
# END_DATE = DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY)
SELECT
  MT.AdUnitCode AS AdUnitCode,
  DT.DATA_DATE AS Date,
  count(*) AS imps
FROM `[DATASET].NetworkImpressions[NETWORK_CODE]` AS DT
LEFT JOIN `[DATASET].MatchTableLineItem_[NETWORK_CODE]` AS MT
ON
  DT.AdUnitId = MT.Id
WHERE
  DT._DATA_DATE BETWEEN [START_DATE] AND [END_DATE]
GROUP BY AdUnitCode, Date
ORDER BY Date desc, imps desc

Command-line

# 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
  MT.AdUnitCode AS AdUnitCode,
  DT._DATA_DATE AS Date,
  count(*) AS imps
FROM `[DATASET].NetworkImpressions_[NETWORK_CODE]` AS DT
LEFT JOIN `[DATASET].MatchTableLineItem_[NETWORK_CODE]` AS MT
ON
  DT.AdUnitId = MT.Id
WHERE
  DT._DATA_DATE BETWEEN [START_DATE] AND [END_DATE]
GROUP BY AdUnitCode, Date
ORDER BY Date desc, imps desc'

Impressions by line item

The following sample query analyzes the number of impressions by line item over the past 30 days.

Web UI

#standardSQL
# START_DATE = DATE_ADD(CURRENT_DATE(), INTERVAL -31 DAY)
# END_DATE = DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY)
SELECT
  MT.Name AS LineItemName,
  DT._DATA_DATE AS Date,
  count(*) AS imps
FROM `[DATASET].NetworkImpressions[NETWORK_CODE]` AS DT
LEFT JOIN `[DATASET].MatchTableLineItem_[NETWORK_CODE]` AS MT
ON
  DT.LineItemId = MT.Id
WHERE
  DT._DATA_DATE BETWEEN [START_DATE] AND [END_DATE]
GROUP BY LineItemName, Date
ORDER BY Date desc, imps desc

Command-line

# 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
  MT.Name AS LineItemName,
  DT._DATA_DATE AS Date,
  count(*) AS imps
FROM `[DATASET].NetworkImpressions_[NETWORK_CODE]` AS DT
LEFT JOIN `[DATASET].MatchTableLineItem_[NETWORK_CODE]` AS MT
ON
  DT.LineItemId = MT.Id
WHERE
  DT._DATA_DATE BETWEEN [START_DATE] AND [END_DATE]
GROUP BY LineItemName, Date
ORDER BY Date desc, imps desc'

Send feedback about...