Script to check keyword and ad text for basic errors or out-of-date messaging.

Check If You've Screwed Up Your AdWords Account.js

/**
*
* AdWords Script for keyword and ad checking.
* Checks keyword text for punctuation suggesting the wrong match type, checks
* broad match keywords for missing BMM. Checks ad text for text that suggests 
* ads are out-of-date (like previous years) and for common English spelling
* mistakes.
*
* Version: 1.1
* Updated 2017-01-05: changed 'CreativeApprovalStatus' to 'CombinedApprovalStatus'
* Google AdWords Script maintained by brainlabsdigital.com
*
**/

//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
//Options

var spreadsheetUrl = "https://docs.google.com/YOUR-SPREADSHEET-URL-HERE";
// The URL of the Google Doc the results will be put into.

var campaignNameDoesNotContain = [];
// Use this if you want to exclude some campaigns.
// For example ["Display"] would ignore any campaigns with 'Display' in the name,
// while ["Display","Shopping"] would ignore any campaigns with 'Display' or
// 'Shopping' in the name.
// Leave as [] to not exclude any campaigns.

var campaignNameContains = [];
// Use this if you only want to look at some campaigns.
// For example ["Brand"] would only look at campaigns with 'Brand' in the name,
// while ["Brand","Generic"] would only look at campaigns with 'Brand' or 'Generic'
// in the name.
// Leave as [] to include all campaigns.

var ignorePausedCampaigns = true;
// Set this to true to only look at currently active campaigns.
// Set to false to include campaigns that had impressions but are currently paused.

var checkKeywords = true;
// Set this to true to look at keyword text for errors like missing BMM.

var checkAdText = true;
// Set this to true to look at ad text for errors like previous years.

var checkSpelling = true;
// Set this to true to check ad text for some common spelling errors.

var checkAdsFor = ["2013", "2014", "2015", "Easter"];
// This is the text that the script will look for in ad copy. Feel free to add more!


//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
// Advanced Options
var misspellingsSheetUrl = "https://docs.google.com/spreadsheets/d/1Z2Fg_F8WhmY8Ey5Bv4Zuk8vcTW7A2EoVwMbGVz7TNms/edit#gid=0";
// This spreadsheet has the list of English spelling errors, used if checkSpelling
// is true.


//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
// Functions

function main() {
  var spreadsheet = checkSpreadsheet(spreadsheetUrl, "the spreadsheet");
  var sheet = spreadsheet.getSheets()[0];
  var campaignIds = getCampaignIds();
  
  if (checkKeywords) {
    keywordChecking(campaignIds, sheet);
    Logger.log("Finished keyword checks.");
  }
  
  if (checkAdText) {
    adTextChecking(campaignIds, sheet);
    Logger.log("Finished ad text checks.");
  }
  
  if (checkSpelling) {
    adSpellingChecking(campaignIds, sheet); 
    Logger.log("Finished common misspelling checks.");
  }
}


// Check the spreadsheet URL has been entered, and that it works
function checkSpreadsheet(spreadsheetUrl, spreadsheetName) {
  if (spreadsheetUrl.replace(/[AEIOU]/g,"X") == "https://docs.google.com/YXXR-SPRXXDSHXXT-XRL-HXRX") {
    throw("Problem with " + spreadsheetName + " URL: make sure you've replaced the default with a valid spreadsheet URL.");
  }
  try {
    var spreadsheet = SpreadsheetApp.openByUrl(spreadsheetUrl);
    return spreadsheet;
  } catch (e) {
    throw("Problem with " + spreadsheetName + " URL: '" + e + "'");
  }
}

// Get the IDs of campaigns which match the given options
function getCampaignIds() {
  var whereStatement = "WHERE ";
  var whereStatementsArray = [];
  var campaignIds = [];
  
  
  if (ignorePausedCampaigns) {
    whereStatement += "CampaignStatus = ENABLED ";
  } else {
    whereStatement += "CampaignStatus IN ['ENABLED','PAUSED'] ";
  }
  
  for (var i=0; i<campaignNameDoesNotContain.length; i++) {
    whereStatement += "AND CampaignName DOES_NOT_CONTAIN_IGNORE_CASE '" + campaignNameDoesNotContain[i].replace(/"/g,'\\\"') + "' ";
  }
  
  if (campaignNameContains.length == 0) {
    whereStatementsArray = [whereStatement];
  } else {
    for (var i=0; i<campaignNameContains.length; i++) {
      whereStatementsArray.push(whereStatement + 'AND CampaignName CONTAINS_IGNORE_CASE "' + campaignNameContains[i].replace(/"/g,'\\\"') + '" ');
    }
  }
  
  for (var i=0; i<whereStatementsArray.length; i++) {
    var adTextReport = AdWordsApp.report(
      "SELECT CampaignId " +
      "FROM   CAMPAIGN_PERFORMANCE_REPORT " +
      whereStatementsArray[i] +
      "DURING LAST_30_DAYS");
    
    var rows = adTextReport.rows();
    while (rows.hasNext()) {
      var row = rows.next();
      campaignIds.push(row['CampaignId']);
    }
  }
  
  if (campaignIds.length == 0) {
    throw("No campaigns found with the given settings.");
  }
  
  Logger.log(campaignIds.length + " campaigns found");
  return campaignIds;
}

// Prints an array of rows into the spreadsheet
function printRows(sheet, title, headers, rows) {
  try {
    var printArray = [];
    
    sheet.getRange("R" + (sheet.getLastRow()+2) + "C1").setValue(title);
    if (rows.length == 0) {
      sheet.appendRow(["No issues found"]);
      Logger.log("Nothing to output for '" + title + "'");
      return;
    }
    
    sheet.appendRow(headers);
    
    for (var i=0; i < rows.length; i++) {
      printArray.push(rows[i]);
    }
    
    var lastRow = sheet.getLastRow();
    sheet.getRange("R" + (lastRow + 1) + "C1:R" + (lastRow+printArray.length)
    + "C" + (printArray[0].length) ).setValues(printArray);
    
    Logger.log("Printed " + rows.length + " rows for '" + title + "'");
    
  } catch (e) {
    Logger.log("Printing rows '" + title + "' failed: " + e); 
  }
}

function keywordChecking(campaignIds, sheet) {
  try {
    
    var broadMissingPlusses = [];
    var nonBroadWithPlusses = [];
    var nonExactWithBrackets = [];
    var nonPhraseWithQuotes = [];
    
    var keywordMatchReport = AdWordsApp.report(
      "SELECT CampaignName, AdGroupName, Criteria, KeywordMatchType " +
      "FROM   KEYWORDS_PERFORMANCE_REPORT " +
      "WHERE AdGroupStatus = ENABLED AND Status = ENABLED AND IsNegative = FALSE " +
      "AND CampaignId IN [" + campaignIds.join(",") + "] " +
      "DURING LAST_30_DAYS");
    
    var keywordMatchRows = keywordMatchReport.rows();
    while (keywordMatchRows.hasNext()) {
      var keywordMatchRow = keywordMatchRows.next();
      
      if (keywordMatchRow["KeywordMatchType"].toLowerCase() === "broad") {
        
        if(keywordMatchRow["Criteria"].indexOf("+") <0) {
          //if the broad KW is entirely missing +s
          broadMissingPlusses.push([keywordMatchRow["CampaignName"],keywordMatchRow["AdGroupName"],"'"+keywordMatchRow["Criteria"],keywordMatchRow["KeywordMatchType"]]);
        } else {
          // check that each word starts with a +
          var words = keywordMatchRow["Criteria"].split(" ");
          var missingPlus = false;
          for (var j=0; j<words.length; j++) {
            if (words[j].substr(0,1) != "+") {
              missingPlus = true;
              break;
            }
          }
          if (missingPlus) {
            broadMissingPlusses.push([keywordMatchRow["CampaignName"],keywordMatchRow["AdGroupName"],"'"+keywordMatchRow["Criteria"],keywordMatchRow["KeywordMatchType"]]);
          }
        }
        
      } else {
        // If the keyword is not broad
        if(keywordMatchRow["Criteria"].indexOf("+") > -1) {
          nonBroadWithPlusses.push([keywordMatchRow["CampaignName"],keywordMatchRow["AdGroupName"],"'"+keywordMatchRow["Criteria"],keywordMatchRow["KeywordMatchType"]]);
        } 
      }
      
      if (keywordMatchRow["KeywordMatchType"].toLowerCase() != "exact") {
        if(keywordMatchRow["Criteria"].indexOf("[") > -1 || keywordMatchRow["Criteria"].indexOf("]") > -1) {
          nonExactWithBrackets.push([keywordMatchRow["CampaignName"],keywordMatchRow["AdGroupName"],"'"+keywordMatchRow["Criteria"],keywordMatchRow["KeywordMatchType"]]);
        }
      }
      
      if (keywordMatchRow["KeywordMatchType"].toLowerCase() != "phrase") {
        if(keywordMatchRow["Criteria"].indexOf('"') > -1) {
          nonPhraseWithQuotes.push([keywordMatchRow["CampaignName"],keywordMatchRow["AdGroupName"],"'"+keywordMatchRow["Criteria"],keywordMatchRow["KeywordMatchType"]]);
        }
      }
      
    }//end while
    
    var headers = ["Campaign", "Ad Group", "Keyword", "Match"];
    printRows(sheet, "Broad Match Keywords Missing +s", headers, broadMissingPlusses);
    printRows(sheet, "Non-Broad Match Keywords With +s", headers, nonBroadWithPlusses);
    printRows(sheet, "Non-Exact Match Keywords With [ or ]", headers, nonExactWithBrackets);
    printRows(sheet, 'Non-Phrase Match Keywords With "s', headers, nonPhraseWithQuotes);
    
  } catch (e) {
    Logger.log("Keyword checking failed: " + e); 
  }
} // end function keywordChecking


function adTextChecking(campaignIds, sheet) {
  try {
    var adLines = ['Headline', 'Description1', 'Description2', 'DisplayUrl'];
    var adsWithBadText = [];
    var patterns = [];
    var charactersToEscape = ["\\", "/", ".", "?", "+", "*", "^", "$", "[", "]", "(", ")", "{", "}"];
    for (var k=0; k<checkAdsFor.length; k++) {
      var cleanedText = checkAdsFor[k].toLowerCase();
      for (var i=0; i<charactersToEscape.length; i++) {
        cleanedText = cleanedText.replace(charactersToEscape[i],"\\" + charactersToEscape[i]);
      }
      patterns.push( RegExp("(^|\\W)" + cleanedText + "($|\\W)"));
    }
    
    var adTextReport = AdWordsApp.report(
      "SELECT CampaignName, AdGroupName, Headline, Description1, Description2, DisplayUrl " +
      "FROM   AD_PERFORMANCE_REPORT " +
      "WHERE AdGroupStatus = ENABLED AND Status = ENABLED " +
      "AND AdType = TEXT_AD AND CombinedApprovalStatus != DISAPPROVED " +
      "AND CampaignId IN [" + campaignIds.join(",") + "] " +
      "DURING LAST_30_DAYS");
    
    var rows = adTextReport.rows();
    
    while (rows.hasNext()) {
      var row = rows.next();
     
      var adCopy = "";
      for (var j=0; j<adLines.length; j++) {
        adCopy += " " + row[adLines[j]];
      }
      adCopy = adCopy.toLowerCase();
      var textFound = [];
      
      for (var k=0; k<checkAdsFor.length; k++) {
        if (adCopy.match(patterns[k])) {
          textFound.push(checkAdsFor[k]);
        }
      }
      
      if (textFound.length > 0) {
        adsWithBadText.push([row["CampaignName"], row["AdGroupName"], row['Headline'],
                             row['Description1'], row['Description2'], row['DisplayUrl'],
                             textFound.join(", ")]);
      }
      
    } // end while
    
    var headers = ["Campaign", "Ad Group", 'Headline', 'Description 1', 'Description 2',
                   'Display Url', 'Problematic Text'];
    printRows(sheet, "Ad Copy With Problematic Text", headers, adsWithBadText);
    
  } catch (e) {
    Logger.log("Ad text checking failed: " + e); 
  }
  
} // function adTextChecking


function adSpellingChecking(campaignIds, sheet) {
  try {
    var misspellingsSpreadsheet = checkSpreadsheet(misspellingsSheetUrl, "the misspelling spreadsheet");
    var misspellingsSheet = misspellingsSpreadsheet.getSheets()[0];
    var misspellings = misspellingsSheet.getRange(2, 1, misspellingsSheet.getLastRow()-1, 2).getValues()
    
    for (var k=0; k<misspellings.length; k++) {
      misspellings[k][0] = " " + misspellings[k][0] + " ";
    }
    
    var adLines = ['Headline', 'Description1', 'Description2', 'DisplayUrl'];
    var adsWithBadText = [];
    
    var adTextReport = AdWordsApp.report(
      "SELECT CampaignName, AdGroupName, Headline, Description1, Description2, DisplayUrl " +
      "FROM   AD_PERFORMANCE_REPORT " +
      "WHERE AdGroupStatus = ENABLED AND Status = ENABLED " +
      "AND AdType = TEXT_AD AND CombinedApprovalStatus != DISAPPROVED " +
      "AND CampaignId IN [" + campaignIds.join(",") + "] " +
      "DURING LAST_30_DAYS");
    
    var rows = adTextReport.rows();
    while (rows.hasNext()) {
      var row = rows.next();
      
      var adCopy = " ";
      for (var j=0; j<adLines.length; j++) {
        adCopy += " " + row[adLines[j]];
      }
      adCopy += " ";
      adCopy = adCopy.toLowerCase();
      adCopy = adCopy.replace(/(_|[^\w\-'0-9])/g," ");
      var textFound = [];
      var didYouMean = [];
      
      for (var k=0; k<misspellings.length; k++) {
        if (adCopy.indexOf(misspellings[k][0]) > -1) {
          textFound.push(misspellings[k][0].trim());
          didYouMean.push(misspellings[k][1]);
        }
      }
      
      if (textFound.length > 0) {
        adsWithBadText.push([row["CampaignName"], row["AdGroupName"], row['Headline'],
                             row['Description1'], row['Description2'], row['DisplayUrl'],
                             textFound.join(", "), didYouMean.join(", ")]);
      }
      
    } // end while
    
    var headers = ["Campaign", "Ad Group", 'Headline', 'Description 1', 'Description 2',
                   'Display Url', 'Possible Misspelling', 'Possible Corrections'];
    printRows(sheet, "Ad Copy With Possible Misspellings", headers, adsWithBadText);
    
  } catch (e) {
    Logger.log("Ad spell checking failed: " + e); 
  }
  
} // function adSpellingChecking