Stern Learning Science Lab have long been using Mailchimp to manage their daily email marketing campaigns. However, it is hard to find out insights on content creation from the data Mailchimp provided: the platform only gives some of the indicator of the performance to each single campaign; If the marketing manager wants to run some more complicated analysis, he has to download the performance every week to make charts and compare.
Except for the email campaign analysis needs, LSL is also looking for a new way to visualize internal performance data such as office hour attendance, workshop feedbacks, and so on. They wanted to know more about who are their audience, and how the staff perform to serve the Stern community. Previously, the department normally record these data in a lot of separate spreadsheets, which makes managing and organizing a pain for managers.
I started the project by talking with the marketing manager and other stakeholders. What I learned is that first, there is a lot of data available. They keep a good record of their workshop attendance, feedbacks. Second, the data sources are varied; the department normally record the data in a lot of separate spreadsheets, or generating from third-party platforms like Mailchimp. Third, the data does not have an automated updating method, it makes keeping the datasets up-to-date a burden of handwork for the department.
What LSL is looking for is a one-place-for-all solution, where they can easily make use of the data they own and gain insights from it, and also they are looking for a solution with automation to free their labor from updating dataset from time to time.
To combine the varied data source, I first proposed to the team that using Google Spreadsheet and Data Studio as our primary dataset management solution because many of our current dataset is stored as a Google Spreadsheet or could be transformed as a Google Spreadsheet. And we also constantly using Google Form to collect feedbacks for our workshops, so it could be a good place to accomplish our automation.
Then my first challenge is to extract the email campaign data from Mailchimp to Google Spreadsheet with Mailchimp API and Google App Script. I also changed attribute names in this step.
var API_KEY = 'APIKEY';
var LIST_ID = ' LISTID';
/********************************************************************************
* call the Mailchimip API to get campaign data
* This gets all campaigns in an account
*/
function mailchimpCampaign() {
// URL and params for the Mailchimp API
var root = '<https://us20.api.mailchimp.com/3.0/>';
var endpoint = 'campaigns?count=500';
// parameters for url fetch
var params = {
'method': 'GET',
'muteHttpExceptions': true,
'headers': {
'Authorization': 'apikey ' + API_KEY
}
};
try {
// call the Mailchimp API
var response = UrlFetchApp.fetch(root+endpoint, params);
var data = response.getContentText();
console.log(data);
var json = JSON.parse(data);
// get just campaign data
var campaigns = json['campaigns'];
// blank array to hold the campaign data for Sheet
var campaignData = [];
// Add the campaign data to the array
for (var i = 0; i < campaigns.length; i++) {
// put the campaign data into a double array for Google Sheets
if (campaigns[i]["emails_sent"] != 0) {
campaignData.push([
campaigns[i]["settings"]["title"],
campaigns[i]["settings"]["subject_line"],
campaigns[i]["send_time"].substr(0,10),
(campaigns[i]["report_summary"]) ? campaigns[i]["report_summary"]["unique_opens"] : 0,
(campaigns[i]["report_summary"]) ? campaigns[i]["report_summary"]["open_rate"] : 0,
(campaigns[i]["report_summary"]) ? campaigns[i]["report_summary"]["opens"] : 0,
(campaigns[i]["report_summary"]) ? campaigns[i]["report_summary"]["subscriber_clicks"] : 0,
(campaigns[i]["report_summary"]) ? campaigns[i]["report_summary"]["click_rate"] : 0,
(campaigns[i]["report_summary"]) ? campaigns[i]["report_summary"]["clicks"] : 0,
]);
}
else {
campaignData.push([
campaigns[i]["settings"]["title"],
campaigns[i]["settings"]["subject_line"],
"Not sent",
"N/a",
"N/a",
"N/a",
"N/a",
"N/a",
"N/a",
"N/a",
"N/a",
]);
}
}
// Log the campaignData array
Logger.log(campaignData);
// select the campaign output sheet
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName('Campaign Analysis');
// calculate the number of rows and columns needed
var numRows = campaignData.length;
var numCols = campaignData[0].length;
// output the numbers to the sheet (automation starts from row 36, old data 34 rows+ description 1 row)
sheet.getRange(39,1,numRows,numCols).setValues(campaignData);
}
catch (error) {
// deal with any errors
Logger.log(error);
};
}
I mainly perform the transforming and cleaning to the workshop and office hour dataset. I also found the role of the attendee (adjunct professor, faculty, or staff) could bring interesting insights if we combine the datasets, so I use identical email as search key to generate their roles in the booking workshop dataset.
I first present the sketches to the marketing manager to gain the first round of feedbacks. The manager commented on the chart choices and what else data insights they are looking for. Then I prototyped the sketches in the data studio as a high-fidelity prototype, and present to the project manager for another round of feedback.
After the second round of prototyping, I made some final refinements and set up the automation in both App Script (Trigger every 24 hours) and Data Studio(Refreshing the dataset every 24 hours).