Resolution Reminder App: Achieve Your Goals with Ember

Resolutions are hard. No matter what your goal is, there’s always a brighter, shinier… SQUIRREL!! Oh, sorry! Like I was saying, there’s always something to be distracted by.

Even if you can successfully eliminate the distractions, keeping yourself on task with a new habit can be difficult without accountability. On the other hand, everyone knows that sharing your resolution can leave you open to judgement and ridicule.

I have the perfect solution… a trustworthy source to reveal your resolutions to, who promises to not judge you. Worst case scenario, he’ll send you cute pictures to soften the blow when you’re reminded you have yet to accomplish everything you set out to do. This is our Resolution App Reminder.

Step 1: Find a Dog

No one ever feels judged by a dog! Ok, maybe we do… however I promise this dog will only send helpful, friendly messages to encourage you along your road to successful resolution keeping. If you haven’t heard about Ember before, get to know him (and me) here. Then circle back, secure in the knowledge that he’s not only a good protector of me, but an awesome secret-keeper as well.

resolution app reminder dog ember resolution reminder app

So here’s the deal. You put your resolution and a few other details in Ember’s secret hiding spot (aka fill out this Google Form) and he’ll send you a handful of emails throughout the year. They’ll range from encouraging to motivational to congratulatory, but they’ll always include a picture of him along his own 2017 resolution path. Easy peasy. What do you have to lose? Pick your best resolution (or two), and give them to Ember for safe keeping.

Behind the Curtain

For those of you curious about how we built all of this, It turned out to be a bit more convoluted than I anticipated (turns out there’s no “easy button”). But roll up your sleeves — I’ll walk you through it.

First of all, we (Ember, myself, and Ember’s Dad/my partner Jeremy) decided to go with Python as our language of choice. It’s Jeremy’s strong suit, and given that he was the primary brains behind the app functionality, that made the most sense. He also has experience using the SparkPost Python client library, which is a bonus for this project!

My first task was to set up a sending domain for Ember, since he doesn’t have the opposable thumbs to do it himself.

meme dog computer resolution reminder app

This part is easy for us humans:

  • If you don’t already have a SparkPost account, sign up for one.
  • Next, you’ll want to create an API key with “Transmissions Read/Write” permission
  • Lastly, you’ll need to set up and verify your sending domain, if you don’t already have one on hand

Next, I wrote out all of the steps that we needed the app to take. There are a lot of moving pieces in this process — personalizing data, remote logging into Google Sheets, verifying that emails were sent, etc. I’m a big proponent of knowing the big picture before you tackle a problem, especially one that seems complicated and involves technology that you aren’t particularly familiar with.

First Up: Integrating with Google

First things first, we had to set up a service account to access our spreadsheet for us in the background. Lucky for me (and you), my colleague Cole did the legwork on that for me already in a previous blogpost. Follow along with his directions on how to set up a service account and then come on back here. We promise to be patient.

Google Apps has an extensive API that lets you integrate with everything from Gmail to Calendar, and in our case, Forms and Sheets. There are a lot of moving pieces if you’re not already accustomed to working with this API. However, with the help of Anton Burnashev’s Github repo for interfacing with Google Sheets using Python, we got up and running quickly with the basics.

Once we knew the scope of what we could do, it was time to start applying it to our use case. First up: organizing the data.

Just for You

We set up three separate tabs in the Google Sheet that holds the data from the resolution form you filled out above (you did fill out the form, right?). The first tab is “resolutions” and (unsurprisingly) contains the details for each user who signed up.

The second tab, “messages”, organizes our resolution topics and allows us to personalize the emails. This way, Ember can address your needs directly. After all, not all of you are dedicated to working on your career or improving your love life in 2017. As such, for each resolution topic, Ember will include a picture of him engaging in a related activity to show solidarity. We also wanted to include a phrase that pertained to the resolution topic that you committed to.

Substituting the text and photos is easy enough with SparkPost (you can see how I did it in the welcome email). However, figuring out which of the 30 total photos to programmatically use on each of the emails took a little more thought.

table phrases resolution reminder app

 

