Keywords for Google Shopping Campaigns

Keywordsforshopping

/**
*
* Exact Match For Shopping
*
* This script reads a list of exact match keywords for Shopping campaigns from a Google Doc,
* and then excludes any search queries from those camapigns if they do not match those keywords.
*
* Version: 1.0
* Google AdWords Script maintained by brainlabsdigital.com
*
**/

function main() {
  // Put your spreadsheet's URL here:
  var spreadsheetUrl = "https://docs.google.com/YOUR-SPREADSHEET-URL-HERE";
  // Make sure the keywords are in columns A to C in the first sheet.
  
  
  //////////////////////////////////////////////////////////////////////////////
  var dateRange = "YESTERDAY";
  // By default the script just looks at yesterday's search queries.
  
  var impressionThreshold = 0;
  // The script only looks at searches with impressions higher than this threshold.
  // Use this if you get a wide range of searches and only want to exclude the highest volume ones.
  
  // Read the spreadsheet
  try {
    var spreadsheet = SpreadsheetApp.openByUrl(spreadsheetUrl);
  } catch (e) {
    Logger.log("Problem with the spreadsheet URL: '" + e + "'");
    Logger.log("Make sure you have correctly copied in your own spreadsheet URL.");
    return;
  }
  var sheet = spreadsheet.getSheets()[0];
  var spreadsheetData = sheet.getDataRange().getValues();
  
  // Record each campaigns' keywords, and the words (of more than 4 characters) that are in the keywords
  var keywords = {};
  var words = {};
  var numberOfKeywords = 0;
  var numberOfadGroups = 0;
  for(var i=1; i<spreadsheetData.length; i++) {
    var campaignName = spreadsheetData[i][0];
    var adGroupName = spreadsheetData[i][1];
    if (keywords[campaignName] == undefined) {
      keywords[campaignName] = [];
      words[campaignName] = {};
    }
    if (keywords[campaignName][adGroupName] == undefined) {
      keywords[campaignName][adGroupName] = [];
      words[campaignName][adGroupName] = {};
      numberOfadGroups++;
    }
    var keyword = spreadsheetData[i][2];
    keyword = keyword.toLowerCase().replace(/[^\w\s\d&]/g," ").replace(/ +/g," ").trim();
    keywords[campaignName][adGroupName].push(keyword);
    numberOfKeywords++;
    var keywordWords = keyword.split(" ");
    for (var k=0; k<keywordWords.length; k++) {
      if (keywordWords[k].length > 4) {
        words[campaignName][adGroupName][keywordWords[k]] = true;
      }
    }
  }
  var campaignNames = Object.keys(keywords);
  Logger.log("Found " + numberOfKeywords + " keywords for " + numberOfadGroups + " ad groups in " + campaignNames.length + " campaign(s).");
  
  // Get the IDs of the ad groups named in the spreadsheet
  var adGroupIds = [];
  var campaignIds = [];
  var campaignReport = AdWordsApp.report(
    "SELECT CampaignName, AdGroupName, CampaignId, AdGroupId " +
    "FROM   ADGROUP_PERFORMANCE_REPORT " +
    "WHERE Impressions > 0 " + 
    'AND CampaignName IN ["' + campaignNames.join('","') + '"] ' +
    "DURING " + dateRange
    );
  var campaignRows = campaignReport.rows();
  while (campaignRows.hasNext()) {
    var row = campaignRows.next();
    if (campaignIds.indexOf(row["CampaignId"]) < 0) {
      campaignIds.push(row["CampaignId"]);
    }
    if (keywords[row["CampaignName"]][row["AdGroupName"]] != undefined) {
      adGroupIds.push(row["AdGroupId"]);
    }
  }//end while
  
  if (adGroupIds.length == 0) {
    Logger.log("Could not find any ad groups with impressions that matched the given names.");
    return;
  }
  Logger.log("Found " + adGroupIds.length + " ad groups in " + campaignIds.length + " campaign(s) with impressions that matched the given names.");
  
  // Initialise the arrays for each campaign, and sorts the keywords from longest to shortest
  var negativeQueries = {}; // Contains the queries
  var exactNegatives = {};  // Contains any negatives to add with exact match
  var phraseNegatives = {}; // Contains any negatives to add with phrase match
  for (var campaignName in keywords) {
    negativeQueries[campaignName] = {};
    exactNegatives[campaignName] = {};
    phraseNegatives[campaignName] = {};
    for (var adGroupName in keywords[campaignName]) {
      negativeQueries[campaignName][adGroupName] = [];
      exactNegatives[campaignName][adGroupName] = [];
      phraseNegatives[campaignName][adGroupName] = [];
      keywords[campaignName][adGroupName].sort(function(a,b) {return b.length - a.length;});
    }
  }
  
  // Get the queries that don't exactly match keywords
  var report = AdWordsApp.report(
    "SELECT Query, AdGroupId, CampaignId, CampaignName, AdGroupName, Impressions " +
    "FROM SEARCH_QUERY_PERFORMANCE_REPORT " +
    "WHERE AdGroupId IN [" + adGroupIds.join(",") + "] " +
    "AND Impressions > " + impressionThreshold + " " +
      "DURING " + dateRange);
  var rows = report.rows();
  var numberQueries = 0;
  while (rows.hasNext()) {
    var row = rows.next();
    var query = row["Query"].toLowerCase().replace(/[^\w\s\d&]/g," ").replace(/ +/g," ").trim();
    var campaignName = row["CampaignName"];
    var adGroupName = row["AdGroupName"];
    if (keywords[campaignName][adGroupName].indexOf(query) < 0) {
      negativeQueries[campaignName][adGroupName].push(query);
      numberQueries++;
    }
  }
  
  // Process queries
  Logger.log("Processing " + numberQueries + " queries that do not match any keywords.");
  var numberExactNegatives = 0;
  var numberPotentialPhraseNegatives = 0;
  for (var campaignName in negativeQueries) {
    for (var adGroupName in negativeQueries[campaignName]) {
      for (var i=0; i<negativeQueries[campaignName][adGroupName].length; i++) {
        var query = negativeQueries[campaignName][adGroupName][i];
        var queryDone = false;
        
        // If the query is contained within a keyword, it has to be an exact match negative
        if (isStringInsideKeywords(query, keywords[campaignName][adGroupName])) {
          exactNegatives[campaignName][adGroupName].push(query);
          numberExactNegatives++;
          continue;
        }
        
        // Check each word (that's over 4 characters) in the query - if it's not in the words array
        // then it isn't in the keywords, so it's fine to use as a phrase negative
        var queryWords = query.split(" ");
        for (var w=0; w<queryWords.length; w++) {
          if (queryWords[w].length > 4) {
            if (words[campaignName][adGroupName][queryWords[w]] == undefined) {
              phraseNegatives[campaignName][adGroupName].push(queryWords[w]);
              queryDone = true;
              break;
            }
          }
        }
        
        // Check if there is a keyword inside the query. If there is, see if the part of the query before
        // or after the keyword could be used as a phrase negative.
        for (var k=0; k<keywords[campaignName][adGroupName].length && !queryDone; k++) {
          var keyword = keywords[campaignName][adGroupName][k];
          if ((" " + query + " ").indexOf(" " + keyword + " ") > -1) {
            var queryBits = (" " + query + " ").split(" " + keyword + " ");
			queryBits[0] = queryBits[0].trim();
			queryBits[1] = queryBits[1].trim();
            if (queryBits[0].length > 0 && !isStringInsideKeywords(queryBits[0], keywords[campaignName][adGroupName])) {
              phraseNegatives[campaignName][adGroupName].push(queryBits[0]);
              queryDone = true;
              break;
            }
            if (queryBits[1].length > 0 && !isStringInsideKeywords(queryBits[1], keywords[campaignName][adGroupName])) {
              phraseNegatives[campaignName][adGroupName].push(queryBits[1]);
              queryDone = true;
              break;
            }
          }
        }
        
        // If nothing smaller than the full query would work, then add the full query as a negative
        if (!queryDone) {
          phraseNegatives[campaignName][adGroupName].push(query);
        }
        numberPotentialPhraseNegatives++;
      }
    }
  }
  Logger.log("Found " + numberPotentialPhraseNegatives + " potential phrase match negatives and " + numberExactNegatives + " exact match negatives.");
  
  // Remove any redundant phrase negatives
  Logger.log("Checking for redundant negatives.");
  var numberPhraseNegatives = 0;
  for (var campaignName in negativeQueries) {
    for (var adGroupName in negativeQueries[campaignName]) {
      // Order the phrases from shortest to longest
      phraseNegatives[campaignName][adGroupName].sort(function(a,b) {return a.length - b.length;});
      
      for (var i=0; i<phraseNegatives[campaignName][adGroupName].length; i++) {
        var shorterPhrase = " " + phraseNegatives[campaignName][adGroupName][i] + " ";
        
        // As the array is now ordered, any phrase negatives with higher indices must be longer than shorterPhrase
        for (var j=i+1; j<phraseNegatives[campaignName][adGroupName].length; j++) {
          var longerPhrase = " " + phraseNegatives[campaignName][adGroupName][j] + " ";
          
          // If the shorterPhrase is within the longerPhrase, then the longerPhrase is redundant
          // so it is removed from the array. This also means duplicates are removed.
          if (longerPhrase.indexOf(shorterPhrase) > -1) {
            phraseNegatives[campaignName][adGroupName].splice(j,1);
            j--;
          }
        } 
      }
      numberPhraseNegatives += phraseNegatives[campaignName][adGroupName].length;
    }
  }
  Logger.log("Going to create " + numberPhraseNegatives + " phrase match negatives and " + numberExactNegatives + " exact match negatives");
  
  // Iterate through the Shopping ad groups and add the negative keywords
  var groupIterator = AdWordsApp.shoppingAdGroups()
  .withIds(adGroupIds)
  .get()
  
  while (groupIterator.hasNext()) {
    var adGroup = groupIterator.next();
    var adGroupName = adGroup.getName();
    var campaignName = adGroup.getCampaign().getName();
    
    for (var i=0; i<exactNegatives[campaignName][adGroupName].length; i++) {
      adGroup.createNegativeKeyword("[" + exactNegatives[campaignName][adGroupName][i] + "]");
    }
    
    for (var i=0; i<phraseNegatives[campaignName][adGroupName].length; i++) {
      adGroup.createNegativeKeyword('"' + phraseNegatives[campaignName][adGroupName][i] + '"');
    }
  }
  
  Logger.log("Finished.");
} // end main function


// Check if a word is a substring of any strings in the keywords array
function isStringInsideKeywords(word, keywords) {
  for (var k=0; k<keywords.length; k++) {
    var keyword = " " + keywords[k] + " ";
    if (keyword.indexOf(" " + word + " ") > -1) {
      return true;
    }
  }
  return false;
}