3 Scripts for Automating Google Ads Reports in Spreadsheets

As PPC Account Managers, we have always had to do a lot of reporting to keep our stakeholders informed.

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

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

But while reporting is an essential task for PPC management, it can also be one of the most time consuming. That is unless you use your own automation layers like the one I’m going to share here.

There are plenty of free and paid solutions out there that make reporting easier (you can find some in Aaron Levy’s Free PPC Tools Report).

But most people ignore advertising texts because they are worried that this might 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 ad data to a Google spreadsheet.

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

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

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

Scenario 1: You know how to write simple SQL

If you just need a quick dump of your Google Ads data into a Google Spreadsheet, check out the Add to Google Sheets first. It is by far the easiest way to get started.

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

You can write an AWQL query and the script simply executes the query and puts the resulting rows into your new Google Sheet.

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

You can use this to generate a huge report containing every bit of data that Google has about your ads.

The above report was run automatically using an AWQL query and a simple ad text.

awql AdWords Query Language stands for an SQL-like language for querying AdWords reporting data.

It is provided by GAQL Which stands for Google Ads Query Language which was introduced when Google moved from AdWords API to Google Ads API in 2020.

Despite its power, AWQL cannot sort or group data. If you need this additional functionality, plan to write GAQL queries instead or have a plan to get the data in the correct order in the spreadsheet.

Here is an example of how simple a query can be to fetch the number of conversions of different 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, chances are the new version of Ads Scripts will 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 the manipulation with the resulting data in the spreadsheet because sorting and grouping don’t work with AWQL.
• You can’t join data from multiple reports so if you want to automate some data processing you’ll have to handle that in the spreadsheet after downloading the raw data.

Get this script and its instructions.

Script 2: You prefer WYSIWIG Report Builder

The next option is for those who prefer not to deal with AWQL or GAQL.

This script involves a little more code setup, but this code does something very useful: it reads the Google Reports documentation and puts the available options into a spreadsheet so creating a report is as simple as selecting the things you want to include from a few drop by drop.

The first script is a Google Apps Script that adds a menu item to the sheet and then takes data about available ad reports and translates that into a set of dropdown menus.

This code is already included in the spreadsheet you are going to copy (details are in the linked instructions) and to make it work all you have to do is allow the code to run inside your spreadsheet.

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

Note that you may need to take some time to figure out what things are called in the reports.

For example, in keyword reports, you’ll find something called Standards (that’s a keyword), and if you’re looking for a report on “sitelinks,” it’s called placeholder feed element in reports.

Here it is Google Advertising Reporting Documentation To help you figure out these nuances.

The second script to install for this is the Google Ads script. This person looks at the data you request with your settings in the sheet and then fetches what is needed and drops it in the right place.

A big benefit of this script is that you can schedule it to fetch new data every hour.

This way, whenever you load the sheet, it can be ready with the data you need to do 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 (ads text) needs to be copied and pasted.
• Instead of writing a query, all field selections to include are done in the spreadsheet drop-down lists.
• Filters for the data to be returned can also be set by selecting from drop-down lists in the spreadsheet.

Gets This scenario or read it directions.

Script 3: You want to process data in your spreadsheet

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

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

To use this script you must be able to Distinguish between segments, attributes, and metrics Or at least be able to use Google Sheets detailing what before you type your query into an AdWords script.

Segments, scales, and themes are highlighted in different colors.

What sets this apart is that it handles the data for the fragments in a unique way.

Let’s say you’re trying to get data about the different types of conversions your account generates.

The previous two programs will include it in the report, but it will form a new line for each segment.

For example, if a keyword provided two sign-up conversions and one call conversion, the other two texts in this post would 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 helps to have a report with just one line per entity so you can see everything about one keyword in one line rather than having to scan the entire table to find other references for that keyword.

So here is what the example output from this script looks like:

This script puts the ads data into a Google spreadsheet after converting the different values ​​available for the segments into columns. This allows you to display all data for an AdWords entity such as a keyword on one line of the sheet.

Key points about this script

• This file produces pivot data so you get one row for each AdWords entity (eg campaign, ad, or keyword).
• The segments of the item being reported will be shown as columns (eg day of the week = Monday, day of the week = Tuesday, etc.).

Get this script and its instructions.

conclusion

Ad scripts are a great solution to reduce the manual work involved in repetitive PPC tasks such as reporting.

If you’re ready to give it a try, consider one of these free reporting scripts as an alternative to paid solutions for transferring your Google Ads data to spreadsheets.

All these scripts do is transfer data from an ad account to a spreadsheet, so it’s low risk for a newbie to try.

The worst you can do is mess up your spreadsheet. These scripts will not make changes to your ads account.

And with the time savings you can achieve by not having to manually transfer advertising data, you’ll have more resources to devote to the higher value work you bring to PPC management.

More resources:

• 5 things you can automate with Google Ads scripts
• The new Google Ads budget report tracks daily campaign spend
• PPC 101: A Complete Guide to the Basics of PPC Marketing

---

Featured image: Studio Romantic/Shutterstock