Exact Match Close Variant Prevention

Overview

...stole this from brainlabsdigital.com :/

BUT it's a great script for automatically adding Exact Match (Close Variant) searches into the Negative Keyword list as a Negative Keyword. So figured I'd throw it in here :)

How does it work?

It looks at the “close variant” search queries in active campaigns from the last 30 days. If a query doesn’t precisely match the exact keyword, that query is added as an ad group negative. You can get an email with a list of the new negatives as well.

Unlike with the older version, you can have more than one keyword per ad group. If particular keywords aren’t set to exact match, the script won’t add those negatives. And if there are multiple exact keywords, the script makes sure the query is different from all of them before excluding it.

There’s also the option to schedule the script to analyze the campaigns without automatically making changes. Then you can just manually review the list of suggested negatives and create them when you’re happy with them.

There are a number of caveats:

• It will only work on smallish accounts (I’d estimate up to 10,000 keywords). If your account is bigger or you have lots of accounts, then you need to do this via the API. If you can’t do that yourself, you might look to a partner.

• It only adds negative keywords in retrospect, i.e., the similar variation has to appear and be identified before it can be excluded. So you’ll rack up some spend while this process occurs.

• You may run into negative keyword limits in a large account.

• If you want to be proactive about cross-contamination, there are technological ways to accomplish this. (For example, we have a module in the Brainlabs Tech Stack for this purpose, but it relies on a clean structure that separates exact and broad keywords, either at the campaign or AdGroup level).

How do I use it?

Now that we’ve covered the caveats, let’s get to the script. To take back control of your exact keywords, make a new Script in Google Ads and copy in the code below. Then, modify the settings below as appropriate for your situation:

1. campaignNameContains is used to filter the campaigns the script looks at. If you only want the script to look at campaigns that have certain words or phrases in the names, put those words or phrases in the square brackets, in double quotes and separated with commas. For example, if campaignNameContains is [“Brand”, “Generic”] then only campaigns with names containing “brand” or “generic” are included.

2. campaignNameDoesNotContain is the same, but for words or phrases in the names of campaigns you want the script to ignore. For example if campaignNameDoesNotContain is [“Misspellings”, “Competitor”] then any campaigns with names containing “misspellings” or “competitor” are ignored.

   • campaignNameContains and campaignNameDoesNotContain are not case-sensitive.

   • Leave them blank, [], to cover all campaigns.

   • If you need to put a double quote into the text of the campaign name for either variable, put a backslash before it.

3. If makeChanges is true, then the script will create the negatives for you. If you’d rather just check through the negatives before making changes, you can set it to false.

4. emailAddresses is where you put your email address, if you want to be sent a CSV of the new negatives (or the negatives that would be made, if makeChanges is false).

5. If you don’t want an email, leave this as “”.

Once you have done a preview run and are happy, you can put the script on a schedule to run weekly or monthly—and keep your account exactly as you want.

Script

/**

*

* Make Exact Match Exact

*

* Adds negatives for any search query that doesn't actually exactly match an exact

* match keyword.

*

* Version: 2.0

* Google AdWords Script maintained on brainlabsdigital.com

*

**/

//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//

//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//

//Options

var campaignNameDoesNotContain = [];

// Use this if you want to exclude some campaigns. Case insensitive.

// For example ["Brand"] would ignore any campaigns with 'brand' in the name,

// while ["Brand","Competitor"] would ignore any campaigns with 'brand' or

// 'competitor' in the name.

// Leave as [] to not exclude any campaigns.

var campaignNameContains = [];

// Use this if you only want to look at some campaigns.  Case insensitive.

// 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.

//Choose whether the negatives are created, or if you just get an email to review

var makeChanges = true;

// These addresses will be emailed when the tool is run, eg "daniel@example.com"

// If there are multiple addresses then separate them with commas, eg "a@a.com, b@b.com"

// Leave as "" to not send any emails

var emailAddresses = "";

//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//

//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//

