BigQuery Data Transfer Service for Google AdWords

Overview

The BigQuery Data Transfer Service for Google AdWords allows you to automatically schedule and manage recurring load jobs for Adwords reporting data. Currently, the BigQuery Data Transfer Service supports Google Adwords v201705 reports.

For information on AdWords API migration, see:

By default, AdWords data transfers adhere to the following schedule:

  • Transfers created before May 24, 2017 occur daily at approximately 6:07 UTC.
  • Transfers created after May 24, 2017 occur daily at the time the transfer was created.
  • By default, data for the past 7 days is refreshed on a daily basis. When you configure the transfer, you can choose up to 30 days of data.

Before you begin

  1. Sign in to your Google account.

    If you don't already have one, sign up for a new account.

  2. Select or create a Cloud Platform project.

    Go to the Manage resources page

  3. Enable billing for your project.

    Enable billing

  4. BigQuery is automatically enabled in new projects. To activate BigQuery in a pre-existing project, Enable the BigQuery API.

    Enable the API

  5. Create a BigQuery dataset to store the AdWords data.
  6. The user creating your transfers should be granted the bigquery.admin role for the transfers to succeed. You can apply roles using the IAM & Admin console.
  7. IAM & Admin

Before using the BigQuery Transfer Service, you must complete the enrollment form. To complete the form, you need the project number for the project containing your transfers.

  1. Open the Cloud Platform Console.
  2. Cloud Platform Console
  3. If your transfer project isn't selected, at the top of the window, click the project name.
  4. In the Select project dialog, select your project and then click Open.
  5. On the Dashboard tab, in the Project Info box, note your project number (below your project ID). The project number is a string of numerals.
  6. Project number
  7. Complete the BigQuery Data Transfer Service enrollment form.

The enrollment request must be completed by the BigQuery team before you can use the service. Requests are generally completed within 1 business day but may take up to 3 business days depending on the volume of requests. Please contact your Cloud Sales Representative if you have questions about the status of your enrollment request.

Create an AdWords data transfer

Setting up a data transfer for AdWords reporting requires your customer ID and read access to your AdWords account. For information on retrieving your AdWords customer ID, see: Find your customer ID.

To create a data transfer for AdWords reporting:

  1. Go to the BigQuery web UI.

    Go to the BigQuery web UI

  2. Click Transfers.

  3. Click Enable to grant permissions to the BigQuery Data Transfer Service. The first time you enable transfers in a project, you are required to grant permissions to the Data Transfer Service. After permissions are granted, you are not prompted again.

    Enable transfers

  4. Click Add Transfer.

  5. On the New Transfer page:

    • For Source, choose AdWords Reports.
    • For Destination, choose the appropriate dataset.
    • 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 incude 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 30. For more information, see Determining your data's freshness.
    • For Customer ID, enter your customer ID.

      Channel transfer

  6. Click Add.

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

    Allow transfer

    Once the transfer is added, you can edit the transfer, delete the transfer, update the credentials, schedule a backfill, or view the transfer properties such as the next scheduled transfer in the BigQuery web UI.

    Transfer properties

    As your scheduled transfers are run, the BigQuery web UI displays the run history, including successful transfers and transfers that fail. Scheduled runs more than 90 days old are automatically deleted from the run history.

    Run history

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

Set up a backfill

To transfer historical Google AdWords data to BigQuery, you must request a backfill. The BigQuery Data Transfer Service supports backfilling up to 180 days at a time. When backfilling large date ranges, break your backfill requests into 180 day chunks and wait for the previous backfill request to finish before creating another one.

To set up a backfill:

  1. Go to the BigQuery web UI.
  2. BigQuery web UI
  3. Click Transfers.
  4. On the Transfer page, click the appropriate transfer.
  5. Click Schedule Backfill.
  6. Schedule backfill
  7. In the Schedule Run dialog:
    • For Data Start Date, enter or choose the appropriate date in MM/DD/YYYY format.
    • For Data End Date, enter or choose the appropriate date in MM/DD/YYYY format.
    • Click Ok.
    • Schedule backfill

