Effortlessly Monitor Close Variants with this Google Ads Script

UPDATES:

2018, Sep 23: Added Cost metrics, and added column for analyzing the difference in Cost per Conversion between the Exact Match keyword and its Close Variants.

2018, Sep 16: Added Click metrics, and “Alerting Percentage Highlighting” to output.

***

Google Ads Exact Match Close Variants

Google’s exact match close variants will expand again: Google is using it’s “AI” to include same meaning variations, and searches with “same intent”.

According to Google, this change will roll out for English keywords through October, with more languages to follow over the next few months.

So, Google is removing yet another level of control for experienced account managers. Exact Match will no longer be…Exact Match. Google provides itself with a lot more runway to match your exact keywords to whatever the heck they deem relevant.

On Twitter, #ppcchat peepz all flip the F out on Google for limiting our ability to actively manage the accounts. The first and second close variants expansions already showed some pretty horrible search terms in our report. Fear is this will only increase with the latest expansion.

Personally, I doubt it’ll go too nuts unless you have some form of smart bidding on. And yes, Google is pushing that too.

But, one thing is for sure: we’re all in for a lot of new traffic in the near future, and determining the value of that new traffic will require some diligence. Not all matching search terms have equal conversion value and the spread will increase.

Next to the difference in conversion value, there is also the issue of sculpting your account. Last changes to exact match, when spelling and the order of words in exact match (as well as bridge words like “with”, “in”, “near”, etc) became flexible, caused a lot of issues with accounts now having many effectively duplicate keywords.  Again, making exact match management a huge bandwidth suck.

These future changes are going to complicate things even further, as Google will now be adding intent to the flexibility of close variants within exact match keywords.

In order to determine how big of a suck this change really is i decided to write a script to monitor Close Variant and send you alerts if Google goes haywire.

 

This script will check all Exact Match keywords for close variants and will report the percentage of impressions per keyword that are matched by close variants, as well as the number of close variant search terms.
The total is logges as well so you have an idea of how big a portion of the ads is served via close variants.

Here’s what the output will look like (click to enlarge):

Exact Match Close Variant Analyses
Exact Match Close Variant Analyses

Don’t worry if you have never run an Adwords Script before. It is fairly easy if you follow these steps on how to set up and run Google Ads Scripts.

Be sure to create a new empty spreadsheet and add the url to the script (line 32).

The script is work in progress, please post your ideas/requests in the comments below or send an email to nils [at] nilsrooijmans.com .

TODOS:
– add sheet with the close variants
– add email alert when a keyword has a close variant that performs terribly
– add daily account totals to seperate sheet

 

// Copyright 2018, Nils Rooijmans, All Rights Reserved.
//
// Licensed under the Apache License, Version 2.0 (the "License");
// you may not use this file except in compliance with the License.
// You may obtain a copy of the License at
//
//     http://www.apache.org/licenses/LICENSE-2.0
//
// Unless required by applicable law or agreed to in writing, software
// distributed under the License is distributed on an "AS IS" BASIS,
// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
// See the License for the specific language governing permissions and
// limitations under the License.


/**
* @overview:
* For each campaign that has the label CAMPAIGNLABEL this script will check all Exact Match keywords for close variants and will report 
* the percentage of impressions per keyword that are matched by close variants, as well as the number of close variant search terms.
* The total is logges as well so you have an idea of how big a portion of the ads is served via close variants.
*
* @author:  Nils Rooijmans [nils@nilsrooijmans.com]
* @version: 1.4
*
* CHANGES:
* Version 1.4: added column for analysing the difference in Cost per Conversion between EM and CVs
* Version 1.3: added Cost and Conversion metrics to output
* Version 1.2: added alert highlighting in output
* Version 1.1: added Click metrics to output
*
* TODOS:
* - add sheet with the close variants
* - add email alert when a keyword has a close variant that performs terribly
* - add daily account totals to seperate sheet
*/

var SPREADSHEET_URL = "";  //insert a new blank spreadsheet url
var CAMPAIGNLABEL = ""; // Leave blank for all campaigns 

var DATE_RANGE = 'LAST_7_DAYS';
var MATCH_TYPE = '[EXACT, NEAR_EXACT]';

var THRESHOLD = 0.4; // higlight percentages in sheet when above this value
 
function main() {

  // let's get the campaings 
  var campaignSelector = AdWordsApp.campaigns()
       .withCondition("AdvertisingChannelType = SEARCH") // Search campaings only (no display/shopping, ...)    
       .withCondition("CampaignTrialType = BASE") //skip Drafts and Experiments
    	 .withCondition("Name DOES_NOT_CONTAIN_IGNORE_CASE 'DSA'") // skip DSA campaigns
       .withCondition("Status = ENABLED") ;
  
  if (CAMPAIGNLABEL!="") {
    campaignSelector=campaignSelector
       .withCondition("LabelNames CONTAINS_ANY ['"+CAMPAIGNLABEL+"']");
  }
  
   
  var campaignIterator = campaignSelector.get();
  var campaignIds=[] ;
  while (campaignIterator.hasNext()) {
    var campaign = campaignIterator.next();
    campaignIds.push(campaign.getId()) ;
  }
  Logger.log("  NR of campaigns to check: "+campaignIterator.totalNumEntities());


  // Let's check for close variants
  var keywordStats = checkCloseVariants(campaignIds);


  // let's fix the sheet
  var ss = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
  var sheet = ss.getActiveSheet();
  sheet.clear(); //remove earlies alerts
  var header = [
    "Keyword", 
    "Nr of CV Search Terms",
    "EM Cost/Conv",
    "CV Cost/Conv",
    "Diff Cost/Conv",
    "Total Impressions",    
    "EM Impressions",
    "CV Impressions",
    "CV Impression Share",
    "Total Clicks",    
    "EM Clicks",
    "CV Clicks",
    "CV Click Share",    
    "Total Cost",    
    "EM Cost",
    "CV Cost",
    "CV Cost Share",
    "Total Conversions",    
    "EM Conversions",
    "CV Conversions",
    "CV Conversions Share"       
  ];

  // write issues to sheet
  var range = sheet.getRange(1, 1, keywordStats.length, header.length);
  Logger.log("Header length: "+header.length);
  Logger.log("KeywordStats length: "+keywordStats[0].length);
  range.setValues(keywordStats);
  
  // sort by Close Variant Impressions desc, add header when sorted
  sheet.sort(8, false); 
  sheet.insertRows(1);
  range = sheet.getRange(1, 1, 1, header.length);
  range.setValues([header]);
  
  // highlight issues
  var range0 = sheet.getRange('E:E');
  var range1 = sheet.getRange('I:I');
  var range2 = sheet.getRange('M:M');
  var range3 = sheet.getRange('Q:Q');
  var range4 = sheet.getRange('U:U'); 
  var rangeHeader = sheet.getRange('1:1');
  var rule1 = SpreadsheetApp.newConditionalFormatRule()
    .whenNumberGreaterThan(THRESHOLD)
    .setBackground("#FFCC99")
    .setRanges([range0, range1, range2, range3, range4])
    .build();
  var rules = sheet.getConditionalFormatRules();
  rules.push(rule1);
  sheet.setConditionalFormatRules(rules);
  rangeHeader.setFontWeight("bold");
}


function checkCloseVariants(campaignIds) {

  var keywordStats = [];

  var awq="SELECT KeywordTextMatchingQuery, QueryMatchTypeWithVariant, Query, Impressions, Clicks, Cost, Conversions FROM SEARCH_QUERY_PERFORMANCE_REPORT WHERE CampaignId IN ["+campaignIds.join(",")+"] AND QueryMatchTypeWithVariant IN "+MATCH_TYPE+" DURING "+DATE_RANGE;

  var rows=AdWordsApp.report(awq).rows();

  var map = {};

  var totalNrOfCloseVariants = 0;
  var totalImpressionsEM = 0;
  var totalImpressionsCV = 0;
  var totalClicksEM = 0;
  var totalClicksCV = 0;
  var totalCostEM = 0;
  var totalCostCV = 0;  
  var totalConversionsEM = 0;
  var totalConversionsCV = 0;  


  while (rows.hasNext()) {

    var row = rows.next();
    var keyword = row['KeywordTextMatchingQuery'];

    //Logger.log("Search term: "+row['Query']+" --> Match Type: "+row['QueryMatchTypeWithVariant']);

    if( (keyword.indexOf('\"') == -1) && (keyword.indexOf('+') == -1) ) { // make sure the keyword is not MBM or Phrase (somehow KWs of these matchtypes can also generate "exact (close variant)'s" or "exact" matches ?!?    

  	  // build the map per keyword
      if ( !map.hasOwnProperty(keyword) ) {
          map[keyword] = new KeywordStatsObject(keyword);
      }
  
      if(row['QueryMatchTypeWithVariant']=='exact') {
        map[keyword].impressionsEM += numericalize(row['Impressions']);
        map[keyword].clicksEM += numericalize(row['Clicks']);
        map[keyword].costEM += numericalize(row['Cost']);
        map[keyword].conversionsEM += numericalize(row['Conversions']);
        totalImpressionsEM += numericalize(row['Impressions']);
        totalClicksEM += numericalize(row['Clicks']);
        totalCostEM += numericalize(row['Cost']);
        totalConversionsEM += numericalize(row['Conversions']);
      }
  
      if(row['QueryMatchTypeWithVariant']=='exact (close variant)') {
        totalNrOfCloseVariants++;
        map[keyword].nrOfCloseVariants++;
        map[keyword].impressionsCV += numericalize(row['Impressions']);
        map[keyword].clicksCV += numericalize(row['Clicks']);
		map[keyword].costCV += numericalize(row['Cost']);
        map[keyword].conversionsCV += numericalize(row['Conversions']);
        totalImpressionsCV += numericalize(row['Impressions']);
        totalClicksCV += numericalize(row['Clicks']);
        totalCostCV += numericalize(row['Cost']);
        totalConversionsCV += numericalize(row['Conversions']);
      }
    }
  }

  Logger.log("Total NR of Close Variants: "+totalNrOfCloseVariants+"\n");
  Logger.log("Total Real EM Impressions: "+totalImpressionsEM);
  Logger.log("Total Close Variant Impressions: "+totalImpressionsCV);
  Logger.log("Total Real EM Clicks: "+totalClicksEM);
  Logger.log("Total Close Variant Clicks: "+totalClicksCV);
  Logger.log("Total Real EM Cost: "+totalCostEM);
  Logger.log("Total Close Variant Cost: "+totalCostCV);
  Logger.log("Total Real EM Conversions: "+totalConversionsEM);
  Logger.log("Total Close Variant Conversions: "+totalConversionsCV);
  
  
  for (var keyword in map) {
    if (map.hasOwnProperty(keyword)) {
	  keywordStats.push([
        "["+map[keyword].keyword+"]",
        map[keyword].nrOfCloseVariants,
        (map[keyword].costEM/map[keyword].conversionsEM).toFixed(2),
        (map[keyword].costCV/map[keyword].conversionsCV).toFixed(2),
        (( (map[keyword].costCV/map[keyword].conversionsCV)/(map[keyword].costEM/map[keyword].conversionsEM) ) - 1).toFixed(2),
        map[keyword].impressionsCV+map[keyword].impressionsEM,
        map[keyword].impressionsEM,
        map[keyword].impressionsCV,
        (map[keyword].impressionsCV/(map[keyword].impressionsCV+map[keyword].impressionsEM)).toFixed(2),
        map[keyword].clicksCV+map[keyword].clicksEM,
        map[keyword].clicksEM,
        map[keyword].clicksCV,
        (map[keyword].clicksCV/(map[keyword].clicksCV+map[keyword].clicksEM)).toFixed(2),
        map[keyword].costCV+map[keyword].costEM,
        map[keyword].costEM,
        map[keyword].costCV,
        (map[keyword].costCV/(map[keyword].costCV+map[keyword].costEM)).toFixed(2),
        map[keyword].conversionsCV+map[keyword].conversionsEM,
        map[keyword].conversionsEM,
        map[keyword].conversionsCV,
        (map[keyword].conversionsCV/(map[keyword].conversionsCV+map[keyword].conversionsEM)).toFixed(2)
      ]);
    }
  }
  keywordStats.unshift(["_TOTAL_", 
                     totalNrOfCloseVariants,
                     (totalCostEM/totalConversionsEM).toFixed(2),
                     (totalCostCV/totalConversionsCV).toFixed(2),   
                     (( (totalCostCV/totalConversionsCV)/(totalCostEM/totalConversionsEM) ) -1).toFixed(2),   
                     totalImpressionsEM+totalImpressionsCV, totalImpressionsEM, totalImpressionsCV, (totalImpressionsCV/(totalImpressionsEM+totalImpressionsCV)).toFixed(2), 
                     totalClicksEM+totalClicksCV, totalClicksEM, totalClicksCV, (totalClicksCV/(totalClicksEM+totalClicksCV)).toFixed(2), 
                     totalCostEM+totalCostCV, totalCostEM, totalCostCV, (totalCostCV/(totalCostEM+totalCostCV)).toFixed(2),
                     totalConversionsEM+totalConversionsCV, totalConversionsEM, totalConversionsCV, (totalConversionsCV/(totalConversionsEM+totalConversionsCV)).toFixed(2)
                     ]); 

  return keywordStats;
}


function numericalize(string){
  return parseFloat(string.toString().replace(/\,/g, ''));
} 


function KeywordStatsObject(keyword) {
  this.keyword = keyword;
  this.nrOfCloseVariants = 0;
  this.impressionsEM = 0;
  this.impressionsCV = 0;
  this.clicksEM = 0;
  this.clicksCV = 0;
  this.costEM = 0;
  this.costCV = 0;  
  this.conversionsEM = 0;
  this.conversionsCV = 0;  

}

 

Join my list to automate your way to PPC success

* indicates required


 

How to Set Up and Run Adwords Scripts

You KNOW you HAVE TO get started with Adwords scripts to take your skills and Adwords performance to the next level.

You just…haven’t…started…yet.

You’re probably feeling like your manager who is looking at your excel sheets. Where do you even begin?!?

And then there is the Adwords Scripts interface. A bit Intimidating maybe? Confusing, for sure!

Fear no more.

Here’s a short demo on how to actually install and have a script running in Adwords, within 5 minutes!

Continue reading “How to Set Up and Run Adwords Scripts”

How to Make Time to Learn Coding Adwords Scripts

There are lots of excuses people make for not progressing in learning a new skill like coding; I’m not smart enough, it’s too hard, I can’t remember the concepts, I have different priorities now etc. But the ONE EXCUSE I hear the most frequent is…

“Dude, I’m already so busy managing a ton of accounts. I don’t have any time to learn how to write my own Adwords Scripts!”
Continue reading “How to Make Time to Learn Coding Adwords Scripts”

My Frequently Updated List of Resources on Adwords Scripts

Here’s a list of resources to get you started on using Adwords Scripts, and learning to write custom scripts for yourself. I’ll be updating the list on a regular basis if I discover additional valuable resources.

Getting Started Guides

I recommend reading my 3 Ways to Getting Started with Adwords Scripts post first. Continue reading “My Frequently Updated List of Resources on Adwords Scripts”

How Knowing Adwords Scripts will Improve Your Career, For Sure

“How will learning Adwords Scripts progress my career?”

That’s a no brainer. Adwords Scripts increase your earning potential. Whether you’re an employee, freelancer or agency owner. Automating PPC tasks will enable you to dramatically increase your salary, or charge waaaay more $$$ for the same work that (thanks to scripts) requires less time, less people.

Also, using Adwords Scripts you’ll enjoy your work more.

Google Ads Scripts Salary

Continue reading “How Knowing Adwords Scripts will Improve Your Career, For Sure”