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:
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:
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
L3 and use the Format > Merge Cells > Merge horizontally option:
Now select the cells from
L14 and add a border:
Next, we're going to create a totals row; select cell
=su, choose the
SUM option from the drop-down menu, select rows
D14, then press Enter:
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:
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
Copy the formula down to cell
G15 (including the total cell), then across to the CTR column:
Do the same for the ROI column using the following formula:
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
This can now be used together with the total impressions to get the average position of all the keywords:
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:
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:
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):
This will bring up a dialog for you to enter the new sheet name:
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:
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:
Now select the “Total:” cell and make it right aligned and bold. Then make the following font changes:
- set the report title at
A1to 14pt and bold,
- set the table title at
A3to 12pt bold and italic,
- set the table column titles from
L4to 11pt and bold,
- set the totals row from
L15to 11pt and italic,
- set the position column from
Next, set the background color of the column titles and totals row to light-gray:
Now we're going to format the numbers; select cells
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
- format the cells
I15as floats with one decimal place,
- format the cells
- format the cells
L15as percentages with two decimal places.
Finally, you can add some vertical space on the title and total rows and center them vertically:
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:
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:
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:
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:
Finally, the script opens the data sheet, makes sure it's empty, and adds the report date that's used in the report title:
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
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
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:
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:
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
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
falseas 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:
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:
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
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:
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:
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:
This is what the dashboard looks like when you first open it up:
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:
Before you can use the script, you need to allow it to access your AdWords account using the Authorize now button:
This will open the Google OAuth dialog in a new browser window:
Press the Grant access button then close the dialog. Next, enter the URL of the template spreadsheet into the
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!):
You'll need to confirm that you're happy to run the script without previewing it first:
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:
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:
To see what the problem is, you can click on the View details link:
This shows that the URL was incorrect; after changing it and starting the script again, you should see a log confirming it has completed:
If you check your Google drive, you should see the new report:
You can now schedule the report to run each month by clicking on the Create schedule link next to the script:
This will reveal a panel with 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:
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.