Introducing the Quality Score Tracker v3.0

It’s bigger, it’s better, and it’s official: The new Quality Score Tracker is finally available!

The Quality Score Tracker is a free AdWords script that keeps track of an account’s Quality Scores. The previous version is still incredibly popular, but it’s three years old by now. This is why I created a new one that’s much faster and has some fancy new features.

en-dashboard1

The new version is different from the old ones in several aspects. A major objective was to write a script that’s as easy to use as possible – even easier than the old ones. The new Quality Score Tracker can be used right away, without any further setup.

While being easy to use the new script is very complex and handles a lot of things without bothering the user.

Here’s what it does:

Quality Score Tracking

The Quality Score Tracker saves your keywords’ Quality Scores in a spreadsheet. Unlike with the previous versions you don’t have to choose which ones – it tracks all of them. This allows you to go back and look up the history for any of your keywords later.

Dashboard

While tracking thousands of individual Quality Scores may be helpful later, the script also provides a dashboard to show you where you currently stand.

en-dashboard2

The dashboard is provided in a Google spreadsheet as well. This spreadsheet also provides summarized data about your Quality Scores.

Google Drive

All files (dashboard and tracked keyword Quality Scores) are stored in a folder in your Google Drive. You can access them through your browser, or use all of the Google Drive features, like synchronizing the folder with your device, or sharing folders with others.

There’s a limit on how many cells a Google spreadsheet can hold (two million). Should you reach this limit while tracking Quality Scores, the script just creates additional spreadsheets. Spreadsheet files are named and numbered so you can put everything together later, if needed.

en-drive

Since everything is in Google Spreadsheet files, the occupied space doesn’t count towards your Google Drive storage limit.

Customizable

The script provides a number of settings you can change (but you don’t have to). For example, you can add other charts to the dashboard, like a click weighted Quality Score.

Integrated FAQ

The dashboard comes with an FAQ that is updated every time the script runs.

More to come!

The script is already pretty complex, but I have a couple of ideas for new features that I want to add in the future (an MCC version is probably next). When a new version is available, a subtle reminder is added to the dashboard.

tabs

The latest version of the script will always be available at this URL: http://www.ppc-epiphany.com/qstracker/latest

Quick Setup

Since the script works out of the box, setting it up is easy.

  1. Get the code (see below) and paste it into your account.
  2. Schedule the script to run daily. Recommendation: 1:00 am.
  3. Run the script for the first time. Check the logs to find the URL to your dashboard, or find everything in your Google Drive.

Detailed Setup

In AdWords, navigate to Bulk operations and then Scripts. Click +Script. Paste the script from the end of this post. Name it “Quality Score Tracker v3.0”. Click Save and then Authorize now.

Go back to Scripts and find the Quality Score Tracker at the top of the list. Click +Create Schedule. As frequency select “Daily”, along with a time of your convenience. Recommendation: 1:00 am. Click Save.

Click Run, then scroll down to see the script running under Logs. With big accounts, this can take a while, otherwise it could be done in 15 seconds. Click Logs on the right hand side. In the next screen, click on the Logs button on top. Now the URL’s for the dashboard and the Google Drive folder are displayed. As an alternative, you can find everything in your Google Drive.

Have fun!

Allright, that’s it. Get the script from below and get started!

The Script
/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* Quality Score Tracker v3.0.1
* Written by Martin Roettgerding.
* © 2016 Martin Roettgerding, Bloofusion Germany GmbH.
* www.ppc-epiphany.com/qstracker/latest
*/
function main(){
  /*
  * The following preferences can be changed to customize this script.
  * Most of options can be set by using 1 for yes or 0 for no.
  * You don't have to change anything here. The script will do fine with the defaults.
  */
  var config = {
    /*
    * Which of the following charts should be displayed on the dashboard?
    * The "per QS" charts are column charts. They show the current state compared to a previous one (see next option).
    * "Average" and "weighted" charts are line charts, showing changes over time.
    */
    "chartsToDisplay" : {
      "Keywords per QS" : 0,
      "Average QS" : 0,
      "Keywords with Impressions per QS" : 1,
      "Average QS for Keywords with Impressions" : 0,
      "Impressions per QS" : 0,
      "Impression weighted QS" : 1,
      "Clicks per QS" : 0,
      "Click weighted QS" : 0,
      "Conversions per QS" : 0,
      "Conversion weighted QS" : 0,
      "Conversion value per QS" : 0,
      "Conversion value weighted QS" : 0,
    },
    /*
    * Column charts can show a former date for comparison. Set the number of steps you want to go back for this.
    * Note that the date you're comparing this to will depend on how often you've run the script in the past.
    * Example: If the setting is 30 and you ran the script daily, your comparison will be with the values from 30 days before. If you ran it hourly, it will be with values from 30 hours before.
    * If you haven't run the script often enough, the comparison will go as far back as possible.
    * Put 0 to disable the comparison.
    */
    "chartsCompareStepsBack" : 30,
    /*
    * When stats are taken into account (like impressions per QS, or impression weighted QS), this timeframe is used.
    * Note that this affects the values to be tracked and stored. Past values that are already stored won't be affected.
    * Use one of the following: TODAY, YESTERDAY, LAST_7_DAYS, THIS_WEEK_SUN_TODAY, THIS_WEEK_MON_TODAY, LAST_WEEK, LAST_14_DAYS, LAST_30_DAYS, LAST_BUSINESS_WEEK, LAST_WEEK_SUN_SAT, THIS_MONTH
    */
    "statsTimeframe" : "LAST_30_DAYS",
    /* 
    * Whether to only look at stats from Google (e.g. for impression weighted QS).
    * Recommended. Quality Score itself only reflects data from Google, so weighting should only take Google into account and leave out search partners.
    * Note that this affects the values to be tracked and stored. Past values that are already stored won't be affected.
    */
    "googleOnly" : 1,
    /* 
    * Whether to only track active keywords. This means that the keyword, the adgroup, and the campaign have to be enabled.
    * Recommended. Otherwise inactive keywords with meaningless Quality Scores might skew your data.
    */
    "activeKeywordsOnly" : 1,
    /*
    * Set to 1 if you want your dates (in charts, table headers, and file names) to contain hours and minutes as well.
    * Do this if you want to run the script hourly.
    */
    "useHours" : 0,
    /*
    * Use this option to not keep track of individual keywords' Quality Scores and only save data to the dashboard file.
    * This makes sense if you have more than 400,000 keywords. Note that you don't have to change this: The script will notice on its own and log a message otherwise.
    */
    "skipIndividualKeywords" : 0,
    /*
    * The name of the file where dashboard and summarized data are stored.
    */
    "summaryFileName" : "Dashboard + Summary",
    /*
    * The base folder for all Quality Score Tracker files.
    */
    "baseFolder" : "Quality Score Tracker/",
    /*
    * Whether to add a client folder in the base folder (resulting in a folder like "Quality Score Tracker/CLIENT_NAME (123-456-7890)/")
    * The folder's name is not important, as long as the Adwords client id remains in it.
    * This is useful if you want to track multiple accounts with this script.
    */
    "useClientFolder" : 1,
  }
  
  trackQS(config);
}

