Load Google Ads data into BigQuery

You can load data from Google Ads (formerly known asGoogle AdWords) to BigQuery using theBigQuery Data Transfer Service for Google Ads connector. With theBigQuery Data Transfer Service, you can schedule recurring transfer jobs thatadd your latest data from your Google Ads toBigQuery.

To learn about recent datasource changes, seeBigQuery Data Transfer Service data source changelog.

Connector overview

The BigQuery Data Transfer Service for the Google Ads connector supports the following options for your data transfer.

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

Data ingestion from Google Ads transfers

Refresh windows

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

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

To retrieve data outside the refresh window, such as historical data, or torecover data from any transfer outages or gaps, you can initiate or schedule abackfill run.

Limitations

• The maximum frequency that you can configure a Google Adsdata transfer for is once every24 hours. By default, a transfer starts at the time that you create thetransfer. However, you can configure the transfer start time when youcreate your transfer.
• The BigQuery Data Transfer Service does not support incremental data transfers during aGoogle Ads transfer. When you specify a date for a datatransfer, 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 toenable the BigQuery Data Transfer Service.
• Create a BigQuery Data Transfer Service datasetto store the Google Ads data.
• If you intend to set up transfer run notifications for Pub/Sub,ensure that you have thepubsub.topics.setIamPolicy permission.Pub/Sub permissions are not required if you set up emailnotifications. For more information, seeBigQuery Data Transfer Service run notifications.

Required permissions

Ensure that you have granted the following permissions.

Required BigQuery roles

To get the permissions that you need to create a BigQuery Data Transfer Service data transfer, ask your administrator to grant you theBigQuery Admin (roles/bigquery.admin) IAM role on your project. For more information about granting roles, seeManage access to projects, folders, and organizations.

This predefined role contains the permissions required to create a BigQuery Data Transfer Service data transfer. To see the exact permissions that are required, expand theRequired permissions section:

You might also be able to get these permissions withcustom roles or otherpredefined roles.

For more information, seeGrantbigquery.admin access.

Required Google Ads roles

You must grant read access to the Google Ads Customer ID ormanager account (MCC)that is used in the transfer configuration.

To authorize a service account for your Google Ads data transfer, we recommend that you grant the service account direct-account access to Google Ads. For more information, seeAuthorization with direct account access

Create Google Ads data transfer

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

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

bqmk\--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

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

importcom.google.api.gax.rpc.ApiException;importcom.google.cloud.bigquery.datatransfer.v1.CreateTransferConfigRequest;importcom.google.cloud.bigquery.datatransfer.v1.DataTransferServiceClient;importcom.google.cloud.bigquery.datatransfer.v1.ProjectName;importcom.google.cloud.bigquery.datatransfer.v1.TransferConfig;importcom.google.protobuf.Struct;importcom.google.protobuf.Value;importjava.io.IOException;importjava.util.HashMap;importjava.util.Map;// Sample to create ads(formerly AdWords) transfer configpublicclassCreateAdsTransfer{publicstaticvoidmain(String[]args)throwsIOException{// TODO(developer): Replace these variables before running the sample.finalStringprojectId="MY_PROJECT_ID";StringdatasetId="MY_DATASET_ID";// the customer_id only allows digits and hyphen ('-').StringcustomerId="012-345-6789";StringrefreshWindow="100";Map<String,Value>params=newHashMap<>();params.put("customer_id",Value.newBuilder().setStringValue(customerId).build());params.put("refreshWindow",Value.newBuilder().setStringValue(refreshWindow).build());TransferConfigtransferConfig=TransferConfig.newBuilder().setDestinationDatasetId(datasetId).setDisplayName("Your Ads Transfer Config Name").setDataSourceId("adwords").setParams(Struct.newBuilder().putAllFields(params).build()).build();createAdsTransfer(projectId,transferConfig);}publicstaticvoidcreateAdsTransfer(StringprojectId,TransferConfigtransferConfig)throwsIOException{try(DataTransferServiceClientclient=DataTransferServiceClient.create()){ProjectNameparent=ProjectName.of(projectId);CreateTransferConfigRequestrequest=CreateTransferConfigRequest.newBuilder().setParent(parent.toString()).setTransferConfig(transferConfig).build();TransferConfigconfig=client.createTransferConfig(request);System.out.println("Ads transfer created successfully :"+config.getName());}catch(ApiExceptionex){System.out.print("Ads transfer was not created."+ex.toString());}}}

Manually trigger a Google Ads transfer

When youmanually trigger a transferfor Google Ads, snapshots of Match Tables are taken once a dayand stored in the partition for the latest run date. When you trigger amanual 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

Custom reports

The BigQuery Data Transfer Service for Google Ads connector alsosupports the use of custom reports using Google Ads QueryLanguage (GAQL) queries in the Google Ads transfer configuration.These custom reports ingest data from all resources available in theGoogle Ads API version supported by the BigQuery Data Transfer Service.For more information about using and validating a GAQL query, seeGoogle Ads Query Builder.

You can specify custom reports when youCreate a Google Ads transfer.

Custom reports limitations

Custom reports with the Google Ads connector are subject to thefollowing limitations:

• The Google Ads connector doesn't supportWHERE,ORDER BY,LIMIT, andPARAMETERS clauses. Your GAQL query should be in the formatsimilar to the following:SELECTFIELD_NAME,FIELD_NAME,... FROMRESOURCE_NAME.
• The Google Ads connector automatically appendsWHEREsegments.date =run_date when there is a core date segment(for example,segments.date,segments.week,segments.month,segments.quarter,segments.year) in the query. This can cause theGoogle Ads Query Validatorto return an error, for example,The filtering conditions in the WHERE clause mustcombine to form a valid, finite date range composed of the core date segments.... You can safely ignore these errors.
• GAQL queries without asegments.date fieldacts asmatch tables,which are only updated once per day and are not supported in backfill runs. Ifyou want to backfill data, you must include asegments.date field in theGAQL query.
• The Google Ads connector supports up to 100 custom reports ina single transfer.

Performance Max (PMax) campaigns

The Google Ads connector lets you exportPMaxcampaigns data. You mustselect theInclude PMax Campaign Tables checkbox whencreating a data transfer, as PMax data is not exportedby default.

Including PMax data removesad_group fields from certain tables and includes new tables. You cannot includead_group fields because the Google Ads API filters the PMax data.

The following tables excludead_group related columns when theInclude PMaxCampaign Tables checkbox is selected:

• GeoStats
• GeoConversionStats
• ShoppingProductConversionStats
• ShoppingProductStats
• LocationsUserLocationsStats

The following tables are added when theInclude PMax Campaign Tablescheckbox 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 Transfersare encouraged to set up a single Google Ads Transfer at the Manager Account(MCC) level, schedule a backfill, and disable individual Customer ID-specificGoogle Ads Transfers.

Using Google Ads manager accounts provides several benefits over usingindividual Customer IDs:

• You no longer need to manage multiple data transfers to report on multipleCustomer IDs.
• Cross-customer queries are much simpler to write because all the Customer IDsare stored in the same table.
• Using MCCs alleviates BigQuery Data Transfer Service load quota issues becausemultiple Customer IDs are loaded in the same job.

For more information about Google Ads manager accounts (MCCs), seeWorking with managed accountsandAbout linking accounts to your manager account.

Each Customer ID linked to a manager account appears in each report. For moreinformation about the Google Ads reporting structure in BigQuery Data Transfer Service,seeGoogle 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) wouldgenerate 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) wouldgenerate 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) wouldgenerate 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 thatinclude 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 theMCC structure, you canset up a backfillto add your existing data to the tables created by the transfer configurationlinked to the manager account. Note that when you schedule a backfill, matchtables are not updated.

Troubleshoot Google Ads transfer setup

If you are having issues setting up your data transfer, seeGoogle Ads transfer issuesinTroubleshooting transfer configurations.

Query your data

When your data is transferred to BigQuery Data Transfer Service, the data iswritten to ingestion-time partitioned tables. For more information, seeIntroduction to partitioned tables.

If you query your tables directly instead of using the auto-generated views, youmust use the_PARTITIONTIME pseudocolumn in your query. For more information,seeQuerying partitioned tables.

Google Ads sample queries

You can use the following Google Ads sample queries to analyze your transferreddata. You can also use the queries in a visualization tool such asLooker Studio.These queries are provided to help you get started on querying your Google Adsdata with BigQuery Data Transfer Service. For additional questions about what you cando with these reports, contact your Google Ads technical representative.

If you query your tables directly instead of using the auto-generatedviews, you must use the_PARTITIONTIME pseudocolumn in your query. For moreinformation, seeQuerying partitioned tables.

Campaign performance

The following sample query analyzes Google Ads campaign performance for the past30 days.

SELECTc.customer_id,c.campaign_name,c.campaign_status,SUM(cs.metrics_impressions)ASImpressions,SUM(cs.metrics_interactions)ASInteractions,(SUM(cs.metrics_cost_micros)/1000000)ASCostFROM`DATASET.ads_Campaign_CUSTOMER_ID`cLEFTJOIN`DATASET.ads_CampaignBasicStats_CUSTOMER_ID`csON(c.campaign_id=cs.campaign_idANDcs._DATA_DATEBETWEENDATE_ADD(CURRENT_DATE(),INTERVAL-31DAY)ANDDATE_ADD(CURRENT_DATE(),INTERVAL-1DAY))WHEREc._DATA_DATE=c._LATEST_DATEGROUPBY1,2,3ORDERBYImpressionsDESC

bqquery--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'

Replace the following:

• DATASET: the name of the dataset that you created tostore the transferred table
• CUSTOMER_ID: your Google Ads CustomerID.

Count of keywords

The following sample query analyzes keywords by campaign, ad group, and keywordstatus. This query uses theKeywordMatchType function. Keyword match typeshelp control which searches can trigger your ad. For more information about keywordmatching options, seeAbout keyword matching options.

SELECTc.campaign_statusASCampaignStatus,a.ad_group_statusASAdGroupStatus,k.ad_group_criterion_statusASKeywordStatus,k.ad_group_criterion_keyword_match_typeASKeywordMatchType,COUNT(*)AScountFROM`DATASET.ads_Keyword_CUSTOMER_ID`kJOIN`DATASET.ads_Campaign_CUSTOMER_ID`cON(k.campaign_id=c.campaign_idANDk._DATA_DATE=c._DATA_DATE)JOIN`DATASET.ads_AdGroup_CUSTOMER_ID`aON(k.ad_group_id=a.ad_group_idANDk._DATA_DATE=a._DATA_DATE)WHEREk._DATA_DATE=k._LATEST_DATEGROUPBY1,2,3,4

bqquery--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'

Replace the following:

• DATASET: the name of the dataset that you created tostore the transferred table
• CUSTOMER_ID: your Google Ads CustomerID.