Forked from siliconvallaeys/Ad Component Report with Segments
Created
September 24, 2020 19:43
-
-
Save dnalob/11fe54498c0bf78565f2e9852c467228 to your computer and use it in GitHub Desktop.
Ad component report for Google Ads with segment data
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 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 = "[email protected]"; | |
| currentSetting.emailAddresses = "[email protected]"; | |
| 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