function trackQS(config){
  var version = "3.0";
  
  if(config['useHours']) var dateString = Utilities.formatDate(new Date(), AdWordsApp.currentAccount().getTimeZone(), "yyyy-MM-dd HH:mm");
  else var dateString = Utilities.formatDate(new Date(), AdWordsApp.currentAccount().getTimeZone(), "yyyy-MM-dd");
  
  var folder = getOrCreateFolder(config['baseFolder']);
  if(config['useClientFolder']) folder = getOrCreateClientFolder(folder);
  
  // Find the latest report file in the folder.
  var maxFileNumber = 0;
  var reportFile;
  var summaryFile;
  var fileIterator = folder.getFiles();
  while(fileIterator.hasNext()){
    var file = fileIterator.next();
    var matches = new RegExp(' #([0-9]+) ').exec(file.getName());
    if(matches && parseInt(matches[1]) > maxFileNumber){
      maxFileNumber = parseInt(matches[1]);
      reportFile = file;
    }else if(file.getName() == config['summaryFileName']) summaryFile = file;
  }
  
  // No report file found? Add a new one.
  if(maxFileNumber == 0){
    reportFile = addReportFile(folder, "QS Report #1 (" + dateString + ")");
    maxFileNumber = 1;
  }
  // No summary file found? Add a new one.
  if(!summaryFile) summaryFile = addSummaryFile(folder, config['summaryFileName']);
  
  Logger.log("All files are stored at");
  Logger.log(folder.getUrl());
  Logger.log("The dashboard is here:");
  Logger.log(summaryFile.getUrl());
  
  var spreadsheet = SpreadsheetApp.open(reportFile);
  var sheet = spreadsheet.getActiveSheet();
  var lastRowNumber = sheet.getLastRow();
  var sheetLastColumn = sheet.getLastColumn();
  var idColumnValues = sheet.getRange(1, 4, lastRowNumber, 1).getValues();  
  var summarySpreadsheet = SpreadsheetApp.open(summaryFile);
  updateInfo(summarySpreadsheet, version);
  var sheetCharts = summarySpreadsheet.getSheetByName("Dashboard");
  
  summarySpreadsheet.setActiveSheet(sheetCharts);
  summarySpreadsheet.moveActiveSheet(1);
  
  // Track an event in Google Analytics.
  trackInAnalytics(version);
  
  // Remember the line number for every keyword.
  var lineNumbers = {};  
  for(var i = 1; i < lastRowNumber; i++){
    lineNumbers[idColumnValues[i][0]] = i;
  }
  
  // qsValues represents the new column that will go right next to the others.
  var qsValues = new Array(lastRowNumber);
  qsValues[0] = [ dateString ];
  // Initialize everything with an empty string.
  var qsValuesLength = qsValues.length;
  for(var i = 1; i < qsValuesLength; i++) qsValues[i] = [""];
  
  // In case new keywords are found, they'll be added as new rows below the rest (campaign, adgroup, keyword, id string).
  var newRows = [];
  
  // All aggregated data goes in this variable.
  var qsStats = {
    "Keywords" : {},
    "Keywords with impressions" : {},
    "Impressions" : {},
    "Clicks" : {},
    "Conversions" : {},
    "Conversion value" : {}
  };
  // Initialize the arrays so that everything can be added up later. Index 0 is for totals, 1-10 for Quality Scores.
  for(var key in qsStats){
    for(var i = 0; i <= 10; i++){
      qsStats[key][i] = 0;
    }
  }
  
  // Get the data from AdWords.
  var awql = "SELECT Id, Criteria, KeywordMatchType, CampaignId, CampaignName, AdGroupId, AdGroupName, QualityScore, Impressions, Clicks, Conversions, ConversionValue FROM KEYWORDS_PERFORMANCE_REPORT WHERE Id NOT_IN [3000000, 3000006] AND Status = 'ENABLED' AND AdGroupStatus = 'ENABLED' AND CampaignStatus = 'ENABLED'";
  if(config['googleOnly']) awql += " AND AdNetworkType2 = 'SEARCH'";
  if(config['activeKeywordsOnly']) awql += " AND CampaignStatus = 'ENABLED' AND AdGroupStatus = 'ENABLED' AND Status = 'ENABLED'";
  awql += " DURING " + config['statsTimeframe'];
  var report = AdWordsApp.report(awql);
  var reportRows = report.rows();
  
  // Go through the report and count Quality Scores.
  while(reportRows.hasNext()){
    var row = reportRows.next();
    // Save the aggregated data.
    qsStats['Keywords'][row['QualityScore']]++;
    if(row['Impressions'] > 0) qsStats['Keywords with impressions'][row['QualityScore']]++; 
    qsStats['Impressions'][row['QualityScore']] += parseInt(row['Impressions']);
    qsStats['Clicks'][row['QualityScore']] += parseInt(row['Clicks']);
    qsStats['Conversions'][row['QualityScore']] += parseInt(row['Conversions']);
    qsStats['Conversion value'][row['QualityScore']] += parseInt(row['ConversionValue']);
    
    // Save the individual keyword's Quality Score.
    if(!config['skipIndividualKeywords']){
      var id = row['CampaignId']+"_"+row['AdGroupId']+"_"+row['Id'];
      // Check if there is already a line for this keyword
      if(lineNumbers[id]) var line_number = lineNumbers[id];
      else{
        // There is no line for this keyword yet. Create a new one and add the line headers.
        line_number = qsValues.length;        
        if(row['KeywordMatchType'] == "Exact") var keyword = '[' + row['Criteria'] + ']';
        else if(row['KeywordMatchType'] == "Phrase") var keyword = '"' + row['Criteria'] + '"';
        else var keyword = row['Criteria'];
        
        newRows.push([row['CampaignName'], row['AdGroupName'], keyword, id]);
      }
      
      qsValues[line_number] = [row['QualityScore']];
    }
  }
  
  // Check if everything fits.
  if(!config['skipIndividualKeywords']){
    // A spreadsheet can hold up to 2 million cells. Calculate if the new data will fit in with the rest.
    // With four rows needed for every keyword, plus one for every tracking run, this won't fit if there are more than 400,000 rows (header + 399,999 keywords).
    if(qsValues.length >= 400000){
      Logger.log("There are too many keywords to be tracked (" + qsValues.length + "). This tool can only track up to 399,999 keywords.");
      Logger.log("A summary will be logged, but individual keyword quality scores cannot be stored.");
      skipIndividualKeywords = true;
    }else if((qsValues.length + 1) * (sheet.getLastColumn() + 1) > 2000000){
      // This spreadsheet is full, a new one is needed.
      // Add new file.
      maxFileNumber++;
      reportFile = addReportFile(folder, "QS Report #" + maxFileNumber + " (" + dateString + ")");
      var newSpreadsheet = SpreadsheetApp.open(reportFile);
      var newSheet = newSpreadsheet.getActiveSheet();
      // Copy the first columns from the old sheet to the new one.
      newSheet.getRange(1, 1, lastRowNumber, 4).setValues(sheet.getRange(1, 1, lastRowNumber, 4).getValues());
      // From now on, work with the new sheet and spreadsheet.
      spreadsheet = newSpreadsheet;
      sheet = newSheet;
      sheetLastColumn = 4;
    }
  }
  
  // Store the keyword data in the spreadsheet.
  if(!config['skipIndividualKeywords']){
    // If there are new rows, add their line headers beneath the others.
    if(newRows.length > 0){
      sheet.insertRowsAfter(lastRowNumber, newRows.length).getRange(lastRowNumber + 1, 1, newRows.length, 4).setValues(newRows);
      sheet.autoResizeColumn(1).autoResizeColumn(2).autoResizeColumn(3);
    }
    // Add a new column with the tracked data.    
    sheet.insertColumnAfter(sheetLastColumn);
    sheet.getRange(1, sheetLastColumn + 1, qsValues.length, 1).setValues(qsValues);
    sheet.autoResizeColumn(sheetLastColumn + 1);
    
    // Change file name to reflect the new date.
    // Find out which dates are currently noted in the file's name.
    var matches = /\((.*?)( - (.*))?\)/.exec(reportFile.getName());
    if(matches && matches[1]){
      if(matches[2]){
        // There's a start date and an end date.
        var startDate = matches[1];
        var endDate = matches[3];
        if(endDate != dateString){
          var newFileName = reportFile.getName().replace(endDate, dateString);
          reportFile.setName(newFileName);
        }
      }else{
        // There's just a start date.
        var startDate = matches[1];
        if(startDate != dateString){
          var newFileName = reportFile.getName().replace(startDate, startDate + " - " + dateString);
          reportFile.setName(newFileName);
        }
      }
    }else{
      Logger.log("Could not recognize dates in file name " + reportFile.getName() +". File name remains unchanged.");
    }
  }
  
  // Now take care of the summary file.
  
  // Get the total numbers.
  for(var key in qsStats){
    for(var i = 1; i <= 10; i++){
      qsStats[key][0] += qsStats[key][i];
    }
  }
  
  // Prepare a new column for the Percentages data sheet.
  var newValues = [];
  var newValuesNumberFormats = [];
  
  for(var key in qsStats){
    newValues.push([dateString]);
    newValuesNumberFormats.push(["@STRING@"]);
    for(var i = 1; i <= 10; i++){
      if(qsStats[key][0]) newValues.push([qsStats[key][i] / qsStats[key][0]]); else newValues.push([0]);
      newValuesNumberFormats.push(["0.00%"]);
    }
    newValues.push([qsStats[key][0]]);
    newValuesNumberFormats.push(["0.##"]);
  }
  
  var sheetPercentages = summarySpreadsheet.getSheetByName("Percentages");
  var sheetAverages = summarySpreadsheet.getSheetByName("Averages");
  
  var lastCol = sheetPercentages.getLastColumn() + 1;
  var lastRow = sheetAverages.getLastRow() + 1;
  
  // Add the data to the Percentages sheet.
  sheetPercentages.insertColumnAfter(lastCol - 1);
  sheetPercentages.getRange(1, lastCol, 72, 1).setNumberFormats(newValuesNumberFormats).setValues(newValues);
  sheetPercentages.autoResizeColumn(lastCol);
  
  // Add a new row with formulas to the Averages sheet.
  sheetAverages.appendRow([""]);
  sheetAverages.getRange(lastRow, 1, 1, 1).setValue(dateString);
  sheetAverages.getRange(lastRow, 2, 1, 6).setFormulasR1C1([[
    "=SUMPRODUCT(Percentages!R2C1:R11C1; Percentages!R2C" + lastCol + ":R11C" + lastCol + ")",
    "=SUMPRODUCT(Percentages!R14C1:R23C1; Percentages!R14C" + lastCol + ":R23C" + lastCol + ")",
    "=SUMPRODUCT(Percentages!R26C1:R35C1; Percentages!R26C" + lastCol + ":R35C" + lastCol + ")",
    "=SUMPRODUCT(Percentages!R38C1:R47C1; Percentages!R38C" + lastCol + ":R47C" + lastCol + ")",
    "=SUMPRODUCT(Percentages!R50C1:R59C1; Percentages!R50C" + lastCol + ":R59C" + lastCol + ")",
    "=SUMPRODUCT(Percentages!R62C1:R71C1; Percentages!R62C" + lastCol + ":R71C" + lastCol + ")"
  ]]);
  
  
  // The properties for the charts. This is not meant to be reconfigured.
  var chartsProperties = {
    "Keywords per QS" : {
      "type" : "column",
      "vCol" : 2,
    },
    "Average QS" : {
      "type" : "line",
      "vCol" : 2,
    },
    "Keywords with Impressions per QS" : { 
      "type" : "column",
      "vCol" : 3,
    },
    "Average QS for Keywords with Impressions" : {
      "type" : "line",
      "vCol" : 3,
    },
    "Impressions per QS" : {
      "type" : "column",
      "vCol" : 4,
    },
    "Impression weighted QS" : {
      "type" : "line",
      "vCol" : 4,
    },
    "Clicks per QS" : {
      "type" : "column",
      "vCol" : 5, 
    },
    "Click weighted QS" : {
      "type" : "line", 
      "vCol" : 5, 
    },
    "Conversions per QS" : {
      "type" : "column", 
      "vCol" : 6, 
    },
    "Conversion weighted QS" : {
      "type" : "line", 
      "vCol" : 6,
    },
    "Conversion value per QS" : {
      "type" : "column", 
      "vCol" : 7, 
    },
    "Conversion value weighted QS" : {
      "type" : "line", 
      "vCol" : 7,
    },
  };
  
  var row = 1;
  var col = 1;  
  var summarySheets = {
    "dataH": sheetPercentages,
    "dataV": sheetAverages,
    "charts": sheetCharts,
  }
  
  // Add charts to the dashboard.
  for(var chartName in config['chartsToDisplay']){
    // Skip all charts that are not set to be displayed.
    if(!config['chartsToDisplay'][chartName]) continue;
    
    addChartToDashboard(chartName, chartsProperties[chartName]['type'], summarySheets, row, col, lastRow, lastCol, chartsProperties[chartName]['vCol'], config['chartsCompareStepsBack']);
    
    // Add the "Average QS" cells.
    sheetCharts.setRowHeight(row, 60).setRowHeight(row + 1, 20).setRowHeight(row + 2, 270);
    sheetCharts.getRange(row, 2).setValue("Average QS").setFontWeight("bold").setFontSize(24).setBorder(true, true, false, true, null, null);
    sheetCharts.getRange(row + 2, 2).setFontWeight("bold").setFontSize(24).setNumberFormat("0.00").setBorder(false, true, false, true, null, null);
    sheetCharts.getRange(row + 1, 2, 2, 1).setFormulasR1C1(
      [
        ["=LOWER(Averages!R1C" + chartsProperties[chartName]['vCol'] + ")"], ["=Averages!R" + lastRow + "C" + chartsProperties[chartName]['vCol']]
      ]).setBorder(false, true, true, true, null, null);
    sheetCharts.autoResizeColumn(2);
    row += 3;
  }
}

/*
* Checks if there is a folder with the given name in the Google Drive root folder. If not, the folder is created.
* The folderName can be in the form of a complete path with subfolders, like "QS Reports/123/whatever".
* Returns the folder.
*/
function getOrCreateFolder(folderName){
  return getOrCreateFolderFromArray(folderName.toString().split("/"), DriveApp.getRootFolder());
}

/*
* Does the actual work for getOrCreateFolder. Recursive function, based on an array of folder names (to handle paths with subfolders).
*/
function getOrCreateFolderFromArray(folderNameArray, currentFolder){
  var folderName = "";
  // Skip empty folders (multiple slashes or a slash at the end).
  do folderName = folderNameArray.shift(); while(folderName == "" && folderNameArray.length > 0);
  
  if(folderName == "") return currentFolder;
  
  // See if the folder is already there.
  var folderIterator = currentFolder.getFoldersByName(folderName);
  if(folderIterator.hasNext()){
    var folder = folderIterator.next();
  }else{
    // Create folder.
    Logger.log("Creating folder '" + folderName + "'");
    var folder = currentFolder.createFolder(folderName);
  }
  
  if(folderNameArray.length > 0) return getOrCreateFolderFromArray(folderNameArray, folder);
  return folder;
}

/*
* Checks if there is a folder for the current client account in the base folder. If not, the folder is created.
* Existing client folders are recognized by the client id in parentheses. This way, folders can be found again, even if an account has been renamed.
*/
function getOrCreateClientFolder(baseFolder){
  var folderIterator = baseFolder.getFolders();
  var regExp = new RegExp(AdWordsApp.currentAccount().getCustomerId());
  while(folderIterator.hasNext()){
    var folder = folderIterator.next();
    if(folder.getName().match(regExp)) return folder;
  }
  // Since no folder has been found: Create one.
  var newFolderName = AdWordsApp.currentAccount().getName() + " (" + AdWordsApp.currentAccount().getCustomerId() + ")";
  Logger.log("Creating folder '" + newFolderName + "'");
  return baseFolder.createFolder(newFolderName);
}

