Using Google Apps and SparkPost for Small Business
My wife Dawna is an (amazingly talented and beautiful) acupuncturist who owns her own business. Every now and then a patient misses an appointment and that translates to lost money and time for her. She can charge people for missed appointments, but it makes for an awkward start to an appointment which is usually a very lovely time together. The other day, she asked me if I knew of any service that would remind her patients about their upcoming appointments in the hopes of preventing no-shows.
I looked up a few services and they were all around $25 to $35 per month, and at the rate that she had no-shows these services would almost (but not quite) pay for themselves. To be fair, these services offered a lot more than sending email but her need was really just to remind her patients about appointments.
Then I stepped back and realized what she was asking for: a way to email patients about their upcoming appointments. Um… I know a little something about sending email. So I set out a goal for myself: could I build an inexpensive email notification system for her small business using Google Apps and SparkPost?
I was able to set this all up pretty quickly. Let’s take a look at how I got this simple notification system wired together and deployed.
The Stack
I chose SparkPost for the email engine, the price was certainly right (not to mention SparkPost is amazing you guys). I priced out some options for hosting and settled on Heroku. I’m comfortable with the command line so this makes deploying simple. With the Heroku Schedule add-on, I could run a script once an hour for pennies a month.
Dawn already uses Google Calendar for her business to keep track of appointments, so I decided to build this new tool in a way that would integrate with her existing workflow without too many changes. I needed to gather some additional information about her patients, including their email address, which could be included in an email template. I chose Google Sheets for this, since it’s a “database” that Dawn is already familiar with (NOTE: if you are storing PHI and are subject to HIPAA compliance, you will need to sign a BAA with Google). This also simplifies the stack: no DB to manage or host and no UI to build on top of it. Google Apps and SparkPost? Match made! But how to integrate all these services?
Setting Up a Google Service User
This was easily the most time-consuming part for me, mostly because of the trial and error and research required. Google offers several authentication mechanisms and not every API supports all the types of auth. I settled on creating a service account to access the calendar and spreadsheet. A service account is basically an email address that behaves like a user on your behalf. It’s perfect for this use case of server-to-server communication.
To create a service account, go to https://console.developers.google.com and create a new project.
Then create a service account and and a private key for that account. You’ll want that key for later.
And there you go, now you have your own phantom Google user!
Your service account has an email address associated with it and you can share your Google Calendar and Sheet with that account by using its email address just like any human user. I only gave the account view permissions because this app won’t need to write any data.
Setting Up SparkPost
If you don’t have a SparkPost account, sign up for one. You’ll need to create an API key with “Transmissions Read/Write” permission and set up and verify your sending domain, if you don’t already have one. I also wrote a (very) simple email template. Easy peasy lemon squeezy!
So How Does It Work?
Once deployed, the app will wake up once an hour, pull in upcoming events from the calendar and pull data from the spreadsheet. Any events with a name that ends with a unique recipient ID are merged with the corresponding row of spreadsheet data. That data is passed through a series of transforms which can annotate the recipient data to make processing easier later. Finally that annotated data is passed along to SparkPost to send.
Here’s what that flow looks like in code:
The heart of the getData function is merging event data with spreadsheet rows. The rows are represented as key-value pairs. In this function we test to see if any of the events in our calendar match a unique ID given to each row. If we find a match, we merge the data together.
In other words, the function takes this spreadsheet data:
And this calendar event (notice the same row ID in the title):
And recognizes that they go together. That merge data is passed through a pipeline of transforms that can annotate each row of data with new properties or filter out rows that are “uninteresting.” Those transformed rows are passed on to the SparkPost wrapper, which sorts and groups the data into recipients, then sends an email. The bulk of the work happens in these two methods:
The most interesting (to me) line here is this one:
This means the substitution data passed to the SparkPost template is whatever we entered into the spreadsheet. So we can just add columns willy nilly and with no code changes that data can be used in our SparkPost template!
In case you are visual (like me), that code is taking this SparkPost Template (remember when I said this template was very simple? I wasn’t exaggerating.):
And taking the data from the spreadsheet we just saw and sending this email in Jerry’s inbox:
Mission accomplished with Google Apps and SparkPost!
We set up an inexpensive notification system using Google Apps and SparkPost! It’s flexible and can adapt as Dawn’s business grows. I encourage you to dig around in the code. While this project is pretty specific to Dawna’s use case, it would be easy to fork and make it applicable to whatever you need. Or maybe it will be a nice jumping off point for something totally new! Have a look around and let me know what you come up with. Happy coding!
-Cole
ps: Have questions about Google Apps and SparkPost? Or other integrations? Come chat in our community slack channel!
The post Google Apps and SparkPost: Building a Notification System for Small Business appeared first on SparkPost Email Deliverability Blog.