WordPress Hosting

3 Scripts for Automating Google Ads Reports in Spreadsheets

As PPC account managers, we’ve at all times needed to do lots of reporting to maintain our stakeholders knowledgeable.

Now with the continued rise in automation, reporting has taken on new significance as one of many key methods we are able to monitor what the machines are doing.

For instance, we are able to use studies to observe which queries are thought-about shut variants for precise match key phrases.

But whereas reporting is a key PPC administration job, it can be one of the vital time-consuming. That is, until you employ your personal automation layers like those I’ll share right here.

There are loads of nice free and paid options that make reporting simpler (you will discover some in Aaron Levy’s free PPC instruments roundup).

But most individuals overlook Ad Scripts as a result of they’re apprehensive that this can be too superior for them.

The excellent news is that there are some nice scripts on the market already written by consultants, and if you know the way to copy-and-paste and comply with just a few easy directions, you should utilize a script to routinely put your Google Ads studies right into a spreadsheet.


Continue Reading Below

In this column, you’ll discover three free scripts I’ve written that may ship several types of advertisements knowledge right into a Google Sheet.

The fantastic thing about Sheets is that they’re a fantastic connector to different programs. Once you’ve got the information you need in a Google spreadsheet, it’s comparatively straightforward to attach it along with your favourite dashboard software program or a shopper reporting instrument.

Or, merely use it as the premise for making optimization selections to enhance your account.

Deciding which of those three scripts is finest for you relies on the specified output and your degree of consolation with writing SQL, digging up subject names in Google’s documentation, and manipulating knowledge your self in the spreadsheet after it’s been imported routinely.

Script 1: You Know How to Write Simple SQL

If you simply want a fast dump of Google Ads knowledge right into a Google Sheet, take a look at the add-on for Google Sheets first. It’s by far the simplest strategy to get began.


Continue Reading Below

But in my very own expertise, it doesn’t cowl all of the report varieties out there in the API so should you’re aware of SQL and wish one thing greater than what the add-on supplies, do that script.

You get to jot down an AWQL question and the script merely executes the question and places the ensuing rows in a brand new Google Sheet.

You may even use one script to run a number of queries and ship the output to completely different tabs in the identical spreadsheet.

You might use this to create an enormous report that incorporates each piece of information Google has about your advertisements.

Screenshot from Google Sheets, October 2021

The report above was automated utilizing an AWQL question and a easy advertisements script.

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

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

While it’s highly effective, AWQL can’t kind or group knowledge. If you want this further performance, plan to jot down GAQL queries as a substitute or have a plan for getting knowledge in the best order in the spreadsheet.

Here’s an instance of how easy the question is to fetch the variety of conversions of assorted varieties for advert teams:

‘SELECT AdGroupId, Id, Conversions, ConversionCategoryName ‘ +



Key Points About This Script

  • You have to know methods to write an AWQL or GAQL question, just like SQL.
  • If you write AWQL, the brand new model of Ads Scripts will probably be capable to convert it to GAQL when AWQL is retired, making the transition very straightforward for you.
  • Learn methods to write GAQL as a substitute of AWQL until you’re keen to do all manipulation of the ensuing knowledge in the spreadsheet as a result of sorting and grouping doesn’t work with AWQL.
  • You can’t be a part of knowledge from a number of studies so if you wish to automate a few of the knowledge processing, you’ll should deal with that in the spreadsheet after you’ve downloaded the uncooked knowledge.

Get this script and its instructions.


Continue Reading Below

Script 2: You Prefer a WYSIWIG Report Builder

This subsequent choice is for those that would moderately not take care of AWQL or GAQL.

This script entails establishing a bit extra code, however that code does one thing fairly helpful: it reads Google’s reporting documentation and places the out there choices right into a spreadsheet in order that producing a report turns into so simple as deciding on the belongings you wish to embody from just a few dropdowns.

The first script is a Google Apps Script that provides a menu merchandise to the sheet after which takes the information about out there Ads studies and interprets that right into a bunch of dropdowns.

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

