Remind volunteers when and where to be with Twilio, Airtable, and Zapier

#twiliohackathon #airtable #zapier #automation

Helping COVID-19 community response teams coordinate neighbors helping neighbors. A Twilio x DEV Hackathon submission for Engaging Engagements and/or Interesting Integrations.

Automating volunteer reminder texts for COVID-19 community response teams

If we follow Mr. Rogers’ advice to look for the helpers right now, we will find them. Team Rubicon has distributed hundreds of thousands of masks and gloves to healthcare workers in Colorado. In New York City, Backpacks for the Street has shared 1,200 COVID-19 prevention kits with residents experiencing homelessness. Yesterday, TogetherSF volunteers delivered 35,000 steaks to Bay Area food banks.

Organizing these efforts takes a lot of communication, and it can be challenging to stay on top of it all, especially as a resource-constrained nonprofit. It would be impossible to manually text every volunteer who signs up for a shift to remind them when and where to be, but Airtable, Twilio, and Zapier can automate that for us.

Setting up an Airtable Base

Airtable is a database spreadsheet hybrid that helps with all things organization. The nonprofit I work with uses Airtable to collect volunteer sign-ups and to match them with volunteering opportunities, so I dived into their existing system to see how I could work with it.

After looking at their setup and reading up on Airtable’s docs and community forums, I learned I would need to change a few things to get automated text reminders working.

Have a look at the Volunteer Coordination Base template, and I’ll walk you through what’s special.

The Events tab (or table) lists out the volunteering opportunities that come our way, including who asked for help (typically somebody in the Needs table), the kind of help they're asking for, and when and where we've scheduled a time to support.

The first change I made was to make the When field in Events more granular, adding in a timestamp. It took me longer than I’d like to admit to spot the “Include a time field” toggle in Airtable, but can I really be blamed -- what is time these days?

While there is a Date type for columns in Airtable, there is no separate Time type, so I needed to come up with a workaround. I knew I needed to send a text reminder n hours before an event, so a column that calculated the number of hours until an event cold fit my use case. I added in an Hours Until Event formula column to sort it out with DATETIME_DIFF({When}, NOW(), 'hours').

The nonprofit I worked with already included a Matched Volunteers column to list out the volunteers that would be at an event. Next up I needed to get all of those volunteers’ phone numbers into the Events table too.

Record scratch, freeze frame: Yup, that's me, about to make an incorrect assumption

The following has been updated to reflect what I got wrong in the first version of this post:

I assumed that because we used the "Phone Number" data type in our Volunteer sign up Airtable form, all of the numbers would be standardized, despite user input. This was incorrect! It looks like folks have been chatting about data validation and Airtable at least between 2015 and 2019. I haven't seen any updates that this feature request has been added.

So, to work with user entered phone numbers, I needed to not only account for E.164 format so that numbers could be texted, but also to standardize those numbers. Our Uniform Phone Numbers column in the Volunteers table is built on a very messy formula, so messy it's getting a gist. Our conditionals represent ways we thought user input phone numbers might differ:

The formula also adds the necessary +1 for this US-based nonprofit (in E.164 it’s really +{CountryCode}), and replaces all nonnumeric characters with empty strings using SUBSTITUTE.

Now, back to the Events table and our Matched Volunteers. We add another column called Matched Volunteer Phone Numbers that sees those Matched Volunteers and looks up their corresponding Uniform Phone Numbers in the original Volunteers table.

Back to our regularly scheduled post, now that assumptions have been updated.

With all that in place, I set up an Airtable View to filter for records where Hours Until Event hits the point when I need to send a text, so fewer than four hours before. I also added a filter to make sure we’re only looking at events that haven’t happened yet, where their Hours Until Event hasn’t dropped below zero. I called this View Volunteer Reminder Texts.

Airtable should be good to go from here! Time to leave spreadsheets behind for a minute and head over to Twilio.

Setting up Twilio

Developers use Twilio to programmatically send and receive calls and texts, but the limit really does not exist. Chloe Condon and I once used it to build a Mean Girls’ day bot, and Twilio Champions get up to all kinds of projects.

From my experience with Twilio, I knew it could handle texting this nonprofit’s volunteers. Here’s where to sign up for an account if you don’t have one already. You will also need a Twilio phone number, which you can set up from your Console.

Putting it all together with Zapier

Zapier automates connecting different apps together (you can sign up here).

Those apps include Airtable and Twilio. I clicked “Connect these Apps” and had my Airtable API key and Twilio API key both handy for the setup process.

Once the accounts were linked, I specified that when a new record popped up in an Airtable View, specifically Volunteer Reminder Texts, Zapier should tell Twilio to text those volunteers. Since my Hours Until Event column automatically calculates, new records are added to the View automatically, meaning the texts will be automatic too.

And, since Zapier pulls in all the data from the View, it also automatically pulls in the specific When and Where for each record, so your texts will be personalized for every event.

You should see whatever you specify in Zapier show up in your test text, and, if you turn on the Zap, they’ll show up again four hours before the event too:

After the event

And that was it! There are lots of things I could do to improve, and I know there's loads more I can do with Twilio and Airtable. We’re considering automated feedback surveys once an event is done, for example.

I’m excited to explore all the options. I thought these automated reminders would involve a lot more time and work, and I was surprised to find a practically no code solution. This was another lesson for me that it’s always good to start looking at systems already in place, meeting your users, in my case volunteer organizers, where they’re at, and understanding their needs before jumping into building something from scratch. I love when existing tools free up time to be a helper in other ways.