loading...
Cover image for Automating My Newsletter Generation with MailChimp, Google Sheets, and AWS Lambda

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

kylegalbraith profile image Kyle Galbraith Updated on ・10 min read

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 logo kylegalbraith / newsletter-automation

An AWS Lambda function that runs on a CRON schedule to grab content from a Google Sheet and generate a new MailChimp campaign. Read the detailed blog post here -> https://dev.to/kylegalbraith/automating-my-newsletter-generation-with-mailchimp-google-sheets-and-aws-lambda-1n11

newsletter-automation

An AWS Lambda function that runs on a CRON schedule to grab content from a Google Sheet and generate a new MailChimp campaign.

Getting Started

  1. You must create a g-auth.json file and add it to the src of this project next to serverless.yml. Instructions on how to create this authentication file for Google Sheets can be found here.
  2. Update serverless.yml with the appropriate environment variables.
environment:
  googleSheetId: <your-google-sheet-id>
  mailChimpKey: <your-mailchimp-api-key>
  mailchimpListId: <your-mailchimp-unique-list-id>
  1. Run npm install from the command line.
  2. Run serverless deploy from the command line.



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 commands to deploy our infrastructure.

kylegalbraith:$ npm install
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.

Learn By Doing Newsletter Google Sheet

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.

Learn AWS by actually using it

If you enjoyed this post and are hungry to start learning more about Amazon Web Services, I have created a new course on how to host, secure, and deliver static websites on AWS! It is a book and video course that cuts through the sea of information to accelerate your learning of AWS. Giving you a framework that enables you to learn complex things by actually using them.

In the course, I dive deep into core AWS services like Lambda, S3, CloudFront, and API Gateway in order to give you the tools you need to actually start learning the platform in a scalable way.

Head on over to the landing page to get a copy for yourself and begin your AWS certification adventure!

Posted on by:

kylegalbraith profile

Kyle Galbraith

@kylegalbraith

Programmer by day and author by night. I am passionate about all things development related, but especially Amazon Web Services. I recently created a course about learning AWS by using it.

Discussion

markdown guide
 

I am fairly new to node.js. If anyone had some quick tips, would appreciate it. I am getting an error loading the handler module. I thought it was a permissions error but I have them as loose as possible. Originally edited on Ubuntu 18.04 before using serverless to push to AWS Lambda. TIA

Unable to import module 'handler': Error
at Function.Module._resolveFilename (module.js:547:15)
at Function.Module._load (module.js:474:25)
at Module.require (module.js:596:17)
at require (internal/module.js:11:18)
at Object. (/var/task/handler.js:2:25)
at Module._compile (module.js:652:30)
at Object.Module._extensions..js (module.js:663:10)
at Module.load (module.js:565:32)
at tryModuleLoad (module.js:505:12)
at Function.Module._load (module.js:497:3)

 

Vincent,

This error is typically seen when running AWS Lambda and you have defined the handler for the Lambda incorrectly. For example handler.js defines the following Lambda entry point:

module.exports.generateNewsletter = (event, context, callback) => {
  //some code
}

Therefore the handler must be defined for the Lambda function as handler.generateNewsletter as you see in the serverless.yml file.

So I suspect the error you are seeing is that either A) You have handler.js but a different name for the function entry point that is not generateNewsletter or B) Your entry point is not in a file called handler.js.

 

Will give this review / fix a try in the next few days. Off to buy your book and videos. May I suggest an AWS Lambda for noobs book next if you are taking votes? Thanks for the excellent fast response!

Best, V.

I am really stumped. I git cloned the repo exactly as is so I don't see why the function names would be messed up. Everything is in its respective original directory/folder location from the original repo. And I verified permissions were readable+writeable by all. Still can't find handler.js which is right there in the same dir as serverless.yml. Node.js is 8.10 and I updated npm to the latest while trying to eliminate factors. I will re-verify everything from the article.

It's got to be something else. I tried a basic 'hello world' exercise from the serverless.com site and get the proper response. Both serverless.yml and handler.js are in the same dir. Similar to this repo when I git clone it. Since I have not changed the name of the function or its placement, I suspect some component of handler.js. Learning more about troubleshooting lambda quickly. To clarify, even though when we download the authentication file from google and a name is assigned, we have to rename it to g-auth.json and have it in the root dir? I have tried it in a few places.

Sounds like you probably didn't run npm install before running serverless deploy. This is required in order to deploy the necessary node_modules that contain the dependencies needed for the Lambda to run. I have updated this blog post to reflect that.

Thanks Kyle. I retried with the npm install and still didn't have any luck. After grubbing around google, I decided to give package.json a look and there were some babel dependencies that crept in. I have no idea how since I git cloned your repo but after doing a diff on the files and replacing with the original code, I have eliminated that error.

Now I am getting a undefined type error when it reads the 'text' column from the spreadsheet. Good crash course on node.js troubleshooting. ;-)

 

Thanks for sharing this, Kyle! Ever since you mentioned it on Twitter, I've been trying to figure out how to automate a lot of my manual tasks. I use Airtable to curate my newsletter. I'm learning AWS and serverless to automate creating the MailChimp campaign. This is really helpful!

 

Thank you for the kind words Veni! This should definitely be applicable to AirTable as well. AWS Lambda/Serverless is fantastic for automating all those manual tasks that suck time from your day.

 

I'm a beginner coder and AWS'er. I run awsbeginner.com. This post was way above my head unfortunately. Maybe after a bunch more practice with coding and learning Lambda, I'll be able to follow along easier.