Building a SMS survey with Twilio Studio and Airtable

Overview

I am sure you have all received at some point a text message asking you to rate a service, or provide feedback about 1 thing or another. This post will explore how to build your own survey type application. 

In this post, we will be building a SMS based survey application, using Twilio Studio flow that has some data dips to Airtable. 

Twilio Studio is an easy low code / no code way to build Twilio applications. Think of it as a flow chart, and as users interact with it they are moving though the tree. While a bit over simplistic, that's pretty close to what you are doing when building a Studio flow. This is a really simple and easy way to get started with Twilio and build an application that can ingest Messages, Voice, or be kicked off from a REST API call from another external application. 

Our walkthrough today will build an application that the user will start with an inbound text message. That inbound message will trigger the Twilio Studio flow, which will then reach out to Airtable to pull and push data from and back into our Survey Base in Airtable. 

Getting Started

A Base in Airtable is a really neat concept, it's kind of like a workbook in your spreadsheet application, and kind of like a classic relational database - here is the link to their explainer

The Base I created to use with my survey app has 3 sheets, 1 for contacts, 1 for my survey questions, and 1 for the survey results. 

So when someone messages into my Twilio phone number, the first thing that will happen is a query to the contacts table, to see if the user is already known to us, or if we need to capture and create a new user record. 

Voila! We now have a dynamically updated survey application, that captures new user information, prompts with questions that can evolve over time, and saves the users responses for further analysis. 

Now let's dig in and walk through actually building it. 

Here is a quick sketch of the workflow that we are going to be building. 

Lets walk though this step by step:

Requirements

To complete the exercises we will need an account with both Twilio and Airtable. If you don't have accounts for them, you can sign up for free trials with each. A trial Twilio account will give you access to every feature and a $15 account balance, which should be more than enough to complete this exercise. You can find the sign up form here, if you already have an account go ahead and login. 

On the Airtable side, again you can sign up for a free personal account, again here is a link to the signup form here. If you have an account go ahead and login here as well. 

Creating a new Airtable Base

We are going to start with creating a new Base in Airtable. Once you have created your account or logged in, from the landing page hit the "Add a Base" button and then "Start from scratch"

A new Base will be created, Let's give it a descriptive name so we know what it is later on, in my case I will call it "Twilio Survey". 

Creating the Contacts table

The default view that you are going to be presented with is very similar to a spreadsheet, if you have familiarity with excel, you're going to do great! 

Let's do a little clean up on this first table. In the upper left corner you will see that the table name by default is Table 1. Let's change that to Contacts. Hit the dropdown triangle and hit the rename table menu option.

Since this is going to be our Contacts table, lets adjust the fields to make a bit more sense. 

By default, we have Name, Notes, Attachments and Status. For a contacts table, all we are going to need is the Name field and a phone number field to match up with the name. 

So lets delete the Notes, Attachments and Status fields, Right click on each and hit delete field from the drop down menu. 

Now we are down to just the Name field, let's add a new field to store the phone number. 

Hit the + button and let's name the Field “Phone”, and select the Phone number data type and hit Create Field. 

Great, now we have our table to store contact information in.

Once we have those 2 fields, let's add a test user so our API examples will be populated later on. 

Put in a test user name and test phone number. 

Creating our Questions table

Next let's create our table to store our survey questions in. 

Next to the Contacts tab at the top of our window, hit the Add or import option, and then select the Create an empty table menu option. 

This will create the same basic table we had initially, so once again let's do some renaming and clean up of the fields. 

Name or rename the table from Table 2 to “Questions”. 

Lets rename the "Name" field to "Questions", and delete the Notes and Attachments fields. This time we are going to keep the Status field though - we will use that to control what the active question in our survey is. 

Now let's edit the possible values of the Status field. 

Hit the dropdown button on the Status field header, and select the Customize field type menu option. 

For our purposes, let's rename “Todo” to “in active” and “Done” to “Active”. So we should have 3 field types now: "in active", "in progress", and "Active". Hit save

Now let's make up a question and set the status to active. 

Here I am going to simply ask the user what 2+2 is, and set the Status to Active

Finally we have 1 more table to make, this one is going to be used to store the users response. 

