Perform Product Analysis using SQL Server Full-Text Search. Part 1

This article considers how to perform basic product analysis using Full-Text Search.

Additionally, the readers are going to learn some practical examples of product analysis regarding sales with the help of Full-Text queries.

In a nutshell, this article emphasizes the importance of Full-Text Search, providing insights into data, along with answering many sales queries from the business point of view without the need for adopting more sophisticated methodologies, which do have their benefits, though.

About Product Analysis

Let us have a look at product analysis first.

What is product analysis?

Product analysis from the business point of view is the method of analyzing your product and its sales (data) in such a way that it helps you understand the strengths and weaknesses of your product.

For instance, the top management of a car selling company might want to know all car features related to safety, security, and sensors to make it a strong selling point for their car.

Another example is when a business may be interested to see all products sold during a rainy season that contain ‘rain’, ‘showerproof’ or ‘waterproof’ (in the description) to understand whether the customers want to buy any new products for a rainy season (from them) or not.

Why is product analysis necessary? 

Product analysis is the key to a successfully running business, and it can help bring out the pluses and minuses of a business. 

It can also help the business to make better decisions based on the existing purchases.

Apart from that, product analysis can assist the business in understanding the business trends and which way they are heading.

How to perform product analysis?

For one thing, the basic way of analyzing your product is by running SQL queries against the database that contains the product and its sales data.

Still, a better way to get more information is to use Full-Text Search to query your sales database since it offers improved search criteria that are not available in general SQL queries. 

Product analysis with Full-Text Search limitation

Although a considerably good analysis of a product can be performed with Full-Text Search, it has its own limitation.

For a more flexible and in-depth analysis and reporting of your data backed by a purpose-built infrastructure, you have to develop a Business Intelligence Data Warehouse Solution.

Prerequisites

Please go through the prerequisites in order to perform product analysis using Full-Text Search coming up later in this article.

Strong T-SQL skills

This article expects the readers (intending to implement the product analysis) to have strong T-SQL skills and to be able to comfortably write and run SQL queries against the database(s).

Basic and advanced understanding of Full-Text Search

The article assumes that the readers who intend to implement the walkthrough using Full-Text Search have a fairly good understanding of Full-Text Search and its uses.

Please refer to the article Implementing Full-Text Search in SQL Server 2016 for Advanced Users to get an advanced level understanding of Full-Text Search if you are already familiar with its basics.

However, please go through the article Implementing Full-Text Search in SQL Server 2016 for beginners if you need to develop a basic understanding before getting started with advanced uses of Full-Text Search. 

Full-Text Search Installed

Besides, this article expects you to have Full-Text Search already installed on your SQL instance.

Please install the Full-Text Search onto SQL server as follows:

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.

Please refer to the article Implementing Full-Text Search in SQL Server 2016 for beginners to get detailed information of the steps mentioned above.

Finally, check the Full-Text Search status by running the following T-SQL script against the master database:

-- Is Full-Text Search installed then 1 or 0
SELECT fulltextserviceproperty('IsFulltextInstalled') as [Full-Text Search]

The output should be as follows (if Full-Text Search is successfully installed):

Preparing Full-Text Search for Product Analysis 

Prepare to run Full-Text queries to perform product analysis after setting up a sample database.

Set up sample database (CarPlus)

Please set up a sample database called CarPlus as follows:

-- Create sample database (CarPlus)
CREATE DATABASE CarPlus;
GO
 
 
USE CarPlus;
 
-- (1) Create CarOrder table in the sample database
CREATE TABLE CarOrder (
    CarOrderId int NOT NULL IDENTITY(1,1),
    CustomerAge int,
    FeaturesLiked varchar(300) NOT NULL,
    CONSTRAINT [PK_CarOrder] PRIMARY KEY ([CarOrderId]))
 
 
-- (2) Add data to the table
SET IDENTITY_INSERT [dbo].[CarOrder] ON
INSERT INTO [dbo].[CarOrder] ([CarOrderId], [CustomerAge], [FeaturesLiked]) VALUES (1, 27, N'Traction Control System, Lane Change Alert, Reverse Parking Sensor, Start Button, Advanced Cruise Control, DAB Radio')
INSERT INTO [dbo].[CarOrder] ([CarOrderId], [CustomerAge], [FeaturesLiked]) VALUES (2, 43, N'Advanced Anti-lock Braking System, Immobiliser, Four Wheels Drive, Start Button, Advanced Cruise Control, DAB Radio')
INSERT INTO [dbo].[CarOrder] ([CarOrderId], [CustomerAge], [FeaturesLiked]) VALUES (3, 33, N'Traction Control System, Immobiliser, Hill Assist, Auto Climate Control, Advanced Cruise Control, DAB Radio')
INSERT INTO [dbo].[CarOrder] ([CarOrderId], [CustomerAge], [FeaturesLiked]) VALUES (4, 35, N'Isofix child seat fittings, Lane Change Alert, Collision Detection, Start Button, Advanced Cruise Control, Car Wifi')
INSERT INTO [dbo].[CarOrder] ([CarOrderId], [CustomerAge], [FeaturesLiked]) VALUES (5, 33, N'Advanced Anti-lock Braking System, Lane Change Alert, Reverse Parking Sensor, Auto Climate Control, Advanced Cruise Control, Car Wifi')
INSERT INTO [dbo].[CarOrder] ([CarOrderId], [CustomerAge], [FeaturesLiked]) VALUES (6, 27, N'Touch Sensor, Lane Change Alert, Child Safety Lock, Auto Climate Control, Advanced Cruise Control, DAB Radio')
INSERT INTO [dbo].[CarOrder] ([CarOrderId], [CustomerAge], [FeaturesLiked]) VALUES (7, 33, N'Traction Control System, Lane Change Alert, Reverse Parking Sensor, Navigation System, Advanced Cruise Control, USB Connection')
INSERT INTO [dbo].[CarOrder] ([CarOrderId], [CustomerAge], [FeaturesLiked]) VALUES (8, 33, N'Advanced Anti-lock Braking System, Lane Change Alert, Collision Detection, Navigation System, Advanced Cruise Control, DAB Radio')
INSERT INTO [dbo].[CarOrder] ([CarOrderId], [CustomerAge], [FeaturesLiked]) VALUES (9, 20, N'Traction Control System, Advanced Disc Brakes, Child Safety Lock, Navigation System, Advanced Cruise Control, DAB Radio')
INSERT INTO [dbo].[CarOrder] ([CarOrderId], [CustomerAge], [FeaturesLiked]) VALUES (10, 27, N'Isofix child seat fittings, Remote Locking, Tyre Pressure Alert, Start Button, Advanced Cruise Control, DAB Radio')
INSERT INTO [dbo].[CarOrder] ([CarOrderId], [CustomerAge], [FeaturesLiked]) VALUES (11, 46, N'Isofix child seat fittings, Immobiliser, Four Wheels Drive, Start Button, Advanced Cruise Control, Bluetooth')
INSERT INTO [dbo].[CarOrder] ([CarOrderId], [CustomerAge], [FeaturesLiked]) VALUES (12, 46, N'Traction Control System, Immobiliser, Collision Detection, Navigation System, Advanced Cruise Control, DAB Radio')
INSERT INTO [dbo].[CarOrder] ([CarOrderId], [CustomerAge], [FeaturesLiked]) VALUES (13, 33, N'Advanced Anti-lock Braking System, Lane Change Alert, Hill Assist, Auto Climate Control, Advanced Cruise Control, Bluetooth')
INSERT INTO [dbo].[CarOrder] ([CarOrderId], [CustomerAge], [FeaturesLiked]) VALUES (14, 33, N'Traction Control System, Lane Change Alert, Tyre Pressure Alert, Start Button, Advanced Cruise Control, Bluetooth')
INSERT INTO [dbo].[CarOrder] ([CarOrderId], [CustomerAge], [FeaturesLiked]) VALUES (15, 40, N'Isofix child seat fittings, Lane Change Alert, Hill Assist, Start Button, Advanced Cruise Control, DAB Radio')
INSERT INTO [dbo].[CarOrder] ([CarOrderId], [CustomerAge], [FeaturesLiked]) VALUES (16, 33, N'Isofix child seat fittings, Immobiliser, Four Wheels Drive, Auto Climate Control, Advanced Cruise Control, Bluetooth')
INSERT INTO [dbo].[CarOrder] ([CarOrderId], [CustomerAge], [FeaturesLiked]) VALUES (17, 27, N'Touch Sensor, Lane Change Alert, Four Wheels Drive, Auto Climate Control, Advanced Cruise Control, Car Wifi')
INSERT INTO [dbo].[CarOrder] ([CarOrderId], [CustomerAge], [FeaturesLiked]) VALUES (18, 18, N'Touch Sensor, Lane Change Alert, Collision Detection, Start Button, Advanced Cruise Control, DAB Radio')
INSERT INTO [dbo].[CarOrder] ([CarOrderId], [CustomerAge], [FeaturesLiked]) VALUES (19, 33, N'Isofix child seat fittings, Lane Change Alert, Reverse Parking Sensor, Navigation System, Advanced Cruise Control, USB Connection')
INSERT INTO [dbo].[CarOrder] ([CarOrderId], [CustomerAge], [FeaturesLiked]) VALUES (20, 33, N'Advanced Anti-lock Braking System, Advanced Disc Brakes, Child Safety Lock, Auto Climate Control, Advanced Cruise Control, Car Wifi')
INSERT INTO [dbo].[CarOrder] ([CarOrderId], [CustomerAge], [FeaturesLiked]) VALUES (21, 27, N'Advanced Anti-lock Braking System, Immobiliser, Child Safety Lock, Start Button, Advanced Cruise Control, Bluetooth')
INSERT INTO [dbo].[CarOrder] ([CarOrderId], [CustomerAge], [FeaturesLiked]) VALUES (22, 43, N'Advanced Anti-lock Braking System, Advanced Disc Brakes, Reverse Parking Sensor, Auto Climate Control, Advanced Cruise Control, Bluetooth')
INSERT INTO [dbo].[CarOrder] ([CarOrderId], [CustomerAge], [FeaturesLiked]) VALUES (23, 33, N'Advanced Anti-lock Braking System, Immobiliser, Child Safety Lock, Navigation System, Advanced Cruise Control, Bluetooth')
INSERT INTO [dbo].[CarOrder] ([CarOrderId], [CustomerAge], [FeaturesLiked]) VALUES (24, 46, N'Advanced Anti-lock Braking System, Remote Locking, Four Wheels Drive, Auto Climate Control, Advanced Cruise Control, USB Connection')
INSERT INTO [dbo].[CarOrder] ([CarOrderId], [CustomerAge], [FeaturesLiked]) VALUES (25, 43, N'Touch Sensor, Lane Change Alert, Hill Assist, Navigation System, Advanced Cruise Control, USB Connection')
INSERT INTO [dbo].[CarOrder] ([CarOrderId], [CustomerAge], [FeaturesLiked]) VALUES (26, 32, N'Advanced Anti-lock Braking System, Immobiliser, Reverse Parking Sensor, Navigation System, Advanced Cruise Control, Bluetooth')
INSERT INTO [dbo].[CarOrder] ([CarOrderId], [CustomerAge], [FeaturesLiked]) VALUES (27, 32, N'Isofix child seat fittings, Immobiliser, Four Wheels Drive, Auto Climate Control, Advanced Cruise Control, Car Wifi')
INSERT INTO [dbo].[CarOrder] ([CarOrderId], [CustomerAge], [FeaturesLiked]) VALUES (28, 26, N'Touch Sensor, Lane Change Alert, Hill Assist, Start Button, Advanced Cruise Control, DAB Radio')
INSERT INTO [dbo].[CarOrder] ([CarOrderId], [CustomerAge], [FeaturesLiked]) VALUES (29, 43, N'Traction Control System, Lane Change Alert, Child Safety Lock, Start Button, Advanced Cruise Control, USB Connection')
INSERT INTO [dbo].[CarOrder] ([CarOrderId], [CustomerAge], [FeaturesLiked]) VALUES (30, 26, N'Touch Sensor, Remote Locking, Reverse Parking Sensor, Navigation System, Advanced Cruise Control, Bluetooth')
SET IDENTITY_INSERT [dbo].[CarOrder] OFF;
GO

Identify the product 

The sample CarPlus database contains the following information, which is stored in a single table to simplify database structural complexity and to entirely focus on product analysis:

1. Order Number (for the car purchased)
2. The age of the customer buying the product (car)
3. Features (of the car) that the customer liked

Please note that the product we have chosen for the analysis in this article is the car purchased by a customer.

However, we might be more interested in the features the customer liked when he bought the car because these features have crucial information for the business. 

The CarOrder table of the database is represented as follows:

Create a Full-Text catalog

The first step once the sample database (CarPlus) has been created and populated successfully is to create a Full-Text Catalog.

This enables to implement the Full-Text Search.

Please create a Full-Text Catalog by running the following T-SQL script against the sample database:

-- Create Full-Text Catalog 
CREATE FULLTEXT CATALOG CarCatalog AS DEFAULT;  
GO  

Define a Full-Text index on FeaturesLiked column

Since FeaturesLiked column is going to be the central column used in product analysis, we must define a Full-Text Index on it to run Full-Text queries.

Write the following T-SQL script to define a Full-Text Index:

-- Define Full-Text Index on FeaturesLiked
CREATE FULLTEXT INDEX ON dbo.CarOrder(FeaturesLiked)   
   KEY INDEX PK_CarOrder
   WITH STOPLIST = SYSTEM;  
GO 

Please note that you can only define a full-text index on one column of the table, so please choose it very wisely.

Total orders by the customers’ age

Let us first do a quick analysis to see how many cars were sold to the customers belonging to a particular age group by running the following script:

--View total orders by customers age
SELECT CustomerAge,count(CarOrderId) as TotalOrders FROM dbo.CarOrder
GROUP BY CustomerAge
ORDER BY count(CarOrderId) DESC

The output is as follows:

The analysis is going to get more interesting when we apply Full-Text Search.

Child Safety Features’ Analysis (Full-Text Search) 

The business is eager to know the child safety features that the customers liked when they bought the cars.

Finding top 3 child safety features the customers liked

Let us first find top 3 child safety features that the customers liked when they purchased the car, and this can be found by using Full-Text Search as follows:

--View Top 3 child safety features liked using full-text search
SELECT TOP 3
  O.CarOrderId
 ,O.CustomerAge
 ,O.FeaturesLiked
FROM dbo.CarOrder O
WHERE CONTAINS(FeaturesLiked, '"*child*"')

The results are as follows:

Finding child safety features other than Isofix and Safety Lock

However, to make sure that these are the only two child safety features which the customers who bought the cars (in this season) liked, we need to modify our Full-Text query to examine if there are any more child safety features:

To find this, run the following Full-Text Search query:

--View child safety features other than isofix and safety locked liked by customers
SELECT
  O.CarOrderId
 ,O.CustomerAge
 ,O.FeaturesLiked
FROM dbo.CarOrder O
WHERE CONTAINS(FeaturesLiked, '"*Child*" AND NOT "Child Safety Lock" AND NOT "Isofix*"')

The output is as follows:

Analysis Results’ Summary

In light of the outputs above, we can easily say that the customer liked the following child safety features:

1. Isofix child seat fittings
2. Child Safety Lock

We are now left with the two following possibilities:

1. These are the only child safety features that the business offers.
2. There were some other child safety features offered by the business, but the customers did not prefer them when buying the cars.

Based on the results, we have to make sure these child safety features will not be replaced or excluded in the next season since they seem to be a strong selling point for the business.

Brakes Features’ Analysis

The next thing for the business to see is what type of features related to brakes the customers liked so that it can focus on those features in the upcoming sales.

Search for brakes features the customers liked

To find Top 5 brake-related features that the customers liked, we can run the following Full-Text query:

-- Searching top 5 features related to brakes liked by the customers (full-text query)
SELECT TOP 5 O.CustomerAge,O.FeaturesLiked FROM dbo.CarOrder O
WHERE CONTAINS(FeaturesLiked,'FORMSOF(INFLECTIONAL,brake)')

The output is as follows:

Analysis Results’ Summary

As a result of the Full-Text query above, we now know that the features related to brakes that the customers liked at the time of buying cars were as follows:

1. Advanced Anti-lock Braking System
2. Advanced Disc Brakes

It seems that out of the many features related to brakes, this season, the customers chose the two above, and perhaps some more information may help the business to improve their future decisions.

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

Stay in touch since Part 2 of this article contains a slightly more advanced form of a product analysis using Full-Text Search.

Things to do

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

1. Try to get correlated information by finding the customers who liked both brakes and child safety features when buying the car(s).
2. Keeping this article in mind, search for the customers who liked the Isofix child seat fittings feature but not the Child Safety Lock feature.
3. Try to search for the customers who did not like Advanced Disc Brakes but did like Advanced Anti-lock Braking System.