Ad component report for Google Ads with segment data
// Create a report in a Google spreadsheet with performance data by ad component | |
// Metrics for each unique headline, description, path, and visible URL are aggregated | |
// Use this data to find the best ad components for use in new ads or responsive search ads | |
// Free AdWords Script courtesy of Optmyzr.com - try Optmyzr for more PPC tools and scripts | |
// February 2019 | |
function main() { | |
var currentSetting = {}; | |
currentSetting.spreadsheetUrl = "NEW"; | |
currentSetting.time = "20180101,20190201"; //"LAST_30_DAYS", "LAST_MONTH", "20180101,20181231" | |
currentSetting.accountManagers = "example@example.com"; | |
currentSetting.emailAddresses = "example@example.com"; | |
currentSetting.campaignNameIncludesIgnoreCase = ""; // e.g. 'search'; | |
currentSetting.segment = "Device"; //Device, DayOfWeek, Slot | |
var currencyCode = "$"; | |
//END OF EDIT-SECTION | |
var DEBUG = 1; | |
var segmentCols = currentSetting.segment ? { | |
'Device': { 'Computers': 1, 'Mobile devices with full browsers': 1, 'Tablets with full browsers': 1 }, | |
'DayOfWeek': { 'Monday': 1, 'Tuesday': 1, 'Wednesday': 1, 'Thursday': 1, 'Friday': 1, 'Saturday': 1, 'Sunday': 1 }, | |
'Slot': { | |
'Google search: Side': 1, 'Google search: Top': 1, 'Google search: Other': 1, | |
'Google Display Network': 1, 'Search partners: Top': 1, 'Search partners: Other': 1 , | |
'Cross-network': 1 | |
} | |
}[currentSetting.segment] : ''; | |
if(currentSetting.campaignNameIncludesIgnoreCase.indexOf("'") != -1) { | |
var campaignSelectorString = 'AND CampaignName CONTAINS_IGNORE_CASE ' + '"' + currentSetting.campaignNameIncludesIgnoreCase + '"'; | |
} else { | |
var campaignSelectorString = "AND CampaignName CONTAINS_IGNORE_CASE " + "'" + currentSetting.campaignNameIncludesIgnoreCase + "'"; | |
} | |
if(DEBUG == 1) Logger.log("spreadsheet URL: " + currentSetting.spreadsheetUrl); | |
var reportDate = new Date(); | |
var dateForFilename = reportDate.yyyymmdd(); | |
if(currentSetting.spreadsheetUrl.toLowerCase().indexOf("new") != -1) | |
{ | |
var spreadsheet = SpreadsheetApp.create("Ad Template Report - " + AdWordsApp.currentAccount().getName() + " - " + dateForFilename + " (" + currentSetting.time + ")"); | |
currentSetting.spreadsheetUrl = spreadsheet.getUrl(); | |
} | |
var spreadsheet = SpreadsheetApp.openByUrl(currentSetting.spreadsheetUrl); | |
currentSetting.accountManagersArray = currentSetting.accountManagers.replace(/\s/g, "").split(","); | |
spreadsheet.addEditors(currentSetting.accountManagersArray); | |
//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]); | |
} | |
var expandedTextAdList = new Array(); | |
var expandedTextAdKeys = new Array(); | |
var headline1List = new Array(); | |
var headline2List = new Array(); | |
var headline3List = new Array(); | |
var descriptionList = new Array(); | |
var description2List = new Array(); | |
var path1List = new Array(); | |
var path2List = new Array(); | |
var pathList = new Array(); | |
var pathKeys = new Array(); | |
var query = [ | |
'SELECT HeadlinePart1, HeadlinePart2, ExpandedTextAdHeadlinePart3, Description, ExpandedTextAdDescription2, Path1, Path2,', | |
currentSetting.segment ? (currentSetting.segment + ',') : '', | |
'Clicks, Impressions, Cost, Conversions, ConversionValue', | |
'FROM AD_PERFORMANCE_REPORT', | |
'WHERE Cost > 0 and AdType = EXPANDED_TEXT_AD', | |
currentSetting.campaignNameIncludesIgnoreCase ? campaignSelectorString : '', | |
'DURING', currentSetting.time | |
].join(' '); | |
var report = AdsApp.report(query, {apiVersion: 'v201809'}); | |
var rows = report.rows(); | |
while(rows.hasNext()) { | |
var row = rows.next(); | |
var segmentVal = ''; | |
if(currentSetting.segment) { | |
segmentVal = row[currentSetting.segment]; | |
} | |
var clicks = parseInt(row['Clicks']); | |
var impressions = parseInt(row['Impressions']); | |
var cost = parseFloat(row['Cost'].replace(/,/g,"")); | |
var conversions = parseInt(row['Conversions'].replace(/,/g,"")); | |
var conversionValue = parseFloat(row['ConversionValue'].replace(/,/g,"")); | |
var headline1 = row['HeadlinePart1']; | |
var headline2 = row['HeadlinePart2']; | |
var headline3 = row['ExpandedTextAdHeadlinePart3']; | |
var description = row['Description']; | |
var description2 = row['ExpandedTextAdDescription2']; | |
var path1 = row['Path1']; | |
var path2 = row['Path2']; | |
var adFootPrint = headline1 + "--n--" + headline2 + "--n--" + headline3 + "--n--" + description + "--n--" + description2; | |
var pathFootPrint = path1 + "--n--" + path2; | |
if(!expandedTextAdList[adFootPrint]) { | |
expandedTextAdList[adFootPrint] = {}; | |
if(currentSetting.segment) { | |
for(var col in segmentCols) { | |
expandedTextAdList[adFootPrint][col] = { | |
'clicks': 0, 'impressions': 0, 'cost': 0, 'conversions': 0, 'conversionValue': 0 | |
} | |
} | |
} | |
expandedTextAdList[adFootPrint]['total'] = { | |
'clicks': 0, 'impressions': 0, 'cost': 0, 'conversions': 0, 'conversionValue': 0 | |
} | |
expandedTextAdKeys.push(adFootPrint); | |
} | |
if(currentSetting.segment && segmentCols[segmentVal]) { | |
expandedTextAdList[adFootPrint][segmentVal].clicks += clicks; | |
expandedTextAdList[adFootPrint][segmentVal].impressions += impressions; | |
expandedTextAdList[adFootPrint][segmentVal].cost += cost; | |
expandedTextAdList[adFootPrint][segmentVal].conversions += conversions; | |
expandedTextAdList[adFootPrint][segmentVal].conversionValue += conversionValue; | |
} | |
expandedTextAdList[adFootPrint]['total'].clicks += clicks; | |
expandedTextAdList[adFootPrint]['total'].impressions += impressions; | |
expandedTextAdList[adFootPrint]['total'].cost += cost; | |
expandedTextAdList[adFootPrint]['total'].conversions += conversions; | |
expandedTextAdList[adFootPrint]['total'].conversionValue += conversionValue; | |
if(!headline1List[headline1]) { | |
headline1List[headline1] = {}; | |
if(currentSetting.segment) { | |
for(var col in segmentCols) { | |
headline1List[headline1][col] = { | |
'clicks': 0, 'impressions': 0, 'cost': 0, 'conversions': 0, 'conversionValue': 0 | |
} | |
} | |
} | |
headline1List[headline1]['total'] = { | |
'clicks': 0, 'impressions': 0, 'cost': 0, 'conversions': 0, 'conversionValue': 0 | |
} | |
} | |
if(currentSetting.segment && segmentCols[segmentVal]) { | |
headline1List[headline1][segmentVal].clicks += clicks; | |
headline1List[headline1][segmentVal].impressions += impressions; | |
headline1List[headline1][segmentVal].cost += cost; | |
headline1List[headline1][segmentVal].conversions += conversions; | |
headline1List[headline1][segmentVal].conversionValue += conversionValue; | |
} | |
headline1List[headline1]['total'].clicks += clicks; | |
headline1List[headline1]['total'].impressions += impressions; | |
headline1List[headline1]['total'].cost += cost; | |
headline1List[headline1]['total'].conversions += conversions; | |
headline1List[headline1]['total'].conversionValue += conversionValue; | |
if(!headline2List[headline2]) { | |
headline2List[headline2] = {}; | |
if(currentSetting.segment) { | |
for(var col in segmentCols) { | |
headline2List[headline2][col] = { | |
'clicks': 0, 'impressions': 0, 'cost': 0, 'conversions': 0, 'conversionValue': 0 | |
} | |
} | |
} | |
headline2List[headline2]['total'] = { | |
'clicks': 0, 'impressions': 0, 'cost': 0, 'conversions': 0, 'conversionValue': 0 | |
} | |
} | |
if(currentSetting.segment && segmentCols[segmentVal]) { | |
headline2List[headline2][segmentVal].clicks += clicks; | |
headline2List[headline2][segmentVal].impressions += impressions; | |
headline2List[headline2][segmentVal].cost += cost; | |
headline2List[headline2][segmentVal].conversions += conversions; | |
headline2List[headline2][segmentVal].conversionValue += conversionValue; | |
} | |
headline2List[headline2]['total'].clicks += clicks; | |
headline2List[headline2]['total'].impressions += impressions; | |
headline2List[headline2]['total'].cost += cost; | |
headline2List[headline2]['total'].conversions += conversions; | |
headline2List[headline2]['total'].conversionValue += conversionValue; | |
if(!headline3List[headline3]) { | |
headline3List[headline3] = {}; | |
if(currentSetting.segment) { | |
for(var col in segmentCols) { | |
headline3List[headline3][col] = { | |
'clicks': 0, 'impressions': 0, 'cost': 0, 'conversions': 0, 'conversionValue': 0 | |
} | |
} | |
} | |
headline3List[headline3]['total'] = { | |
'clicks': 0, 'impressions': 0, 'cost': 0, 'conversions': 0, 'conversionValue': 0 | |
} | |
} | |
if(currentSetting.segment && segmentCols[segmentVal]) { | |
headline3List[headline3][segmentVal].clicks += clicks; | |
headline3List[headline3][segmentVal].impressions += impressions; | |
headline3List[headline3][segmentVal].cost += cost; | |
headline3List[headline3][segmentVal].conversions += conversions; | |
headline3List[headline3][segmentVal].conversionValue += conversionValue; | |
} | |
headline3List[headline3]['total'].clicks += clicks; | |
headline3List[headline3]['total'].impressions += impressions; | |
headline3List[headline3]['total'].cost += cost; | |
headline3List[headline3]['total'].conversions += conversions; | |
headline3List[headline3]['total'].conversionValue += conversionValue; | |
if(!descriptionList[description]) { | |
descriptionList[description] = {}; | |
if(currentSetting.segment) { | |
for(var col in segmentCols) { | |
descriptionList[description][col] = { | |
'clicks': 0, 'impressions': 0, 'cost': 0, 'conversions': 0, 'conversionValue': 0 | |
} | |
} | |
} | |
descriptionList[description]['total'] = { | |
'clicks': 0, 'impressions': 0, 'cost': 0, 'conversions': 0, 'conversionValue': 0 | |
} | |
} | |
if(currentSetting.segment && segmentCols[segmentVal]) { | |
descriptionList[description][segmentVal].clicks += clicks; | |
descriptionList[description][segmentVal].impressions += impressions; | |
descriptionList[description][segmentVal].cost += cost; | |
descriptionList[description][segmentVal].conversions += conversions; | |
descriptionList[description][segmentVal].conversionValue += conversionValue; | |
} | |
descriptionList[description]['total'].clicks += clicks; | |
descriptionList[description]['total'].impressions += impressions; | |
descriptionList[description]['total'].cost += cost; | |
descriptionList[description]['total'].conversions += conversions; | |
descriptionList[description]['total'].conversionValue += conversionValue; | |
if(!description2List[description2]) { | |
description2List[description2] = {}; | |
if(currentSetting.segment) { | |
for(var col in segmentCols) { | |
description2List[description2][col] = { | |
'clicks': 0, 'impressions': 0, 'cost': 0, 'conversions': 0, 'conversionValue': 0 | |
} | |
} | |
} | |
description2List[description2]['total'] = { | |
'clicks': 0, 'impressions': 0, 'cost': 0, 'conversions': 0, 'conversionValue': 0 | |
} | |
} | |
if(currentSetting.segment && segmentCols[segmentVal]) { | |
description2List[description2][segmentVal].clicks += clicks; | |
description2List[description2][segmentVal].impressions += impressions; | |
description2List[description2][segmentVal].cost += cost; | |
description2List[description2][segmentVal].conversions += conversions; | |
description2List[description2][segmentVal].conversionValue += conversionValue; | |
} | |
description2List[description2]['total'].clicks += clicks; | |
description2List[description2]['total'].impressions += impressions; | |
description2List[description2]['total'].cost += cost; | |
description2List[description2]['total'].conversions += conversions; | |
description2List[description2]['total'].conversionValue += conversionValue; | |
if(!path1List[path1]) { | |
path1List[path1] = {}; | |
if(currentSetting.segment) { | |
for(var col in segmentCols) { | |
path1List[path1][col] = { | |
'clicks': 0, 'impressions': 0, 'cost': 0, 'conversions': 0, 'conversionValue': 0 | |
} | |
} | |
} | |
path1List[path1]['total'] = { | |
'clicks': 0, 'impressions': 0, 'cost': 0, 'conversions': 0, 'conversionValue': 0 | |
} | |
} | |
if(currentSetting.segment && segmentCols[segmentVal]) { | |
path1List[path1][segmentVal].clicks += clicks; | |
path1List[path1][segmentVal].impressions += impressions; | |
path1List[path1][segmentVal].cost += cost; | |
path1List[path1][segmentVal].conversions += conversions; | |
path1List[path1][segmentVal].conversionValue += conversionValue; | |
} | |
path1List[path1]['total'].clicks += clicks; | |
path1List[path1]['total'].impressions += impressions; | |
path1List[path1]['total'].cost += cost; | |
path1List[path1]['total'].conversions += conversions; | |
path1List[path1]['total'].conversionValue += conversionValue; | |
if(!path2List[path2]) { | |
path2List[path2] = {}; | |
if(currentSetting.segment) { | |
for(var col in segmentCols) { | |
path2List[path2][col] = { | |
'clicks': 0, 'impressions': 0, 'cost': 0, 'conversions': 0, 'conversionValue': 0 | |
} | |
} | |
} | |
path2List[path2]['total'] = { | |
'clicks': 0, 'impressions': 0, 'cost': 0, 'conversions': 0, 'conversionValue': 0 | |
} | |
} | |
if(currentSetting.segment && segmentCols[segmentVal]) { | |
path2List[path2][segmentVal].clicks += clicks; | |
path2List[path2][segmentVal].impressions += impressions; | |
path2List[path2][segmentVal].cost += cost; | |
path2List[path2][segmentVal].conversions += conversions; | |
path2List[path2][segmentVal].conversionValue += conversionValue; | |
} | |
path2List[path2]['total'].clicks += clicks; | |
path2List[path2]['total'].impressions += impressions; | |
path2List[path2]['total'].cost += cost; | |
path2List[path2]['total'].conversions += conversions; | |
path2List[path2]['total'].conversionValue += conversionValue; | |
if(!pathList[pathFootPrint]) { | |
pathList[pathFootPrint] = {}; | |
if(currentSetting.segment) { | |
for(var col in segmentCols) { | |
pathList[pathFootPrint][col] = { | |
'clicks': 0, 'impressions': 0, 'cost': 0, 'conversions': 0, 'conversionValue': 0 | |
} | |
} | |
} | |
pathList[pathFootPrint]['total'] = { | |
'clicks': 0, 'impressions': 0, 'cost': 0, 'conversions': 0, 'conversionValue': 0 | |
} | |
pathKeys.push(pathFootPrint); | |
} | |
if(currentSetting.segment && segmentCols[segmentVal]) { | |
pathList[pathFootPrint][segmentVal].clicks += clicks; | |
pathList[pathFootPrint][segmentVal].impressions += impressions; | |
pathList[pathFootPrint][segmentVal].cost += cost; | |
pathList[pathFootPrint][segmentVal].conversions += conversions; | |
pathList[pathFootPrint][segmentVal].conversionValue += conversionValue; | |
} | |
pathList[pathFootPrint]['total'].clicks += clicks; | |
pathList[pathFootPrint]['total'].impressions += impressions; | |
pathList[pathFootPrint]['total'].cost += cost; | |
pathList[pathFootPrint]['total'].conversions += conversions; | |
pathList[pathFootPrint]['total'].conversionValue += conversionValue; | |
} | |
//if(DEBUG == 1) Logger.log(expandedTextAdKeys.length + " Unique Ads Evaluated"); | |
var metrics = [ | |
"Clicks", "Impressions", "Cost ("+currencyCode+")", "Conversions", "CTR", | |
"Avg. CPC ("+currencyCode+")", "CPA ("+currencyCode+")", "ROAS", "Conv. Value ("+currencyCode+")" | |
]; | |
var mainHeader = [] | |
for(var i in metrics) { | |
if(currentSetting.segment) { | |
for(var col in segmentCols) { | |
mainHeader.push(metrics[i] + ' (' + col + ')'); | |
} | |
mainHeader.push(metrics[i] + ' (total)'); | |
} else { | |
mainHeader.push(metrics[i]); | |
} | |
} | |
var header = ["Headline1"].concat(mainHeader); | |
var output = [header]; | |
for(var headline1 in headline1List) { | |
var stats = headline1List[headline1]; | |
var out = [headline1]; | |
for(var key in stats) { | |
out.push(stats[key].clicks); | |
} | |
for(var key in stats) { | |
out.push(stats[key].impressions); | |
} | |
for(var key in stats) { | |
out.push(stats[key].cost); | |
} | |
for(var key in stats) { | |
out.push(stats[key].conversions); | |
} | |
for(var key in stats) { | |
var ctr = stats[key].impressions == 0 ? 0 : stats[key].clicks / stats[key].impressions; | |
out.push(100*ctr+'%'); | |
} | |
for(var key in stats) { | |
var cpc = stats[key].clicks == 0 ? 0 : stats[key].cost / stats[key].clicks; | |
out.push(cpc); | |
} | |
for(var key in stats) { | |
var cpa = stats[key].conversions == 0 ? 0 : stats[key].cost / stats[key].conversions; | |
out.push(cpa); | |
} | |
for(var key in stats) { | |
var roas = stats[key].cost == 0 ? 0 : stats[key].conversionValue / stats[key].cost; | |
out.push(100*roas+'%'); | |
} | |
for(var key in stats) { | |
out.push(stats[key].conversionValue); | |
} | |
output.push(out); | |
} | |
var headline1Sheet = spreadsheet.insertSheet("Headline 1"); | |
headline1Sheet.setFrozenRows(1); | |
headline1Sheet.getRange(1,1,1,output[0].length).setFontWeight("bold"); | |
headline1Sheet.getRange(1,1,output.length,output[0].length).setValues(output); | |
var header = ["Headline2"].concat(mainHeader); | |
var output = [header]; | |
for(var headline2 in headline2List) { | |
var stats = headline2List[headline2]; | |
var out = [headline2]; | |
for(var key in stats) { | |
out.push(stats[key].clicks); | |
} | |
for(var key in stats) { | |
out.push(stats[key].impressions); | |
} | |
for(var key in stats) { | |
out.push(stats[key].cost); | |
} | |
for(var key in stats) { | |
out.push(stats[key].conversions); | |
} | |
for(var key in stats) { | |
var ctr = stats[key].impressions == 0 ? 0 : stats[key].clicks / stats[key].impressions; | |
out.push(100*ctr+'%'); | |
} | |
for(var key in stats) { | |
var cpc = stats[key].clicks == 0 ? 0 : stats[key].cost / stats[key].clicks; | |
out.push(cpc); | |
} | |
for(var key in stats) { | |
var cpa = stats[key].conversions == 0 ? 0 : stats[key].cost / stats[key].conversions; | |
out.push(cpa); | |
} | |
for(var key in stats) { | |
var roas = stats[key].cost == 0 ? 0 : stats[key].conversionValue / stats[key].cost; | |
out.push(100*roas+'%'); | |
} | |
for(var key in stats) { | |
out.push(stats[key].conversionValue); | |
} | |
output.push(out); | |
} | |
var headline2Sheet = spreadsheet.insertSheet("Headline 2"); | |
headline2Sheet.setFrozenRows(1); | |
headline2Sheet.getRange(1,1,1,output[0].length).setFontWeight("bold"); | |
headline2Sheet.getRange(1,1,output.length,output[0].length).setValues(output); | |
var header = ["Headline3"].concat(mainHeader); | |
var output = [header]; | |
for(var headline3 in headline3List) { | |
var stats = headline3List[headline3]; | |
var out = [headline3]; | |
for(var key in stats) { | |
out.push(stats[key].clicks); | |
} | |
for(var key in stats) { | |
out.push(stats[key].impressions); | |
} | |
for(var key in stats) { | |
out.push(stats[key].cost); | |
} | |
for(var key in stats) { | |
out.push(stats[key].conversions); | |
} | |
for(var key in stats) { | |
var ctr = stats[key].impressions == 0 ? 0 : stats[key].clicks / stats[key].impressions; | |
out.push(100*ctr+'%'); | |
} | |
for(var key in stats) { | |
var cpc = stats[key].clicks == 0 ? 0 : stats[key].cost / stats[key].clicks; | |
out.push(cpc); | |
} | |
for(var key in stats) { | |
var cpa = stats[key].conversions == 0 ? 0 : stats[key].cost / stats[key].conversions; | |
out.push(cpa); | |
} | |
for(var key in stats) { | |
var roas = stats[key].cost == 0 ? 0 : stats[key].conversionValue / stats[key].cost; | |
out.push(100*roas+'%'); | |
} | |
for(var key in stats) { | |
out.push(stats[key].conversionValue); | |
} | |
output.push(out); | |
} | |
var headline3Sheet = spreadsheet.insertSheet("Headline 3"); | |
headline3Sheet.setFrozenRows(1); | |
headline3Sheet.getRange(1,1,1,output[0].length).setFontWeight("bold"); | |
headline3Sheet.getRange(1,1,output.length,output[0].length).setValues(output); | |
var header = ["Description 1"].concat(mainHeader); | |
var output = [header]; | |
for(var desc in descriptionList) { | |
var stats = descriptionList[desc]; | |
var out = [desc]; | |
for(var key in stats) { | |
out.push(stats[key].clicks); | |
} | |
for(var key in stats) { | |
out.push(stats[key].impressions); | |
} | |
for(var key in stats) { | |
out.push(stats[key].cost); | |
} | |
for(var key in stats) { | |
out.push(stats[key].conversions); | |
} | |
for(var key in stats) { | |
var ctr = stats[key].impressions == 0 ? 0 : stats[key].clicks / stats[key].impressions; | |
out.push(100*ctr+'%'); | |
} | |
for(var key in stats) { | |
var cpc = stats[key].clicks == 0 ? 0 : stats[key].cost / stats[key].clicks; | |
out.push(cpc); | |
} | |
for(var key in stats) { | |
var cpa = stats[key].conversions == 0 ? 0 : stats[key].cost / stats[key].conversions; | |
out.push(cpa); | |
} | |
for(var key in stats) { | |
var roas = stats[key].cost == 0 ? 0 : stats[key].conversionValue / stats[key].cost; | |
out.push(100*roas+'%'); | |
} | |
for(var key in stats) { | |
out.push(stats[key].conversionValue); | |
} | |
output.push(out); | |
} | |
var descriptionSheet = spreadsheet.insertSheet("Description 1"); | |
descriptionSheet.setFrozenRows(1); | |
descriptionSheet.getRange(1,1,1,output[0].length).setFontWeight("bold"); | |
descriptionSheet.getRange(1,1,output.length,output[0].length).setValues(output); | |
var header = ["Description 2"].concat(mainHeader); | |
var output = [header]; | |
for(var desc2 in description2List) { | |
var stats = description2List[desc2]; | |
var out = [desc2]; | |
for(var key in stats) { | |
out.push(stats[key].clicks); | |
} | |
for(var key in stats) { | |
out.push(stats[key].impressions); | |
} | |
for(var key in stats) { | |
out.push(stats[key].cost); | |
} | |
for(var key in stats) { | |
out.push(stats[key].conversions); | |
} | |
for(var key in stats) { | |
var ctr = stats[key].impressions == 0 ? 0 : stats[key].clicks / stats[key].impressions; | |
out.push(100*ctr+'%'); | |
} | |
for(var key in stats) { | |
var cpc = stats[key].clicks == 0 ? 0 : stats[key].cost / stats[key].clicks; | |
out.push(cpc); | |
} | |
for(var key in stats) { | |
var cpa = stats[key].conversions == 0 ? 0 : stats[key].cost / stats[key].conversions; | |
out.push(cpa); | |
} | |
for(var key in stats) { | |
var roas = stats[key].cost == 0 ? 0 : stats[key].conversionValue / stats[key].cost; | |
out.push(100*roas+'%'); | |
} | |
for(var key in stats) { | |
out.push(stats[key].conversionValue); | |
} | |
output.push(out); | |
} | |
var description2Sheet = spreadsheet.insertSheet("Description 2"); | |
description2Sheet.setFrozenRows(1); | |
description2Sheet.getRange(1,1,1,output[0].length).setFontWeight("bold"); | |
description2Sheet.getRange(1,1,output.length,output[0].length).setValues(output); | |
var header = ["Path 1"].concat(mainHeader); | |
var output = [header]; | |
for(var path1 in path1List) { | |
var stats = path1List[path1]; | |
var out = [path1]; | |
for(var key in stats) { | |
out.push(stats[key].clicks); | |
} | |
for(var key in stats) { | |
out.push(stats[key].impressions); | |
} | |
for(var key in stats) { | |
out.push(stats[key].cost); | |
} | |
for(var key in stats) { | |
out.push(stats[key].conversions); | |
} | |
for(var key in stats) { | |
var ctr = stats[key].impressions == 0 ? 0 : stats[key].clicks / stats[key].impressions; | |
out.push(100*ctr+'%'); | |
} | |
for(var key in stats) { | |
var cpc = stats[key].clicks == 0 ? 0 : stats[key].cost / stats[key].clicks; | |
out.push(cpc); | |
} | |
for(var key in stats) { | |
var cpa = stats[key].conversions == 0 ? 0 : stats[key].cost / stats[key].conversions; | |
out.push(cpa); | |
} | |
for(var key in stats) { | |
var roas = stats[key].cost == 0 ? 0 : stats[key].conversionValue / stats[key].cost; | |
out.push(100*roas+'%'); | |
} | |
for(var key in stats) { | |
out.push(stats[key].conversionValue); | |
} | |
output.push(out); | |
} | |
var path1Sheet = spreadsheet.insertSheet("Path 1"); | |
path1Sheet.setFrozenRows(1); | |
path1Sheet.getRange(1,1,1,output[0].length).setFontWeight("bold"); | |
path1Sheet.getRange(1,1,output.length,output[0].length).setValues(output); | |
var header = ["Path 2"].concat(mainHeader); | |
var output = [header]; | |
for(var path2 in path2List) { | |
var stats = path2List[path2]; | |
var out = [path2]; | |
for(var key in stats) { | |
out.push(stats[key].clicks); | |
} | |
for(var key in stats) { | |
out.push(stats[key].impressions); | |
} | |
for(var key in stats) { | |
out.push(stats[key].cost); | |
} | |
for(var key in stats) { | |
out.push(stats[key].conversions); | |
} | |
for(var key in stats) { | |
var ctr = stats[key].impressions == 0 ? 0 : stats[key].clicks / stats[key].impressions; | |
out.push(100*ctr+'%'); | |
} | |
for(var key in stats) { | |
var cpc = stats[key].clicks == 0 ? 0 : stats[key].cost / stats[key].clicks; | |
out.push(cpc); | |
} | |
for(var key in stats) { | |
var cpa = stats[key].conversions == 0 ? 0 : stats[key].cost / stats[key].conversions; | |
out.push(cpa); | |
} | |
for(var key in stats) { | |
var roas = stats[key].cost == 0 ? 0 : stats[key].conversionValue / stats[key].cost; | |
out.push(100*roas+'%'); | |
} | |
for(var key in stats) { | |
out.push(stats[key].conversionValue); | |
} | |
output.push(out); | |
} | |
var path2Sheet = spreadsheet.insertSheet("Path 2"); | |
path2Sheet.setFrozenRows(1); | |
path2Sheet.getRange(1,1,1,output[0].length).setFontWeight("bold"); | |
path2Sheet.getRange(1,1,output.length,output[0].length).setValues(output); | |
var header = ["Headline1", "Headline2", "Headline3", "Description", "Description2"].concat(mainHeader); | |
var output = [header]; | |
for(var i=0; i < expandedTextAdKeys.length; i++) { | |
var adKey = expandedTextAdKeys[i]; | |
var stats = expandedTextAdList[adKey]; | |
var adParts = adKey.split("--n--"); | |
var headline1 = adParts[0]; | |
var headline2 = adParts[1]; | |
var headline3 = adParts[2] | |
var description = adParts[3]; | |
var description2 = adParts[4]; | |
var out = [headline1,headline2,headline3,description,description2]; | |
for(var key in stats) { | |
out.push(stats[key].clicks); | |
} | |
for(var key in stats) { | |
out.push(stats[key].impressions); | |
} | |
for(var key in stats) { | |
out.push(stats[key].cost); | |
} | |
for(var key in stats) { | |
out.push(stats[key].conversions); | |
} | |
for(var key in stats) { | |
var ctr = stats[key].impressions == 0 ? 0 : stats[key].clicks / stats[key].impressions; | |
out.push(100*ctr+'%'); | |
} | |
for(var key in stats) { | |
var cpc = stats[key].clicks == 0 ? 0 : stats[key].cost / stats[key].clicks; | |
out.push(cpc); | |
} | |
for(var key in stats) { | |
var cpa = stats[key].conversions == 0 ? 0 : stats[key].cost / stats[key].conversions; | |
out.push(cpa); | |
} | |
for(var key in stats) { | |
var roas = stats[key].cost == 0 ? 0 : stats[key].conversionValue / stats[key].cost; | |
out.push(100*roas+'%'); | |
} | |
for(var key in stats) { | |
out.push(stats[key].conversionValue); | |
} | |
output.push(out); | |
} | |
var expandedTextAdSheet = spreadsheet.insertSheet("Expanded Text Ads"); | |
expandedTextAdSheet.setFrozenRows(1); | |
expandedTextAdSheet.getRange(1,1,1,output[0].length).setFontWeight("bold"); | |
expandedTextAdSheet.getRange(1,1,output.length,output[0].length).setValues(output); | |
var header = ["Path1", "Path2"].concat(mainHeader); | |
var output = [header]; | |
for(var i=0; i < pathKeys.length; i++) { | |
var pathKey = pathKeys[i]; | |
var stats = pathList[pathKey]; | |
var parts = pathKey.split("--n--"); | |
var path1 = parts[0]; | |
var path2 = parts[1]; | |
var out = [path1,path2]; | |
for(var key in stats) { | |
out.push(stats[key].clicks); | |
} | |
for(var key in stats) { | |
out.push(stats[key].impressions); | |
} | |
for(var key in stats) { | |
out.push(stats[key].cost); | |
} | |
for(var key in stats) { | |
out.push(stats[key].conversions); | |
} | |
for(var key in stats) { | |
var ctr = stats[key].impressions == 0 ? 0 : stats[key].clicks / stats[key].impressions; | |
out.push(100*ctr+'%'); | |
} | |
for(var key in stats) { | |
var cpc = stats[key].clicks == 0 ? 0 : stats[key].cost / stats[key].clicks; | |
out.push(cpc); | |
} | |
for(var key in stats) { | |
var cpa = stats[key].conversions == 0 ? 0 : stats[key].cost / stats[key].conversions; | |
out.push(cpa); | |
} | |
for(var key in stats) { | |
var roas = stats[key].cost == 0 ? 0 : stats[key].conversionValue / stats[key].cost; | |
out.push(100*roas+'%'); | |
} | |
for(var key in stats) { | |
out.push(stats[key].conversionValue); | |
} | |
output.push(out); | |
} | |
var pathSheet = spreadsheet.insertSheet("Path Combinations"); | |
pathSheet.setFrozenRows(1); | |
pathSheet.getRange(1,1,1,output[0].length).setFontWeight("bold"); | |
pathSheet.getRange(1,1,output.length,output[0].length).setValues(output); | |
var remainingQuota = MailApp.getRemainingDailyQuota(); | |
currentSetting.emailAddressesArray = currentSetting.emailAddresses.replace(/\s/g, "").split(","); | |
if(remainingQuota > currentSetting.emailAddressesArray.length) { | |
var subject = "Ad Template Report for " + AdWordsApp.currentAccount().getName() + " is ready"; | |
var body = "Visit this link for the ad template report for CID " + AdWordsApp.currentAccount().getCustomerId() + ": <a href=\""+ currentSetting.spreadsheetUrl+"\" target=\"_blank\">link</a>"; | |
MailApp.sendEmail({ | |
to:currentSetting.emailAddresses, | |
subject: subject, | |
htmlBody: body | |
}); | |
} | |
currentSetting.notes = "Download Report: : <a href=\""+ currentSetting.spreadsheetUrl+"\" target=\"_blank\">link</a>"; | |
return(currentSetting.notes); | |
} | |
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 | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment