Load Campaign Manager data into BigQuery

You can load data from Campaign Manager to BigQuery using theBigQuery Data Transfer Service for Campaign Manager connector. With theBigQuery Data Transfer Service, you can schedule recurring transfer jobs thatadd your latest data from your Campaign Manager toBigQuery.

Connector overview

The BigQuery Data Transfer Service for the Campaign Manager connector supports the following options for your data transfer.

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

Data transfer optionsSupport
Supported reports The Campaign Manager connector supports the transfer of data from the following reports:
Repeat frequencyThe Campaign Manager connector supports data transfer every 8 hours.

By default, Campaign Manager data transfers are scheduled at the time when the data transfer is created.
Refresh windowThe Campaign Manager connector retrieves Campaign Manager data from up to 2 days at the time the data transfer is run. You cannot configure the refresh window for this connector.

For more information, seeRefresh windows.
Backfill data availabilityRun a data backfill to retrieve data outside of your scheduled data transfer. You can retrieve data as far back as the data retention policy on your data source allows.

For information about the data retention policy for Display & Video 360, seeData deletion and retention controls.

Data ingestion from Campaign Manager transfers

When you transfer data from Campaign Manager into BigQuery, thedata is loaded into BigQuery tables that are partitioned by date.The table partition that the data is loaded into corresponds to the date fromthe data source. If you schedule multiple transfers for the same date,BigQuery Data Transfer Service overwrites the partition for that specific date withthe latest data. Multiple transfers in the same day or running backfills don'tresult in duplicate data, and partitions for other dates are not affected.

Refresh windows

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

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

To retrieve data outside the refresh window, such as historical data, or torecover data from any transfer outages or gaps, you can initiate or schedule abackfill run.

Before you begin

Before you create a Campaign Manager data transfer:

Required permissions

Ensure that you have granted the following permissions.

Required BigQuery roles

To get the permissions that you need to create a BigQuery Data Transfer Service data transfer, ask your administrator to grant you theBigQuery Admin (roles/bigquery.admin) IAM role on your project. For more information about granting roles, seeManage access to projects, folders, and organizations.

This predefined role contains the permissions required to create a BigQuery Data Transfer Service data transfer. To see the exact permissions that are required, expand theRequired permissions section:

Required permissions

The following permissions are required to create a BigQuery Data Transfer Service data transfer:

  • BigQuery Data Transfer Service permissions:
    • bigquery.transfers.update
    • bigquery.transfers.get
  • BigQuery permissions:
    • bigquery.datasets.get
    • bigquery.datasets.getIamPolicy
    • bigquery.datasets.update
    • bigquery.datasets.setIamPolicy
    • bigquery.jobs.create

You might also be able to get these permissions withcustom roles or otherpredefined roles.

For more information, seeGrantbigquery.admin access.

Required Campaign Manager roles

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

Set up a Campaign Manager transfer

Setting up a Campaign Manager data transfer requires a:

  • Cloud Storage bucket: The Cloud Storage bucket URI for yourCampaign Manager DTv2 files as described inBefore you begin.The bucket name should look like the following:

    dcdt_-dcm_account123456

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

Find your Campaign Manager ID

To retrieve your Campaign Manager ID, you can use the Cloud Storageconsole to examine the files in yourCampaign Manager Data Transfer Cloud Storagebucket. The Campaign Manager ID is used to match files in the providedCloud Storage bucket. The ID is embedded in thefile name, not theCloud Storage bucket name.

For example:

  • In a file nameddcm_account123456_activity_*,the ID is123456.
  • In a file nameddcm_floodlight7890_activity_*,the ID is7890.
  • In a file nameddcm_advertiser567_activity_*,the ID is567.

Finding your filename 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 Platformservices team.

For example:

  • In a file nameddcm_account123456custom_activity_*,the prefix isdcm_account123456custom — everything before_activity.

ContactCampaign Manager supportif you need help.

Create a data transfer for Campaign Manager

Console

  1. Go to the Data transfers page in the Google Cloud console.

    Go to Data transfers

  2. ClickCreate transfer.

  3. On theCreate Transfer page:

    • In theSource type section, forSource, chooseCampaign Manager.

      Transfer source

    • In theTransfer config name section, forDisplay name, enter aname for the data transfer such asMy Transfer. The transfer name canbe any value that lets you identify the transfer if you need to modifyit later.

      Transfer name

    • In theSchedule options section, forSchedule, leave thedefault value (Start now) or clickStart at a set time.

      • ForRepeats, choose an option for how often to run thetransfer. If you choose an option other thanDaily, additionaloptions are available. For example, if you chooseWeekly, anoption appears for you to select the day of the week.
      • ForStart date and run time, enter the date and time to startthe data transfer. If you chooseStart now, this option isdisabled.

    • In theDestination settings section, forDestination dataset,choose the dataset you created to store your data.

      Transfer dataset

    • In theData source details section:

      • ForCloud Storage bucket, enter or browse for the name ofthe Cloud Storage bucket that stores your Data Transfer V2.0files. When you enter the bucket name, don't includegs://.
      • ForDoubleClick ID, enter the appropriate Campaign Manager ID.
      • (Optional) If your files havestandard names like these examples,leave theFile name prefix field blank. Specify afilename prefixif the files in your Cloud Storage bucket have custom filenames.

      Campaign Manager source details

    • (Optional) In theNotification options section:

      • Click the toggle to enable email notifications. When you enable thisoption, the transfer administrator receives an email notificationwhen a transfer run fails.
      • ForSelect a Pub/Sub topic, choose yourtopicname or clickCreate a topic. This option configuresPub/Sub runnotifications for yourtransfer.

  4. ClickSave.

bq

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

  • --data_source
  • --target_dataset
  • --display_name
  • --params
bqmk--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 data transfer configuration.
  • name is the display name for the data transfer configuration. Thetransfer name can be any value that lets you identify thetransfer if you need to modify it later.
  • parameters contains the parameters for the created data transferconfiguration in JSON format. For example:--params='{"param":"param_value"}'. For Campaign Manager, you must supplythebucket andnetwork_id, parameters.bucket is theCloud 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 (CampaignManager).
Caution: You cannot configure notifications using the command-linetool.

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

For example, the following command creates a Campaign Managerdata transfer namedMy Transfer using Campaign Manager ID123456,Cloud Storage bucketdcdt_-dcm_account123456, and target datasetmydataset. The parameterfile_name_prefix is optional and used for rare,custom file names only.

The data 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 andfollow the instructions to retrieve an authentication code.

Follow the instructions and paste the authentication code on the commandline.

Caution: When you create a Campaign Manager data transfer using thecommand-line tool, the transfer configuration is set up using the defaultvalue forSchedule (every 8 hours).

API

Use theprojects.locations.transferConfigs.createmethod and supply an instance of theTransferConfigresource.

Java

Before trying this sample, follow theJava setup instructions in theBigQuery quickstart using client libraries. For more information, see theBigQueryJava API reference documentation.

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

importcom.google.api.gax.rpc.ApiException;importcom.google.cloud.bigquery.datatransfer.v1.CreateTransferConfigRequest;importcom.google.cloud.bigquery.datatransfer.v1.DataTransferServiceClient;importcom.google.cloud.bigquery.datatransfer.v1.ProjectName;importcom.google.cloud.bigquery.datatransfer.v1.TransferConfig;importcom.google.protobuf.Struct;importcom.google.protobuf.Value;importjava.io.IOException;importjava.util.HashMap;importjava.util.Map;// Sample to create campaign manager transfer configpublicclassCreateCampaignmanagerTransfer{publicstaticvoidmain(String[]args)throwsIOException{// TODO(developer): Replace these variables before running the sample.finalStringprojectId="MY_PROJECT_ID";StringdatasetId="MY_DATASET_ID";Stringbucket="gs://cloud-sample-data";// the network_id only allows digitsStringnetworkId="7878";StringfileNamePrefix="test_";Map<String,Value>params=newHashMap<>();params.put("bucket",Value.newBuilder().setStringValue(bucket).build());params.put("network_id",Value.newBuilder().setStringValue(networkId).build());params.put("file_name_prefix",Value.newBuilder().setStringValue(fileNamePrefix).build());TransferConfigtransferConfig=TransferConfig.newBuilder().setDestinationDatasetId(datasetId).setDisplayName("Your Campaignmanager Config Name").setDataSourceId("dcm_dt").setParams(Struct.newBuilder().putAllFields(params).build()).build();createCampaignmanagerTransfer(projectId,transferConfig);}publicstaticvoidcreateCampaignmanagerTransfer(StringprojectId,TransferConfigtransferConfig)throwsIOException{try(DataTransferServiceClientclient=DataTransferServiceClient.create()){ProjectNameparent=ProjectName.of(projectId);CreateTransferConfigRequestrequest=CreateTransferConfigRequest.newBuilder().setParent(parent.toString()).setTransferConfig(transferConfig).build();TransferConfigconfig=client.createTransferConfig(request);System.out.println("Campaignmanager transfer created successfully :"+config.getName());}catch(ApiExceptionex){System.out.print("Campaignmanager transfer was not created."+ex.toString());}}}
Warning: If you change the schema of a report, all files on that day must havethe same schema, or the data transfer for the entire day will fail.

Troubleshoot Campaign Manager transfer setup

If you are having issues setting up your data transfer, seeCampaign Manager transfer issuesinTroubleshooting transfer configurations.

Query your data

When your data is transferred to BigQuery, the data iswritten to ingestion-time partitioned tables. For more information, seeIntroduction to partitioned tables.

If you query your tables directly instead of using the auto-generated views, youmust use the_PARTITIONTIME pseudocolumn in your query. For more information,seeQuerying partitioned tables.

Campaign Manager sample queries

You can use the following Campaign Manager sample queries toanalyze your transferred data. You can also use the queries in a visualizationtool such asLooker Studio.These queries are provided to help you get started on querying yourCampaign Manager data with BigQuery. For additional questions onwhat you can do with these reports, contact your Campaign Managertechnical representative.

Note: If you query your tables directly instead of using the auto-generatedviews, you must use the_PARTITIONTIME pseudocolumn in your query. For moreinformation, seeQuerying partitioned tables.

In each of the following queries, replace the variables likedatasetwith your values.

Latest campaigns

The following SQL sample query retrieves the latest campaigns.

SELECTCampaign,Campaign_IDFROM`dataset.match_table_campaigns_campaign_manager_id`WHERE_DATA_DATE=_LATEST_DATE

Impressions and distinct users by campaign

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

# START_DATE = DATE_ADD(CURRENT_DATE(), INTERVAL -31 DAY)# END_DATE = DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY)SELECTCampaign_ID,_DATA_DATEASDate,COUNT(*)AScount,COUNT(DISTINCTUser_ID)ASduFROM`dataset.impression_campaign_manager_id`WHERE_DATA_DATEBETWEENstart_dateANDend_dateGROUPBYCampaign_ID,Date

Latest campaigns ordered by campaign and date

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

# START_DATE = DATE_ADD(CURRENT_DATE(), INTERVAL -31 DAY)# END_DATE = DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY)SELECTCampaign,Campaign_ID,DateFROM(SELECTCampaign,Campaign_IDFROM`dataset.match_table_campaigns_campaign_manager_id`WHERE_DATA_DATE=_LATEST_DATE),(SELECTdateASDateFROM`bigquery-public-data.utility_us.date_greg`WHEREDateBETWEENstart_dateANDend_date)ORDERBYCampaign_ID,Date

Impressions and distinct users by campaign within a date range

The following SQL sample query analyzes the number of impressions and distinctusers by campaign betweenstart_date andend_date.

# START_DATE = DATE_ADD(CURRENT_DATE(), INTERVAL -31 DAY)# END_DATE = DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY)SELECTbase.*,imp.countASimp_count,imp.duASimp_duFROM(SELECT*FROM(SELECTCampaign,Campaign_IDFROM`dataset.match_table_campaigns_campaign_manager_id`WHERE_DATA_DATE=_LATEST_DATE),(SELECTdateASDateFROM`bigquery-public-data.utility_us.date_greg`WHEREDateBETWEENstart_dateANDend_date))ASbaseLEFTJOIN(SELECTCampaign_ID,_DATA_DATEASDate,COUNT(*)AScount,COUNT(DISTINCTUser_ID)ASduFROM`dataset.impression_campaign_manager_id`WHERE_DATA_DATEBETWEENstart_dateANDend_dateGROUPBYCampaign_ID,Date)ASimpONbase.Campaign_ID=imp.Campaign_IDANDbase.Date=imp.DateWHEREbase.Campaign_ID=imp.Campaign_IDANDbase.Date=imp.DateORDERBYbase.Campaign_ID,base.Date

Impressions, clicks, activities and distinct users by campaign

The following SQL sample query analyzes the number of impressions, clicks,activities, and distinct users by campaign over the past 30 days. Inthis query, replace the variables likecampaign_list with yourvalues. For example, replacecampaign_list with a comma separatedlist of all the Campaign Manager campaigns of interest within the scope of thequery.

