A Powerful Ally in Data Marketing!? A Case Study of Intent Analysis Using BigQuery and LLM by Shingen Taguchi
The following article is a translation of a Japanese article by our colleague Shingen.
Hi, I’m Shingen Taguchi, an engineer of the Growth Team at Ubie. We provide a service called “Ubie AI Symptom Checker,” a symptom search engine that supports users from symptom search to medical consultation. We aim to create a world where “health becomes as natural as the air.”
Now, understanding user behavior is essential for growing our service. By knowing what users are looking for and how they use the service, we can identify areas for improvement to enhance user satisfaction.
However, a significant challenge for large websites is dealing with the vast number of search queries that need analysis.
I will discuss our case of using the ML.GENERATE_TEXT to create a prompt-based data pipeline and conduct a user search intent analysis.
📝 Table of Contents
- What is ML.GENERATE_TEXT?
- Preparing the Data for Classification
- Create a MODEL with SQL
- Classify a Search Query in SQL
- Further Classifying Search Intent for Know Queries
- Persistence and Scheduling of Classification
- Summary and Outlook
- Note
- References
1. What is ML.GENERATE_TEXT?
ML.GENERATE_TEXT is a feature that allows you to invoke VertexAI’s natural language foundation model from BigQuery. This enables natural language generation using SQL alone.
For more details, please refer to the following documentation.
The ML.GENERATE_TEXT function | BigQuery | Google Cloud
By adjusting the prompt, you can perform the following functions using SQL alone, which can be a handy feature:
- Classification
- Sentiment analysis
- Entity extraction
- Extractive question-answering
- Summarization
- Rewriting text in different styles
- Ad copy generation
- Concept ideation
2. Preparing the Data for Classification
I started by classifying search queries into four categories: Go, Do, Buy, and Know. This is a standard method used for search intent analysis.
📝 Four Search Queries
- Do Query …The query associated with an action that involves an intention to do something.
- Know Query …A query intending to acquire information or solve a problem.
- Go Query …A query with the intention to “visit a specific website” (not referring to physically going somewhere).
- Buy Query …The “Buy” query is a subset of “Do” queries with the specific intent of wanting to purchase or acquire something.
Living in the Micro-Moment
Google is certain that they have figured out consumer intentions
medium.com
To classify user search intent, search queries are essential. To achieve this, we utilized data from Google Search Console.
Google Search Console and BigQuery offer data transfer capabilities, making data integration into your BigQuery project straightforward.
You can access the bulk data export settings by selecting the “Bulk Data Export” option from the settings menu. Please note that you can create only one data transfer configuration.
Google Search Console Bulk Data Export Configuration Screen.
Tables that have been created.
Please refer to the following documents for detailed instructions on the tasks and Google Cloud Platform settings.
Start a new bulk data export — Search Console Help
3. Create a MODEL in SQL
You can create a remote model in VertexAI. You can also create a model using SQL. Specify ‘CLOUD_AI_LARGE_LANGUAGE_MODEL_V1’ for the remote_service_type.
-- SQL
CREATE OR REPLACE MODEL `<your-project-id>.<your-dataset-id>.<your-model-id>`
REMOTE WITH CONNECTION `<your-project-id>.<your-location-id>.<your-connection-id>`
OPTIONS (remote_service_type = 'CLOUD_AI_LARGE_LANGUAGE_MODEL_V1');For detailed remote model configuration, please refer to the following document.
The CREATE MODEL statement for remote models | BigQuery | Google Cloud
4. Classify a Search Query in SQL
The first step is to prepare the prompt. In this case, I’ve implemented a function using a JavaScript UDF that generates a prompt corresponding to a given search query, allowing for flexible expressions.
-- SQL
CREATE TEMP FUNCTION
CreatePrompt(text STRING)
RETURNS STRING
LANGUAGE js AS r"""
return `
Classify the following text as intent, outputting only the intent.intent:
Do: Intend to take some action
Know: Intend to know information
Go: Intend to go to the desired website
Buy: Intend to buy a specific thing, product, etc.
Other: Something that is difficult to categorizesamples of output:
text: "Download Google Chrome"
intent: Dotext: "Ubie"
intent: Gotext: "BigQuery Case Study"
intent: Knowtext: "Google Cloud Platform Pricing"
intent: Buytext: ${text}
intent:
`.trim();
""";
The usage of ML.GENERATE_TEXT is as follows:
First, you can pass the flatten_json_output option to receive the generated text in the response instead of JSON.
The second point is that in addition to the prompt, you are using an asterisk (*) as a specifier. As a result, the response will include both the original data and the text generated by the LLM. This is useful for later analysis as it retains the original data for reference.
-- SQL
SELECT *
FROM
ML.GENERATE_TEXT( MODEL `<your-project-id>.<your-dataset-id>.<your-model-id>`,
(
SELECT
CreatePrompt(query) AS prompt,
*,
-- Include all columns from the original searchdata_site_impression in the SELECT result
FROM
`<your-project-id>.<your-dataset-id>.searchdata_site_impression`
),
STRUCT( 0.8 AS temperature,
100 AS max_output_tokens,
0.95 AS top_p,
40 AS top_k,
TRUE AS flatten_json_output ) )When dealing with a large amount of data, it can take a considerable time. It is recommended to start by narrowing down the number of records and adjusting prompts and parameters.
The final SQL query is as follows.
-- SQL
CREATE TEMP FUNCTION
CreatePrompt(text STRING)
RETURNS STRING
LANGUAGE js AS r"""
return `
Classify the following text as intent, outputting only the intent.intent:
Do: Intend to take some action
Know: Intend to know information
Go: Intend to go to the desired website
Buy: Intend to buy a specific thing, product, etc.
Other: Something that is difficult to categorizesamples of output:
text: "Download Google Chrome"
intent: Dotext: "Ubie"
intent: Gotext: "BigQuery Case Study"
intent: Knowtext: "Google Cloud Platform Pricing"
intent: Buytext: ${text}
intent:
`.trim();
""";SELECT *
FROM
ML.GENERATE_TEXT( MODEL `<your-project-id>.<your-dataset-id>.<your-model-id>`,
(
SELECT
CreatePrompt(query) AS prompt,
*,
-- Include all columns from the original searchdata_site_impression in the SELECT result
FROM
`<your-project-id>.<your-dataset-id>.searchdata_site_impression`
),
STRUCT( 0.8 AS temperature,
100 AS max_output_tokens,
0.95 AS top_p,
40 AS top_k,
TRUE AS flatten_json_output ) )
After processing, we will use BigQuery’s graph features to examine the classification results. If any issues are found, we will improve the prompts. The generated text is stored in the ‘ml_generate_text_llm_result’ column.
Upon running several samples, it was observed that there are many “Know” queries in the results for the symptom search engine, “Ubie AI Symptom Checker.” This seems to produce valid results as it’s a medical information service.
The results after sampling and classifying search queries.
5. Further Classifying Search Intent for Know Queries
I had initially anticipated the high number of “Know” queries, so I further refined the analysis to understand what information users are seeking.
The information provided by the symptom search engine “Ubie AI Symptom Checker” is categorized, allowing for the classification of search queries into different categories.
It’s impressive how prompt adjustments alone can make the system adaptable to various use cases.
- SQL
CREATE TEMP FUNCTION CreatePrompt(text STRING) RETURNS STRING LANGUAGE js AS r""" return `
Classify the following text as intent, outputting only the intent.intent:
Diseases
Symptoms
Diagnosis
Lab Test
Treatment
Drug
Health checkup
Govermental Support
Medical Consultation
Hospital
Other出力例:
text: "Shingles"
intent: Diseasetext: "Ubie"
intent: Othertext: "Stomachache"
intent: Symptomstext: ${text}
intent:
`.trim();
""";
From the results, it became apparent that some cases don’t fit into the categories we’ve set. These originate from unexpected search queries and have aided in understanding potential user needs. It’s likely that manually identifying specific questions of interest from tens of thousands of search queries would have had its limitations.
The results of further categorizing search queries into categories.
Additionally, analyzing the correlation between search query impressions, CTR, and search intent may provide new insights.
6. Persistence of Classification and Scheduling
In some cases, you may want to perform further analysis based on the classified results. However, running ML.GENERATE_TEXT every time can be time-consuming and costly. It’s convenient to save the classification results in a table and reference it for analytical purposes.
Moreover, data from Google Search Console is automatically synchronized daily, but manually processing it every day can be pretty cumbersome. Depending on the prompt size and the number of records, it can be a time-consuming task. For instance, classifying 1000 sampled search queries took approximately 16 minutes.
To address this, I’ve scheduled the processing to run automatically at night, and the results are saved as a BigQuery table.
You can easily configure the scheduling settings by pressing the schedule button in the BigQuery console.
For detailed SQL scheduling settings, please consult the documentation below:
Scheduling queries | BigQuery | Google Cloud
7. Summary and Outlook
By integrating BigQuery and LLM, we’ve showcased a case where intent analysis was conducted using SQL alone.
All the operations up to this point have primarily been carried out within BigQuery, without the need for traditional tasks like creating machine learning models or preparing batch applications. The flexibility to tackle a wide range of tasks through prompt tuning has been a significant advantage, eliminating the need for these additional steps.
The architecture of the data pipeline for this project is as follows:
In addition to intent analysis, it seems that with prompt tuning, the following tasks can be accomplished:
- Summarizing the content of inquiry forms.
- Conducting sentiment analysis on product reviews.
Given the data in BigQuery, the ability to execute these tasks using SQL alone is quite convenient, and I look forward to trying them out.
Currently, ML.GENERATE_TEXT is in the preview phase, but there are high hopes for its future developments.
8. Note
The characteristics of large language models (LLMs) include the potential to return unexpected results. Additionally, there may be restrictions on harmful categories.
For more details, please refer to the following document on Responsible AI.
Responsible AI | Vertex AI | Google Cloud
9. References
LLM with Vertex AI only using SQL queries in BigQuery | Google Cloud Blog
KNOW, GO, DO, BUY: A B2B Copywriter on Google’s Micro-moments
About “Ubie AI Symptom Checker”
“Ubie AI Symptom Checker” supports your health and safety by bridging the gap between you and the right medical care at the right time and eases your anxiety, worries, and pain about medical care.
Just by answering an easy 3-min questionnaire, you can get an AI-powered personalized report for free. With Ubie, you can get information regarding diseases that are highly related to your symptoms.
The algorithm of Ubie is based on 50K+ medical papers, supervised by 50+ doctors in various specialties, and is used in 1,500+ medical institutions. Currently, 7M+ users all over the world use this service every month.