Copy data from Google Ads using Azure Data Factory or Synapse Analytics
APPLIES TO: Azure Data Factory Azure Synapse Analytics
Try out Data Factory in Microsoft Fabric, an all-in-one analytics solution for enterprises. Microsoft Fabric covers everything from data movement to data science, real-time analytics, business intelligence, and reporting. Learn how to start a new trial for free!
This article outlines how to use the Copy Activity in an Azure Data Factory or Synapse Analytics pipeline to copy data from Google Ads. It builds on the copy activity overview article that presents a general overview of copy activity.
This Google Ads connector is supported for the following capabilities:
|Copy activity (source/-)
① Azure integration runtime ② Self-hosted integration runtime
For a list of data stores that are supported as sources/sinks, see the Supported data stores table.
The service provides a built-in driver to enable connectivity, therefore you don't need to manually install any driver using this connector.
To perform the Copy activity with a pipeline, you can use one of the following tools or SDKs:
- The Copy Data tool
- The Azure portal
- The .NET SDK
- The Python SDK
- Azure PowerShell
- The REST API
- The Azure Resource Manager template
Create a linked service to Google Ads using UI
Use the following steps to create a linked service to Google Ads in the Azure portal UI.
Browse to the Manage tab in your Azure Data Factory or Synapse workspace and select Linked Services, then click New:
Search for Google Ads and select the Google Ads connector.
Configure the service details, test the connection, and create the new linked service.
Connector configuration details
The following sections provide details about properties that are used to define Data Factory entities specific to Google Ads connector.
Linked service properties
The following properties are supported for Google Ads linked service:
|The type property must be set to: GoogleAdWords
|The Google Ads API version that you use when you select the recommended driver version. You can refer this article for API version information.
|The Client customer ID of the Ads account that you want to fetch report data for.
|The customer ID of the Google Ads manager account through which you want to fetch report data of specific customer.
|The developer token associated with the manager account that you use to grant access to the Ads API. You can choose to mark this field as a SecureString to store it securely, or store password in Azure Key Vault and let the copy activity pull from there when performing data copy - learn more from Store credentials in Key Vault.
|The OAuth 2.0 authentication mechanism used for authentication.
Allowed values are: ServiceAuthentication, UserAuthentication.
ServiceAuthentication can only be used on self-hosted IR.
|The refresh token obtained from Google for authorizing access to Ads for UserAuthentication. You can choose to mark this field as a SecureString to store it securely, or store password in Azure Key Vault and let the copy activity pull from there when performing data copy - learn more from Store credentials in Key Vault.
|The client ID of the Google application used to acquire the refresh token. You can choose to mark this field as a SecureString to store it securely, or store password in Azure Key Vault and let the copy activity pull from there when performing data copy - learn more from Store credentials in Key Vault.
|The client secret of the google application used to acquire the refresh token. You can choose to mark this field as a SecureString to store it securely, or store password in Azure Key Vault and let the copy activity pull from there when performing data copy - learn more from Store credentials in Key Vault.
|The service account email ID that is used for ServiceAuthentication and can only be used on self-hosted IR.
|The service private key that is used for ServiceAuthentication for recommended driver version and can only be used on self-hosted IR. You can choose to mark this field as a SecureString to store it securely, or store password in Azure Key Vault and let the copy activity pull from there when performing data copy - learn more from Store credentials in Key Vault.
|For ServiceAuthentication using the legacy driver version:
|The service account email ID that is used for ServiceAuthentication and can only be used on self-hosted IR.
|The full path to the
.json key file that is used to authenticate the service account email address and can only be used on self-hosted IR.
|The full path of the .pem file containing trusted CA certificates for verifying the server when connecting over TLS. This property can only be set when using TLS on self-hosted IR. The default value is the cacerts.pem file installed with the IR.
|Specifies whether to use a CA certificate from the system trust store or from a specified PEM file. The default value is false.
For a full list of sections and properties available for defining datasets, see the datasets article. This section provides a list of properties supported by Google Ads dataset.
To copy data from Google Ads, set the type property of the dataset to GoogleAdWordsObject. The following properties are supported:
|The type property of the dataset must be set to: GoogleAdWordsObject
|Name of the table. Specify this property when you use the legacy driver version.
|No (if "query" in activity source is specified)
"referenceName": "<GoogleAds linked service name>",
Copy activity properties
For a full list of sections and properties available for defining activities, see the Pipelines article. This section provides a list of properties supported by Google Ads source.
Google Ads as source
To copy data from Google Ads set the source type in the copy activity to GoogleAdWordsSource. The following properties are supported in the copy activity source section:
|The type property of the copy activity source must be set to: GoogleAdWordsSource
|Use the GAQL query to read data. For example:
SELECT campaign.id FROM campaign.
|No (if "tableName" in dataset is specified)
"referenceName": "<GoogleAds input dataset name>",
"referenceName": "<output dataset name>",
"query": "SELECT campaign.id FROM campaign"
"type": "<sink type>"
Lookup activity properties
To learn details about the properties, check Lookup activity.
Upgrade the Google Ads driver version
To upgrade your Google Ads driver version, you need update your linked service and learn how to migrate from SQL to Google Ads Query Language (GAQL).
Update the linked service configuration
In Edit linked service page, select Recommended under Driver version and configure the linked service by referring to Linked service properties.
Migrate from SQL to GAQL
Convert your query statements and field names when migrating from SQL to GAQL.
If you use SQL query in the copy activity source or the lookup activity that refers to the legacy Google Ads linked service, you need to update them to GAQL query.
In contrast to SQL, the query in GAQL is made up of six kinds of clauses:
Go to Google Ads Query Language Grammar for the introduction of GAQL.
Take the following SQL statement as an example:
SELECT *|FieldName FROM ResourceName WHERE FieldName Operator Value
You can follow the guidance below to convert the SQL statement to the corresponding GAQL statement:
*(asterisk) is used after the
SELECTclause, then you need to specify all the required fields in place of the asterisk as GAQL doesn't support
SELECT *. Go to this article to see all the selectable fields in the specific resource.
- If the field name is used after the
SELECTclause, then you need to convert the name to the corresponding field name in GAQL as they have different naming conventions. For example, the field name
campaign_idin SQL query statement should be converted to
campaign.idin GAQL. See Field name for more details on field name conversion.
- The resource name can be left as it is unless its case is inconsistent with what is specified here.
WHEREclause should be updated according to the GAQL grammar as the operators supported by GAQL are not consistent with SQL, and field name should also be converted as described in the second point.
Here are two very useful tools offered by Google and they are highly recommended when building the corresponding GAQL query statements:
The field name used in SQL is not aligned with GAQL. You also need to learn the conversion rules from field names in SQL to field names in GAQL. The conversion rule can be summarized as follows:
If the field name belongs to a resource, the underscore (
_) in SQL will be changed to dot (
.) in GAQL. And for the words between the dot, the camelCase type statement used in SQL will be changed to standalone words with added underscores in between. The first string of type PascalCase in SQL will be changed to the corresponding resource name in GAQL.
If the field name belongs to segments or metrics, the prefix
metrics.should be added in GAQL, then follow the same rule as described in the first point to convert the name.
Here are the concrete examples of the field name conversion:
|Field names in SQL
|Field names in GAQL
Upgrade Google AdWords connector to Google Ads connector
Upgrade your Google AdWords linked service to the latest Google Ads linked service following the steps below:
Select Recommended as driver version to create a new Google Ads linked service and configure it by referring to Linked service properties.
Update your pipelines that refer to the legacy Google AdWords linked service. Considering that the Google Ads linked service only supports using query to copy data, so:
If your pipeline is directly retrieving data from the report of Google AdWords, find the corresponding resource name of Google Ads in the table below and use this tool to build the query.
Google AdWords report Google Ads resource ACCOUNT_PERFORMANCE_REPORT customer AD_PERFORMANCE_REPORT ad_group_ad ADGROUP_PERFORMANCE_REPORT ad_group AGE_RANGE_PERFORMANCE_REPORT age_range_view AUDIENCE_PERFORMANCE_REPORT campaign_audience_view,ad_group_audience_view AUTOMATIC_PLACEMENTS_PERFORMANCE_REPORT group_placement_view BID_GOAL_PERFORMANCE_REPORT bidding_strategy BUDGET_PERFORMANCE_REPORT campaign_budget CALL_METRICS_CALL_DETAILS_REPORT call_view CAMPAIGN_AD_SCHEDULE_TARGET_REPORT ad_schedule_view CAMPAIGN_CRITERIA_REPORT campaign_criterion CAMPAIGN_PERFORMANCE_REPORT campaign CAMPAIGN_SHARED_SET_REPORT campaign_shared_set CAMPAIGN_LOCATION_TARGET_REPORT location_view CLICK_PERFORMANCE_REPORT click_view DISPLAY_KEYWORD_PERFORMANCE_REPORT display_keyword_view DISPLAY_TOPICS_PERFORMANCE_REPORT topic_view GENDER_PERFORMANCE_REPORT gender_view GEO_PERFORMANCE_REPORT geographic_view,user_location_view KEYWORDLESS_QUERY_REPORT dynamic_search_ads_search_term_view KEYWORDS_PERFORMANCE_REPORT keyword_view LABEL_REPORT label LANDING_PAGE_REPORT landing_page_view,expanded_landing_page_view PAID_ORGANIC_QUERY_REPORT paid_organic_search_term_view PARENTAL_STATUS_PERFORMANCE_REPORT parental_status_view PLACEHOLDER_FEED_ITEM_REPORT feed_item,feed_item_target PLACEHOLDER_REPORT feed_placeholder_view PLACEMENT_PERFORMANCE_REPORT managed_placement_view PRODUCT_PARTITION_REPORT product_group_view SEARCH_QUERY_PERFORMANCE_REPORT search_term_view SHARED_SET_CRITERIA_REPORT shared_criterion SHARED_SET_REPORT shared_set SHOPPING_PERFORMANCE_REPORT shopping_performance_view TOP_CONTENT_PERFORMANCE_REPORT No longer available in the Google Ads API. URL_PERFORMANCE_REPORT detail_placement_view USER_AD_DISTANCE_REPORT distance_view VIDEO_PERFORMANCE_REPORT video
If the pipeline is using query to retrieve data from Google AdWords, use Query Migration tool to translate the AWQL (AdWords Query Language) into GAQL (Google Ads Query Language).
Be aware that there are certain limitations with this upgrade:
- Not all report types from AWQL are supported in GAQL.
- Not all AWQL queries are cleanly translated to GAQL queries.
For a list of data stores supported as sources and sinks by the copy activity, see supported data stores.