/*
* Creates a spreadsheet for QS tracking.
* Adds headers to the spreadsheet.
* Returns the file.
*/
function addReportFile(folder, name){
  var spreadsheet = SpreadsheetApp.create(name, 1, 4);
  var sheet = spreadsheet.getActiveSheet();
  sheet.setName("QS history");
  // Put in the table headings
  sheet.getRange(1, 1, 1, 4).setValues([["Campaign", "AdGroup", "Keyword", "ID string"]]);
  //sheet.getRange(1, 1, 1, 4).setFontWeight("bold");
  sheet.setColumnWidth(4, 1);
  var file = DriveApp.getFileById(spreadsheet.getId());
  folder.addFile(file);
  var parentFolder = file.getParents().next();
  parentFolder.removeFile(file);
  return folder.getFilesByName(name).next();
}

/*
* Creates a spreadsheet for the summary and stores it in the folder.
* Creates sheets for the Percentages and Averages.
* Populates header rows and columns.
*/
function addSummaryFile(folder, name){
  var spreadsheet = SpreadsheetApp.create(name);
  var sheetH = spreadsheet.getActiveSheet();
  sheetH.setName("Percentages");
  
  // Add the first column for the horizontal data table.
  sheetH.getRange(1, 1, 72, 1).setValues(
    [["All keywords"], [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], ['Total'],
     ["Keywords with impressions"], [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], ['Total'],
     ["Impression weighted"], [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], ['Total'],
     ["Click weighted"], [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], ['Total'],
     ["Conversion weighted"], [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], ['Total'],
     ["Conversion value weighted"], [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], ['Total']
    ]
  );
  sheetH.getRange("A:A").setNumberFormat('@STRING@');
  sheetH.autoResizeColumn(1);
  
  var sheetV = spreadsheet.insertSheet("Averages");
  // Add the first rows for the vertical data table.
  sheetV.getRange(1, 1, 4, 7).setValues([
    ["Date", "Average", "Average for keywords with impressions", "Impression weighted", "Click weighted", "Conversion weighted", "Value weighted"],
    ["Highest", "", "", "", "", "", ""],
    ["Lowest", "", "", "", "", "", ""],
    ["Average", "", "", "", "", "", ""]
  ]);
  // Add some formulas for maximums, minimums, and averages.
  sheetV.getRange(2, 2, 3, 6).setFormulas([
    ["=MAX(B$5:B)", "=MAX(C$5:C)", "=MAX(D$5:D)", "=MAX(E$5:E)", "=MAX(F$5:F)", "=MAX(G$5:G)"],
    ["=MIN(B$5:B)", "=MIN(C$5:C)", "=MIN(D$5:D)", "=MIN(E$5:E)", "=MIN(F$5:F)", "=MIN(G$5:G)"],
    ["=AVERAGE(B$5:B)", "=AVERAGE(C$5:C)", "=AVERAGE(D$5:D)", "=AVERAGE(E$5:E)", "=AVERAGE(F$5:F)", "=AVERAGE(G$5:G)"]
  ]);
  sheetV.getRange(1, 1, 1, 7).setFontWeight("bold").setNumberFormat('@STRING@');
  sheetV.autoResizeColumn(1);
  sheetV.autoResizeColumn(2);
  sheetV.autoResizeColumn(3);
  sheetV.autoResizeColumn(4);
  sheetV.autoResizeColumn(5);
  sheetV.autoResizeColumn(6);
  sheetV.autoResizeColumn(7);
  
  // Store the spreadsheet.
  var file = DriveApp.getFileById(spreadsheet.getId());
  folder.addFile(file);
  var parentFolder = file.getParents().next();
  parentFolder.removeFile(file);
  return folder.getFilesByName(name).next();
}

/*
* Replaces the About sheet in the summary spreadsheet with a fresh one from the master sheet. This way, the sheet (including the FAQ) stays up to date.
* Also replaces the Dashboard with a fresh copy (resulting in an empty sheet with the correct conditional formatting).
* If there's a new version, a sheet "New Version Available!" is added.
*/
function updateInfo(summarySpreadsheet, version){
  var templateSpreadsheet = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1qnTYdpBCgHP_5u5eQcXmc5gP0NrOrBK51JnTCTlc0_g/");
  
  var oldSheet = summarySpreadsheet.getSheetByName("Dashboard");
  if(oldSheet) summarySpreadsheet.deleteSheet(oldSheet);
  templateSpreadsheet.getSheetByName("Dashboard v" + version).copyTo(summarySpreadsheet).setName("Dashboard");
  
  var oldSheet = summarySpreadsheet.getSheetByName("About + FAQ");
  if(oldSheet) summarySpreadsheet.deleteSheet(oldSheet);
  templateSpreadsheet.getSheetByName("About v" + version).copyTo(summarySpreadsheet).setName("About + FAQ");
  
  var oldSheet = summarySpreadsheet.getSheetByName("New Version Available!");
  if(oldSheet) summarySpreadsheet.deleteSheet(oldSheet);
  
  // Check if there is a newer version.
  var versionHistory = templateSpreadsheet.getSheetByName("Version History").getDataRange().getValues();
  if(versionHistory[0][0] != version){
    // There's a new version available (at least one).
    // Look for the row which has the info about the current (old) version.
    var oldVersionRow = 1;
    while(oldVersionRow < versionHistory.length && versionHistory[oldVersionRow][0] != version){
      oldVersionRow++;
    }
    
    // Copy the entire version history.
    var newVersionSheet = templateSpreadsheet.getSheetByName("Version History").copyTo(summarySpreadsheet).setName("New Version Available!");
    // Remove everything about the old version.
    newVersionSheet.deleteRows(oldVersionRow + 1, versionHistory.length - oldVersionRow);
    // Add new Rows at the beginning.
    newVersionSheet.insertRows(1, 6);
    newVersionSheet.getRange(1, 1, 6, 2).setValues([["Latest version:", versionHistory[0][0]], ["Your version:", version], ["", ""], ["Get the latest version at", "http://www.ppc-epiphany.com/qstracker/latest"], ["", ""], ["Newer Versions", ""]]);
    newVersionSheet.getRange(1, 1, 1, 2).setFontWeight("bold");
    newVersionSheet.getRange(6, 1, 1, 1).setFontWeight("bold");
    newVersionSheet.autoResizeColumn(1);
    newVersionSheet.autoResizeColumn(2);
  }
}

/*
* Inserts a line or column chart into the dashboard sheet.
* The chart is based on data from the Percentages or Averages sheet.
*/
function addChartToDashboard(name, type, sheets, row, col, lastRow, lastCol, vCol, compareStepsBack){
  var chartBuilder = sheets['charts'].newChart();
  chartBuilder
  .setOption('title', name)
  .setOption('width', 800)
  .setOption('height', 349)
  .setOption('colors', ['#fa9d1c','#00507d'])
  .setPosition(row, col, 0, 0);
  
  switch(type){
    case "column":        
      var statsRow = (vCol - 2) * 12 + 1;
      // First range for a column chart is always the same column with QS from 1 to 10.
      var dataRanges = [sheets['dataH'].getRange(1, 1, 11, 1)];
      if(compareStepsBack && lastCol > 2){
        // The column for comparison is either the specified number of columns behind lastCol, or 2 (the first column with data).
        dataRanges.push(sheets['dataH'].getRange(statsRow, Math.max(2, lastCol - compareStepsBack), 11, 1));
      }
      dataRanges.push(sheets['dataH'].getRange(statsRow, lastCol, 11, 1));
      chartBuilder = chartBuilder.asColumnChart();
      break;
    case "line":
      var dataRanges = [sheets['dataV'].getRange(5, 1, lastRow - 2, 1), sheets['dataV'].getRange(5, vCol, lastRow - 2, 1)];
      chartBuilder = chartBuilder.asLineChart();
      chartBuilder.setOption("vAxis.maxValue", 10);
      chartBuilder.setOption("vAxis.ticks", [0,2,4,6,8,10]);
      chartBuilder.setLegendPosition(Charts.Position.NONE);
      break;
  }
  
  for(var i in dataRanges) chartBuilder.addRange(dataRanges[i]);
  sheets['charts'].insertChart(chartBuilder.build());
}

/* 
* Tracks the execution of the script as an event in Google Analytics.
* Sends the version number and a random UUID (basically just a random number, required by Analytics).
* Basically tells that somewhere someone ran the script with a certain version.
* Credit for the idea goes to Russel Savage, who posted his version at http://www.freeadwordsscripts.com/2013/11/track-adwords-script-runs-with-google.html.
*/
function trackInAnalytics(version){
  // Create the random UUID from 30 random hex numbers gets them into the format xxxxxxxx-xxxx-4xxx-yxxx-xxxxxxxxxxxx (with y being 8, 9, a, or b).
  var uuid = "";
  for(var i = 0; i < 30; i++){
    uuid += parseInt(Math.random()*16).toString(16);
  }
  uuid = uuid.substr(0, 8) + "-" + uuid.substr(8, 4) + "-4" + uuid.substr(12, 3) + "-" + parseInt(Math.random() * 4 + 8).toString(16) + uuid.substr(15, 3) + "-" + uuid.substr(18, 12);
  
  var url = "http://www.google-analytics.com/collect?v=1&t=event&tid=UA-74705456-1&cid=" + uuid + "&ds=adwordsscript&an=qstracker&av="
  + version
  + "&ec=AdWords%20Scripts&ea=Script%20Execution&el=QS%20Tracker%20v" + version;
  UrlFetchApp.fetch(url);
}
[collapse]

Small Update March 22

I’ve updated the code a little to smooth out some spreadsheet operations. If the script runs fine, no update is necessary.

Update August 31

Google has sent out emails to anyone using scripts for QS reporting, warning us about a change. The Quality Score Tracker won’t be affected by this change. No action is required.

What Google changed: Keywords without a QS used to report a 6/10. Now they will report “–” or null. This is a good thing for advertisers since they now get a clearer picture.

As a result, you *might* see fewer QS 6 keywords in your reports and in the dashboard for this script. However, this only affects new keywords and keywords with very few impressions. Because of this, weighted averages (like the default impression weighted average) should be unaffected.

Small Update October 27

Fixed a minor bug that caused a Spreadsheet error in line 239 in rare cases.

