Learn to Perform Product Analysis with SQL Server Full-Text Search. Part 2

This article is dedicated to a slightly more complex product analysis with Full-Text Search as opposed to the first part where we looked at how to perform a basic product analysis.

Here, the readers are going to implement some comprehensive examples of product analysis from a sales point of view with the help of Full-Text queries.

This article unfolds the strength of Full-Text queries when used with analytic functions in performing a product analysis.

Prerequisites

I suggest you go through the prerequisites of this article before you start implementing product analysis examples.

Part 1. Essentials

We assume that you have gone through Learn to Perform Product Analysis using SQL Server Full-Text Search. Part 1 and have the capability to perform a basic product analysis with the help of the examples mentioned in the first part.

T-SQL and Full-Text Search understanding

To feel comfortable while reading this article, we recommend readers to have a good understanding of T-SQL scripts and Full-Text Search.

Refer to Implementing Full-Text Search in SQL Server 2016 for Advanced Users to get an advanced level understanding of Full-Text Search provided you are already familiar with its basics. Still, we advise going through the article Implementing Full-Text Search in SQL Server 2016 for beginners if you need to develop a basic understanding before proceeding with advanced uses of Full-Text Search.

Full-Text Search installed

To successfully proceed with the article, ensure you have installed Full-Text Search on your SQL instance.

Follow these steps to install the Full-Text Search on SQL server:

1. Run SQL Installer.
2. Run Setup File.
3. Add as a Feature.
4. Choose your Current Server.
5. Select Instance Feature to be added.

You can also refer to the article Implementing Full-Text Search in SQL Server 2016 for beginners to get detailed information on the steps mentioned above.

Sample database (CarPlus)

You should follow the instructions in the first part if you have not yet set up the sample database. Note that you will have to set up the CarPlus sample database to implement the examples in this article.

Full-Text catalog and Full-Text index

We also expect you to define the Full-Text catalog and Full-Text index in the FeaturesLiked column.

Please refer to Learn to Perform Product Analysis using SQL Server Full-Text Search. Part 1 to perform the steps above unless you have already completed them.

Analysis 1: Full-Text Search for sensors

Let’s say we had several out of the box sensors provided with the car, and now, the top management is eager to know the following things:

1. Did the customers who bought the cars like any out of the box sensors?
2. What kind of sensors did they like?
3. How many sensors of a particular type did they like?
4. Can we compare all the different sensors that the customers liked?

You can find the answers to all these questions with the help of Full-Text Search.

Searching sensors preferred by customers

In order to know whether the customers liked any of the car sensors when they placed their orders, we have to run a full-text query as follows:

--View Orders where customers liked Sensors
SELECT
CarOrderId
,FeaturesLiked
FROM dbo.CarOrder
WHERE CONTAINS(FeaturesLiked, 'Sensor')

You will then see the following results:

One thing is certain: the customers liked out of the box sensors when they bought their cars. That’s because Full-Text Search for sensors returned a number of results.

The search informs that we have at least two types of sensors that the customers liked:

1. Touch Sensor
2. Reverse Parking Sensor

It is safe to say that the customers liked at least the above-mentioned types of sensors.

Searching all parking sensors preferred by customers

Now, we need to use Full-Text Search to find all types of Parking Sensors that our customers liked.

We can find this out by using NEAR with the following search terms (as follows):

--View Orders where customers liked parking sensors
SELECT
FeaturesLiked
FROM dbo.CarOrder
WHERE CONTAINS(FeaturesLiked, 'Sensor NEAR Parking')

The output is as follows:

As a result, there seem to be seven of such cases where customers liked Parking Sensor. However, we need to go deeper to get more information.

Searching sensors other than parking sensors preferred by customers

Now, we would like to know the other side of the story, that is, to find out which customers liked the sensors other than Parking Sensor.

Use the following full-text query to search for the sensors that the customers liked other than Parking Sensor:

--View Orders where customers liked other than parking sensors
SELECT 
CarOrderId
,FeaturesLiked 
FROM dbo.CarOrder 
WHERE CONTAINS(FeaturesLiked,'Sensor AND NOT Parking')

The results of the above full-text query are as follows:

Apart from Parking Sensor, the customers liked Touch Sensor.

Comparing parking sensors with touch sensors, and with other sensors

The next big thing is to see if we can compare Parking Sensor with Touch Sensor with other types of sensors that the customers liked when they placed their orders to buy a car.

We also need to know the total number of orders placed this season to make the comparison more interesting.

However, this is not enough. We also need to understand the percentage of the similarity between the sensors.

You can find this out, using a full-text query with the help of variables and T-SQL aggregations as follows:

