Hello! I’m Yong Wang, a business analyst specializing in digital transformation for waste-to-energy plants. I recently became a contributor to the pgai extension and decided to share a pgai retrieval-augmented generation (RAG) use case.

In this post, I'll walk you through a valuable RAG concept for businesses, with technical steps to implement it. We’ll explore how to deploy pgai and leverage a large language model effectively in a virtual pizza shop scenario.

🔖

Editor’s Note: Pgai is an open-source PostgreSQL extension (GitHub ⭐s welcome!) that brings more AI workflows into PostgreSQL, like embedding creation and model completion. It makes it easier for PostgreSQL developers to become AI engineers and build search and RAG applications. Learn more about pgai.

Executive Summary: A RAG Use Case With Pgai

We’ll dive into how integrating pgai with AI-generated content (AIGC) and ChatGPT can transform customer relationship management (CRM) in the pizza industry. By leveraging AI-driven insights, we'll explore how customer satisfaction and operational efficiency can be enhanced in a virtual pizza shop.

The integration of AIGC is revolutionizing business practices, and this post illustrates how the application of ChatGPT and pgai, an AI extension for PostgreSQL, can improve customer interactions and provide real-time insights.

Case Study: Enhancing Customer Satisfaction in a Virtual Pizza Shop

As a business analyst, you are often tasked with understanding why sales orders have recently dropped from customer feedback databases and then delivering a business report for management improvement.

With pgai, you can simply ask SQL-based questions like:

why don't customers like our pizza?

And in seconds, pgai generates a business report! Here’s an example:

# Business Report: Customer Feedback on Pizza

## Summary of Customer Feedback

Based on the collected feedback regarding our pizza offerings, several key points emerge that reveal customer dissatisfaction:

1. **Quality Concerns**: A customer expressed extreme dissatisfaction, stating, \"The pizza was disgusting.\" This indicates potential issues with the ingredients or preparation. The mention of \"pepperoni made from rats\" suggests that there are serious concerns about the quality and safety of the ingredients used in our pizzas.

2. **Toppings Discontent**: Another feedback highlights a common polarizing topping—pineapple. While one customer remarked their dislike for pineapple on pizza, they also noted, \"it worked well on this pizza though.\" This suggests that while the specific customer generally dislikes pineapple, our pizza managed to appeal to them in this instance. This indicates an inconsistent acceptance of certain toppings based on individual taste preferences.

3. **Consumable Experience**: Additionally, a customer reported a drastic reaction after consuming the pizza, stating, \"I ate 11 slices and threw up.\" This raises significant concern regarding the overall quality and potentially the health impact of our product. The phrase \"tasty in both directions\" further suggests that while the taste may initially be appealing, it can lead to negative physical outcomes.

## Conclusion

In summary, customer feedback indicates major concerns regarding the quality of ingredients, specific topping preferences, and the overall dining experience. Immediate attention to these areas is essential to improve customer satisfaction and brand reputation. Future actions may include ingredient audits, customer preference surveys, and possibly recipe adjustments.

Amazing, isn't it? Let’s see how pgai works behind the scenes to help generate this report. I’ll detail the RAG process and our case study and outline a five-step approach designed to enhance CRM operations. RAG will be the backbone of this approach, allowing us to query existing data and generate new insights.

Run pgai

Let's use pgai to establish an integrated framework combining CRM, databases, and AIGC interfaces.

There are many ways to run pgai, but in this blog demo, we’ll use Docker. Run the TimescaleDB Docker image, and then enable the pgai extension.

Note: Run the following SQL command in the database client tools. I used DBeaver for this demo.

Create extension:

CREATE EXTENSION IF NOT EXISTS ai CASCADE;

We’ll use the gpt-4o-mini and text-embedding-3-small models. You can also use local models like Ollama, depending on your business needs.

Ensure the model connection is properly configured by setting your API key:

set ai.openai_api_key = 'replace you api key here or use pgai default api_key environment';
select pg_catalog.current_setting('ai.openai_api_key', true) as api_key;

Collect and Prepare the Customer Feedback

Listening to your customers is vital for sustained business success. Almost every business will listen to customer feedback and record it. So, let's create a customer feedback data table and store the demo customer voices as records.

We’ll keep the customer feedback on pizza in a pizza_reviews table that works as a minimal CRM table. The below SQL command will perform the following jobs:

-- clean the history test table to initialize
DROP TABLE IF EXISTS  PUBLIC.pizza_reviews  CASCADE;   -- CUSTOMER FEEDBACK STORED HERE
DROP TABLE IF EXISTS PUBLIC.pizza_reviews_embeddings CASCADE;  -- CUSTOMER FEEDBACK EMBEDDED 
DROP TABLE IF EXISTS  PUBLIC.ai_report CASCADE;      -- THE AI GENERATED BUSINESS REPORT
CREATE TABLE public.pizza_reviews  (
id bigserial NOT NULL,
product text NOT NULL,
customer_message text NULL,
text_length INTEGER GENERATED ALWAYS AS (LENGTH(customer_message)) stored,
CONSTRAINT pizza_reviews_pkey PRIMARY KEY (id)
);

Then, we'll insert the data into the pizza_reviews table:

INSERT INTO public.pizza_reviews  (product,customer_message) VALUES
	 ('pizza','The best pizza I''ve ever eaten. The sauce was so tangy!'),
	 ('pizza','The pizza was disgusting. I think the pepperoni was made from rats.'),
	 ('pizza','I ordered a hot-dog and was given a pizza, but I ate it anyway.'),
	 ('pizza','I hate pineapple on pizza. It is a disgrace. Somehow, it worked well on this izza though.'),
	 ('pizza','I ate 11 slices and threw up. The pizza was tasty in both directions.');

Embedding the Customer Feedback

Now, let’s create the embed table for the pizza_reviews table for future analysis. This table will store the embedded customer feedback to compare and calculate the L2 or cosine distance.

CREATE TABLE public.pizza_reviews_embeddings (
id bigserial NOT NULL,
text_id text NOT NULL, 
text_content text NOT NULL, -- it is same as pizza_reviews 
model_name text NOT NULL,
ntoken int4 NULL,
nlength int4 NULL,
embedding public.vector(1536) NOT NULL,
CONSTRAINT pizza_reviews_embeddings_pkey PRIMARY KEY (id)
);

In a real business scenario, the CRM database should have a large volume of data. The embedding process should also be done in advance via batch jobs. You can then insert the embedding results into an embedding table to answer questions.

with tmp as (
select
	tt.id, tt.customer_message,
	'text-embedding-3-small'::text as model_name,
	openai_embed('text-embedding-3-small',customer_message) as embedding
from
	pizza_reviews  as tt
)
insert into pizza_reviews_embeddings 
		(text_id, text_content, model_name, embedding )
	select 
		id, customer_message, model_name, embedding
	from 
		tmp;

Next, let's create an index for query speed optimization—this step is optional as we have little data in this demo.

Note: There are many index extensions for pgai vector features. In this case, we're using the ivfflat extension.

CREATE INDEX ON pizza_reviews_embeddings USING ivfflat (embedding vector_cosine_ops) WITH (lists='5');

Now, all the customer feedback is AI-ready for further analysis.

Ask Questions

Pizza shop sales can fluctuate for many reasons—competition, quality, and even something as unpredictable as the weather. As a business analyst, you’ve probably spent countless hours testing different hypotheses to figure out what's driving these changes.

In the past, this was a lengthy process, as you needed to test the hypotheses and get the final answers. Now, with pgai, you can generate a business report for each hypothesis in record time, quickly determining its validity and uncovering additional insights.

In this case, our first question is: "Why don't customers like our pizza?" This new business question can be answered via a simple SQL query to pgai to complete the RAG process. Pgai can help us:

Get the embedded question vector. "Why don't customers like our pizza?" will be converted to a numeric vector.
Compare the question vector to the embedded historical customer feedback (pizza_reviews_embeddings) using vector distance.
Use criteria to filter the most similar customer feedback. For demonstration purposes, we use a sample size of three (3), but the process is more complex in real business scenarios.

with
business_question as (
	select question 
	from 
		(values 
			('why customer do not like our pizza?')
			)as t(question)	
)
, embedding_question as (
	select 
		question, openai_embed('text-embedding-3-small',question) as embedding 
	from
		business_question
)
select
	eqt.question, 
	emt.text_content , 
	emt.embedding <-> eqt.embedding as similarity
from pizza_reviews_embeddings emt  cross join embedding_question eqt
order by emt.embedding <-> eqt.embedding
limit 3;

Note:

In our demo, a limit of three (3) is used as the criterion for selecting historical data. In a real use case, the criterion value should be chosen based on business needs.
At this step, we check the intermediate results and find some feedback clues: "Disgusting,” “hate,” “disgrace,” or “threw up."

Generate Answers

Now, we will complete the process from posing a question to comparing similarities, filtering results, and sending the data to ChatGPT to generate a final business report. Here is the SQL code:

