Search Ads 360 transfers

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:

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
    • bigquery.datasets.update permissions on the target dataset

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

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

Setting up a Search Ads 360 data transfer

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

To create a data transfer for Search Ads 360 reporting:

Console

  1. Go to the BigQuery page in the Cloud Console.

    Go to the BigQuery page

  2. Click Transfers.

  3. Click Create Transfer.

  4. On the Create Transfer page:

    • In the Source type section, for Source, choose Search Ads 360 (formerly Doubleclick Search).

      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 Schedule options section, for Schedule, leave the default value (Start now) or click Start at a set time.

      • For Repeats, choose an option for how often to run the transfer.

        • Daily (default)
        • Weekly
        • Monthly
        • Custom
        • On-demand
      • For Start date and run time, enter the date and time to start the transfer. If you choose Start now, this option is disabled.

        Transfer schedule

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

        Search Ads 360 source details

      • (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",
            "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 Data Transfer Service destination table. The column name has to follow the [format requirements for BigQuery Data Transfer Service column names](https://cloud.google.com/bigquery/docs/schemas#column_names

        Search Ads 360 CFV Console

    • (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

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 Data Transfer Service destination table. The column name has to follow the format requirements for BigQuery Data Transfer Service 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_items":"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.

Troubleshooting Search Ads 360 transfer setup

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

Querying 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 pseudo-column 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.