This integration is powered by Singer's Google AdWords tap and certified by Stitch. Check out and contribute to the repo on GitHub.
For support, contact Stitch support.
Google Ads integration summary
Note: To use the AdWords API, an Ads account must be connected to a My Client Center (MCC) account.
Google Ads feature snapshot
A high-level look at Stitch's Google Ads (v1) integration, including release status, useful links, and the features supported in Stitch.
|Release Status||Supported By|
|Singer GitHub Repository|
|Configurable Replication Methods||
Connecting Google Ads
Google Ads setup requirements
To set up Google Ads in Stitch, you need:
To pause any ad-blocking software. Ad blockers can interfere with pop-ups, which are used in Google authorization and may prevent authorization from successfully completing.
Access to the Google Ads data you want to replicate. Before beginning, verify that the user creating the integration has access to the reports you want to replicate.
To connect your Ads account to a My Client Center (MCC) account. This will ensure your account has access to the AdWords API, thereby allowing Stitch to query for and extract data.
An MCC account is an Ads account type that enables you to manage several Ads accounts under a single login. Think of manager accounts as trees: they can branch out to individual accounts or even other manager accounts. Read more about MCC accounts here.
By default, regular advertiser accounts - that is, individual Ads accounts - don’t have access to the AdWords API. To gain access, they must be linked to an MCC account. If you don’t have an MCC account, create one using these instructions and then link it to your Ads account by following these steps.
Step 1: Add Google Ads as a Stitch data source
- Sign into your Stitch account.
On the Stitch Dashboard page, click the Add Integration button.
Click the Google Ads icon.
Enter a name for the integration. This is the name that will display on the Stitch Dashboard for the integration; it’ll also be used to create the schema in your destination.
For example, the name “Stitch Google Ads” would create a schema called
stitch_google_adsin the destination. Note: Schema names cannot be changed after you save the integration.
Step 2: Define the historical sync
The Sync Historical Data setting will define the starting date for your Google Ads integration. This means that:
- For tables using Incremental Replication, data equal to or newer than this date will be replicated to your data warehouse.
- For tables using Full Table Replication, all data - including records that are older, equal to, or newer than this date - will be replicated to your data warehouse.
Change this setting if you want to replicate data beyond Google Ads’s default setting of 30 days. For a detailed look at historical replication jobs, check out the Syncing Historical SaaS Data guide.
Step 3: Create a replication schedule
In the Replication Frequency section, you’ll create the integration’s replication schedule. An integration’s replication schedule determines how often Stitch runs a replication job, and the time that job begins.
Google Ads integrations support the following replication scheduling methods:
To keep your row usage low, consider setting the integration to replicate less frequently. See the Understanding and Reducing Your Row Usage guide for tips on reducing your usage.
Step 4: Authorize Stitch & Select Google Ads Profiles
- Next, you’ll be prompted to log into your Google account and to approve Stitch’s access to your Google Ads data. Note: We will only ever read your data.
- Click Authorize to continue.
After your credentials are validated, you’ll be prompted to select the Google Ads profile(s) you want to connect to Stitch.
If you don’t see the profile(s) you want to connect, verify that you have completed the setup requirements.
When selecting profiles, keep the following in mind:
- You can select up to 400 profiles per Google Ads integration. If you need to replicate data from more than 400 profiles, you should create additional Google Ads integrations in your Stitch account.
- Selecting a subprofile will also select the parent, or top-level profile. If you de-select the top-level profile, you will be unable to select any subprofiles.
- If multiple profiles are selected, data for all the selected profiles will map to the same table in your destination. For example: If two profiles are selected and the
accountstable is tracked, account data for both profiles will be replicated into the
accountstable. This is applicable to every table selected in the next step.
- When finished selecting profiles, click Continue.
Step 5: Set tables and columns to replicate
Column selection and Google compatibility rules
Because of Google’s compatibility rules, some columns (metrics and segments) can’t be tracked together. As you select columns to track, incompatible fields will automatically be greyed out.
You can create additional Google Ads integrations if you need to track incompatible columns. The resulting table names will still be the same (ex:
account_performance_report) but the data will reside in different schemas in your data warehouse.
To complete the setup, you’ll need to select the tables and columns you want to replicate to your data warehouse.
Check out the Schema section to learn more about the available tables in Google Ads and how they replicate.
- In the list of tables that displays - or in the Tables to Replicate tab, if you skipped this step during setup - locate a table you want to replicate.
To track a table, click the checkbox next to the table’s name. A green checkmark means the table is set to replicate.
To track a column, click the checkbox next to the column’s name. A green checkmark means the column is set to replicate.
- Repeat this process for all the tables and columns you want to replicate.
- When finished, click the Finalize Your Selections button at the bottom of the screen to save your selections.
Note: If you change these settings while a replication job is still in progress, they will not be used until the next job starts.
Initial and historical replication jobs
After you finish setting up Google Ads, its Sync Status may show as Pending on either the Stitch Dashboard or in the Integration Details page.
For a new integration, a Pending status indicates that Stitch is in the process of scheduling the initial replication job for the integration. This may take some time to complete.
Initial replication jobs with Anchor Scheduling
If using Anchor Scheduling, an initial replication job may not kick off immediately. This depends on the selected Replication Frequency and Anchor Time. Refer to the Anchor Scheduling documentation for more information.
Free historical data loads
The first seven days of replication, beginning when data is first replicated, are free. Rows replicated from the new integration during this time won’t count towards your quota. Stitch offers this as a way of testing new integrations, measuring usage, and ensuring historical data volumes don’t quickly consume your quota.
Google Ads replication
There are two types of tables in Stitch’s Google Ads integration: Core Object and Report.
Report tables are the various Google Ads reports. The replication process for these tables is a bit unlike that of other tables:
Extraction: Data is extracted using a Conversion Window. A Conversion Window is a period of time after a customer clicks an ad that a conversion (ex: a purchase) is recorded in Google Ads. Stitch currently uses a Conversion Window of 30 days.
This means that data from the past 30 days will be replicated during every replication job.
Loading: Data is loaded into your data warehouse using Append-Only Replication.
For Report tables, this means that:
Due to the Conversion Window, a high Replication Frequency may not be necessary. Because Stitch will replicate data from the past 30 days during every replication job, recent data will be re-replicated and count towards your row quota.
To reduce your row usage and replicating redundant data, consider setting the integration to replicate less frequently. For example: every 12 or 24 hours.
Querying for the latest data in Report tables will require a different strategy than you might usually use. Stitch will add a column named
_sdc_report_datetimeto Report tables to help you identify the most recent records in a table. See the Query for the Latest Data section for more info and a sample query.
Each part of the replication process for Report tables is explained below.
Report tables: Data extraction and conversion windows
Every time Stitch runs a replication job for Google Ads, the last 30 days’ worth of data will be replicated for any Report tables currently being tracked.
Stitch replicates data in this way to account for updates made during the Conversion Window.
For historical and full re-replications of Google Ads data, Stitch will query for and extract data newer than or equal to the date defined in the Start Date field in the Integration Settings page.
The Start Date, in conjunction with the Conversion Window, will define the minimum date Stitch should query for when extracting historical data. This is calculated as:
Start Date - Conversion Window = Minimum Extraction Date
During the initial set up, the Start Date field is set to
July 3, 2017, or
To account for the Conversion Window, Stitch would calculate the Minimum Extraction Date value as:
2017-07-03 00:00:00 - 30 days = 2017-06-03 00:00:00
If you were to write a SQL query using this date for the
ad_performance_report table, it might look like this:
SELECT * FROM google_ads.ad_performance_report WHERE day >= '2017-06-03 00:00:00' /* Min. Extraction Date */ ORDER BY day
For ongoing replication jobs, Stitch will query for and extract data using the last saved maximum value in the table’s Replication Key column and the Conversion Window setting.
Note: This applies to every replication job that takes place after the historical replication job.
The last maximum saved Replication Key value for the
ad_performance_report table is
To account for the Conversion Window of 30 days, we’d subtract this from the last maximum saved Replication Key value:
2017-10-01 00:00:00 - 30 days = 2017-09-01 00:00:00
In this case, Stitch would query for and extract data that is newer than or equal to
2017-09-01 00:00:00 and older than or equal to
If this were a SQL query, it might look like this:
SELECT * FROM ad_performance_report WHERE day >= '2017-09-01 00:00:00' /* max Replication Key value - Conversion Window */ AND day <= '2017-10-01 00:00:00' /* max Replication Key value from previous job */ ORDER BY day
Report tables: Data loading and Append-Only Replication
When Stitch loads the extracted data for Report tables into your destination, it will do so using Append-Only Replication. This is a type of Incremental Replication where existing rows aren’t updated, but appended to the end of the table.
Additionally, the number of rows loaded into the table during each replication job is dependent on the combination of unique values in the dimension columns you track. See the Column Selection and Statistic Aggregation section for more info and examples.
Let’s say these columns are tracking in the
campaignId(dimension) - This is the ID associated with a campaign. In this example, there are two campaigns:
device(dimension) - The device type. There are two values for this example:
impressions(metric) - The total number of impressions.
Every time Stitch replicates and loads data, a row for each unique combination of the dimension columns will be appended to the end of the table:
|2017-10-01 17:48:26.791||2017-09-10 00:00:00||1585293495||929007494||Computer||61|
|2017-10-01 17:48:26.791||2017-09-10 00:00:00||1585293495||929007494||Tablet||15|
|2017-10-01 17:48:26.791||2017-09-10 00:00:00||1585293495||929599581||Computer||37|
|2017-10-01 17:48:26.791||2017-09-10 00:00:00||1585293495||929599581||Tablet||9|
Report tables: Query for the latest data
You may need to alter this query
The query below is meant to act as a foundation for writing your own. Depending on your SQL syntax and the dimensions contained in a given table, you may need to alter this query for your own usage.
When querying your Google Ads data, you’ll want to include the Dimensions you’re analyzing in the partition. For example: If you’re analyzing campaigns over time, you’d include columns like
If you want only the most recently replicated data for any Google Ads Report table, you can use the sample query below to account for the Append-Only Replication Stitch uses.
This query uses two columns - which are automatically included for every Report table - to return the latest data:
day- The day that the record pertains to.
_sdc_report_datetime- The starting time of the Stitch job that extracted the record.
SELECT * FROM ( SELECT *, RANK() OVER (PARTITION BY day, customerid ORDER BY _sdc_report_datetime DESC) FROM ad_performance_report ORDER BY day ASC ) AS latest WHERE latest.rank = 1
In this query:
- A subquery first uses a window function to create a ‘window’ of data for each
- The values of the
_sdc_report_datetimecolumn are ranked within each window partition, and
- Then, in the outer query, only the rows with
_sdc_report_datetimevalues ranked as
1- which is equal to the maximum timestamp - are returned.
Google Ads table schemas
Schemas and versioning
Schemas and naming conventions can change from version to version, so we recommend verifying your integration’s version before continuing.
The schema and info displayed below is for version 1 of this integration.
Report tables: Values for money fields
When conducting analyses on Report tables, you might notice that values in money fields - like a
cost field, for example - look higher than usual. This is because Google Ads’ API sends Stitch money data in micro currency units. Micro amounts always refer to your account’s local currency.
For example: The value of $2.25USD will be recorded as
2250000. To represent this value as
2.25 in a report, divide by one million:
2250000 / 1000000 = 2.25.
Report tables: Column selection and statistic aggregation
The dimension columns selected for replication in Report tables can impact how performance statistics are aggregated. Additionally, this can also affect the number of rows replicated and loaded into your destination.
For example: if
impressions were selected, the
impressions column would contain the total number of impressions for the device type for that date:
column were also selected, a row for every unique combination of `device` and would be created and
impressions would be aggregated accordingly:
Table and column names in your destination
Depending on your destination, table and column names may not appear as they are outlined below.
For example: Object names are lowercased in Redshift (
customers), while case is maintained in PostgreSQL destinations (
CusTomERs). Refer to the Loading Guide for your destination for more info.