# START_DATE = DATE_ADD(CURRENT_DATE(), INTERVAL -31 DAY)# END_DATE = DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY)SELECTbase.*,imp.countASimp_count,imp.duASimp_du,click.countASclick_count,click.duASclick_du,activity.countASactivity_count,activity.duASactivity_duFROM(SELECT*FROM(SELECTCampaign,Campaign_IDFROM`dataset.match_table_campaigns_campaign_manager_id`WHERE_DATA_DATE=_LATEST_DATE),(SELECTdateASDateFROM`bigquery-public-data.utility_us.date_greg`WHEREDateBETWEENDATE_ADD(CURRENT_DATE(),INTERVAL-31DAY)ANDDATE_ADD(CURRENT_DATE(),INTERVAL-1DAY)))ASbaseLEFTJOIN(SELECTCampaign_ID,_DATA_DATEASDate,COUNT(*)AScount,COUNT(DISTINCTUser_ID)ASduFROM`dataset.impression_campaign_manager_id`WHERE_DATA_DATEBETWEENDATE_ADD(CURRENT_DATE(),INTERVAL-31DAY)ANDDATE_ADD(CURRENT_DATE(),INTERVAL-1DAY)GROUPBYCampaign_ID,Date)ASimpONbase.Campaign_ID=imp.Campaign_IDANDbase.Date=imp.DateLEFTJOIN(SELECTCampaign_ID,_DATA_DATEASDate,COUNT(*)AScount,COUNT(DISTINCTUser_ID)ASduFROM`dataset.click_campaign_manager_id`WHERE_DATA_DATEBETWEENDATE_ADD(CURRENT_DATE(),INTERVAL-31DAY)ANDDATE_ADD(CURRENT_DATE(),INTERVAL-1DAY)GROUPBYCampaign_ID,Date)ASclickONbase.Campaign_ID=click.Campaign_IDANDbase.Date=click.DateLEFTJOIN(SELECTCampaign_ID,_DATA_DATEASDate,COUNT(*)AScount,COUNT(DISTINCTUser_ID)ASduFROM`dataset.activity_campaign_manager_id`WHERE_DATA_DATEBETWEENDATE_ADD(CURRENT_DATE(),INTERVAL-31DAY)ANDDATE_ADD(CURRENT_DATE(),INTERVAL-1DAY)GROUPBYCampaign_ID,Date)ASactivityONbase.Campaign_ID=activity.Campaign_IDANDbase.Date=activity.DateWHEREbase.Campaign_IDINcampaign_listAND(base.Date=imp.DateORbase.Date=click.DateORbase.Date=activity.Date)ORDERBYbase.Campaign_ID,base.Date

Campaign activity

The following SQL sample query analyzes campaign activity over the past 30 days.In this query, replace the variables likecampaign_list with yourvalues. For example, replacecampaign_list with a comma separatedlist of all the Campaign Manager campaigns of interest within the scope of thequery.

# START_DATE = DATE_ADD(CURRENT_DATE(), INTERVAL -31 DAY)# END_DATE = DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY)SELECTbase.*,activity.countASactivity_count,activity.duASactivity_duFROM(SELECT*FROM(SELECTCampaign,Campaign_IDFROM`dataset.match_table_campaigns_campaign_manager_id`WHERE_DATA_DATE=_LATEST_DATE),(SELECTmt_at.Activity_Group,mt_ac.Activity,mt_ac.Activity_Type,mt_ac.Activity_Sub_Type,mt_ac.Activity_ID,mt_ac.Activity_Group_IDFROM`dataset.match_table_activity_cats_campaign_manager_id`ASmt_acJOIN(SELECTActivity_Group,Activity_Group_IDFROM`dataset.match_table_activity_types_campaign_manager_id`WHERE_DATA_DATE=_LATEST_DATE)ASmt_atONmt_at.Activity_Group_ID=mt_ac.Activity_Group_IDWHERE_DATA_DATE=_LATEST_DATE),(SELECTdateASDateFROM`bigquery-public-data.utility_us.date_greg`WHEREDateBETWEENstart_dateANDend_date))ASbaseLEFTJOIN(SELECTCampaign_ID,Activity_ID,_DATA_DATEASDate,COUNT(*)AScount,COUNT(DISTINCTUser_ID)ASduFROM`dataset.activity_campaign_manager_id`WHERE_DATA_DATEBETWEENDATE_ADD(CURRENT_DATE(),INTERVAL-31DAY)ANDDATE_ADD(CURRENT_DATE(),INTERVAL-1DAY)GROUPBYCampaign_ID,Activity_ID,Date)ASactivityONbase.Campaign_ID=activity.Campaign_IDANDbase.Activity_ID=activity.Activity_IDANDbase.Date=activity.DateWHEREbase.Campaign_IDINcampaign_listANDbase.Activity_ID=activity.Activity_IDORDERBYbase.Campaign_ID,base.Activity_Group_ID,base.Activity_ID,base.Date

Except as otherwise noted, the content of this page is licensed under theCreative Commons Attribution 4.0 License, and code samples are licensed under theApache 2.0 License. For details, see theGoogle Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.

Last updated 2026-02-18 UTC.