Google Ad Manager transfers

The BigQuery Data Transfer Service for Google Ad Manager allows you to 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 currently supports the following reporting options:

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

Reporting option Support
Schedule

Every 8 hours, based on the creation time.

Not configurable

Refresh window

Last 2 days

Not configurable

Maximum backfill duration

Last 60 days

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

Before you begin

Before you create a Google Ad Manager 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 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 transfer has the following permissions in BigQuery:

      • bigquery.transfers.update permissions to create the 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 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 is most easily accomplished by adding the Ad Manager user who authenticated the 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 transfer for Google Ad Manager:

    Console

    1. Go to the BigQuery page in the Google Cloud console.

      Go to the BigQuery page

    2. Click Data transfers.

    3. Click Create Transfer.

    4. On the Create Transfer page:

      • In the Source type section, for Source, choose Google Ad 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 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 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.

          Google Ad Manager source details

      • In the Service Account menu, select a service account from the service accounts associated with your Google Cloud project. You can associate a service account with your transfer instead of using your user credentials. For more information about using service accounts with data transfers, see Use service accounts.

      • (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 Pub/Sub topic, choose your topic name or click Create a topic. This option configures Pub/Sub run notifications for your transfer.
    5. Click Save.

    bq

    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

    Optional flags:

    • --service_account_name - Specifies a service account to use for Google Ad Manager transfer authentication instead of your user account.
    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
    

    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 Google Ad Manager, you must supply the bucket and network_code, parameters. bucket is the Cloud Storage bucket that contains your Google Ad Manager DT files. network_code is your network code.
    • data_source is the data source — dfp_dt (Google Ad Manager).
    • service_account_name is the service account name used to authenticate your transfer. The service account should be owned by the same project_id used to create the transfer and it should have all of the required permissions.

    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 Google Ad Manager 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.

    Java

    Before trying this sample, follow the Java setup instructions in the BigQuery quickstart using client libraries. For more information, see the BigQuery Java API reference documentation.

    To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.

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

    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