atom tips
  • PPC

14th March 2014
By Ankur

‘Big Data’ has been one of the buzzwords of 2014 so far. According to the researchers at St Andrews University, it’s the “storage and analysis of large and complex sets of data using a series of techniques including but not limited to machine learning”. Google has been one of the early adopters and now provides more information than ever to its advertisers via the Adwords interface. Fortunately it also provides access to automated programs, such as Adwords scripts to harness the real power of the available data.

What are Adwords scripts?

Adwords scripts use Javascript to automate routine activities, allowing the advertiser to spend more of their valuable time optimising the PPC account as opposed to gathering and compiling reports. While Javascript and programming can sound daunting, this post explains how you can take advantage of one of the most helpful Adwords scripts – the Quality Score script, with minimal technical expertise.

Quality Score: Boon or Bane?

Google’s Quality Score metric has traditionally been fascinating and frustrating in equal measures. In theory a higher Quality Score can result in a lower cost per click and a higher ad position for the advertiser, however the lack of historical information makes it hard if not impossible to analyse QS over time.

The solution – Quality Score script combined with labels

This Adwords script tracks keyword level QS over time, and you can schedule it to run as often as you may deem appropriate; hourly/daily/weekly/monthly etc. It also creates and logs changes in a Google spreadsheet, along with the option to send email notifications to the advertiser.

Set up is as simple as 1, 2, 3

1. Get the Script

In your Adwords account, go to the ‘Bulk operations’ section, click ‘Scripts’ and add a new script. Next copy and paste the piece of code (from the bottom of the page), choose a descriptive name and save it.

script-1_496x175.jpg

2. Run the script to create the spreadsheet and the label

Once authorised the script will automatically create a Google spreadsheet where historic QS changes will be logged. You can find the spreadsheet URL under the logs section after the script has been run once.

script-2_500x99.jpg

A new label called ‘Track QS’ will be created along with 10 other labels (QS: 1 to QS: 10), these 10 labels can be ignored.

script-3_498x365.jpg

3.    Select the keywords you wish the script to track

You can apply the ‘Track QS’ label to the keywords you would like to track, and the script will track, log and email you the changes. Simply changing the label ‘Track QS’ will also allow you to change the set of tracked keywords.

script-5_500x305.jpg

You now have detailed keyword level QS data at your fingertips. All that awaits is for you to analyse whether your ad copy or bid changes etc are resulting in QS improvements or not.

The Quality score script – Javascript Code

var spreadsheet_url = "INSERT_SPREADSHEET_URL_HERE";
var email_address = "YOUR_EMAIL_HERE";
 
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);
  }
}

Replies