-- create a build_pizza_report function to put an ai instruction in it, and call it in one sql command.
CREATE OR REPLACE FUNCTION build_pizza_report(_question text, _n_criteria smallint)
RETURNS SETOF ai_report AS
$$
BEGIN
RETURN QUERY
    
with
embedding_question as (
	select 
		_question as question, openai_embed('text-embedding-3-small',_question) as embedding
	), 
reasons as (
	select
		eqt.question, 
		emt.text_content , 
		emt.embedding <-> eqt.embedding as similarity
	from pizza_reviews_embeddings emt  cross join embedding_question eqt
	order by 
		emt.embedding <-> eqt.embedding
		limit _n_criteria
	)
,agg_resons as (
 	select 
 		question,  jsonb_pretty(jsonb_agg(text_content)) as reasons
 	from reasons
 	group by question
	)
,report_needs as (
	select 
	chr(10)||'// 1. requirements:
	// 1.1 generate a business report to answer user question with provided data.
	// 1.2 The report should be markdown format and less than 300 words' || chr(10) as report_needs,
	chr(10)||'// 2. data' || chr(10)as data_needs,
	chr(10)||'// 3. user question'|| chr(10)as user_question
 	)
,ai_report as (
		select 
			report_needs || data_needs ||  reasons  ||user_question || question as send_message,
			openai_chat_complete(
			'gpt-4o-mini',
			jsonb_build_array(
				jsonb_build_object(
					'role', 'user', 'content', 
					report_needs || data_needs ||  reasons  ||user_question || question)
			)) as chat_completion
		from 
			agg_resons cross join report_needs

)
select 
	send_message, chat_completion,
	replace(chat_completion['choices'][0]['message']['content']::text,'\n',chr(10)) as final_report,
	now() as create_time
--into ai_report
from ai_report;

    
END;
$$
LANGUAGE plpgsql;

---- call the build_pizza_report now

insert into ai_report (send_message, chat_completion,final_report,create_time) 
select  
  send_message, chat_completion,final_report,create_time
from 
	build_pizza_report('why customer dont like our pizza'::text,3::int2);
select * from ai_report ;

Again, we can get the answer by using pgai with a large language model. Here is the screen capture:

Screen capture of the summary of customer feedback

Conclusion

Cutting-edge tools

For the five records in the pizza shop case, we can see how historical customer feedback data is used and augmented by AI. If your business stores large volumes of data, consider using AIGC with your data to deliver further value. The pizza shop case is just a virtual example. If you're running an e-commerce business or digital plants, the data is a voice, and the large language model can “hear” it and translate it into business language.

The proven platform

Pgai is a PostgreSQL extension that is also available in the cloud through the Timescale database. Combining a mature platform with cutting-edge tools is a fantastic idea. I have worked as a digital transformer for waste-to-energy plants. In the past, Lean and Six Sigma concepts were used with digital tools like PostgreSQL to help customers reduce costs and improve operational efficiency. Massive sensor data was collected and processed with a reliable database and advanced features. Now, AIGC opens new opportunities to serve customers better.

Exploring the resources and tools mentioned in this blog will help you enhance your CRM operations. Try using pgai to create new opportunities and ways to utilize data.

Resources

The demo case: Input, output, tools

Input: A table with customer feedback on pizza. Some feedback is good, some is bad.

The best pizza I've ever eaten. The sauce was so tangy!
The pizza was disgusting. I think the pepperoni was made from rats.
I ordered a hot-dog and was given a pizza, but I ate it anyway.
I hate pineapple on pizza. It is a disgrace. Somehow, it worked well on this pizza though.
I ate 11 slices and threw up. The pizza was tasty in both directions.

Output: An AI result table with three columns: send_message, chat_completion, final_report. The final report columns are markdown-formatted business analysis reports on why customers don't like the pizza.

The output of the AI result table with three columns

Tools

Vector database: pgai container to store the input and output data. It has to be run before proceeding to the next step.
AI model service: OpenAI 'gpt-4o-mini', 'text-embedding-3-small'
The RAG concept of the process
Any database client supporting Postgres 16 or later.

About the author

I am a digital transformation expert, helping customers improve operational efficiency and reduce costs through digital means. I apply digital Six Sigma and Lean concepts to incineration power plants to improve overall equipment effectiveness and predict maintenance. With AIGC enhancements, a new door has opened. This blog is based on virtual pizza shop data but demonstrates the complete process of generating a customer satisfaction report and helping improve quality.

This blog was first published as Enhancing Customer Satisfaction in Pizza Shops with RAG - A Deep Dive of Integrated Pgai.

Stay updated with new posts and releases.