Thanks!

You are now subscribed to our monthly blog digest. Happy reading!

Click anywhere to dismiss ...

Behind the Scenes at Jungle Disk - Tracking Email Deliverability Over Time with Node.js, Mailgun, and Google Sheets

The ability and reliability of communications is an extremely vital part of running any type of subscription business. For many, if not all, SaaS (software as a service) businesses, the primary channel of communication with customers is email. So, ensuring that customers actually receive these communications is a top priority; whether the content of the communication is a newsletter, announcement, password reset or billing information.

At Jungle Disk, we use Mailgun to send these “transactional” email communications and to track the deliverability of the email we send. Mailgun is a superb email service, but has limitations on the length of time that logs and event information is available, 30 days to be exact. So, in order to track our deliverability over time, we make use of Mailgun’s API to record historical data about the emails we send. This allows us to have a longer term view on the performance of our communications over the statistics that Mailgun already provides.

Tracking Email Events with Mailgun

There are several specific email events that we track: * Delivered - This event is logged when an email is successfully accepted by the recipient’s mail server. * Failed - This event is logged when an email cannot be delivered to the intended recipient, this can be a temporary or permanent failure. * Unsubscribes - This event is logged when a recipient clicks the unsubscribe link in an email. * Complaints - This event is logged when a recipient uses their email client or email provider to report an email as spam.

Using Node.js and Google Sheets to Track Deliverability Over Time

So, to get a deeper look at the deliverability of our email communications, I wrote up a fairly simple script that queries the Mailgun API for all email events for the billing-related emails that we send to customers and writes out the results to a Google Sheet. This script is run every morning.

This guide assumes some knowledge of Javascript, Node.js, and NPM. We’ll walk through the script piece by piece and I’ll expand on the purpose and functionality of each section.

In this block, we’re including external libraries to expand the functionality of our script. The top section is included to authenticate with Google and utilize the Google Sheets API, which allows us to programmatically write our data to a Google Sheet. Lastly, we include Mailgun’s official API client for Node.js and Underscore, an extremely useful library that extends the Javascript language and makes it much easier to work with objects and arrays.

const fs = require('fs');
const readline = require('readline');
const google = require('googleapis');
const OAuth2 = google.auth.OAuth2;

const mailgun = require('mailgun.js');  
const _ = require('underscore');

Next, we define some additional constants for the script, and set up a query object that will be used to narrow the results we get from Mailgun. Our example is pretty basic, but could become quite granular if needed.

const MG_DOMAIN = 'customers.jungledisk.com';
const FAILED_EMAIL_SUBJECT = 'Problem processing your Jungle Disk Purchase';
const FAILED_EMAIL_FROM = 'billing@jungledisk.com';

// specify what to query when calling the Mailgun API
const QUERY = {
    "subject": FAILED_EMAIL_SUBJECT,
    "from": FAILED_EMAIL_FROM,
    "limit": 300
}

Next, we instantiate the Mailgun API client and some global variables. Obviously, you would need to use your own API key.

var mg = mailgun.client({
    username: 'api',
    key: process.env.MAILGUN_API_KEY || <APIKEY>
});

var oauth2Client,
    numPages = 0;

In this first function, we’re authenticating with Google Sheets API using OAuth 2. This is a little out of scope for this post, but you can learn more here.

function startAuthFlow() {
    // Read Google keys and instantiate OAuth client
    fs.readFile('client_secret.json', function (err, content) {
      if (!err) {
        var secrets = JSON.parse(content);
        oauth2Client = new OAuth2(
            secrets.installed.client_id,
            secrets.installed.client_secret,
            secrets.installed.redirect_uris[0]
        );
      }
    });

    // Check for existing OAuth tokens
    fs.readFile('.credentials/google_creds.json', function(err, tokens) {
    if (err) {
        // No tokens/expired tokens
        getAndStoreTokens(oauth2Client);
    } else {
        // we have tokens, set them and move on
        oauth2Client.setCredentials(JSON.parse(tokens));
        google.options({auth: oauth2Client});

        // start querying the mailgun api
        startMailgunFlow(QUERY);
    }
  });
}

// This is only called if the OAuth tokens are expired or non-existent
function getAndStoreTokens(oauthclient) {
  var authUrl = oauth2Client.generateAuthUrl({access_type: 'offline', scope: 'https://www.googleapis.com/auth/spreadsheets'});
  // print URL to the screen
  console.log(authUrl);

  // use readline to accept input from terminal
  var rl = readline.createInterface({ input: process.stdin, output: process.stdout });

  rl.question('Enter the code from the page above: ', function(code) {
    rl.close();
    oauth2Client.getToken(code, function(err, tokens) {
      if (!err) {
        oauth2Client.setCredentials(tokens);
        google.options({auth: oauth2Client});
        fs.writeFile('.credentials/google_creds.json', JSON.stringify(tokens));
        console.log('OAuth Tokens are now written and stored');

        // Got our tokens, start querying Mailgun API
        startMailgunFlow(QUERY);
        }
    });
  });
}

Next, we query the Mailgun Events API and pass the response data to another function that pushes that data onto an array containing all of the data that we’ll be woking with.

function startMailgunFlow(query) {
  mg.events.get(MG_DOMAIN, query)
    .then(data => processPage(data))
    .catch(error => console.log(error));
}

var allData = [];

function processPage(data) {
  if (data.items.length > 0) {
    data.items.forEach(val => allData.push(val));
    numPages++;
    console.log("Got results for page " + numPages + ". Total result count is currently: " + allData.length);
    startMailgunFlow({page: data.pages.next.number});
  } else {
    console.log("Done - Number of Pages: " + numPages + " Number of records: " + allData.length + "!");
    doAnalysis(allData);
  }
}

