Create a Google Ads Experiments dashboard for multiple accounts in an MCC

Google Ads Experiment Dashboard (MCC multi-account)

// Create a dashboard of Google Ads experiments in a Google spreadsheet 
// -- MCC version
//
// Free AdWords Script courtesy of 
// Optmyzr.com 
// PPC tools, reports and scripts for Google, Bing, Facebook, and Amazon
//
// Aug 2021 (added line 92 so it will only pull active experiments)
// 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 accountSelector = AdsManagerApp.accounts()
      .withLimit(50)
      .withCondition("Impressions > 100")
  //.withIds([..022273]) // enter your comma separated list of account IDs here
      .forDateRange("LAST_MONTH")
      .orderBy("Clicks DESC");

  var results = accountSelector.executeInParallel("processClientAccount", "afterProcessAllClientAccounts");
}



function afterProcessAllClientAccounts(results) {
  var currentSetting = new Object();
  var spreadsheetUrl = "https://docs.google.com/spreadsheets/d/10zZq_...Hqklx3N33L_e9yMnT8apSKH-7ayCeguTYw/edit#gid=0"; // 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);
  
  for (var i = 0; i < results.length; i++) {
    var dataString = results[i].getReturnValue();
    var data = JSON.parse(dataString);
    
    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 processClientAccount() {
    var output = new Array();
    var map = new Object();
  
    var accountName = AdsApp.currentAccount().getName();
    var accountId = AdsApp.currentAccount().getCustomerId();
  
    var experimentSelector = AdsApp.experiments()
       .withCondition("Status = ACTIVE")
       .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 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 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 + "%");
      
      }
    }
  var outputString = JSON.stringify(output);
  return(outputString);
}
    
  

Looks like the script does not load all campaigns in alle accounts, also not when only entered only one account ID in the withIDs filter.