reporting with sparkpost using node.js

SparkPost is the world’s most powerful email platform. Our engineering team takes advantage of this fact by using SparkPost to power several pieces of functionality within SparkPost itself. One of the areas we use SparkPost is to power our internal reporting. In this post I’ll demonstrate how we use SparkPost to build an email report.

We’re going to build what we refer to as our sending domains report. This report goes to our deliverability team, which ensures we follow best practices to help our customers succeed. We’ll use Node.js, demonstrate some queries in Cassandra, which is where our customer data lives, and pull it all together with the templates and transmissions capabilities of SparkPost.

The content of the email will be a simple heading and table with a row for each record in the data we retrieve from Cassandra. Here we create a template that makes use of the SparkPost template substitution data:

<h2>Sending Domains Report</h2>
{{ if empty(domains) }}
<p>No new sending domains have been created in the past 24 hours.</p>
{{ else }}
<table>
  <tr>
    <th>Customer ID</th>
    <th>Customer Name</th>
    <th>Domain</th>
    <th>SPF Status</th>
    <th>DKIM Status</th>
    <th>Abuse@ Status</th>
    <th>Postmaster@ Status</th>
    <th>Compliance Status</th>
  </tr>
{{ each domains }}
  <tr>
    <td>{{ loop_var.customer_id }}</td>
    <td>{{ loop_var.customer_name }}</td>
    <td>{{ loop_var.domain }}</td>
    <td>{{ loop_var.spf_status }}</td>
    <td>{{ loop_var.dkim_status }}</td>
    <td>{{ loop_var.abuse_at_status }}</td>
    <td>{{ loop_var.pm_at_status }}</td>
    <td>{{ loop_var.comply_status }}</td>
  </tr>
{{ end }}
{{ end }}
</table>

We’ll create a new template with the ID sending-domains-report by going to the SparkPost Templates UI and pasting the HTML content into our template editor:

t

Let’s break down what we’re doing in the template:

<h2>Sending Domains Report</h2>
{{ if empty(domains) }}
<p>No new sending domains have been created in the past 24 hours.</p>

First, we have a heading and an if statement. We use this statement to see if the domains variable is empty (we’ll show you where that comes from later). If it is, the template will just show a friendly message.

If the domains variable is not empty we create a table with a heading row:

{{ else }}
<table>
  <tr>
    <th>Customer ID</th>
    <th>Customer Name</th>
    <th>Domain</th>
    <th>SPF Status</th>
    <th>DKIM Status</th>
    <th>Abuse@ Status</th>
    <th>Postmaster@ Status</th>
    <th>Compliance Status</th>
  </tr>

And finally we loop through each item in the domains variable and render some data:
{{ each domains }}
  <tr>
    <td>{{ loop_var.customer_id }}</td>
    <td>{{ loop_var.customer_name }}</td>
    <td>{{ loop_var.domain }}</td>
    <td>{{ loop_var.spf_status }}</td>
    <td>{{ loop_var.dkim_status }}</td>
    <td>{{ loop_var.abuse_at_status }}</td>
    <td>{{ loop_var.pm_at_status }}</td>
    <td>{{ loop_var.comply_status }}</td>
  </tr>
{{ end }}
{{ end }}
</table>

Now that we have our template, we need to create a Node.js script that queries our Cassandra database and uses node-sparkpost to send the email.

First we have to require some libraries:

var _ = require('lodash')
  , q = require('q')
  , config = require('config')
  , sparkpost = require('sparkpost')
  , cassWrapper = require('@sparkpost/msys-cassandra')
  , db = cassWrapper.getDb(config.cassandra);

Next we create the functions for retrieving data from our sending domains and accounts tables. Note that we created our own wrapper around cassandra-driver to promisify the batch/execute methods and add an executeByStream function that allowed us to stream back more than 5,000 results:
function getSendingDomains() {
  return db.executeByStream('select * from sparkpost.sending_domains', []);
}
 
function getCustomers() {
  return db.executeByStream('select * from sparkpost.accounts', []);
}

We have to do some processing on the data returned from the queries to merge them together:
function processQueries(results) {
  var domainResults = results[0]
    , customerResults = results[1]
    , domains
    , customers;
 
  domains = domainResults.rows;
  customers = customerResults.rows;
 
  // merge the customer name into the domains
  _.forEach(domains, function(domain) {
    var customer = _.find(customers, {customer_id: domain.customer_id});
 
    domain.customer_name = 'NONE'; // defaults to 'NONE'
    if (customer && customer.company_name) {
      domain.customer_name = customer.company_name.toLowerCase();
    }
    delete domain.__columns;
  });
 
  return domains;
}

This function is responsible for sending our message through SparkPost using the node-sparkpost library. We use the config library to set up our connection to Sparkpost, prepare the message meta and substitution data and deliver the message:
function sendMessage(domains) {
  var deferred = q.defer()
    , message = {};
 
  // sparkpost API config
  sparkpost = sparkpost({
    host: config.sparkpost.host,
    protocol: config.sparkpost.protocol,
    port: config.sparkpost.port,
    key: config.sparkpost.apikey
  });
 
  // message meta
  message.campaign = 'sending-domains-report';
  message.from = config.from;
  message.subject = 'Sending Domains Report';
  message.recipients = config.support;
  message.template = 'sending-domains-report';
 
  // set up the substitution data using the data we queried earlier
  message.substitutionData = {domains: _.map(domains['new'], function(domain) {
    return _.pick(domain, ['customer_id', 'customer_name', 'domain', 'spf_status', 'dkim_status', 'abuse_at_status', 'pm_at_status', 'comply_status']);
  })};
 
  sparkpost.transmission.send(message, function(err, response) {
    if (err) {
      deferred.reject(err);
    } else {
      deferred.resolve(response);
    }
  });
  return deferred.promise;
}

Finally we pull it all together into a promise chain and log out the results:
q.all([getSendingDomains(), getCustomers()])
  .then(sendMessage)
  .then(function(result) {
    logger.notice('Message ' + result.results.id + ' sent!', 'Rejected:', result.results.total_rejected_recipients, 'Accepted:', result.results.total_accepted_recipients);
    process.exit(0);
  })
  .fail(function(err) {
    logger.error('Error:', err);
    process.exit(1);
  });

You can view the code in full as a Gist on Github. The end result an email report that looks like this:

reports-email

Internal reporting is only a small sample of how we use SparkPost at SparkPost. In a future post we will dig into how we use SparkPost to power the various emails that are sent to users of SparkPost.

-Rich

Dev Survival Guide Blog Footer