Search Ads 360 transfers

The BigQuery Data Transfer Service for Search Ads 360 lets you automatically schedule and manage recurring load jobs for Search Ads 360 reporting data.

Supported reports

The BigQuery Data Transfer Service for Search Ads 360 supports Search Ads 360 reporting API v0:

Search Ads 360 v0 reports

For information about 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	v0
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 last run date. Match Table snapshots are not updated for backfills or for days loaded using the refresh window.
Maximum backfill duration	No limit
Number of Customer IDs per manager account	2,000 The BigQuery Data Transfer Service supports a maximum of 2000 Customer IDs for each Search Ads 360 manager account.

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

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 reporting 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 reporting API in your project.

Required permissions

Ensure that the use 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.
Search Ads 360:
- Read access to the Search Ads 360 Customer ID or manager account that is used in the transfer configuration.

Create a Search Ads 360 data transfer

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

Console

Go to the BigQuery page in the Google Cloud console.

Go to the BigQuery page

Click Data transfers.
Click Create transfer.
In the Source type section, for Source, choose Search Ads 360 - Preview.
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 lets you 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 Dataset, select the dataset that you created to store your data.
In the Data source details section:
For Customer ID, enter your Search Ads 360 customer ID:
Optional: Enter a Agency ID and Advertiser ID (both must be specified) to retrieve ID mapping tables.
Optional: Enter a comma-separated list of tables to include, for example Campaign, AdGroup. Prefix this list with the - character to exclude certain tables, for example -Campaign, AdGroup. All tables are included by default.
Optional: For Refresh window, enter a value between 1 and 30. If not set, the refresh window defaults to 7 days.
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.
- Click the toggle to enable Pub/Sub notifications. For Select a Cloud 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

The following flags are optional:

--project_id: Specifies which project to use. If the flag is not specified, the default project is used.
--table_filter: Specifies which tables to include in the transfer. If the flag is not specified, all tables are included. To include only specific tables, use a comma-separated list of values (for example, Ad, Campaign, AdGroup). To exclude specific tables, prefix the values with a hyphen (-) (for example, -Ad, Campaign, AdGroup).

bq mk \
--transfer_config \
--project_id=project_id \
--target_dataset=dataset \
--display_name=name \
--params='parameters' \
--data_source=data_source

Where:

project_id: your project ID.
dataset: the target dataset for the transfer configuration.
name: the display name for the transfer configuration. The transfer name can be any value that lets you identify the transfer if you need to modify it later.
parameters: the parameters for the created transfer configuration in JSON format. For example: --params='{"param":"param_value"}'. You must supply the customer_id parameter.
data_source: the data source — search_ads.

For example, the following command creates a Search Ads 360 transfer named My Transfer using Customer ID 123-123-1234 and target dataset mydataset. The transfer is created in the default project:

bq mk \
--transfer_config \
--target_dataset=mydataset \
--display_name='My Transfer' \
--params='{"customer_id":"123-123-1234"}' \
--data_source=search_ads

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 last run date. When you trigger a manual transfer, Match Table snapshots for the following tables are not updated:

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

Support for Search Ads 360 manager accounts

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

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

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

Set up a single Search Ads 360 transfer at the manager or sub-manager account level.
Schedule a backfill.
Disable individual Customer ID-specific Search Ads 360 transfers.

For more information about Search Ads 360 manager accounts, see About manager accounts in the new Search Ads 360 and See how accounts are linked to your manager account.

Example

The following list shows the Customer IDs linked to particular Search Ads 360 manager 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 more information about the Search Ads 360 reporting structure in BigQuery Data Transfer Service, see Search Ads 360 report transformation.

Transfer configuration for Customer ID 1234567890

A transfer configuration for the root manager account (Customer ID 1234567890) generates 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) generates 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) generates 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) generates transfer runs that include the following Customer IDs:

8888
9999

Transfer configuration for Customer ID 0000

A transfer configuration for Customer ID 0000 generates transfer runs that include only the individual Customer ID:

0000

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 pseudo-column in your query. For more information, see Querying partitioned tables.

Search Ads 360 sample queries

You can use the following Search Ads 360 sample queries to analyze your transferred data. You can also view the queries in a visualization tool such as Looker Studio.

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

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.

Campaign performance

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

SELECT
  c.customer_id,
  c.campaign_name,
  c.campaign_status,
  SUM(cs.metrics_clicks) AS Clicks,
  (SUM(cs.metrics_cost_micros) / 1000000) AS Cost,
  SUM(cs.metrics_impressions) AS Impressions
FROM
  `DATASET.sa_Campaign_CUSTOMER_ID` c
LEFT JOIN
  `DATASET.sa_CampaignStats_CUSTOMER_ID` cs
ON
  (c.campaign_id = cs.campaign_id
  AND cs._DATA_DATE BETWEEN
  DATE_ADD(CURRENT_DATE(), INTERVAL -31 DAY) AND DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY))
WHERE
  c._DATA_DATE = c._LATEST_DATE
GROUP BY
  1, 2, 3
ORDER BY
  Impressions DESC

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 keyword status.

  SELECT
    c.campaign_status AS CampaignStatus,
    a.ad_group_status AS AdGroupStatus,
    k.ad_group_criterion_status AS KeywordStatus,
    k.ad_group_criterion_keyword_match_type AS KeywordMatchType,
    COUNT(*) AS count
  FROM
    `DATASET.sa_Keyword_CUSTOMER_ID` k
    JOIN
    `DATASET.sa_Campaign_CUSTOMER_ID` c
  ON
    (k.campaign_id = c.campaign_id AND k._DATA_DATE = c._DATA_DATE)
  JOIN
    `DATASET.sa_AdGroup_CUSTOMER_ID` a
  ON
    (k.ad_group_id = a.ad_group_id AND k._DATA_DATE = a._DATA_DATE)
  WHERE
    k._DATA_DATE = k._LATEST_DATE
  GROUP BY
    1, 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 different ID space than the old Search Ads 360. For existing Search Ads 360 transfer users who want to combine data from the old Search Ads 360 with the new Search Ads 360 API, you can use the BigQuery Data Transfer Service to transfer ID mapping tables if you provide a valid agency ID and advertiser ID in the transfer configuration.

Supported entities contain two columns, legacy_id and new_id, which specifies the ID mapping for entities in old and new versions of Search Ads 360 respectively. 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

Example queries

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

SELECT CustomerID, CampaignID, Sum(Clicks), Sum(Cost) FROM
(SELECT
  cs.customer_id AS CustomerID,
  cs.campaign_id AS CampaignID,
  cs.metrics_clicks AS Clicks,
  cs.metrics_cost_micros / 1000000 AS Cost
FROM
  `DATASET.sa_CampaignStats_CUSTOMER_ID` cs
WHERE cs._DATA_DATE = 'NEW_DATA_DATE'
UNION ALL
SELECT
  customer_id_mapping.new_id AS CustomerID,
  campaign_id_mapping.new_id AS CampaignID,
  cs.clicks AS Clicks,
  cs.cost AS Cost
FROM
  `DATASET.CampaignStats_ADVERTISER_ID` cs
LEFT JOIN
  `DATASET.IdMapping_CUSTOMER_ADVERTISER_ID` customer_id_mapping
ON cs.accountId = customer_id_mapping.legacy_id
LEFT JOIN
  `DATASET.IdMapping_CAMPAIGN_ADVERTISER_ID` campaign_id_mapping
ON cs.campaignId = campaign_id_mapping.legacy_id
WHERE cs._DATA_DATE = 'OLD_DATA_DATE')
GROUP BY
1, 2
ORDER BY
1, 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 360 table
OLD_DATA_DATE: the data date for the previous Search Ads 360 table

The following query makes use of ID mapping tables to aggregate per-campaign metrics across tables from previous and new Search Ads 360 transfers in the old ID space.

SELECT CustomerID, CampaignID, Sum(Clicks), Sum(Cost) FROM
(SELECT
  customer_id_mapping.legacy_id AS CustomerID,
  campaign_id_mapping.legacy_id AS CampaignID,
  cs.metrics_clicks AS Clicks,
  cs.metrics_cost_micros / 1000000 AS Cost
FROM
  `DATASET.sa_CampaignStats_CUSTOMER_ID` cs
LEFT JOIN
  `DATASET.IdMapping_CUSTOMER_ADVERTISER_ID` customer_id_mapping
ON cs.customer_id = customer_id_mapping.new_id
LEFT JOIN
  `DATASET.IdMapping_CAMPAIGN_ADVERTISER_ID` campaign_id_mapping
ON cs.campaign_id = campaign_id_mapping.new_id
WHERE cs._DATA_DATE = 'NEW_DATA_DATE'
UNION ALL
SELECT
  CAST(accountId AS INT) AS CustomerID,
  CAST(campaignId AS INT) AS CampaignID,
  cs.clicks AS Clicks,
  cs.cost AS Cost
FROM
  `DATASET.CampaignStats_ADVERTISER_ID` cs
WHERE cs._DATA_DATE = 'OLD_DATA_DATE')
GROUP BY
1, 2
ORDER BY
1, 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 360 table
OLD_DATA_DATE: the data date for the previous Search Ads 360 table

Potential quota issues

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

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

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

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