Build a Survey App With Python, Twilio, and Airtable

May 29, 2020
Written by
Liz Moy
Twilion
Reviewed by

Build a Survey App With Python, Twilio and Airtable

"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.

Airtable NPS survey testing number with Twilio

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

Prerequisites

To build the project you will need:

Here’s a roadmap of what we’ll do in this post:

  1. Create an Airtable Base
  2. Set up Your Flask application
  3. Write the Survey Code
  4. Buy a Number
  5. 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.

Where to find the Airtable Base ID

 

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):

$ python -m venv venv
$ source venv/bin/activate
(venv) $ pip install twilio flask python-dotenv airtable-python-wrapper

For those of you following the tutorial on Windows, enter the following commands in a command prompt window:

$ python -m venv venv
$ venv\Scripts\activate
(venv) $ pip install twilio flask python-dotenv airtable-python-wrapper

The last command you see uses pip, the Python package installer. This will install the Python packages in this project, which are:

For your reference, at the time this tutorial was released these were the versions of the above packages and their dependencies:

airtable-python-wrapper==0.13.0
certifi==2020.4.5.1
chardet==3.0.4
click==7.1.1
Flask==1.1.2
idna==2.9
itsdangerous==1.1.0
Jinja2==2.11.2
MarkupSafe==1.1.1
PyJWT==1.7.1
python-dateutil==1.5
python-dotenv==0.13.0
pytz==2019.3
requests==2.23.0
six==1.14.0
twilio==6.38.1
urllib3==1.25.9
Werkzeug==1.0.1

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.

Reba McEntire’s Fancy GIF

Use the Flask framework and start by creating the directories.

$ mkdir nps-survey
$ cd nps-survey

We'll also create a .env file to use later where we'll add environment variables.

$ touch .env

Then, create the directory where you want the app to go.

$ mkdir app
$ cd app

We’ll make a single file called main.py to hold all the juicy bits of our app.

$ touch main.py; open main.py

Set Environment Variables

Before we go any further, we need to set our environment variables.

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).

$ cd ..
$ open .env
FLASK_APP=app/main.py
FLASK_ENV=development
AIRTABLE_API_KEY=key*****
AIRTABLE_BASE_ID=app*****

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.

from flask import Flask, request, session

app = Flask(__name__)
app.secret_key = "super secret key"

@app.route('/send-survey', methods=['POST'])
def send_survey():
   pass

@app.route('/get-scores', methods=['GET'])
def get_scores():

   scores = {"score_list": []}

   return scores

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.

(venv) $ flask run

You should see something like the following output once the server starts:

 * Environment: development
 * Debug mode: on
 * Running on http://127.0.0.1:5000/ (Press CTRL+C to quit)
 * Restarting with stat
 * Debugger is active!
 * Debugger PIN: 264-310-862

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.

Preview of what you should see on the get scores page

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.

import os
from airtable import Airtable
from dotenv import load_dotenv
from flask import Flask, request, session
from twilio.twiml.messaging_response import MessagingResponse

load_dotenv()
app = Flask(__name__)
app.secret_key = "super secret key"

AIRTABLE_BASE_ID = os.environ.get("AIRTABLE_BASE_ID")

In our 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 Input).

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.

@app.route('/send-survey', methods=['POST'])
def send_survey():
   airtable = Airtable(AIRTABLE_BASE_ID, 'Input')
   incoming_msg = request.values.get('Body', 'message error').lower()
   sender_phone_number = request.values.get('From', 'unknown_sender')
   twilio_phone_number = request.values.get('To', 'unknown_number')

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 sender_phone_number.

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 sender_phone_number.

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 sms_count.

   if not 'sms_count' in session:
       session['sms_count'] = 0
       session[sender_phone_number] = {}
      
   sms_count = session['sms_count']

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.

   if 'reset' in incoming_msg:
       del session['sms_count']
       session.pop(sender_phone_number, None)
       return("resetting the session")

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.

def get_message(sms_count):
   if sms_count == 0:
       sms_message = "On a scale of 1 (not likely) to 10 (extremely likely) how likely are you to recommend Twilio to a friend or colleague?""
   elif sms_count == 1:
       sms_message = "Why did you give us that score?"
   elif sms_count == 2:
       sms_message = "Is there anything we could do better next time?"
   elif sms_count == 3:
       sms_message = "Which Team were you a part of?"
   else:
       sms_message = "Thanks for your responses!"
   return sms_message

Back in our send_survey function, let’s add a new variable called sms_message. We’ll call our helper function here.

   sms_message = get_message(sms_count)

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.

   if sms_count >= 0 and sms_count <= 4:
       if sms_count == 0:
           session[sender_phone_number]['Twilio_Phone_Number'] = twilio_phone_number
       elif sms_count == 1:
           session[sender_phone_number]['Score'] = int(incoming_msg)
       elif sms_count == 2:
           session[sender_phone_number]['Reason'] = incoming_msg
       elif sms_count == 3:
           session[sender_phone_number]['Comments'] = incoming_msg
       elif sms_count == 4:
           session[sender_phone_number]['Team'] = incoming_msg
           airtable.insert(session[sender_phone_number])
       session['sms_count'] += 1

The last step of this section is to use the Twilio Helper Library to generate TwiML and send the SMS message to the participant.

   resp = MessagingResponse()
   msg = resp.message()
   msg.body(sms_message)

   return str(resp)

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.

