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:
Adwords Script to Track Quality Score on Account, Campaign & Ad Group Level
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
Adwords Script to Track Quality Score on Account, Campaign & Ad Group Level
Here is the spreadsheet template – click on this link and then click on “Make a copy” to copy it.
Make sure that the Account tab contains Account name in 1st column, Campaign tab must contain Campaign names in 1st column and Adgroup tab 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:
Adwords Script to Track Quality Score on Account, Campaign & Ad Group Level
3. Paste in the script
Paste in the following script ensuring that you replace any other script in the box:
Everything after this >>
var spreadsheet_url = “https://docs.google.com/spreadsheet/ccc?key=0AuPPDa_qWJYOdDNHUlYwajhRYlc3d0dfUXIwb1BLQXc#gid=3″; 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; else var change = “NEW”; var row = [today, FinalAccountQualityScore, change]; account_history.push(row); 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(history_sheet.getLastRow()+1, 1, account_history.length, 3).setValues(account_history);
//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;
else var change = “NEW”;
var row = [today, campaign_name, FinalCampaignQualityScore, change];
campaign_history.push(row);
// 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 campaign_history_sheet = spreadsheet.getSheetByName(‘Campaigns QS history’); campaign_history_sheet.getRange(campaign_history_sheet.getLastRow()+1, 1, campaign_history.length, 4).setValues(campaign_history);
//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;
else var change = “NEW”;
var row = [today, campaign_name, adgroup_name, FinalAdgroupQualityScore, change];
adgroup_history.push(row);
// 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(history_sheet.getLastRow()+1, 1, adgroup_history.length, 5).setValues(adgroup_history); //Adgroup QS Ends
// Send Quality Score Changes through Email
var message = “The following Account Quality Score changes were discovered:nNewtOldtChangetAccountn”; 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:nNewtOldtChangetCampaignn”; 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:nNewtOldtChangetCampaign NameAdgroup Namen”; 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); }
More Business articles from Business 2 Community:
