3 Scripts for Automating Google Ads Reports in Spreadsheets

As PPC account managers, we’ve always had to do a lot of reporting to keep our stakeholders informed.

Now with the continued rise in automation, reporting has taken on new importance as one of the key ways we can monitor what the machines are doing.

For example, we can use reports to monitor which queries are considered close variants for exact match keywords.

But while reporting is a key PPC management task, it can also be one of the most time-consuming. That is, unless you use your own automation layers like the ones I’ll share here.

There are plenty of great free and paid solutions that make reporting easier (you can find some in Aaron Levy’s free PPC tools roundup).

But most people overlook Ad Scripts because they’re worried that this may be too advanced for them.

The good news is that there are some great scripts out there already written by experts, and if you know how to copy-and-paste and follow a few simple instructions, you can use a script to automatically put your Google Ads reports into a spreadsheet.

In this column, you’ll find three free scripts I’ve written that can send different types of ads data into a Google Sheet.

The beauty of Sheets is that they are a great connector to other systems. Once you have the data you want in a Google spreadsheet, it’s relatively easy to connect it with your favorite dashboard software or a client reporting tool.

Or, simply use it as the basis for making optimization decisions to improve your account.

Deciding which of these three scripts is best for you depends on the desired output and your level of comfort with writing SQL, digging up field names in Google’s documentation, and manipulating data yourself in the spreadsheet after it’s been imported automatically.

Script 1: You Know How to Write Simple SQL

If you just need a quick dump of Google Ads data into a Google Sheet, check out the add-on for Google Sheets first. It’s by far the easiest way to get started.

But in my own experience, it doesn’t cover all the report types available in the API so if you’re familiar with SQL and need something more than what the add-on provides, try this script.

You get to write an AWQL query and the script simply executes the query and puts the resulting rows in a new Google Sheet.

You can even use one script to run multiple queries and send the output to different tabs in the same spreadsheet.

You could use this to create a massive report that contains every piece of data Google has about your ads.

The report above was automated using an AWQL query and a simple ads script.

AWQL stands for AdWords Query Language and it’s a SQL-like language for querying AdWords reporting data.

It’s the precursor of GAQL which stands for the Google Ads Query Language and which was introduced when Google transitioned from the AdWords API to the Google Ads API in 2020.

While it’s powerful, AWQL can’t sort or group data. If you need this extra functionality, plan to write GAQL queries instead or have a plan for getting data in the right order in the spreadsheet.

Here’s an example of how simple the query is to fetch the number of conversions of various types for ad groups:

‘SELECT AdGroupId, Id, Conversions, ConversionCategoryName ‘ +

‘FROM KEYWORDS_PERFORMANCE_REPORT ‘ +

‘DURING LAST_30_DAYS’

Key Points About This Script

• You need to know how to write an AWQL or GAQL query, similar to SQL.
• If you write AWQL, the new version of Ads Scripts will likely be able to convert it to GAQL when AWQL is retired, making the transition very easy for you.
• Learn how to write GAQL instead of AWQL unless you’re willing to do all manipulation of the resulting data in the spreadsheet because sorting and grouping doesn’t work with AWQL.
• You can’t join data from multiple reports so if you want to automate some of the data processing, you’ll have to handle that in the spreadsheet after you’ve downloaded the raw data.

Get this script and its instructions.

Script 2: You Prefer a WYSIWIG Report Builder

This next option is for those who would rather not deal with AWQL or GAQL.

This script involves setting up a bit more code, but that code does something quite useful: it reads Google’s reporting documentation and puts the available options into a spreadsheet so that generating a report becomes as simple as selecting the things you want to include from a few dropdowns.

The first script is a Google Apps Script that adds a menu item to the sheet and then takes the data about available Ads reports and translates that into a bunch of dropdowns.

This code is already embedded in the spreadsheet you’ll copy (details are in the linked instructions) and to get it to work, all you need to do is authorize the code to run inside your spreadsheet.

The end result is that you can bypass learning AWQL and instead just select what you want in your report in the spreadsheet itself.

Screenshot from Google Sheets, October 2021

Note that you may need to spend a bit of time figuring out what things are called in reports.

For example, in keyword reports, you’ll find something called a criteria (that’s a keyword), and if you’re looking for a report for ‘Sitelinks,’ those are called Placeholder Feed Item in reports.

Here is the ads reporting documentation from Google to help you figure out these nuances.

The second script to install for this is the Google Ads Script. This one looks at what data you’re requesting with your settings in the sheet and then fetches what’s needed and drops it into the right place.

A huge benefit of this script is that you can schedule it to fetch new data as often as hourly.

That way, whenever you load the sheet, it can be ready with the data you need to go about your work.

Key Points to Remember About This Script

• You’ll use two scripts: an Ads Script and an App Script. Both need to be authorized by clicking a button but only one has to be copied-and-pasted (the Ads Script).
• Rather than writing a query, all selections of fields to include are done in spreadsheet dropdowns.
• Filters on what data to return can also be set by selecting from dropdowns in the spreadsheet.

Get this script or read its instructions.

Script 3: You Want Processed Data in Your Spreadsheet

This third option is best when you need more than just raw ads data.

Instead of doing the manipulation of data through pivot tables in a spreadsheet, this script will do it for you so when you open the sheet, it will already have more meaningful and human-readable data.

To use this script you should be able to distinguish between segments, attributes, and metrics or at least be able to use Google’s tables detailing what’s what before you write your query in the AdWords Script.

Segments, metrics, and attributes are highlighted in different colors.

Screenshot from Google API documentation, October 2021

What sets this one apart is that it processes the data for segments in a unique way.

Say that you’re trying to get data about the different types of conversions your account is driving.

The two previous scripts will include that in the report, but it will make a new line for each segment.

For example, if a keyword has delivered two signup conversions and one call conversion, the other two scripts in this post will include two lines for that keyword, the first line showing the number of signups and the next line showing the number of calls.

Sometimes it’s useful to have a report that has just one line per entity so that you can see everything about one keyword on one line rather than having to scan the entire table to find other references to that keyword.

So here’s what an example output from this script looks like:

Screenshot from Google Sheets, October 2021

This script puts ads data in a Google Sheet after converting different available values for segments into columns. This lets you view all data for an AdWords entity like a keyword on one line of the sheet.

Key Points About This Script

• This one outputs pivoted data so you get one row per AdWords entity (e.g., a campaign, ad, or keyword).
• Segments for the element that is being reported will be shown as columns (e.g. day of week = Monday, day of week = Tuesday, etc.).

Get this script and its instructions.

Conclusion

Ads scripts are a great solution to cut down on the manual labor involved in repetitive PPC tasks like reporting.

If you’re ready to try it out, consider one of these free reporting scripts as an alternative to paid solutions for moving Google Ads data into spreadsheets.

All these scripts do is move data from an ads account into a spreadsheet, so they’re low risk for a newbie to try.

The worst you can do is mess up a spreadsheet. These scripts won’t make changes to your ads account.

And with the time savings you can achieve by no longer having to manually move ads data, you’ll have more resources to dedicate to the higher value work you bring to PPC management.

More Resources:

Featured Image: Studio Romantic/Shutterstock