About Martin Roettgerding
Martin Roettgerding is the head of SEM at SEO/SEM agency Bloofusion Germany. On Twitter he goes by the name @bloomarty, and you can find him regularly on #ppcchat.

  • Arjan ter Huurne

    “Google recently launched a new version of its API that allows someone to download each Quality Score factor, along with its status (average, below average, above average) for every search keyword in an account” – are you going to add this layer of complexity? That’d be awesome!

    • Martin Röttgerding

      Yes, I plan to, but I haven’t decided in what way this should be stored or represented in the dashboard. Do you have a preference?

    • Martin Röttgerding

      By the way, if you want to see your own data on QS details: I’ve created a little script to download a keyword report including all QS fields. You can find it at the end of my post about QS factors: http://www.ppc-epiphany.com/2016/02/04/keyword-quality-score-up-close/

  • Heh, I Googled quality score history Adwords then got your V1 post, googled the same thing with “V2” saw your vv post, then searched v3 and saw this fresh awesome nugget 🙂 this is great!

  • Roy Smid

    Thnx for your efforts Martin, it looks promising. I’ll give it a testrun soon.

  • Liviu Olos

    Very nice script Martin. I have added in the QS conditional formatting so I can see when the QS increased or decreased for a certain keyword. On the “QS Report” I have used conditional formatting with the formula =indirect(CHAR(COLUMN()+64)&ROW())>indirect(CHAR(COLUMN()+63)&ROW()) for green/increase and =indirect(CHAR(COLUMN()+64)&ROW())<indirect(CHAR(COLUMN()+63)&ROW()) for red – decrease.

    • Martin Röttgerding

      Hi Liviu,
      This is a fantastic idea! If possible, I’ll add this to the next version. Thanks for sharing!

      • Liviu Olos

        Here is another coloring possibility https://docs.google.com/a/docs.loftrek.ro/spreadsheets/d/1TZMstYsAxRSliMKSl2nmGbiP0R9JlCDjjHTEZScJ7Us/edit?usp=sharing I have also noticed that remarketing display keywords are counted with 0 QS, this leads to an QS average difference (see bottom sum in the example) – =sumif(E2:E112,”>0″)/countif(E2:E112,”>0″).

        • Martin Röttgerding

          Unfortunately, I can’t access the spreadsheet…

          About remarketing keywords: Yes, I noticed this too late and now the script also tracks some QS 0 keywords. This does not affect the dashboard, though. The dashboard generates its charts from the data in the summary file. There are only values for 1-10 stored.

          What’s still wrong is the total number of keywords that’s stored in the summary file, Percentages sheet, row 12. This number isn’t used anywhere, but is there in case you want to get the calculate numbers from the percentages. The other totals (keywords with impressions, etc.) should not be affected since, by default, only stats from Google search are tracked.

          Anyway, I’ll fix this in the next version. Thanks for the heads up!

          • Liviu Olos

            You’re welcome. I would be glad to be involved in the next version. If you want we can talk via Skype. I really like this project and I would like to help it’s expansion (also with the individual QS components).
            I have changed the rights to the google doc and I have also inserted a new graph suggestion for the “Impression weighted QS”. Thanks a lot!

    • Roy Smid

      Hi Liviu,

      Sounds interesting, where did you ad this code?

      Regards,

      Roy

  • Reinier van Eijk

    I’ve shared this on my LinkedIn, thanks so much again Martin. the PPC community owes you 🙂

  • Александр Сидачёв

    Thanks Martin,
    a good job

  • Verfranzt Nocheins

    Hi

    • Martin Röttgerding

      Hey,
      The weighted QS means that each QS is multiplied by the number of clicks/impressions/conversions/value and then divided by the total number of the same metric.

      For example, lets say you have to keywords with QS 5 and 7. The unweighted average is 6.

      Lets say the first keyword has 10 clicks, the other one has 20. Now the click weighted average is (5×10 + 7×20) / (10+20) = 190/30 = 6.33

      The weights are supposed to help you evaluating your QS situation. Most people prefer impression weighted QS because it means that keywords with many impressions gets more weight while keywords without impressions get none.

      • Verfranzt Nocheins

        Beautiful, thanks Martin!

  • Sherry Projects-Abroad

    Hi thank you for the script. I implemented the script a while but can only see last 6 days’ history data, is that what it meant to ?

    • Martin Röttgerding

      Hi Sherry,
      Thank you for the feedback! No, that’s not supposed to happen…

      Is it only 6 days of data in the “QS report” spreadsheet, or in the dashboard, or both?

      • Sherry Projects-Abroad

        Hi Martin,
        There is only last 6 days data in both “QS report” and dashboard

        • Martin Röttgerding

          Hi Sherry,
          If both are missing, it suggests that the script never got to the part where data was to be written. Either there was an error or the script didn’t run…

          Both should be documented in the script logs on the scripts page in your account. Could you check for an earlier time when the script ran (when no history was stored), click on the logs link there and tell me what the log says?

          • Sherry Projects-Abroad

            Hi Martin,
            Thanks for the reply. The logs are successful, if i go to the worksheet URL provided in logs, the history are there. But if I directly go to my Google drive’s folder and open worksheet, i can only see last 6 days. The two URLs’ encoding are a bit different.

          • Martin Röttgerding

            Hi Sherry,
            This is interesting. So there are different files… there are a few scenarios I could think of:
            1) The script’s user somehow changed and the files are now in different Google drives. If you go to https://drive.google.com – can you find both spreadsheets there? (the one with all the data and the 6 days one)
            2) The script setting “baseFolder” has been changed at some point. Then the script wouldn’t find the old files and start new ones in the new folder.
            3) The folder’s name in Google Drive has been changed at some point. Then the script would basically do the same as in 2 and recreate the folder to start fresh.

            For 2 and 3: Are there different folders for the Quality Score Tracker in https://drive.google.com ?

          • Sherry Projects-Abroad

            Hi Martin,
            Thank you for the reply
            It’s 3) Yes there are two folders, I changed folder’s name at some point, the renamed folder has all record inside 🙂

            Thank you very much !

  • Julie

    I have tried changing the name of these sheets and it is not working. I have several accounts I want to track for and this would be easier if I could change the name. Can someone help?

    • Martin Röttgerding

      Hey Julie,
      I’m here to help! What exactly isn’t working? What sheets would you like to rename?

      • Julie

        Martin,

        I tried changing this and adding the client name and ID and it still just named it Quality score tracker.
        I am wondering if I didn’t change in the right place. I am not terribly familiar with scripts.

        “baseFolder” : “Quality Score Tracker/”,
        /*
        * Whether to add a client folder in the base folder (resulting in a folder like “Quality Score Tracker/CLIENT_NAME (123-456-7890)/”)
        * The folder’s name is not important, as long as the Adwords client id remains in it.
        * This is useful if you want to track multiple accounts with this script.
        */
        “useClientFolder” : 1,

        • Martin Röttgerding

          Hmmm, can you tell me again what you’re trying to accomplish? Sorry, I may be a bit slow tonight…

          The script basically puts it’s files into a client folder, which is located in the base folder:
          / baseFolderAsSpecified / Client Name (123-456-7890)

          In this folder, the summary file and the report files are stored. You can change the summary file’s name. You can also change the client folder’s name, but this has to be done in Google Drive by just changing the actual folder. As long as the client’s id remains, the script can find it again.

          With this structure, you can use the script in several accounts using an MCC login. Since they’ll all use the Google Drive from that login, all of your folders will be in the same base folder, so that it’s easy to keep them close together.

  • Vincent

    Hi Martin, first of all, thanks for your amazing job.
    I’m attempting to run the script on a very big account but this following message occurs : “Exceeded maximum execution time”.
    Does someone already had this issue and knows how to solve it ?
    Regards,

    • Martin Röttgerding

      Hi Vincent,
      That’s the first time I heard about that – there must be a lot of keywords. On occasion I’ve seen the QS Tracker taking a bit longer than usual (like, 5 instead of 2 minutes). If you’re in luck, the next time it will do just fine.

      Unfortunately I don’t think there’s a way to get around this issue. The script has to go through all of the keywords. It already employs the fast way to do that.

      One alternative here would be to only track some of your keywords. If the account is so big I imagine there’ll be a lot of keywords without impressions. You could exclude them by changing this line:

      var awql = “SELECT Id, Criteria, KeywordMatchType, CampaignId, CampaignName, AdGroupId, AdGroupName, QualityScore, Impressions, Clicks, Conversions, ConversionValue FROM KEYWORDS_PERFORMANCE_REPORT WHERE Id NOT_IN [3000000, 3000006] AND Status = ‘ENABLED’ AND AdGroupStatus = ‘ENABLED’ AND CampaignStatus = ‘ENABLED'”;

      to:

      var awql = “SELECT Id, Criteria, KeywordMatchType, CampaignId, CampaignName, AdGroupId, AdGroupName, QualityScore, Impressions, Clicks, Conversions, ConversionValue FROM KEYWORDS_PERFORMANCE_REPORT WHERE Id NOT_IN [3000000, 3000006] AND Status = ‘ENABLED’ AND AdGroupStatus = ‘ENABLED’ AND CampaignStatus = ‘ENABLED’ AND Impressions > 0”;

      This way, only keywords with impressions > 0 will be tracked. It’s not perfect, but I imagine it’ll do the trick for your account. It will affect metrics like number of keywords per QS, but all of the weighted metrics (like impression-weighted QS) are unaffected – and those are the ones that cound.

      Let me know if this helped.

      • Vincent

        Hi Martin,
        Thanks a lot for your answer, it works very well now. It totally does the trick for this account. As you said, many keywords don’t have impressions, that’s why the script exceeded the limit given by Google.

  • Callum Taylor

    Hi Martin,

    Firstly, as people have mentioned before, this is fantastic!

    I’ve been looking through the script, and was wondering whether there is any way to filter which parts of the account are looked at in terms of quality score, and if it was possible to filter it by campaign or label – any thoughts?

    Thanks!

    • Martin Röttgerding

      Hi Callum,
      thanks! Yes, you could tweak the script to just select some keywords for tracking. Look for the line that starts with var awql = “SELECT

      The keywords to track are selected there. In the WHERE part of the statement you see some conditions and you can easily add new ones, like “AND QualityScore < 5" or "AND CampaignName = 'Something'" or "AND CampaignName CONTAINS 'something'".

      What's not available here are campaign labels. The report here is about keywords and you can only refer to keyword attributes, like keyword labels.

      To do this with campaign labels you'd have to code a little more. Basically you'd select the campaigns from the label, collect their IDs and then put them into the statement above, making something like "AND CampaignId IN [1843523, 7423423, 547654241]".

      HTH
      Martin

      • Callum Taylor

        Perfect, thanks Martin – I’ll give your suggestions a go and report back!

  • Ярослав Біличенко

    Martin, there is a slight mistake in script for option “activeKeywordsOnly’. For it in row 170 you need to exclude ‘AND Status = ‘ENABLED’ AND AdGroupStatus = ‘ENABLED’ AND CampaignStatus = ‘ENABLED’. Otherwise row 173 won’t work.

    • Martin Röttgerding

      Ooops, you’re right. Thanks for pointing this out! I’m fixing it now.

      • Resh

        Hi Martin. I’m trying scripts for the first time and owing to the plethora of scripts available to “give a go” I chose yours because of my interest in QS and that you actually reply to threads!

        I have followed instructions and have found that I have an error message reading ”
        Invalid reporting query. (line 174)” Please see http://prntscr.com/bwi9fe

        Hope you can help set me on my path!

        • Resh

          ah..I figured it. This works at the account level and not MMC level.

          Is this correct? Is it advisable to schedule this monthly?

          Many thanks for this body of work. Amazing!

          • Martin Röttgerding

            Hi Resh,
            Yes, this is correct: The script only works at the account level (for the moment).

            Thanks for the kind words 🙂

            Martin

  • MDaniell

    Hi Martin,

    Love the script, very helpful to find the nuggets of gold to make accounts that much better.

    I’m running into an issue on a new account, in the logs it states:
    “Service error: Spreadsheets (line 239)”

    Are you able to provide any insight into what may cause this error?

    • Mike Pierce

      Hi MDaniell, were you able to figure out the line 239 error? Thanks!

      • Martin Röttgerding

        Hey,
        sorry for getting back to you guys so late. I believe I found and fixed the problem. I’ve update the code above – let me know if it solves the problem.

        • Pavel

          Martin,

          Thanks for being with us, and big thanks for the script.
          I’m experiencing the same issue. Ran the script twice, reinstalled it.
          Now I’m receiving “Service timed out: Spreadsheets (line 240)” message.

          Thank you.

          • Александр Королев

            Try turning off the AutoFilter if it is applied in the spreadseet

      • Pavel

        Through the “Log” tab.
        I’m having the same error, and I’ve just installed the script, like 30 mins ago.

  • Anastasiya Ivanova

    Thank you Martin! this is great script!
    Can you help me?
    I would like to use this script for different campaigns separately. But there are the same sheets for different scripts in Google Drive and it is overwriting. How can I change the code to creating different sheets for each one of the scripts?

  • Dana Athra

    @martinrttgerding:disqus Thank you so much for this useful tool!

    I just tried running it but unfortunately am facing an invalid error in line 174 as shown in the SS, despite running it in the account level. Is there something am doing wrong?

    http://prnt.sc/caehmg

    • Martin Röttgerding

      Hi Dana,
      Sorry for getting back to you so late. By the way, thanks for the screenshot, this is very helpful.

      Google seems to be having problems with reporting at the moment. We’re seeing a lot of random failed reports in our various applications. The problem you reported seems to be the same thing. Since it appears to be a random thing, I’d guess that the script should work fine the next time you run it. Or does the problem persist?

      • Dana Athra

        Thanks for the reply! Unfortunately it presist. I would LOVE to test it out. Please let me know if you find a way around this.

        • Martin Röttgerding

          Hey Dana, I’m sorry for taking so long. I’ll get back to you, but I’m a bit behind at the moment…

          • Tom Harris

            Hi Martin,

            This tool looks incredibly helpful–thank you for supporting it! I am having this precise problem – line 174, which is the same line in Dana’s screenshot, with the same error message.

          • Martin Röttgerding

            Hey, sorry for taking so long. If one of you could paste the script configuration, I should be able to find an answer. Could you copy and paste lines 13-82 here?

  • Leonardo Cunha

    @martinrttgerding:disqus Thank you so much for this brilliant tool.

    I just found out that in the week of September 12 , 2016, we’ll start seeing null Quality Scores (designated by “–” in the table).

    Source ( https://support.google.com/adwords/answer/7050591?hl=en&utm_source=awfe&utm_medium=referral&utm_campaign=notifications&authuser=0 )

    • Martin Röttgerding

      Hey,
      The script should be unaffected by the changes. To be sure I ran a simulation with some keywords reporting “–” and there was no problem.

      Your numbers might change a little bit due to Google now truly reporting that former QS 6 keywords actually have no QS. The fluctuation should be minimal, though.

  • Marc

    Hi Martin, Is there a need to make any adjustments in the script because of the upcoming Google QS update which gives a value NULL (‘–‘) back to keywords that are generation almost no impressions and clicks?

    • Martin Röttgerding

      Hi Marc,
      The script shouldn’t be affected by this, so there’s no action necessary. I’ve added a note about this in the post.

  • Mitchell Holt

    Martin, this is awesome!

    Quick question though. Does this script still automatically create and update the labels in the AdWords interface like V2? If not how would you suggest I implement this feature?

    • Martin Röttgerding

      Hi Mitchell,
      The script doesn’t use labels anymore. If for some reason you still need them, I’d suggest you copy and adapt v2 according to your needs. But I think v3 should already have everything you need for QS tracking 🙂

  • Hatem Jarad

    Hi Martin,

    I got this error “This script is currently not authorized to run in your account. Please start the authorization process again.”
    Anything I can do?

    • Greg Stout

      Had the same problem. Every time I try to authorize I get the same message.

  • Hunters Services

    Very interested in this but the link to download it seems to be broken. When I click on it, it re-routes me back to this page. Thanks for anything you can do. 🙂

  • Sam Lloyd

    Thanks for sharing! Is there a way to track just certain campaigns or keywords?

    • Martin Röttgerding

      Hey,
      Yes, if you tweak the script a little. I’ve updated the FAQ in the dashboard to include a description how to do this.

  • Stein-Thore Nergaard

    Hello Martin!

    Loving your script, and works on pretty much all my accounts, but on two of them i get this exact error :” Service timed out: Spreadsheets (line 234)” . Any idea what it might be ? Same line 234 is on both the accounts…. Don’t know how to resolve it as there is literally no difference in the script or the amount of keywords on the accounts in question vs the ones that work….

    • Mike Pierce

      Similar issue here, except that it’s line 239.

    • Martin Röttgerding

      Hey Stein-Thore,
      I couldn’t find an explanation for this error. A service timeout sounds like a temporary thing… Any chance it works now?

      • Mike Pierce

        Hi Martin,

        Thank you so much for your help! Unfortunately I’m still seeing ‘Service timed out: Spreadsheets (line 239)’. I copied the script from the expandable box above and replaced the existing one – is that the correct step to take? Thanks again.

        • Martin Röttgerding

          Hi Mike,
          Apparently it’s the same problem in both lines. It makes no sense, but I managed to find other people who had the same problem with different scripts. Apparently it’s an old problem that Google refuses to fix…
          I may have found a workaround and updated the script again. Could you try it out and tell me if it works?

          • Mike Pierce

            Drat. Service timed out: Spreadsheets (line 240). What’s interesting (at least to me) is that the two Google docs think they’ve been updated. Nothing appears to have changed on the sheets, but revision history says ‘last edit made 6 minutes ago’ for my most recent attempt to run the script. Not sure if that’s helpful. I really appreciate you trying to fix this!

          • Martin Röttgerding

            Argh, this is unfortunate… I was under the impression that it was only specific calls to the spreadsheet that fail, but apparently they all fail at that point. Basically, in the beginning of the script it works fine and at some point it doesn’t. I’ll keep digging, but right now I’m unsure there is a way to fix this in the script.

            Anyway, something else might help: I read that someone had just replaced their spreadsheet with a copy. So if you’d just copy the report spreadsheet, remove/rename the original and name the copy like the original it might solve the problem.

          • Mike Pierce

            Thanks Martin. Copying, removing, and then renaming the copy with the original also didn’t work. However, I just deleted the report Google Sheet altogether (kept the Dashboard + Summary) sheet, and reran the script. The script ran properly, and the QS report sheet now only has data on in since today (10/27/2016), but the Dashboard + Summary still has all the historical data PLUS today’s. So that’s a win in my book. Thank you again for the awesome script and your help troubleshooting it!

          • Martin Röttgerding

            Ah okay, then I guess the problem was somehow connected to the spreadsheet file. Thanks for your feedback!

  • Aaron Weiner

    Interesting script.

    I noticed in the trackInAnalytics function, you left in the Analytics ID for I’m guessing your own account. Shouldn’t that value be updated by the end user?

    • Martin Röttgerding

      Yes, that’s my own account. It’s not intended to be updated as it has no further use beyond basically sending a “ping”.

      I wanted to know how many people actually use the script, but I didn’t feel comfortable to actually track anything that’s even remotely personal. That’s why the information sent is kept to a minimum: the script’s name including the version (“QS Tracker v3.0”) and a random number (required by Analytics).

      • Aaron Weiner

        Understood. Thank you

  • Olena Sawicka

    Hi Martin,

    Thank you so much for sharing and updating this awesome script!

    Is it possible to track expected CTR, ad relevance and LP relevance on a daily basis as the Quality Score is tracked in this case? Could you please share a script for this if you have it?

  • Mike

    Hi Martin,

    I’m getting the error: Service timed out: Spreadsheets (line 231) Is there any update on this issue as i seen you’ve discussed similar issues below?

    Thanks,

    Mike

  • Shaun Friedlander

    I also received an authorization error for this script. Does anybody know how to resolve it?

  • Simon Toussaint

    Wow! Great script. Actually using it on a daily basis since few months and it’s really a nice tool.

    Life before this script < life after this script.

  • Tom Harris

    Hi Martin,

    I love this script, and have been using it daily for over a month now. Two quick questions, though:

    1) I started using the script on 10/21 and it returned quality scores for roughly 20k of the 22k keywords it was tracking in account X. Starting on 11/4, it began returning only 8k quality scores. I thought this was due to some campaign changes made by another analyst (which weren’t major but it was the only thing I could think of). However, since copying the script over to account Y i noticed that the script was returning only 1k account Y quality scores for 11k keywords, which kind of throws my theory out the window. The rest of the keywords in both accounts are returning dashes “–“. Any idea what could be causing this?

    2) I also attempted to copy the script to account Z but each attempt to run the script yesterday and today results in a time out error. Any ideas here?

    Thanks again, despite a couple of issues I’ve had recently this is by far the best script out there. I really appreciate your work 🙂

    Tom

    • Martin Röttgerding

      Hi Tom,

      Thanks for your kind words 🙂

      About the first question: Google has changed the reporting of quality scores for keywords in October. Keywords without enough data (usually: impressions) used to display a 6/10 but now report just those two dashes. This is actually an improvement since the old 6/10 was meaningless and could give you the wrong impression.

      About your second question: Is this a script timeout (ie the script goes over 30 minutes) or a service timeout from Google Spreadsheets? If it’s the latter, I have no good explanation, but there’s a comment thread about this below. There, Mike Pierce found the solution to remove the QS report sheet so that the script would re-create it. It worked fine after that.

      If it’s a script timeout I assume the account is really, really big…? In this case I could come up with a workaround (make the script skip over keywords without QS / with dashes only). Let me know and I’ll get back to you.

      Cheers

      Martin

      • Tom Harris

        Hey Martin,

        Yes, the timing out account is quite big, and I believe it is a script timeout as the error is “Exceeded maximum execution time.” A filter that excludes null values would be incredibly helpful! If you could do that that would be great.

        Thanks,
        Tom

  • Александр Королев

    If a table column headings apply AutoFilter script fails.
    Service error: Spreadsheets (line 240)

  • Александр Королев

    Do you plan to add the details of the components of Quality Score?
    CreativeQualityScore
    PostClickQualityScore
    SearchPredictedCtr