Skip to content

Instantly share code, notes, and snippets.

@siliconvallaeys
Last active July 24, 2019 03:11
Show Gist options
  • Save siliconvallaeys/356b031b5d6da0857b43c35c1ef1ab5d to your computer and use it in GitHub Desktop.
Save siliconvallaeys/356b031b5d6da0857b43c35c1ef1ab5d to your computer and use it in GitHub Desktop.
Compare phrase and exact match Google Ads keywords with the close match variants they are shown for
// Report on how close variants relate to your keywords in Google Ads
// Free AdWords Script courtesy of Optmyzr.com
// September 12, 2018
function main() {
// -----------------
// Edit this section with your preferences
// ----------------
var time = 'LAST_30_DAYS';
var reportVersion = 'v201802';
var emailAddresses = 'example@example.com';
var accountManagers = 'example@example.com';
var spreadsheetUrl = 'new';
var includeLevinsthein = 1; // set value to 0 if your script times out. by not adding this score, the script will run faster
// use 1 or none of the following two settings to limit the campaigns analyzed
var campaignNameContains = ""; // this is NOT case sensitive
var campaignNameDoesNotInclude = ""; // this is NOT case sensitive
// -------------------
// Don't make edits after this unless you know how to write scripts
// -------------------
var map = new Array();
// Get Campaign IDs
var campaignsToCheck = new Array();
if(campaignNameDoesNotInclude) {
if(campaignNameDoesNotInclude.indexOf("'") != -1)
{
var doesNotContainString = '"' + campaignNameDoesNotInclude + '"';
} else {
var doesNotContainString = "'" + campaignNameDoesNotInclude + "'";
}
var campaigns = AdWordsApp.campaigns()
.withCondition('Name DOES_NOT_CONTAIN_IGNORE_CASE ' + doesNotContainString)
.withCondition('Status != REMOVED')
.get();
} else if(campaignNameContains) {
if(campaignNameContains.indexOf("'") != -1) {
var containsString = '"' + campaignNameContains + '"';
} else {
var containsString = "'" + campaignNameContains + "'";
}
var campaigns = AdWordsApp.campaigns()
.withCondition('Name CONTAINS_IGNORE_CASE ' + containsString)
.withCondition('Status != REMOVED')
.get();
} else {
var campaigns = AdWordsApp.campaigns()
.withCondition('Status != REMOVED')
.get();
}
while(campaigns.hasNext()) {
var campaign = campaigns.next();
var campaignId = campaign.getId();
Logger.log(campaignId);
campaignsToCheck.push(campaignId);
}
// Keywords
var query =
'SELECT Id, KeywordMatchType, Criteria, Clicks, Impressions, Cost, ConversionValue, Conversions, AveragePosition, Ctr, AverageCpc, AdGroupName, CampaignName, CampaignId, AdGroupId ' +
'FROM KEYWORDS_PERFORMANCE_REPORT ' +
'WHERE Impressions > 0 ' +
'AND CampaignId IN ' + JSON.stringify(campaignsToCheck) + " " +
'DURING ' + time;
var report = AdWordsApp.report(query,{apiVersion: reportVersion});
var rows = report.rows();
while(rows.hasNext()) {
var row = rows.next();
var adGroupId = row['AdGroupId'];
var id = row['Id'];
var criteria = row['Criteria'];
//Logger.log(row['Criteria']);
if(criteria.indexOf('+') != -1) {
var subMatchType = "BMM";
} else {
var subMatchType = row['KeywordMatchType'];
}
var key = adGroupId + "-" + id;
if(!map[key]) {
map[key] = new Object();
map[key].keyword = new Object();
map[key].searchTerms = new Array();
map[key].keyword.criteria = row['Criteria'];
map[key].keyword.clicks = parseInt(row['Clicks'],10);
map[key].keyword.impressions = parseInt(row['Impressions'],10);
map[key].keyword.cost = getFloat(row['Cost']);
map[key].keyword.conversions = getFloat(row['Conversions']);
map[key].keyword.ctr = getFloat(row['Ctr']);
map[key].keyword.averagePosition = getFloat(row['AveragePosition']);
map[key].keyword.averageCpc = getFloat(row['AverageCpc']);
map[key].keyword.conversionValue = getFloat(row['ConversionValue']);
map[key].keyword.matchType = row['KeywordMatchType'];
map[key].keyword.subMatchType = subMatchType;
map[key].keyword.campaignName = row['CampaignName'];
map[key].keyword.adGroupName = row['AdGroupName'];
}
}
Logger.log("");
// Search Terms
var query =
'SELECT KeywordId, KeywordTextMatchingQuery, Query, QueryMatchTypeWithVariant, Clicks, Impressions, Cost, ConversionValue, Conversions, AveragePosition, Ctr, AverageCpc, AdGroupName, CampaignName, CampaignId, AdGroupId ' +
'FROM SEARCH_QUERY_PERFORMANCE_REPORT ' +
'WHERE Impressions > 0 ' +
'AND CampaignId IN ' + JSON.stringify(campaignsToCheck) + " " +
'DURING ' + time;
var report = AdWordsApp.report(query,{apiVersion: reportVersion});
var rows = report.rows();
while(rows.hasNext()) {
var row = rows.next();
var adGroupId = row['AdGroupId'];
var id = row['KeywordId'];
var query = row['Query'];
//Logger.log(query);
var key = adGroupId + "-" + id;
if(!map[key]) {
// most likely a shopping campaign
// Logger.log("query not associated with kw in campaign: " + row['CampaignName']);
} else {
if(!map[key].searchTerms[query]) {
map[key].searchTerms[query] = new Object();
map[key].searchTerms[query].matchTypeVariant = row['QueryMatchTypeWithVariant'];
map[key].searchTerms[query].clicks = parseInt(row['Clicks'],10);
map[key].searchTerms[query].impressions = parseInt(row['Impressions'],10);
map[key].searchTerms[query].cost = getFloat(row['Cost']);
map[key].searchTerms[query].conversions = getFloat(row['Conversions']);
map[key].searchTerms[query].ctr = getFloat(row['Ctr']);
map[key].searchTerms[query].averagePosition = getFloat(row['AveragePosition']);
map[key].searchTerms[query].averageCpc = getFloat(row['AverageCpc']);
map[key].searchTerms[query].conversionValue = getFloat(row['ConversionValue']);
map[key].searchTerms[query].impressions = parseInt(row['Impressions'],10);
map[key].searchTerms[query].campaignName = row['CampaignName'];
}
}
}
// Spreadsheet
var reportDate = new Date();
var dateForFilename = reportDate.yyyymmdd();
if(spreadsheetUrl.toLowerCase().indexOf("new") != -1)
{
var spreadsheet = SpreadsheetApp.create("Keyword Analysis - " + AdWordsApp.currentAccount().getName() + " - " + dateForFilename + " (" + time + ")");
var spreadsheetUrl = spreadsheet.getUrl();
}
var spreadsheet = SpreadsheetApp.openByUrl(spreadsheetUrl);
if(accountManagers && accountManagers!=""){
var accountManagersArray = accountManagers.replace(/\s/g, "").split(",");
spreadsheet.addEditors(accountManagersArray);
}
// Sheet for Keyword Report
//get all sheets except first and delete them and insert new sheets every time to avoid name error
var allSheets = spreadsheet.getSheets();
for(var i=1,len=allSheets.length;i<len;i++){
spreadsheet.deleteSheet(allSheets[i]);
}
allSheets[0].setName("Keywords");
var kwSheet = allSheets[0];
kwSheet.appendRow(["Campaign Name", "Ad Group Name", "Match Type", "Sub Match Type", "Keyword", "Search Term", "Query Match Type with Variant", "Levenshtein distance",
"KW Clicks", "KW Impressions", "KW Cost", "KW CTR", "KW Avg. CPC", "KW Conversions", "KW Conv. Value", "KW Avg Pos.",
"Query Clicks", "Query Impressions", "Query Cost", "Query CTR", "Query Avg. CPC", "Query Conversions", "Query Conv. Value", "Query Avg Pos."]);
kwSheet.setFrozenRows(1);
// Render
for(var key in map) {
//Logger.log(key);
var criteria = map[key].keyword.criteria;
var matchType = map[key].keyword.matchType;
var subMatchType = map[key].keyword.subMatchType;
var kwClicks = map[key].keyword.clicks;
var kwImpressions = map[key].keyword.impressions;
var kwConversions = map[key].keyword.conversions;
var kwCost = map[key].keyword.cost;
var kwCtr = map[key].keyword.ctr;
var kwAverageCpc = map[key].keyword.averageCpc;
var kwConversionValue = map[key].keyword.conversionValue;
var kwAveragePosition = map[key].keyword.averagePosition;
var campaignName = map[key].keyword.campaignName;
var adGroupName = map[key].keyword.adGroupName;
for(var query in map[key].searchTerms) {
var queryClicks = map[key].searchTerms[query].clicks;
var matchTypeVariant = map[key].searchTerms[query].matchTypeVariant;
//Logger.log(criteria + " | " + query + " | " + matchType + " " + matchTypeVariant);
if(matchType.toLowerCase() != matchTypeVariant.toLowerCase()) {
//var difference = getDifference(criteria, query);
//var diffLen = difference.length;
var rawCriteria = criteria.replace(/\+/g,"");
if(includeLevinsthein) {
var diffLen = levDist(rawCriteria, query);
} else {
var diffLen = "";
}
var queryClicks = map[key].searchTerms[query].clicks;
var queryImpressions = map[key].searchTerms[query].impressions;
var queryConversions = map[key].searchTerms[query].conversions;
var queryCost = map[key].searchTerms[query].cost;
var queryCtr = map[key].searchTerms[query].ctr;
var queryAverageCpc = map[key].searchTerms[query].averageCpc;
var queryConversionValue = map[key].searchTerms[query].conversionValue;
var queryAveragePosition = map[key].searchTerms[query].averagePosition;
//Logger.log(criteria + " " + matchType + " " + query + " " + matchTypeVariant + " " + kwClicks + " " + " " + diffLen);
kwSheet.appendRow([campaignName, adGroupName, matchType, subMatchType, "'"+criteria, query, matchTypeVariant, diffLen,
kwClicks, kwImpressions, kwCost, kwCtr, kwAverageCpc, kwConversions, kwConversionValue, kwAveragePosition,
queryClicks, queryImpressions, queryCost, queryCtr, queryAverageCpc, queryConversions, queryConversionValue, queryAveragePosition]);
}
}
}
// Notify
Logger.log(spreadsheetUrl);
var body = "your report is ready at: " + spreadsheetUrl;
MailApp.sendEmail(emailAddresses, "notifications@optmyzr.com", "Your match type analysis is ready", body);
}
// date functions
Date.prototype.yyyymmdd = function() {
var yyyy = this.getFullYear().toString();
var mm = (this.getMonth()+1).toString(); // getMonth() is zero-based
var dd = this.getDate().toString();
return yyyy + (mm[1]?mm:"0"+mm[0]) + (dd[1]?dd:"0"+dd[0]); // padding
};
function getFloat (input) {
if(!input || input == "" || typeof(input) === 'undefined') var input = "0.0";
input = input.toString();
var output = parseFloat(input.replace(/,/g, ""));
return output;
}
// Function: levDist
// Author James Westgate (https://stackoverflow.com/users/305319/james-westgate)
// Source: https://stackoverflow.com/questions/11919065/sort-an-array-by-the-levenshtein-distance-with-best-performance-in-javascript/11958496#11958496
// License: CC-BY-SA (https://creativecommons.org/licenses/by-sa/4.0/)
function levDist(s, t) {
var d = []; //2d matrix
// Step 1
var n = s.length;
var m = t.length;
if (n == 0) return m;
if (m == 0) return n;
//Create an array of arrays in javascript (a descending loop is quicker)
for (var i = n; i >= 0; i--) d[i] = [];
// Step 2
for (var i = n; i >= 0; i--) d[i][0] = i;
for (var j = m; j >= 0; j--) d[0][j] = j;
// Step 3
for (var i = 1; i <= n; i++) {
var s_i = s.charAt(i - 1);
// Step 4
for (var j = 1; j <= m; j++) {
//Check the jagged ld total so far
if (i == j && d[i][j] > 4) return n;
var t_j = t.charAt(j - 1);
var cost = (s_i == t_j) ? 0 : 1; // Step 5
//Calculate the minimum
var mi = d[i - 1][j] + 1;
var b = d[i][j - 1] + 1;
var c = d[i - 1][j - 1] + cost;
if (b < mi) mi = b;
if (c < mi) mi = c;
d[i][j] = mi; // Step 6
//Damerau transposition
if (i > 1 && j > 1 && s_i == t.charAt(j - 2) && s.charAt(i - 2) == t_j) {
d[i][j] = Math.min(d[i][j], d[i - 2][j - 2] + cost);
}
}
}
// Step 7
return d[n][m];
}
@Fabmacintosh
Copy link

Hi, it gives me an error
Report version V201802 is not supported. Only versions V201806,V201809 are supported. (file Code.gs, line 73) =( changing the version does not help (it never finishes)

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment