Google Ad Manager transfers

The BigQuery Data Transfer Service for Google Ad Manager connector lets you automatically create and manage recurring load jobs for Google Ad Manager (formerly known as DoubleClick for Publishers) reporting data.

Supported reports

The BigQuery Data Transfer Service for Google Ad Manager connector supports the following reporting options:

• Data Transfer (Google Ad Manager DT) files
• Data Transfer fields
• Match tables provided by the BigQuery Data Transfer Service. These are automatically created and updated.
• Match tables fetched with PQL
• Match tables from CompanyService (v201908)
• Match tables from OrderService (v201908)
• Match tables from PlacementService (v201908)

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

Data ingestion from Google Ad Manager transfers

Refresh windows

A refresh window is the number of days that a data transfer retrieves data when a data transfer occurs. For example, if the refresh window is three days and a daily transfer occurs, the BigQuery Data Transfer Service retrieves all data from your source table from the past three days. In this example, when a daily transfer occurs, the BigQuery Data Transfer Service creates a new BigQuery destination table partition with a copy of your source table data from the current day, then automatically triggers backfill runs to update the BigQuery destination table partitions with your source table data from the past two days. The automatically triggered backfill runs will either overwrite or incrementally update your BigQuery destination table, depending on whether or not incremental updates are supported in the BigQuery Data Transfer Service connector.

When you run a data transfer for the first time, the data transfer retrieves all source data available within the refresh window. For example, if the refresh window is three days and you run the data transfer for the first time, the BigQuery Data Transfer Service retrieves all source data within three days.

Refresh windows are mapped to the TransferConfig.data_refresh_window_days API field.

To retrieve data outside the refresh window, such as historical data, or to recover data from any transfer outages or gaps, you can initiate or schedule a backfill run.

Incremental updates

Tables that are created from Google Ad Manager Data Transfer (Google Ad Manager DT) files can be updated incrementally. Google Ad Manager adds the Google Ad Manager DT files into the Cloud Storage bucket. A transfer run then incrementally loads the new Google Ad Manager DT files from the Cloud Storage bucket into the BigQuery table without reloading files that have already been transferred to the BigQuery table.

For example: Google Ad Manager adds file1 into the bucket at 1:00 AM and file2 at 2:00 AM. A transfer run begins at 3:30 AM and loads file1 and file2 to BigQuery. Google Ad Manager then adds file3 at 5:00 AM and file4 at 6:00 AM. A second transfer run begins at 7:30AM and appends file3 and file4 into BigQuery, instead of overwriting the table by loading all four files.

Before you begin

Before you create a Google Ad Manager data transfer:

• Verify that you have completed all actions required to enable the BigQuery Data Transfer Service.
• Create a BigQuery dataset to store the Google Ad Manager data.

• Ensure that your organization has access to Google Ad Manager Data Transfer (Google Ad Manager DT) files. These files are delivered by the Google Ad Manager team to a Cloud Storage bucket. To gain access to Google Ad Manager DT files, review Ad Manager Data Transfer reports. Additional charges from the Google Ad Manager team might apply.

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

      gdfp-12345678
    

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

• Enable API access to your Google Ad Manager network.
• If you intend to set up data transfer notifications, you must have pubsub.topics.setIamPolicy permissions for Pub/Sub. Pub/Sub permissions are not required if you just set up email notifications. For more information, see BigQuery Data Transfer Service run notifications.

Required permissions

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

  • bigquery.transfers.update permissions to create the data transfer
  • Both bigquery.datasets.get and bigquery.datasets.update permissions on the target dataset

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

• Google Ad Manager: Read access to the Google Ad Manager DT files stored in Cloud Storage. Permissions for Google Ad Manager DT files are managed by the Google Ad Manager team. In addition to the Google Ad Manager DT files, the person creating the data transfer must be added to the Google Ad Manager network, with read access to all the entities needed to create the various match tables (line item, order, ad unit, etc.). This can be accomplished by adding the Ad Manager user who authenticated the data transfer to the All Entities team in Ad Manager.

Set up a Google Ad Manager transfer

Setting up a BigQuery data transfer for Google Ad Manager requires a:

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

  gdfp-12345678

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

To create a BigQuery Data Transfer Service data transfer for Google Ad Manager:

bq mk --transfer_config \
--project_id=project_id \
--target_dataset=dataset \
--display_name=name \
--params='parameters' \
--data_source=data_source \
--service_account_name=service_account_name

bq mk --transfer_config \
--target_dataset=mydataset \
--display_name='My Transfer' \
--params='{"bucket": "gdfp-12345678","network_code": "12345678"}' \
--data_source=dfp_dt

import com.google.api.gax.rpc.ApiException;
import com.google.cloud.bigquery.datatransfer.v1.CreateTransferConfigRequest;
import com.google.cloud.bigquery.datatransfer.v1.DataTransferServiceClient;
import com.google.cloud.bigquery.datatransfer.v1.ProjectName;
import com.google.cloud.bigquery.datatransfer.v1.TransferConfig;
import com.google.protobuf.Struct;
import com.google.protobuf.Value;
import java.io.IOException;
import java.util.HashMap;
import java.util.Map;

// Sample to create a ad manager(formerly DFP) transfer config
public class CreateAdManagerTransfer {

  public static void main(String[] args) throws IOException {
    // TODO(developer): Replace these variables before running the sample.
    final String projectId = "MY_PROJECT_ID";
    String datasetId = "MY_DATASET_ID";
    String bucket = "gs://cloud-sample-data";
    // the network_code can only be digits with length 1 to 15
    String networkCode = "12345678";
    Map<String, Value> params = new HashMap<>();
    params.put("bucket", Value.newBuilder().setStringValue(bucket).build());
    params.put("network_code", Value.newBuilder().setStringValue(networkCode).build());
    TransferConfig transferConfig =
        TransferConfig.newBuilder()
            .setDestinationDatasetId(datasetId)
            .setDisplayName("Your Ad Manager Config Name")
            .setDataSourceId("dfp_dt")
            .setParams(Struct.newBuilder().putAllFields(params).build())
            .build();
    createAdManagerTransfer(projectId, transferConfig);
  }

  public static void createAdManagerTransfer(String projectId, TransferConfig transferConfig)
      throws IOException {
    try (DataTransferServiceClient client = DataTransferServiceClient.create()) {
      ProjectName parent = ProjectName.of(projectId);
      CreateTransferConfigRequest request =
          CreateTransferConfigRequest.newBuilder()
              .setParent(parent.toString())
              .setTransferConfig(transferConfig)
              .build();
      TransferConfig config = client.createTransferConfig(request);
      System.out.println("Ad manager transfer created successfully :" + config.getName());
    } catch (ApiException ex) {
      System.out.print("Ad manager transfer was not created." + ex.toString());
    }
  }
}

Troubleshoot Google Ad Manager transfer setup

If you are having issues setting up your data transfer, see Google Ad Manager transfer issues in Troubleshooting transfer configurations.

Query 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 pseudocolumn in your query. For more information, see Querying partitioned tables.

Google Ad Manager sample queries

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

In each of the following queries, replace variables like dataset with your values. For example, replace network_code with your Google Ad Manager network code.

Impressions and unique users by city

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

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

Impressions and unique users by line item type

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

# 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.NetworkImpressionsnetwork_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 SQL sample query analyzes the number of impressions by ad unit over the past 30 days.

# 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.NetworkImpressionsnetwork_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 SQL sample query analyzes the number of impressions by line item over the past 30 days.

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