PPC

3 Spreadsheet Scripts for Automating Google Ads Reports

Ad scripts reduce the manual labor involved in repetitive PPC tasks such as reporting. Here are three Google Ads scripts that you can use.

We’ve always had to do a lot of reporting as PPC account managers to keep our stakeholders informed.

With the continued rise of automation, reporting has taken on new significance as one of the primary means by which we can monitor what the machines are doing.

Reports, for example, can be used to track which queries are considered close variants for exact match keywords.

However, while reporting is an important part of PPC management, it can also be one of the most time-consuming. That is unless you create your automation layers, such as the ones I’ll show you here.

There are numerous excellent free and paid reporting solutions (some of which can be found in Aaron Levy’s free PPC tool roundup).

Most people, however, ignore Ad Scripts because they are concerned that they are too advanced for them.

The good news is that there are already some great scripts written by experts available, 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.

This column contains three free scripts that I’ve written that can send various types of ad data into a Google Sheet.

Sheets have the advantage of being a great connector to other systems. Once you’ve gathered the information you need in a Google spreadsheet, it’s relatively simple to integrate it with your preferred dashboard software or client reporting tool.

Alternatively, you can simply use it as the foundation for making optimization decisions to improve your account.

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

Script 1: You Can Write Simple SQL

Check out the Google Sheets add-on first if you just need a quick dump of Google Ads data into a Google Sheet. It’s by far the most straightforward way to get started.

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

You get to write an AWQL query, and the script simply runs it and saves the results in a new Google Sheet.

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

You could use this to generate a massive report containing all of the data Google has on your ads.

An AWQL query and a simple ads script were used to generate the above report.

AdWords Query Language (AWQL) is a SQL-like language for querying AdWords reporting data.

It’s the forerunner to GAQL, which stands for Google Ads Query Language, and was introduced in 2020 when Google switched from the AdWords API to the Google Ads API.

Despite its strength, AWQL cannot sort or group data. If you require this additional functionality, plan to write GAQL queries instead, or have a strategy in place for getting data in the correct order in the spreadsheet.

Here’s an example of how easy it is to retrieve the number of conversions of various types for ad groups:

  • ‘SELECT AdGroupId, Id, Conversions, ConversionCategoryName FROM AdGroupId, Id, Conversions, ConversionCategoryName’ +
  • ‘FROM THE KEYWORDS PERFORMANCE REPORT’+
  • ‘OVER THE LAST 30 DAYS’

The Most Important Aspects of This Script

  • You must understand how to write an AWQL or GAQL query, which is similar to SQL.
  • If you write AWQL, the new Ads Scripts version will most likely be able to convert it to GAQL when AWQL is retired, making the transition very simple for you.
  • Learn how to write GAQL instead of AWQL unless you’re willing to do all data manipulation in the spreadsheet because AWQL doesn’t support sorting and grouping.
  • Because you can’t join data from multiple reports, you’ll have to handle some of the data processing in the spreadsheet after you’ve downloaded the raw data.

Read: Reducing Your Business Overheads: A Quick Guide.

Script 2: You Prefer a WYSIWYG Report Creator

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

This script requires a bit more code to be set up, but that code does something very useful: it reads Google’s reporting documentation and converts the available options into a spreadsheet, making generating a report as simple as selecting the items you want to include from a few dropdowns.

The first script is a Google Apps Script that adds a menu item to the sheet before converting the data about available Ads reports into a slew of dropdowns.

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

As a result, instead of learning AWQL, you can simply select what you want in your report in the spreadsheet itself.

It’s worth noting that you may need to spend some time figuring out what things are called in reports.

In keyword reports, for example, you’ll see 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 Google’s ad reporting documentation to help you figure out these nuances.

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

This script has the huge advantage of being able to be scheduled to fetch new data as frequently as hourly.

As a result, whenever you load the sheet, it will be pre-populated with the information you require to complete your tasks.

Important Notes About This Script

  • Two scripts will be used: an Ads Script and an App Script. Both must be approved by clicking a button, but only one must be copied and pasted (the Ads Script).
  • Rather than writing a query, all field selections are made using spreadsheet dropdowns.
  • Filters for what data to return can also be set in the spreadsheet by selecting from dropdown menus.

You Want Processed Data in Your Spreadsheet, Script 3

This third option is ideal if you require more than just raw ad data.

Instead of manipulating data in a spreadsheet with pivot tables, this script will do it for you, so that when you open the sheet, it already has more meaningful and human-readable data.

To use this script, you should be able to differentiate between segments, attributes, and metrics, or at the very least be able to use Google’s tables that explain what’s what before writing your query in the AdWords Script.

Different colors are used to highlight segments, metrics, and attributes.

What distinguishes this one is that it processes data for segments in an unusual manner.

Assume you’re looking for information on the various types of conversions that your account is generating.

The previous two scripts will include that in the report, but each segment will have its own line.

For example, if a keyword resulted in two signup conversions and one call conversion, the other two scripts in this post will include two lines for that keyword, with the first line displaying the number of signups and the second line displaying the number of calls.

It can be useful to have a report with only one line per entity so that you can see everything about one keyword on one line rather than having to search the entire table for other references to that keyword.

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

This script populates a Google Sheet with ad data after converting the various available values for segments into columns. This allows you to view all data for an AdWords entity, such as a keyword, on a single line of the sheet.

The Most Important Aspects of This Script

This one generates pivoted data, so you get one row for each AdWords entity (e.g., a campaign, ad, or keyword).
Columns will display segments for the element being reported (e.g., day of week = Monday, day of week = Tuesday, etc.).

Conclusion

Ads scripts are an excellent way to reduce the manual labor involved in repetitive PPC tasks such as reporting.

Consider one of these free reporting scripts as an alternative to paid solutions for moving Google Ads data into spreadsheets if you’re ready to give it a shot.

These scripts only move data from an ad account into a spreadsheet, so they’re safe for a beginner to try.

The worst thing you can do is screw up a spreadsheet. These scripts will not make any changes to your advertising account.

And, because you won’t have to manually move ad data anymore, you’ll have more time to devote to the higher-value work you bring to PPC management.

Need help with our free SEO tools? Try our free Page Authority Checker, Domain Authority Checker, Google Index Checker.

Learn more from PPC and read 7 Proven Ways to Improve the Performance of Your PPC Campaign.

Related Articles

Leave a Reply

Your email address will not be published. Required fields are marked *

Back to top button

Adblock Detected

Don't miss the best oppertunities.