Google Ads Transfers

The BigQuery Data Transfer Service for Google Ads (formerly known as Google AdWords) allows you to automatically schedule and manage recurring load jobs for Google Ads reporting data.

Supported Reports

The BigQuery Data Transfer Service for Google Ads currently supports the following reporting options:

For information on how Google Ads reports are transformed into BigQuery tables and views, see Google Ads Report Transformations.

To map Google Ads reports to what you see in the Google Ads UI, see Mapping Reports to the Google Ads UI.

Reporting option Support
Supported API version

v201806

For information on Google Ads API migration, see Migration Guide (v201806).

Schedule

Daily, at the time the Transfer is first created (default)

You can configure the time of day.

Refresh window

Last 7 days

Configurable up to 7 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 Google Ads 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.

Number of Customer IDs per manager account

1,000

The BigQuery Data Transfer Service supports a maximum of 1000 Customer IDs per Google Ads manager account (MCC).

Before you begin

Before you create a Google Ads transfer:

  • Verify that you have completed all actions required to enable the BigQuery Data Transfer Service.
  • Create a BigQuery dataset to store the Google Ads data.
  • Ensure that the person creating the transfer has the following required permissions:
    • Google Ads: Read access to the Google Ads Customer ID or manager account (MCC) that is used in the transfer configuration.
    • Google Cloud Platform: bigquery.transfers.update permissions to create the transfer and bigquery.datasets.update permissions on the target dataset. The bigquery.admin predefined, project-level IAM role includes bigquery.transfers.update and bigquery.datasets.update permissions. For more information on IAM roles in BigQuery, see Access Control.
    • BigQuery: bigquery.dataOwner and bigquery.admin
  • If you intend to create transfers using the BigQuery web UI, allow pop-ups from bigquery.cloud.google.com so you can view the permissions window. You must allow the BigQuery Data Transfer Service permission to manage your Google Ads campaigns for the transfer to succeed.
  • Transfer run notifications are currently in Alpha. If you intend to setup transfer run notifications for Cloud Pub/Sub, you must have pubsub.topics.setIamPolicy permissions. Cloud Pub/Sub permissions are not required if you just set up email notifications. For more information, see BigQuery Data Transfer Service Run Notifications.

Setting up a Google Ads data transfer

Setting up a data transfer for Google Ads reporting requires a:

  • Customer ID: Your Google Ads Customer ID or manager account (MCC) as issued by Google Ads. For information on retrieving your Google Ads Customer ID, see Find your Customer ID.

To create a data transfer for Google Ads reporting:

Web UI

  1. Go to the BigQuery web UI.

    Go to the BigQuery web UI

  2. Click Transfers.

  3. Click Add Transfer.

  4. On the New Transfer page:

    • For Source, choose Google Ads Reports.
    • For Destination, choose the dataset you created to store your Google Ads data.
    • 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.
    • For Refresh window, enter the number of days to include in the data transfer. If you enter 1, there is no daily backfill. Entering 0 configures the default refresh window of 7 days. The maximum value is 7. For more information, see Determining your data's freshness.
    • 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.

      Google Ads transfer

    • For Schedule, leave the default value (every 24 hours at transfer creation time) or click Edit and change the time.

      Google Ads schedule

    • (Optional) Expand the Advanced section and configure run notifications for your transfer. Transfer run notifications are currently in Alpha.

      • For Cloud Pub/Sub topic, enter your topic name, for example, projects/myproject/topics/mytopic.
      • Check Send email notifications to allow email notifications of transfer run failures.
      • Don't check Disabled when setting up a transfer. See Working with transfers to disable existing transfers.

        Cloud Pub/Sub topic

  5. Click Add.

  6. When prompted, click Allow to give the BigQuery Transfer Service permission to manage your Google Ads campaigns. You must allow pop-ups from bigquery.cloud.google.com to view the permissions window.

    Allow transfer

Command-line

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.
  • --target_dataset is the target dataset for the transfer configuration.
  • --display_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.
  • --params contains the parameters for the created transfer configuration in JSON format. For example: --params='{"param":"param_value"}'. For Google Ads, you must supply the customer_id parameter. You may optionally set the exclude_removed_items parameter to true 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 Google Ads 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.

Support for Google Ads manager accounts

Existing customers who have multiple Customer ID-specific Google Ads Transfers are encouraged to set up a single Google Ads Transfer at the Manager Account (MCC) level, schedule a backfill, and disable individual Customer ID-specific Google Ads Transfers.

Using Google Ads 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 load quota issues because multiple Customer IDs are loaded in the same job.

For more information on Google Ads manager accounts (MCCs), see Working with managed accounts and About linking accounts to your manager account.

Example

The following list shows the Customer IDs linked to particular Google Ads 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 linked to a manager account appears in each report. For more information on the Google Ads reporting structure in BigQuery, see Google Ads 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

Migrating Google Ads data to MCCs

To migrate your existing Google Ads data in BigQuery 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.

Troubleshooting Google Ads transfer setup

If you are having issues setting up your transfer, see Google Ads transfer issues in Troubleshooting BigQuery Data Transfer Service Transfer Setup.

Querying your data

When your data is transferred to BigQuery, the data is written to ingestion-time partitioned tables. For more information, see 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.

You can use the following Google Ads sample queries to analyze your transferred data. You can also use the queries in a visualization tool such as Google Cloud Datalab or Google Data Studio 360. These queries are provided to help you get started on querying your Google Ads data with BigQuery. For additional questions on what you can do with these reports, contact your Google Ads technical representative.

In each of the following queries, replace [DATASET] with your dataset name. Replace [CUSTOMER_ID] with your Google Ads Customer ID.

These samples use BigQuery’s support for standard SQL. Use the #standardSQL tag to let BigQuery know you want to use standard SQL. For more information about the #standardSQL prefix, see Setting a query prefix.

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 Google Ads campaign performance for the past 30 days.

Web UI

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

Command-line

bq query --use_legacy_sql=false '
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 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.

Web UI

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

Command-line

bq query --use_legacy_sql=false '
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'
¿Te sirvió esta página? Envíanos tu opinión:

Enviar comentarios sobre…