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’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.
In order to make sure this change does not harm the performance of my clients’ accounts i decided to write script to monitor the impact.
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):
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 .
Subscribe to my mailing list to receive more scripts and updates on how to start learning to write your own Google Ads Scripts.
TODOS:
– add email alert when a keyword has a close variant that performs terribly
– add daily account totals to a 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
//
// https://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 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 spreadsheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
var keywordStatsSheet = spreadsheet.getActiveSheet();
keywordStatsSheet.clear();
keywordStatsSheet.setName('Keyword Stats');
if (spreadsheet.getSheetByName('Close Variants') != null) {
Logger.log("CV sheet already exists");
var closeVariantSheet = spreadsheet.getSheetByName('Close Variants');
closeVariantSheet.clear();
} else {
Logger.log("creating CV sheet");
var closeVariantSheet = spreadsheet.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 awql_query="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(awql_query);
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'];
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.KeywordTextMatchingQuery, reportRow.Impressions, reportRow.Clicks, reportRow.Cost, reportRow.Conversions]);
}
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]);
var rangeHeader = sheet.getRange('1:1');
rangeHeader.setFontWeight("bold");
// highlight issues
var range_DiffInCostPerConv = sheet.getRange('E:E');
var range_CVImprShare = sheet.getRange('I:I');
var range_CVClickShare = sheet.getRange('M:M');
var range_CVCostShare = sheet.getRange('Q:Q');
var range_CVConvShare = sheet.getRange('U:U');
var rule1 = SpreadsheetApp.newConditionalFormatRule()
.whenNumberGreaterThan(THRESHOLD)
.setBackground("#FFCC99")
.setRanges([range_DiffInCostPerConv, range_CVImprShare, range_CVClickShare, range_CVCostShare, range_CVConvShare])
.build();
var rules = sheet.getConditionalFormatRules();
rules.push(rule1);
sheet.setConditionalFormatRules(rules);
}
function reportCloseVariants(closeVariantSheet){
// let's build the Close Variant sheet
var closeVariantSheetHeader = [
"Search Term (Close Variant)",
"Keyword",
"Impressions",
"Clicks",
"Cost",
"Conversions"
];
// 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");
}
Subscribe to my mailing list to receive more scripts and updates on how to start learning to write your own Google Ads Scripts.