Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Create a Google Ads Experiments dashboard for multiple accounts in an MCC
// 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
/* 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()
.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);
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.