How to predict purchase intent using SQL
Tags: machinelearning, sql, nlp, mindsdb
Published: 2023-01-28 12:18:33

Customer service is an essential part of any business. In the end it can determine whether a sale is made or not. Currently, most customers choose text based messaging to communicate with companies ([Salesforce](https://www.salesforce.com/resources/research-reports/state-of-service/)). It does not matter if it is email or instant messaging as long as you reach customers fast and reliably. This can be especially challenging in e-commerce, where customers send thousands of messages about different products or services daily. With limited resources it's impossible to get to all customers instantly. What can help in such cases is to identify which customers should be served as soon as possible and which could wait a little longer.

In this tutorial, you will learn how to use **MindsDB** and a pre-trained text classification model from **HuggingFace**, to carry out purchase intent prediction inside your database. All without leaving SQL thanks to the new **Hugging Face - MindsDB integration**!

At the end, you will be able to perform the same classification on your own data.

## Prerequisites

To follow along, you will need access to MindsDB. You can either install [MindsDB locally](https://docs.mindsdb.com/setup/self-hosted) or start instantly with the [cloud based version](https://docs.mindsdb.com/setup/cloud). You can sign up for a [free account at MindsDB Cloud here](https://cloud.mindsdb.com/register). You also need the demo dataset. You can download it from the [Registry of Open Data on AWS](https://registry.opendata.aws/pre-post-purchase-questions). The steps in this tutorial are performed on a **PostgreSQL** database, but you can use any other data source that is currently supported by MindsDB. See [all the supported integrations here](https://docs.mindsdb.com/data-integrations/all-data-integrations).

## 1. Loading the demo data

All queries shown in this section can be run using the `psql` tool that comes with PostgreSQL.

To begin, create the `demo` database. You can also use an existing database.

```sql
CREATE DATABASE demo;
```

Don't forget to activate the created database.

```console
\c demo
```

Next, you need to create a table inside your database. To do so, run the following query.

```sql
CREATE TABLE customer_questions
(
    id serial,
    asin text,
    question text,
    item_name text,
    hours_diff double precision,
    label text,
    PRIMARY KEY (id)
);
```

The next step is to import the downloaded demo data. You can do it using the `\copy` command available in PostgreSQL. Below is an example. Remember to replace the file path to the location where you have downloaded the dataset.

```console
\copy customer_questions (asin, question, item_name, hours_diff, label) FROM 'path/to/dataset/PrePostQuestions.csv' DELIMITER ',' CSV HEADER ENCODING 'UTF8' QUOTE '\"';
```

After the import has finished, you can check if the data was loaded properly.

```sql
SELECT id,question FROM customer_questions LIMIT 5;
```


```console
 id |                                                                 question
----+------------------------------------------------------------------------------------------------------------------------------------------
  1 | Are boots cold reliable?
  2 | is the main body rubber or plastic?
  3 | Do you know the thread pitch/count for the tang?
  4 | The item ordered came tarnished and my son needed it by wednesday!!! is there any way you can send a replacement as soon as possible??!!
  5 | "I purchased these and now they look weird with my stock headlights.  What headlights would ""match"" these?"
(5 rows)
  ```

If everything went correctly, you should see a similar output to the one above. 

Great! You can now move on to the next section, where you will set up the purchase intent model inside MindsDB.

## 2. Configuring the model

To start, open the MindsDB user interface. If you are using MindsDB Cloud you can [log in here](https://cloud.mindsdb.com/login).
There, you will see a query execution interface where you can execute your queries.

Let's begin with connecting the PostgreSQL database with the MindsDB instance. Run the following query with the correct parameters for your own database.

```sql
CREATE DATABASE demo
WITH ENGINE = "postgres",
PARAMETERS = {
    "user": "postgres",
    "password": "my-secret-password",
    "host": "171.174.170.81",
    "port": "5432",
    "database": "demo"
    };
```

You can verify the connection by executing another  `SELECT` query to see the data.

```sql
SELECT id, question FROM demo.customer_questions limit 5;
```
![MindsDB SELECT query result](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/yz437z0hnjfsxmm36ymb.png)

You should see the same data as in the database itself.

Now, let's set up the pre-trained HuggingFace purchase intent model to classify the data.

```sql
CREATE MODEL purchase_intent_classifier
PREDICT label
USING
  engine = "huggingface",
  task = "text-classification",
  model_name = "j-hartmann/purchase-intention-english-roberta-large",
  input_column = "question";
```

Depending on the size of the model, the process of creating your *AI Table* might take a bit. So please be patient. You can check the status anytime by selecting your `MODEL` by name from the `models` table.

```sql
SELECT name, status FROM models WHERE name='purchase_intent_classifier';
```

You will know that your model is ready as soon as you see a `completed` status.

![MindsDB model status](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/wskk3cu6afyo7yia4oq3.png)

Now that the classifier is trained, you can move on to the last section, where you will learn how to actually use it to detect purchase intent.

## 3. Predicting the customer purchase intent

First, let's try out the model with an example customer message.

```sql
SELECT * FROM purchase_intent_classifier WHERE question ='How much does this t-shirt cost? I would like to try it on.';
```

Below is the output of the query. As you can see, the model predicted that the question carries a purchase intent!

![MindsDB example prediction](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/3pk72vc62j1igquj4oah.png)

What is left is to run the classifier against the whole dataset. To run the model over all the data points, you need to use a `JOIN` clause. 

```sql
SELECT questions.question, purchase_intent.label
FROM demo.customer_questions AS questions
JOIN purchase_intent_classifier AS purchase_intent;
```

![MindsDB example prediction on full dataset](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/an3wxnws9227ia6p2jxi.png)

As you can see, the `JOIN` clause applies the predictor on all questions from the `customer_questions` table. The output is the corresponding purchase intent label. The label `no` suggests that the customer does not have any purchase intent while the label `yes` indicates that the customer is interested in purchasing.

If you would like to run the prediction on a subset of the data, you can achieve that easily by adding a standard `LIMIT` SQL clause.

```sql
SELECT questions.question, purchase_intent.label
FROM demo.customer_questions AS questions
JOIN purchase_intent_classifier AS purchase_intent
LIMIT 100;
```

You can compose your prediction queries however you like. You just need to follow basic SQL syntax rules.

## Conclusion

In this tutorial, you have learned how to use MindsDB to perform purchase intent classification in customer messages. With this knowledge, you can easily start your own machine learning projects without leaving SQL. To learn more about using MindsDB with HuggingFace models check out the [NLP with MindsDB and HuggingFace section](https://docs.mindsdb.com/nlp/nlp-mindsdb-hf) of the official documentation. Additionally, you can find guides for different tasks such as time-series forecasting or regression at the [official](https://docs.mindsdb.com/sql/tutorials) and [community](https://docs.mindsdb.com/tutorials) tutorial sections. And in case you need any help with MindsDB feel free to join the [MindsDB community Slack](https://mindsdb.com/joincommunity).