ETL Your AdWords Data to a Data Warehouse

Advertising on the Google Search and Display Networks via Google Ads has become incredibly data-driven. Data-driven marketing is built on insights pulled from the analysis of data, collected through site visitor actions and engagements.

Today we’re going to discuss how you can extract your Google Ads data using the AdWords API and get it loaded into a data warehouse.

This will get quite technical and may involve coding your own API solution or hiring a developer. Alternately you can use an ETL tool such as Analytics Canvas which uses a visual interface into the Google AdWords API to easily create queries, access large volumes of data from multiple Google Ads accounts, and loads into SQL Server, BigQuery, Amazon Redshift, MySQL and other popular databases.

Let’s dive in…

Table of Contents

• What is the AdWords API?
• The Problem With Coding your own API solution vs. using a third party service.
• There is a Better Way – Using Analytics Canvas
• Choosing the right Google AdWords Report to Pull via the API
• How to Connect Your Google AdWords Account to Analytics Canvas
• Loading Your Data Warehouse

What is the AdWords API?

“The AdWords API allows you to programmatically retrieve report data and store it in your own databases. You could then create custom dashboards for monitoring the performance of accounts.”Google AdWords

The AdWords API allows applications to directly interact with the Google Ads platform. Managing large Google Ads accounts and complicated campaigns now becomes easier via:

• Automated account management
• Data-based bid management
• Custom reporting

You can use the AdWords API to programmatically do almost everything the Google Ads user interface does. The AdWords API utilizes SOAP and WSDL tech to perform its services, and the new Google Ads API (which we’ll support when its ready for prime time and before the AdWords API is sunset in 2020) is REST based. Client libraries include Java, .NET, Python, PHP, Perl, and Ruby.

The problem with coding your own API solution vs. using a third party service

Keeping up with the AdWords API Change History and Migration Guides

Keeping up with all of the changes to the AdWords API and staying on top of the deprecation schedule requires a fair amount of effort. On average, each API version is available for 9 months before ultimately meeting its sunset date.

Here is the migration guide, summarizing all of the breaking changes to services and their components within the AdWords API.

AdWords Deprecation and Sunset Schedule

Google provides a useful deprecation and sunset schedule to help you track the three API versions that are released in each calendar year.  

February 2018 – v201802 migrated to v201806

June 2018 – v201806 migrated to API version v201809

September 2018 – we currently remain at v201809 while Google developers launch the new Google Ads API.

An API sunset means:

1. It will not return any data after that sunset date.
2. Going through the migration guide (and Dev & QA) and to figure out what’s changed.
3. Updating all of your code and making sure reports don’t break.

Cost

Often times companies take things in-house and opt to code their own analytics solutions while ‘chasing’ the AdWords API. This can quickly add thousands of dollars to your marketing and analytics budget.

There are four main problems when it comes to building your own API based solution:

1. The current AdWords API is SOAP (Simple Object Access Protocol) based which is much harder than the REST (Representational State Transfer) based API to work with.
2. Google is currently transitioning from the AdWords API to a new Google Ads API. Support for the AdWords API is going to go away as people move over to the new Google Ads API. (don’t worry, Analytics Canvas will support the new Google Ads API before the AdWords API is sunset)
3. Hard coded solutions don’t offer much in the way of flexibility due to the amount of time and special resources that need to be allocated to build them.
4. Analysts can’t update queries or make new ones while they’re ‘in the flow’ of their analysis. They must pause, request new data, refine the request, etc., before finally getting to their analysis. These kind of delays can cripple a fast moving marketing campaign.

If you’re a developer, an IT manager, or at all concerned about your headcount costs and how much time people are spending on development, that should be a big concern. All of that time and effort you invest is going to evaporate. And your team is just going to have to learn this new API within a few months.

But what if you could:

1. Achieve scalability and pull in data from as many different accounts as your agency has access to.
2. Build a reporting workflow that features both ease of use and much faster time to result.
3. Empower analysts with self-sufficiency so that they’re not dependent on IT.

You can!

There is a Better Way – Using Analytics Canvas

With Canvas, one of the big differentiators is that it’s an ETL tool. ETL is extract, transform and load, which is very different from an Excel add-in, which generally performs the function of just extract.

Unlike traditional ETL tools, Canvas was designed for business analysts, not developers or IT professionals.

Typically, if you’re the marketing manager and you want to know how all the accounts are doing, you would go into each account to perform an API query—one by one—in one of the spreadsheet applications in order to export AdWords data.

Most other platforms only accomplish extract and load (no transform). And even then, you’re choosing one account at a time. With Analytics Canvas you can get all the accounts to perform the same query and get all of that data to our database at once.

This will literally save you hours of work each month.

Analytics Canvas allows you to add as many authorizations as you need onto the platform. If you’re an agency and you deal with a lot of different AdWords clients, this is a game changer because you’re authorizing once and all the authorizations persist.

Example: An analyst may just want to look at ‘cost and conversions’ for a number of accounts. And if they’re pulling data from a variety of accounts, they’ll need the information separated. The data needs to land into the correct rows.  

You want flexibility. Imagine having the ability to…

• Choose from a variety of AdWords reports.
• Pull only the dimensions and metrics required.
• Update your queries in minutes.
• And generate new follow-up queries based on questions from your clients and management.  

