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:
- Search Ads 360 account report
- Search Ads 360 ad report
- Search Ads 360 advertiser report
- Search Ads 360 adGroup report
- Search Ads 360 adGroupTarget report
- Search Ads 360 bidStrategy report
- Search Ads 360 campaign report
- Search Ads 360 campaignTarget report
- Search Ads 360 conversion report
- Search Ads 360 feedItem report
- Search Ads 360 floodlightActivity report
- Search Ads 360 keyword report
- Search Ads 360 negativeAdGroupKeyword report
- Search Ads 360 negativeAdGroupTarget report
- Search Ads 360 negativeCampaignKeyword report
- Search Ads 360 negativeCampaignTarget report
- Search Ads 360 productAdvertised report
- Search Ads 360 productGroup report
- Search Ads 360 productLeadAndCrossSell report
- Search Ads 360 visit report
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 | |
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
andbigquery.datasets.update
permissions on the target dataset
The
bigquery.admin
predefined IAM role includesbigquery.transfers.update
,bigquery.datasets.update
andbigquery.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
andserviceusage.serviceUsageConsumer
predefined IAM roles includeserviceusage.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.
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 theadvertiser_id
andagency_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.
- (Optional) Set
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 fromproject-B.data_set_b.table_name_b
) b on a.item1 = b.item3Contact Search Ads 360 support and request additional quota. For more information, see Request additional quota.