Get aggregate performance data by keyword and search term match type
// 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