Effortlessly Monitor Close Variants with this Google Ads Script

UPDATES:

2018, Oct 6: Added Close Variant search terms to seperate sheet.

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.5
*
* CHANGES:
* Version 1.5: added sheet with the close variants
* 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 email alert when a keyword has a close variant that performs terribly
* - add daily account totals to seperate sheet
*/

var SPREADSHEET_URL = "[INSERT YOUR SPREADSHEET URL HERE]";  //insert a new blank spreadsheet url
var CAMPAIGNLABEL = ""; // Leave blank for all campaigns 

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

var KEYWORD_STATS = []; // array with calculated stats for keywords that have close variants
var CLOSE_VARIANTS = []; // array with close variants and metrics

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

  // let's prepare the sheet
  var ss = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
  var keywordStatsSheet = ss.getActiveSheet();
  keywordStatsSheet.clear();
  keywordStatsSheet.setName('Keyword Stats');
  if (ss.getSheetByName('Close Variants') != null) {
    Logger.log("CV sheet already exists");
  	var closeVariantSheet = ss.getSheetByName('Close Variants');
  	closeVariantSheet.clear();
  } else {
    Logger.log("creating CV sheet");
    var closeVariantSheet = ss.insertSheet('Close Variants');
  }
  
  
  // let's get the list of campaings to check
  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 and get the stats
  checkCloseVariants(campaignIds);

  Logger.log("Nr of KWs: "+KEYWORD_STATS.length);
  Logger.log("Nr of CVs: "+CLOSE_VARIANTS.length);

  reportKeywordStats(keywordStatsSheet);

  reportCloseVariants(closeVariantSheet);
  
}


function checkCloseVariants(campaignIds) {

  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 report=AdWordsApp.report(awq);
  var rows = report.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)') { // Query (search term) is a 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']);

        // Query (search term) is a close variant, so let's add it to array with all close variants and their metrics 
        addToArray(row, CLOSE_VARIANTS);
      }
    }
  }

  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)) {
	  KEYWORD_STATS.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)
      ]);
    }
  }
  KEYWORD_STATS.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)
                     ]); 

}


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;  

}

function addToArray(reportRow,array) {
  array.push([reportRow.Query, reportRow.Impressions, reportRow.Clicks, reportRow.Cost, reportRow.Conversions, reportRow.KeywordTextMatchingQuery]);
}


function reportKeywordStats(sheet) {

  // let's build the spreadsheet
  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, KEYWORD_STATS.length, header.length);
  range.setValues(KEYWORD_STATS);
  
  // 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 reportCloseVariants(closeVariantSheet){

  // let's build the Close Variant sheet
  var closeVariantSheetHeader = [
    "Search Term", 
	"Impressions", 
    "Clicks",
    "Cost",
    "Conversions",
    "Keyword"   
  ];

  // write issues to Close Variant sheet
  var range = closeVariantSheet.getRange(1, 1, CLOSE_VARIANTS.length, closeVariantSheetHeader.length);
  range.setValues(CLOSE_VARIANTS);
  
  // sort by Close Variant Impressions desc, add header when sorted
  closeVariantSheet.sort(2, false); 
  closeVariantSheet.insertRows(1);
  range = closeVariantSheet.getRange(1, 1, 1, closeVariantSheetHeader.length);
  range.setValues([closeVariantSheetHeader]);  
  
  var rangeHeader = closeVariantSheet.getRange('1:1');
  rangeHeader.setFontWeight("bold");
}

 

Join my list to automate your way to PPC success

* indicates required


 

Please follow and like us:

Leave a Reply

Your email address will not be published. Required fields are marked *