Quality Score Tracker

[Muditha Dahanayaka]

Hi Anash

Some time back I've implemented the quality score tracker script in one of my accounts.

1. I got this error massage after running the script

The coordinates or dimensions of the range are invalid. (line 57)

line 57  - sheet.getRange(2, 4, result_range.length, 2).setValues(result_range);

How should I adjust the script to avoid the run time error?

(Please note that I have copied the excel template from another account)

2. I have noticed that Google have discontinued the Quality score tracker script. it's no longer available in https://developers.google.com/adwords/scripts/docs/solutions/

What's the reason? 

Cheers

Mudi

[Anash Oommen]

Hi Muditha,

My guess is that you'd have changed the spreadsheet so it's original layout got affected. You have to modify your script so that it refer to the new layout instead of the old one. The quality score tracker hasn't been discontinued, it was always called "Keyword Performance" report.

Cheers,

Anash P. Oommen

AdWords Scripts Team.

[Muditha Dahanayaka]

Thank you very much for the prompt reply.

The keyword performance report is not the one that I'm referring to.

Some time back there was an Adwords script in the Google Dev page which tracks the changes in quality score for every keyword.

I have implemented this into one of my accounts. It runs weekly and tells me specifically which keyword lost/improved their QS and by how much.

When I checked yesterday, I was unable to find this particular Quality score tracker script in the Google developers script page as I wanted to implement it to a new account.

Therefore I took a copy of the existing excel file template which I've implemented few months ago and tested it with the QS tracker script.

Then it gave me the above run time error. I have not done any changes to the template.

Could you please help me rectify this issue? I have provided a screen shot of the template and the QS tracker script below.

The template. 

The Script

var spreadsheet_url = " ";

var email_address = " ";

 

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 sheet = spreadsheet.getSheetByName('Input Keywords');

  var sheet_values = sheet.getDataRange().getValues();

  var result_range = new Array(); // holds the results to write back

  var alert_text = new Array();

  var history = new Array();

  var currentTime = new Date();

  var today = (currentTime.getMonth() + 1) + "/" + currentTime.getDate() + "/" + currentTime.getFullYear();

  

  for(i = 1; i < sheet_values.length; i++){

    // make sure there is actually some data here

    if(sheet_values[i][0] == "") continue;

    result_range[i] = [today, 0];

    var campaign_name = sheet_values[i][0];

    var adgroup_name = sheet_values[i][1];

    // remove single quotes at the beginning of the keyword (Excel sometimes adds them in front of modified broad matches, like: '+keyword)

    var keyword_text = sheet_values[i][2].replace(/^[']+/g, "");    

    var latest_check = sheet_values[i][3];

    var old_quality_score = sheet_values[i][4];

    

    var keywordIterator = AdWordsApp.keywords()

      .withCondition("CampaignName = '" + campaign_name + "'")

      .withCondition("AdGroupName = '" + adgroup_name + "'")

        // this won't let us filter for phrase or exact matches so we have to remove brackets and quotation marks (broad match modifiers are fine)

      .withCondition("Text = \"" + keyword_text.replace(/[\[\]\"]/g, "") + "\"")

      .get();

    while(keywordIterator.hasNext()){

      var keyword = keywordIterator.next();

      // since we couldn't filter phrase or exact matches directly, we have to make sure that this is the right keyword

      if(keyword.getText() == keyword_text){

        var current_quality_score = keyword.getQualityScore();

        // save quality score for results

        result_range[i][1] = current_quality_score;

        // for the history we also note the change or whether this keyword is new

        if(old_quality_score > 0) var change = current_quality_score - old_quality_score;

        else var change = "NEW";

        var row = [today, campaign_name, adgroup_name, keyword_text, current_quality_score, change];

        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 && current_quality_score != old_quality_score){

          alert_text.push(current_quality_score + "\t" + old_quality_score + "\t" + change + "\t" + latest_check + "\t" + keyword_text);

        }

        // we've found the keyword we were looking for so we look no further

        break;

      }

    }

  }

  // write results to spreadsheet

  result_range.splice(0,1);

  sheet.getRange(2, 4, result_range.length, 2).setValues(result_range);

  // write history to spreadsheet

  var history_sheet = spreadsheet.getSheetByName('QS history');

  history_sheet.getRange(history_sheet.getLastRow()+1, 1, history.length, 6).setValues(history);

  // if we've made notes for alerts then we send them via email

  if(alert_text.length){

    var message = "The following quality score changes were discovered:\nNew\tOld\tChange\tPreviously checked\tKeyword\n";

    for(i = 0; i < alert_text.length; i++) message += alert_text[i] + "\n";

    // also include a link to the spreadsheet

    message += "\n" + "Settings and complete history are available at " + spreadsheet_url;    

    // if we have an email address we send out a notification

    if(email_address && email_address != "YOUR_EMAIL_HERE"){

      MailApp.sendEmail(email_address, "AdWords quality score changes detected", message);

    }

    // log the message

    Logger.log(message);

  }

}

Cheers

Mudi

[Muditha Dahanayaka]

Hi Anash

Would be great if you can get back to me on this.

Thanks

Cheers

Mudi

[Anash Oommen]

Hi Muditha,

Do you have a spreadsheet url I can refer to for the template? I'm not sure if this was a solution on developers.google.com, but nevertheless I'll take a look.

Cheers,
Anash P. Oommen,

AdWords Scripts Team.