Skip to content

Instantly share code, notes, and snippets.

@siliconvallaeys
Last active January 14, 2020 03:18
Show Gist options
  • Save siliconvallaeys/8a5030cae63e34a56ec575cad08ac51a to your computer and use it in GitHub Desktop.
Save siliconvallaeys/8a5030cae63e34a56ec575cad08ac51a to your computer and use it in GitHub Desktop.
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