AWQL Tutorial & Generator

 

Our AWQL generator automatically generates a working AWQL query for you. Just pick your report, metrics, filters & timeframe. The report output is updated automatically and ready to copy and paste!

Benutzung:

  1. Choose a report
  2. Set dimensions & metrics
  3. (optional) Apply filters (if necessary)
  4. Choose timeframe
  5. Done. Ready to copy & paste :)

 

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:

CampaignName
Impressions
Clicks
Cost

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:

Query Part    Optional / Mandatory
SELECT Dimension1, Dimension2, Dimension3, ...    mandatory
FROM Report Name    mandatory
WHERE Filter 1    optional
AND Filter 2    optional
DURING Timeframe    optional

 

Filtering

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

< <= > >= = !=

Integer operators are used for columns that have natural numerical values. E.g. Impressions or Clicks.

//examples
'WHERE Impressions = 0 '
'WHERE Clicks >= 5 '

 

Double Operators

< >

Double operators are used for columns with numerical values, which have a decimal seperator. E.g. ClickThroughRate, ConversionRate or Cost.

//examples
'WHERE CTR > 5.0 '

 

String Operators

= != 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.

//examples
'WHERE CampaignName = Campaign1 '
'WHERE CampaignName CONTAINS 1 '
'WHERE CampaignName STARTS_WITH_IGNORE_CASE Camp '

 

Enumeration Operators

= != IN [] NOT_IN []

Enumeration operators filter on text columns, where the values are predefined by the Adwords system. Like Keyword status, for example.

//examples
'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'] "

 

Timeframes

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. 

//examples
'DURING YESTERDAY'
'DURING LAST_7_DAYS'
'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 ' +
                               'DURING LAST_7_DAYS');

var report = AdWordsApp.report('SELECT Query, KeywordTextMatchingQuery, QueryTargetingStatus, Impressions, Clicks, Ctr ' +
                               'FROM SEARCH_QUERY_PERFORMANCE_REPORT ' +
                               'DURING 20180501, 20180531');