Creating the Response table

So once again, let's hit the Add or import menu option and select Create empty table. Again name or rename it from “Table 3” to “Response”. 

Here I am interested in 3 fields, the person who answered the question, what the question was, and what their answer to it was. 

So we need 3 fields to capture that. We will keep the name field that is already there, and let's rename the “Notes” field to “Question”. 

Lets delete the Attachments and Status fields as we did before. 

We need 1 more field to hold the response so let's hit the + button to add a field and let's name it “Response” with a Long text type. Hit Create field. 

Beautiful, we now have our Airtable Base created, it contains 3 tables, 1 to store contact names and phone numbers, 1 to store our questions that we want to ask our users and finally 1 table to hold the response to the question. 

Creating our Twilio Studio flow

If we are not already logged into Twilio, or have our trial account set up, let's go ahead and do those now. You can find the sign up form here, if you already have an account go ahead and login. 

In the Twilio Console, we are going to load up the Studio Editor. To find Studio,  expand the all products and services menu, and under the Runtime header you will see the Studio tool. Hit that to launch it.

This will load your Studio Dashboard, and any flows you have created will be listed here. 

We are going to create a new flow. I'll start by hitting the blue + button to start the new flow wizard.

You will be prompted to give your New flow a name, I am going to call mine "Airtable" so in the future I know what this one is integrated with. Name yours and hit next

The next step in the wizard is to pick a template or start from scratch. 

The templates are a really quick and easy way to get started, and in the future I would like to highly encourage you to try them out. 

Today however, we are going to Start from scratch and build our own from the ground up. 

Select Start from scratch and hit Next

We will land on an empty canvas to build our application. 

Quick orientation, the Grid view in the center is our canvas - that is our main workspace where we will do all our drag and drop of widgets and connecting them together. 

The widget library on the right hand side, contains all the different functionality we can drag and drop onto our canvas, and when we are editing a widget will be the configuration pane

The bright red Trigger widget at the top of our canvas is the entry or the starting point of our flow. 

You can see the 3 different types of entry I mentioned earlier, Incoming message, Incoming call, and REST API. 

Since we are going to build a SMS based Survey application today, we are going to be interested in the Incoming message trigger.

Performing a Contact lookup

For our Survey app, one of the first things we want to know is, "Do i know this person I am interacting with?". 

If the user does not exist in our Contacts table, our initial response will be different than someone who already does. 

So our first action will be to check Airtable for a known contact based on the incoming SMS phone number. This can be easily done with a HTTP request to the Airtable API. 

To do this, in our Studio Flow the first widget we are going to add to our canvas is going to be the "Make HTTP request" widget. 

This is going to be the first connection back to our Airtable base, to see if the incoming message is from a new or known user

To add the Make HTTP Request widget to our workspace, scroll down to the TOOLS & Execute Code section of the widget Library and Drag “Make HTTP Request” to the canvas.

Next, let's grab the handle coming out of the incoming message trigger and drag it to the grey dot in the upper left corner of the Make HTTP request widget. 

This will then route the incoming message to the next step of the flow, which is making a HTTP request. Easy enough

Lets edit our HTTP request, so that it has a configuration to actually do something. 

In our case, we want it to query the Contacts table of our Airtable to see if the incoming message is from a known user. 

Click on the widget and in the properties let's change the Widget name to “ContactLookup” (the names of widgets do not support spaces). For the request URL, we will need to head back to Airtable and find the API docs.

In Airtable, in the upper right corner, click “Help” and at the bottom of the help menu is a link to the API documentation, click that link. 

If you open the API documentation while in the Survey Base, it should open an API explorer in the right context, so that we can just copy and paste the examples. If not, it should prompt you to select a Base to view the API configuration for, select the Survey Base we created in the previous steps.

On the right hand side, I am going to tick the “show API key” box, to make my copy and paste of commands easier.

We are going to start in the Authentication Section and copy the Example using Query Parameter

EXAMPLE USING QUERY PARAMETER

$ curl https://api.airtable.com/v0/SOMEUUID/Contacts?api_key=YOUR_API_KEY

In the far left hand column of the API explorer, select the menu option for the Contacts Table. And then under that pick the List records menu option.

By looking at this API, we can see that by calling it with a GET  verb, it will return a matching set of users. 

Additionally we can filter based on a formula, which is exactly how we are going to check for an existing user - in this case by filtering on a phone number.

So let's grab that example request we copied earlier and copy and paste it into a text editor so we can do a little different formatting.

EXAMPLE USING QUERY PARAMETER

$ curl https://api.airtable.com/v0/SOMEUUID/Contacts?api_key=YOUR_API_KEY

Secondly, we need some way to dynamically include the incoming phone number in this request. 

Luckily Twilio Studio includes what is called Liquid text, which is essentially a way to use variables throughout the Studio flow and reference inputs / outputs from the various widgets.

When messages, voice calls, etc. come into the twilio system there is a bunch of metadata associated with them that is passed along as well. For incoming messages, the “From” value will contain the user's phone number. 

So we can reference that in liquid text by using the value {{trigger.message.From}}.

 Breaking that apart, and walking though it, we can see that the trigger value references the trigger widget, the message value references the message action inside the trigger widget, and the From value references the From metadata inside the message action inside the trigger widget. Not all that complicated!

Based on the Airtable API documentation we can see the ability to use A formula used to filter records. The formula will be evaluated for each record, and if the result is not false, the record will be included in the response.

We can use that as a filter in the URL query by using the parameter filterByFormula and setting it equal to the field name in our table and equal again to the {{trigger.message.From}} value. 

So to put it all together the URL should look like this:

https://api.airtable.com/v0/SOMEUUIDHERE/Contacts?api_key=YOURAPIKEY&filterByFormula=Phone={{trigger.message.From}}

Copy your URL and paste it into the Request URL field in Twilio Studio for the widget. And hit Save

Click back in the grey workspace to bring up the Widget Library again, and this time under Flow Control, select "Split based On.." and drag that over onto the workspace. 

Grab the Success output from the ContactLookup widget and connect it to the grey dot on the Split Widget we just created. 

Select the Split widget to bring up its configuration.

In the variable to Test field, select the widgets.ContactLookup.parsed dropdown option. 

As before this with the inbound from number, this is now referencing the ContactLookup widget. 

Here we can't just take the default dropdown option since Airtable will return an array (or an empty array) of matching results. 

We want to make a decision based on the count of matching results, in the case of a new user - the count should be 0, in the case of an existing user the count should be 1. 

So let's add some extra information to the Variable to Test field. At the end of the prefield text, we will add “.records.size”. This will then count and output the number of returned results

So the variable to test will be: 

widgets.ContactLookup.parsed.records.size

Click on the Transitions header and add a new condition

Set the conditions to be Greater Than and the value to be 0

And hit Save. 

The widget will update and a new output will be added to the widget for us to use. 

Now we have 2 possible outputs, a match of more 1 result and a no match meaning 0 results. 

Translated, that is a user does not exist yet based on the incoming phone number, resulting in a 0 in the split decision, or a user does already exist based on the incoming phone number resulting in a 1, in the split decision widget.

We have now completed our first data dip into the Airtable system, using the incoming phone number to query the contacts table there to see if the user is known or unknow, and used that information to make a decision to go down one path or another in Studio

Handling a New User

Since we only have a test user in our Contacts table at the moment, any incoming text message should result in a no match in the split decision widget. So, let's create a welcome message to send in response to a new user. 

Click back on the workspace and in the Widget Library under the Messaging Header, drag the Send Message Widget onto the board.

Drag the handle from the spilt_1 No Condition Matches widget onto the new send message widget.

Click on send message 1 to edit the properties. 

I am going to rename the widget to something more friendly, like “newuserwelcome” and in the message body something like “hello, and welcome! It looks like you are new here.”

Hit Save.

Across the top, let's hit “Publish” on our flow to make it live and give it a test.

Before we really try this - we need to get a phone number associated with this Studio flow.

Associating a phone number with a Twilio Studio Flow

I won't go through the process of getting a phone number step by step here, because there is a great support document here that already does that: Support page

Even with a trial account - the process is easy and can be done with the credits you get when signing up. 

