Adwords Script to track Quality Score on Account, campaign & Ad Group Level

Warning: this post is a bit more technical than most of our posts as we go into some depth on Adwords scripts. BUT – do not worry – if you follow the post through you will hopefully be able to just follow the instructions and achieve what you need to achieve with no coding at all. It is aimed at getting ALL PPC marketers using a basic Adwords script to track quality score.

About 6 weeks ago I read this brilliant post by Frederick Vallaeys where he gave up a simple Google Adwords script that allows you to track the Quality Score of an Adwords account every day in an automated fashion by keeping a daily log in a Google Document.

This was my introduction to Google scripts as to be honest I am not a coder and apart from basic HTML any kind of code scares me a little. However – Frederick gave the instructions and the code so all I had to do was copy and paste it into my clients Adwords accounts – the screen shot below shows you where this needs to go in your Adwords account. On the left hand menu go to:

Bulk Operations >> Scripts:

Google Adwords scripts


I started collecting the data and within 1 day I had a strong desire to track the quality score of campaigns and ad groups. I did a lot of digging around the Google Adwords developer forum for scripts and eventually found another blinding post on quality score tracking by Martin Roettgerding of Bloofusion.

In this post Martin explains how to track the quality score of your most important keywords over time – up to around 100 of them.

This was a step in the right direction BUT I really felt that what we needed for our clients was the ability to track campaigns and ad groups as that way we can prioritise time and effort efficiently. For example if a specific campaign makes up 20% of the total Adwords spend and it’s average quality score is just 5 then it is one that needs attention as there are large cost savings to be made.

Martin kindly gave me some advice after I left this query on his post and directed me to some Adwords scripts. I spent a frustrating morning trying to hack together scripts into a working format and unfortunately got nowhere. Luckily one of our team is far more adept with scripts than me and took the reigns. Within a few hours he had a working script that did what we wanted – it tracks quality score at account, campaign and ad group level.

Today we will be sharing that script with you.

Caution: Adwords scripts time out and break if they do not complete within a few minutes so if you have hundreds of campaigns and thousands of ad groups you will not be able to track everything with this script. The way we have been using it is to track the campaigns with the most spend and to then dig into those that have the lowest quality score on an ad group level so that specific actions can be taken – improving ad copy relevance and developing negative keywords, splitting out keywords into separate ad groups etc.

Step by step guide to tracking Adwords quality score by account, campaign and ad group

Step 1 – prepare a Google Spreadsheet

Copy a Google SpreadsheetHere is the spreadsheet template – click on this link and then click on “Make a copy” to copy it: https://docs.google.com/spreadsheet/ccc?key=0At6gVZSGVZXTdC1FdFNHcFZyWlQ2cUxUOWxVQjRCZVE&usp=sharing.

Make sure that the Account tabs contains Account name in 1st column, Campaign tabs must contain Campaign names in 1st column and Adgroup tabs must contain Campaign and Adgroup names in 1st and 2nd columns. This way you can decide which Campaign and Adgroups you want to track. This is important as if the account size is too big the script might time out as Google allows script run time to be only 30 minutes. In the sheet, I’ve included names such as Campaign-01 but they need to be changed with actual Account, Campaign and Ad group names.

2. Go to Google Scripts

In your Adwords account you should see on the left hand menu an option called “bulk operations” – click on that. There are 3 options; under scripts, click on “create and manage scripts”

There will be a green button that says +create script – click on that.

You should see a screen like this:

Create an Adwords script

3. Paste in the script

Paste in the following script ensuring that you replace any other script in the box in the Google interface;

do not copy the opening and closing code tags in square brackets:

[code language=”text”]

var spreadsheet_url = “https://docs.google.com/spreadsheet/ccc?key=0At6gVZSGVZXTdC1FdFNHcFZyWlQ2cUxUOWxVQjRCZVE&usp=sharing”;
var email_address = “myemail@mycompany.com”;

function main() {
var matches = new RegExp(‘key=([^&#]*)’).exec(spreadsheet_url);
if (!matches || !matches[1]) throw ‘Invalid spreadsheet URL: ‘ + spreadsheetUrl;
var spreadsheetId = matches[1];
var spreadsheet = SpreadsheetApp.openById(spreadsheetId);
var account_sheet = spreadsheet.getSheetByName(‘Account’);
var campaign_sheet = spreadsheet.getSheetByName(‘Campaigns’);
var adgroup_sheet = spreadsheet.getSheetByName(‘Adgroups’);
var account_sheet_values = account_sheet.getDataRange().getValues();
var campaign_sheet_values = campaign_sheet.getDataRange().getValues();
var adgroup_sheet_values = adgroup_sheet.getDataRange().getValues();
var result_range = new Array();
var adgroup_result_range = new Array();
var account_alert_text = new Array();
var campaign_alert_text = new Array();
var adgroup_alert_text = new Array();
var campaign_history = new Array();
var account_history = new Array();
var adgroup_history = new Array();
var currentTime = new Date();
var today = (currentTime.getMonth() + 1) + “/” + currentTime.getDate() + “/” + currentTime.getFullYear();

//Account QS Starts

for(i = 1; i < account_sheet_values.length; i++){
if(account_sheet_values[i][0] == “”) continue;
result_range[i] = [today, 0];
var account_name = account_sheet_values[i][0];
var latest_check = account_sheet_values[i][1];
var old_quality_score = account_sheet_values[i][2];
var totalImpressionsAnalyzed = 0;
var totalQualityScoreAnalyzed = 0;
var keywordIterator = AdWordsApp.keywords()
.withCondition(“Status = ACTIVE”)
.forDateRange(“LAST_30_DAYS”)
.withCondition(“Impressions > 0”)
.orderBy(“Impressions DESC”)
.withLimit(50000)
.get();

while (keywordIterator.hasNext()) {
var keyword = keywordIterator.next();
var qualityScore = keyword.getQualityScore();
var keywordStats = keyword.getStatsFor(“LAST_30_DAYS”);
var impressions = keywordStats.getImpressions();
var qualityScoreContribution = qualityScore * impressions;
totalQualityScoreAnalyzed = totalQualityScoreAnalyzed + qualityScoreContribution;
totalImpressionsAnalyzed = totalImpressionsAnalyzed + impressions;
}

var accountQualityScore = totalQualityScoreAnalyzed / totalImpressionsAnalyzed;
var FinalAccountQualityScore = accountQualityScore.toFixed(2);

// Save account quality score for results
result_range[i][1] = FinalAccountQualityScore;
// for the history we note the change
if(old_quality_score > 0) var change = (FinalAccountQualityScore – old_quality_score).toFixed(1);
else var change = “NEW”;
var column = [FinalAccountQualityScore];
account_history.push(column);
account_alert_text.push(FinalAccountQualityScore + “\t” + old_quality_score + “\t” + change + “\t” + account_name);
}

// write results to spreadsheet
result_range.splice(0,1);
account_sheet.getRange(2, 2, result_range.length, 2).setValues(result_range);
// write history to spreadsheet
var history_sheet = spreadsheet.getSheetByName(‘Account QS history’);
history_sheet.getRange(2,history_sheet.getLastColumn()+1, account_history.length, 1).setValues(account_history);
history_sheet.getRange(1,history_sheet.getLastColumn(), 1, 1).setValue(today);

//Account QS Ends

//Campaign QS Starts

for(i = 1; i < campaign_sheet_values.length; i++){
// make sure there is actually some data here
if(campaign_sheet_values[i][0] == “”) continue;
result_range[i] = [today, 0];
var campaign_name = campaign_sheet_values[i][0];
var latest_check = campaign_sheet_values[i][1];
var old_quality_score = campaign_sheet_values[i][2];
var totalImpressionsAnalyzed = 0;
var totalQualityScoreAnalyzed = 0;
var keywordIterator = AdWordsApp.keywords()
.withCondition(“CampaignName = ‘” + campaign_name + “‘”)
.withCondition(“CampaignStatus = ENABLED”)
.withCondition(“AdGroupStatus = ENABLED”)
.orderBy(“Impressions”)
.forDateRange(“LAST_30_DAYS”)
.withLimit(50000)
.get();

while(keywordIterator.hasNext()){
var keyword = keywordIterator.next();
var current_quality_score = keyword.getQualityScore();
var keywordStats = keyword.getStatsFor(“LAST_30_DAYS”);
var impressions = keywordStats.getImpressions();
var qualityScoreContribution = current_quality_score * impressions;
totalQualityScoreAnalyzed = totalQualityScoreAnalyzed + qualityScoreContribution;
totalImpressionsAnalyzed = totalImpressionsAnalyzed + impressions;
}

var CampaignQualityScore = totalQualityScoreAnalyzed / totalImpressionsAnalyzed;
var FinalCampaignQualityScore=CampaignQualityScore.toFixed(2);

// save quality score for results
result_range[i][1] = FinalCampaignQualityScore;
// for the history we note the change
if(old_quality_score > 0) var change = (FinalCampaignQualityScore – old_quality_score).toFixed(1);
else var change = “NEW”;

var column = [FinalCampaignQualityScore];
campaign_history.push(column);
// if we have a previously tracked quality score and it’s different from the current one, we make a note to log it and send it via email later
if(old_quality_score > 0 && CampaignQualityScore != old_quality_score){
campaign_alert_text.push(FinalCampaignQualityScore + “\t” + old_quality_score + “\t” + change + “\t” + campaign_name);
}
}

// write results to spreadsheet
result_range.splice(0,1);
campaign_sheet.getRange(2, 2, result_range.length, 2).setValues(result_range);
// write history to spreadsheet
var history_sheet = spreadsheet.getSheetByName(‘Campaigns QS history’);
history_sheet.getRange(2,history_sheet.getLastColumn()+1, campaign_history.length, 1).setValues(campaign_history);
history_sheet.getRange(1,history_sheet.getLastColumn(), 1, 1).setValue(today);

//Campaign QS Ends

//Adgroup QS Starts

for(i = 1; i < adgroup_sheet_values.length; i++){
// make sure there is actually some data here
if(adgroup_sheet_values[i][0] == “”) continue;
adgroup_result_range[i] = [today, 0];
var campaign_name = adgroup_sheet_values[i][0];
var adgroup_name = adgroup_sheet_values[i][1];
var latest_check = adgroup_sheet_values[i][2];
var old_quality_score = adgroup_sheet_values[i][3];
var totalImpressionsAnalyzed = 0;
var totalQualityScoreAnalyzed = 0;
var keywordIterator = AdWordsApp.keywords()
.withCondition(“CampaignName = ‘” + campaign_name + “‘”)
.withCondition(“AdGroupName = ‘” + adgroup_name + “‘”)
.withCondition(“CampaignStatus = ENABLED”)
.withCondition(“AdGroupStatus = ENABLED”)
.orderBy(“Impressions”)
.forDateRange(“LAST_30_DAYS”)
.withLimit(50000)
.get();

while(keywordIterator.hasNext()){
var keyword = keywordIterator.next();
var current_quality_score = keyword.getQualityScore();
var keywordStats = keyword.getStatsFor(“LAST_30_DAYS”);
var impressions = keywordStats.getImpressions();
var qualityScoreContribution = current_quality_score * impressions;
totalQualityScoreAnalyzed = totalQualityScoreAnalyzed + qualityScoreContribution;
totalImpressionsAnalyzed = totalImpressionsAnalyzed + impressions;
}

var AdgroupQualityScore = totalQualityScoreAnalyzed / totalImpressionsAnalyzed;
var FinalAdgroupQualityScore=AdgroupQualityScore.toFixed(2);

// save quality score for results
adgroup_result_range[i][1] = FinalAdgroupQualityScore;
// Note the change for the history
if(old_quality_score > 0) var change = (FinalAdgroupQualityScore – old_quality_score).toFixed(1);
else var change = “NEW”;
var column = [FinalAdgroupQualityScore];
adgroup_history.push(column);
// if we have a previously tracked quality score and it’s different from the current one, we make a note to log it and send it via email later
if(old_quality_score > 0 && CampaignQualityScore != old_quality_score){
adgroup_alert_text.push(FinalAdgroupQualityScore + “\t” + old_quality_score + “\t” + change + “\t” + campaign_name + “\t” + adgroup_name);
}
}

// write results to spreadsheet
adgroup_result_range.splice(0,1);
adgroup_sheet.getRange(2, 3, adgroup_result_range.length, 2).setValues(adgroup_result_range);
// write history to spreadsheet
var history_sheet = spreadsheet.getSheetByName(‘Adgroups QS history’);
history_sheet.getRange(2,history_sheet.getLastColumn()+1, adgroup_history.length, 1).setValues(adgroup_history);
history_sheet.getRange(1,history_sheet.getLastColumn(), 1, 1).setValue(today);
//Adgroup QS Ends

// Send Quality Score Changes through Email

var message = “The following Account Quality Score changes were discovered:\nNew\tOld\tChange\tAccount\n”;
for(i = 0; i < account_alert_text.length; i++) message += account_alert_text[i] + “\n”;
message += “\n” + “The following Campaign Quality Score changes were discovered:\nNew\tOld\tChange\tCampaign\n”;
for(i = 0; i < campaign_alert_text.length; i++) message += campaign_alert_text[i] + “\n”;
message += “\n” + “The following Adgroup Quality Score changes were discovered:\nNew\tOld\tChange\tCampaign Name\Adgroup Name\n”;
for(i = 0; i < adgroup_alert_text.length; i++) message += adgroup_alert_text[i] + “\n”;
// Include a link to the spreadsheet
message += “\n” + “Settings and complete history are available at ” + spreadsheet_url;
MailApp.sendEmail(email_address, “AdWords quality score changes detected”, message);
}

[/code]

4. Change email address and spreadsheet URL

In the first few lines of the script you will see a spreadsheet URL and a dummy email address. Replace the spreadsheet URL with the URL of the spreadsheet that you created (after copying ours). Then also put in your email address (you can use multiple addresses if you want to send reports to multiple people – just separate with a comma).

The script is divided in 4 parts – Account QS calculation, Campaign QS calculation, Adgroup QS calculation and Email sending. You can see in the script where each part starts and finishes.

The logic of calculating account, campaign and ad group QS is based on http://searchengineland.com/how-account-quality-score-can-guide-adwords-optimization-148595. As you know the original script for tracking keyword QS was written on PPC Epiphany. http://www.ppc-epiphany.com/2012/08/14/an-adwords-script-to-track-quality-scores/

5. Authorise the script

Before the script will run you will need to hit “Authorize now” button in the Authorize bar.

6. Run the script

Click run and the script will run. If it times out then check how many campaigns and ad groups you pasted into the spreadsheet to track and consider reducing a little.

Check your spreadsheet and you should see the data there and it should then be possible to set a schedule for the script to run every day or week so that you can monitor changes based on your account management as well as to prioritise your time effectively.

Having a high quality score can save you loads of cash – read this post to find out more on quality score.

We hope that you get a lot of benefit from this script. Much appreciation has to go to the guys mentioned above who really helped out us and the rest of the Adwords community by pioneering some early scripts to track Adwords quality score.



Author: Joel
Founder of Deep Footprints Online Marketing Ltd.

コメントを残す