DECLARE @TotalParkingSensorLikes INT
DECLARE @TotalTouchSensorLikes INT
DECLARE @TotalOtherSensorLikes INT
 
 
DECLARE @TotalOrders INT
 
 
SET @TotalOrders=(SELECT COUNT(*) FROM dbo.CarOrder)
SET @TotalParkingSensorLikes=(SELECT COUNT(*) FROM dbo.CarOrder WHERE CONTAINS(FeaturesLiked,'Sensor NEAR Parking'))
SET @TotalTouchSensorLikes=(SELECT COUNT(*) FROM dbo.CarOrder WHERE CONTAINS(FeaturesLiked,'Sensor NEAR Touch'))
SET @TotalOtherSensorLikes=(SELECT COUNT(*) FROM dbo.CarOrder WHERE CONTAINS(FeaturesLiked,'Sensor AND NOT Touch AND NOT Parking'))
 
  
-- Total parking sensors likes vs touch sensors
SELECT @TotalOrders AS TotalOrders,@TotalTouchSensorLikes AS Total_Parking_Sensor_Likes,
(@TotalTouchSensorLikes*100/@TotalOrders) AS PercentLikes,
@TotalParkingSensorLikes AS Total_Touch_Sensor_Likes,
(@TotalParkingSensorLikes*100/@TotalOrders) AS PercentLikes,
@TotalOtherSensorLikes AS Total_Other_Sensor_Likes,
(@TotalOtherSensorLikes*100/@TotalOrders) AS PercentLikes

The output can be seen as follows:

Analysis results’ summary

We can conclude that the customers preferred the car Touch Sensor feature over the Reverse Parking Sensor feature. However, they were the only sensors that the customers liked. And the feature they liked most, which is Touch Sensor, only takes 23 percent of all the features they liked.

Analysis 2: Full-Text Search for Driving Assistance and Alerts

If we now assume that it was during this season that the car manufacturing company introduced a couple of driving assistance features including some alerts.

The business would like to know the following things:

1. What types of driving alerts and assistance features did the customers like?
2. How many customers liked the combination of alerts and assistance features?
3. Can we perform an age-based analysis of the customers who liked both alerts and assistance features?

Searching driving alerts and assistance features preferred by customers 

We can search for driving alerts and assistance features that the customers liked using a full-text query, but we should keep in mind that the word “Detect” can also be used as an alternative to “Alerts” as follows:

--Searching driving alerts and assistance features liked by customers
SELECT
CarOrderId
,FeaturesLiked
FROM dbo.CarOrder
WHERE CONTAINS(FeaturesLiked,'FORMSOF(INFLECTIONAL,Assisting) OR FORMSOF(INFLECTIONAL,Alerts)
OR FORMSOF(INFLECTIONAL,Detect)
')

The results of the above full-text query are as follows:

So, using a full-text query, we have found the answer to the first business question about the types of features and alerts preferred by the customers.

It could therefore be concluded that the customers liked the following types of driving alerts and assistance features:

1. Lane Change Alert
2. Hill Assist
3. Collision Detection
4. Tyre Pressure Alert

Searching customers who like all driving alerts and assistance features

A slightly more complicated question is to find how many customers liked all the driving assistance features and alerts, and this can be answered by a full-text query as follows:

--Searching order where both alerts and driving assistance features are liked by customers
SELECT
CarOrderId
,FeaturesLiked
FROM dbo.CarOrder
WHERE CONTAINS(FeaturesLiked,'FORMSOF(INFLECTIONAL,Assisting) AND (FORMSOF(INFLECTIONAL,Alerts) OR FORMSOF(INFLECTIONAL,DETECT)
)')

The output is as follows:

So, there are four orders where customers liked both driving assistance features and alerts.

Age-based analysis of the customers who liked driving assistance and alerts features

To get more information, we will try to understand the driving assistance features and alerts that the customers liked based on their age.

You can also perform this by using the combination of a full-text query and Analytical functions provided in T-SQL as follows:

-- Age based analysis of customers liking all driving alerts and assistance features
SELECT DISTINCT(CustomerAge),TotalOrders=COUNT(*) OVER (Partition BY CustomerAge),
FIRST_VALUE(CustomerAge) OVER (Order by CustomerAge) as YoungestCustomer,
LAG(CustomerAge,1,0) OVER (ORDER BY CustomerAge) as YoungerCustomerOrSameAge,
LEAD(CustomerAge,1,0) OVER (ORDER BY CustomerAge) as OlderCustomerOrSameAge,
FeaturesLiked as Driving_Assistance_or_Alerts_Features
FROM dbo.CarOrder
WHERE CONTAINS(FeaturesLiked,'FORMSOF(INFLECTIONAL,Assisting) OR FORMSOF(INFLECTIONAL,Alerts)
OR FORMSOF(INFLECTIONAL,Detect)')
ORDER BY TotalOrders DESC

The output is as follows:

Please refer to the article The Art of Aggregating Data in SQL from Simple to Sliding Aggregations to learn more about how to use analytic functions in T-SQL.

Analysis results’ summary

In the light of the above analysis using Full-Text Search, it is clear that the driving assistance and driving alert features that the customers liked mostly belong to the age group of 35.

So, the customers aged 35 liked these features most, while the youngest customer who liked these features is aged 18.

The oldest customer who liked these features is 52 years old.

It can be inferred from the above analysis that mostly the customers aged between 18 and 52 enjoyed the driving assistance and driving alert features, where the customers who liked these features more than others are 35 years old.

Congratulations, you have successfully learned how to perform a slightly complex product analysis using Full-Text Search.

Things to do

Now that you can do the product analysis with Full-Text Search, I encourage you to try the following:

1. Try to get correlated information by finding the customers who liked both sensors and the driving assistance features but not the alerts when they bought the car(s). 
2. Keeping this article in mind, search for the customers who liked the alerts but not the driving assistance features.
3. Try to perform customers’ age-based analysis for the sensors similar to the one we performed for driving assistance and alert features.