Automating My Newsletter Generation with MailChimp, Google Sheets, and AWS

I am constantly building and launching new projects. One of the many things I evaluate when starting a new project is how I am going to maintain it so that it doesn’t become stale and continues to provide value. For folks to get value out of my project they must be engaged in what I am building. Thus, I must be engaged as well.

Anybody who is an entrepreneur, indie hacker, or side project enthusiast will tell you, launching the product/project is just step one. It is what you do after launching that defines your success. This includes marketing, sales, writing, and continuing to evolve the project. Some folks have deemed this, “always be launching”.

My most recent project, a curated newsletter of cloud content, blockchain basics, and general coding articles, was an idea born over a weekend. I am constantly reading the latest articles surrounding the things I am interested in like AWS, Blockchain, and coding. So naturally, I thought why not share everything I find interesting with those that have similar interests.

I built the initial project with a single Google Spreadsheet to track content, a sign-up form created in MailChimp, and a quick landing page built out with Tailwind CSS. When the newsletter initially launched 500 people signed up for it.

To prove my idea out for the newsletter I did just about everything manually. I added links to a spreadsheet and created new MailChimp campaigns with emails generated by my own two hands. I took the content from the MailChimp campaigns and copied it over to my landing page to keep things fresh there as well.

Start manual but then automate like crazy

I learned a very valuable lesson shortly after launching the Learn By Doing newsletter.

Doing mundane tasks manually is the fastest way to lose interest.

This isn’t to say you shouldn’t start there, you absolutely should do things that don’t scale when first getting started. But, once the idea has been proven to be viable, automate all the things that are not focused on delivering value to your audience.

For me, I thoroughly enjoy writing and curating content for the newsletter. Lucky for me, this is also why folks sign up for my newsletter. They enjoy the curated content and my thoughts on what I found in a given week and I enjoy sharing the content. But, folks that sign up for my newsletter don’t care how it is created or how mundane the tasks are to assemble it week over week.

So creating those MailChimp campaigns, configuring the email templates, and copying HTML over to my landing page are not things that provide value. They are necessary, but they are just the magic behind the scenes to produce a high-quality project.

They are also incredibly mundane to do as a human.

So, I used AWS Lambda, the Google Sheets API, and the MailChimp API to automate the mundane tasks. Freeing me up to focus on what delivers value, curating quality content for my audience.

Automating all the things

There are a few pre-requisites before we dive into the actual code. So, if you are looking to follow along you will need the following things.

• An AWS account is a must have, but you could also sign up for one of the other providers that offer functions as a service similar to AWS Lambda.
• A MailChimp API key, you can generate one by following the documentation.
• To schedule a MailChimp campaign tied to a particular email list you are going to need the unique id of that list. This Mailchimp document tells you where to find it .
• We are going to be using the google-spreadsheet NPM package to access our Google Sheet of content. This requires us to authenticate with Google using a Service Account. Click through to the NPM package, scroll down to “Authentication”, and then complete the steps in “Service Account method”.

Alright, got all those pre-requisites taken care of? Let’s dive into some code. You can fork the repository from my GitHub if you would like to follow along with my open sourced code.

{% github https://github.com/kylegalbraith/newsletter-automation %}

Leveraging Serverless Framework

In recent posts, I have talked a lot about leveraging Infrastructure-as-Code (IAC) to represent our AWS resources. There is a lot of benefits to this practice. My personal favorite is not having to click buttons. Nothing will break something faster than having to do countless manual steps multiple times. Represent those buttons clicks in your IAC template and never manually provision an AWS resource again.

Recently I have been talking a lot about Terraform and it is definitely one of my favorites. But Hashicorp is not the only player in the cloud provisioning space.

I took the opportunity to learn a new framework, Serverless while building a solution to my mundane task problem. This post isn’t a deep dive into the framework by any means, so if you are looking to learn more of the details, check out their exquisite documentation.

To install Serverless we can run a single global NPM install from our command line and confirm it works by doing a version check.

kylegalbraith:$ npm install -g serverless
...
...
kylegalbraith:$ serverless version
1.27.3

With the Serverless framework installed globally, we can now provision the infrastructure for our Lambda function to interact with Google Sheets and MailChimp. This is done with the serverless.yml file from the GitHub repo.

service: sheets-automation
provider:
  name: aws
  runtime: nodejs8.10
  region: us-west-2

functions:
  generate-next-newsletter:
    handler: handler.generateNewsletter
    events:
      - schedule: cron(0 19 ? 1-10 THU *)
    environment:
      googleSheetId: <your-google-sheet-id>
      mailChimpKey: <your-mailchimp-api-key>
      mailchimpListId: <your-mailchimp-unique-list-id>

This Serverless YAML is defining a function, generate-next-newsletter, with a handler that is specified in the handler.js file with a function called generateNewsletter.

The event that triggers this function will be a CloudWatch rule that runs on a CRON schedule that is defined here as cron(0 19 ? 1-10 THU *), or better known as every Thursday at 12:00 PM Pacific Time.

Lastly, there are three environment variables defined for the function, googleSheetId, mailChimpKey, and mailChimpListId. These represent the unique id for our newsletter Google Sheet (found in the url when viewing the sheet), our secret MailChimp API key, and our unique MailChimp list id.

Now with the Serverless template saved and while we are in the directory, we can run the following command to deploy our infrastructure.

kylegalbraith:$ serverless deploy

The code that turns the wheels

The infrastructure at this point is provisioned. We have a Lambda function with our environment variables defined that is triggered by a CloudWatch rule. Now, we need to configure that Lambda function to actually make our lives easier.

To do that we can take a look at handler.js that serves as the entry point for our Lambda function.

'use strict'
var GoogleSpreadsheet = require('google-spreadsheet')
var Mailchimp = require('mailchimp-api-v3')

var SheetRepository = require('./repository/sheetRepository')
var MailchimpEmailTemplate = require('./templates/mailchimpEmailTemplate')
var NewsletterGenerator = require('./generators/newsletterGenerator')
var MailchimpDelivery = require('./delivery/mailchimpDelivery')

module.exports.generateNewsletter = (event, context, callback) => {
  let sheetId = process.env.googleSheetId
  let mailchimpApiKey = process.env.mailChimpKey
  let mailchimpList = process.env.mailchimpListId

  var sheetRepo = new SheetRepository(new GoogleSpreadsheet(sheetId))
  var newsletterGenerator = new NewsletterGenerator()

  sheetRepo.loadLatestSheet(function (data) {
    var mailChimpMarkup = newsletterGenerator.generateMarkup(
      new MailchimpEmailTemplate(),
      data.results,
      data.volume,
      data.para1,
      data.para2,
      data.para3,
    )

    createNewMailchimpTemplate(mailchimpApiKey, mailChimpMarkup, mailchimpList, data.volume, data.subject)
      .then((response) => {
        callback(null, response)
      })
      .catch((err) => {
        callback(err)
      })
  })
}

function createNewMailchimpTemplate(mailChimpKey, markup, listId, volume, subjectText) {
  var mailChimpClient = new MailchimpDelivery(new Mailchimp(mailChimpKey))
  return mailChimpClient.saveTemplate(`Learn By Doing Volume ${volume}`, markup).then((data) => {
    return mailChimpClient.createRegularCampaign(
      listId,
      `Weekly Learn AWS Newsletter [Vol ${volume}]`,
      `[Learn By Doing] Volume #${volume}: ${subjectText}`,
      data.id,
    )
  })
}

Taking it from the top we see that we are leveraging the google-spreadsheet and mailchimp-api-v3 NPM packages in order to interact with our Google Sheet and MailChimp campaigns.

We then have four classes defined. Diving deep into each of those is beyond the scope of this blog post, but here is a synopsis of what each of these is responsible for in our workflow.

• SheetRepository is the class that is responsible for interacting with our Google Sheet. If you take a look at the source code you will see the function loadLatestSheet() that is loading the rows from the newest spreadsheet for our newsletter. Important note: The repository is calling useServiceAccountAuth on the google-spreadsheet package. This authentication is using a g-auth.json file that you download when you provision your service account following the directions earlier. Never check this file into source control as it contains sensitive keys.
• MailChimpEmailTemplate is the class that contains the HTML template for our MailChimp campaign (source code). This template contains special tags like #replaceCloud#, these are used in the generator to feed content from the Google Sheet into the HTML template.
• NewsletterGenerator is responsible for actually taking the data in our Google Sheet and feeding it into our HTML template (source code). The key function is generateMarkup which takes the HTML template, our content from Google Sheet, and for my use case some extra parameters like Newsletter volume and paragraph content to feed into the template.
• MailchimpDelivery is an abstraction of the MailChimp API that contains the methods needed to save a new email template and create a regular campaign. (source code).

That is the abstractions for our automated newsletter generation. We load the data for each section of the newsletter from a Google Sheet with the following layout.

If we were to dive into SheetRepository.loadLatestSheet() we would see that it is parsing the rows in this Google Sheet and returning them as an array of objects.

Now, let’s dig into how everything is stitched together in the Lambda function to make it all happen.

module.exports.generateNewsletter = (event, context, callback) => {
  let sheetId = process.env.googleSheetId
  let mailchimpApiKey = process.env.mailChimpKey
  let mailchimpList = process.env.mailchimpListId

  var sheetRepo = new SheetRepository(new GoogleSpreadsheet(sheetId))
  var newsletterGenerator = new NewsletterGenerator()

  sheetRepo.loadLatestSheet(function (data) {
    var mailChimpMarkup = newsletterGenerator.generateMarkup(
      new MailchimpEmailTemplate(),
      data.results,
      data.volume,
      data.para1,
      data.para2,
      data.para3,
    )

    createNewMailchimpTemplate(mailchimpApiKey, mailChimpMarkup, mailchimpList, data.volume, data.subject)
      .then((response) => {
        callback(null, response)
      })
      .catch((err) => {
        callback(err)
      })
  })
}

Remember in the serverless.yml file we declared the handler for the Lambda function we defined to be handler.generateNewsletter, this is the entry point we see here with module.exports.generateNewsletter.

Every Lambda function invocation contains an event, context, and callback parameter. The event parameter we are not making use of here, but this parameter contains the event information that triggered our function. The context parameter contains information about the current invocation of the function. The callback parameter as we see here is used to indicate whether the invocation was successful, callback(null, response), or failed callback(err).

Diving into the generateNewsletter function, we begin by grabbing the environment variables passed in that we defined in our Serverless template. The sheetId is then used to create a new GoogleSpreadsheet client that is passed into the SheetRepository for accessing the Sheet of content.

We then call sheetRepo.loadLatestSheet() and give it a callback function to send data back to. The callback is used because the google-spreadsheet package doesn’t currently support promises. The data returned from the sheet repository, as we see, is in the following format:

{
    volume: '<current-newsletter-volume>',
    para1: '<first-paragraph-content-for-newsletter>',
    para2: '<second-paragraph-content-for-newsletter>',
    para3: '<third-paragraph-content-for-newsletter>',
    results: [
        volume: '<current-newsletter-volume>',
        category: '<category-of-newsletter-articles>',
        articles: [
            title: '<newsletter-article-title>',
            link: '<newsletter-article-link>',
            text: '<newsletter-article-text>'
        ]
    ]
}

The data loaded from Google Sheet is then fed to the NewsletterGenerator.generateMarkup() function. It takes the results array that contains the various newsletter categories and their accompaning articles. In addition it also takes the volume number for this iteration of the newsletter and the paragraphs of introduction text I have written.

What is returned from the function is a string that contains all of the MailChimp markup necessary to create a new email template. Once the markup is crafted a call is made to the helper function createNewMailchimpTemplate.

function createNewMailchimpTemplate(mailChimpKey, markup, listId, volume, subjectText) {
  var mailChimpClient = new MailchimpDelivery(new Mailchimp(mailChimpKey))
  return mailChimpClient.saveTemplate(`Learn By Doing Volume ${volume}`, markup).then((data) => {
    return mailChimpClient.createRegularCampaign(
      listId,
      `Weekly Learn AWS Newsletter [Vol ${volume}]`,
      `[Learn By Doing] Volume #${volume}: ${subjectText}`,
      data.id,
    )
  })
}

This function makes a call to MailchimpDelivery.saveTemplate() to save a new email template that can later be leveraged in a campaign. Once the template is saved, a Regular Mailchimp campaign can be created which is what we see here with the call to createRegularCampaign().

A regular campaign will be created for the email list id that has been passed in. That campaign will have the email subject of [Learn By Doing] Volume #${volume}: ${subjectText} and it will use the email template that was just created. When the campaign is successfully created we signal success back in the main handler by calling our callback function, callback(null, response).

Wrapping up

This has been a much longer blog post than I typically write, but I believe this post really highlights a key concept for something like AWS Lambda. Is this the most elegant solution to the problem, not at all. Could I have written better code? Absolutely. But neither one of those is the point.

The point is that a cloud provider gives both developers and businesses an incredible amount of agility and can dramatically increase effectiveness. They allow you to iterate and develop new ideas quickly. A service like AWS Lambda allows you to write code like this that automates a task that was taking anywhere from 4-6 hours a week to absolutely nothing.

My agility has been elevated by a tool like AWS. Instead of spending 4-6 hours a week clicking buttons, a Lambda runs and everything goes where it needs to go when it needs to go there with no intervention needed from me. I can focus on the things that bring value, curating high-quality content for my subscribers.

I understand that the use-case is simple here, but I can assure you the background concept is applicable to hundreds, if not thousands, of use cases. Start freeing up your time to focus on the things that matter to you and your users by automating away the things that don’t, but are necessary, by spending a few hours writing a Lambda or two.