BigQuery Data Transfer Service for DoubleClick for Publishers

Overview

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

To schedule a BigQuery data transfer for DoubleClick for Publishers, you must have access to:

  • Read access to your DoubleClick for Publishers network

By default, BigQuery data transfers for DoubleClick for Publishers adhere to the following schedule:

  • Transfers run every 8 hours.
  • By default, data for the past 2 days is refreshed on a daily basis.

Before you begin

  1. Sign in to your Google account.

    If you don't already have one, sign up for a new account.

  2. Select or create a Cloud Platform project.

    Go to the Projects page

  3. Enable billing for your project.

    Enable billing

  4. BigQuery is automatically enabled in new projects. To activate BigQuery in a pre-existing project, Enable the BigQuery API.

    Enable the API

  5. Create a BigQuery dataset to store the DoubleClick for Publishers data.
  6. Work with your DoubleClick sales or support contact to setup access to DoubleClick Data Transfer.
  7. Obtain the Google Cloud Storage bucket URI for your DoubleClick Data Transfer files from your DoubleClick administrator.
  8. The user creating your transfers should be granted the bigquery.admin role for the transfers to succeed. You can apply roles using the IAM & Admin console.
  9. IAM & Admin

Before using the BigQuery Transfer Service, you must complete the enrollment form. To complete the form, you need the project number for the project containing your transfers.

  1. Open the Cloud Platform Console.
  2. Cloud Platform Console
  3. If your transfer project isn't selected, at the top of the window, click the project name.
  4. In the Select project dialog, select your project and then click Open.
  5. On the Dashboard tab, in the Project Info box, note your project number (below your project ID). The project number is a string of numerals.
  6. Project number
  7. Complete the BigQuery Data Transfer Service enrollment form.

The enrollment request must be completed by the BigQuery team before you can use the service. Requests are generally completed within 1 business day but may take up to 3 business days depending on the volume of requests. Please contact your Cloud Sales Representative if you have questions about the status of your enrollment request.

Set up a BigQuery data transfer for DoubleClick for Publishers

Setting up a BigQuery data transfer for DoubleClick for Publishers reporting requires your network code. You'll find the 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:

  1. Go to the BigQuery web UI.

    Go to the BigQuery web UI

  2. Click Transfers.

  3. Click Enable to grant permissions to the BigQuery Data Transfer Service. The first time you enable transfers in a project, you are required to grant permissions to the Data Transfer Service. After permissions are granted, you are not prompted again.

    Enable transfers

  4. Click Add Transfer.

  5. 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

  6. Click Add.

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

    Once the transfer is added, you can edit the transfer, delete the transfer, update the credentials, schedule a backfill, or view the transfer properties such as the next scheduled transfer in the BigQuery web UI.

    Transfer properties

As your scheduled transfers are run, the BigQuery web UI displays the run history, including successful transfers and transfers that fail. Scheduled runs more than 90 days old are automatically deleted from the run history.

To view the run history:

  1. Go to the BigQuery web UI.

    Go to the BigQuery web UI

  2. Click Transfers.

  3. On the Transfers page, click the appropriate transfer in the list.

  4. On the properties page, the run history appears below the transfer properties.

    Run history

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

Set up a backfill

To transfer historical DoubleClick data to BigQuery, you must request a backfill. You can schedule DoubleClick for Publishers backfills up to the past 60 days.

To set up a backfill:

  1. Go to the BigQuery web UI.
  2. BigQuery web UI
  3. Click Transfers.
  4. On the Transfer page, click the appropriate transfer.
  5. Click Schedule Backfill.
  6. Schedule backfill
  7. In the Schedule Run dialog:
    • For Data Start Date, enter or choose the appropriate date in MM/DD/YYYY format.
    • For Data End Date, enter or choose the appropriate date in MM/DD/YYYY format.
    • Click Ok.
    • Schedule backfill

DoubleClick for Publishers delimiter support

The BigQuery Data Transfer Service supports the following delimiters in DoubleClick for Publishers Data Transfer files:

  • Tab ( \t )
  • Pipe ( | )
  • Caret ( ^ )
  • Comma ( , )

For more information, see What's included in Data Transfer files.

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