Search Ads 360 transfers (Deprecated)

The BigQuery Data Transfer Service for Search Ads 360 (formerly known as Doubleclick Search) allows you to automatically schedule and manage recurring load jobs for Search Ads 360 reporting data.

Supported Reports

The BigQuery Data Transfer Service for Search Ads 360 currently supports the following reporting options:

For information on how Search Ads 360 reports are transformed into BigQuery Data Transfer Service tables and views, see Search Ads 360 report transformations.

Reporting option	Support
Supported API version	v2
Schedule	Daily, at the time the Transfer is first created (default) You can configure the time of day.
Refresh window	Last 7 days (default) Configurable up to 30 days Snapshots of Match Tables are taken once a day and stored in the partition for the latest run date. Match Table snapshots are NOT updated for backfills or for days loaded via the refresh window. For information on which Match Tables are not updated by the refresh window, see Set up a backfill.
Maximum backfill duration	No limit While Search Ads 360 has no known data retention limits, the BigQuery Data Transfer Service has limits on how many days can be requested in a single backfill. For information on backfills, see Set up a backfill.

Before you begin

Before you create a Search Ads 360 transfer:

Verify that you have completed all actions required to enable the BigQuery Data Transfer Service.
Create a BigQuery Data Transfer Service dataset to store the Search Ads 360 data.
If you intend to setup transfer run notifications for Pub/Sub, you must have pubsub.topics.setIamPolicy permissions. Pub/Sub permissions are not required if you just set up email notifications. For more information, see BigQuery Data Transfer Service run notifications.
Enable access to the Search Ads 360 API in your project.

Required permissions

Ensure that the person creating the transfer has the following required permissions:

BigQuery Data Transfer Service:
- 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 Cloud:
- serviceusage.services.use permissions to download data from Search Ads 360 on the project
The editor, owner and serviceusage.serviceUsageConsumer predefined IAM roles include serviceusage.services.use permissions. For more information on IAM roles in Service Usage, see Access control reference.
Google Search Ads 360: Read access to Google Search Ads 360 Agency ID and Advertiser ID that is used in the transfer configuration. For more information, see Create a Google Account and get SA360 permissions.

Set up a Search Ads 360 data transfer

Setting up a data transfer for Search Ads 360 reporting requires a:

Agency ID: Your Google Search Ads 360 Agency ID. For information on retrieving your Search Ads 360 Agency ID, see Create a Google Account and get SA360 permissions.
Advertiser ID: Your Google Search Ads 360 Advertiser ID. For information on retrieving your Search Ads 360 Advertiser ID, see Create a Google Account and get SA360 permissions.

To create a data transfer for Search Ads 360 reporting:

Console

Go to the BigQuery page in the Google Cloud console.

Go to the BigQuery page
Click Transfers.
Click Create Transfer.
On the Create Transfer page:
- In the Source type section, for Source, choose Search Ads 360 (formerly Doubleclick Search).
- 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.
- In the Schedule options section:
  - For Repeat frequency, choose an option for how often to run the transfer. If you select Days, provide a valid time in UTC.
    - Hours
    - Days
    - On-demand
  - If applicable, select either Start now or Start at set time and provide a start date and run time.
- In the Destination settings section, for Destination dataset, choose the dataset you created to store your data.
- In the Data source details section:
  - For Agency ID, enter your Agency ID.
  - For Advertiser ID, enter your Advertiser ID.
  - Check include removed items to include removed entities and metrics to be transferred.
  - (Optional) For Table Filter, leave this field empty to load all tables. To import data for only some of the supported tables, list the name prefix of those tables, separated by whitespace. Example: Account AccountDeviceStats AccountFloodlightAndDeviceStats. See Search Ads 360 report transformation for all the supported table names.
  - (Optional) For Custom Floodlight Variables, leave this field blank to only import the columns predefined in Search Ads 360 report transformation.
    To import Custom Floodlight Variables that you've made in Search Ads 360, name those fields in JSON format, like this example:
    - ```
    [{
      "cfv_name": "variable_name",
      "cfv_type": "metric or dimension",
      "campaign_manager_type": "number or string",
      "destination_table_name": "table_name",
      "bigquery_column_name": "custom_column_name"
    },{
      "cfv_name": "variable_name_2",
      "cfv_type": "metric or dimension",
      "campaign_manager_type": "number or string",
      "destination_table_name": ["table_name_2","table_name_3"],
      "bigquery_column_name": "custom_column_name_2"
    }]
```
- For every item in the JSON list, include the following:
  - cfv_name is the "friendly" name that you specified when the custom Floodlight variable was created in Campaign Manager. The friendly name also appears in the Search Ads 360 UI when setting up custom dimensions and metrics.
  - cfv_type should be either metric or dimension. Match this to how you set up the custom Floodlight variable in the Search Ads 360 UI.
  - campaign_manager_type should be either number or string. Match this to how you set up the custom Floodlight variable set up in the Search Ads 360 UI.
  - destination_table_name is the name prefix or a list of name prefixes of the destination table that you want to add the custom Floodlight variable to. Example: AccountDeviceStats, [AdDeviceStats,CampaignFloodtlightAndDeviceStats].
  - bigquery_column_name is the column name this custom Floodlight variable will have in the BigQuery destination table. The column name has to follow the format requirements for BigQuery column names.
