Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Save BrainlabsDigital/df8fe8fb5bed16e3d882e1642e3dcad5 to your computer and use it in GitHub Desktop.
Save BrainlabsDigital/df8fe8fb5bed16e3d882e1642e3dcad5 to your computer and use it in GitHub Desktop.
Script to check keyword and ad text for basic errors or out-of-date messaging.
/**
*
* 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
@tymosd
Copy link

tymosd commented Mar 29, 2018

Has anyone tested this? The keyword checker is great, but I tried inserting several of the Common Misspellings into my ads and the script couldn't identify them.

@Markg7
Copy link

Markg7 commented Jun 15, 2018

I'm thinking that the ad checking parts are using the old text ad names, not ones updated for the new expanded text ads.

@Markg7
Copy link

Markg7 commented Jun 16, 2018

This code should work - I've commented out the old code & added the updated items:

**
*

  • 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
  • MODIFIED by Markg7 15-6-18 - Checks extended text ads rather than old text ad format

**/

//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
//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 adLines = ['HeadlinePart1', 'HeadlinePart2', 'Description', 'Path1', 'Path2'];
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 " +
  "SELECT CampaignName, AdGroupName, HeadlinePart1, HeadlinePart2, Description, Path1, Path2 " +
  "FROM   AD_PERFORMANCE_REPORT " +
  "WHERE AdGroupStatus = ENABLED AND Status = ENABLED " +
  //"AND AdType = TEXT_AD AND CombinedApprovalStatus != DISAPPROVED " +
  "AND AdType = EXPANDED_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(", ")]);
    adsWithBadText.push([row["CampaignName"], row["AdGroupName"], row['HeadlinePart1'],
                         row['HeadlinePart2'], row['Description'], row['Path1'], row['Path2'],
                         textFound.join(", ")]);
  }
  
} // end while

//var headers = ["Campaign", "Ad Group", 'Headline', 'Description 1', 'Description 2',
//               'Display Url', 'Problematic Text'];
var headers = ["Campaign", "Ad Group", 'HeadlinePart1', 'HeadlinePart2', 'Description',
               'Path1', 'Path2', '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 adLines = ['HeadlinePart1', 'HeadlinePart2', 'Description', 'Path1', 'Path2'];
var adsWithBadText = [];

var adTextReport = AdWordsApp.report(
  //"SELECT CampaignName, AdGroupName, Headline, Description1, Description2, DisplayUrl " +
  "SELECT CampaignName, AdGroupName, HeadlinePart1, HeadlinePart2, Description, Path1, Path2 " +
  "FROM   AD_PERFORMANCE_REPORT " +
  "WHERE AdGroupStatus = ENABLED AND Status = ENABLED " +
  //"AND AdType = TEXT_AD AND CombinedApprovalStatus != DISAPPROVED " +
  "AND AdType = EXPANDED_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'],
    adsWithBadText.push([row["CampaignName"], row["AdGroupName"], row['HeadlinePart1'],
                         row['HeadlinePart2'], row['Description'], row['Path1'], row['Path2'],                   
                         textFound.join(", "), didYouMean.join(", ")]);
  }
  
} // end while

//var headers = ["Campaign", "Ad Group", 'Headline', 'Description 1', 'Description 2',
//               'Display Url', 'Possible Misspelling', 'Possible Corrections'];
var headers = ["Campaign", "Ad Group", 'HeadlinePart1', 'HeadlinePart2', 'Description',
               'Path1', 'Path2', 'Possible Misspelling', 'Possible Corrections'];
printRows(sheet, "Ad Copy With Possible Misspellings", headers, adsWithBadText);

} catch (e) {
Logger.log("Ad spell checking failed: " + e);
}

} // function adSpellingChecking

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment