AdWords Scripts are about to blow up. Even though they were released to all users in late 2012, I’m predicting 2013 will see a rush of innovation at the hand of resourceful PPC managers using AdWords scripts to overcome shortfalls of the AdWords platform, streamline campaign management, and integrate external data into campaigns.

Switched on AdWords management agencies should be eagerly skilling up their staff in JavaScript, or electing an AdWords Script champion to drive innovation and streamline reporting and optimisation processes for their teams.

There are thousands of problems that can be solved, reports that can be simplified, and new optimisation techniques implemented using AdWords scrips. Now it’s just up to the AdWords community to start developing and sharing scripts and ideas.

Creating an Quality Score Logger with AdWords Scripts and Google Docs

This simple script is an example of how AdWords Scripts can be used to overcome some of the shortfalls of the AdWords platform. One feature I know many AdWords managers wish they had access to is the ability to look up historical changes in Quality Score. While you can schedule a daily, or weekly keyword report that includes Quality Score, unless you download the report manually, the report will not be archived for later comparison.

This script uses Google Docs Spreadsheets to create a weekly keyword Quality Score report and Quality Score summary and save the spreadsheet to Google Drive, for you to refer to later when you want to compare historical Quality Scores. The script could be easily edited to use monthly or even daily data.

Step 1: Creating a new script

From the AdWords sidebar menu, choose Bulk OperationsScripts, and then Create Script.

Step 2: Paste The Code

Give your script a name like “Weekly Quality Score Report” and then paste the following code into the main code box:

[sourcecode language=”javascript”]
function main() {

var now = new Date();
var reportName = “Weekly QS Report – ” + Utilities.formatDate(now, “PST”, “yyyyMMdd”);
// Create a new spreadsheet (will have private access for you only).
var spreadsheet = SpreadsheetApp.create(reportName);
// Get the default sheet.
var sheet = spreadsheet.getActiveSheet();

/**
* Outputs Quality score related data to the spreadsheet
* @param {Sheet} sheet The sheet to output to.
*/

// Output header row
var header = [
‘Quality Score’,
‘Num Keywords’,
‘Impressions’,
‘Clicks’,
‘CTR (%)’,
‘Cost’
];
sheet.getRange(1, 1, 1, 6).setValues([header]);

// Initialize
var qualityScoreMap = [];
for (i = 1; i <= 10; i++) {
qualityScoreMap[i] = {
numKeywords: 0,
totalImpressions: 0,
totalClicks: 0,
totalCost: 0.0
};
}

// Compute data
var keywordIterator = AdWordsApp.keywords()
.forDateRange(‘LAST_WEEK’)
.withCondition(‘Impressions > 0’)
.get();
while (keywordIterator.hasNext()) {
var keyword = keywordIterator.next();
var stats = keyword.getStatsFor(‘LAST_WEEK’);
var data = qualityScoreMap[keyword.getQualityScore()];
if (data) {
data.numKeywords++;
data.totalImpressions += stats.getImpressions();
data.totalClicks += stats.getClicks();
data.totalCost += stats.getCost();
}
}

// Output data to spreadsheet
var rows = [];
for (var key in qualityScoreMap) {
var ctr = 0;
var cost = 0.0;
if (qualityScoreMap[key].numKeywords > 0) {
ctr = (qualityScoreMap[key].totalClicks /
qualityScoreMap[key].totalImpressions) * 100;
}
var row = [
key,
qualityScoreMap[key].numKeywords,
qualityScoreMap[key].totalImpressions,
qualityScoreMap[key].totalClicks,
ctr.toFixed(2),
qualityScoreMap[key].totalCost];
rows.push(row);
}
sheet.getRange(2, 1, rows.length, 6).setValues(rows);

// Let’s pull all keywords with impressions greater than 0 from the account
var keywordsIterator = AdWordsApp.keywords()
.forDateRange(“LAST_WEEK”)
.withCondition(‘Impressions > 0’)
.get();

// Write header row.
sheet.getRange(“A13”).setValue(“Campaign”);
sheet.getRange(“B13”).setValue(“Ad Group”);
sheet.getRange(“C13”).setValue(“Keyword”);
sheet.getRange(“D13”).setValue(“Max CPC”);
sheet.getRange(“E13”).setValue(“Impressions”);
sheet.getRange(“F13”).setValue(“Clicks”);
sheet.getRange(“G13”).setValue(“Cost”);
sheet.getRange(“H13”).setValue(“Quality Score”);

// Write body of report.
for (var row = 14; keywordsIterator.hasNext(); row ++) {
var keyword = keywordsIterator.next();
var stats = keyword.getStatsFor(“LAST_WEEK”);

sheet.getRange(“A” + row).setValue(keyword.getCampaign().getName());
sheet.getRange(“B” + row).setValue(keyword.getAdGroup().getName());
sheet.getRange(“C” + row).setValue(keyword.getText());
sheet.getRange(“D” + row).setValue(keyword.getMaxCpc());
sheet.getRange(“E” + row).setValue(stats.getImpressions());
sheet.getRange(“F” + row).setValue(stats.getClicks());
sheet.getRange(“G” + row).setValue(stats.getCost());
sheet.getRange(“H” + row).setValue(keyword.getQualityScore());
}

Logger.log(“Report ready! Visit the following URL to see it:”);
Logger.log(“https://docs.google.com/spreadsheet/ccc?key=” + spreadsheet.getId());
}
[/sourcecode]

Step 3: Authorise Your Script

Click Authorise now and then Grant access to allow your script to run. You’re almost there, now just click Save at the top of the code box.

Step 4: Schedule Your Script

Navigate back to the Bulk operations > Scripts page and click + Create schedule next to the script you have just created. Schedule your script to run weekly, on a day and time that suits you. If you work on a Monday – Sunday week, you might like to schedule the report to run at midnight every Sunday.

Step 5: Collect Your Data

The script exports data directly to a Google Docs Spreadsheet, simply visit Google Drive to view your archived reports.