Building a Slackbot to answer analytics questions from your database
Tags: ruby, productivity, tutorial
Published: 2019-06-05 15:01:56

---
title: Building a Slackbot to answer analytics questions from your database
published: true
description: We'll show you how to build a Slackbot that's smart enough to look up data in the database to answer questions. Anyone from the engineering or marketing team can get the latest data simply by asking the chatbot. It can answer a question right there in the channel without sending people to a separate web page.
tags: #ruby #productivity #tutorial
cover_image: https://thepracticaldev.s3.amazonaws.com/i/lbmmqhdpx45umryldhdy.jpg
---

Any business, whatever size or industry, runs on information. A persistent challenge for the DevOps team is finding faster, easier and better ways of getting information into the hands of business users when and where they need it. This used to require using a business intelligence (BI) solution or running custom queries against the database. What if it were as simple as asking a chatbot? No need to worry about reports or queries. The chatbot can answer a question right there in the channel where your whole team can see it.

[Kajabi](https://kajabi.com/), providers of a digital product delivery platform, [solved this problem](https://www.heroku.com/podcasts/codeish/3-spreading-the-database-love) by developing a simple chatbot to answer business questions. Anyone from the engineering or marketing team can get the latest data simply by asking the chatbot. It’s smart enough to look up data in the database to answer questions. 

The following diagram shows how the solution works. When the chatbot receives a message from a Slack user, a request is sent to a pre-established [dataclip](https://data.heroku.com/dataclips), which responds with real-time data fetched directly from the production database. The results are displayed in the chat window, nicely formatted for the user. Once the chatbot is set up, new requests are easy to create and share with the relevant business audience for their use.

![Lita chatbot from Datclips](https://thepracticaldev.s3.amazonaws.com/i/4hfhchjz9hqhv8c6n431.jpg "Lita chatbot")
_Illustration of Lita chatbot fetching data from Dataclips_


# A cost-saving alternative to BI and dashboards 

The chatbot approach offers an alternative to writing ad-hoc analytical queries and creating an entire infrastructure to expose them. Most companies address the demand for timely business data with full-fledged business intelligence solutions or by building dashboards that end up being web applications—both of which are very costly in terms of development and maintenance.

Dashboards require the DevOps team to deploy, maintain and upgrade your application and databases, while the development team is forced into updating the pipeline as well as adding features down the line to satisfy the more complex use cases. People also have to look at a physical dashboard in the office or pull up a separate report to see the latest results, which limits visibility.

A chatbot makes the data instantly visible to your team right in the chat room where everyone is already looking, both in-office and remote staff. When you are discussing a topic and have a question, you can see and share that insight with everyone in the group. There’s no need to click a link to open up a separate report. Now business users in marketing, finance and operations, for example, benefit from ready access to insights they need to help the company grow.

Furthermore, instead of creating custom data dumps and reports, this frees up the DevOps team to work on higher value projects. It will take time to initially set up the chatbot, but adding additional prompts is relatively quick.


# How to create your own chatbot

[Lita](http://www.lita.io/) makes it easy to create your own chatbots using Ruby. It can query data from the database using a dataclip. With Dataclips, anyone on your team familiar with SQL can easily create reports against live production data and securely [share the results within your organization](https://devcenter.heroku.com/articles/dataclips) using a unique URL. The chatbot component makes it easy for business users to run the queries against Dataclips. The chatbot extracts the results from that URL and displays it within the Slack conversation.

With this tutorial, you’ll learn how to replicate Kajabi’s setup by creating a chatbot that can access your databases through Dataclips and provide your end users with relevant insights.


# Creating a dataclip

In this tutorial, our example business is tracking clicks from various sources, including social networks and websites. The dataclip will be used to provide the top sources of clicks. This step of the tutorial assumes that you have access to a [Heroku Postgres](https://devcenter.heroku.com/articles/heroku-postgresql) database.

```sql
-- Top sources of clicks
SELECT button_id, SUM(clicks) AS clicks FROM button_click GROUP BY button_id ORDER BY clicks DESC LIMIT 10;
```

If you are missing a schema to run analytical queries on, you can use the example one that will be used across this blog post:

```sql
CREATE TABLE button_click (
  id serial PRIMARY KEY,
  "uuid" VARCHAR(37),
  button_id VARCHAR(90),
  created_date TIMESTAMP NOT NULL DEFAULT now(),
  clicks integer
);
```

And you can populate with insert queries like the following:

```sql
INSERT INTO button_click("uuid", button_id, created_date, clicks) VALUES ('b2344e6d-b864-418f-91e7-28748bb94487', 'facebook', '2019-04-19T20:11:49-07:00', '3');
```

Now, proceed to access your [Dataclips page](https://data.heroku.com/dataclips) and click on **New Dataclip**, assign it a name,  and select a datastore (your database). Then fill the query section on the page with the previous query:

![New Dataclip](https://thepracticaldev.s3.amazonaws.com/i/zwhocnfadn9drhu5rc09.png "New Dataclip")

Click **Save & Run** and the query is now presented in a table format with data, as well as the option to view it as a chart:

![data chart](https://thepracticaldev.s3.amazonaws.com/i/cmf1wcmlsv60a5iaygak.png "data chart")

Finally, copy the url by clicking on **Download/Export** and selecting the text field **JSON URL**:

![JSON URL](https://thepracticaldev.s3.amazonaws.com/i/em3otp6g75o2i12vl0lq.png "JSON URL")

Take a note of the URL or export it to the environment variable `DOT_API_URL` (you’ll use it when developing the chatbot).

# Setting up Lita

There are a lot of libraries out there to create a chatbot. In this tutorial, you’ll learn to use [Lita](https://www.lita.io/), a framework that dramatically simplifies the process while offering great extensibility. The installation process requires the following software:

*   [Ruby](https://www.ruby-lang.org/en/downloads/) (this tutorial uses version 2.6.1)
*   [RubyGems](https://rubygems.org/)
*   [Bundler](https://bundler.io/) version 2 (Ruby gem)
*   [Redis](https://redis.io/download)
*   Access to a [Slack workspace](https://slack.com/create) as admin
*   The previously created dataclip

Install Lita 4.7.1 (the version used for this tutorial) by running:

```
$ gem install lita --version 4.7.1
Successfully installed lita-4.7.1
1 gem installed
```

Then, create a Lita project named “Dot,” which will serve as baseline to which you will add the Ruby code for the bot:

```
$ lita new dot
      create  dot
      create  dot/Gemfile
      create  dot/lita_config.rb
```

Run `cd dot` and reconfigure the `Gemfile` to include the required dependencies:

```
source "https://rubygems.org"
ruby ">= 2.6.1"

gem "lita",       "~> 4.7.1"
gem "lita-slack", "~> 1.8.0"
gem "httparty",   "~> 0.17.0"
```

Finally, install the dependencies by using` bundle install`. The two additional gems present in the Gemfile are:

*   [lita-slack](https://github.com/litaio/lita-slack), a Slack [adapter for Lita](https://docs.lita.io/plugin-authoring/adapters/)
*   [httparty](https://github.com/httprb/http), a simple HTTP client for Ruby

The last step of the setup involves changing Lita’s configuration file `lita_config.rb` to use the Slack adapter and to access the Redis database (needed to run Lita):

```rb
Lita.configure do |config|
  # This is your bot name
  config.robot.name = "Dot"

  config.robot.locale = :en

  config.robot.log_level = :info
  config.robot.admins = [ENV.fetch("DOT_ADMIN_ID")]

  config.robot.adapter = :slack
  config.adapters.slack.token = ENV.fetch("DOT_SLACK_TOKEN")

  config.http.port = ENV["PORT"] || 3000
  config.redis = { url: ENV.fetch("REDIS_URL") }

  dataclip = config.handlers.dataclip_handler
  dataclip.top_clicks_all_time_url = ENV.fetch("DOT_API_URL")
end
```

Additionally, you will need to set these environment variables when launching the application:

*   `DOT_ADMIN_ID` is your user ID on Slack
*   `DOT_SLACK_TOKEN` is the token obtained by [adding Lita integration to your Slack workspace](https://my.slack.com/services/new/lita)
*   `PORT` is used by Lita to run a tiny web server to allow the bot to accept input over HTTP, as well as creating nicely-formatted web pages that you can provide as responses to some commands
*   `REDIS_URL` is a string that specifies the details for connecting to your Redis server using the [redis-url format](https://github.com/ddollar/redis-url)
*   `DOT_API_URL` is the url you copied previously when setting up the dataclip; it is used by the bot to provide the answer to the user request


# Writing the handler

After configuring Lita, it’s time to add functionality to respond to the chat command “Top source of clicks.” To get there, make these changes:

*   Add at the top of your `lita_config.rb` file the line `require_relative "./dataclip_handler"`
*   Create the file `dataclip_handler.rb`

The file `dataclip_handler.rb` is a [handler](https://docs.lita.io/plugin-authoring/handlers/) in Lita’s terms, which allows your bot to respond to chat commands and events and write chat messages in response. Paste the following code in the file:

```rb
module Lita
  module Handlers
    class DataclipHandler < Handler
      config :top_clicks_all_time_url

      route(/products\shave\sthe\smost\sclicks/, command: true) do |response|
        url = config.top_clicks_all_time_url
        http_response = HTTParty.get(url, follow_redirects: true)
        data = JSON.
          parse(http_response.body).
          fetch("values").
          lazy.
          map { |arr| ({ name: arr[0], clicks: arr[1] }) }.
          take(5)

        msg = "The products with the most clicks are:\n"
        data.each do |source|
          msg << "- *#{source[:name]}* with #{source[:clicks]} clicks\n"
        end

        response.reply(msg)
      end
    end

    Lita.register_handler(DataclipHandler)
  end
end
```

Let’s go over each relevant line:

```rb
class DataclipHandler < Handler
```

The `Handler` class will provide various class methods, the most interesting one being `route,` which allows listening to chat messages.

```rb
config :top_clicks_all_time_url
```

This line allows the handler to accept a configuration option, which is provided in `lita_config.rb`. You fetch an environment variable with the following code:


```rb
dataclip = config.handlers.dataclip_handler
dataclip.top_clicks_all_time_url = ENV.fetch("DOT_API_URL")
```

The next line is:

```rb
route(/products\shave\sthe\smost\sclicks/, command: true) do |response|
```

It instructs Lita to listen to chat messages **addressed directly to the bot** (`command: true` option) that contain the sentence “products have the most clicks” and to skip general unaddressed messages. A valid chat message would be:

```
@dot What products have the most clicks?
```

The answer to the query is determined by the content of the _do block._ In this case, the next line states:

```rb
url = config.top_clicks_all_time_url
http_response = HTTParty.get(url, follow_redirects: true)
```

The http_response contains a string in JSON format that looks like:

```json
{
  "title": "Top source of clicks all time",
  "values": [
    ["msr-reactor-2.5l-stove-system", 120],
    ["browse-kits", 93],
    ["the-camper-kit", 92],
    ["facebook", 92],
    ["the-chemex-kit", 90],
    ["support", 83],
    ["chemex-classic-6-cup-coffee-maker", 81],
    ["the-french-press-kit", 66],
    ["contact-us", 58],
    ["the-pour-over-kit", 50]
  ],
  "fields": ["button_id", "clicks"],
  "types": [1043, 20],
  "type_names": ["text", "integer"],
  "started_at": "2019-04-21 02:10:09 +0000",
  "finished_at": "2019-04-21 02:10:09 +0000",
  "checksum": "cf491f4dd64f044eb2e41f04676f5dca"
}
```

So the following lines will be used to extract the results from the `values` key and expose them in a more usable format:

```rb
data = JSON.
  parse(http_response.body).
  fetch("values").
  lazy.
  map { |arr| ({ name: arr[0], clicks: arr[1] }) }.
  take(5)
```

First, the response body is parsed, and the values are extracted. Then, each entry of the array is converted to a hash with the format `{ name: "source_name_here", clicks: 123 }`. Last, only the first five results are displayed to prevent overrunning the chat room.

With the data fetched and nicely prepared, it’s possible to create the chat message that the bot will write:

```rb
msg = "The products with the most clicks are:\n"
data.each do |source|
  msg << "- *#{source[:name]}* with #{source[:clicks]} clicks\n"
end
```

This code is straightforward: it creates a chat message in the variable `msg` and fills it with a bullet list made up of the five leading entries and the number of clicks.

The last line is:

```rb
response.reply(msg)
```

This commands the bot to reply with the previously built chat message.

There is another important line in the file which is outside the `route` block:

```rb
Lita.register_handler(DataclipHandler)
```

This instructs Lita to load the handler on boot, so that the code can be executed.


# Interacting with your chatbot

If you followed this tutorial up to this point, you are ready to test your application. Inside the `Dot` folder, run the following command:


```
$ bundle exec lita start
[2019-04-25 06:18:10 UTC] INFO: Connected to Slack.
```

The application will start and greet you with a log entry stating “Connected to Slack.”

Access your Slack workspace, invite the chatbot in a room and send a chat message addressed to it with the content:

```
@dot What products have the most clicks?
```

The bot will respond based on the content of your dataclip:

![bot](https://thepracticaldev.s3.amazonaws.com/i/gwangdnmim6qzn3pdlb5.png "bot")

You can extend the functionality of your chatbot by adding the ability to [turn Slack messages into SQL queries.](https://seekwell.io/) This lets your team experiment before creating the dataclip and simplifies customization. 

# Better visibility for business insights

With this tutorial, you were able to create a dataclip and connect it to a Slackbot so that every marketing team member could access the query at any time. This configuration is not only extremely simple, but it also provides a much higher degree of visibility for your business analytics by injecting the process seamlessly into the users’ existing workflow.