How to predict purchase intent using SQL

Kamil Tyborowski — Sat, 28 Jan 2023 12:18:33 +0000

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.

Prerequisites

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.

CREATE DATABASE demo;

Don't forget to activate the created database.

\c demo

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

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 '\"';

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

SELECT id,question FROM customer_questions LIMIT 5;

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

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.

SELECT id, question FROM demo.customer_questions limit 5;

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

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.

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

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

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;

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;

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

DEV Community: Kamil Tyborowski

How to predict purchase intent using SQL

Prerequisites

1. Loading the demo data

2. Configuring the model

3. Predicting the customer purchase intent

Conclusion