Skip to content

Instantly share code, notes, and snippets.

@siliconvallaeys

siliconvallaeys/Ad Component Report with Segments

Created February 13, 2019 00:26
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 2 You must be signed in to fork a gist
  • Save siliconvallaeys/cbb26d96bf759feeda56fa49cc909f07 to your computer and use it in GitHub Desktop.
Save siliconvallaeys/cbb26d96bf759feeda56fa49cc909f07 to your computer and use it in GitHub Desktop.
Download ZIP
Ad component report for Google Ads with segment data
Raw
Ad Component Report with Segments
// 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);
// EMAIL
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