Hackers’ Alternative to Marketo: Treasure Data + Heroku + SendGrid

Emails: The Secret Retention Weapon

It is no secret that email marketing continues to be inbound marketers’ best weapon. (For a good overview of customer lifecycle emails, read this great write-up by Patrick McKenzie.) It’s also no secret that there are many fantastic tools to set up and manage your email engine. From enterprise powerhouse Marketo to Mailchimp of the Email Genome Project fame and startups like Customer.io and Dotmailer, you have a wide range of options.

As a developer, though, I always have an itch to scratch. For example, what if I wanted to send an email to all customers who used a particular feature in our web app? I am sure there is a way to do this in Marketo, but wouldn’t it be great if I could write SQL against my event data, pull up the email list, and email them with a targeted, highly relevant message?

Additionally, cost is a factor; many marketing automation tools aren’t exactly cheap and charge you by the size of your email database. Here, we aim to build a system that’s going to cost a fraction of such a service and allows you to store millions of emails.

So, let’s build that. The rest of this article shows how to build such a engine using SendGrid, Heroku and Treasure Data. We assume that you have a Heroku account.

Step 1: Logging Customer Events

We need customer data to feed our smart email engine. So, let’s start logging our customer visits.

As the first step, enable Treasure Data Add-on like this:*

heroku addons:add treasure-data -a YOUR_APP_NAME

The upshot of using Treasure Data on Heroku is that you can just log to STDOUT with the “<YOUR_DB>.<YOUR_TABLE>@<EVENT_AS_JSON>” format and the event is automatically picked up and saved into our schemaless storage.

So, in the case of this Rails app, in your controller code, add a line to app/helpers/session_helper.rb like this:

module SessionsHelper
  def sign_in(user)
    remember_token = User.new_remember_token
    cookies.permanent[:remember_token] = remember_token
    user.update_attribute(:remember_token, User.digest(remember_token))
    puts "@[events.login] {"email":#{user.email}, "user_id":#{user.id}, "time":#{Time.now.to_i}}"
    self.current_user = user
  end
#more code
end

The “puts …” line logs an event to Treasure Data’s events database in the login table every time a user logs in. This event will have three fields: user_id, timestamp, and email. In theory, the email field is redundant because you can look it up in your database, but including it here obviates future lookups.

This is it! Once you deploy this into production, you can run heroku logs -t to see your stdour/stderr logs and should see a line like this when your user logs in:

2015-05-06T23:24:47.701449+00:00 app[web.1]: @[events.login] {"email":"kiyoto@treasure-data.com", "user_id":1, "time":1430954687}

Finally, run heroku addons:open treasure-data -a to open our web console, and follow the navigation to find the “login” table inside the “events” database.

Step 2: Setting up the td2email app on Heroku

The second step is to create the td2email, a little service on Heroku that receives the list of emails (to be implemented in Step 3) from Treasure Data over HTTP PUT and sends them one by one using SendGrid.

You can see the sample code on GitHub. It is a simple Sinatra app that renders different Markdown email templates based on the path given to it. For example, if you hit /retention, it will render email_templates/retention.markdown as body. The actual hard work of sending a transactional email is done by SendGrid using their Ruby library. The app expects an input coming from Treasure Data, looks for the column named “email”, and sends emails to the email addresses in that column.

This app has been packaged so that it can be deployed on Heroku by clicking on the button that looks like this:

Step 3: Writing a Treasure Data SQL Job

Now that all the pieces are ready, it’s time to define the logic. Suppose you want to send emails to all users who have not signed in for five days. Here is how you do it:

1. Go to Treasure Data’s query authoring page
2. Choose “events” from the database dropdown menu
3. Click on “Add” in the Result Export section and select “HTTP PUT” (This is how to output data to the td2email app in Step 2)
4. Fill out the hostname for the app. Also, specify “/retention” for “Path” and click on “Use”
5. Write the following query:

SELECT
     email
FROM (
   SELECT
         email,
         MAX(time) AS most_recent_login_time
     FROM
         login
     GROUP BY
         email
    ) t
WHERE
     NOT TD_TIME_RANGE(most_recent_login_time,
     TD_TIME_ADD(TD_SCHEDULED_TIME(),
     '-7d'))

and hit “Run”. This query looks for all emails that have not logged in for at least seven days.

In a short while, you should receive an email that looks like this, assuming that you have not logged in for seven days. (To test this app, just simply remove “NOT” so that you get all users that logged in within seven days.)

To recap, here is what the entire system looks like:

But Can’t I Just Use Service X?

One might ask, “All of this is great, but can’t I simply use [blank] to do the same thing with less effort?” The answer is, “It depends on the logic of the emails you want to send out.” Treasure Data’s SQL interface gives raw access to user events, and which user events to store is totally up to you. Here are some email campaigns that might be hard to program with more “ready-made” services:

1. Tailoring your onboarding instructions based on mobile devices that customers use to access your service (iOS/Android, phone/tablet)
2. Giving product updates to a subset of your customers that was inactive for the last three months but just logged in.
3. Some crazy logic that requires all the windows functions available in SQL. 

Next Steps

Of course, this sample app is a proof of concept with much room for improvement. For example,

1. With this method, Treasure Data’s HTTP PUT-based result output can only output up to 100,000 records. If you are running a large service, you need to find an alternate method of outputting data, possibly by writing out the results to Postgres.
2. The td2email web app is sending emails serially without ensuring successful delivery. In a production-ready system, you should strongly consider using some kind of worker queue. (Our recommendation is Iron.io, which is available on Heroku as an add-on.)
3. Finally, you probably want better customization in your email templates, possibly with placeholders (pull requests welcomed!).

So go out and start sending better targeted emails, annoy fewer customers and retain more $$$!

* Note: Currently, our lowest paid tier is 100 USD/mo on Heroku. So, assuming you try this for a day, it would cost you about $3. Hopefully, you won’t mind that.  =)