Overview

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.

• If they are new to us, we will challenge them for their first name, and then save their name and phone number into our contacts table.

• If they are an existing user, we will send them a customized welcome greeting.

• Once they are welcomed back, or in the case of a new user added to our database, we will move on to the next step, letting them know that we have a quick question for them.

• Following that, we will make a query back to our Airtable base, this time to the Questions table, to see which is our active question at the moment.

• The question marked active in Airtable is returned to the Twilio Studio application, and then sent onto the user.

• Finally, the user answers the question via SMS and that response is captured in Studio and posted back into our Results page in Airtable.

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.

Requirements

Creating a new Airtable Base

Creating the Contacts table

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

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

Creating the Response table

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.

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.

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

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.

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.

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.

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.