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.

To schedule an AdWords data transfer, you must apply for access to the AdWords API.

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

  • Transfers occur daily at approximately 8:40 UTC.
  • 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 Projects 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.

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. 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 > Add Transfer.

  3. 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. Entering 1 prevents daily transfers. Entering 0 configures the default refresh window of 7 days. The maximum value is 30.
    • For Customer ID, enter your customer ID.

      Channel transfer

  4. Click Add.

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

Set up a backfill

To transfer historical Google AdWords data to BigQuery, you must request a backfill. You can schedule an AdWords data transfer backfill up to the past 180 days.

By default, AdWords backfills adhere to the following schedule:

  • Daily at approximately 8:40 UTC

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

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].CampaignStats_[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].CampaignStats_[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'

Send feedback about...

BigQuery Documentation