I find that the most satisfying projects are the ones that solve real-world problems.  They ease the workload and make things more efficient. They leverage existing resources and they don’t cost a lot of cash.  What follows is a peek into one such project that continues to evolve.

A few months ago, our lead messaging engineer Steve Tuck wrote a post on a simple way to leverage Google Sheets with the SparkPost API to create a recipient manager for our stored templates.  This solved an immediate problem as we really only needed to send a couple of hundred emails a month and had zero budget.

When we deployed this it was clear that it was only going to be a short-term fix and eventually we were going to need other helpful bits as well, like the ability to use a WYSIWYG editor, update substitution variables, and report on delivery and engagement. If the goal was to send thousands per month then we would have just used one of our trusted integration partners, but this use case did not warrant it.  What we needed was a relatively easy way to expose our Recipient, Reporting, Template, and Transmissions APIs in a friendly WebUI.

I wrote this in PHP, but that is just a personal preference.  It should be easy to follow along and replicate this in any language. The code snippets I show here are truncated to show only the important bits.  

A quick note on complexity

Whenever you evaluate a project, it is good to consider how much time and effort will be involved and what it will draw from other efforts.  This project has turned into a labour of love for me and has been done completely on my own time outside of standard work hours.  Because of this, it is hard to place a real LOE (Level of Effort) on it.  I can tell you that I binge-watched a full season of “The Good Doctor” while writing the parts discussed here and due to the distractions of the show, much of this is poorly formatted spaghetti-code.  Please forgive me – I promise to clean it up later.  If I were to actually focus and re-write this properly, I doubt it would consume more than a full workday.  I will try to keep better track of future additions.

Where to start?

Since SparkPost already includes a content editor, that part can wait until later.  The real need was for a recipient list editor and a way to generate a transmission that links a template with a recipient list.  This is a lot easier than it sounds and the API has all the functionality built-in already, we just need to make it easy to connect the dots.

The Recipient API allows for an upload or download of an entire list.  There is no edit or filter function, which is not usually an issue because smaller senders typically send to a full list and larger senders will use one of our partners with more comprehensive front ends.  In our case, we do want to send to a full list but need to be able to easily edit it.  All we really need to do is wrap the API in a friendly and simple User Interface.

Like… REALLY simple…

This only really needs a couple of basic functions to start. I dropped a basic version that proves this out in Git here. The basics are…

  1. Show a list of all the lists so you can select one, and 
  2. Offer the ability to upload a new list.

The result of this works looks like this: 

              

A REST GET to the recipient-lists endpoint will provide a JSON reply with all of the available lists, then you can just list them in a standard HTML SELECT. The highlights are below.

$url = "https://".$apidomain."/api/v1/recipient-lists";
  curl_setopt($ch, CURLOPT_URL, $url);
  $response = curl_exec($ch);
  $reciplistArray = json_decode($response,true);

  echo '<p>  <select name="SPRecipients" id="SPRecipients">';
    foreach($reciplistArray as $a=>$b){
      foreach ($b as $c=>$d){
        echo "<option value=$d[id] selected>$d[name]</option>";
      }
    }
  echo '</select><button type="submit">Open</button> &nbsp;</p>';

Once we have selected the list, that is POSTed to another page to load the list for edits.

  $listID = $_POST['SPRecipients'];

  $url = "https://".$apidomain."/api/v1/recipient-lists/".$listID."";
  curl_setopt($ch, CURLOPT_URL, $url);

  $response = curl_exec($ch);

  $recipData = json_decode($response,true);

Now iterate over the resulting array and drop that into a table for edits. To make things super easy, I allowed for the removal and addition of entire data rows so I would not have to manage a local database – this is all done as an array in memory.

You can see in the image above that you can flag an entire data row for deletion (1).  You have text fields to add new data rows (2). When you click [UPDATE] the code will remove any rows marked for deletion and add any rows with new data, then upload the entire array as a new file replacing the old one entirely.

In addition, I added the ability to simply download (4) the existing file so you could drop it into a spreadsheet for more complicated editing.  You can then upload the replacement list when you are done with edits. So now you have the ability to edit a recipient list, and you always had the ability to create and edit templates.  How do we bring those together to create a useful mailer tool?

Follow the Sherpa

Building and managing a customer mailing is a bit of a journey.  To bring it all together I scripted a little thing I called “sherpa” – as in the mountain guide.  

Credit: Photo 210405590 © Lesia Povkh | Dreamstime.com

This is more UI than anything, but providing an easy way to select a template and a recipient list and then scheduling them to fire off a mailing was an important thing to make easy.  And yes, of course, I made a fully functional git repo you can just clone to play with this.

When you click on the [Follow the Sherpa] button, it first allows you to select a template, then a recipient list, and then you can schedule a mailing time and give it a campaign name for tracking later.  Finally it displays a summary you can approve or cancel.

Hitting the [EXECUTE] button there will call the Transmissions API to use the stored template and the stored recipient with the scheduled time list like this:

// Form a JSON request with the current List ID and Template ID
 $json = '{
          "name": "'.$ProjName.'",
          "campaign_id": "'.$Campaign_ID.'",
          "options": {
            "start_time": "'.$dateStamp.'"
          },
          "recipients": {
            "list_id": "'.$SPRecipients.'"
          },
          "content": {
            "template_id": "'.$SPTemplate.'"
          }
        }';

  // Call the Transmissions API 
  curl_setopt($ch, CURLOPT_URL,   "https://$apidomain/api/v1/transmissions");
  curl_setopt($ch, CURLOPT_POSTFIELDS, $json);
  $response = curl_exec($ch);
  $res = json_decode($response,true);
  // Capture the Transmissions ID to use for reporting later
  $TransID = $res['results']['id'];

And that is about it.  The stored template will be sent using the stored recipient list and reporting will be available in the SparkPost reporting UI.  I continue to develop this as there are a few other things I’d like to add.  For instance, using this method, you can only schedule 3 days in advance, and there is minimal control over the schedule.

Security considerations

One thing that was top of mind while writing this is that I did not want to store any of the data in a local system, even for a short time.  All of the list details and template body are stored in SparkPost.  The list editing function is done with an array in memory so as to avoid writing any data to disk. Even the use of the API key can be stored in a Session variable to avoid writing it to disk.

Next Steps

This should be good enough to provide a timed transmission of jobs, but what about reporting, and editing tools?  We’ll save those for a future post.

I’d be interested in hearing any feedback or suggestions for expanding this project.

~ Tom