Google AdWords Report Automation Using AWQL

As digital marketers, applying automation to daily or regular tasks frees up a lot of time, which can be better spent doing analysis and research. This is especially the case when it comes to Google AdWords reporting, which can take up a lot of quality time if done entirely manually. This article will provide some guidance on how to automate Google AdWords reporting using AWQL. Let’s begin by talking about AdWords query language.

What is AdWords Query Language (AWQL)?

AdWords has a very simple SQL query-like engine to query reports. The reports created using AWQL can be scheduled to run automatically. The AWQL library is also available in many languages including PHP, .NET, Python, Java, Ruby etc. Using Google App scripts for automation purposes is beneficial, especially when using spreadsheets to automate reporting requirements.

The formal AWQL grammars are given for reference below.

Statement    -> SelectClause FromClause1 WhereClause?

                 DuringClause2 OrderByClause? LimitClause?

SelectClause  -> SELECT ColumnList

FromClause   -> FROM SourceName

WhereClause   -> WHERE ConditionList

DuringClause  -> DURING DateRange

OrderByClause  -> ORDER BY Ordering (, Ordering)*

LimitClause   -> LIMIT StartIndex , PageSize

ConditionList  -> Condition (AND Condition)*

Condition    -> ColumnName Operator Value

Value      -> ValueLiteral | String | ValueLiteralList | StringList

Ordering    -> ColumnName (DESC | ASC)?

DateRange    -> DateRangeLiteral | Date,Date

ColumnList   -> ColumnName (, ColumnName)*

ColumnName   -> Literal

SourceName   -> Literal

StartIndex   -> Non-negative integer

PageSize    -> Non-negative integer

Operator    -> = | != | > | >= | < | <= | IN | NOT_IN | STARTS_WITH | STARTS_WITH_IGNORE_CASE |

          CONTAINS | CONTAINS_IGNORE_CASE | DOES_NOT_CONTAIN | DOES_NOT_CONTAIN_IGNORE_CASE |

          CONTAINS_ANY | CONTAINS_NONE | CONTAINS_ALL

String     -> StringSingleQ | StringDoubleQ

StringSingleQ  -> ‘(char)‘

StringDoubleQ  -> “(char)“

StringList   -> [ String (, String)* ]

ValueLiteral  -> [a-zA-Z0-9_.]*

ValueLiteralList -> **[** ValueLiteral (, ValueLiteral)* **]**3

Literal     -> [a-zA-Z0-9_]*

DateRangeLiteral -> TODAY | YESTERDAY | LAST_7_DAYS | THIS_WEEK_SUN_TODAY | THIS_WEEK_MON_TODAY | LAST_WEEK |

          LAST_14_DAYS | LAST_30_DAYS | LAST_BUSINESS_WEEK | LAST_WEEK_SUN_SAT | THIS_MONTH

Date      -> 8-digit integer: YYYYMMDD

The regular reporting task itself takes a lot of time when you are managing campaigns for multiple territories. Using the Google App script to automate the tasks in Google spreadsheets is convenient, because users can make template dashboards which will automatically update based on requirements.

How to automate AdWords using apps Script

The scripts can be created in the “Bulk Operations” section of the Google AdWords campaign being run as displayed in the screenshot below.

The automate script can be scheduled to run based on user requirements

Scheduling can be done after creating the scripts, which can be scheduled to run hourly, daily, weekly, monthly etc.

The language used for automation is Google Apps script, which is JavaScript itself. If you are familiar with JavaScript, then the scripting is very simple. The functions used to access all platforms such as AdWords, Sheets, Docs should be trained.

The steps involved in accessing reports from AdWords using AWQL and writing it into Google spreadsheets is provided below. There is a procedure main () in the Google AdWords scripting, and the execution of script starts from that function.

1. Create a new Script in AdWords
2. API should be initialised with API version in the beginning.
3. As we are exporting the automated report to a Google spreadsheet, the full URL of the google spreadsheet should be linked with the automation script.
4. The AdWords time zone may be different than the spreadsheet, because it may have been stored in different servers and locations. In order to synchronise these time zones, execute the following statement. The spreadsheet is the object ‘SpreadsheetApp’.
5. To retrieve the report from AdWords, the function displayed below should be executed. The database from where the data is to be taken is available in the documentation here. The following report is executed from ‘ACCOUNT_PERFORMANCE_REPORT’.
6. In most reporting cases there should be a date range that you want to specify. The date range should be specified as ‘start date to end date’ in the format ‘YYYYMMDD,YYYYMMDD’. A simple procedure to find the date range for a previous month or an older month is given below. The parameter to be passed should be the index of the previous month. For example: if you pass variable ‘prevMonthIndex’ as ‘1’, then the full date range of the previous month is taken and ‘2’ is the month before last month and so forth. The same procedure could be applied for any number of previous months.

The full source code is available here.

Common use cases of AdWords scripts

As well as for reporting automation purposes, there are many other use cases for AdWords scripts. Some of the use cases are explained below.

• Increase keyword bid when the quality score and CTR is high.

• Create a historic quality score report in a spreadsheet.
• Find out keywords that may be under-performing after optimisation.
• Make your own business logic specific ROI metrics.
• Alert when there is a sudden increase in searches due to some unforeseen conditions.

Conclusion

This article provides only an overview of how AWQL could be used to automate reporting tasks. But there are a number of other procedures in AdWords to improve the overall ROI of your campaign. If you want to get the most out of your AdWords campaign, why not contact our experts?