Reporting is one of the most time-consuming things we do in PPC. Any tool that can shave some time off this task will be a welcome addition to a search manager’s toolkit.
There are plenty of great free and paid solutions that make reporting easier (see some in Aaron Levy’s recent roundup), but most people overlook AdWords Scripts because they’re worried that this may be too advanced for them.
The good news is that others may have already written the code so if you know how to copy-and-paste, you can use a script to automatically put your AdWords reports into a spreadsheet.
I’ll share three recent free scripts I’ve written that do this. Once you have the data in a spreadsheet, you can deploy it to dashboards, calculated reports, and other automations.
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’re familiar with SQL and just need a quick data dump of an AdWords report into a spreadsheet, this first script will serve you best.
You get to write an AWQL query and the script simply executes the query and puts the resulting rows in a 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 AdWords has about your ads.
Example of AdWords data in a Google spreadsheet. This report was automated using an AWQL query and a simple AdWords script.
AWQL stands for AdWords Query Language and it’s a SQL-like language for querying AdWords reporting data.
While it’s powerful, it can’t sort or group data so you’ll have to plan a way to get the data in the right order in the spreadsheet.
But most account managers have their own set of functions and macros and what they most want to automate is getting the raw data into a spreadsheet and this script is ideal for that.
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 query, similar to SQL
- You’re willing to do all manipulation of the resulting data in the spreadsheet because sorting and grouping doesn’t work
- You can’t join data from multiple reports so if you need this type of thing you’ll have to handle it in the spreadsheet after you’ve downloaded all the reports
Get this script and its instructions
Script 2: You Prefer a WYSIWIG Report Builder
This next option is for you if you’d rather not deal with AWQL.
This script involves setting up a bit more code, but the extra code takes all Google’s documentation and puts it 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 AdWords reports and translates that into a bunch of dropdowns.
The end result is that you can bypass learning AWQL and you just select what you want in your report in the spreadsheet itself.
This script will add a new menu item to a Google sheet to make it easy to build a query for pulling in the right AdWords data.
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.
The second script to install for this is the AdWords 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 about this script:
- You’ll have to install two scripts: an AdWords Script and an App 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 and its instructions
Script 3: You Want Processed Data in Your Spreadsheet
This third option is best when you need more than just raw AdWords 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.
Screenshot from Google showing the available reporting columns for a keyword report from AdWords. Segments, metrics, and attributes are highlighted in different colors.
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 multiple lines for every AdWords element.
For example, if a keyword has delivered 2 signups and 1 call, 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:
This script puts AdWords data in a Google Sheet after converting different available values for segment into a column. This lets you view all data for an AdWords entitity 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
- Segments for the element that is being reported will be shown as columns
Get this script and its instructions
Conclusion
If you’ve always been curious about AdWords Scripts but you’ve hesitated to try them, consider one of these reporting scripts as your first foray into the scripts world.
All these scripts do is move data from AdWords into a spreadsheet, so they’re low risk for a newbie to try. The worst you can do is mess up a spreadsheet.
And with the time savings you can achieve by no longer having to manually pull AdWords data or paying for a tool to do this, you’ll have more resources to dedicate to the higher value work you bring to AdWords management.
