Airtable is an awesome product. It’s like a cross between a spreadsheet and a database, but with a lot more flexibility for creating dynamic views of your data. In this post you’ll learn how to write to Airtable from a Twilio application. The advantages of doing so are as follows. Twilio + Airtable / Collecting data from a chatbot. Ask Question Asked 11 months ago. Active 11 months ago. Viewed 71 times 0. I´m developing a chatbot using Twilio Studio and at some point, I need to collect the data inputs from the clients that talk to my bot, and then post it into an airtable base. Plan a product launch, organize user feedback, outline your content calendar, or even manage fundraising outreach. With Airtable, you get a tool that can do it all.
- Integrate Airtable with Twilio SMS
- Quick Deploy
Use this app if you would like to understand how Airtable can serve as a way to store data sent to your Twilio phone number via SMS.
If you have an existing Airtable instance with a list of contacts, you can use Twilio SMS to add to that database and broadcast messages to your contacts.
There was an issue loading the page.
Please try again in some time.
'Scandia 010' by How I See Life is licensed under CC BY-ND 2.0
Airtable is like a cool big-budget superhero crossover between a spreadsheet and a database.
It’s easy to write to an Airtable from your Twilio app, so we’re going to take that idea and build off of it to make a SMS-driven survey app using Python and the Flask framework.
The survey app we are going to build will gather NPS, which stands for Net Promoter Score, a metric used to measure customer experience. However, you can customize the survey for purposes specific to your needs. Try it out by texting your favorite emoji to the number below.
We’ll go through this step-by-step, and we will do it all in a single file. If you would like to download the complete project you can find it in this GitHub repository: https://github.com/Eclairemoy/nps-survey
Building the Project
To build the project you will need:
- A free Airtable account.
- Python 3.6 or newer, which you can download at python.org.
- ngrok. We will use this spiffy utility that will give the Flask application running on your system a public URL that Twilio can connect to. You can download a copy for Windows, MacOS or Linux.
- A free or paid Twilio account. If you are new to Twilio get your free account here (this link will give you $10 when you upgrade).
- A Twilio number with SMS capabilities (this quickstart will help you get going)
Here’s a roadmap of what we’ll do in this post:
- Create an Airtable Base
- Set up Your Flask application
- Write the Survey Code
- Buy a Number
- Connect the App Using Ngrok
Create an Airtable Base
You can create your own Airtable base, or you can copy this one that I made by hitting “Copy Base” in the upper right hand corner.
You’re going to need the Airtable API key and a base ID to access the base from your app. You will get the API key from your account page. You will get the base ID from the auto-generated API documentation when you click on the base that you want to use (Net Promoter Score - Tutorial if you are using the copy). You can pull it from the URL, or the introduction section.
Create a Python Virtual Environment
Let’s create a virtual environment where we will install our Python dependencies.
If you are using a Unix or MacOS system, open a terminal and enter the following commands (note that if you have multiple versions of Python on your machine, you may need to use the
Python3 command to specify that version):
For those of you following the tutorial on Windows, enter the following commands in a command prompt window:
The last command you see uses
pip, the Python package installer. This will install the Python packages in this project, which are:
- The Twilio Python Helper library to use write our SMS message as TwiML
- The Flask framework, to create the server
- Python-dotenv, to import the contents of our .env file as environment variables
- Airtable Python Wrapper, which provides a class with handy methods to read and write from your Airtable base
For your reference, at the time this tutorial was released these were the versions of the above packages and their dependencies:
Set Up the Project
This is not going to be the fanciest app,but I recommend taking what we have here and making it fancier if you want. Also, listen to Reba McEntire’s Fancy while you go through this tutorial.
Use the Flask framework and start by creating the directories.
We'll also create a .env file to use later where we'll add environment variables.
Then, create the directory where you want the app to go.
We’ll make a single file called main.py to hold all the juicy bits of our app.
Set Environment Variables
Twilio Air Tablet
Before we go any further, we need to set our environment variables.
Twilio Airtable Integration
Open your .env file, then add in the text using your Airtable API Key and base ID (information on where to find that is above under the “Create an Airtable Base” heading).
Start the Server
Next, wire in the parts that will make Flask run. We’re going to create two routes in
main.py that will correspond with the “GET” and “POST” methods. Eventually “GET” will retrieve scores from our Airtable base. For now, we will create a dictionary as a placeholder.
We should be able to run the server now! All you will see on a
GET request is the empty dictionary, but it can be nice to know that our set up is working so far. Here’s how we run the server.
You should see something like the following output once the server starts:
Once that is running, you can navigate to http://127.0.0.1:5000/get-scores. If you see this, you’re on the right track.
A quick joke before we keep going:
“Is your server running? You better go catch it”.
Send the Survey and Store Responses
We can now write the code in
main.py that will send the SMS survey as an autoresponder. We’ll first need to update our import statements to make sure we have access to the different helper libraries. We’ll also call
load_dotenv to access our environment variables.
send_survey function, create an instance of the Airtable Class from the wrapper library. We’ll pass in the
AIRTABLE_BASE_ID and the name of our table (which is
We will get the text of the incoming message, the sender’s phone number, and the Twilio number that they are texting in to from the request.
Sessions are super important to this project. The session keeps track of where the user is at in the survey, which we are going to store in a key called
sms_count. It’s also where we will temporarily store their survey responses until we are ready to write to Airtable. These responses are organized using the sender’s phone number as a key named
If we’re starting the survey for the first time
sms_count will not exist. Let’s create that key in the session if it’s not there. We’ll also initialize a new empty dictionary within that dictionary for the responses with the key
We need the sms count to determine which survey question to send the participant. We can store that in its own variable as a string, named
This is optional, but I like to create a mechanism to reset the session for when I’m testing the survey. The below code will clear out the session object.
Twilio Air Tables
Next, we will write a helper function to get the text that should be sent in the SMS message. We will pass in sms_count to get the right string of text based on where the participant is in the survey.
Back in our
send_survey function, let’s add a new variable called
sms_message. We’ll call our helper function here.
We need to store the participants’ question responses in the session, increment the
sms_count in the session, and insert the data into airtable once the participant responds to the last question. The Airtable insert will fail if data for each column is not present, which is why we only insert after the survey is finished. Another thing to note, is that the data type you insert must match the data type that you set for the column.
The last step of this section is to use the Twilio Helper Library to generate TwiML and send the SMS message to the participant.
Read from Airtable and Calculate NPS
Now that we’ve stored the data in our Airtable base, we can access it using a GET request. We can pass in our phone number as a query parameter, and then use the Airtable Python Wrapper to retrieve the responses.
We’ll use the request context to access our phone number.
Next, we initialize the airtable class. Remove the empty dictionary we used as a placeholder previously, and create a new empty dictionary called
airtable_data_dict and an empty list of scores called
We are going to get the data from Airtable, and put it in our
airtable_data_dict dictionary. We are also going to add the score from each record to
score_list. This is because we will want to use this list of scores to calculate the total NPS.
The formula for calculating NPS is (Number of Promoters — Number of Detractors) / (Number of Respondents) x 100. Let’s write a helper function to do this calculation for us.
Finally, we will call our calc_nps function and pass in
score_list and store the result in a variable called
nps_total_score. We will create a new dictionary object with values that are the NPS score and a dictionary of all the survey responses. Flask will convert this to JSON for you in the return statement.
Your finished main.py should look like this:
If your server has stopped, re-run
flask run. And now on to buy and configure a Twilio number to test our app.
Buy A Programmable Phone Number
If you don’t already have a Twilio Programmable Phone Number, follow the instructions here to get one:
- Navigate to the Buy a Number page in the console
- Search for a number that has SMS capabilities (in the United States, that’s all of them)
- Click “Buy Number”
If successful, you should come to a page that looks like this.
Connect to Twilio Using Ngrok
We need to open up a tunnel between our locally running server and a public-facing URL on the ngrok.io domain. Keep your Flask server running, and in a new window, in the directory where you have ngrok installed, run this command:
It should look something like this:
Grab the forwarding link from your ngrok terminal that starts with “https://” (underlined in red above) and copy it.
Now open up your Twilio console for the Programmable Phone Number you purchased and scroll down to the Messaging section. Drop the ngrok link into the “A Message Comes In” Webhook input.
Hit save, and text in to your number to see if it works. Once you’ve finished the flow, test the GET request — you should get JSON formatted like the below.
The code displayed will show the Airtable data (named
airtableData) as values listed by ID, and the calculated NPS score named
If you change any of the Field Names, also known as columns, in the Airtable, be sure that you update those where they are accessed in
record['fields']. If you are working with an empty Airtable,
calc_nps will fail, because there will not be any scores passed in. For this reason, it is recommended that you use the example Airtable, or make sure you have dummy data in your base to avoid that error.
For the larger desktop printers, such as the Imageclass series, load the vellum in the multipurpose tray on the left side of the printer. Large pedestal printers, such as the Imagerunner series. Best printer for vellum paper printable. Try printing on transparency setting as this puts out a lot less ink. DRAWBACK: The printing will not. The best way to know how your printer will work with vellum is to first know how it works with other papers. Use, test and get to know your printer! Use, test and get to know your printer! Familiarize yourself with the driver, the trays, accepted paper types and weights, print settings and so on. Vellum paper can be used on all kinds of printers. Though, people generally prefer printing the paper on an ink jet or a laser printer. You will also need to change the settings of the printer to a draft mode so that less ink used on the paper. Vellum paper does not require a lot of ink as it my smudge the paper.
Conclusion: Building an Airtable NPS calculator in Python
- Started an Airtable Base
- Made a Python SMS-app using Twilio
- Stored and accessed data from your app to Airtable
If you want to try out building a Twilio app using Airtable and Twilio Functions, please check out this post. And whichever stack you choose, I’d love to hear about what projects you’re working on! Message me and let me know at lmoy [at] twilio [dot] com.