Created
June 8, 2018 15:27
-
-
Save BrainlabsDigital/a4c335883bb64aa670106a3cb9df0c87 to your computer and use it in GitHub Desktop.
Script to find potential negatives based on search queries that contain an existing phrase negative.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/** | |
* | |
* Expanding Phrase Match Negatives | |
* | |
* This script searches for all occurrences of phrase match negatives in search | |
* queries resulting from typos. Outputs a report to a Google Doc spreadsheet. | |
* | |
* Version: 1.0 | |
* Google AdWords Script maintained on brainlabsdigital.com | |
* | |
**/ | |
function main() { | |
////////////////////////////////////////////////////////////////////////////// | |
// Options | |
var phraseMatchNegative = "phrase match negative"; | |
// The phrase match negative that you would like to analyse | |
var useCaseSensitiveSearch = false; | |
// Only compare search queries where the case matches | |
// e.g. If set to true: For the phrase 'Free', 'freeman' would be ignored. | |
var startDate = '2018-01-01'; | |
var endDate = '2018-01-31'; | |
// The start and end date of the date range for your search query data | |
// Format is yyyy-mm-dd | |
var currencySymbol = "£"; | |
// The currency symbol used for formatting. For example "£", "$" or "€". | |
var campaignNameDoesNotContain = []; | |
// Use this if you want to exclude some campaigns. | |
// For example ["Display"] would ignore any campaigns with 'Display' in the name, | |
// while ["Display","Shopping"] would ignore any campaigns with 'Display' or | |
// 'Shopping' in the name. | |
// Leave as [] to not exclude any campaigns. | |
var campaignNameContains = []; | |
// Use this if you only want to look at some campaigns. | |
// For example ["Brand"] would only look at campaigns with 'Brand' in the name, | |
// while ["Brand","Generic"] would only look at campaigns with 'Brand' or 'Generic' | |
// in the name. | |
// Leave as [] to include all campaigns. | |
var ignorePausedCampaigns = true; | |
// Set this to true to only look at currently active campaigns. | |
// Set to false to include campaigns that had impressions but are currently paused. | |
var ignorePausedAdGroups = true; | |
// Set this to true to only look at currently active ad groups. | |
// Set to false to include ad groups that had impressions but are currently paused. | |
var spreadsheetUrl = "https://docs.google.com/YOUR-SPREADSHEET-URL-HERE"; | |
// The URL of the Google Doc the results will be put into. | |
var clearSpreadsheet = true; | |
// If true, data already in the spreadsheet will be overwritten. | |
////////////////////////////////////////////////////////////////////////////// | |
// Thresholds | |
var impressionThreshold = 10; | |
var clickThreshold = 0; | |
var costThreshold = 0; | |
var conversionThreshold = 0; | |
// Suggested negatives will not be reported for your phrase match negative if their statistics | |
// are lower than any of these thresholds | |
////////////////////////////////////////////////////////////////////////////// | |
// The actual code starts here | |
var spreadsheet = checkSpreadsheet(spreadsheetUrl, 'the spreadsheet'); | |
var campaignSheet = makeSheet(spreadsheet, 'Campaign Phrase Analysis'); | |
var accountSheet = makeSheet(spreadsheet, 'Account Phrase Analysis'); | |
// Get the IDs of the campaigns to look at | |
var activeCampaignIds = getCampaignIds(campaignNameDoesNotContain, campaignNameContains, ignorePausedCampaigns); | |
////////////////////////////////////////////////////////////////////////////// | |
// Define the statistics to download or calculate, and their formatting | |
var statColumns = ["Clicks", "Impressions", "Cost", "Conversions", "ConversionValue"]; | |
var calculatedStats = [["CTR","Clicks","Impressions"], | |
["CPC","Cost","Clicks"], | |
["Conv. Rate","Conversions","Clicks"], | |
["Cost / conv.","Cost","Conversions"], | |
["Conv. value/cost","ConversionValue","Cost"]]; | |
var currencyFormat = currencySymbol + "#,##0.00"; | |
var formatting = ["#,##0", "#,##0", "#,##0", currencyFormat, "#,##0", currencyFormat,"0.00%",currencyFormat,"0.00%",currencyFormat,"0.00%"]; | |
////////////////////////////////////////////////////////////////////////////// | |
// Go through the search query report, record the performance of each | |
// occurrence of phrase match negative | |
var dateRange = startDate.replace(/-/g, "") + "," + endDate.replace(/-/g, ""); | |
if(startDate == '' || endDate == ''){ | |
throw ("Problem with date range. Start Date and End Date cannot be blank"); | |
} | |
if (ignorePausedAdGroups) { | |
var whereAdGroupStatus = "AND AdGroupStatus = ENABLED "; | |
} else { | |
var whereAdGroupStatus = "AND AdGroupStatus IN ['ENABLED','PAUSED'] "; | |
} | |
var queryReport = AdWordsApp.report( | |
"SELECT CampaignName, CampaignId, AdGroupId, AdGroupName, Query, MonthOfYear, " + statColumns.join(", ") + " " + | |
"FROM SEARCH_QUERY_PERFORMANCE_REPORT " + | |
"WHERE CampaignId IN [" + activeCampaignIds.join(",") + "] " + whereAdGroupStatus + | |
"DURING " + dateRange); | |
var suggestedNegativesByCampaign = {}; | |
var suggestedNegativesByAccount = {}; | |
phraseMatchNegative = phraseMatchNegative.trim().toLowerCase(); | |
var queryRows = queryReport.rows(); | |
if(!queryRows.hasNext()) { | |
throw('The search query report has returned empty. Consider adjusting your options.') | |
} | |
while (queryRows.hasNext()) { | |
queryRow = queryRows.next(); | |
var query = queryRow["Query"]; | |
if(query.indexOf(phraseMatchNegative) > -1) { | |
var matchedPhrase = extractSubstringContainingPhraseMatchNegative(query, phraseMatchNegative); | |
if(suggestedNegativesByCampaign[matchedPhrase] == undefined) { | |
suggestedNegativesByAccount[matchedPhrase] = {}; | |
suggestedNegativesByAccount[matchedPhrase]['Query Count'] = 1; | |
suggestedNegativesByCampaign[matchedPhrase] = {}; | |
suggestedNegativesByCampaign[matchedPhrase][queryRow['CampaignName']] = {}; | |
suggestedNegativesByCampaign[matchedPhrase][queryRow['CampaignName']]['Query Count'] = 1; | |
for (var s=0; s<statColumns.length; s++) { | |
suggestedNegativesByCampaign[matchedPhrase][queryRow['CampaignName']][statColumns[s]] = parseFloat(queryRow[statColumns[s]]); | |
suggestedNegativesByAccount[matchedPhrase][statColumns[s]] = parseFloat(queryRow[statColumns[s]]); | |
} | |
}else { | |
if(suggestedNegativesByCampaign[matchedPhrase][queryRow['CampaignName']] == undefined) { | |
suggestedNegativesByCampaign[matchedPhrase][queryRow['CampaignName']] = {}; | |
suggestedNegativesByCampaign[matchedPhrase][queryRow['CampaignName']]['Query Count'] = 1; | |
suggestedNegativesByAccount[matchedPhrase]['Query Count'] += 1; | |
for (var s=0; s<statColumns.length; s++) { | |
suggestedNegativesByCampaign[matchedPhrase][queryRow['CampaignName']][statColumns[s]] = parseFloat(queryRow[statColumns[s]]); | |
suggestedNegativesByAccount[matchedPhrase][statColumns[s]] += parseFloat(queryRow[statColumns[s]]); | |
} | |
}else { | |
suggestedNegativesByCampaign[matchedPhrase][queryRow['CampaignName']]['Query Count'] += 1; | |
suggestedNegativesByAccount[matchedPhrase]['Query Count'] += 1; | |
for (var s=0; s<statColumns.length; s++) { | |
suggestedNegativesByCampaign[matchedPhrase][queryRow['CampaignName']][statColumns[s]] += parseFloat(queryRow[statColumns[s]]); | |
suggestedNegativesByAccount[matchedPhrase][statColumns[s]] += parseFloat(queryRow[statColumns[s]]); | |
} | |
} | |
} | |
} | |
} | |
Logger.log("Finished analysing queries."); | |
////////////////////////////////////////////////////////////////////////////// | |
// Output the data into the spreadsheet | |
var campaignOutputs = []; | |
var campaignFormats = []; | |
var accountOutputs = []; | |
var accountFormats = []; | |
var campaignHeaders = ['Suggested Negative', 'Campaign', 'Query Count'].concat(statColumns).concat(calculatedStats.map(function(x){return x[0];})); | |
var accountHeaders = ['Suggested Negative', 'Query Count'].concat(statColumns).concat(calculatedStats.map(function(x){return x[0];})); | |
campaignOutputs.push(campaignHeaders); | |
accountOutputs.push(accountHeaders); | |
for(var keyword in suggestedNegativesByCampaign) { | |
var accountOutputRow = [keyword]; | |
for(var campaign in suggestedNegativesByCampaign[keyword] ) { | |
var campaignOutputRow = [keyword]; | |
if(suggestedNegativesByCampaign[keyword][campaign]['Impressions'] < impressionThreshold) {continue;} | |
if(suggestedNegativesByCampaign[keyword][campaign]['Clicks'] < clickThreshold) {continue;} | |
if(suggestedNegativesByCampaign[keyword][campaign]['Cost'] < costThreshold) {continue;} | |
if(suggestedNegativesByCampaign[keyword][campaign]['Conversions'] < conversionThreshold) {continue;} | |
campaignOutputRow.push(campaign); | |
campaignOutputRow.push(suggestedNegativesByCampaign[keyword][campaign]['Query Count']); | |
for (var s=0; s<statColumns.length; s++) { | |
campaignOutputRow.push(suggestedNegativesByCampaign[keyword][campaign][statColumns[s]]); | |
} | |
for (var s in calculatedStats) { | |
var cs = calculatedStats[s]; | |
if(suggestedNegativesByCampaign[keyword][campaign][cs[2]] != 0) { | |
campaignOutputRow.push(suggestedNegativesByCampaign[keyword][campaign][cs[1]]/suggestedNegativesByCampaign[keyword][campaign][cs[2]]) | |
} else { | |
campaignOutputRow.push('-'); | |
} | |
} | |
campaignOutputs.push(campaignOutputRow); | |
campaignFormats.push(["0", "0"].concat(formatting)); | |
} | |
if(suggestedNegativesByAccount[keyword]['Impressions'] < impressionThreshold) {continue;} | |
if(suggestedNegativesByAccount[keyword]['Clicks'] < clickThreshold) {continue;} | |
if(suggestedNegativesByAccount[keyword]['Cost'] < costThreshold) {continue;} | |
if(suggestedNegativesByAccount[keyword]['Conversions'] < conversionThreshold) {continue;} | |
accountOutputRow.push(suggestedNegativesByAccount[keyword]['Query Count']); | |
for (var s=0; s<statColumns.length; s++) { | |
accountOutputRow.push(suggestedNegativesByAccount[keyword][statColumns[s]]); | |
} | |
for (var s in calculatedStats) { | |
var cs = calculatedStats[s]; | |
if(suggestedNegativesByCampaign[keyword][campaign][cs[2]] != 0) { | |
accountOutputRow.push(suggestedNegativesByAccount[keyword][cs[1]]/suggestedNegativesByAccount[keyword][cs[2]]) | |
} else { | |
accountOutputRow.push('-'); | |
} | |
} | |
accountOutputs.push(accountOutputRow); | |
accountFormats.push(["0"].concat(formatting)); | |
} | |
var filterText = ""; | |
if (ignorePausedAdGroups) { | |
filterText = "Active ad groups"; | |
} else { | |
filterText = "All ad groups"; | |
} | |
if (ignorePausedCampaigns) { | |
filterText += " in active campaigns"; | |
} else { | |
filterText += " in all campaigns"; | |
} | |
if (campaignNameContains != "") { | |
filterText += " containing '" + campaignNameContains + "'"; | |
if (campaignNameDoesNotContain != "") { | |
filterText += " and not containing '" + campaignNameDoesNotContain + "'"; | |
} | |
} else if (campaignNameDoesNotContain != "") { | |
filterText += " not containing '" + campaignNameDoesNotContain + "'"; | |
} | |
writeOutput(campaignOutputs, campaignFormats, campaignSheet, phraseMatchNegative, filterText, clearSpreadsheet); | |
writeOutput(accountOutputs, accountFormats, accountSheet, phraseMatchNegative, filterText, clearSpreadsheet); | |
Logger.log("Finished writing to spreadsheet.") | |
} // end main function | |
function makeSheet(spreadsheet, sheetName) { | |
var sheet = spreadsheet.getSheetByName(sheetName); | |
if (sheet == undefined) { | |
sheet = spreadsheet.insertSheet(sheetName); | |
} | |
return sheet; | |
} | |
// Check the spreadsheet URL has been entered, and that it works | |
function checkSpreadsheet(spreadsheetUrl, spreadsheetName) { | |
if (spreadsheetUrl.replace(/[AEIOU]/g,"X") == "https://docs.google.com/YXXR-SPRXXDSHXXT-XRL-HXRX") { | |
throw("Problem with " + spreadsheetName + " URL: make sure you've replaced the default with a valid spreadsheet URL."); | |
} | |
try { | |
var spreadsheet = SpreadsheetApp.openByUrl(spreadsheetUrl); | |
// Checks if you can edit the spreadsheet | |
var sheet = spreadsheet.getSheets()[0]; | |
var sheetName = sheet.getName(); | |
sheet.setName(sheetName); | |
return spreadsheet; | |
} catch (e) { | |
throw("Problem with " + spreadsheetName + " URL: '" + e + "'"); | |
} | |
} | |
// Get the IDs of campaigns which match the given options | |
function getCampaignIds(campaignNameDoesNotContain, campaignNameContains, ignorePausedCampaigns) { | |
var whereStatement = "WHERE "; | |
var whereStatementsArray = []; | |
var campaignIds = []; | |
if (ignorePausedCampaigns) { | |
whereStatement += "CampaignStatus = ENABLED "; | |
} else { | |
whereStatement += "CampaignStatus IN ['ENABLED','PAUSED'] "; | |
} | |
for (var i=0; i<campaignNameDoesNotContain.length; i++) { | |
whereStatement += "AND CampaignName DOES_NOT_CONTAIN_IGNORE_CASE '" + campaignNameDoesNotContain[i].replace(/"/g,'\\\"') + "' "; | |
} | |
if (campaignNameContains.length == 0) { | |
whereStatementsArray = [whereStatement]; | |
} else { | |
for (var i=0; i<campaignNameContains.length; i++) { | |
whereStatementsArray.push(whereStatement + 'AND CampaignName CONTAINS_IGNORE_CASE "' + campaignNameContains[i].replace(/"/g,'\\\"') + '" '); | |
} | |
} | |
for (var i=0; i<whereStatementsArray.length; i++) { | |
var campaignReport = AdWordsApp.report( | |
"SELECT CampaignId " + | |
"FROM CAMPAIGN_PERFORMANCE_REPORT " + | |
whereStatementsArray[i] + | |
"DURING LAST_30_DAYS"); | |
var rows = campaignReport.rows(); | |
while (rows.hasNext()) { | |
var row = rows.next(); | |
campaignIds.push(row['CampaignId']); | |
} | |
} | |
if (campaignIds.length == 0) { | |
throw("No campaigns found with the given settings."); | |
} | |
Logger.log(campaignIds.length + " campaigns found"); | |
return campaignIds; | |
} | |
function extractSubstringContainingPhraseMatchNegative(query, keyword) { | |
var keywordLength = keyword.length; | |
var queryLength = query.length; | |
var keywordStart = query.indexOf(keyword); | |
var nextSpace = query.indexOf(" ", keywordStart + keywordLength); | |
nextSpace = (nextSpace > -1) ? nextSpace: queryLength; | |
var reverseQuery = reverseString(query); | |
var previousSpace = reverseQuery.indexOf(" ", queryLength - keywordStart - 1); | |
previousSpace = (previousSpace > -1) ? queryLength - previousSpace: 0; | |
return query.substring(previousSpace, nextSpace); | |
} | |
function reverseString(str) { | |
var split = str.split(""); | |
var reverse = split.reverse(); | |
return reverse.join(""); | |
} | |
function writeOutput(outputArray, formatArray, sheet, keyword, filterText, clearSpreadsheet) { | |
if (clearSpreadsheet) { | |
sheet.clear(); | |
} | |
sheet.getRange("R1C1").setValue("Analysis of '" + keyword + "' in Search Query Report."); | |
sheet.getRange("R" + (sheet.getLastRow() + 2) + "C1").setValue(filterText); | |
var lastRow = sheet.getLastRow(); | |
if (formatArray.length == 0) { | |
sheet.getRange("R" + (lastRow + 1) + "C1").setValue("No " + keyword + " found within the thresholds."); | |
} else { | |
sheet.getRange("R" + (lastRow + 1) + "C1:R" + (lastRow+outputArray.length) + "C" + outputArray[0].length).setValues(outputArray); | |
sheet.getRange("R" + (lastRow + 2) + "C1:R" + (lastRow+outputArray.length) + "C" + formatArray[0].length).setNumberFormats(formatArray); | |
var sortByColumns = []; | |
sortByColumns.push({column: 2, ascending: true}); | |
sortByColumns.push({column: outputArray[0].indexOf("Cost") + 1, ascending: false}); | |
sortByColumns.push({column: outputArray[0].indexOf("Impressions") + 1, ascending: false}); | |
sheet.getRange("R" + (lastRow + 2) + "C1:R" + (lastRow+outputArray.length) + "C" + outputArray[0].length).sort(sortByColumns); | |
} | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment