A quick introduction to AWQL
AWQL is used to access reports through Adwords Scripts. It's very similar to SQL syntax, which is used to query databases - and so, deep down inside, is AWQL. What you basically do is send a query to the Adwords database, to return the performance stats you need.
An AWQL query consists of two mandatory, and multiple optional parts. Here's an example query from a script:
var report = AdWordsApp.report('SELECT CampaignName, Impressions, Clicks, Cost ' +
'FROM CAMPAIGNS_PERFORMANCE_REPORT ' +
'WHERE Impressions > 0 ' +
'AND Clicks < 1000 ' +
'DURING 20180501, 20180531');
What you've probably noticed is that there's an upper case word at the beginning of every line. These are called clauses and initate a statement in AWQL. The order is always the same:
We start with the SELECT clause, where we specifiy our columns and metrics.
Next up is the FROM clause. Here we tell Adwords, which report we want the stats to be pulled from.To see which reports are available via Adwords Scripts and which dimensions you can access, head over to the official reference page. Select a report and you will be presented with all the informations you need.
After that comes the WHERE clause. This is optional and gives us the possiblity to set filters. You string multiple filters together with an AND clause. Just remember that the first filter will always be initiated by a WHERE clause, all the following ones by an AND clause.
Last but not least, we have a DURING clause. Being optional as well, we may specifiy a specific time frame.
So as you can see, in the query above we're asking Adwords to return us a report with the following dimensions:
From the report type CAMPAIGNS_PERFORMANCE_REPORT
Where Impressions is higher than 0
And Clicks are lower than 1000
For the timeframe of May 1st, 2018 till May 31st, 2018
Here's a table of the different parts of an AWQL query:
|| Optional / Mandatory
|SELECT Dimension1, Dimension2, Dimension3, ...
|FROM Report Name
|WHERE Filter 1
|AND Filter 2
Filtering on dimensions in AWQL works in different ways, depending on the type the dimension has. This can be a bit frustrating at the beginning, so here's a guide to save you time.
Every column or dimension in an Adwords Report has a speficic type. Those can be integer (eg. 3), double (e.g. 2.7), a string (so text, like a campaign name), enumeration (a type of column where all values come from a predfined list, like keyword status) and string set (a type of column where the values come from a user defined list, like labels). The type of column determines the operators you use to filter on them.
Here's a quick overview of the different AWQL operators we might use to filter on a column / metric.
< <= > >= = !=
Integer operators are used for columns that have natural numerical values. E.g. Impressions or Clicks.
'WHERE Impressions = 0 '
'WHERE Clicks >= 5 '
Double operators are used for columns with numerical values, which have a decimal seperator. E.g. ClickThroughRate, ConversionRate or Cost.
'WHERE CTR > 5.0 '
= != STARTS_WITH STARTS_WITH_IGNORE_CASE CONTAINS CONTAINS_IGNORE_CASE DOES_NOT_CONTAIN DOES_NOT_CONTAIN_IGNORE_CASE
String operators are used for columns with text values not defined by the Adwords System. The values here are usually user selected like Campaign names or Adgroup names.
'WHERE CampaignName = Campaign1 '
'WHERE CampaignName CONTAINS 1 '
'WHERE CampaignName STARTS_WITH_IGNORE_CASE Camp '
= != IN  NOT_IN 
Enumeration operators filter on text columns, where the values are predefined by the Adwords system. Like Keyword status, for example.
'WHERE Status = PAUSED '
'WHERE Status NOT_IN ["PAUSED", "REMOVED"] '
String Set Operators
CONTAINS_ALL  CONTAINS_ANY  CONTAINS_NONE 
String set operators are used for columns with multiple possible user defined text values. E.g. label names.
//examples; Mind the different quotation marks here!
"WHERE LabelNames CONTAINS_ANY ['Label A', 'Label B'] "
AWQL gives us two options on how to specify a timeframe. We can choose from a handful of predefined ones, or select a custom range with dates. The supported predefined timeframes in AWQL are:
TODAY, YESTERDAY, LAST_7_DAYS, LAST_30_DAYS, ALL_TIME or YYYYMMDD, YYYYMMDD
If we want to choose a custom range, we may do that by using dates choosing the dates in the format yyyyMMdd, and seperating them with a comma. The first date is the start date, the second one the end date.
'DURING 20180701, 20180731'
Some more examples of AWQL queries
var report = AdWordsApp.report('SELECT AdGroupId, Status ' +
'FROM ADGROUP_PERFORMANCE_REPORT ');
var report = AdWordsApp.report('SELECT Criteria, Impressions, Clicks, Cost ' +
'FROM KEYWORDS_PERFORMANCE_REPORT ' +
'WHERE Clicks >= 10 ' +
var report = AdWordsApp.report('SELECT Query, KeywordTextMatchingQuery, QueryTargetingStatus, Impressions, Clicks, Ctr ' +
'FROM SEARCH_QUERY_PERFORMANCE_REPORT ' +
'DURING 20180501, 20180531');