AdWords transfers
The BigQuery Data Transfer Service for AdWords allows you to automatically schedule and manage recurring load jobs for AdWords reporting data.
Supported Reports
For reports supported by BigQuery Data Transfer Service and information on how AdWords reports are transformed into BigQuery Data Transfer Service tables and views, see AdWords report transformations.
To map AdWords reports to what you see in the AdWords UI, see Mapping reports to the AdWords UI.
Reporting option | Support |
---|---|
Supported API version |
For information on AdWords API migration, see Migration Guide (v201809). |
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 AdWords has no known data retention limits except Click Performance Report, 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. |
Number of Customer IDs per manager account | 2,000 The BigQuery Data Transfer Service supports a maximum of 2000 Customer IDs per AdWords manager account (MCC). |
Before you begin
Before you create an AdWords 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 AdWords 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.
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.AdWords: Read access to the AdWords Customer ID or manager account (MCC) that is used in the transfer configuration.
Set up a AdWords data transfer
Setting up a data transfer for AdWords reporting requires a:
- Customer ID: Your AdWords Customer ID or manager account (MCC) as issued by AdWords.
To create a data transfer for AdWords 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 AdWords.
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 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.
In the Destination settings section, for Destination dataset, choose the dataset you created to store your data.
In the Data source details section:
- For Customer ID, enter your Customer ID or manager account.
Check Exclude removed/disabled Items to prevent removed or disabled entities and metrics from being transferred.
(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 AdWords, you must supply thecustomer_id
parameter. You may optionally set theexclude_removed_items
parameter totrue
to prevent removed or disabled entities and metrics from being transferred. - data_source is the data source —
adwords
.
For example, the following command creates a AdWords 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","exclude_removed_items":"true"}' \
--data_source=adwords
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.
Java
Before trying this sample, follow the Java setup instructions in the BigQuery quickstart using client libraries. For more information, see the BigQuery Java API reference documentation.
To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for a local development environment.
Support for AdWords manager accounts
Existing customers who have multiple Customer ID-specific AdWords Transfers are encouraged to set up a single AdWords Transfer at the Manager Account (MCC) level, schedule a backfill, and disable individual Customer ID-specific AdWords Transfers.
Using AdWords manager accounts provides several benefits over using individual Customer IDs:
- You no longer need to manage multiple transfers to report on multiple Customer IDs.
- Cross-customer queries are much simpler to write because all the Customer IDs are stored in the same table.
- Using MCCs alleviates BigQuery Data Transfer Service load quota issues because multiple Customer IDs are loaded in the same job.
Example
The following list shows the Customer IDs linked to particular AdWords 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
- 1234 — sub-manager account
Each Customer ID linked to a manager account appears in each report. For more information on the AdWords reporting structure in BigQuery Data Transfer Service, see AdWords report transformation.
Transfer configuration for Customer ID 1234567890
A transfer configuration for the root manager account (Customer ID 1234567890) would generate 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) would generate 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) would generate 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) would generate transfer runs that include the following Customer IDs:
- 8888
- 9999
Transfer configuration for Customer ID 0000
A transfer configuration for Customer ID 0000 would generate transfer runs that include only the individual Customer ID:
- 0000
Migrate AdWords data to MCCs
To migrate your existing AdWords data in BigQuery Data Transfer Service to the MCC structure, you can set up a backfill to add your existing data to the tables created by the transfer configuration linked to the manager account. Note that when you schedule a backfill, match tables are not updated.
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.
AdWords sample queries
You can use the following AdWords sample queries to analyze your transferred data. You can also use the queries in a visualization tool such as Looker Studio. These queries are provided to help you get started on querying your AdWords data with BigQuery Data Transfer Service. For additional questions on what you can do with these reports, contact your AdWords technical representative.
In each of the following queries, replace dataset with your dataset name. Replace customer_id with your AdWords Customer ID.
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 SQL sample query analyzes AdWords campaign performance for the past 30 days.
SELECT c.ExternalCustomerId, c.CampaignName, c.CampaignStatus, SUM(cs.Impressions) AS Impressions, SUM(cs.Interactions) AS Interactions, (SUM(cs.Cost) / 1000000) AS Cost FROM `dataset.Campaign_customer_id` c LEFT JOIN `dataset.CampaignBasicStats_customer_id` cs ON (c.CampaignId = cs.CampaignId 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
Count of keywords
The following SQL sample query analyzes keywords by campaign, ad group,
and keyword status. This query uses the KeywordMatchType
function. Keyword
match types help control which searches can trigger your ad. For more
information on keyword matching options, see
About keyword matching options.
SELECT c.CampaignStatus AS CampaignStatus, a.AdGroupStatus AS AdGroupStatus, k.Status AS KeywordStatus, k.KeywordMatchType AS KeywordMatchType, COUNT(*) AS count FROM `dataset.Keyword_customer_id` k JOIN `dataset.Campaign_customer_id` c ON (k.CampaignId = c.CampaignId AND k._DATA_DATE = c._DATA_DATE) JOIN `dataset.AdGroup_customer_id` a ON (k.AdGroupId = a.AdGroupId AND k._DATA_DATE = a._DATA_DATE) WHERE k._DATA_DATE = k._LATEST_DATE GROUP BY 1, 2, 3, 4