Three Scripts to Get You Started with AWQL

By Jacob Fairclough | @SomeSecretJake | Senior Account Analyst at Hanapin Marketing

The AdWords Query Langauge (AWQL) is an extremely useful extension to AdWords scripts. While scripts allow you to access the entities in an account, AWQL adds the ability to access the AdWords API reports. All the data in the dimensions tabs, the segments, practically anything in your account is now accessible to you through a script. This makes is extremely easy to automatically keep tabs on certain areas of an account.

About a month ago, I made a video post on this topic. Video posts aren’t everyone’s thing and I received a few requests for more examples. Ask and ye shall receive.

If you already understand AWQL, great! If not, we’ll go over a quick overview before jumping in to the examples.

What is AWQL?

As stated earlier, AWQL allows you to access all the data tables regarding your account. AWQL is built for reporting. If you are using a script to pull data, switching to calling the data with AWQL is not only easy but also much faster, giving you more data in a more efficient manner.

As AdWords scripts are based off of JavaScript, AWQL is based off of SQL. If you’ve ever worked with a database or know someone who has, you may have heard about it. The great news is that even if you are uncomfortable with scripts, AWQL is written much more closely to plain English than the JavaScript.

To start off you will need to create a variable to hold the report data. Then you use AdWordsApp.report() to insert the AWQL.  Inside the parentheses you will specify your query and the script will pull the data for you. Following most examples you need to select the data you want, from a specific report, insert any conditions such as cost or clicks, and specify a date range. Easy right, but what does it look like?

Well, a little something like this (example provided by Google)

If you are wondering where all of this came from, it was pulled from the Google reference material. You can find all the reports here in the Google documentation. All of these fields can be found in the appropriate table, you just have to fill it in.

As you can see, we are selecting the AdGroup ID, the query, CTR, cost, and impressions from the search query report.We only want rows with over 10 impressions during the last 30 days. Each one of these can be changed and you can use the same framework for any report.

But what if you want to export this data? Well, you can create an iterator and paste each row into a spreadsheet. Or you can use a newer shortcut that exports automatically. All you have to do here is use exportToRow() on a spreadsheet. I’ll include this in each example; it sounds scarier than it is.

Overall, AWQL is pretty straightforward, though that does not mean you won’t need a little practice to become comfortable with it. Thankfully there a few examples below which should help you out.

Get all Keywords with a Low Quality Score

This is the script I went over in my last video blog. The script searches for keywords with a quality scores less than 5 and at least 1 impression over the last 7 days.

I want to create a new spreadsheet each time this runs so I make one of those on line 3. Then once I make my query, all the data is held in the report variable. I then take the report data, and export it to a spreadsheet.

[code]

function main() {

var spreadsheet = SpreadsheetApp.create(“Low Quality Score”);

var report = AdWordsApp.report(
“SELECT CampaignName, AdGroupName, KeywordText, QualityScore ” +
” FROM KEYWORDS_PERFORMANCE_REPORT ” +
” WHERE ” +
” QualityScore < 5″ +
” AND Impressions > 0″ +
” DURING LAST_7_DAYS”);

//var rows = report.rows();

report.exportToSheet(spreadsheet.getActiveSheet());
Logger.log(“Report available at ” + spreadsheet.getUrl());

}[/code]

Poor Converting Placements

In this script, we will access the placement report to find placements with a certain spend threshold and no conversions. This will help us quickly pare down placements that continue to spend money but don’t convert.

This script looks at placements on an individual level but you could get creative and add them together to find poor performing domains that don’t spend a lot but perform poorly over numerous ad groups and campaigns. One question you might ask is , “why multiply by 1,000,000?”. When filtering, the system goes by microunits or one millionth of the actual value. As a result you need 10,000,00 units to make up $10. The script below automatically multiplies the cost threshold by 1,000,000 and uses that value, removing a step for you.

[code]

function main() {

var spreadsheet = SpreadsheetApp.create(“Poor Placements”);

var cost_threshold = 10;
var costInMicroUnits = cost_threshold *1000000;

var report = AdWordsApp.report(
“SELECT Domain, CampaignName, AdGroupName, Cost ” +
” FROM AUTOMATIC_PLACEMENTS_PERFORMANCE_REPORT ” +
” WHERE ” +
“Cost > ” + costInMicroUnits +
” AND Conversions = 0″ +
” DURING LAST_30_DAYS”);

report.exportToSheet(spreadsheet.getActiveSheet());
Logger.log(“Report available at ” + spreadsheet.getUrl());

}

[/code]

Automatically Get All Converting Keywords from an SQR

Here is a script that gives us a list of every query that has converted. For reference we will also pull the campaign and ad group so we know where the query came from. This allows you to keep a log of all your converting keywords without having to keep downloading SQRs. Having it in a spreadsheet makes it easier to change where these keywords are eventually uploaded as well. You could take this spreadsheet and reload it through your editor immediately.

[code]

function main() {

var spreadsheet = SpreadsheetApp.create(“Converting Queries”);
var cost_threshold = 20;

var report = AdWordsApp.report(
“SELECT Query, CampaignName, AdGroupName, Conversions ” +
” FROM SEARCH_QUERY_PERFORMANCE_REPORT ” +
” WHERE ” +
“Conversions > 1″ +
” DURING LAST_30_DAYS”);

//var rows = report.rows();

report.exportToSheet(spreadsheet.getActiveSheet());
Logger.log(“Report available at ” + spreadsheet.getUrl());

}

[/code]

Where To Go From Here

We just scratched the surface today but there is so much more you can do. Right off the bat you can change any of the thresholds or date ranges to better fit your needs. You can easily include additional fields or pull from other reports. With a bit of extra work you can even right extra code to use this data to make changes to your account. If you are interested in some of the possibilities, check out this script by Google.

A Reminder About Work-Life Balance

Proper Work-Life Balance Is Something Easily Forgotten. Here Is A Reminder That It's Okay To Have A Life Outside Of Work & Constantly Checking Performance.