Google Ads transfers

The BigQuery Data Transfer Service for Google Ads connector (formerly known as Google AdWords) lets you automatically schedule and manage recurring load jobs for Google Ads reporting data. To learn about recent data source changes, see BigQuery Data Transfer Service data source change log.

Supported reports

The BigQuery Data Transfer Service for Google Ads supports Google Ads API v16:

Google Ads v16 reports

For information about how Google Ads reports are transformed into BigQuery Data Transfer Service 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	v16
Repeat frequency	Daily, at the time the data 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 While Google Ads 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 about backfills, see Manually trigger a transfer.
Number of Customer IDs per manager account	8,000 The BigQuery Data Transfer Service supports a maximum of 8000 Customer IDs for each Google Ads manager account (MCC).

Data ingestion from Google Ads transfers

When you transfer data from Google Ads into BigQuery, the data is loaded into BigQuery tables that are partitioned by date. The table partition that the data is loaded into corresponds to the date from the data source. If you schedule multiple transfers for the same date, BigQuery Data Transfer Service overwrites the partition for that specific date with the latest data. Multiple transfers in the same day or running backfills don't result in duplicate data, and partitions for other dates are not affected.

Refresh windows

A refresh window is the number of days that a data transfer retrieves data when a data transfer occurs. For example, if the refresh window is three days and a daily transfer occurs, the BigQuery Data Transfer Service retrieves all data from your source table from the past three days. In this example, when a daily transfer occurs, the BigQuery Data Transfer Service creates a new BigQuery destination table partition with a copy of your source table data from the current day, then automatically triggers backfill runs to update the BigQuery destination table partitions with your source table data from the past two days. The automatically triggered backfill runs will either overwrite or incrementally update your BigQuery destination table, depending on whether or not incremental updates are supported in the BigQuery Data Transfer Service connector.

When you run a data transfer for the first time, the data transfer retrieves all source data available within the refresh window. For example, if the refresh window is three days and you run the data transfer for the first time, the BigQuery Data Transfer Service retrieves all source data within three days.

Refresh windows are mapped to the TransferConfig.data_refresh_window_days API field.

To retrieve data outside the refresh window, such as historical data, or to recover data from any transfer outages or gaps, you can initiate or schedule a backfill run.

Limitations

The maximum frequency that you can configure a Google Ads data transfer for is once every 24 hours. By default, a transfer starts at the time that you create the transfer. However, you can configure the transfer start time when you create your transfer.
The BigQuery Data Transfer Service does not support incremental data transfers during a Google Ads transfer. When you specify a date for a data transfer, all of the data that is available for that date is transferred.

Before you begin

Before you create a Google Ads data transfer, do the following:

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 Google Ads data.
If you intend to set up transfer run notifications for Pub/Sub, ensure that you have the pubsub.topics.setIamPolicy permission. Pub/Sub permissions are not required if you set up email notifications. For more information, see BigQuery Data Transfer Service run notifications.

Required permissions

Ensure that the person creating the data transfer has the following required permissions:

BigQuery Data Transfer Service:
- bigquery.transfers.update permissions to create the data 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 about IAM roles in BigQuery Data Transfer Service, see Access control reference.
Google Ads: Read access to the Google Ads Customer ID or manager account (MCC) that is used in the transfer configuration.

Create Google Ads data transfer

To create a data transfer for Google Ads reporting, you need either your Google Ads customer ID or your manager account (MCC). For information about retrieving your Google Ads customer ID, see Find your Customer ID.

To create a data transfer for Google Ads reporting, select one of the following options:

Console

Go to the Data transfers page in the Google Cloud console.

Go to Data transfers
Click Create transfer.
In the Source type section, for Source, choose Google Ads.
In the Transfer config name section, for Display name, enter a name for the data 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 data transfer. If you select Days, provide a valid time in UTC.
- 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:
1. For Customer ID, enter your Google Ads customer ID.
2. Optional: Select options to exclude removed or deactivated items and include tables new to Google Ads.
3. 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.
4. Optional: Select the option to include tables specific to PMax reports. For more information about PMax support, see PMax support.
5. Optional: For Refresh window, enter a value between 1 and 30.
In the Service Account menu, select a service account from the service accounts associated with your Google Cloud project. You can associate a service account with your data transfer instead of using your user credentials. For more information about using service accounts with data transfers, see Use service accounts.
- If you signed in with a federated identity, then a service account is required to create a transfer. If you signed in with a Google Account, then a service account for the transfer is optional.
- The service account must have the required permissions.
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 if 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 data 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).
--schedule: Specifies how often the query runs. If you don't specify --schedule, the default is set to every 24 hours. For information about the schedule syntax, see Formatting the schedule.
--refresh_window_days: Specifies the refresh window for a transfer configuration in days. The default value is 7.
--service_account_name: Specifies a service account to use for the Google Ads transfer authentication instead of your user account.

bq mk \
--transfer_config \
--project_id=PROJECT_ID \
--target_dataset=DATASET \
--display_name=NAME \
--params='PARAMETERS' \
--data_source=DATA_SOURCE \
--table_filter=TABLES \
--schedule=SCHEDULE
--refresh_window_days=REFRESH_DAYS
--service_account_name=SERVICE_ACCOUNT_NAME

Where:

PROJECT_ID is your project ID.
DATASET is the target dataset for the data transfer configuration.
NAME is the display name for the data transfer configuration. The transfer name can be any value that lets you 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 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 — google_ads.
TABLES is the comma-separated list of tables to include or exclude from the data transfer.
SCHEDULE is how often you want the query to run. If --schedule isn't specified, the default is every 24 hours, starting from the time the transfer is created.
REFRESH_DAYS is an integer that specifies the refresh window for a transfer configuration in days. The default value is 7.
SERVICE_ACCOUNT_NAME is the service account name used to authenticate your transfer. The service account must be owned by the same project_id used to create the transfer and it must have all of the required permissions.

For example, the following command creates a Google Ads data transfer named My Transfer using Customer ID 123-123-1234 and target dataset mydataset. The data 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=google_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.

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 client libraries.

import com.google.api.gax.rpc.ApiException;
import com.google.cloud.bigquery.datatransfer.v1.CreateTransferConfigRequest;
import com.google.cloud.bigquery.datatransfer.v1.DataTransferServiceClient;
import com.google.cloud.bigquery.datatransfer.v1.ProjectName;
import com.google.cloud.bigquery.datatransfer.v1.TransferConfig;
import com.google.protobuf.Struct;
import com.google.protobuf.Value;
import java.io.IOException;
import java.util.HashMap;
import java.util.Map;

// Sample to create ads(formerly AdWords) transfer config
public class CreateAdsTransfer {

  public static void main(String[] args) throws IOException {
    // TODO(developer): Replace these variables before running the sample.
    final String projectId = "MY_PROJECT_ID";
    String datasetId = "MY_DATASET_ID";
    // the customer_id only allows digits and hyphen ('-').
    String customerId = "012-345-6789";
    String refreshWindow = "100";
    Map<String, Value> params = new HashMap<>();
    params.put("customer_id", Value.newBuilder().setStringValue(customerId).build());
    params.put("refreshWindow", Value.newBuilder().setStringValue(refreshWindow).build());
    TransferConfig transferConfig =
        TransferConfig.newBuilder()
            .setDestinationDatasetId(datasetId)
            .setDisplayName("Your Ads Transfer Config Name")
            .setDataSourceId("adwords")
            .setParams(Struct.newBuilder().putAllFields(params).build())
            .build();
    createAdsTransfer(projectId, transferConfig);
  }

  public static void createAdsTransfer(String projectId, TransferConfig transferConfig)
      throws IOException {
    try (DataTransferServiceClient client = DataTransferServiceClient.create()) {
      ProjectName parent = ProjectName.of(projectId);
      CreateTransferConfigRequest request =
          CreateTransferConfigRequest.newBuilder()
              .setParent(parent.toString())
              .setTransferConfig(transferConfig)
              .build();
      TransferConfig config = client.createTransferConfig(request);
      System.out.println("Ads transfer created successfully :" + config.getName());
    } catch (ApiException ex) {
      System.out.print("Ads transfer was not created." + ex.toString());
    }
  }
}

Manually trigger a Google Ads transfer

When you manually trigger a transfer for Google Ads, snapshots of Match Tables are taken once a day and stored in the partition for the latest run date. When you trigger a manual transfer, Match Table snapshots for the following tables are not updated:

Ad
AdGroup
AdGroupAudience
AdGroupBidModifier
AdGroupAdLabel
AdGroupCriterion
AdGroupCriterionLabel
AdGroupLabel
AgeRange
Asset
AssetGroup
AssetGroupAsset
AssetGroupListingGroupFilter
AssetGroupSignal
Audience
BidGoal
Budget
Campaign
CampaignAudience
CampaignCriterion
CampaignLabel
Customer
Gender
Keyword
LocationBasedCampaignCriterion
ParentalStatus
Placement
Video

PMax support

The Google Ads connector lets you export PMax campaigns data. You must select the Include PMax Campaign Tables checkbox when creating a data transfer, as PMax data is not exported by default.

Including PMax data removes ad_group fields from certain tables and includes new tables. You cannot include ad_group fields because the Google Ads API filters the PMax data.

The following tables exclude ad_group related columns when the Include PMax Campaign Tables checkbox is selected:

GeoStats
GeoConversionStats
ShoppingProductConversionStats
ShoppingProductStats
LocationsUserLocationsStats

The following tables are added when the Include PMax Campaign Tables checkbox is selected:

Asset
AssetGroup
AssetGroupAsset
AssetGroupListingGroupFilter
AssetGroupSignal
Audience
AssetGroupProductGroupStats
CampaignAssetStats

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

For more information about 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 about the Google Ads reporting structure in BigQuery Data Transfer Service, see Google Ads report transformation.

Transfer configuration for Customer ID 1234567890

A transfer configuration for the root manager account (Customer ID 1234567890) would generate data 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 data 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 data 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 data 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 data transfer runs that include only the individual Customer ID:

0000

Migrate Google Ads data to MCCs

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

Troubleshoot Google Ads transfer setup

If you are having issues setting up your data transfer, see Google Ads transfer issues in Troubleshooting transfer configurations.

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

Google Ads sample queries

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 Looker Studio. These queries are provided to help you get started on querying your Google Ads data with BigQuery Data Transfer Service. For additional questions about 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.

If you query your tables directly instead of using the auto-generated views, you must use the _PARTITIONTIME pseudocolumn 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.

Console

SELECT
  c.customer_id,
  c.campaign_name,
  c.campaign_status,
  SUM(cs.metrics_impressions) AS Impressions,
  SUM(cs.metrics_interactions) AS Interactions,
  (SUM(cs.metrics_cost_micros) / 1000000) AS Cost
FROM
  `DATASET.ads_Campaign_CUSTOMER_ID` c
LEFT JOIN
  `DATASET.ads_CampaignBasicStats_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

bq

  bq query --use_legacy_sql=false '
  SELECT
    c.customer_id,
    c.campaign_name,
    c.campaign_status,
    SUM(cs.metrics_impressions) AS Impressions,
    SUM(cs.metrics_interactions) AS Interactions,
    (SUM(cs.metrics_cost_micros) / 1000000) AS Cost
  FROM
    `DATASET.ads_Campaign_CUSTOMER_ID` c
  LEFT JOIN
    `DATASET.ads_CampaignBasicStats_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'

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 about keyword matching options, see About keyword matching options.

Console

  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.ads_Keyword_CUSTOMER_ID` k
    JOIN
    `DATASET.ads_Campaign_CUSTOMER_ID` c
  ON
    (k.campaign_id = c.campaign_id AND k._DATA_DATE = c._DATA_DATE)
  JOIN
    `DATASET.ads_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

bq

  bq query --use_legacy_sql=false '
  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.ads_Keyword_CUSTOMER_ID` k
  JOIN
    `DATASET.ads_Campaign_CUSTOMER_ID` c
  ON
    (k.campaign_id = c.campaign_id AND k._DATA_DATE = c._DATA_DATE)
  JOIN
    `DATASET.ads_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'