@app.route('/get-scores', methods=['GET'])
def get_scores():
   phone_number = str(request.args.get('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 score_list.

   airtable = Airtable(AIRTABLE_BASE_ID, 'Input')
   airtable_data_dict = {}
   score_list = []

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.

for page in airtable.get_iter(view='nps', filterByFormula="({Twilio_Phone_Number}=" + phone_number + ")"):
       for record in page:
           num_id = record['fields']['ID']
           airtable_data_dict[num_id] = {}
           airtable_data_dict[num_id]['score'] = record['fields']['Score']
           airtable_data_dict[num_id]['reason'] = record['fields']['Reason']
           airtable_data_dict[num_id]['comments'] = record['fields']['Comments']

           score_list.append(record['fields']['Score'])

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.

def calc_nps(scores):
   promoters = 0
   neutral = 0
   detractors = 0
   for score in scores:
       if score >= 9:
           promoters += 1
       elif score >= 7:
           neutral += 1
       else:
           detractors += 1

   nps_total = (promoters - detractors) / len(scores) * 100

   return nps_total

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.

   nps_total_score = calc_nps(score_list)

   return {'overallNPS': nps_total_score, 'airtableData': airtable_data_dict}

Your finished main.py should look like this:

import os
from airtable import Airtable
from dotenv import load_dotenv
from flask import Flask, request, session
from twilio.twiml.messaging_response import MessagingResponse

load_dotenv()
app = Flask(__name__)
app.secret_key = "super secret key"

AIRTABLE_BASE_ID = os.environ.get("AIRTABLE_BASE_ID")

@app.route('/send-survey', methods=['POST'])
def send_survey():
   airtable = Airtable(AIRTABLE_BASE_ID, 'Input')
   incoming_msg = request.values.get('Body', 'message error').lower()
   sender_phone_number = request.values.get('From', 'unknown_sender')
   twilio_phone_number = request.values.get('To', 'unknown_number')

   # reset session
   if 'reset' in incoming_msg:
       del session['sms_count']
       session.pop(sender_phone_number, None)
       return("resetting the session")
      
   if not 'sms_count' in session:
       session['sms_count'] = 0
       session[sender_phone_number] = {}
      
   sms_count = session['sms_count']
   sms_message = get_message(sms_count)
  
   if sms_count >= 0 and sms_count <= 4:
       if sms_count == 0:
           session[sender_phone_number]['Twilio_Phone_Number'] = twilio_phone_number
       elif sms_count == 1:
           session[sender_phone_number]['Score'] = int(incoming_msg)
       elif sms_count == 2:
           session[sender_phone_number]['Reason'] = incoming_msg
       elif sms_count == 3:
           session[sender_phone_number]['Comments'] = incoming_msg
       elif sms_count == 4:
           session[sender_phone_number]['Team'] = incoming_msg
           airtable.insert(session[sender_phone_number])
       session['sms_count'] += 1

   resp = MessagingResponse()
   msg = resp.message()
   msg.body(sms_message)

   return str(resp)

@app.route('/get-scores', methods=['GET'])
def get_scores():
   phone_number = str(request.args.get('number'))

   airtable = Airtable(AIRTABLE_BASE_ID, 'Input')
   airtable_data_dict = {}
   score_list = []

   for page in airtable.get_iter(view='nps', filterByFormula="({Twilio_Phone_Number}=" + phone_number + ")"):
       for record in page:
           num_id = record['fields']['ID']
           airtable_data_dict[num_id] = {}
           airtable_data_dict[num_id]['score'] = record['fields']['Score']
           airtable_data_dict[num_id]['reason'] = record['fields']['Reason']
           airtable_data_dict[num_id]['comments'] = record['fields']['Comments']

           score_list.append(record['fields']['Score'])
   nps_total_score = calc_nps(score_list)

   return {'overallNPS': nps_total_score, 'airtableData': airtable_data_dict}

def calc_nps(scores):
   promoters = 0
   neutral = 0
   detractors = 0
   for score in scores:
       if score >= 9:
           promoters += 1
       elif score >= 7:
           neutral += 1
       else:
           detractors += 1

   nps_total = (promoters - detractors) / len(scores) * 100

   return nps_total

def get_message(sms_count):
   if sms_count == 0:
       sms_message = "On a scale of 1 (not likely) to 10 (extremely likely) how likely are you to recommend Twilio to a friend or colleague?""
   elif sms_count == 1:
       sms_message = "Why did you give us that score?"
   elif sms_count == 2:
       sms_message = "Is there anything we could do better next time?"
   elif sms_count == 3:
       sms_message = "Which Team were you a part of?"
   else:
       sms_message = "Thanks for your responses!"
   return sms_message

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:

  1. Navigate to the Buy a Number page in the console
  2. Search for a number that has SMS capabilities (in the United States, that’s all of them)
  3. Click “Buy Number”

If successful, you should come to a page that looks like this.

Active number picture from the Twilio console

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:

$ ./ngrok http 5000

It should look something like this:

Screenshot of ngrok running plus the https area

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.

Configure a messaging webhook in Twilio

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.

http://127.0.0.1:5000/get-scores?number=<your_phone_number_here>

JSON response from Airtable NPS calculation

The code displayed will show the Airtable data (named airtableData) as values listed by ID, and the calculated NPS score named overallNPS.

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 get_scores using 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.

Conclusion: Building an Airtable NPS calculator in Python

Today you:

  • 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.