Last active
January 14, 2020 03:18
-
-
Save siliconvallaeys/8a5030cae63e34a56ec575cad08ac51a to your computer and use it in GitHub Desktop.
Get aggregate performance data by keyword and search term match type
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
// Create a report in a Google spreadsheet with performance data by keyword match type and search term match type in Google Ads | |
// Free AdWords Script courtesy of Optmyzr.com | |
// October 22, 2018 | |
var DEBUG = 0; | |
function main(){ | |
var currentSetting = new Object(); | |
// -------- User settings: Edit these to customize the functionality | |
var spreadsheetUrl = ''; // leave this blank or enter the URL to a Google spreadsheet to use for the report | |
var accountManagers = 'example@example.com'; // who should be given permission to edit the generated Google spreadsheet | |
var emailAddress = 'example@example.com'; // who should be notified when a report is ready | |
var time = 'LAST_30_DAYS'; // what date range to use for the report | |
var reportVersion = 'v201802'; // what API reporting version to use | |
// -------- End of User Settings. Do not edit anything after this unless you know scripting.---------- | |
var sheetNameForKeywordData = 'Keyword Match Type'; | |
var sheetNameForQueryData = 'Search Term Match Type'; | |
var spreadsheetName = 'Match Type Performance Report'; | |
var overWriteOldData = 1; // if set to '1', old data in the spreadsheet will be overwritten | |
var includeAccountNameInReportName = 1; | |
var includeDateGeneratedInReportName = 1; | |
var includeDateRangeInReportName = 1; | |
if(typeof sheetNameForKeywordData == "undefined") sheetNameForKeywordData = "Keywords"; | |
if(typeof sheetNameForQueryData == "undefined") sheetNameForQueryData = "Queries"; | |
if(typeof spreadsheetName == "undefined") spreadsheetName = "Keyword and Query Type Stats"; | |
if(typeof overWriteOldData == "undefined") overWriteOldData = 1; | |
if(typeof includeAccountNameInReportName == "undefined") includeAccountNameInReportName = 1; | |
if(typeof includeDateGeneratedInReportName == "undefined") includeDateGeneratedInReportName = 1; | |
if(typeof includeDateRangeInReportName == "undefined") includeDateRangeInReportName = 1; | |
var campaignNameIncludes = currentSetting['Campaign_Name_Contains']; | |
var campaignLabel = currentSetting['Campaign_Label']; | |
if(typeof campaignNameIncludes == "undefined") var campaignNameIncludes = ""; | |
if(typeof campaignLabel == "undefined") var campaignLabel = ""; | |
if(campaignLabel) var matchedCampaigns = getCampaignsByLabel(campaignLabel); | |
var campaignNameCaseSensitive = 0; | |
var campaignNameExactMatch = 0; | |
if(campaignNameIncludes) var campaignNameSelectorString = getCampaignsByName(campaignNameIncludes, campaignNameCaseSensitive, campaignNameExactMatch); | |
var accountName = AdWordsApp.currentAccount().getName(); | |
var sheetNames = new Array(sheetNameForKeywordData, sheetNameForQueryData); | |
var currencyCode = AdWordsApp.currentAccount().getCurrencyCode(); | |
// Construct report name | |
var reportDate = new Date(); | |
var dateForFilename = reportDate.yyyymmdd(); | |
if(includeAccountNameInReportName) { | |
spreadsheetName = spreadsheetName + " - " + accountName; | |
} | |
if(includeDateGeneratedInReportName) { | |
spreadsheetName = spreadsheetName + " - " + dateForFilename; | |
} | |
if(includeDateRangeInReportName) { | |
spreadsheetName = spreadsheetName + " (" + time + ")"; | |
} | |
var destinationSpreadsheet = setUpReportInGoogleSheets(spreadsheetUrl, spreadsheetName, accountManagers, overWriteOldData, sheetNames); | |
var spreadsheet = destinationSpreadsheet.spreadsheet; | |
var isSpreadsheetNew = destinationSpreadsheet.isNew; | |
var spreadsheetUrl = destinationSpreadsheet.url; | |
var kwSheet = spreadsheet.getSheetByName(sheetNameForKeywordData); | |
if(destinationSpreadsheet.overWrite) { | |
kwSheet.appendRow(["Start Date", "End Date", "Type", "Clicks", "Impressions", "Cost (" + currencyCode + ")", "CTR", "Avg. CPC (" + currencyCode + ")", "Conversions", "Cost / Conversion", "Conv. Value (" + currencyCode + ")","Conv. Value / Cost", "Avg Pos."]); | |
kwSheet.setFrozenRows(1); | |
} | |
// Sheet for Query report | |
var querySheet = spreadsheet.getSheetByName(sheetNameForQueryData); | |
if(destinationSpreadsheet.overWrite) { | |
querySheet.appendRow(["Start Date", "End Date", "Type", "Clicks", "Impressions", "Cost (" + currencyCode + ")", "CTR", "Avg. CPC (" + currencyCode + ")", "Conversions", "Cost / Conversion", "Conv. Value (" + currencyCode + ")", "Conv. Value / Cost", "Avg Pos."]); | |
querySheet.setFrozenRows(1); | |
} | |
var reportDates = getReportDates(time); | |
var cols = ['KeywordMatchType', | |
'Criteria', | |
'Clicks', | |
'Impressions', | |
'Cost', | |
'ConversionValue', | |
'AveragePosition', | |
'Conversions', | |
'CampaignId', | |
'CampaignName']; | |
var query = ['SELECT ', | |
cols.join(','), | |
'FROM KEYWORDS_PERFORMANCE_REPORT', | |
'WHERE Impressions > 0']; | |
if(campaignLabel) query.push('AND', matchedCampaigns.selectorStringForReports); | |
if(campaignNameIncludes) query.push('AND', campaignNameSelectorString.forReports); | |
query.push('DURING', time); | |
var query = query.join(' '); | |
if(DEBUG == 1) Logger.log("query: " + query); | |
var report = AdWordsApp.report(query,{apiVersion:reportVersion}); | |
var rows = report.rows(); | |
processReport(kwSheet, rows, "Criteria", "KeywordMatchType", reportDates); | |
addStyleToSheet(kwSheet); | |
var cols = ['QueryMatchTypeWithVariant', | |
'Query', | |
'Clicks', | |
'Impressions', | |
'Cost', | |
'ConversionValue', | |
'AveragePosition', | |
'Conversions', | |
'CampaignId', | |
'CampaignName']; | |
var query = ['SELECT ', | |
cols.join(','), | |
'FROM SEARCH_QUERY_PERFORMANCE_REPORT', | |
'WHERE Impressions > 0']; | |
if(campaignLabel) query.push('AND', matchedCampaigns.selectorStringForReports); | |
if(campaignNameIncludes) query.push('AND', campaignNameSelectorString.forReports); | |
query.push('DURING', time); | |
var query = query.join(' '); | |
if(DEBUG == 1) Logger.log("query: " + query); | |
var report = AdWordsApp.report(query,{apiVersion:reportVersion}); | |
var rows = report.rows(); | |
processReport(querySheet, rows, "Query", "QueryMatchTypeWithVariant", reportDates) | |
addStyleToSheet(querySheet); | |
//var spreadsheetUrl = spreadsheet.getUrl(); | |
var notes = "Full report: <a href=\""+spreadsheetUrl+"\" target=\"_blank\">link</a>"; | |
if(emailAddress) { | |
var subject = "Keyword and Query Report"; | |
var body = "Your report for the performance of different types of keywords and queries is ready: <a href=\""+spreadsheet.getUrl()+"\" target=\"_blank\">link</a><br/><br/>" + "Manage the Optmyzr Enhanced Script that created this report: https://tools.optmyzr.com/enhancedscript_settings/scriptSettings/41"; | |
var emailType = "notification"; | |
sendEmailNotifications(emailAddress, subject, body, emailType); | |
} | |
return(notes); | |
} | |
function processReport(sheet, rows, kwString, matchTypeString, reportDates) { | |
var kwList = new Array(); | |
var kwKeys = new Array(); | |
while(rows.hasNext()) { | |
var row = rows.next(); | |
var clicks = parseInt(row['Clicks']); | |
var impressions = parseInt(row['Impressions']); | |
var cost = getFloat(row['Cost']); | |
var value = getFloat(row['ConversionValue']); | |
var conversions = parseInt(row['Conversions']); | |
var avgPos = getFloat(row['AveragePosition']); | |
var matchType = row[matchTypeString]; | |
var keywordText = row[kwString]; | |
var kwNumWords = keywordText.split(" ").length; | |
if(kwString.toLowerCase().indexOf("criteria") != -1) { | |
debug("keyword text: " + keywordText + " (" + kwNumWords + ")"); | |
kwNumWords = kwNumWords + " word keywords"; | |
} else if(kwString.toLowerCase().indexOf("query") != -1) { | |
debug("query text: " + keywordText + " (" + kwNumWords + ")"); | |
kwNumWords = kwNumWords + " word queries"; | |
} | |
if(!kwList[matchType]) { | |
//Logger.log("new ad"); | |
kwList[matchType] = new Object(); | |
kwList[matchType].clicks = clicks; | |
kwList[matchType].impressions = impressions; | |
kwList[matchType].cost = cost; | |
kwList[matchType].value = value; | |
kwList[matchType].conversions = conversions; | |
kwList[matchType].pos = avgPos * impressions; | |
kwKeys.push(matchType); | |
} else { | |
//Logger.log("existing..."); | |
kwList[matchType].clicks += clicks; | |
kwList[matchType].impressions += impressions; | |
kwList[matchType].cost += cost; | |
kwList[matchType].value += value; | |
kwList[matchType].conversions +=conversions; | |
kwList[matchType].pos += (avgPos * impressions); | |
} | |
if(!kwList[kwNumWords]) { | |
//Logger.log("new ad"); | |
kwList[kwNumWords] = new Object(); | |
kwList[kwNumWords].clicks = clicks; | |
kwList[kwNumWords].impressions = impressions; | |
kwList[kwNumWords].cost = cost; | |
kwList[kwNumWords].value = value; | |
kwList[kwNumWords].conversions = conversions; | |
kwList[kwNumWords].pos = avgPos * impressions; | |
kwKeys.push(kwNumWords); | |
} else { | |
//Logger.log("existing..."); | |
kwList[kwNumWords].clicks += clicks; | |
kwList[kwNumWords].impressions += impressions; | |
kwList[kwNumWords].cost += cost; | |
kwList[kwNumWords].value += value; | |
kwList[kwNumWords].conversions += conversions; | |
kwList[kwNumWords].pos += avgPos * impressions; | |
} | |
} | |
Logger.log(kwKeys.length + " Unique Keyword Types Evaluated"); | |
for(var i=0; i < kwKeys.length; i++) { | |
var kwKey = kwKeys[i]; | |
var kw = kwList[kwKey]; | |
var clicks = kw.clicks; | |
var impressions = kw.impressions; | |
var cost = kw.cost; | |
var value = kw.value; | |
var pos = kw.pos; | |
var conversions = kw.conversions; | |
var roas = (cost==0)? 0 : value / cost; | |
var ctr = (impressions==0)? 0 : clicks / impressions; | |
var avgCpc = (clicks==0)? 0 : cost/ clicks; | |
var pos = (impressions==0)? 0 : pos / impressions; | |
var costPerConversion = (conversions==0)? 0 : cost / conversions; | |
//Logger.log(adKey + " " + clicks + " " + impressions + " " + cost); | |
if(DEBUG == 1) Logger.log(kwKey + " clicks: " + clicks); | |
sheet.appendRow([reportDates.niceStartDate, reportDates.niceEndDate, kwKey, clicks, impressions, cost, ctr, avgCpc, conversions, costPerConversion, value, roas, pos]); | |
} | |
} | |
function addStyleToSheet(dataSheet) { | |
var numRows = dataSheet.getDataRange().getNumRows(); | |
var numColumns = dataSheet.getDataRange().getNumColumns(); | |
dataSheet.getRange(2, 4, numRows, 1).setNumberFormat("#,##0"); //clicks | |
dataSheet.getRange(2, 5, numRows, 1).setNumberFormat("#,##0"); // imp | |
dataSheet.getRange(2, 6, numRows, 1).setNumberFormat("#,##0.00"); // cost | |
dataSheet.getRange(2, 7, numRows, 1).setNumberFormat("0.00%"); // ctr | |
dataSheet.getRange(2, 8, numRows, 1).setNumberFormat("#,##0.00"); // avg cpc | |
dataSheet.getRange(2, 9, numRows, 1).setNumberFormat("#,##0.00"); //conversions | |
dataSheet.getRange(2, 10, numRows, 1).setNumberFormat("#,##0.00"); // cost / conv | |
dataSheet.getRange(2, 11, numRows, 1).setNumberFormat("#,##0.00"); // conv value | |
dataSheet.getRange(2, 12, numRows, 1).setNumberFormat("0.00"); // conv value / cost | |
dataSheet.getRange(2, 13, numRows, 1).setNumberFormat("#,##0.00"); // avg pos | |
// sort on type column | |
dataSheet.getRange(2, 1, numRows, numColumns).sort(3); | |
} | |
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 setUpReportInGoogleSheets(spreadsheetUrl, spreadsheetName, accountManagers, overWriteOldData, sheetNames, folderNames) { | |
var destinationSpreadsheet = new Object(); | |
if(folderNames) { | |
var folderStructure = folderNames.split(","); | |
} else { | |
var folderStructure = new Array(); | |
} | |
var targetFolder = DriveApp.getRootFolder(); | |
for(var i = 0; i < folderStructure.length; i++) { | |
var folderName = folderStructure[i]; | |
if(folderName.toLowerCase().indexOf("[account id]") != -1) { | |
folderName = AdWordsApp.currentAccount().getCustomerId(); | |
} else if(folderName.toLowerCase().indexOf("[account name]") != -1) { | |
folderName = AdWordsApp.currentAccount().getName(); | |
} | |
Logger.log("folderName: " + folderName); | |
var foldersIterator = targetFolder.getFoldersByName(folderName); | |
if (foldersIterator.hasNext()) { | |
targetFolder = foldersIterator.next(); | |
Logger.log("Selected target folder: " + folderName); | |
} else { | |
if(DEBUG==1) Logger.log("Creating a new folder: " + folderName); | |
targetFolder = targetFolder.createFolder(folderName); | |
} | |
} | |
destinationSpreadsheet.overWrite = overWriteOldData; | |
if(!spreadsheetUrl || spreadsheetUrl == "" || spreadsheetUrl == " " || spreadsheetUrl.toLowerCase().indexOf("new") != -1) var isNew = 1; | |
destinationSpreadsheet.isNew = isNew; | |
if(!sheetNames || !sheetNames[0]) { | |
var sheetNames = new Array(); | |
sheetNames[0] = "Sheet 1"; | |
} | |
if(isNew) | |
{ | |
var spreadsheet = SpreadsheetApp.create(spreadsheetName); | |
var id = spreadsheet.getId(); | |
var spreadsheetUrl = spreadsheet.getUrl(); | |
var file = DriveApp.getFileById(id); | |
targetFolder.addFile(file); | |
if(folderName) DriveApp.getRootFolder().removeFile(file); | |
} | |
var spreadsheet = SpreadsheetApp.openByUrl(spreadsheetUrl); | |
destinationSpreadsheet.spreadsheet = spreadsheet; | |
destinationSpreadsheet.url = spreadsheet.getUrl(); | |
// IF NEW -> REMOVE ALL SHEETS, THEN CREATE ALL SHEETS | |
if(isNew){ | |
var allSheets = spreadsheet.getSheets(); | |
// remove | |
for(var i=1,len=allSheets.length;i<len;i++){ | |
spreadsheet.deleteSheet(allSheets[i]); | |
} | |
// create | |
allSheets[0].setName(sheetNames[0]); | |
for(var sheetCounter = 1; sheetCounter < sheetNames.length; sheetCounter++) { | |
var sheetName = sheetNames[sheetCounter]; | |
if(DEBUG == 1) Logger.log("sheet name: " + sheetName); | |
spreadsheet.insertSheet(sheetName); | |
} | |
} else { | |
// IF NOT NEW, MAKE SURE RIGHT SHEETS EXIST | |
for(var sheetCounter = 0; sheetCounter < sheetNames.length; sheetCounter++) { | |
var sheetName = sheetNames[sheetCounter]; | |
if(DEBUG == 1) Logger.log("checking if sheet with name exists: " + sheetName); | |
try { | |
var thisSheet = spreadsheet.getSheetByName(sheetName); | |
if(!thisSheet) spreadsheet.insertSheet(sheetName); | |
} catch (e) { | |
Logger.log(e); | |
} | |
} | |
} | |
// ADD ACCOUNT MANAGERS | |
if(accountManagers && accountManagers!=""){ | |
var accountManagersArray = accountManagers.replace(/\s/g, "").split(","); | |
for(var i=0; i < accountManagersArray.length; i++) { | |
var accountManager = accountManagersArray[i]; | |
try { | |
spreadsheet.addEditor(accountManager); | |
} catch(e){ | |
debug("could not grant spreadsheet permissions to " + accountManager + ". " + e); | |
} | |
} | |
} | |
// IF OVERWRITE, CLEAR SHEETS | |
if(overWriteOldData) { | |
for(var sheetCounter = 0; sheetCounter < sheetNames.length; sheetCounter++) { | |
var sheetName = sheetNames[sheetCounter]; | |
if(DEBUG == 1) Logger.log("sheet name: " + sheetName); | |
try { | |
var thisSheet = spreadsheet.getSheetByName(sheetName); | |
if(thisSheet) thisSheet.clear(); | |
} catch (e) { | |
Logger.log(e); | |
} | |
} | |
} | |
return(destinationSpreadsheet); | |
} | |
function getReportDates(time) { | |
var timeZone = AdWordsApp.currentAccount().getTimeZone(); | |
var date = new Date(); | |
var dt =Utilities.formatDate(date, timeZone, 'MMMM dd, yyyy HH:mm:ss'); | |
today = new Date(dt); | |
var reportDates = new Object(); | |
switch(time) { | |
case "LAST_30_DAYS": | |
var startDate = new Date().setDate(today.getDate()-30); | |
var endDate = new Date().setDate(today.getDate()-1); | |
break; | |
case "LAST_14_DAYS": | |
var startDate = new Date().setDate(today.getDate()-14); | |
var endDate = new Date().setDate(today.getDate()-1); | |
break; | |
case "LAST_7_DAYS": | |
var startDate = new Date().setDate(today.getDate()-7); | |
var endDate = new Date().setDate(today.getDate()-1); | |
break; | |
case "TODAY": | |
var startDate = new Date().setDate(today); | |
var endDate = new Date().setDate(today); | |
break; | |
case "YESTERDAY": | |
var startDate = new Date().setDate(today.getDate()-1); | |
var endDate = new Date().setDate(today.getDate()-1); | |
break; | |
case "THIS_MONTH": | |
var startDate = new Date(today.getFullYear(), today.getMonth(), 1); | |
var endDate = new Date(today.getFullYear(), today.getMonth(), today.getDate()); | |
var lastPossibleDate = new Date(today.getFullYear(), today.getMonth()+1, 0); | |
var reportLastPossibleDate = Utilities.formatDate(lastPossibleDate, "America/Los_Angeles", "yyyyMMdd"); | |
reportDates.reportLastPossibleDate = reportLastPossibleDate; | |
reportDates.lastPossibleDate = lastPossibleDate; | |
break; | |
case "LAST_MONTH": | |
var startDate = new Date(today.getFullYear(), today.getMonth()-1, 1); | |
var endDate = new Date(today.getFullYear(), today.getMonth(), 0); | |
var niceStartDate = Utilities.formatDate(new Date(startDate), "America/Los_Angeles", "yyyy-MM-dd"); | |
var niceEndDate = Utilities.formatDate(new Date(endDate), "America/Los_Angeles", "yyyy-MM-dd"); | |
break; | |
case "THIS_WEEK_SUN_TODAY": | |
var tempDate = today; | |
var startDate = new Date(tempDate.setDate(today.getDate() - today.getDay())); | |
var endDate = new Date(today.getFullYear(), today.getMonth(), today.getDate()); | |
break; | |
case "THIS_WEEK_MON_TODAY": | |
var tempDate = today; | |
var startDate = new Date(tempDate.setDate(today.getDate() - today.getDay() + 1)); | |
var endDate = new Date(today.getFullYear(), today.getMonth(), today.getDate()); | |
break; | |
case "LAST_WEEK": // mon-sun | |
var tempDate = today; | |
var startDate = new Date(tempDate.setDate(today.getDate() - today.getDay() - 6)); | |
var tempDate = new Date(); | |
var endDate = new Date(tempDate.setDate(today.getDate() - today.getDay())); | |
break; | |
case "LAST_WEEK_SUN_SAT": // mon-sun | |
var tempDate = today; | |
var startDate = new Date(tempDate.setDate(today.getDate() - today.getDay() - 7)); | |
var tempDate = new Date(); | |
var endDate = new Date(tempDate.setDate(today.getDate() - today.getDay() -1)); | |
break; | |
case "LAST_BUSINESS_WEEK": // mon-fri | |
var tempDate = today; | |
var startDate = new Date(tempDate.setDate(today.getDate() - today.getDay() - 6)); | |
var tempDate = new Date(); | |
var endDate = new Date(tempDate.setDate(today.getDate() - today.getDay() - 2)); | |
break; | |
} | |
var niceStartDate = Utilities.formatDate(new Date(startDate), "America/Los_Angeles", "yyyy-MM-dd"); | |
var niceEndDate = Utilities.formatDate(new Date(endDate), "America/Los_Angeles", "yyyy-MM-dd"); | |
reportDates.niceStartDate = niceStartDate; | |
reportDates.niceEndDate = niceEndDate; | |
return(reportDates); | |
} | |
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 debug(log) { | |
if(DEBUG) Logger.log(log); | |
} | |
function sendEmailNotifications(emailAddresses, subject, body, emailType ) { | |
if(emailAddresses) { | |
if(emailType.toLowerCase().indexOf("warning") != -1) { | |
var finalSubject = "[Warning] " + subject + " - " + AdWordsApp.currentAccount().getName() + " (" + AdWordsApp.currentAccount().getCustomerId() + ")" | |
} else if(emailType.toLowerCase().indexOf("notification") != -1) { | |
var finalSubject = "[Notification] " + subject + " - " + AdWordsApp.currentAccount().getName() + " (" + AdWordsApp.currentAccount().getCustomerId() + ")" | |
} | |
if(AdWordsApp.getExecutionInfo().isPreview()) { | |
var finalBody = "<b>This script ran in preview mode. No changes were made to your account.</b><br/>" + body; | |
} else { | |
var finalBody = body; | |
} | |
var emailQuotaRemaining = MailApp.getRemainingDailyQuota(); | |
debug("emailQuotaRemaining: " + emailQuotaRemaining); | |
if(emailQuotaRemaining > 0) { | |
MailApp.sendEmail({ | |
to:emailAddresses, | |
subject: finalSubject, | |
htmlBody: finalBody | |
}); | |
return("email sent to " + emailAddresses + ": " + finalSubject) | |
} else { | |
var text = "You have run out of email quota for today. No more emails can be sent today."; | |
debug(text); | |
return(text); | |
} | |
//if(DEBUG == 1) Logger.log("email sent to " + emailAddresses + ": " + finalSubject); | |
} | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment