DEV Community

Cover image for How to predict purchase intent using SQL
Kamil Tyborowski
Kamil Tyborowski

Posted on


How to predict purchase intent using SQL

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


To follow along, you will need access to MindsDB. You can either install MindsDB locally or start instantly with the cloud based version. You can sign up for a free account at MindsDB Cloud here. You also need the demo dataset. You can download it from the Registry of Open Data on AWS. 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.

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.

Enter fullscreen mode Exit fullscreen mode

Don't forget to activate the created database.

\c demo
Enter fullscreen mode Exit fullscreen mode

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

CREATE TABLE customer_questions
    id serial,
    asin text,
    question text,
    item_name text,
    hours_diff double precision,
    label text,
    PRIMARY KEY (id)
Enter fullscreen mode Exit fullscreen mode

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.

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

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

SELECT id,question FROM customer_questions LIMIT 5;
Enter fullscreen mode Exit fullscreen mode
 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)
Enter fullscreen mode Exit fullscreen mode

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

WITH ENGINE = "postgres",
    "user": "postgres",
    "password": "my-secret-password",
    "host": "",
    "port": "5432",
    "database": "demo"
Enter fullscreen mode Exit fullscreen mode

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

SELECT id, question FROM demo.customer_questions limit 5;
Enter fullscreen mode Exit fullscreen mode

MindsDB SELECT query result

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.

CREATE MODEL purchase_intent_classifier
  engine = "huggingface",
  task = "text-classification",
  model_name = "j-hartmann/purchase-intention-english-roberta-large",
  input_column = "question";
Enter fullscreen mode Exit fullscreen mode

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.

SELECT name, status FROM models WHERE name='purchase_intent_classifier';
Enter fullscreen mode Exit fullscreen mode

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

MindsDB model status

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.

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

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

MindsDB example prediction

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.

SELECT questions.question, purchase_intent.label
FROM demo.customer_questions AS questions
JOIN purchase_intent_classifier AS purchase_intent;
Enter fullscreen mode Exit fullscreen mode

MindsDB example prediction on full dataset

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.

SELECT questions.question, purchase_intent.label
FROM demo.customer_questions AS questions
JOIN purchase_intent_classifier AS purchase_intent
LIMIT 100;
Enter fullscreen mode Exit fullscreen mode

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


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 of the official documentation. Additionally, you can find guides for different tasks such as time-series forecasting or regression at the official and community tutorial sections. And in case you need any help with MindsDB feel free to join the MindsDB community Slack.

Top comments (0)

An Animated Guide to Node.js Event Loop

>> Check out this classic DEV post <<