Load Search Ads 360 data into BigQuery

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

Connector overview

The BigQuery Data Transfer Service for the Search Ads 360 connector supports the following options for your data transfer.

Data transfer optionsSupport
Supported reports The Search Ads 360 connector supports the transfer of data from the reports inSearch Ads 360 v0 reports.

For information about how Search Ads 360 reports are transformed into BigQuery tables and views, seeSearch Ads 360 report transformation.

Repeat frequencyThe Search Ads 360 connector supports daily data transfers.

By default, data transfers are scheduled at the time when the data transfer is created. You can configure the time of data transfer when youset up your data transfer.
Refresh windowYou can schedule your data transfers to retrieve Search Ads 360 data from up to 30 days at the time the data transfer is run. You can configure the duration of the refresh window when youset up your data transfer.

By default, the Search Ads 360 connector has a refresh window of 7 days.

For more information, seeRefresh windows.

Snapshots ofMatch Tables are taken once a day and stored in the partition for the last run date. Match Table snapshots are not updated for backfills or for days loaded using the refresh window.

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 Search Ads 360, seeReporting data retention policy.
Number of Customer IDs per manager accountThe BigQuery Data Transfer Service supports a maximum of8000 Customer IDs for each Search Ads 360manager account.

To see the Search Ads 360 transfer guide that uses the old Search Ads 360 reporting API, seeSearch Ads 360 transfers (Deprecated).

Data ingestion from Search Ads 360 transfers

When you transfer data from Search Ads 360 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.

Limitations

  • The maximum frequency that you can configure a Search Ads 360data transfer for is once every24 hours. By default, a transfer starts at the time that you create thetransfer. However, you can configure the data transfer start time when youcreate your transfer.
  • The BigQuery Data Transfer Service does not support incremental data transfers during aSearch Ads 360 transfer. When you specify a date for a datatransfer, all of the data that is available for that date is transferred.

Before you begin

Before you create a Search Ads 360 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 Google Cloud roles

To download data from Search Ads 360, you must have theserviceusage.services.use permission. TheService Usage Consumer (roles/serviceusage.serviceUsageConsumer)predefined IAM role includes this permission.

Required Search Ads 360 roles

Grant read access to the Search Ads 360 Customer ID ormanageraccount that is usedin the transfer configuration. To configure read access for serviceaccounts, you cancontact Search Ads 360support for assistance.

Create a Search Ads 360 data transfer

To create a data transfer for Search Ads 360 reporting, you need either yourSearch Ads 360 Customer ID ormanager account.Select one of the following options:

Console

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

    Go to Data transfers

  2. ClickCreate transfer.

  3. In theSource type section, forSource, chooseSearch Ads 360.

  4. In theTransfer config name section, forDisplay name, enter aname for the data transfer such asMy Transfer. The transfer name can beany value that lets you identify the transfer if you need to modify itlater.

  5. In theSchedule options section:

    • ForRepeat frequency, choose an option for how often to run thedata transfer. If you selectDays, provide a valid time in UTC.
    • If applicable, select eitherStart now orStart at set time,and provide a start date and run time.

  6. In theDestination settings section, forDataset, select thedataset that you created to store your data.

  7. In theData source details section:

    1. ForCustomer ID, enter your Search Ads 360 customer ID.
    2. Optional: Enter both anAgency ID andAdvertiser ID toretrieveID mapping tables.

    3. Optional: ForCustom Floodlight Variables, enter anycustom Floodlight variablesto include in the data transfer. The custom floodlight variables mustbe owned by the Search Ads 360 account that is specified bythe Customer ID in the transfer config. This parameter takes stringinputs in JSON array format and can support multiple custom Floodlightvariables. In each item of the JSON array, the following parametersare required:

      • id: the numeric ID of the custom Floodlight variable. This IDis assigned whena custom Floodlight variable is created in Search Ads 360.If you have specified anid, then aname isn't required.
      • name: the user-defined name of the custom floodlight variables inSearch Ads 360. If you have specified aname, then anid isn't required.
      • cfv_field_name: the exact custom Floodlight variable fieldname based on your use case. The supported values areconversion_custom_metrics,conversion_custom_dimensions,raw_event_conversion_metrics, andraw_event_conversion_dimensions.
      • destination_table_name: a list of BigQuery tablesto include the custom floodlight variables in. When theBigQuery Data Transfer Service retrieves data for these tables, thetransfer includes the custom Floodlight variables in the query.
      • bigquery_column_name_suffix: the user-defined friendly columnname. The BigQuery Data Transfer Service appends the suffix afterthe standard field name to differentiate different customFloodlight variables. Depending on the use case, theBigQuery Data Transfer Service generates a BigQuerycolumn name as follows:
      Custom Floodlight variables as metrics and segmentsCustom Floodlight variables as Raw Event Attributes in the Conversion Resource
      metricsmetrics_conversion_custom_metrics_bigquery_column_name_suffixmetrics_raw_event_conversion_metrics_bigquery_column_name_suffix
      dimensionsegments_conversion_custom_dimensions_bigquery_column_name_suffixsegments_raw_event_conversion_dimensions_bigquery_column_name_suffix

      The following is an exampleCustom Floodlight Variable entrythat specifies two custom Floodlight variables:

      [{"id":"1234","cfv_field_name":"raw_event_conversion_metrics","destination_table_name":["Conversion"],"bigquery_column_name_suffix":"suffix1"},{"name":"example name","cfv_field_name":"conversion_custom_metrics","destination_table_name":["AdGroupConversionActionAndDeviceStats","CampaignConversionActionAndDeviceStats"],"bigquery_column_name_suffix":"suffix2"}]

    4. Optional: In theCustom Columns field, enter anycustom columnsto include in the data transfer. The custom columns must be owned bythe Search Ads 360 account that is specified by theCustomer ID in the transfer config. This field takes string inputs inJSON array format and can support multiple columns. In each item ofthe JSON array, the following parameters are required:

      • id: the numeric ID of the custom column. This ID is assignedwhen acustom column is created.If you have specified anid, then aname isn't required.
      • name: the user-defined name of the custom column inSearch Ads 360. If you have specified aname, then anid isn't required.
      • destination_table_name: a list of BigQuery tablesto include the custom column in. When the BigQuery Data Transfer Serviceretrieves data for these tables, the transfer includes the customcolumn field in the query.
      • bigquery_column_name: the user-defined friendly column name.This is the field name of the custom column in the destinationtables specified indestination_table_name. The column name mustfollow the format requirements for BigQuery column namesand must be unique to other fields in thetable's standard schemaor other custom columns.

      The following is an exampleCustom Columns entry that specifiestwo custom columns:

      [{"id":"1234","destination_table_name":["Conversion"],"bigquery_column_name":"column1"},{"name":"example name","destination_table_name":["AdGroupStats","CampaignStats"],"bigquery_column_name":"column2"}]

    5. Optional: In theTable Filter field, enter a comma-separated listof tables to include, for exampleCampaign, AdGroup. Prefix thislist with the- character to exclude certain tables, for example-Campaign, AdGroup. All tables are included by default.

    6. Optional: SelectInclude PMax Campaign Data to include PMaxcampaign data and excludesad_group fields from certain tables. Formore information, seePerformance Max (PMax)campaigns

    7. Optional: SelectUse Client Account Currency to use the currency ofthe client's account to load cost data, instead of the currency of theaccount used in this data transfer.

    8. Optional: ForRefresh window, enter a value between 1 and 30. Ifnot set, the refresh window defaults to 7 days. For more information,seeRefresh windows

  8. In theService Account menu, select aservice account from the serviceaccounts that areassociated with your Google Cloud project. You canassociate a service account with your transfer instead of using your usercredentials. For more information about using service accounts with datatransfers, seeUse service accounts.

    If you signed in with afederated identity, then aservice account is required to create a transfer. If you signed in with aGoogle Account, then a serviceaccount for the transfer is optional. The service account must have therequired permissions.

  9. Optional: In theNotification options section:

    • Click the toggle to enable email notifications. When you enable thisoption, the transfer administrator receives an email notification when atransfer run fails.
    • Click the toggle to enable Pub/Sub notifications. ForSelect a Cloud Pub/Sub topic, choose yourtopic name or clickCreate a topic. This option configures Pub/Sub runnotifications for yourtransfer.

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

The following flags are optional:

  • --project_id: Specifies which project to use. If the flag is notspecified, the default project is used.
  • --service_account_name: Specifies a service account to use for Search Ads 360transfer authentication instead of your user account.
