How to Build a Facebook Messenger Chatbot Powered by Fast SQL on CSV

A chatbot, like any human customer service rep, needs data about your business and products in order to respond to customers with the correct information. What is an efficient way to hook up your data to a chat application without significant data engineering? In this blog, I will demonstrate how you can build a Facebook Messenger chatbot to help users find vacation rentals using CSV data on Airbnb rentals.

Services Used

We will use the following services to implement our chatbot:

• Rockset - to serve SQL queries on CSV data to the bot
• Facebook Messenger - platform for building the bot
• Dialogflow - to understand user queries using machine learning

Loading the Airbnb Data into Rockset

Airbnb data is available in CSV format for different cities and is divided into listing, review, and calendar data. From the Rockset console, I uploaded these files for Amsterdam into three different collections (airbnb_listings, airbnb_reviews, and airbnb_calendar). You can also upload data for other cities into these collections if needed.

Alternatively, you may place your data in an Amazon S3 bucket and create a collection that continuously syncs with the data you add to the bucket.

Writing the Facebook Messenger Bot

Using the messenger bot tutorial, I created a Facebook page and webhooks to receive events from the chatbot. You can refer to and familiarize yourself with the Node.js project I created here.

app.js creates a HTTP server using Express. The server handles GET requests to verify webhooks and POST requests to respond to user messages.

// Accepts POST requests at the /webhook endpoint
app.post('/webhook', (req, res) => {
  let body = req.body;
  if (body.object === 'page') {
    body.entry.forEach(function(entry) {
        let event = entry.messaging[0];
        if (event.message && event.message.text) {
            // handle the message
            handleMessage(event);
        }
    });
    // Return a '200 OK' response
    res.status(200).send('EVENT_RECEIVED');
  } else {
    // Return a '404 Not Found'
    res.sendStatus(404);
  }
});

handleMessage.js interacts with Dialogflow and queries Rockset to answer users' inquiries.

Using Natural Language Processing with Dialogflow

In order to understand user messages and questions, I have integrated Dialogflow (apiai) in my project. Dialogflow allows you to create intents and extract meanings out of phrases using machine learning. To connect to Dialogflow from your application you need to use Client access token to create the client object.

I created an intent rentalcity and trained it with a few phrases to extract the requested city and date for the rental and the number of people in the party. Similarly, I created a few more intents to answer follow-up requests using contexts. The Dialogflow project export is included in the recipes github repository.

Interacting with the Chatbot

We have the bot set up and a mechanism to understand users' requests. All we need now is a way to translate the requests into meaningful responses. Using Rockset's Node.js client, I will query the the collections created in the first step.

Let's start interacting with the chatbot.

The intent to find listings uses the following SQL query:

with listings as (
    select id, name, price, property_type
    from airbnb_listings
    where lower(airbnb_listings.city) like :city 
        and airbnb_listings.accommodates::int >= :number
    order by airbnb_listings.number_of_reviews desc
)
select listings.id, listings.name, listings.property_type, listings.price
from listings, airbnb_calendar
where airbnb_calendar.date = :date and airbnb_calendar.available = :avail
    and airbnb_calendar.listing_id = listings.id
limit 1

This SQL query uses two collections airbnb_listings and airbnb_calendar to extract the rental with the highest number of reviews available on the given date.

To get more information for this listing, the user can reply with details.

To answer this we fetch the summary from the collection airbnb_listings for the listing_id returned in the previous query.

select summary from airbnb_listings where id = :listing_id

The user can also request the latest reviews for this listing by replying show reviews.

The SQL query to get the reviews for this listing:

select comments, date
from airbnb_reviews
where listing_id = :listing_id
order by date desc
limit 3

To check out another listing, the user types in next. This uses the offset SQL command to get the next result.

Summary

We now have a data-driven chatbot that understands users' requests for vacation rentals and responds using Airbnb listing and calendar data! Its ability to provide instant replies leveraging fast SQL queries on the Airbnb CSV data further enriches the customer experience. Plus it was relatively easy to connect the chatbot to the underlying Airbnb data set. The entire process of building the chatbot took me less than a day, from loading the dataset into Rockset and writing the chatbot to setting up interactions to respond with the relevant information.