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