Below is the bulk of the work this script will do. We’re passing the raw data returned from Mailgun to this function in order to break out metrics/stats for the whole 30-day period and for the current date. Finally, we pass these off to another function that will write the data to a Google sheet.

function doAnalysis(dataSet) {
  //group all events by messageid and only take the latest event for each
  var messages = _.chain(dataSet)
    .groupBy(msg => msg.message.headers['message-id'])
    .mapObject(msg => _.max(msg, 'timestamp'))
    .value();

  // count of all event types
  var allDays = _.countBy(messages, msg => msg.event);

  // count of all mail sent
  var msgCounts = allDays.delivered + (allDays.failed || 0);

  // today's date
  var reportDate = _.chain(messages).groupBy(msg => new Date(msg.timestamp * 1000).toLocaleDateString()).map((msg,day) => new Date(day)).max().value().toLocaleDateString();

  // earliest date in mailgun
  var firstDate = _.chain(messages).groupBy(msg => new Date(msg.timestamp * 1000).toLocaleDateString()).map((msg,day) => new Date(day)).min().value().toLocaleDateString();

  // summary of email sent today
  var today = _.chain(messages)
    .groupBy(msg => new Date(msg.timestamp * 1000).toLocaleDateString())
    .map((msg, day) => [day, _.countBy(msg, 'event')])
    .sortBy(day => new Date(day[0]))
    .value();
  today = today[today.length - 1][1];

  // total number of emails sent today
  var todayCounts = today.delivered + (today.failed || 0);

  // prep all basic stats for google sheet
  var stats = [reportDate, firstDate, reportDate, allDays.delivered, allDays.failed || 0,
    allDays.complained || 0, allDays.unsubscribed || 0,
    msgCounts, (allDays.delivered/msgCounts).toFixed(4),
    today.delivered, today.failed || 0, today.complained || 0, today.unsubscribed || 0,
    todayCounts, (today.delivered/todayCounts).toFixed(4)];

  // get the detailed reason for email failure when failure is permanent
  var failureDetails = _.chain(messages)
    .where({severity: 'permanent'})
    .groupBy(msg => new Date(msg.timestamp * 1000).toLocaleDateString())
    .mapObject((msg, day) => _.groupBy(msg, msg => msg.recipient))
    .mapObject((recipient, day) => _.mapObject(recipient, (data, email) => data[0]['delivery-status'].message || data[0].reason))
    .map((reasons, day) => [day, reasons]).sortBy(day => new Date(day[0])).max(day => new Date(day[0]))
    .value();

  // prep failed email detail for google sheets
  failureDetails[1] = _.map(failureDetails[1], (val, key) => [val, key]);
  failureDetails = _.each(failureDetails[1], list => list.unshift(reportDate));

  // Counts of bounces, complaints, unsubscribes, and other failures
  var failureStats = _.chain(messages).where({severity: 'permanent'})
    .groupBy(msg => new Date(msg.timestamp * 1000).toLocaleDateString())
    .mapObject((msg, day) => _.groupBy(msg, msg => /^suppress-\w+/.test(msg.reason) ? msg.reason : 'other'))
    .mapObject((reason, day) => _.countBy(Object.keys(reason)))
    .map((reasons, day) => [day, reasons])
    .sortBy(day => new Date(day[0]))
    .max(day => new Date(day[0]))
    .value();

  // prep failure stat data for google
  failureStats = [failureStats[0], failureStats[1]['suppress-unsubscribe'] || 0, failureStats[1]['suppress-bounce'] || 0,
   failureStats[1]['suppress-complaint'] || 0, failureStats[1]['other'] || 0]

  // good to write data to sheet...
  sendData(stats, failureDetails, failureStats);
}

This final function just takes the data that we’ve processed and sends it onto a Google Sheet.

function sendData(stats, failDetail, failStats) {

  if (stats.length > 0 && failDetail.length > 0) {
    var sheets = google.sheets('v4');

    // Update the Basics
    sheets.spreadsheets.values.append({
      "spreadsheetId": "<spreadsheet-id>",
      "range": "Sheet2!A:O",
      "valueInputOption": "USER_ENTERED",
      "resource": {
        "values": [stats]
      }
    }, function(err, res) {
      if (err) {
        return console.log(err);
      }
      return console.log(res.updates);
    });

    // Update the Failures
    sheets.spreadsheets.values.append({
      "spreadsheetId": "<spreadsheet-id>,
      "range": "Sheet3!A:C",
      "valueInputOption": "USER_ENTERED",
      "resource": {
        "values": failDetail
      }
    }, function(err, res) {
      if (err) {
        return console.log(err);
      }
      return console.log(res.updates);
    });

    // Update failure stats
    sheets.spreadsheets.values.append({
      "spreadsheetId": <spreadsheet-id>,
      "range": "Sheet3!G:K",
      "valueInputOption": "USER_ENTERED",
      "resource": {
        "values": [failStats]
      }
    }, function(err, res) {
      if (err) {
        return console.log(err);
      }
      return console.log(res.updates);
    });

  } else {
    console.log('something went wrong');
  }
}

Lastly, when we actually put all of this together and execute it, we have to execute something, so we start with the authentication with Google, and everything else flows from there:

startAuthFlow();

Certainly, this isn’t the prettiest script ever written, but it does the job of gathering and processing email deliverability data quite well.

Protect Your Business Data

We are passionate about helping our customers protect their data. We want you to use Jungle Disk to protect yours. Click on Sign Up to get started. It takes less than 5 minutes!

Sign Up