Follow the insturctions there to purchase a phone number and come back here when complete.

Now that you have your phone number, the next step is to associate it with your Twilio Studio flow.

Again, I won't go through that in detail here - because there is another great Support doc that has already done that for us: Support page 

Just scroll down to the “Handling incoming voice and SMS messages with a studio flow” section of this page . 

Since we are planning on only handling incoming SMS messages for now, make sure to configure the messaging section of your number as described. 

First test run

Ok - now we have a basic studio flow configured, with a Query to Airtable, and if there is no match in the contacts table then our Application should send a welcome message back to the user. 

Let's try it out! 

On a phone send a new text message to the phone number you added to your account and configured to point to this Studio Flow.

Go ahead and send it a message - at this time it does not matter what the body is, we just need any message sent to that number.

And indeed, I received the welcome message in response! 

Adding new user records 

Now from here we want to add another data dip to Airtable. 

This time we want to publish data into the contacts table, including the phone number and user's name. 

We already know their phone number from the metadata sent with the incoming message, but we need to ask them for and collect their name to associate with the phone number.

Back in the Studio editor, from the widget library under the messaging section again, we are going to use the Send & Wait for Reply widget. 

So drag that one over. 

Here, drag the handle for the "Sent" output from the newuserwelcome widget to the input of the send_and_reply_1 widget we just added. 

Click on the new widget to edit its properties.

Let's change the name to “promptuserforname” and in the message body lets say “ To begin, what is your first name?” and click save.

The output of this promptuserforname widget will be our POST back Airtable. 

So let's add another Make HTTP Request widget from the Library, and drag it over to the canvas.

Drag the handle for the Reply output of the prompuserforname widget to the input of the http widget we just created.

Click on the widget to edit the properties

Let's change the Name to UpdateContactsTable 

Set the Request Method to POST and Content Type to “Application/JSON”

Based on the API docs we reviewed earlier we know the base URL to use 

https://api.airtable.com/v0/SOMEUUID/Contacts?api_key=YOUR_API_KEY

So go ahead and paste that into the Request URL.

In the Request Body we will send the information needed to populate our contacts table. 

Those would be, phone number and user's submitted name. The formatting for that can be found in the API docs, but mine looks like: 

{

 "records": [

   {

     "fields": {

       "Name": "{{widgets.promptuserforname.inbound.Body}}"

       "Phone": "{{trigger.message.From}}"

     }

   }

 ]

}

Here we see the 2 fields in Airtable - Name and Phone, and the 2 dynamic fields that will contain the phone number from the incoming metadata and the Body of the message from when the user replied to the prompt for name SMS. These Liquid text messages are variables referencing data generated or stored in another widget. 

Hit Save.

Let's Send another text message and see if we get the prompt for our name

Success! I was prompted for my name and i can reply with my name here. 

If I now go check the contacts table in  Airtable, I have a new record created with my phone number and name. Pretty cool!

Sending our survey question

Now that we are picking up a new user and saving them into Airtable,  the next step we want to do is ask them a question from our "Questions" table

In the earlier steps we created the table and put in a simple question of “what is 2+2?” along with marking it as "active" in the status field

To make this a little more user friendly, we are also going to let the user know what to expect and send them a message letting them know we have a quick question for them. 

To start off our survey, and as a bit of a cheat for later on, we are going to start the Survey block of actions with another API call to Contacts table. 

At this point the user should exist in the system either from a prior interaction, or through the new user flow we just created. 

So we are going to duplicate the exact same Make HTTP request we did at the beginning. So go up to the Contact Lookup Widget, and copy the URL used there.

Create a new Make HTTP widget, by dragging it over onto the canvas and connect the Success handle from the UpdateContactsTable Widget. Edit its properties by clicking on it, and changing the name to SurveyUser and pasting the URL we just copied

Now, let's greet the user and let them know we have a quick question for them

So once again, let's drag over the Send Message Widget from the Library and connect the Success handle from the SurveyUser Widget

Lets edit the new widget properties, and change the name to “surveywelcome” and let's set the message body to something like “We have a quick question for you” and hit save

From Studio we will need to make our next data dip into Airtable to get the currently active question

If you haven't figured it out by now, that means another HTTP Request widget, so let's go ahead and add that to our board, connect the Sent handle to the input handle, and then click on the widget to edit it

Let's change the name to GetQuestion, and leave the Request Method as GET

From here, we are headed back to the Airtable Documentation to get the query for the Questions table. Looking at the documentation there, we can see the same ability to filter based on a formula. 

Here we want to get the currently active question from our table. 

So our filter will be: filterbyformula equals Status (since that is the field name) and the value equal to Active. I found I had to encode the quotation marks for Active to match the field name properly

filterByFormula=Status=%22Active%22

So the URL we will be using looks like this: 

https://api.airtable.com/v0/SOMEUUDI/Questions?api_key=YOUR_API_KEY&filterByFormula=Status=%22Active%22

Hit save

Now that we have our question pulled from our Base, let's send that onto the user

Since we want to collect a reply from them we are going to use the Send & Wait for Reply Widget. So let's drag that one over to our canvas, and connect the Success handle from the GetQuestion widget to the new send_and_reply_1 widget. Click on it to edit its properties

Let's change the name to SendQuestion

Here again we are going to use the Liquid text formatting to collect the output of the GetQuestion HTTP request, which is the active question from our table, and send that onto the user

In this case, like the contact lookup, the actual question is going to be stored in an array in the response. So we need to do a little bit of digging to pull it out. If we check the docs for the HTTP widget here we can see how it will store the values returned

Based on the documentation and the response we get, and since it is stored in an array, if we use this liquid text it will pull the question that was returned in the HTTP request: 

{{widgets.Getactivequestions.parsed.records[0].fields.Question}}

I am going to add a bit of extra text in the message back to the user, so my final Message Body looks like this:

OK, this should be easy: {{widgets.Getactivequestions.parsed.records[0].fields.Question}}

Hit Save.

Recording the responses

If you are still hanging with me, we are close I promise! 

The last step in our survey block is to save the response back into our Airtable base

We ended the previous stage with our Studio Flow asking the user a question and waiting for them to reply. When they reply we will send another POST command back to Airtable, like we did when creating a contact, to save the conversation

So lets add another Make HTTP Request widget from the library. As before, connect the Reply handle from the SendQuestion Widget to the input of the HTTP widget and click on it to edit the properties

Let's change the name field to SaveResponse, change the Request Method to POST and content type to Application/JSON

From here, we are headed back to the Airtable Documentation to get the query for the Response table

It looks essentially the same as the others, but we see the Response directory in the URL instead of Contacts or Questions. In the Request body, we are going to send the required response to populate the tables with the Dynamic values from the users session. 

So our Request URL is going to be:

https://api.airtable.com/v0/SOMEUUID/Results?api_key=Your_API_KEY

And the request Body is going to be: 

{

 "records": [

   {

     "fields": {

       "Contact": "{{widgets.SurveyUser.parsed.records[0].fields.Name}}",

       "Question": "{{widgets.GetQuestion.parsed.records[0].fields.Question}}",

       "Answer": "{{widgets.SendQuestion.inbound.Body}}"

     }

   }

 ]

}

This will then capture the user's Name, the question that was asked to them, and finally the response they gave. 

Hit Save

Supporting known users

Remember we wanted to support 2 use cases, both new users to the system and users already known to the system. 

So far we have built our workflow for the new users coming into the system

The good part is, we already have the VAST majority of the work complete. 

We have the intake of users, the testing for them already being present, the ability to pull a question and save the answer back to our Base already done. 

So we just need to wire in a couple of quick steps

Remember we have that first split_1 widget, there we were testing for the presence of the user, so far we only used one of the 2 possible states - the value is equal to 0 state. 

Now For the if value greater than 0 outcome, lets connect that handle down to the input of the SurveyUser widget as well.

This is probably less efficient than we want it to be, in terms of API calls, since we are going to make the same API call again - BUT it will allow us to have a consolidated entry point into the survey actions. 

So once we connect that,  we are done!

In the future, we can add questions to the Questions table, and mark the 2+2 question as inactive, and make our new question the one that will be sent to our Survey takers.