bqmk\--transfer_config\--project_id=PROJECT_ID\--target_dataset=DATASET\--display_name=NAME\--data_source=DATA_SOURCE\--service_account_name=SERVICE_ACCOUNT_NAME\--params='{PARAMETERS,"custom_columns":"[{\"id\": \"CC_ID\",\"destination_table_name\": [\"CC_DESTINATION_TABLE\"],\"bigquery_column_name\": \"CC_COLUMN\"}]","custom_floodlight_variables":"[{\"id\": \"CFV_ID\",\"cfv_field_name\": [\"CFV_FIELD_NAME\"],\"destination_table_name\": [\"CFV_DESTINATION_TABLE\"],\"bigquery_column_name_suffix\": \"CFV_COLUMN_SUFFIX\"}]"}'

Where:

  • PROJECT_ID (Optional): specifies which project to use. If the flag is notspecified, the default project is used.
  • DATASET: the target dataset for the transfer configuration.

  • NAME: the display name for the transfer configuration. Thedata transfer name can be any value that lets you identify the transfer if youneed to modify it later.

  • DATA_SOURCE: the data source —search_ads.

  • SERVICE_ACCOUNT_NAME (Optional): the service account name used toauthenticate your data transfer. The serviceaccount should be owned by the sameproject_id used to create thetransfer and it should have all of therequired permissions.

  • PARAMETERS: the parameters for the created transferconfiguration in JSON format. For example:--params='{"param":"param_value"}'. Youmust supply thecustomer_id parameter.

    • table_filter: Specifies which tables to include in the data transfer. Ifthe flag is not specified, all tables are included. To include onlyspecific tables, use a comma-separated list of values (for example,Ad, Campaign, AdGroup). To exclude specific tables, prefix theexcluded values with a hyphen (-) (for example, using-Ad, Campaign, AdGroupexcludes all three values.)
    • custom_columns: specifies custom columns to your reports. Thisparameter takes string inputs in JSON array format and cansupport multiple columns. In each item of the JSON array, the followingparameters are required:
      • CC_ID: the numeric ID of the custom column. This ID is assigned whenacustom column is created.
      • CC_DESTINATION_TABLE: a list of BigQuery tables to include thecustom column in. When the BigQuery Data Transfer Service retrieves data forthese tables, the data transfer includes the custom column field in thequery.
      • CC_COLUMN: the user-defined friendly column name. This is the field name of the custom column in the destination tables specified indestination_table_name. The column name has tofollow the format requirements for BigQuery column names and must be unique to other fields in thetable's standard schema or other custom columns.
    • custom_floodlight_variables: specifiescustom Floodlight variablesin your transfer. This parameter takes string inputs in JSON array format and cansupport multiple custom Floodlight variables. In each item of the JSON array, the followingparameters are required:
      • CFV_ID: the numeric ID of the custom Floodlight variable. This ID is assigned whena custom Floodlight variable is created in Search Ads 360.
      • CFV_FIELD_NAME: the exact custom Floodlight variable field name based on your use case. The supported values areconversion_custom_metrics,conversion_custom_dimensions,raw_event_conversion_metrics andraw_event_conversion_dimensions.For more information, seeCustom Floodlight metrics.
      • CFV_DESTINATION_TABLE: a list of BigQuery tables to include thecustom floodlight variables in. When the BigQuery Data Transfer Service retrieves data forthese tables, the data transfer includes the custom Floodlight variables in thequery.
      • CFV_COLUMN_SUFFIX: the user-defined friendly column name. The BigQuery Data Transfer Service appends the suffix after the standard field name to differentiate different custom Floodlight variables. Depending on the use case, the BigQuery Data Transfer Service generates a BigQuery column name as follows:
    • use_client_account_currency: specifyTRUE to use the currency ofthe client's account to load cost data, instead of the currency of theaccount used in this data transfer.
    Custom Floodlight variables as metrics and segmentsCustom Floodlight variables as Raw Event Attributes in the Conversion Resource
    metricsmetrics_conversion_custom_metrics_bigquery_column_name_suffixmetrics_raw_event_conversion_metrics_bigquery_column_name_suffix
    dimensionsegments_conversion_custom_dimensions_bigquery_column_name_suffixsegments_raw_event_conversion_dimensions_bigquery_column_name_suffix
Caution: You cannot configure notifications using the command-line tool.

For example, the following command creates a Search Ads 360data transfer namedMy Transfer using Customer ID6828088731 and targetdatasetmydataset. The transfer also specifies a custom floodlight variable. Thedata transfer is created in the default project:

