Last updated: 19 October 2021 

A chatbot can be an excellent way to drum up interest in your products or services. As users engage with your chatbot, you may want to, eventually, present them with a quote for their purchase.

In this tutorial, we show you how you can create a quotation chatbot with a simple spreadsheet integration.

Let's get started.

Step 1: Prepare your spreadsheet

Before we dive into the platform, you'll need to prepare your pricing matrix. For this tutorial, we'll imagine we are working for an online clothing brand.

Our operations are simple: we sell shoes, trousers, shirts, and jumpers. Each item of clothing can be purchased in red, blue, green, yellow, or black.

Our fictional company is not very sophisticated, but that'll do for this tutorial.

This is how our pricing matrix would look like, in a spreadsheet:

Column A shows us colours, row 1 shows us the clothing items. As you can see, each item + colour has a different price. For example, a black shirt costs £45 while a blue shirt costs £34.

As you prepare your own spreadsheet, here are the rules to follow:

  • Row 1 and column A must be used as respective headers.

  • You must start listing your data inside row 1 and column A. No empty rows or columns before or above the data.

  • Your column headers (everything inside row 1) should be lowercase, don’t include any capitals.

  • You must save the spreadsheet as a .csv.

Step 2: Prepare your variables

Our pricing matrix has two variables: items and colours. 

Only when we have both (e.g. shoes + red) can we actually return a price.

Log into your platform and go to SettingsVariables. Create two variables.

As we ask users what they are looking for, we will store their answers into these variables and use them in our spreadsheet looking to return the right cost.

Step 3: Create the quoting conversation

Head over to the Builder and create a new conversation.

We're going to create a simple 3-step conversation. We will:

  • Ask the user to pick a colour.

  • Ask the user to pick an item of clothing.

  • Give the user the cost of the item.

Trigger

Give your conversation a simple trigger, such as an exact match keyword. For now, we want to keep this simple. Once you're all set, feel free to change this trigger to whatever you need.

First question: colour

The first step of the conversation will ask the user which colour they're interested in. It looks something like this:

Hit Save.

Second question: item

Click on the + to create a new step. This step will ask the user to pick an item:

Hit Save.

Quote

Finally, we'll return the quote to the user. Hit the + button to create the final step. For now, we will use placeholder content. We will get back to edit this later.

Hit Save.

Variables

Now that we have our three steps, let's go through each of them and log our variable. In step 1, click Options and go to Transitions.

Under Save variables, select Colour.

Repeat the same process for step 2, selecting the Item variable.

Want to make this conversation even slicker? Use custom composers to force your users to pick from a list of items and colours, instead of providing them with free text. Learn about composers.

Step 4: Create the spreadsheet integration

Go to Integrations. Under the spreadsheet section, click Upload spreadsheet.

Give your spreadsheet a name and hit Create.

Once you have, the upload button should be available. Click it and upload your .csv file.

Hit Close.

Click Lookups. We will create the formula that will, once provided with both pieces of information, help your chatbot find the right price in the matrix.

Name and describe your lookup.

Set up the following lookup:

The formula, in human language, says: "compare the item the user has sent and find it in this list". This allows us to hone in on one row.

We will then use our zero indexing method to move laterally along the row and find the corresponding answer.

More on zero indexing.

Hit Save.

Step 5: Add the spreadsheet integration to the conversation

We're almost ready. We've got a conversation, a spreadsheet, and a good lookin' lookup. Let's tie it all together.

Head back to Builder and your conversation.

On the last step (where you intend on displaying the cost), hit Options

Go to Message content and click Advanced settings.

Under Data type, find Use spreadsheet values. Pick your spreadsheet and the lookup you just created:

Hit Save.

Step 6: Update the lookup variable

When we first created the conversation, we put a placeholder on the actual pricing bit:

Now that we've set everything up, we can replace this placeholder with the real variable. Here's what we should set up:

We're cleverly using our zero-indexing to tell the lookup: 'find the colour the user has given us down the list of colours on our spreadsheet, then use the item the user has given up to zero in on the exact price."

Lo and behold, we've got a price!