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.
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.
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.
// 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 AstenTeam Lead Performance Marketing
Nils RooijmansWater Cooler Topics
Martijn KraanFreelance PPC Specialist
Bas BaudoinTeamlead SEA @ Happy Leads
Jermaya LeijenDigital Marketing Strategist
Krzysztof BycinaPPC Specialist from Poland
How about you?JOIN US!
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.