Use this script to put any AdWords data in a spreadsheet

Optmyzr’s Rule Based Optimizations recently added the ability to integrate with any 3rd party data through Google spreadsheets. By connecting call conversion data, CRM data, internal business data, or data about pretty much anything else, advertisers can now build some really cool optimizations of their own. Even the classic Bid By Weather use case that has been so heavily promoted through AdWords Scripts can now be implemented by advertisers using Optmyzr without writing any code.

To help advertisers create their spreadsheets for some of the more common use cases, like bidding by conversion name or type, we wrote a simple AdWords Script that can pull any Google Ads data into a spreadsheet and keep it updated as often as hourly. This spreadsheet can then be linked to a Rule-Based Optimization in Optmyzr so it’s always ready to run an optimization with the right data.

Automatically Put Raw Google Ads Data In a Spreadsheet

Even if you’re not planning to use this script to automate your account management, it is still useful if you need a way to get a lot of raw Google Ads data into a spreadsheet. It can also be a great supplement to current Optmyzr reports which are more focused on showing the highlights of an account and presenting insights that a stakeholder rather than a PPC practitioner might find useful.

The script can fetch multiple ads reports and add them as different spreadsheets or different tabs in the same spreadsheet.

How to Use the Script

For each report you want to add in a spreadsheet you’ll need to have one section of the code that specifies the query, spreadsheet URL, tab name, and report version. Our sample version already includes a few sample queries but let our support team know if you need help crafting another query.

You can modify the following variables with your settings:

• query: this is the AWQL query that tells AdWords what data to include. We’ve added a few sample queries in the code or you can write your own just like your write SQL.

• spreadsheetUrl: the Url of the Google spreadsheet that this script will update.

• tabName: the name of the sheet (tab) in the spreadsheet that should be updated.

• reportVersion: the version of the AdWords API reports you’re using. The data available in the Ads API changes periodically so this ensures our script talks to the right version of AdWords.

To run this code, simply add it as a new script in AdWords and schedule it. Most likely you’ll just need to schedule this to run once a day, early in the day.

Resources

1. The AdWords reporting API lists available reports and fields. Use this to find what report to use for the data you need. Also use it to find which data you can combine in one report. Note that some types of data are not compatible, like ‘ConversionCategoryName’, and ‘Clicks’.

2. The AWQL guide explains how to construct a query. Note that even though AWQL supports ordering and limiting of results, this functionality is not available when using AWQL inside AdWords scripts. This means that your report will include all results in no particular order and when you run the same report again, the order of results may change.

Sample Queries

The following sample queries are compatible with reportVersion v201710

A breakdown of conversion types for all converting keywords in AdWords in the last 30 days:

'SELECT AdGroupId, Id, Conversions, ConversionCategoryName ' +
     'FROM   KEYWORDS_PERFORMANCE_REPORT ' +
     'DURING LAST_30_DAYS'

'SELECT AdGroupId, Id, Conversions, ConversionTypeName ' +
     'FROM   KEYWORDS_PERFORMANCE_REPORT ' +
     'DURING LAST_30_DAYS'