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

0 comments


“For all These 3 Questions - the Answer & Script is the 1”

This was my introduction (+Digital Marketing Expert)  to Google scripts as to be frank I am not a coder and apart from basic HTML , Am a Digital Marketing Expert &  here to Share the Step By Step Implementation of Adwords Script to track Quality Score on Account, campaign & Ad Group Level..

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

Step 1: Prepare Google Spread Sheet Document ---->> Open this

  &  Make a Copy of it






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 have 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 a click on that.

You should see a screen like this:
Control + A + Delete



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

Copy the Codes Highlighted in Green

Start

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);
}

End
Copy the Code and Paste in Script

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 a just separate with a comma).

The script is divided in 4 parts an Account QS calculation, Campaign QS calculation, Ad group QS calculation and Email sending. You can see in the script where each part starts and finishes.
Click “Authorize” Button And Then “Run Script Now” Button…………


5. 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 priorities 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.

Your Report will be shown In Spreadsheet like this..



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.


 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/



Read More »

Tweet Here

News Feed

Loading...