Keep track of campaign experiments

Create an overview of all experiments that run in your accounts. If you have experiments running in multiple accounts, this script offers quick and easy insight.

Start Now!
Keep track of experiments in Google Ads
Keep track Get started!

For implementing smart bidding strategies in Google Ads accounts, it is wise to always test in advance what the influence of the strategy is on the results in campaigns. You can easily set up an experiment for this. With this you know for sure that Google's smart bidding will also deliver better results. Or not. If you have tests running in multiple accounts, you can easily view the results of these tests using this script.

Author
Difficulty

Category
Google Ads Scripts

Tags

All experiments in one sheet

This script collects the data from all current and completed experiments in the accounts in your MCC. The data from the experiment versus that from the control campaign are included in a sheet. With that you have the results of all accounts together and you can easily monitor the progress of your experiments. In addition, you can draw your own conclusions about certain smart bid strategies across the various accounts.

Settings

  • LOG: Change this to 'true' to see what exactly happens. Leave it on 'false' when the script is really running, because that goes faster.
  • ACCOUNT_LABEL: Use a label to select specific accounts.
  • SPREADSHEET_URL: Make a copy of this spreadsheet and use the new URL: https://docs.google.com/spreadsheets/d/1eO3bhvP41lmyuHUliwDNezATLE7Fg2SIWN0ZpuEOGqY/copy.
  • SHEET_NAME: Leave it on 'Experiments' unless you want to change that name in the spreadsheet.

Source: Optmyzr

The script 
// 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
//
// September 2019
//
// Little tweaks by Tibbe van Asten
// Last update: 16-10-2022
//
////////////////////////////////////////////////////////////////////

var config = {
	
  LOG : false,
  
  // Use accountlabels to select specific account. Leave empty to select all account
  // A script can loopt through 50 account maximum
  ACCOUNT_LABEL : 'Active',
  
  // Make a copy of this spreadsheet: https://docs.google.com/spreadsheets/d/1eO3bhvP41lmyuHUliwDNezATLE7Fg2SIWN0ZpuEOGqY/copy
  SPREADSHEET_URL : 'XXX',
  SHEET_NAME : 'Experiments'
  
}

////////////////////////////////////////////////////////////////////

function main() {
  
  var accountSelector = AdsManagerApp
    .accounts()
    .withLimit(50);
  
  if(config.ACCOUNT_LABEL != ""){
    accountSelector = accountSelector.withCondition("LabelNames CONTAINS '" + config.ACCOUNT_LABEL + "'");
  }

  accountSelector.executeInParallel("processClientAccount", "afterProcessAllClientAccounts");
  
} // function main

////////////////////////////////////////////////////////////////////

function afterProcessAllClientAccounts(results) {
  
  var currentSetting = new Object();
  var spreadsheetUrl = config.SPREADSHEET_URL;
  var sheetName = config.SHEET_NAME;
  
  var spreadsheet = SpreadsheetApp.openByUrl(spreadsheetUrl);
  var sheet = spreadsheet.getSheetByName(sheetName);
  
  sheet.clear();
  var sheetHeaders = new Array("Account Name", "Campaign", "Experiment Name", "Experiment Type", "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);
    
    if(data !== null){
      for(var n = 0; n< data.length; n++) {
        var row = data[n];

        sheet.appendRow(row);
      }
    }
    
  } // resultsIterator
  
  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%");

  // Format header
  sheet.getRange(1,1,1,sheet.getLastColumn()).setBackground("#ac3c73");
  sheet.getRange(1,1,1,sheet.getLastColumn()).setFontColor("#ffffff");
  sheet.getRange(1,1,1,sheet.getLastColumn()).setFontWeight("Bold");
  sheet.getRange(1,1,1,sheet.getLastColumn()).setVerticalAlignment("Middle");

  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);
  sheet.getRange(sheet.getLastRow(),1,1,sheet.getLastColumn()).setFontFamily("Roboto");
  
} // function afterProcessAllClientAccounts

////////////////////////////////////////////////////////////////////

function processClientAccount() {
    var output = new Array();
    var map = new Object();
  
    var accountName = AdsApp.currentAccount().getName();
    var accountId = AdsApp.currentAccount().getCustomerId();
  
    var experimentIterator = AdsApp
      .experiments()
      .orderBy("campaign_experiment.start_date DESC")
      .get();
  
    var i = 0;

    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(experiment.getExperimentCampaign().bidding().getStrategy()){
        var experimentType = experiment.getExperimentCampaign().bidding().getStrategy().getType();
      } else {
        var experimentType = "UNKNOWN";
      }
      
      // Get enddate
      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();
    
      // Get basecampaign of experiment
      var baseCampaign = experiment.getBaseCampaign();
      var baseCampaignId = baseCampaign.getId();
      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();
    
      if(config.LOG === true){
        Logger.log(baseCampaignName+ " " + experimentName + " " + experimentStartDate + " | " + experimentStatus);
      }
    
      if(experimentType != "MANUAL_CPC" && experimentType != "MANUAL_CPM" && experimentStatus != "UNABLE_TO_CREATE"){
        
        if(!map[baseCampaignName]) {
          map[baseCampaignName] = new Object();
          map[baseCampaignName].id = baseCampaignId;
          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].type = experimentType;
        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;
       
      } // target spend
      
    } // experimentIterator  
  
    // Iterate through map
    for(var baseCampaignName in map) {
      var baseCampaign = map[baseCampaignName];
      var baseCampaignName = baseCampaign.name;
      
        if(config.LOG === true){
          Logger.log(accountName + " " + baseCampaignName);
        }
    
      for(var experimentId in map[baseCampaignName].experiments) {
        var experiment = map[baseCampaignName].experiments[experimentId];
        
        var experimentName = experiment.name;
        var experimentType = experiment.type;
        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, baseCampaignName, experimentName, experimentType, 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);
        
        if(config.LOG === true){
          Logger.log("  " + experimentName + " (" + experimentSplit + ") " + baseCampaignClicks + " " + experimentCampaignClicks + " change: " + changeForClicks + "%");
        }
      
      }
    } // baseCampaignIterator
  
  var outputString = JSON.stringify(output);
  return(outputString);
  
} // function processClientAccount
Show whole script!
Loading Comments
The Experts
Tibbe van Asten Team Lead Performance Marketing
Nils Rooijmans Water Cooler Topics
Martijn Kraan Freelance PPC Specialist
Bas Baudoin Teamlead SEA @ Happy Leads
Jermaya Leijen Digital Marketing Strategist
Krzysztof Bycina PPC Specialist from Poland
How about you? JOIN US!
Sharing Knowledge
Caring

Adsscripts.com is all about sharing knowledge. In the current market, PPC specialists like to keep their knowledge and experience to themselves. We're convinced that sharing knowledge can ensure that everyone gets better at their work. We want to change this by sharing our knowledge about scripts with everyone.

Do you also want to contribute? We are open to new ideas and feedback on everything you find on Adsscripts.com.

Contact us

Training &
Workshop Contact us!
Adsscripts Training & Workshop