Let’s see how to go about doing that…

Choosing the right Google AdWords Report to Pull via the API

Here’s an overview of the report types that you can call up via the AdWords API. Each report has a complete reference guide with links to descriptions about each attribute, segment, or metric.

This is extremely useful for those just starting with AdWords reporting as it helps you to understand which combinations of attributes, segments and metrics make sense to answer a given reporting question.

Once you have determined which report you would like to access, hop on over to Canvas and start accessing the data you need.

How to Connect Your Google AdWords account to Analytics Canvas

Let’s take a look at the Google AdWords connector in Canvas. In order to connect to your AdWords data you need to provide Analytics Canvas the right to access the Google AdWords API from your computer.

You can authorize your account by going go the Accounts menu and selecting selecting Authorize new account and then select “Google AdWords”.

Click the authorize button to access AdWords reporting. Analytics Canvas will redirect you to Google’s website or you can log in with your credentials and grant Analytics Canvas permission to access your AdWords data.

Now let’s get your AdWords data into Canvas for analysis and processing.

Step 1. Select AdWords from the New Source menu.

Step 2. Select the account(s) from which you want to extract data.

Step 3. Search for a report or select it from the tree-view,  then click the Next button.

Step 4. Define the query by selecting ‘Dimensions/Segments’ and ‘Metrics’ and specifying a time period for which you need the data.

Step 5. Name your query and click OK. A new Google AdWords import block will appear on the main canvas.

Loading Your Data Warehouse

Analytics Canvas has native connectors to the most popular databases, including SQL Server, Oracle, MySQL, PostgreSQL, Redshift, BigQuery and Access, with additional connectors available via ODBC.

Initial Historical Data Load

The first time that you bring data into a data warehouse is referred to as the initial data load. This will create your tables and populate your database for the first time.

Google provides a list of reports detailing out field names, XML attributes, data type info, and Enum values.

There is also a handy XML Schema Definition provided in the AdWords API docs.

For the most part, you will simply use this as a reference – Canvas will take care of setting the appropriate data type based on the data that’s brought in.

Once you have configured your report, simply set the start and end date for your initial load using the Master Time Period. Canvas can get data as far back as you’ve started your AdWords campaigns.

Connect a Database Export Block, then Run the Canvas to perform your initial data load.

Incremental Load Into Your Data Warehouse

Far too often we come across people who want to perform a “nightly refresh” of their data in order to keep their data “up to date”.

Generally the analyst will want to load the last 1 day of data into their data warehouse. Seems simple enough – except that all of the Google APIs supported in Canvas (Analytics, AdWords, and Search Console), are subject to reprocessing.

In other words, if you only ever fetch the last 1 day of data each night, you are likely falling out of sync with the API and your users will soon report data discrepancies.

Google Ads Data Freshness

Data freshness refers to the time it takes Google Ads to process a hit from your site and make that account data available to you.

Google Ads processes data at midnight in the timezone of your account. That reprocessing will take about 3 hours for most metrics, but reach metrics could take up to 36 hours. Right away that nightly refresh is going to deliver incorrect data if you are only updating the last 1 day!

Under the “Adjustments” section there is a particularly important point that bears repeating:

Note: Your metrics may be adjusted one or more days after a click occurs if the conversion happens one or more days after the first click or if invalid traffic was detected.

For this reason, we recommend a 10-day incremental load process. This means that each day you first remove the last 10 days of data from your report tables, then load the most recent 10 day period. This accounts for data reprocessing, including late-arriving and re-assigned hits within the 10 day window, and ensures that your database is in sync with the API.

Setup the Incremental Load

Save a copy of the Initial Load Canvas and label it as Incremental Load. Select the database export block. In the bottom left corner, select “Append data to Table”.

When selected, you will see an option to ‘Delete data in master date range before export’. What that means is, no matter how many times you run the task, Canvas is always first going to execute a delete statement that removes entries that match the date range of your query. It’s going to look for data in this date range. It’s going to remove that from the table, and then it’s going write that data in. This way you never end up with duplicates and stay closely aligned with the API.

Next go to the Master Time Period and configure it for the 10-day incremental load by setting it to a Relative 10 day period.

That’s it! Save your Incremental Load Canvas and setup an automation routine to run daily in our cloud or on your server.

Your data warehouse will be kept up to date and your AdWords data will be ready and available for reporting and analysis.

Wrapping Up

Keeping up with the AdWords API is time consuming, costly, and complex. With Analytics Canvas you can dramatically reduce the time, effort, and cost involved in loading up your AdWords data warehouse and keeping it up to date.

With Analytics Canvas, you can access all of your AdWords accounts and all of the data inside of them for just $49/mo, and you can automate your routines and keep your data up-to-date from just $299/mo.

Best of all, you can get started right away, getting access to your data in just minutes and loading up your data warehouse before the end of the business day, all during your risk-free, full featured, unrestricted 30-day trial.

So how can you learn more about the most complete visual tool to access Google AdWords?

1. Book a demo / meeting and learn first-hand how Analytics Canvas unlocks deeper and more impactful insight from Google AdWords.
2. Enroll in a 30-day free trial of Canvas and test out all of the features with unlimited access to your data.
3. Contact Sales to discuss your requirements in detail and arrange an assisted trial or proof of concept.