Experimenten in campagnes bijhouden

Maak een overzicht van alle experimenten die in je accounts lopen. Wanneer je experimenten in meerdere accounts hebt lopen, bied dit script snel en eenvoudig inzicht.

Start nu!
Experimenten in Google Ads bijhouden
Experimenten Get started!

Voor het doorvoeren van slimme biedstrategieen in Google Ads accounts is het verstandig om vooraf altijd te testen wat de invloed van de strategie is op de resultaten in campagnes. Daarvoor kan je eenvoudig een experiment opzetten. Hiermee weet je zeker dat de smart bidding van Google ook betere resultaten gaat opleveren. Of juist niet. Wanneer je testen hebt lopen in meerdere accounts, kan je de resultaten van deze testen eenvoudig inzien met behulp van dit script.

Author
Difficulty

Category
Google Ads Scripts

Tags

Alle experimenten in één sheet

Dit script verzameld de data van alle lopende en afgeronden experimenten in de accounts in jouw MCC. De data van het experiment tegenover die van de control-campagne worden in een sheet opgenomen. Daarmee heb je de resultaten van alle accounts bij elkaar staan en kan je eenvoudig de voortgang van je experimenten bewaken. Daarnaast kan je hierin zelf conclusies trekken over bepaalde slimme biedstrategieen over de verschillende accounts heen.

Instellingen

  • LOG: Wijzig dit in 'true' om te zien wat er precies gebeurd. Laat op 'false' staan wanneer het script echt draait, want dat gaat sneller.
  • ACCOUNT_LABEL: Gebruik deze om specifieke accounts te selecteren.
  • SPREADSHEET_URL: Maak een kopie van de volgende sheet en voeg de URL hiervan in: https://docs.google.com/spreadsheets/d/1eO3bhvP41lmyuHUliwDNezATLE7Fg2SIWN0ZpuEOGqY/copy.
  • SHEET_NAME: Laat deze op 'Experiments' staan, tenzij je die naam in de spreadsheet wilt aanpassen.

Bron: 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 staat voor het delen van kennis. In de huidige markt houden SEA-specialisten de kennis en ervaring graag voor zich. Wij zijn er van overtuigd dat het delen van kennis ervoor kan zorgen dat iedereen beter wordt in haar of zijn werk. Daarom lopen wij hier graag in voorop, door onze kennis over scripts te delen met iedereen.

Wil jij ook graag een bijdrage leveren? Wij staan open voor nieuwe ideeën en feedback op alles wat je op Adsscripts.com vindt.

Neem contact op

Training &
Workshop Neem contact op!
Adsscripts Training & Workshop