bqmk\--transfer_config\--target_dataset=mydataset\--display_name='My Transfer'\--data_source=search_ads\--params='{"customer_id":"6828088731", "custom_floodlight_variables":"[{\"id\": \"9876\", \"cfv_field_name\": \"raw_event_conversion_metrics\", \"destination_table_name\": [\"Conversion\"],\"bigquery_column_name_suffix\": \"suffix1\" }]"}'

The first time you run the command, you receive a message like thefollowing:

[URL omitted] Please copy and paste the above URL into your web browser andfollow the instructions to retrieve an authentication code.

Follow the instructions in the message and paste the authentication code onthe command line.

Caution: When you create a Search Ads 360 data transfer using the command-line tool,the transfer configuration is set up using the default values forSchedule (every 24 hours at creation time) andRefresh window (0— configures the default refresh window of 7 days).

API

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

Manually trigger a Search Ads 360 transfer

When youmanually trigger a transferfor Search Ads 360, snapshots of match tables are taken once a dayand stored in thepartition for the last run date. When you trigger a manual transfer, MatchTable snapshots for the following tables are not updated:

  • Account
  • Ad
  • AdGroup
  • AdGroupCriterion
  • AnyID mapping table
  • Asset
  • BidStrategy
  • Campaign
  • CampaignCriterion
  • ConversionAction
  • Keyword
  • NegativeAdGroupKeyword
  • NegativeAdGroupCriterion
  • NegativeCampaignKeyword
  • NegativeCampaignCriterion
  • ProductGroup

Performance Max (PMax) campaigns

The Search Ads 360 connector lets you exportPMaxcampaigns data. You mustselect theInclude PMax Campaign Data checkbox whencreating a data transfer, as PMax data is not exportedby default.

Including PMax data removesad_group fields from certain tables and includes new tables. You cannot includead_group fields because the Search Ads 360 API filters the PMax data.

The following tables excludead_group related columns when theInclude PMaxCampaign Tables checkbox is selected:

  • CartDataSalesStats
  • ProductAdvertised
  • ProductAdvertisedDeviceStats
  • ProductAdvertisedConversionActionAndDeviceStats

Support for Search Ads 360 manager accounts

Using Search Ads 360 manager accounts provides several benefits overusing individual Customer IDs:

  • You don't need to manage multiple data transfers to report on multipleCustomer IDs.
  • Cross-customer queries are simpler to write because all CustomerIDs are stored in the same table.
  • Using manager accounts alleviates BigQuery Data Transfer Service load quotaissues because multiple Customer IDs are loaded in the same job.

For existing customers who have multiple Customer ID-specific Search Ads 360data transfers, we recommend that you switch to a Search Ads 360 manager account instead.You can do this with the following steps:

  1. Set up a single Search Ads 360 data transfer at the manager or sub-manager accountlevel.
  2. Schedule a backfill.
  3. Disable individual Customer ID-specific Search Ads 360 transfers.

For more information about Search Ads 360 manager accounts, seeAbout manager accounts in the new Search Ads 360andSee how accounts are linked to your manager account.

Note: The BigQuery Data Transfer Service pulls reports for all listed Customer IDs, butyou may not see Customer IDs in your reports if it doesn't report activity forthe requested day.

Example

The following list shows the Customer IDs linked to particular Search Ads 360manager accounts:

  • 1234567890 — root manager account
    • 1234 — sub-manager account
      • 1111 — Customer ID
      • 2222 — Customer ID
      • 3333 — Customer ID
      • 4444 — Customer ID
      • 567 — sub-manager account
        • 5555 — Customer ID
        • 6666 — Customer ID
        • 7777 — Customer ID
    • 89 — sub-manager account
      • 8888 — Customer ID
      • 9999 — Customer ID
    • 0000 — Customer ID

Each Customer ID is linked to a manager account appears in each report. For moreinformation about the Search Ads 360 reporting structure inBigQuery Data Transfer Service, seeSearch Ads 360 report transformation.

Transfer configuration for Customer ID 1234567890

A transfer configuration for the root manager account (Customer ID 1234567890)generates data transfer runs that include the following Customer IDs:

  • 1111 (via sub-manager account 1234)
  • 2222 (via sub-manager account 1234)
  • 3333 (via sub-manager account 1234)
  • 4444 (via sub-manager account 1234)
  • 5555 (via sub-manager account 567 and sub-manager account 1234)
  • 6666 (via sub-manager account 567 and sub-manager account 1234)
  • 7777 (via sub-manager account 567 and sub-manager account 1234)
  • 8888 (via sub-manager account 89)
  • 9999 (via sub-manager account 89)
  • 0000 (individual Customer ID)

Transfer configuration for Customer ID 1234

A transfer configuration for sub-manager account 123 (Customer ID 1234) generatesdata transfer runs that include the following Customer IDs:

  • 1111
  • 2222
  • 3333
  • 4444
  • 5555 (via sub-manager account 567)
  • 6666 (via sub-manager account 567)
  • 7777 (via sub-manager account 567)

Transfer configuration for Customer ID 567

A transfer configuration for sub-manager account 567 (Customer ID 567) generatesdata transfer runs that include the following Customer IDs:

  • 5555
  • 6666
  • 7777

Transfer configuration for Customer ID 89

A transfer configuration for sub-manager account 89 (Customer ID 89) generatesdata transfer runs that include the following Customer IDs:

  • 8888
  • 9999

Transfer configuration for Customer ID 0000

A transfer configuration for Customer ID 0000 generates data transfer runs thatinclude only the individual Customer ID:

  • 0000

Query your data

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

Search Ads 360 sample queries

You can use the following Search Ads 360 sample queries to analyze your transferreddata. You can also view the queries in a visualization tool such asLooker Studio.

The following queries are examples to get started querying your Search Ads 360 datawith BigQuery Data Transfer Service. For additional questions about what you can dowith these reports, contact your Search Ads 360 technical representative.

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 performance

The following sample query analyzes Search Ads 360 campaign performance for the past30 days.

SELECTc.customer_id,c.campaign_name,c.campaign_status,SUM(cs.metrics_clicks)ASClicks,(SUM(cs.metrics_cost_micros)/1000000)ASCost,SUM(cs.metrics_impressions)ASImpressionsFROM`DATASET.sa_Campaign_CUSTOMER_ID`cLEFTJOIN`DATASET.sa_CampaignStats_CUSTOMER_ID`csON(c.campaign_id=cs.campaign_idANDcs._DATA_DATEBETWEENDATE_ADD(CURRENT_DATE(),INTERVAL-31DAY)ANDDATE_ADD(CURRENT_DATE(),INTERVAL-1DAY))WHEREc._DATA_DATE=c._LATEST_DATEGROUPBY1,2,3ORDERBYImpressionsDESC

Replace the following:

  • DATASET: the name of the dataset
  • CUSTOMER_ID: the Search Ads 360 customer ID

Count of keywords

The following sample query analyzes keywords by campaign, ad group, and keywordstatus.

SELECTc.campaign_statusASCampaignStatus,a.ad_group_statusASAdGroupStatus,k.ad_group_criterion_statusASKeywordStatus,k.ad_group_criterion_keyword_match_typeASKeywordMatchType,COUNT(*)AScountFROM`DATASET.sa_Keyword_CUSTOMER_ID`kJOIN`DATASET.sa_Campaign_CUSTOMER_ID`cON(k.campaign_id=c.campaign_idANDk._DATA_DATE=c._DATA_DATE)JOIN`DATASET.sa_AdGroup_CUSTOMER_ID`aON(k.ad_group_id=a.ad_group_idANDk._DATA_DATE=a._DATA_DATE)WHEREk._DATA_DATE=k._LATEST_DATEGROUPBY1,2,3,4

Replace the following:

  • DATASET: the name of the dataset
  • CUSTOMER_ID: the Search Ads 360 customer ID

ID mapping tables

Entities in the new Search Ads 360, such as customers, campaigns, and ad groups,have a differentID spacethan the old Search Ads 360. For existing Search Ads 360 transferusers who want to combine data from the old Search Ads 360 with thenew Search Ads 360 API, you can use the BigQuery Data Transfer Service to transferID mapping tables if you provide a valid agency ID and advertiser ID in thetransfer configuration.

Supported entitiescontain two columns,legacy_id andnew_id, which specifies the ID mappingfor entities in old and new versions of Search Ads 360 respectively.For the AD, CAMPAIGN_CRITERION, and CRITERION entities, anew_secondary_id isalso provided as these entitiesdon't have globally unique ids in the newSearch Ads 360.The following is a list of ID mapping tables.

  • IdMapping_AD
  • IdMapping_AD_GROUP
  • IdMapping_CAMPAIGN
  • IdMapping_CAMPAIGN_CRITERION
  • IdMapping_CAMPAIGN_GROUP
  • IdMapping_CAMPAIGN_GROUP_PERFORMANCE_TARGET
  • IdMapping_CRITERION
  • IdMapping_CUSTOMER
  • IdMapping_FEED_ITEM
  • IdMapping_FEED_TABLE
Note: Similar to match tables, snapshots of ID mapping tables are taken once aday and stored in the partition for the latest run date. ID mapping tablesnapshots are not updated for backfills or for days loaded using the refreshwindow.

Example queries

The following query makes use of ID mapping tables to aggregate per-campaignmetrics across tables from previous and new Search Ads 360 data transfers in thenew ID space.

SELECTCustomerID,CampaignID,Sum(Clicks),Sum(Cost)FROM(SELECTcs.customer_idASCustomerID,cs.campaign_idASCampaignID,cs.metrics_clicksASClicks,cs.metrics_cost_micros/1000000ASCostFROM`DATASET.sa_CampaignStats_CUSTOMER_ID`csWHEREcs._DATA_DATE='NEW_DATA_DATE'UNIONALLSELECTcustomer_id_mapping.new_idASCustomerID,campaign_id_mapping.new_idASCampaignID,cs.clicksASClicks,cs.costASCostFROM`DATASET.CampaignStats_ADVERTISER_ID`csLEFTJOIN`DATASET.IdMapping_CUSTOMER_ADVERTISER_ID`customer_id_mappingONcs.accountId=customer_id_mapping.legacy_idLEFTJOIN`DATASET.IdMapping_CAMPAIGN_ADVERTISER_ID`campaign_id_mappingONcs.campaignId=campaign_id_mapping.legacy_idWHEREcs._DATA_DATE='OLD_DATA_DATE')GROUPBY1,2ORDERBY1,2

Replace the following:

  • DATASET: the name of the dataset
  • CUSTOMER_ID: the Search Ads 360 customer ID
  • ADVERTISER_ID: the Search Ads 360 advertiser ID
  • NEW_DATA_DATE: the data date for the new Search Ads 360table
  • OLD_DATA_DATE: the data date for the previousSearch Ads 360 table

The following query makes use of ID mapping tables to aggregate per-campaignmetrics across tables from previous and new Search Ads 360 data transfers in theold ID space.

SELECTCustomerID,CampaignID,Sum(Clicks),Sum(Cost)FROM(SELECTcustomer_id_mapping.legacy_idASCustomerID,campaign_id_mapping.legacy_idASCampaignID,cs.metrics_clicksASClicks,cs.metrics_cost_micros/1000000ASCostFROM`DATASET.sa_CampaignStats_CUSTOMER_ID`csLEFTJOIN`DATASET.IdMapping_CUSTOMER_ADVERTISER_ID`customer_id_mappingONcs.customer_id=customer_id_mapping.new_idLEFTJOIN`DATASET.IdMapping_CAMPAIGN_ADVERTISER_ID`campaign_id_mappingONcs.campaign_id=campaign_id_mapping.new_idWHEREcs._DATA_DATE='NEW_DATA_DATE'UNIONALLSELECTCAST(accountIdASINT)ASCustomerID,CAST(campaignIdASINT)ASCampaignID,cs.clicksASClicks,cs.costASCostFROM`DATASET.CampaignStats_ADVERTISER_ID`csWHEREcs._DATA_DATE='OLD_DATA_DATE')GROUPBY1,2ORDERBY1,2

Replace the following:

  • DATASET: the name of the dataset
  • CUSTOMER_ID: the Search Ads 360 customer ID
  • ADVERTISER_ID: the Search Ads 360 advertiser ID
  • NEW_DATA_DATE: the data date for the new Search Ads 360table
  • OLD_DATA_DATE: the data date for the previousSearch Ads 360 table

Potential quota issues

The Search Ads 360 reporting API assigns aquotafor the number of requests that the Google project can send. If you are using oneproject for the BigQuery Data Transfer Service and other services, all services share thesame quota and can potentially reach the quota limit in any service.

To prevent this potential issue without affecting existing workflows, considerthese options:

  • Use thetable_filter parameter to load only the tables that are needed.

  • Set up a separate project for the BigQuery Data Transfer Service. A cross projecttable join might look like the following:

    #standardSQLselectcount(a.item1)from(selectitem1,item2fromproject-A.data_set_a.table_name_a)ainnerjoin(selectitem3,item4fromproject-B.data_set_b.table_name_b)bona.item1=b.item3

  • ContactSearch Ads 360 supportand request additional quota.

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.