AdWords Scripting & The AWQL (AdWords Query Language)

_It’s been mentioned in previous blog posts that AdWords – and PPC in general – can be a pretty complex system to get around. You have to get accustomed to the nuances of the software, and how little changes in one area can have a huge impact in others. _

Recently, I’ve been learning about the scripting side of things to produce dashboards in Google Drive, as well as a few specialised reports to help us keep tracking of things like daily changes in average quality score.

Nifty Abilities With AdWords Scripting

This is all programmed through the use of Javascript and while it is a fairly straightforward language, it can get a bit tricky if you’re not familiar with it.

It is quite powerful however. Recently, we built a script that takes elements from a website and uses them to dynamically populate parameters for ad copy. This way, when the information changes on the site, the scripts picks up these changes and the ad copy is amended. And, because it’s a parameter, all ad history is preserved, as you’re not actually changing it with the traditional [delete/replace] method that AdWords uses.

Reporting With SQL

In the past, I’ve helped streamline the reporting process for us, and one of the last major updates I made to our system was through the use of SQL. I got familiar with this language and how to use it through Excel, and it was invaluable as we were able to reduce the time for processing reports. The SQL queries could now pull in data from other Excel files, with no additional effort required by the user other than opening the reporting template.

An evolved version of this is in use just now, with databases of information being stored on a server and some clever Excel SQL being used to dynamically update a template file each month. It’s reduced the report production time significantly, which is an important thing to do in an expanding business. I’ll let the team that built it boast about how great it is and what they did to achieve it, because what I want to talk about is the AdWords Query Language that I discovered while doing research for the API.

AWQL (The AdWords Query Language)

This is a language very similar to SQL and uses the same basic commands such as SELECT, WHERE, DURING to help you quickly pull statistics and data out of AdWords in an automated fashion.

In a nutshell, it lets you pull out the different reports types that you can generate from AdWords, such as the campaign, ad group or keyword reports. Instructions on how to use it as well as a few example scripts can be found hereat the Google Developers site.

A full list of the different reports you can access and the metrics you can extract from them can also be found here.

When It’s Best To Use AWQL

Obviously, if you’re just looking to get a standard report, using this to email yourself a weekly keyword report is a bit overkill, as there’s top level options to take care of that for you. But, in a similar way to the Analytics API, I can use the AWQL to quickly select specific data from AdWords reports and send that directly to a Google Drive document, where I can then manipulate it to create active dashboards or custom reports of my choosing. It might sound very similar to using the API scripts but I found that due to the nature of the language it was a bit easier to use and understand just what it was doing.

As I mentioned above, one of the things I’ve built using this is a monthly/weekly dashboard that updates periodically throughout the day. It’s a bit more useful than using the web interface because we can customise it to more readily show the performance of core keywords and ad groups, measure spend v remaining budget, or keep track of keywords that have low quality scores, and the metrics that are potentially driving that.

An AWQL Example

Below is a script that helps us build a dashboard in Google Drive.

This is a basic script to grab key metrics from an account. It takes the campaign level report data for the last 30 and 7 days, this month and last, as well as the account performance level data for 2013 to the end of 2014.

This lets us put together a top level dashboard that shows us campaign level performance over a more recent time frame, as well as an account level overview that can show us changes month on month all the way back to the beginning of 2013.

It’s worth being aware that there is a limit to the number of rows of data that can be processed by a single function, which is why I’ve had to split the 2013 and 2014 stats – so watch out for that if you find that the data you’re pulling is being truncated. You’ll have to adjust the date ranges of the functions to get around this.

It’s also worth noting that in the example above (which is based on the examples provided by Google), the format of the function has been adjusted to make it a bit more readable, but it would also work when you write it as a single line like so:

‘SELECT CampaignName, Clicks, Impressions, Cost, Conversions FROM CAMPAIGNPERFORMANCEREPORT WHERE Impressions > 0 DURING LAST30DAYS’

So if you find you’re getting errors with your queries – I know I was – putting it all back into one line may help you more easily debug it.

The Costs And Benefits Of Scripting

Scripting is a difficult thing to get into, especially if you’re unfamiliar with coding, but it is something I would definitely recommend doing, as it helps make managing AdWords a little easier and opens up a few more elements of functionality.

If you’ve already used AWQL, have any personal tips for scripting, or just want to boast about something awesome you’ve been able to do, let us know.