- (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.
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

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

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.
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 Search Ads 360, you must supply the advertiser_id and agency_id parameters. You may optionally set the following parameters:
- (Optional) Set table_filter to import data for only some of the supported tables, list the name prefix of those tables, separated by whitespace. Example: Account AccountDeviceStats AccountFloodlightAndDeviceStats. See Search Ads 360 report transformation for all the supported table names.
- (Optional) Set custom_floodlight_variables to import Custom Floodlight Variables that you've made in Search Ads 360, name those fields in JSON format, like this example:
  - ```
  [{
    "cfv_name": "variable_name",
    "cfv_type": "metric or dimension",
    "campaign_manager_type": "number or string",
    "destination_table_name": "table_name",
    "bigquery_column_name": "custom_column_name"
  },{
    "cfv_name": "variable_name_2",
    "cfv_type": "metric or dimension",
    "campaign_manager_type": "number or string",
    "destination_table_name": "table_name_2",
    "bigquery_column_name": "custom_column_name_2"
  }]
```
- For every item in the JSON list, include the following:
  - cfv_name is the "friendly" name that you specified when the custom Floodlight variable was created in Campaign Manager. The friendly name also appears in the Search Ads 360 UI when setting up custom dimensions and metrics.
  - cfv_type should be either metric or dimension. Match this to how you set up the custom Floodlight variable in the Search Ads 360 UI.
  - campaign_manager_type should be either number or string. Match this to how you set up the custom Floodlight variable set up in the Search Ads 360 UI.
  - destination_table_name is the name prefix of the destination table that you want to add the custom Floodlight variable to. Example: AccountDeviceStats.
  - bigquery_column_name is the column name this custom Floodlight variable will have in the BigQuery destination table. The column name has to follow the format requirements for BigQuery column names.
- (Optional) Set include_removed_entities to true to include removed entities and metrics to be transferred.
data_source is the data source — doubleclick_search.

For example, the following command creates a Search Ads transfer named My Transfer using Advertiser Id 123, Agency Id 123 and target dataset mydataset. The transfer is created in the default project:

bq mk \
--transfer_config \
--target_dataset=mydataset \
--display_name='My Transfer' \
--params='{"agency_id":"123", "advertiser_id":"123","include_removed_entities":"true"}' \
--data_source=doubleclick_search

The first time you run 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 in the message 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.

Manually trigger a Search Ads 360 transfer

When you manually trigger a transfer for Search Ads 360, snapshots of Match Tables are taken once a day and stored in the partition for the latest run date. When you schedule a manual transfer, Match Table snapshots for the following tables are not updated:

Account
Ad
Advertiser
AdGroup
AdGroupTarget
BidStrategy
Campaign
CampaignTarget
FeedItem
FloodlightActivity
Keyword
NegativeAdGroupKeyword
NegativeAdGroupTarget
NegativeCampaignKeyword
NegativeCampaignTarget
ProductAdvertised
ProductGroup
ProductLeadAndCrossSellFloodlightStats
Video
Placement
Video

Troubleshoot Search Ads 360 transfer setup

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

Query your data

When your data is transferred to BigQuery Data Transfer Service, 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.

Potential quota issues

The Google Search Ads 360 API assigns a daily quota for the number of requests a the Google project can send. If you are using one project for the BigQuery Data Transfer Service and for other services, both services will share quota, and potentially reach the quota limit, in either service.

To prevent this potential issue and not affect existing workflows, consider these options:

Set up a separate project for the BigQuery Data Transfer Service.

Cross project table join example:

  #standardSQL
  select count(a.item1)
  from (select item1, item2 from project-A.data_set_a.table_name_a) a
  inner join
  (select item3, item4 from project-B.data_set_b.table_name_b) b
  on a.item1 = b.item3

Contact Search Ads 360 support and request additional quota. For more information, see Request additional quota.