The finish result’s you can bypass studying AWQL and as a substitute simply choose what you need in your report in the spreadsheet itself.

Adding a new menu item to a Google sheet. Screenshot from Google Sheets, October 2021

Note that you could be have to spend a little bit of time determining what issues are known as in studies.


Continue Reading Below

For instance, in key phrase studies, you’ll discover one thing known as a standards (that’s a key phrase), and should you’re wanting for a report for ‘Sitelinks,’ these are known as Placeholder Feed Item in studies.

Here is the ads reporting documentation from Google that can assist you work out these nuances.

The second script to put in for that is the Google Ads Script. This one seems at what knowledge you’re requesting along with your settings in the sheet after which fetches what’s wanted and drops it into the best place.

An enormous advantage of this script is you can schedule it to fetch new knowledge as typically as hourly.

That manner, everytime you load the sheet, it may be prepared with the information you’ll want 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 should be licensed by clicking a button however just one needs to be copied-and-pasted (the Ads Script).
  • Rather than writing a question, all picks of fields to incorporate are carried out in spreadsheet dropdowns.
  • Filters on what knowledge to return can be set by deciding on from dropdowns in the spreadsheet.


Continue Reading Below

Get this script or learn its instructions.

Script 3: You Want Processed Data in Your Spreadsheet

This third choice is finest if you want extra than simply uncooked advertisements knowledge.

Instead of doing the manipulation of information by pivot tables in a spreadsheet, this script will do it for you so if you open the sheet, it can have already got extra significant and human-readable knowledge.

To use this script it’s best to be capable to distinguish between segments, attributes, and metrics or a minimum of be capable to use Google’s tables detailing what’s what earlier than you write your question in the AdPhrases Script.

Segments, metrics, and attributes are highlighted in completely different colours.

Available reporting columns for keyword reports from Google Ads. Screenshot from Google API documentation, October 2021

What units this one aside is that it processes the information for segments in a novel manner.


Continue Reading Below

Say that you simply’re making an attempt to get knowledge in regards to the several types of conversions your account is driving.

The two earlier scripts will embody that in the report, however it can make a brand new line for every phase.

For instance, if a key phrase has delivered two signup conversions and one name conversion, the opposite two scripts in this put up will embody two strains for that key phrase, the primary line displaying the variety of signups and the subsequent line displaying the variety of calls.

Sometimes it’s helpful to have a report that has only one line per entity so as to see the whole lot about one key phrase on one line moderately than having to scan the whole desk to seek out different references to that key phrase.

So right here’s what an instance output from this script seems like:

(*3*)Screenshot from Google Sheets, October 2021

This script places advertisements knowledge in a Google Sheet after changing completely different out there values for segments into columns. This enables you to view all knowledge for an AdPhrases entity like a key phrase on one line of the sheet.


Continue Reading Below

Key Points About This Script

  • This one outputs pivoted knowledge so that you get one row per AdPhrases entity (e.g., a marketing campaign, advert, or key phrase).
  • Segments for the factor that’s being reported shall be proven as columns (e.g. day of week = Monday, day of week = Tuesday, and many others.).

Get this script and its instructions.


Ads scripts are a fantastic resolution to chop down on the guide labor concerned in repetitive PPC duties like reporting.

If you’re able to strive it out, contemplate one among these free reporting scripts as a substitute for paid options for shifting Google Ads knowledge into spreadsheets.

All these scripts do is transfer knowledge from an advertisements account right into a spreadsheet, so that they’re low danger for a beginner to strive.

The worst you are able to do is mess up a spreadsheet. These scripts received’t make adjustments to your advertisements account.

And with the time financial savings you may obtain by now not having to manually transfer advertisements knowledge, you’ll have extra assets to dedicate to the upper worth work you deliver to PPC administration.


Continue Reading Below

More Resources:

Featured Image: Studio Romantic/Shutterstock

Related Articles

Leave a Reply

Back to top button