/**
 * Original code developed by 'Karooya Technologies Pvt. Ltd.'
 * Adapted by Mark & Michal at Oneegg.com.au to incorporate revenue and ROI metrics
 * 
 */

// Path to the folder in Google Drive where all the reports are to be created
var REPORTS_FOLDER_PATH = 'Experiment Performance';

// Specify a date range for the report 
var DATE_RANGE = "LAST_30_DAYS"; // Other allowed values are: TODAY, YESTERDAY, LAST_7_DAYS, THIS_WEEK_SUN_TODAY, THIS_WEEK_MON_TODAY, LAST_WEEK, LAST_14_DAYS, LAST_30_DAYS, LAST_WEEK, LAST_BUSINESS_WEEK, LAST_WEEK_SUN_SAT, THIS_MONTH, LAST_MONTH 

// Or specify a custom date range. Format is: yyyy-mm-dd
var USE_CUSTOM_DATE_RANGE = true;
var START_DATE = "2018-01-13"; // Example "2017-01-01"
var END_DATE = "2018-03-12"; // Example "2017-01-31"

/*-- More filter for MCC account --*/
//Is your account a MCC account
var IS_MCC_ACCOUNT = false;

var FILTER_ACCOUNTS_BY_LABEL = false;
var ACCOUNT_LABEL_TO_SELECT = "INSERT_LABEL_NAME_HERE";

var FILTER_ACCOUNTS_BY_IDS = false;
var ACCOUNT_IDS_TO_SELECT = ['INSERT_ACCOUNT_ID_HERE', 'INSERT_ACCOUNT_ID_HERE'];
/*---------------------------------*/

//The script is expected to work with following API version
var API_VERSION = {
    apiVersion: 'v201809'
}

//////////////////////////////////////////////////////////////////////////////
function main() {
  var reportsFolder = getFolder(REPORTS_FOLDER_PATH);
  
  var recoSummaryArray = new Array();
  if (!IS_MCC_ACCOUNT) {
    processCurrentAccount(reportsFolder);
  } else {
    var childAccounts  = getManagedAccounts();
    while(childAccounts.hasNext()) {
      var childAccount  = childAccounts .next()
      MccApp.select(childAccount);
      processCurrentAccount(reportsFolder);
    }
  }
   
//  trackEventInAnalytics();
  Logger.log("Done!");
  Logger.log("=========================");
  Logger.log("All the reports are available in the Google Drive folder at following URL: ");
  Logger.log(reportsFolder.getUrl());
  Logger.log("=========================");
}

function getManagedAccounts() {
  var accountSelector = MccApp.accounts();
  if (FILTER_ACCOUNTS_BY_IDS) {
    accountSelector = accountSelector.withIds(ACCOUNT_IDS_TO_SELECT);
  }
  if (FILTER_ACCOUNTS_BY_LABEL) {
    accountSelector = accountSelector.withCondition("LabelNames CONTAINS '" + ACCOUNT_LABEL_TO_SELECT + "'")
  }
  return accountSelector.get();  
}

function processCurrentAccount(reportsFolder) {
  var adWordsAccount = AdWordsApp.currentAccount();
  var spreadsheet = getReportSpreadsheet(reportsFolder, adWordsAccount);
  
  var accountName = adWordsAccount.getName();
  var currencyCode = adWordsAccount.getCurrencyCode();
  Logger.log("Accesing AdWords account: " + accountName);
  Logger.log("Fetching data from AdWords..");
  var campaignPerformanceReport = getCampaignPerformanceReport();
  
  Logger.log("Computing..");
  var report = compute(campaignPerformanceReport);
  
//  Logger.log("Exporting raw data..");
//  exportRawData(campaignPerformanceReport, spreadsheet);
  
  Logger.log("Exporting report for " + accountName + " to spreadsheet..");
  var newSheetName = Utilities.formatDate(new Date(), adWordsAccount.getTimeZone(), "yyyyMMdd") + "_" + getDateRange("-");
  var sheet = getSheetByName(spreadsheet, newSheetName);
  exportToSpreadsheet(report, sheet, accountName, currencyCode);
}

function exportRawData(adwordsReport, spreadsheet) {
  var sheet = getSheetByName(spreadsheet, "Raw Report Data");
  adwordsReport.exportToSheet(sheet);
}

function compute(report) {
  var reportIterator = report.rows();

  // Group campaigns by experiments (i.e. by BaseCampaignIDs) 
  var campaignMap = new Object();
  while (reportIterator.hasNext()) {
    var campaign = reportIterator.next();
    groupCampaignsByBaseCampaign(campaignMap, campaign);
  }
  
  var campaignExpArr = new Array();
  for (var key in campaignMap) {
    if (campaignMap.hasOwnProperty(key)) {
      var campaignGroup = campaignMap[key]
      if (campaignGroup.trialCampaign && campaignGroup.baseCampaign) { // Has a trial campaign
        var exp = campaignMap[key];
        exp["statsDiff"] = computePerformanceDiff(exp.baseCampaign, exp.trialCampaign);
        campaignExpArr.push(exp);
      }
    }
  }
  
  // Sort by total cost
  campaignExpArr.sort(function(obj1, obj2) {
    var val1 = cleanAndParseFloat(obj1.baseCampaign.Cost) + cleanAndParseFloat(obj1.trialCampaign.Cost);
    var val2 = cleanAndParseFloat(obj2.baseCampaign.Cost) + cleanAndParseFloat(obj2.trialCampaign.Cost);
    if (val1 < val2) return 1;
    else if (val1 > val2) return -1;
    else return 0;
  }, true);
  
  var accountSummary = getAccountSummary(campaignExpArr)
  var result = {
      'campaignExpArr' : campaignExpArr,
      'accountSummary' : accountSummary
  }
  return result;
}

function getAccountSummary(campaignExpArr) {
  var pertArr = new Array();
  for(var idx=0; idx<campaignExpArr.length; idx++) {
    pertArr.push(campaignExpArr[idx].baseCampaign);
  }
  var baseCampaign = computeCombinedPerformance(pertArr);
  baseCampaign["CampaignName"] = "All Base Campaigns";
  baseCampaign["CampaignTrialType"] = "";
  
  pertArr = new Array();
  for(var idx=0; idx<campaignExpArr.length; idx++) {
    pertArr.push(campaignExpArr[idx].trialCampaign);
  }
  var trialCampaign  = computeCombinedPerformance(pertArr);
  trialCampaign["CampaignName"] = "All Trial Campaigns";
  trialCampaign["CampaignTrialType"] = "";
  
  var statsDiff = computePerformanceDiff(baseCampaign, trialCampaign);
  
  return {
    'baseCampaign' : baseCampaign,
    'trialCampaign' : trialCampaign,
    'statsDiff': statsDiff
  };
}

function groupCampaignsByBaseCampaign(campaignMap, campaign) {
  var campaignGroup = campaignMap[campaign["BaseCampaignId"]];
  if (!campaignGroup) {
    campaignGroup = {
        'baseCampaign' : undefined,
        'trialCampaign' : undefined
    };
    campaignMap[campaign["BaseCampaignId"]] = campaignGroup;
  }
  
  if (campaign["CampaignTrialType"] === "trial campaign") {
    if (campaignGroup.trialCampaign) {
      Logger.log("WARNING: Two active trials for a campaigns. CampaignIDs: " + campaign["CampaignId"] + "/" + campaignGroup.trialCampaign["CampaignId"]);
    } else {
      campaignGroup.trialCampaign = campaign;
    }
  } else if (campaign["CampaignTrialType"] === "base campaign") {
    if (campaignGroup.baseCampaign) {
      Logger.log("ERROR: Duplicate records for a base campaign: " + campaign["CampaignId"]);
    } else {
      campaignGroup.baseCampaign = campaign;
    }
  } else {
    Logger.log("WARNING: Unexpected CampaignTrialType. Campaign/Type: " + campaign["CampaignName"] + "/" + campaign["CampaignTrialType"]);
  }
}


function printCampaigns() {
  var campaignSelector = AdWordsApp.campaigns();
  var campaigns = campaignSelector.get();
  while(campaigns.hasNext()) {
    var campaign = campaigns.next();
    Logger.log(campaign.getId() + " : " + campaign.getName() + " : " + campaign.getEntityType() + " : " + campaign.getCampaignTrialType());
  }
}

function getCampaignPerformanceReport() {
  var dateRange = getDateRange(",");

  var query = "SELECT CampaignId, CampaignName, CampaignStatus, CampaignTrialType, " +
        "BaseCampaignId, AverageCpc, AveragePosition, Clicks, Ctr, Impressions, " +
        "Cost, Conversions, CostPerConversion, ConversionRate, ConversionValue " +
    " FROM  CAMPAIGN_PERFORMANCE_REPORT " +
    " WHERE CampaignStatus = ENABLED AND ServingStatus = SERVING " +
    " DURING " + dateRange;
	//var report = AdWordsApp.report(query, API_VERSION);
	//var rows = report.rows();
	
	//while (rows.hasNext()){
	//	var row = rows.next();
	//	if (row['ConversionValue'] > 0) {
	//	 row['ROI'] = row['Cost'] / row['ConversionValue'];	
	//	} else {
	//		row['ROI'] = 0;
	//	}
		
	//}
  return AdWordsApp.report(query, API_VERSION);
}

function getDateRange(seperator) {
  var dateRange = DATE_RANGE;
  if (USE_CUSTOM_DATE_RANGE) {
    dateRange = START_DATE.replace(/-/g, "") + seperator + END_DATE.replace(/-/g, "");
  }
  return dateRange;
}

function computePerformanceDiff(basePerf, trialPerf) {
  var diffPerf = new Object();
  diffPerf["Clicks"] = rateOfChange(basePerf["Clicks"], trialPerf["Clicks"]);
  diffPerf["Impressions"] = rateOfChange(basePerf["Impressions"], trialPerf["Impressions"]);
  diffPerf["Ctr"] = rateOfChange(basePerf["Ctr"], trialPerf["Ctr"]);
  diffPerf["AverageCpc"] = rateOfChange(basePerf["AverageCpc"], trialPerf["AverageCpc"]);
  diffPerf["Cost"] = rateOfChange(basePerf["Cost"], trialPerf["Cost"]);
  diffPerf["Conversions"] = rateOfChange(basePerf["Conversions"], trialPerf["Conversions"]);
  diffPerf["ConversionRate"] = rateOfChange(basePerf["ConversionRate"], trialPerf["ConversionRate"]);
  diffPerf["CostPerConversion"] = rateOfChange(basePerf["CostPerConversion"], trialPerf["CostPerConversion"]);
  diffPerf["AveragePosition"] = rateOfChange(basePerf["AveragePosition"], trialPerf["AveragePosition"]);
  diffPerf["ConversionValue"] = rateOfChange(basePerf["ConversionValue"], trialPerf["ConversionValue"]);
  return diffPerf;
}

function computeCombinedPerformance(perfArray) {
  var totalClicks = 0;
  var totalImpressions = 0;
  var totalCost = 0.0;
  var totalPositionImpressions = 0.0;
  var totalConversions = 0.0;
  var totalConversionValue = 0.0;
  
  for(var idx=0; idx < perfArray.length; idx++) {
    totalClicks += cleanAndParseInt(perfArray[idx]["Clicks"]);
    totalImpressions += cleanAndParseInt(perfArray[idx]["Impressions"]);
    totalCost += cleanAndParseFloat(perfArray[idx]["Cost"]);
    totalPositionImpressions += cleanAndParseInt(perfArray[idx]["Impressions"]) * cleanAndParseFloat(perfArray[idx]["AveragePosition"]);
    totalConversions += cleanAndParseFloat(perfArray[idx]["Conversions"]);
	totalConversionValue += cleanAndParseFloat(perfArray[idx]["ConversionValue"]);
  }
  
  var result = {
    'Clicks' : totalClicks,
    'Impressions' : totalImpressions,
    'Cost' : totalCost,
    'Conversions' : totalConversions,
	'ConversionValue': totalConversionValue
  }
  var ctr = 0;
  var cpc = 0;
  var costPerConversion = 0;
  
  if (totalClicks > 0 && totalImpressions > 0) {
    ctr = findCtr(totalClicks, totalImpressions);
    cpc = findCPC(totalCost, totalClicks);
  }
  
  result['Ctr'] = ctr;
  result['AverageCpc'] = cpc;
  result['ConversionRate'] = findConvRate(totalConversions, totalClicks);

  if (totalConversions > 0) {
    costPerConversion = totalCost/totalConversions;
  }
  result['CostPerConversion'] = costPerConversion;

  var avgPosition = totalPositionImpressions;
  if (totalImpressions > 0) {
    avgPosition = totalPositionImpressions / totalImpressions;
  }
  result['AveragePosition'] = avgPosition;

  return result;
}

function findCtr(clicks, impressions) {
  if (impressions <= 0) {
    return 0;
  }
  return (clicks / impressions);
}

function findCPC(cost, clicks) {
  if (clicks <= 0) {
    return 0;
  }
  return cost/clicks;
}

function findConvRate(conversions, clicks) {
  if (clicks <= 0) {
    return 0;
  }
  return (conversions / clicks);
}

function rateOfChange(refVal, trialVal) {
  refVal = cleanAndParseFloat(refVal);
  trialVal = cleanAndParseFloat(trialVal);
  if (refVal == 0 && trialVal == 0) { return 0; }
  if (refVal == 0 && trialVal > 0) { return 1; }
  return (trialVal - refVal) / refVal;
}

/***********/
function exportToSpreadsheet(report, sheet, accountName, currencyCode) {
  var colTitleColor = "#03cfcc"; // Aqua
  var summaryRowColor = "#D3D3D3"; // Grey
  var oddRowColor = "#FFFFFF";
  var evenRowColor = "#EFEFEF";
  var headers = getColHeaders(currencyCode);
  var tableHeaderRowPos = 4;
  var rowIdx = tableHeaderRowPos;
  sheet.getRange(rowIdx++, 1, 1, headers.length).setValues([headers]).setBackground(colTitleColor).setFontWeight("BOLD").setWrap(true).setHorizontalAlignment("center").setVerticalAlignment("middle");
  
  addCampaignStatsRow(sheet, rowIdx++, report.accountSummary.baseCampaign, summaryRowColor, "BOLD");
  addCampaignStatsRow(sheet, rowIdx++, report.accountSummary.trialCampaign, summaryRowColor, "BOLD");
  addStatsDiffRow(sheet, rowIdx++, report.accountSummary.statsDiff, summaryRowColor, "BOLD");
  sheet.getRange(rowIdx++, 1, 1, headers.length).merge() // Blank line
  
  for(var idx=0; idx<report.campaignExpArr.length; idx++) {
    var expt = report.campaignExpArr[idx];
    var bgColor = (idx % 2) ? evenRowColor : oddRowColor;
    addCampaignStatsRow(sheet, rowIdx++, expt.baseCampaign, bgColor);
    addCampaignStatsRow(sheet, rowIdx++, expt.trialCampaign, bgColor);
    addStatsDiffRow(sheet, rowIdx++, expt.statsDiff, bgColor);
    sheet.getRange(rowIdx++, 1, 1, headers.length).merge() // Blank line
  }
  
  var colPosition = 1;
  setColumnWidth(sheet, colPosition++, 25); setColumnWidth(sheet, colPosition++, 12);
  for(var i=2; i < headers.length; i++) {
    setColumnWidth(sheet, colPosition++, 8);
  }
  
  sheet.getRange(1, 1, 1, headers.length).merge().setValue("AdWords account: " + accountName).setFontWeight("BOLD").setFontSize(18);
  sheet.getRange(2, 1, 1, 3).merge().setValue("Report Generated On: " + Utilities.formatDate(new Date(), AdWordsApp.currentAccount().getTimeZone(), "yyyy-MM-dd HH:mm:ss (Z) "));
  var dateRange = DATE_RANGE;
  if (USE_CUSTOM_DATE_RANGE) {
    dateRange = START_DATE + " To " + END_DATE;
  }
  sheet.getRange(2, 5, 1, 6).merge().setValue("Date Range: " + dateRange);
  
  sheet.setFrozenRows(tableHeaderRowPos + 3);
}

function addCampaignStatsRow(sheet, rowIdx, campaignStats, bgColor, fontWeight) {
  var row = getCampaignStatsRow(campaignStats);
  var range = sheet.getRange(rowIdx, 1, 1, row.length).setValues([row]).setBackground(bgColor); 
  if (fontWeight) {
    range.setFontWeight(fontWeight)
  }
  sheet.getRange(rowIdx, 3, 1, 2).setNumberFormat("###,##0");
  sheet.getRange(rowIdx, 5, 1, 1).setNumberFormat("0.0%");
  sheet.getRange(rowIdx, 6, 1, 2).setNumberFormat("###,##0.00");
  sheet.getRange(rowIdx, 8, 1, 1).setNumberFormat("###,##0");
  sheet.getRange(rowIdx, 9, 1, 1).setNumberFormat("0.0%");
  sheet.getRange(rowIdx, 10, 1, 2).setNumberFormat("###,##0.00");
  sheet.getRange(rowIdx, 11, 1, 2).setNumberFormat("###,##0.00");
  sheet.getRange(rowIdx, 12, 1, 2).setNumberFormat("###,##0.00");
}

function addStatsDiffRow(sheet, rowIdx, statsDiff, bgColor, fontWeight) {
  var row = [
     "Change %",
     "",
     statsDiff["Clicks"],
     statsDiff["Impressions"],
     statsDiff["Ctr"],
     statsDiff["AverageCpc"],
     statsDiff["Cost"],
     statsDiff["Conversions"],
     statsDiff["ConversionRate"],
     statsDiff["CostPerConversion"],
     statsDiff["AveragePosition"],
	 statsDiff["ConversionValue"],
   ];
  
  var range = sheet.getRange(rowIdx, 1, 1, row.length).setValues([row]).setBackground(bgColor); 
  if (fontWeight) {
    range.setFontWeight(fontWeight)
  }
  sheet.getRange(rowIdx, 1+2, 1, row.length-2).setNumberFormat("0.0%");
}

function setColumnWidth(sheet, colPosition, width) {
  sheet.setColumnWidth(colPosition, width * 10); 
}

function getCampaignStatsRow(campaign) {
	var ROI = 0.0;
  Logger.log("Campaign's Conv Value: " + campaign["ConversionValue"] +" Campaign's Cost: "+ campaign["Cost"]);
  Logger.log("Campaign's Conv Value: " + cleanAndParseFloat(campaign["ConversionValue"]) +" Campaign's Cost: "+ cleanAndParseFloat(campaign["Cost"]));
	//if (campaign["ConversionValue"] > 0 && campaign["Cost"] > 0) {
		ROI = cleanAndParseFloat(campaign["ConversionValue"]) / cleanAndParseFloat(campaign["Cost"]);
        Logger.log("Campaign's ROI: " + ROI);
	//}
  
  return [
      campaign["CampaignName"],
      campaign["CampaignTrialType"],
      campaign["Clicks"],
      campaign["Impressions"],
      campaign["Ctr"],
      campaign["AverageCpc"],
      campaign["Cost"],
      campaign["Conversions"],
      campaign["ConversionRate"],
      campaign["CostPerConversion"],
      campaign["AveragePosition"],
	  campaign["ConversionValue"],
	  ROI,
    ];
}

function getColHeaders(currencyCode) {
  return [
    "Campaign Name",
    "Campaign Trial Type",
    "Clicks",
    "Impressions",
    "CTR(%)",
    "CPC (" + currencyCode + ")",
    "Cost (" + currencyCode + ")",
    "Conversions",
    "Conversion Rate (%)",
    "Cost / Conversion (" + currencyCode + ")",
    "Avg. Position",
	"Conversion Value (" + currencyCode + ")",
    "ROI",
    ];
}

/*
 * Gets the worksheet from the given spreadsheet and workshee-name.
 * Creates new worksheet if doesn't exist.
 */
function getSheetByName(spreadsheet, newSheetName) {
  var sheet = spreadsheet.getSheetByName(newSheetName);
  if (sheet != null) {
    sheet.clear();
  } else {
    sheet = spreadsheet.insertSheet(newSheetName, 0);
  }
  return sheet;
}

/*
 * Gets the report file (spreadsheet) for the given Adwords account in the given folder.
 * Creates a new spreadsheet if doesn't exist.
 */
function getReportSpreadsheet(folder, adWordsAccount) {
  var accountId = adWordsAccount.getCustomerId();
  var accountName = adWordsAccount.getName();
  var spreadsheet = undefined;
  var files = folder.searchFiles(
      'mimeType = "application/vnd.google-apps.spreadsheet" and title contains "'+ accountId + '"');
  if (files.hasNext()) {
    var file = files.next();
    spreadsheet = SpreadsheetApp.open(file);
  }
  
  if (!spreadsheet) {
    var fileName = accountName + " (" + accountId + ")";
    spreadsheet = SpreadsheetApp.create(fileName);
    var file = DriveApp.getFileById(spreadsheet.getId());
    var oldFolder = file.getParents().next();
    folder.addFile(file);
    oldFolder.removeFile(file);
  }
  return spreadsheet;
}

/*
* Gets the folder in Google Drive for the given folderPath.  
* Creates the folder and all the intermediate folders if needed.
*/
function getFolder(folderPath) {
  var folder = DriveApp.getRootFolder();
  var folderNamesArray = folderPath.split("/");
  for(var idx=0; idx < folderNamesArray.length; idx++) {
    var newFolderName = folderNamesArray[idx];
    // Skip if new folder name is empty (possibly due to slash at the end) 
    if (newFolderName.trim() == "") { 
      continue;
    }
    var folderIterator = folder.getFoldersByName(newFolderName);
    if (folderIterator.hasNext()) {
      folder = folderIterator.next();
    } else {
      Logger.log("Creating folder '" + newFolderName + "'");
      folder = folder.createFolder(newFolderName);
    }
  }
  return folder;
}

/* ******************************************* */
function cleanAndParseFloat(valueStr) {
  valueStr = cleanValueStr(valueStr);
  return parseFloat(valueStr);
}

function cleanAndParseInt(valueStr) {
  valueStr = cleanValueStr(valueStr);
  return parseInt(valueStr);
}

function cleanValueStr(valueStr) {
  valueStr = valueStr.toString();
  if (valueStr.charAt(valueStr.length - 1) == '%') {
    valueStr = valueStr.substring(0, valueStr.length - 1);
  }
  valueStr = valueStr.replace(/,/g,'');
  return valueStr;
}