Digital marketers operating on Adwords often have to spend a lot of time downloading reports from Adwords.
The most painful part is when we need to download the same report repeatedly.
When it comes to repetitive tasks, Adwords has helped to simplify it by allowing us to use Adwords Scripts.
Adwords Scripts is a way to utilise Javascript to automate tasks on Adwords. Apart from working directly on Adwords, it can also interact with an external data source.
In this post, I will introduce a basic script to pull a report from Adwords and stream it into Google Sheets.
To access Adwords Scripts, click the spanner icon at the top right of the new Adwords interface. You should see "Scripts" under Bulk Actions.
After clicking, you should see a list of scripts you already have, or empty if it's your first time there. Click on the huge blue "+" button to start writing your script.
Next, you should be in the Adwords Scripts IDE. To get started hit the "Authorize" button and agree to the terms.
You're now ready to go.
This part is pretty straightforward and it resembles Google Apps Script's methodology to connect to a Google Sheets.
This is done via the SpreadsheetApp utility (detailed documentation).
var sheetId = "your sheet ID";
var ss = SpreadsheetApp.openById(sheetId);
var sheet = ss.getSheetByName('your sheet name');
All the code should be under the "function main { }" part of the code unless you're writing another function.
You can get the sheet ID from the sheet's URL.
The sheet name is the name of your tab.
You can do more once you have the sheet object. One possible thing to do is to get the number of populated rows.
var columnA = sheet.getRange("A1:A").getValues();
var lastRow = columnA.filter(String).length;
The lastRow variable will contain the length of populated data on column A.
This is especially useful if you're building a report and you would like the schedule new data to be added. You need to know on which row your new data will be added from and you can use the lastRow variable as a starting point.
We'll be using the lastRow and sheet objects later on.
To define the dimensions we want to pull from Adwords, we need to use Adwords Query Language (AWQL).
This is the same query that is used in the QUERY formula in Google Sheets that was covered briefly in my first post.
Also with any Adwords report, we need to define the type of report to pull. The following example code combines AWQL with the KEYWORD_PERFORMANCE_REPORT to pull some basic keyword metrics.
var fields = ["Week", "Criteria", "CampaignName", "AdGroupName", "QualityScore", "Impressions", "Clicks", "Cost", "Conversions"];
var query = "SELECT " + fields.join(", ") +
" FROM KEYWORDS_PERFORMANCE_REPORT" +
" WHERE AdNetworkType1 = SEARCH AND HasQualityScore = 'TRUE'" +
" DURING LAST_7_DAYS";
var reportOptions = {
apiVersion: 'v201802'
};
The "fields" variable define the fields I want, while the "query" variable defines it into an AWQL query where I've filtered for only SEM keywords that have quality scores (i.e. keywords with sufficient traffic) within the last 7 days.
Please update the apiVersion to the latest version if 201802 is not the latest.
Once we have all the query parameters ready, we execute the following to create the AdwordsApp.report object.
var report = AdWordsApp.report(query);
This step is kind of weird, but it's how Adwords Script works with the report object.
We need to run through the ReportRowIterator object to get the reporting data (as defined by our parameters) row by row.
To do that, we have to get the iterator object first.
var reportRows = report.rows();
Next, we'll initialise an empty array object ("rows") and populate each row of the iterator's data into that empty array.
var rows = [];
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);
}
The "row" variable has all the data we need from the reporting parameters we defined.
Now it all comes full circle. We'll use the variables we defined during the first step ("sheet" and "lastRow") to link Adwords Script to Google Sheets to upload or stream the data to Google Sheets.
var range = sheet.getRange(lastRow + 1, 1, rows.length, fields.length);
range.setValues(rows);
We always need to define the dimension of columns and rows we're updating with SpreadsheetApp, hence the reason why sheet.getRange has 4 parameters.
The first one is the starting row to update the data. As mentioned earlier, we want to append new data to the sheet on a schedule. Hence, the new data starts from the last row plus one.
The second parameter is the starting column. Here, we use column A i.e. 1st column.
The third parameter is the number of rows we're updating. This is easy to get, as it is the length of the rows array that we've appended the iterator data onto.
The final parameter is the number of columns. This is tricky, but we actually had it from our "fields" variable that defines the column of metrics/attributes we're pulling from Adwords. We get the number of columns by getting the length of the fields variable.
The second row, "range.setValues(rows);", updates the report's defined range with the report data.
And that's all. You've managed to write a simple script to pull some data from your Adwords account.
With this, you can start automating all your reporting work and reduce the amount of copy and pasting you need to do on a daily/weekly/monthly basis.
Several other features you can add explore:
Have fun with Adwords Script!
Like the post? Consider donating to fund the maintenance of this website: