Get a dashboard of all the experiments in a single Google Ads account

Google Ads Experiment Dashboard (single account)

// Create a dashboard of Google Ads experiments in a Google spreadsheet 
// -- Single account version
//
// Free AdWords Script courtesy of 
// Optmyzr.com 
// PPC tools, reports and scripts for Google, Bing, Facebook, and Amazon
//
// September 2019

/* instructions
 * ------------
 *
 * On the line that starts with 'var spreadsheetUrl = ', enter the URL to a blank Google spreadsheet you created. The dashboard will be built here
 * On the line that starts with 'var sheet =  ', enter the name of the Google sheet (tab) where the dashboard should go
 *
 * update the accountSelector as needed to limit which accounts are included on the dashboard
 */


function main() {
  
  var currentSetting = new Object();
  var spreadsheetUrl = "https://docs.google.com/a/optmyzr.com/spreadsheets/d/...O57qhDeAyBEB97Pa2OFxIHViN9_Sente8dmMvRmg/edit"; // enter your own blank spreadsheet URL here
  var sheetName = "Sheet1";
  
  var spreadsheet = SpreadsheetApp.openByUrl(spreadsheetUrl);
  var sheet = spreadsheet.getSheetByName(sheetName);
  
  sheet.clear();
  var sheetHeaders = new Array("Account Name", "Account Id", "Campaign", "Experiment Name", "Experiment Split", "Status", "Start Date", "End Date", 
                               "Conversions (control)", "Conversions (experiment)", "Conversions (difference)",
                               "Clicks (control)", "Clicks (experiment)", "Clicks (difference)",
                               "Impressions (control)", "Impressions (experiment)", "Impressions (difference)",
                               "Cost (control)", "Cost (experiment)", "Cost (difference)",
                               "Conversion Rate (control)", "Conversion Rate (experiment)", "Conversion Rate (difference)", "z-score", "p-value",
                               "CTR (control)", "CTR (experiment)", "CTR (difference)", "z-score", "p-value",
                               "Avg. CPC (control)", "Avg. CPC (experiment)", "Avg. CPC (difference)"
                              );
  sheet.appendRow(sheetHeaders);
  
  var data = getExperiments();
  for(var i = 0; i< data.length; i++) {
    var row = data[i];
    sheet.appendRow(row);
  }
  
  var numRows = sheet.getDataRange().getNumRows();
  sheet.getRange(2, 11, numRows, 1).setNumberFormat("0.00%");
  sheet.getRange(2, 14, numRows, 1).setNumberFormat("0.00%");
  sheet.getRange(2, 17, numRows, 1).setNumberFormat("0.00%");
  sheet.getRange(2, 20, numRows, 1).setNumberFormat("0.00%");
  sheet.getRange(2, 23, numRows, 1).setNumberFormat("0.00%");
  sheet.getRange(2, 28, numRows, 1).setNumberFormat("0.00%");
  sheet.getRange(2, 33, numRows, 1).setNumberFormat("0.00%");
  
  var numRowsMinusOne = numRows - 1;
  sheet.getRange(2, 25, numRowsMinusOne, 1).setFormulaR1C1("NORMDIST(R[0]C[-1]:R[0]C[-1],0,1,TRUE)");
  sheet.getRange(2, 30, numRowsMinusOne, 1).setFormulaR1C1("NORMDIST(R[0]C[-1]:R[0]C[-1],0,1,TRUE)");
  
  sheet.setFrozenColumns(4);
  sheet.setFrozenRows(1);
  

}

function getExperiments() {
    var output = new Array();
    var map = new Object();
  
    var accountName = AdsApp.currentAccount().getName();
    var accountId = AdsApp.currentAccount().getCustomerId();
  
    var experimentSelector = AdsApp.experiments()
       .orderBy("StartDate DESC");

    var experimentIterator = experimentSelector.get();
    while (experimentIterator.hasNext()) {
      var experiment = experimentIterator.next();
      var experimentId = experiment.getId();
      var experimentName = experiment.getName();
      var experimentStartMonth = ("0" + experiment.getStartDate().month).slice(-2);
      var experimentStartDay = ("0" + experiment.getStartDate().day).slice(-2);
      var experimentStartDate = "" + experiment.getStartDate().year + experimentStartMonth + experimentStartDay;
      var experimentEndMonth = ("0" + experiment.getEndDate().month).slice(-2);
      var experimentEndDay = ("0" + experiment.getEndDate().day).slice(-2);
      var endDate = experiment.getEndDate();
      if(!endDate) {
        var date = new Date();
        var timeZone = AdWordsApp.currentAccount().getTimeZone();
        var today =  Utilities.formatDate(date, timeZone, "yyyyMMdd");
        var experimentEndDateForDisplay = "none";
        var experimentEndDate = today;
      } else {
        var experimentEndMonth = ("0" + experiment.getEndDate().month).slice(-2);
        var experimentEndDay = ("0" + experiment.getEndDate().day).slice(-2);
        var experimentEndDate = "" + experiment.getEndDate().year + experimentEndMonth + experimentEndDay;
        var experimentEndDateForDisplay = experimentEndDate;
      }
      var experimentSplit = experiment.getTrafficSplitPercent();
      var experimentStatus= experiment.getStatus();
    
      var dateStringForStats = experimentStartDate + "," + experimentEndDate;
    
      var baseCampaign = experiment.getBaseCampaign();
      var baseCampaignName = baseCampaign.getName();
      var baseCampaignStats = experiment.getBaseCampaign().getStatsFor(experimentStartDate, experimentEndDate);
      var baseCampaignClicks = baseCampaignStats.getClicks();
      var baseCampaignImpressions = baseCampaignStats.getImpressions();
      var baseCampaignConversions = baseCampaignStats.getConversions();
      var baseCampaignAverageCpc = baseCampaignStats.getAverageCpc();
      var baseCampaignCost = baseCampaignStats.getCost();
      var baseCampaignCtr = baseCampaignStats.getCtr();
      var baseCampaignConversionRate = baseCampaignStats.getConversionRate();
    
      var experimentCampaign = experiment.getExperimentCampaign();
      var experimentCampaignName = experimentCampaign.getName();
      var experimentCampaignStats = experimentCampaign.getStatsFor(experimentStartDate, experimentEndDate);
      var experimentCampaignClicks = experimentCampaignStats.getClicks();
      var experimentCampaignImpressions = experimentCampaignStats.getImpressions();
      var experimentCampaignConversions = experimentCampaignStats.getConversions();
      var experimentCampaignAverageCpc = experimentCampaignStats.getAverageCpc();
      var experimentCampaignCost = experimentCampaignStats.getCost();
      var experimentCampaignCtr = experimentCampaignStats.getCtr();
      var experimentCampaignConversionRate = experimentCampaignStats.getConversionRate();
    
    
    
      //Logger.log(baseCampaignName+ " " + experimentName + " " + experimentStartDate);
    
      if(!map[baseCampaignName]) {
        map[baseCampaignName] = new Object();
        map[baseCampaignName].name = baseCampaignName;
        map[baseCampaignName].experiments = new Array();
        map[baseCampaignName].experiments[experimentId] = new Object();
        map[baseCampaignName].experiments[experimentId].baseCampaign = new Object();
        map[baseCampaignName].experiments[experimentId].experimentCampaign = new Object();
      } else if(!map[baseCampaignName].experiments[experimentId]) {
        map[baseCampaignName].experiments[experimentId] = new Object();
        map[baseCampaignName].experiments[experimentId].baseCampaign = new Object();
        map[baseCampaignName].experiments[experimentId].experimentCampaign = new Object();
      }
    
      map[baseCampaignName].experiments[experimentId].name = experimentName;
      map[baseCampaignName].experiments[experimentId].split = experimentSplit;
      map[baseCampaignName].experiments[experimentId].startDate = experimentStartDate;
      map[baseCampaignName].experiments[experimentId].endDate = experimentEndDateForDisplay;
      map[baseCampaignName].experiments[experimentId].status = experimentStatus;
    
      map[baseCampaignName].experiments[experimentId].baseCampaign.clicks = baseCampaignClicks;
      map[baseCampaignName].experiments[experimentId].baseCampaign.impressions = baseCampaignImpressions;
      map[baseCampaignName].experiments[experimentId].baseCampaign.conversions = baseCampaignConversions;
      map[baseCampaignName].experiments[experimentId].baseCampaign.averageCpc = baseCampaignAverageCpc
      map[baseCampaignName].experiments[experimentId].baseCampaign.cost = baseCampaignCost;
      map[baseCampaignName].experiments[experimentId].baseCampaign.ctr = baseCampaignCtr;
      map[baseCampaignName].experiments[experimentId].baseCampaign.conversionRate = baseCampaignConversionRate;
    
      map[baseCampaignName].experiments[experimentId].experimentCampaign.clicks = experimentCampaignClicks;
      map[baseCampaignName].experiments[experimentId].experimentCampaign.clicks = experimentCampaignClicks;
      map[baseCampaignName].experiments[experimentId].experimentCampaign.impressions = experimentCampaignImpressions;
      map[baseCampaignName].experiments[experimentId].experimentCampaign.conversions = experimentCampaignConversions;
      map[baseCampaignName].experiments[experimentId].experimentCampaign.averageCpc = experimentCampaignAverageCpc
      map[baseCampaignName].experiments[experimentId].experimentCampaign.cost = experimentCampaignCost;
      map[baseCampaignName].experiments[experimentId].experimentCampaign.ctr = experimentCampaignCtr;
      map[baseCampaignName].experiments[experimentId].experimentCampaign.conversionRate = experimentCampaignConversionRate;
    }
  
  
  
    // iterate through map
    for(var baseCampaignName in map) {
      var baseCampaign = map[baseCampaignName];
      var baseCampaignName = baseCampaign.name;
      Logger.log(baseCampaignName);
    
      for(var experimentId in map[baseCampaignName].experiments) {
        var experiment = map[baseCampaignName].experiments[experimentId];
        var experimentName = experiment.name;
        var experimentSplit = experiment.split;
        var experimentStartDate = experiment.startDate;
        var experimentEndDate = experiment.endDate;
        var experimentStatus = experiment.status;
      
        var baseCampaign = experiment.baseCampaign;
        var baseCampaignClicks = baseCampaign.clicks;
        var baseCampaignImpressions = baseCampaign.impressions;
        var baseCampaignConversions = baseCampaign.conversions;
        var baseCampaignAverageCpc = baseCampaign.averageCpc;
        var baseCampaignCost = baseCampaign.cost;
        var baseCampaignCtr = baseCampaign.ctr;
        var baseCampaignConversionRate = baseCampaign.conversionRate;
      
        var experimentCampaign = experiment.experimentCampaign;
        var experimentCampaignClicks = experimentCampaign.clicks;
        var experimentCampaignImpressions = experimentCampaign.impressions;
        var experimentCampaignConversions = experimentCampaign.conversions;
        var experimentCampaignAverageCpc = experimentCampaign.averageCpc;
        var experimentCampaignCost = experimentCampaign.cost;
        var experimentCampaignCtr = experimentCampaign.ctr;
        var experimentCampaignConversionRate = experimentCampaign.conversionRate;
      
        var changeForClicks = (experimentCampaignClicks * (100-experimentSplit)/experimentSplit - baseCampaignClicks ) / baseCampaignClicks;
        var changeForImpressions = (experimentCampaignImpressions * (100-experimentSplit)/experimentSplit  - baseCampaignImpressions ) / baseCampaignImpressions;
        var changeForConversions = (experimentCampaignConversions * (100-experimentSplit)/experimentSplit  - baseCampaignConversions ) / baseCampaignConversions;
        var changeForCost = (experimentCampaignCost * (100-experimentSplit)/experimentSplit  - baseCampaignCost ) / baseCampaignCost;
        
        var changeForAverageCpc = (experimentCampaignAverageCpc - baseCampaignAverageCpc) / baseCampaignAverageCpc;
        var changeForCtr = (experimentCampaignCtr - baseCampaignCtr) / baseCampaignCtr;
        var changeForConversionRate = (experimentCampaignConversionRate - baseCampaignConversionRate) / baseCampaignConversionRate;
        
        var ctrStdevControl = Math.sqrt((baseCampaignCtr * (1 - baseCampaignCtr) / baseCampaignImpressions));
        var ctrStdevExp = Math.sqrt((experimentCampaignCtr * (1 - experimentCampaignCtr) / experimentCampaignImpressions));
        var ctrZScore = (baseCampaignCtr - experimentCampaignCtr) / Math.sqrt(Math.pow(ctrStdevControl,2) + Math.pow(ctrStdevExp,2));
          
        var conversionRateStdevControl = Math.sqrt((baseCampaignConversionRate * (1 - baseCampaignConversionRate) / baseCampaignClicks));
        var conversionRateStdevExp = Math.sqrt((experimentCampaignConversionRate * (1 - experimentCampaignConversionRate) / experimentCampaignClicks));
        var conversionRateZScore = (baseCampaignConversionRate - experimentCampaignConversionRate) / Math.sqrt(Math.pow(conversionRateStdevControl,2) + Math.pow(conversionRateStdevExp,2));
          
      
        var rowContents = new Array(accountName, accountId, baseCampaignName, experimentName, experimentSplit, experimentStatus, experimentStartDate, experimentEndDate, 
                                    baseCampaignConversions, experimentCampaignConversions, changeForConversions.toFixed(2),
                                    baseCampaignClicks, experimentCampaignClicks, changeForClicks.toFixed(2),
                                    baseCampaignImpressions, experimentCampaignImpressions, changeForImpressions.toFixed(2),
                                    baseCampaignCost, experimentCampaignCost, changeForCost.toFixed(2),
                                    baseCampaignConversionRate, experimentCampaignConversionRate, changeForConversionRate.toFixed(2), conversionRateZScore, "",
                                    baseCampaignCtr, experimentCampaignCtr, changeForCtr.toFixed(2), ctrZScore, "",
                                    baseCampaignAverageCpc, experimentCampaignAverageCpc, changeForAverageCpc.toFixed(2)
                                   );
        output.push(rowContents);
        //sheet.appendRow(rowContents);
        //Logger.log("  " + experimentName + " (" + experimentSplit + ") " + baseCampaignClicks + " " + experimentCampaignClicks + " change: " + changeForClicks + "%");
      
      }
    }
  return(output);
}