Sorry Google, we’ve written a script to reverse your ‘Close Variant Matching’

Sorry Google, we’ve written a script to reverse your ‘Close Variant Matching’

Google 0 – 1 Brainlabs

You may have heard that Google are removing exact match at the end of September. They’re telling us that it will give us “control with less complexity” which is really patronising for those sophisticated advertisers who have built systems and workflows around precise match types. It’s just a way of collecting more cash. People are going bonkers about it – there’s even a petition to make Google reverse this decision.

But instead of moaning and waiting for a change that will never come, we’ve written a script to reverse all this “close variant matching”. If you’ve not run a script before please read our Introduction to AdWords Scripts. For more open-source fun check out our AdWords Scripts directory. And if you want to use some of our technology that’s arrived back from the future then sign up for one of our paid plans on the Brainlabs Tech Stack.

 

/**
 *
 * Adds as campaign or AdGroup negatives search queries which have triggered exact keywords
 * Version: 1.1
 * Updated: 2015-10-26
 * Authors: Visar Shabi & Daniel Gilbert
 * brainlabsdigital.com
 *
 **/
function main() {

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

  //Choose whether to add your negative exact keywords at campaign or AdGroup level.
  //Set variable as "true" to add or as "false" to not add.
  var AddAdGroupNegative = true;  // true or false
  var AddCampaignNegative = true; // true of false

  //Parameters for filtering by campaign name and AdGroup name. The filtering is case insensitive.
  //Leave blank, i.e. "", if you want this script to run over all campaigns and AdGroups.
  var campaignNameContains = "";
  var adGroupNameContains = "";

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

  var campaigns = {};
  var adGroups = {};

  var exactKeywords = [];

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

  var report = AdWordsApp.report(
    "SELECT AdGroupId, Id " +
    "FROM KEYWORDS_PERFORMANCE_REPORT " +
    "WHERE Impressions > 0 AND KeywordMatchType = EXACT " +
    "DURING LAST_7_DAYS");

  var rows = report.rows();
  while (rows.hasNext()) {
    var row = rows.next();
    var keywordId = row['Id'];
    var adGroupId = row['AdGroupId'];
    exactKeywords.push(adGroupId + "#" + keywordId);
  }

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

  var report = AdWordsApp.report(
    "SELECT Query, AdGroupId, CampaignId, KeywordId, KeywordTextMatchingQuery, Impressions, QueryMatchTypeWithVariant " +
    "FROM SEARCH_QUERY_PERFORMANCE_REPORT " +
    "WHERE CampaignName CONTAINS_IGNORE_CASE '" + campaignNameContains + "' " +
    "AND AdGroupName CONTAINS_IGNORE_CASE  '" + adGroupNameContains + "' " +
    "DURING LAST_7_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();
    var keyword = row['KeywordTextMatchingQuery'].toLowerCase();
    var matchType = row['QueryMatchTypeWithVariant'].toLowerCase();
    if(keyword !== searchQuery && matchType.indexOf("exact (close variant)") !== -1){

      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 campaignIds = [];
  var adGroupNegatives = [];
  var campaignNegatives = [];

  for(var x in campaigns){
    campaignIds.push(parseInt(x));
    campaignNegatives.push([]);
    for(var y = 0; y < campaigns[x][0].length; y++){
      var keywordId = campaigns[x][1][y];
      var keywordText = campaigns[x][0][y];
      if(exactKeywords.indexOf(keywordId) !== -1){
        campaignNegatives[campaignIds.indexOf(parseInt(x))].push(keywordText);
      }
    }
  }

  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 campaignResults = {};
  var adGroupResults = {};

  if(AddCampaignNegative){
    var campaignIterator = AdWordsApp.campaigns()
    .withIds(campaignIds)
    .get();
    while(campaignIterator.hasNext()){
      var campaign = campaignIterator.next();
      var campaignId = campaign.getId();
      var campaignName = campaign.getName();
      var campaignIndex = campaignIds.indexOf(campaignId);
      for(var i = 0; i < campaignNegatives[campaignIndex].length; i++){
        campaign.createNegativeKeyword("[" + campaignNegatives[campaignIndex][i] + "]")
        if(!campaignResults.hasOwnProperty(campaignName)){
          campaignResults[campaignName] = [];
        }
        campaignResults[campaignName].push(campaignNegatives[campaignIndex][i]);
      }
    }
  }

  if(AddAdGroupNegative){
    var adGroupIterator = AdWordsApp.adGroups()
    .withIds(adGroupIds)
    .get();
    while(adGroupIterator.hasNext()){
      var adGroup = adGroupIterator.next();
      var adGroupId = adGroup.getId();
      var adGroupName = adGroup.getName();
      var adGroupIndex = adGroupIds.indexOf(adGroupId);
      for(var i = 0; i < adGroupNegatives[adGroupIndex].length; i++){
        adGroup.createNegativeKeyword("[" + adGroupNegatives[adGroupIndex][i] + "]");
        if(!adGroupResults.hasOwnProperty(adGroupName)){
          adGroupResults[adGroupName] = [];
        }
        adGroupResults[adGroupName].push(adGroupNegatives[adGroupIndex][i]);
      }
    }
  }

  //~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
  //Format the results

  var resultsString = "The following negative keywords have been added to the following campaigns:";

  for(var x in campaignResults){
    resultsString += "\n\n" + x + ":\n" + campaignResults[x].join("\n");
  }

  resultsString += "\n\n\n\nThe following negative keywords have been added to the following AdGroups:";

  for(var x in adGroupResults){
    resultsString += "\n\n" + x + ":\n" + adGroupResults[x].join("\n");
  }

  Logger.log(resultsString);

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

}

Share this post