Google AdWords report transformation

When your Google AdWords reports are transferred to BigQuery, the reports are transformed into the following BigQuery tables:

Google AdWords report BigQuery table(s)
ACCOUNT_PERFORMANCE_REPORT

Customer

HourlyAccountConversionStats

AccountConversionStats

HourlyAccountStats

AccountNonClickStats

AccountBasicStats

AccountStats

AD_PERFORMANCE_REPORT

AdBasicStats

AdCrossDeviceStats

AdConversionStats

AdStats

AdCrossDeviceConversionStats

Ad

ADGROUP_PERFORMANCE_REPORT

AdGroupStats

AdGroupBasicStats

AdGroupCrossDeviceStats

HourlyAdGroupConversionStats

HourlyAdGroupStats

AdGroupConversionStats

AdGroupCrossDeviceConversionStats

AdGroup

AUDIENCE_PERFORMANCE_REPORT

Audience

AudienceConversionStats

AudienceNonClickStats

AudienceBasicStats

AudienceStats

CAMPAIGN_LOCATION_TARGET_REPORT

CampaignLocationTargetStats

LocationBasedCampaignCriterion

CAMPAIGN_PERFORMANCE_REPORT

Campaign

CampaignBasicStats

CampaignConversionStats

CampaignCrossDeviceStats

HourlyCampaignConversionStats

CampaignStats

HourlyCampaignStats

CampaignCrossDeviceConversionStats

CampaignCookieStats

CLICK_PERFORMANCE_REPORT ClickStats
GEO_PERFORMANCE_REPORT

GeoConversionStats

GeoStats

KEYWORDS_PERFORMANCE_REPORT

Keyword

KeywordBasicStats

KeywordCrossDeviceStats

KeywordStats

KeywordCrossDeviceConversionStats

KeywordConversionStats

PAID_ORGANIC_QUERY_REPORT PaidOrganicStats
PLACEMENT_PERFORMANCE_REPORT

PlacementBasicStats

PlacementNonClickStats

PlacementStats

Placement

PlacementConversionStats

SEARCH_QUERY_PERFORMANCE_REPORT

SearchQueryStats

SearchQueryConversionStats

SHOPPING_PERFORMANCE_REPORT

ShoppingProductConversionStats

ShoppingProductStats

VIDEO_PERFORMANCE_REPORT

VideoBasicStats

VideoConversionStats

VideoStats

Video

VideoNonClickStats

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 Google Cloud Datalab or Google Data Studio 360.

In each of the following queries, replace [DATASET] with your dataset name. Replace [CUSTOMER_ID] with your AdWords 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.

Campaign performance

The following sample query analyzes AdWords 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'

Disabling a transfer

To disable a transfer:

  1. Go to the BigQuery web UI.

    Go to the BigQuery web UI

  2. Click Transfers.

  3. Click the transfer you're disabling.
  4. On the transfer properties page, click Disabled.

    Disable transfer

  5. After disabling the transfer, click Save.

When you disable a transfer, [DISABLED] is added to the transfer name. When the transfer is disabled, no new transfer runs are scheduled. Any transfer runs in progress are completed.

Disabling a transfer does not remove any data already transferred to BigQuery. Data previously transferred incurs standard BigQuery storage costs until you delete the dataset or delete the tables.

Deleting a transfer

To delete a transfer:

  1. Go to the BigQuery web UI.

    Go to the BigQuery web UI

  2. Click Transfers.

  3. Click the transfer you're editing.
  4. On the transfer properties page, click Delete.
  5. When prompted, click Ok to remove the transfer.

When the transfer is deleted, you are no longer charged for transferring data. Deleting a transfer does not remove any data already transferred to BigQuery. Data previously transferred incurs standard BigQuery storage costs until you delete the dataset or delete the tables.

Monitor your resources on the go

Get the Google Cloud Console app to help you manage your projects.

Send feedback about...