Automating Monthly Reporting Using AdWords Scripts

in AdWords Scripts

This post explains how to create a performance report using an AdWords Script that extracts data from an account into a spreadsheet. I'll show you how to build a report displaying basic keyword stats, and how to schedule the report so that it's automatically generated on the first day of each month. You should then be able to build on my example to create a full monthly performance report suitable for sending to your clients.

Google has already published tutorials explaining how to create performance reports, but they have since added the full AdWords API reporting capabilities to scripting, so many more people can now create sophisticated reports without needing to buy a PPC reporting tool…and maybe I'll be out of a job as well!

I'm going to show you how to build a Google spreadsheet that displays the impressions, clicks, costs, CPC, CTR, average position, conversions, revenue, and ROI for the top ten keywords—based on the number of clicks—during the previous month. Once finished, the report will look something like this:

Report Showing the Top Ten Keywords Last Month
Report Showing the Top Ten Keywords Last Month

Here's the steps for creating the report:

  • Set up a spreadsheet template to hold your report.
  • Build a script to prepare the report.
  • Install the script and schedule it to run once a month.

I will explain each step in detail, but if you find anything confusing then please post a comment so I can make it clearer.

Setting Up the Spreadsheet

So, lets start by creating a spreadsheet to hold the keyword performance data we export from AdWords. I will build a template spreadsheet using the following steps:

  • Create a new spreadsheet with a sheet to hold your report.
  • Add a data sheet to hold the AdWords extract.
  • Link the top ten keywords in the data sheet to the report sheet.
  • Format the report.

The script will copy this spreadsheet each month and populate it with data.

Creating the Report Worksheet

Google documents are all administered via Google Drive now, so the first step is to go to your drive and create a new spreadsheet then add the headings:

The Report Structure
The Report Structure

We'll be formatting the report later, but for now I'm just going to merge the top three rows horizontally and add a border to the keywords table. To merge the cells, select the cells from A1 to L3 and use the Format > Merge Cells > Merge horizontally option:

Merging Cells in a Google Spreadsheet
Merging Cells in a Google Spreadsheet

Now select the cells from A4 to L14 and add a border:

Adding a Border to a Google Spreadsheet
Adding a Border to a Google Spreadsheet

Next, we're going to create a totals row; select cell D15, enter =su, choose the SUM option from the drop-down menu, select rows D4 to D14, then press Enter:

Calculating the Total for a Range of Cells
Calculating the Total for a Range of Cells

Drag the blue square at the bottom right corner of the cell across to cell L15 to copy the formula, and also add a border while the cells are selected:

Copying a Cell Across a Range
Copying a Cell Across a Range

A problem with this, which you might have noticed, is that you shouldn't use a SUM operation on the average columns; instead, you need to calculated them using values from the totals row. In fact, I'm just going to calculate the CPC, CTR, and ROI rather than pulling them out of the API. Enter the following expression into cell G4:

=IF(E5 > 0, F5/E5, 0)
Average CPC: Cost / Clicks

Copy the formula down to cell G15 (including the total cell), then across to the CTR column:

Copying a Range
Copying a Range

Do the same for the ROI column using the following formula:

=IF(F5 > 0, (K5-F5)/F5, 0)
Return on Investment: (Revenue - Cost) / Cost

We now need to deal with the remaining average column: Avg Position. The problem with this is that we don't have the totals to use in our calculation, and they're not available in AdWords; however, we do have the average position and total impressions for each keyword, so we can work backwards to the original divisor used in the averaging calculation by multiplying the average position of each keyword by the impressions that it received. Place the following formula into a new column at the end of the table, and copy it down to cell M15:

=I5*D5
Sum of Positions for all Impressions: Avg Pos * Impressions

This can now be used together with the total impressions to get the average position of all the keywords:

=SUM(M5:M14)/D15
Total Avg Pos: SUM(Avg Pos * Impressions) / SUM(Impressions)

One final problem to deal with is the extra column of data at the end of the table. You can sort this out by hiding the column; select the column and use the contextual menu to choose Hide column:

Hiding a Range in a Google Spreadsheet
Hiding a Range in a Google Spreadsheet

Adding the Data Worksheet

We now have a basic report, but without any AdWords data it's not much use. I'm going to import the keywords into a separate sheet to the main report, then link the two together. This will make testing the report easier, the script a bit simpler, and you could also use the data in other areas of the report. Insert a new worksheet using the Insert > New sheet menu item:

Adding a Sheet to a Google Spreadsheet
Adding a Sheet to a Google Spreadsheet

This will create a sheet, called “Sheet2”; you need to rename it to “Keyword Data” using the Rename option of the sheets contextual menu (at the bottom left of the screen):

Renaming a Sheet
Renaming a Sheet

This will bring up a dialog for you to enter the new sheet name:

The Rename Sheet Dialog
The Rename Sheet Dialog

While you're at it, you should also rename the report sheet, then add in some test data to check everything is working. You can use this TSV file from my original report; you should be able to just cut-and-paste it into the worksheet:

Test Data for the Keyword Performance Report
Test Data for the Keyword Performance Report

You can see that I've also added the text “March, 2013” to cell K2, which contains the date of the report; we'll be using it later to create a dynamic report title.

Formatting the Report Sheet

The report is now fully functional and we'll be populating it with live data in the next section, but first I'm going to format the report to make it easier to read and a bit more attractive. Here's the modifications I'm going to make:

  • Center everything apart from the “Total:” text, which will be right aligned.
  • Change the font settings to make the headings, totals, and positions more prominent.
  • Format the numbers as either integer, floating point, currency, or percent data types.

I'll deal with the first item by highlighting everything and pressing the Center Align button:

Setting the Cell Alignment
Setting the Cell Alignment

Now select the “Total:” cell and make it right aligned and bold. Then make the following font changes:

  • set the report title at A1 to 14pt and bold,
  • set the table title at A3 to 12pt bold and italic,
  • set the table column titles from A4 to L4 to 11pt and bold,
  • set the totals row from D15 to L15 to 11pt and italic,
  • set the position column from A5 to L14 to bold.

Next, set the background color of the column titles and totals row to light-gray:

Formatting the Background Color of a Cell
Formatting the Background Color of a Cell

Now we're going to format the numbers; select cells D5 to E15 and use the Format > Number > 1,000 menu item to change them into a comma separated integers. In the same way, make the following changes:

  • format the cells F5 to G15 and K5 to K15 as currencies,
  • format the cells I5 to I15 as floats with one decimal place,
  • format the cells J5 to J15 as integers,
  • format the cells H5 to H15 and L5 to L15 as percentages with two decimal places.

Finally, you can add some vertical space on the title and total rows and center them vertically:

Vertical Alignment in a Google Spreadsheet
Vertical Alignment in a Google Spreadsheet

Building the Script

OK, now we have a template report and can move on to writing some code to populate it with data. The script needs to do the following:

  • Copy the template spreadsheet to create a new report.
  • Export the keyword performance data from AdWords.
  • Import the data into the spreadsheet and sort it by number of clicks.

In case you're eager to get some real data into your report immediately, here's the full script I'm going to explain in detail over the next few sections of the tutorial:

function main() {
    /*
     * *************** Prepare the spreadsheet ****************
     */
    // The URL of the template spreadsheet
    var spreadsheetUrl = "YOUR_TEMPLATE_URL";
 
    // Open the template spreadsheet
    var templateSpreadsheet = SpreadsheetApp.openByUrl(spreadsheetUrl);
 
    // Get the name of the previous month
    var monthNames = [
        "January", "February", "March", "April", "May", "June", "July",
        "August", "September", "October", "November", "December"
    ];
    var today = new Date();
    today.setMonth(today.getMonth() - 1);
    var month = monthNames[today.getMonth()] + ", " + today.getFullYear();
 
    // Create a new copy to hold this month's report
    var newSpreadsheetName = "AdWords Performance Report: " + month;
    var spreadsheet = templateSpreadsheet.copy(newSpreadsheetName);
 
    // Get the sheet that holds the data
    var sheetName = "Keyword Data";
    var sheet = spreadsheet.getSheetByName(sheetName);
 
    // Make sure the data sheet is clear
    sheet.clear();
 
    // Add the report date to the data sheet
    sheet.getRange(1, 10).setValue("Report Date:");
    sheet.getRange(1, 11).setValue(month);
 
    /*
     * *********** Export the report from AdWords **************
     */
    // The fields to retrieve from the API
    var fields = [
        "KeywordText", "KeywordMatchType", "Impressions", "Clicks",
        "Cost", "AveragePosition", "Conversions", "ConversionValue"
    ];
 
    // Define the report
    var awql = "SELECT " + fields.join(",") +
            " FROM KEYWORDS_PERFORMANCE_REPORT " +
            " DURING LAST_MONTH";
 
    // Additional reporting API options        
    var reportOptions = {
        includeZeroImpressions: false,
        apiVersion: 'v201302'
    };
 
    // Generate the report and extract its rows
    var reportRows = AdWordsApp.report(awql, reportOptions).rows();
 
    /*
     * ********** Import the data into the spreadsheet **************
     */
 
    // An array to hold the rows to insert into the report
    var rows = [];
 
    // Add field names as a header row
    rows.push(fields);
 
    // Iterate over the report rows
    for (var rowNum = 0; reportRows.hasNext(); rowNum++) {
        // Construct the row
        var reportRow = reportRows.next();
        var row = [];
        for (var colNum = 0; colNum < fields.length; colNum++) {
            row.push(reportRow[fields[colNum]]);
        }
        rows.push(row);
    }
 
    // Get the cells to hold the keyword data
    var range = sheet.getRange(1, 1, rows.length, fields.length);
 
    // Add the rows to the spreadsheet
    range.setValues(rows);
 
    // Sort the rows by the number of clicks
    range.sort({column: 4, ascending: false});
}
Complete Script that Creates the Report

To get this working in your own account, see the section on “Installing and Scheduling the Script” later in this tutorial.

Copying the Template to Create a New Report

The script starts by preparing a fresh copy of the report template. The first command opens the template:

var spreadsheetUrl = "YOUR_TEMPLATE_URL";
var templateSpreadsheet = SpreadsheetApp.openByUrl(spreadsheetUrl);
Code to Open a Google Spreadsheet

This takes the URL of your spreadsheet—which you need to enter into the spreadsheetUrl variable—and places a reference to it into the templateSpreadsheet variable. Next, the script derives the name of the previous month for use in the name of the new spreadsheet:

var monthNames = [
    "January", "February", "March", "April", "May", "June", "July",
    "August", "September", "October", "November", "December"
];
var today = new Date();
today.setMonth(today.getMonth() - 1);
var month = monthNames[today.getMonth()] + ", " + today.getFullYear();
Formatting the Report Date

There aren't many options for formatting dates in JavaScript, so the program holds the name of each month in an array: it obtains last month's name by subtracting one month from today's date and referencing the month's index in the monthNames array. To generate a unique name for each report, the script appends the long format of the year—which includes the century—to the month name, then creates the new copy of the template spreadsheet:

var newSpreadsheetName = "AdWords Performance Report: " + month;
var spreadsheet = templateSpreadsheet.copy(newSpreadsheetName);
Creating a New Copy of the Template

Finally, the script opens the data sheet, makes sure it's empty, and adds the report date that's used in the report title:

var sheetName = "Keyword Data";
var sheet = spreadsheet.getSheetByName(sheetName);
 
sheet.clear();
 
sheet.getRange(1, 10).setValue("Report Date:");
sheet.getRange(1, 11).setValue(month);
Preparing the Data Sheet

Exporting the Keyword Performance Data From AdWords

Now that the initial work is out-of-the-way, we can get to the real meat of this tutorial: extracting performance data from an AdWords account. We're going to use the AdWords Query Language (AWQL) feature of the AdWords API to build a report definition, then submit it to the AdWords API report service using the AdWordsApp.report() method.

AWQL is an SQL like language introduced into the AdWords API as an intuitive way to request data from several of the API's services. For reporting, you need to supply a SELECT, FROM, and DURING clause; there's also an optional WHERE clause, which I'll come back to later. Here's the basic structure of an AWQL query:

SELECT [fields]
FROM [report_name]
DURING [date_range]
Basic AWQL Structure

You need to replace the [fields] with a list of report column names, the [report_name] with a report type, and the [date_range] with either a date range literal or a start and end date. I'm not going to cover AdWords API reporting in detail here, but you can get the fields and report names from the AdWords API report reference and the date range format from the AWQL Guide; there's also a good official guide to matching API reports with the UI.

For this tutorial I want to get the keyword text, keyword match type, impressions, clicks, cost, average position, conversions, and conversion value columns for the keyword performance report over the past month:

 
Keyword Performance Report AWQL Query

Converted to JavaScript, the code is as follows:

var fields = [
    "KeywordText", "KeywordMatchType", "Impressions", "Clicks",
    "Cost", "AveragePosition", "Conversions", "ConversionValue"
];
 
var awql = "SELECT " + fields.join(",") +
        " FROM KEYWORDS_PERFORMANCE_REPORT " +
        " DURING LAST_MONTH";
JavaScript AWQL Query

Because I need an array of fields later, I've first put the fields into an array, then joined it together using the join() method. The script also contains supplemental query configuration using the reportOptions parameter:

var reportOptions = {
    includeZeroImpressions: false,
    apiVersion: 'v201302'
};
Optional Report Parameters

This is specifying two additional options:

  • includeZeroImpressions: Filters out keywords that haven't received any impressions during the selected time period; I've set this to false as I don't need these keywords and it makes the report much smaller.
  • apiVersion: Selects the current version of the AdWords API; Google releases new versions of the API several times a year, so it's best to explicitly select the version you want to use so your code doesn't stop working when things it relies on get changed.

Now the report's been defined, it can be submitted to the API:

var reportRows = AdWordsApp.report(awql, reportOptions).rows();
Retrieving the Report from the AdWords API

This code runs the report and retrieves its rows, ready for importing into the spreadsheet.

Importing the Data into the Spreadsheet and Sorting the Rows

We now have the rows of the report, but they're not in the correct format for insertion into a spreadsheet: the rows() method of the Report object doesn't return a two-dimensional array, it returns a ReportRowIterator with methods to scroll through a collection of row objects that contain a property for each column in the report. You can access each item of data using the associative array object notation:

var rows = [];
rows.push(fields);
 
for (var rowNum = 0; reportRows.hasNext(); rowNum++) {
    var reportRow = reportRows.next();
    var row = [];
    for (var colNum = 0; colNum < fields.length; colNum++) {
        row.push(reportRow[fields[colNum]]);
    }
    rows.push(row);
}
Looping through the Rows in the Report

This initializes an array to hold the rows and adds the field names as a header row, then it goes through each row in the report using the hasNext() and next() methods. It uses the row from the report to create an array of values by looping through the array of fields and using the field name to extract the data. Once the rows have been extracted into a two dimensional array, they can be inserted into the spreadsheet:

var range = sheet.getRange(1, 1, rows.length, fields.length);
range.setValues(rows);
Inserting the Rows into the Data Sheet

This is referencing a range of cells of the same size as the two-dimensional array of report data, then inserting the array into it using the setValues() method. I also tried approaching this by appending the data to the sheet on each iteration of the report rows, without using the intermediate array, but I found that inserting all the data in one step was a bit faster, which could be significant on a larger set of keywords.

This now shows the first ten keywords in the data sheet, but they won't necessarily be the best performing keywords. So, the last step of the script sorts the range of cells in descending order of clicks:

range.sort({column: 4, ascending: false});
Sorting a Range

Here the script is using the sort() method of the range object to sort the fourth column of data in descending order. Once sorted, the report will update to reference the top ten keywords during the previous month. If you want to sort by a different column, just change the column property of the sort specification.

Installing and Scheduling the Script

So, now you've got a spreadsheet and script, all that remains is to add the script to your AdWords account, test it out, then schedule it to run each month. The scripts dashboard is reachable via the Create and manage scripts menu item of the Automate button on most of the campaign tabs in the AdWords UI:

The AdWords Automation Menu
The AdWords Automation Menu

This is what the dashboard looks like when you first open it up:

The AdWords Scripts Dashboard
The AdWords Scripts Dashboard

Click on the Create script button to open the script editor, remove any code that's already there, paste the code into the editor, and give the script a name:

The AdWords Script Editor
The AdWords Script Editor

Before you can use the script, you need to allow it to access your AdWords account using the Authorize now button:

Authorizing an AdWords Scirpt
Authorizing an AdWords Scirpt

This will open the Google OAuth dialog in a new browser window:

Granting an AdWords Script Access to an Account
Granting an AdWords Script Access to an Account

Press the Grant access button then close the dialog. Next, enter the URL of the template spreadsheet into the spreadsheetUrl variable:

Entering the Spreadsheet URL
Entering the Spreadsheet URL

You should normally now use the Preview button to check that the script won't make unexpected changes to your account, but the preview only runs for 30 seconds, which might not be enough time if you have many keywords, so just press the Run script now button (as long as you trust my script, of course!):

Running an AdWords Script
Running an AdWords Script

You'll need to confirm that you're happy to run the script without previewing it first:

Running a Script Without Preview
Running a Script Without Preview

Then the script will start to run, and you will be taken back to the scripts dashboard. The logs section of the dashboard should now show that your script has a status of “running” and the number of seconds it's been processing:

A Running AdWords Script
A Running AdWords Script

If something goes wrong—for example, you didn't enter the spreadsheet URL correctly—then the logs will show that the report didn't complete:

A Failed AdWords Script
A Failed AdWords Script

To see what the problem is, you can click on the View details link:

The AdWords Script Error Log
The AdWords Script Error Log

This shows that the URL was incorrect; after changing it and starting the script again, you should see a log confirming it has completed:

A Completed AdWords Script
A Completed AdWords Script

If you check your Google drive, you should see the new report:

The New Report in Google Drive
The New Report in Google Drive

You can now schedule the report to run each month by clicking on the Create schedule link next to the script:

AdWords Scripts Schedule Link
AdWords Scripts Schedule Link

This will reveal a panel with scheduling options:

Scheduling Options
Scheduling Options

It's best to leave at least three hours from midnight on the last day of the month before running the report to ensure that the month end processing has completed. After pressing the Save button, the script will be scheduled to run:

Script Scheduled to Run Monthly
Script Scheduled to Run Monthly

Refining and Extending the Report

The report's pretty basic at the moment, so you'll need to extend it into something you can send out each month. You'll also want to filter out some of the report rows, such as the data from the Google display network. I'll cover some additional topics in my next post to give you more assistance with automating your monthly reporting, but if there's something else you'd like to know about then please post a comment.

Comments

I'm keen to get feedback on my posts, so if you have any questions or comments, then please send me a message and I'll be happy to help.

David LeFevre

Thanks for putting this together. It really helped me solve a few problems I was trying to solve using Adwords Script

Reply

Chris

Wow really nice post. Thanks so much for breaking down each part of the script. Not too many people do that and for those of us who are strangers to java, it give us the ability to slowly grasp its power.

Reply

ewanheming

Hi Chris,

Thanks for the comment. It's good to see people learning to program and trying out AdWords scripts because it's not as hard as it seems if you break it down into small steps.

Reply

Wow! Very impressive Ewan. One of the better posts on this topic I've found!

Question, If I wanted to report on campaigns & ad groups on one tab, keywords on another new tab and ad copy on a third tab, would I need to create three scripts and a data tab for each? One data tab for campaigns & ad groups, one for keywords and another for ad copy.

Also, when using charts, do you think it's best to use the data from the report table, or data from the hidden data tabs?

Thanks again! Looking forward to reading your other posts.

Reply