For each topic, we created a dictionary with the appropriate topic phrase and photo prefix details. Each of these topic dictionaries then lives within a container dictionary so that we can reference it later on.

In retrospect, this might have been more easily accomplished with a class, but we didn’t do that… so we created a convoluted web of nested dictionaries for your enjoyment.

meme yo dawg resolution reminder app

Scheduled Sends (and why we didn’t use them)

SparkPost has an amazing feature which allows you to schedule your emails to be sent up to 31 days in advance. 😸 Fantastic, isn’t it? The only problem we ran into is that with the exception of the first reminder email, we were looking to schedule things more than 31 days out. Sad trombone.

So, back to square one… time to build date functionality into the app itself. We were basically looking for something similar to a Drip Campaign or a Welcome Series. We wanted you, the user, to get an automated welcome email, as well as periodic emails reminding you that we’re still rooting for you. (Speaking of which, if you’re looking to set up a welcome series for your company, check out our code for a starting place. With a few more calls to the get_dates function, you could set up a fully functioning welcome series based on when your new users sign up.)

Back to our sending mechanism… Remember the third tab that we haven’t talked about yet? This is where we drew the parameters for which email templates get sent when, how we determine which picture to send, and how to mark an email as sent in the “resolutions” tab.

table information resolution reminder app

 

Most of these columns are fairly self-explanatory:

  • Start and End dates indicates the time period in which each email should be sent
  • TemplateID corresponds with the SparkPost template to send at that time
  • The subject column is just for visual reference and isn’t used programmatically. These are all defined in the templates
  • send_id is a little more complicated… we’ll dig into that in a moment

The app steps through the following code, starting by figuring out what the current time is. It then checks the date ranges in the “dates” tab to see which one we’re currently in. Lastly, it returns the template and send_id that correspond with the proper timeframe.

Verify, Verify, Verify

In my opinion, send_id  is where it gets fun. It has two functions:

  1. Determining which picture to send by appending the corresponding number (1-5) to the end of the photo prefix we mentioned earlier
  2. Logging emails as “sent” in the “resolutions” tab

The latter makes it easy for us to verify that you’re getting your emails from Ember on a regular basis. It also keeps us from re-triggering emails accidentally.

As the app steps through the “resolutions” tab, it first checks to see if the cell says Sent. If it doesn’t, it sends the appropriate email based on our calculations above.

Then, if the email send is successful, it updates the sent flag for the appropriate column. It calculates which column to post in using the send_id number as well as the standard column offset of 8 that we established early on in the code.

Sweet, verifiable success.

To ensure said success going forward, we deployed this on a small Amazon instance that runs the script every 5 minutes. You could set up something similar on Heroku, or even a spare Raspberry Pi. Basically… anything that runs Python and has a consistent internet connection.

Lessons Learned

The “Just make it do this thing” phrase is the bane of most developer’s existence, and when you find a developer who doesn’t run away screaming upon hearing these words, you should keep them close by your side and reward them greatly. (Thanks again, Jeremy!)

In addition, we could have used a database for organizing some of this data, but we opted to use Google Spreadsheets in the interest of KISS. Since we were already using Google Forms to gather the data and had already set up the Google Sheets API to talk to Google Sheets, it seemed easier to add the few extra fields that we needed into the same Google Doc instead of creating a separate database. Fewer moving parts makes it a little harder to code, but a lot easier to deploy.

Let’s Do This

So… what do you think? Are you with us? We’d love to have you join all three of us as we attempt to make 2017 our best year yet. Sign up here and keep Ember up to date on how you do throughout the year.

-Mary, Jeremy and Ember 🐾

p.s. Care to take a look at the Resolution Reminder App code? Feel free to check it out on Github.

Google Apps and SparkPost Calendar Graphic

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?

Challenge Accepted NPH

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.

Create a project

Then create a service account and and a private key for that account. You’ll want that key for later.

create a service account

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:

recipient data

And this calendar event (notice the same row ID in the title):

calendar event

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:  'substitution_data': _.cloneDeep(recipient). 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.):

SparkPost template

And taking the data from the spreadsheet we just saw and sending this email in Jerry’s inbox:

final result

Mission accomplished with Google Apps and SparkPost!

mission accomplished

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!