Verse of the Day, Part 1: Making use of Google Apps, Twilio, MailChimp, and ESV Bible WS


Our Church does a 40 Day Event during this time of year that includes, among other things, being able to signup to get an relevant Bible verse and message via SMS and/or email.

This year, I decided it was time to make the process a little less labor intensive and take a few ruby modules for a spin.

What I needed:

  1. A place to record the the verses and messages we will be sending out each morning for 40 days. Also a place to store other information for the messages like email subjects and titles.
  2. A way to sign people up by hand ( a signup sheet passed a round on a Sunday morning ) and electronically.
  3. Send one or more text messages to each person who signed up. Depending on the overall length of the message, it may need to be broken up to fit the 160 character SMS limit.
  4. A way to send attractive emails to everyone who signed up.

Storage

I decided to store everything in a Google Docs spreadsheet because it was a tool that most of our staff are familiar with. We created a spreadsheet called “Verse of the Day” with two sheets in it, one for recipients, one for content needed for messages. Google provides ruby libs for authentication and reading data from the sheets.

Sign-Up

The signup part was simple, MailChimp provides facility to setup opt-in registration forms. With a wordpress plugin, Easy MailChimp Forms in this case. MailChimp allows you to define additional fields, so I am using it to get users signed up to the MailChimp list we will be using as well as collect their SMS number and an explicate check they they want to get the SMS and/or the email each morning.

Once people sign-up, I can use MailChimp’s export feature to dump the user list to my Google spreadsheet. I know this creates two stores with virtually the same data and potentially a synchronization nightmare, but I ran out of time to make one work over the other. MailChimp needs the list of email addresses in order to function, so it would make since to keep it all there, but I ran out of time to explore their lists API. Maybe next version.

Sending SMS Texts

I have explored different ways to send SMS including using cell vendor specific email-to-sms gateways, using Google Voice, even setting up my own SMPP gateway, but then I ran across Twilio. Twilio is a service the (among other things) provides for sending and receiving SMS for about 3/4 of a penny, give you a pretty useful REST interface and tools to build simple autoresponders using their TwiML XML markup. They also provide a phone number to send and receive messages for a dollar a month.

Sending HTML emails

The last part was to send out a nice looking email every morning to those who wanted it. Again, MailChimp to the rescue. Their service allows me to not only send out the bulk emails, but also track level of engagement with open and click-through tracking. While tracking who opens the email isn’t perfect, the users need to allow showing embedded images, it gives me a good sense engagement compared to other email campaigns we have done.

I wanted to have a basic template that myself or other staff could edit on MailChimp’s WYSIWYG editor that my script could then substitute content and send out each day. This was perhaps the most involved part of the project as it also included accessing Google Docs to get the parts to go in the email and ESV Bible webservices to get the verses we would be sending out.

Setup

I decided to run the process as a cron job on my desktop machine at the church. Like most of the computers at our church, I run Ubuntu Linux so running a current ruby environment was not an issue. After testing the a number of libraries, I set up the ones I needed in a Gemfile:

source 'https://rubygems.org'

gem 'twilio-ruby'
gem 'google_drive'
gem 'google-api-client'
gem 'esv_api'
gem 'mailchimp-api', require: 'mailchimp'

Using bundler, install the needed gems and dependancies:

$ bundle install

Then I need a place to keep my configurations such as API keys, authentication tokens, and pointers to resources like my spreadsheet:

module Config
  ESV = { 
          :api_key => 'xxxxxxxxxxxxxxxx'
        }
  
  TWILIO = {  
              :account_sid => 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx',
              :auth_token => 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx',
              :from_number => '+11015551212' # number provided by Twilio
            }

  GDRIVE = {
              :ss_title => 'Verse of the Day',
              :schedule_ws => 'Schedule',
              :recipients_ws => 'Recipients',
              :oauth_client_id => "xxxxxxxxxxx-xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx.apps.googleusercontent.com",
              :oauth_client_secret => "XXXXXXXXXXXXXXXXXXXXXXXX",
              :oauth_refresh_token => '1/xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx-xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx',
              :scope => "https://www.googleapis.com/auth/drive https://spreadsheets.google.com/feeds/",
            }
  
  MAILCHIMP = {
                :api_key => 'XXXXXXXXXXXXXXXXXXXXXXXX-us2',
                :template_name => "40 Days Template"
              }
end

I’ll explain each of the above configs as we use them. One thing to point out is my choice of using real ruby code for my configurations over something like YAML. While I used to be a big fan of YAML, I have gotten lazy in my old age and started using the native language for configs in my ruby and php code. It just think give me a bit more flexibility and save me learning or retain yet another syntax.

Lastly, my little program to run from cron. This is just the beginning of the script, I’ll add more detail in the next article:

#!/usr/bin/env ruby
# VotD - Verse of the Day
# Send daily verses messages with mailchimp and twilio
#
# Copyright 2015 Chris Jackson <chris@faithhack.com>
#
# This program is free software: you can redistribute it and/or modify
#    it under the terms of the GNU General Public License as published by
#    the Free Software Foundation, either version 3 of the License, or
#    (at your option) any later version.
#
#    This program is distributed in the hope that it will be useful,
#    but WITHOUT ANY WARRANTY; without even the implied warranty of
#    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
#    GNU General Public License for more details.
#
#    You should have received a copy of the GNU General Public License
#    along with this program.  If not, see <http://www.gnu.org/licenses/>.


require 'rubygems'
require_relative 'config'
require 'bundler/setup'
Bundler.require(:default)

The use of require_relative() above should clue you into to the fact that my project requires a recent version of ruby (>1.9.2) I am using 2.2 but I don’t think I am using stuff requiring a version > 2.0.

Next time, I’ll describe setting up my spreadsheets, logging into Google Docs with OAuth2, and getting the message current message.


One response to “Verse of the Day, Part 1: Making use of Google Apps, Twilio, MailChimp, and ESV Bible WS”

Leave a Reply

Your email address will not be published. Required fields are marked *