function main() {

  var campaigns = {};

  var adGroups = {};

  var exactKeywords = [];

  var exactGroupIds = {};

  //~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//

  //Pull a list of all exact match keywords in the account

  var campaignIds = getCampaignIds();

  var report = AdWordsApp.report(

    "SELECT AdGroupId, Id, Criteria " +

    "FROM KEYWORDS_PERFORMANCE_REPORT " +

    "WHERE Impressions > 0 AND KeywordMatchType = EXACT " +

    "AND CampaignId IN [" + campaignIds.join(",") + "] " +

    "AND AdGroupStatus IN [ENABLED, PAUSED] " +

    "AND Status IN [ENABLED, PAUSED] " +

    "DURING LAST_30_DAYS");

  var rows = report.rows();

  while (rows.hasNext()) {

    var row = rows.next();

    var keywordId = row['Id'];

    var adGroupId = row['AdGroupId'];

    exactKeywords.push(adGroupId + "#" + keywordId);

    exactGroupIds[adGroupId] = true;

    if(!adGroups.hasOwnProperty(adGroupId)){

      adGroups[adGroupId] = [[], [], []];

    }

    adGroups[adGroupId][2].push(row['Criteria'].toLowerCase().trim());

  }

  exactGroupIds = Object.keys(exactGroupIds);

  //~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//

  // Remove ad groups with non-exact keywords

  var nonExactGroupIds = {};

  for (var i=0; i<exactGroupIds.length; i+=10000) {

    var exactGroupIdsChunk = exactGroupIds.slice(i, i+10000);

    var report = AdWordsApp.report(

      "SELECT AdGroupId, Id " +

      "FROM KEYWORDS_PERFORMANCE_REPORT " +

      "WHERE KeywordMatchType != EXACT AND IsNegative = FALSE " +

      "AND AdGroupId IN [" + exactGroupIdsChunk.join(",") + "] " +

      "AND Status IN [ENABLED, PAUSED] " +

      "DURING LAST_30_DAYS");

    var rows = report.rows();

    while (rows.hasNext()) {

      var row = rows.next();

      var adGroupId = row['AdGroupId'];

      nonExactGroupIds[adGroupId] = true;

    }

  }

  var onlyExactGroupIds = [];

  for (var i=0; i<exactGroupIds.length; i++) {

    if (nonExactGroupIds[exactGroupIds[i]] == undefined) {

      onlyExactGroupIds.push(exactGroupIds[i]);

    }

  }

  Logger.log(onlyExactGroupIds.length + " ad groups (with only exact keywords) were found.");

  //~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//

  //Pull a list of all exact (close variant) search queries

  for (var i=0; i<onlyExactGroupIds.length; i+=10000) {

    var onlyExactGroupIdsChunk = onlyExactGroupIds.slice(i, i+10000);

    var report = AdWordsApp.report(

      "SELECT Query, AdGroupId, CampaignId, KeywordId, KeywordTextMatchingQuery, Impressions, QueryMatchTypeWithVariant, AdGroupName " +

      "FROM SEARCH_QUERY_PERFORMANCE_REPORT " +

      "WHERE AdGroupId IN [" + onlyExactGroupIdsChunk.join(",") + "] " +

      "DURING LAST_30_DAYS");

    var rows = report.rows();

    while (rows.hasNext()) {

      var row = rows.next();

      var adGroupId = parseInt(row['AdGroupId']);

      var campaignId = parseInt(row['CampaignId']);

      var keywordId = parseInt(row['KeywordId']);

      var searchQuery = row['Query'].toLowerCase().trim();

      var keyword = row['KeywordTextMatchingQuery'].toLowerCase().trim();

      var matchType = row['QueryMatchTypeWithVariant'].toLowerCase().trim();

      if(keyword !== searchQuery && matchType.indexOf("exact (close variant)") !== -1){

        if (adGroups[adGroupId][2].indexOf(searchQuery) > -1) {

          // This query is a positive keyword in the ad group

          // so we don't want to add is as a negative

          continue;

        }

        if(!campaigns.hasOwnProperty(campaignId)){

          campaigns[campaignId] = [[], []];

        }

        campaigns[campaignId][0].push(searchQuery);

        campaigns[campaignId][1].push(adGroupId + "#" + keywordId);

        if(!adGroups.hasOwnProperty(adGroupId)){

          adGroups[adGroupId] = [[], []];

        }

        adGroups[adGroupId][0].push(searchQuery);

        adGroups[adGroupId][1].push(adGroupId + "#" + keywordId);

      }

    }

  }

  //~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//

  //Parse data correctly

  var adGroupIds = [];

  var adGroupNegatives = [];

  for(var x in adGroups){

    adGroupIds.push(parseInt(x));

    adGroupNegatives.push([]);

    for(var y = 0; y < adGroups[x][0].length; y++){

      var keywordId = adGroups[x][1][y];

      var keywordText = adGroups[x][0][y];

      if(exactKeywords.indexOf(keywordId) !== -1){

        adGroupNegatives[adGroupIds.indexOf(parseInt(x))].push(keywordText);

      }

    }

  }

  //~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//

  //Create the new negative exact keywords

  var results = [];

    for (var i=0; i<adGroupIds.length; i+=10000) {

      var adGroupIdsChunk = adGroupIds.slice(i, i+10000);

      var adGroupIterator = AdWordsApp.adGroups()

      .withIds(adGroupIdsChunk)

      .get();

      while(adGroupIterator.hasNext()){

        var adGroup = adGroupIterator.next();

        var adGroupId = adGroup.getId();

        var adGroupName = adGroup.getName();

        var adGroupIndex = adGroupIds.indexOf(adGroupId);

        var campaignName = adGroup.getCampaign().getName();

        for(var j=0; j < adGroupNegatives[adGroupIndex].length; j++){

          if (makeChanges) {

            adGroup.createNegativeKeyword("[" + adGroupNegatives[adGroupIndex][j] + "]");

          }

          results.push([campaignName, adGroupName, adGroupNegatives[adGroupIndex][j]]);

        }

      }

    }

  if (!makeChanges || AdWordsApp.getExecutionInfo().isPreview()) {

    Logger.log(results.length + " new negatives were found.");

  } else {

    Logger.log(results.length + " new negatives were created.");

  }

  //~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//

  //Email the results

  if (emailAddresses == "") {

    Logger.log("No email addresses given - not sending email.");

  } else if(results.length == 0) {

    Logger.log("No changes to email."); 

  } else {

    var attachments = [];

    var headers = ["Campaign", "Ad Group", "Negative"];

    attachments.push(createEscapedCsv([headers].concat(results), "Ad-Group-Negatives.csv"));

    if (!makeChanges || AdWordsApp.getExecutionInfo().isPreview()) {

      var verb = "would be";

    } else {

      var verb = "were";

    }

    var subject = AdWordsApp.currentAccount().getName() + " - Making Exact Match Exact - " + Utilities.formatDate(new Date(), "GMT", "yyyy-MM-dd");

    var body = "Please find attached a list of the " + results.length + " negative keywords that " + verb + " added to your account.";

    var options = {attachments: attachments};

    MailApp.sendEmail(emailAddresses, subject, body, options);

  }

}

//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//

// Prepare an array to be made into a CSV

function createEscapedCsv(array, csvName) {

  var cells = [];

  for (var i=0; i<array.length; i++) {

    var row = [];

    for (var j=0; j<array[i].length; j++) {

      row.push(array[i][j].replace(/"/g,'""'));

    }

    cells.push('"' + row.join('","') + '"');

  }

  return Utilities.newBlob("\ufeff" + cells.join("\n"), 'text/csv', csvName);

}

//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//

// Get the IDs of campaigns which match the given options

function getCampaignIds() {

  var whereStatement = "";

  var whereStatementsArray = [];

  var campaignIds = [];

  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 campaignReport = AdWordsApp.report(

      "SELECT CampaignId " +

      "FROM   CAMPAIGN_PERFORMANCE_REPORT " +

      "WHERE  CampaignStatus = ENABLED " +

      whereStatementsArray[i] +

      "DURING LAST_30_DAYS");

    var rows = campaignReport.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 were found.");

  return campaignIds;

}