使用代管 AI 函式執行語意分析
本教學課程說明如何使用 BigQuery ML 管理的 AI 函式，對顧客意見回饋執行語意分析。
目標
在本教學課程中，您將執行下列作業：
- 建立資料集，並將情緒資料載入資料表
- 使用下列 AI 函式執行語意分析：
AI.IF： 使用自然語言條件篩選資料AI.SCORE： 依情緒評估輸入內容AI.CLASSIFY： 將輸入內容分類至使用者定義的類別
費用
本教學課程使用 Google Cloud的計費元件，包括：
- BigQuery
- BigQuery ML
如要進一步瞭解 BigQuery 費用，請參閱 BigQuery 定價頁面。
如要進一步瞭解 BigQuery ML 費用，請參閱 BigQuery ML 定價。
事前準備
- 登入 Google Cloud 帳戶。如果您是 Google Cloud新手，歡迎 建立帳戶，親自評估產品在實際工作環境中的成效。新客戶還能獲得價值 $300 美元的免費抵免額，可用於執行、測試及部署工作負載。
-
In the Google Cloud console, on the project selector page, select or create a Google Cloud project.
Roles required to select or create a project
- Select a project: Selecting a project doesn't require a specific IAM role—you can select any project that you've been granted a role on.
-
Create a project: To create a project, you need the Project Creator role
(
roles/resourcemanager.projectCreator), which contains theresourcemanager.projects.createpermission. Learn how to grant roles.
-
If you're using an existing project for this guide, verify that you have the permissions required to complete this guide. If you created a new project, then you already have the required permissions.
-
In the Google Cloud console, on the project selector page, select or create a Google Cloud project.
Roles required to select or create a project
- Select a project: Selecting a project doesn't require a specific IAM role—you can select any project that you've been granted a role on.
-
Create a project: To create a project, you need the Project Creator role
(
roles/resourcemanager.projectCreator), which contains theresourcemanager.projects.createpermission. Learn how to grant roles.
-
If you're using an existing project for this guide, verify that you have the permissions required to complete this guide. If you created a new project, then you already have the required permissions.
-
啟用 BigQuery API。
啟用 API 時所需的角色
如要啟用 API，您需要服務使用情形管理員 IAM 角色 (
roles/serviceusage.serviceUsageAdmin)，其中包含serviceusage.services.enable權限。瞭解如何授予角色。新專案會自動啟用 BigQuery API。
- 選用： 啟用專案的計費功能。如果您不想啟用帳單或提供信用卡，仍可按照本文步驟操作。BigQuery 提供沙箱，方便您執行這些步驟。詳情請參閱「啟用 BigQuery 沙箱」一文。
必要的角色
如要取得使用 AI 函式所需的權限，請要求管理員在專案中授予您下列 IAM 角色：
-
執行查詢工作和載入工作：
BigQuery 工作使用者 (
roles/bigquery.jobUser) -
建立資料集、建立資料表、將資料載入資料表，以及查詢資料表：
BigQuery 資料編輯器 (
roles/bigquery.dataEditor)
如要進一步瞭解如何授予角色，請參閱「管理專案、資料夾和組織的存取權」。
建立樣本資料
如要為本教學課程建立名為 my_dataset 的資料集，請執行下列查詢。
CREATE SCHEMA my_dataset OPTIONS (location = 'LOCATION');
接著，建立名為 customer_feedback 的資料表，其中包含裝置的顧客評論範例：
CREATE TABLE my_dataset.customer_feedback AS (
SELECT
*
FROM
UNNEST( [STRUCT<review_id INT64, review_text STRING>
(1, "The battery life is incredible, and the screen is gorgeous! Best phone I've ever had. Totally worth the price."),
(2, "Customer support was a nightmare. It took three weeks for my order to arrive, and when it did, the box was damaged. Very frustrating!"),
(3, "The product does exactly what it says on the box. No complaints, but not exciting either."),
(4, "I'm so happy with this purchase! It arrived early and exceeded all my expectations. The quality is top-notch, although the setup was a bit tricky."),
(5, "The price is a bit too high for what you get. The material feels cheap and I'm worried it won't last. Service was okay."),
(6, "Absolutely furious! The item arrived broken, and getting a refund is proving impossible. I will never buy from them again."),
(7, "This new feature for account access is confusing. I can't find where to update my profile. Please fix this bug!"),
(8, "The shipping was delayed, but the support team was very helpful and kept me informed. The product itself is great, especially for the price.")
])
);
分類整體觀感
擷取文字中表達的整體情緒感受，有助於支援下列用途：
- 根據評論評估顧客滿意度。
- 監控社群媒體上的品牌觀感。
- 根據使用者的不滿程度，決定支援單的優先順序。
下列查詢說明如何使用 AI.CLASSIFY 函式，將 customer_feedback 表格中的評論分類為正面、負面或中性：
SELECT
review_id,
review_text,
AI.CLASSIFY(
review_text,
categories => ['positive', 'negative', 'neutral']) AS sentiment
FROM
my_dataset.customer_feedback;
傳回的結果看起來類似下列內容：
+-----------+------------------------------------------+-----------+ | review_id | review_text | sentiment | +-----------+------------------------------------------+-----------+ | 7 | This new feature for account access is | negative | | | confusing. I can't find where to update | | | | my profile. Please fix this bug! | | +-----------+------------------------------------------+-----------+ | 4 | "I'm so happy with this purchase! It | positive | | | arrived early and exceeded all my | | | | expectations. The quality is top-notch, | | | | although the setup was a bit tricky." | | +-----------+------------------------------------------+-----------+ | 2 | "Customer support was a nightmare. It | negative | | | took three weeks for my order to | | | | arrive, and when it did, the box was | | | | damaged. Very frustrating!" | | +-----------+------------------------------------------+-----------+ | 1 | "The battery life is incredible, and | positive | | | the screen is gorgeous! Best phone I've | | | | ever had. Totally worth the price." | | +-----------+------------------------------------------+-----------+ | 8 | "The shipping was delayed, but the | positive | | | support team was very helpful and kept | | | | me informed. The product itself is | | | | great, especially for the price." | | +-----------+------------------------------------------+-----------+ | 5 | The price is a bit too high for what | negative | | | you get. The material feels cheap and | | | | I'm worried it won't last. Service was | | | | okay. | | +-----------+------------------------------------------+-----------+ | 3 | "The product does exactly what it says | neutral | | | on the box. No complaints, but not | | | | exciting either." | | +-----------+------------------------------------------+-----------+ | 6 | "Absolutely furious! The item arrived | negative | | | broken, and getting a refund is proving | | | | impossible. I will never buy from them | | | | again." | | +-----------+------------------------------------------+-----------+
分析以面向為基礎的情緒
如果正面或負面等整體情緒不足以滿足您的用途，您可以分析文字意義的特定面向。舉例來說，您可能想瞭解使用者對產品品質的態度，但不在意他們對價格的想法。您甚至可以要求自訂值，指出特定層面不適用。
以下範例說明如何使用 AI.SCORE 函式，根據 customer_feedback 資料表中每則評論對價格、客戶服務和品質的評價，將使用者情緒評等從 1 分到 10 分。如果評論中未提及任何方面，函式會傳回自訂值 -1，方便您稍後篩除這些評論。
SELECT
review_id,
review_text,
AI.SCORE(
("Score 0.0 to 10 on positive sentiment about PRICE for review: ", review_text,
"If price is not mentioned, return -1.0")) AS price_score,
AI.SCORE(
("Score 0.0 to 10 on positive sentiment about CUSTOMER SERVICE for review: ", review_text,
"If customer service is not mentioned, return -1.0")) AS service_score,
AI.SCORE(
("Score 0.0 to 10 on positive sentiment about QUALITY for review: ", review_text,
"If quality is not mentioned, return -1.0")) AS quality_score
FROM
my_dataset.customer_feedback
LIMIT 3;
傳回的結果看起來類似下列內容：
+-----------+------------------------------------------+--------------+---------------+---------------+ | review_id | review_text | price_score | service_score | quality_score | +-----------+------------------------------------------+--------------+---------------+---------------+ | 4 | "I'm so happy with this purchase! It | -1.0 | -1.0 | 9.5 | | | arrived early and exceeded all my | | | | | | expectations. The quality is top-notch, | | | | | | although the setup was a bit tricky." | | | | +-----------+------------------------------------------+--------------+---------------+---------------+ | 8 | "The shipping was delayed, but the | 9.0 | 8.5 | 9.0 | | | support team was very helpful and kept | | | | | | me informed. The product itself is | | | | | | great, especially for the price." | | | | +-----------+------------------------------------------+--------------+---------------+---------------+ | 6 | "Absolutely furious! The item arrived | -1.0 | 1.0 | 0.0 | | | broken, and getting a refund is proving | | | | | | impossible. I will never buy from them | | | | | | again." | | | | +-----------+------------------------------------------+--------------+---------------+---------------+
偵測情緒
除了正面或負面情緒，您也可以根據選取的特定情緒分類文字。這項功能有助於深入瞭解使用者回覆，或標記情緒激動的意見回饋以供審查。
SELECT
review_id,
review_text,
AI.CLASSIFY(
review_text,
categories => ['joy', 'anger', 'sadness', 'surprise', 'fear', 'disgust', 'neutral', 'other']
) AS emotion
FROM
my_dataset.customer_feedback;
傳回的結果看起來類似下列內容：
+-----------+------------------------------------------+---------+ | review_id | review_text | emotion | +-----------+------------------------------------------+---------+ | 2 | "Customer support was a nightmare. It | anger | | | took three weeks for my order to | | | | arrive, and when it did, the box was | | | | damaged. Very frustrating!" | | +-----------+------------------------------------------+---------+ | 7 | This new feature for account access is | anger | | | confusing. I can't find where to update | | | | my profile. Please fix this bug! | | +-----------+------------------------------------------+---------+ | 4 | "I'm so happy with this purchase! It | joy | | | arrived early and exceeded all my | | | | expectations. The quality is top-notch, | | | | although the setup was a bit tricky." | | +-----------+------------------------------------------+---------+ | 1 | "The battery life is incredible, and | joy | | | the screen is gorgeous! Best phone I've | | | | ever had. Totally worth the price." | | +-----------+------------------------------------------+---------+ | 8 | "The shipping was delayed, but the | joy | | | support team was very helpful and kept | | | | me informed. The product itself is | | | | great, especially for the price." | | +-----------+------------------------------------------+---------+ | 5 | The price is a bit too high for what | sadness | | | you get. The material feels cheap and | | | | I'm worried it won't last. Service was | | | | okay. | | +-----------+------------------------------------------+---------+ | 3 | "The product does exactly what it says | neutral | | | on the box. No complaints, but not | | | | exciting either." | | +-----------+------------------------------------------+---------+ | 6 | "Absolutely furious! The item arrived | anger | | | broken, and getting a refund is proving | | | | impossible. I will never buy from them | | | | again." | | +-----------+------------------------------------------+---------+
依主題分類評論
您可以使用 AI.CLASSIFY 函式，將評論歸入預先定義的主題。
例如，您可以執行以下操作：
- 找出顧客意見回饋中常見的主題。
- 依主題整理文件。
- 依主題轉送支援單。
以下範例說明如何將顧客意見回饋分類為「帳單問題」或「帳戶存取權」等各種類型，然後計算每個類別的評論數量：
SELECT
AI.CLASSIFY(
review_text,
categories => ['Billing Issue', 'Account Access',
'Product Bug', 'Feature Request',
'Shipping Delay', 'Other']) AS topic,
COUNT(*) AS number_of_reviews,
FROM
my_dataset.customer_feedback
GROUP BY topic
ORDER BY number_of_reviews DESC;
傳回的結果看起來類似下列內容：
+----------------+-------------------+ | topic | number_of_reviews | +----------------+-------------------+ | Other | 5 | | Shipping Delay | 2 | | Product Bug | 1 | +----------------+-------------------+
找出語意相似的評論
您可以要求 AI.SCORE 函式評估兩段文字的語意相似程度，藉此判斷兩者是否相似。這項功能可協助您完成下列工作：
- 找出重複或幾乎重複的項目。
- 將類似的意見回饋歸類為一組。
- 支援語意搜尋應用程式。
下列查詢會找出討論產品設定難度的評論：
SELECT
review_id,
review_text,
AI.SCORE(
(
"""How similar is the review to the concept of 'difficulty in setting up the product'?
A higher score indicates more similarity. Review: """,
review_text)) AS setup_difficulty
FROM my_dataset.customer_feedback
ORDER BY setup_difficulty DESC
LIMIT 2;
傳回的結果看起來類似下列內容：
+-----------+------------------------------------------+------------------+ | review_id | review_text | setup_difficulty | +-----------+------------------------------------------+------------------+ | 4 | "I'm so happy with this purchase! It | 3 | | | arrived early and exceeded all my | | | | expectations. The quality is top-notch, | | | | although the setup was a bit tricky." | | +-----------+------------------------------------------+------------------+ | 7 | This new feature for account access is | 1 | | | confusing. I can't find where to update | | | | my profile. Please fix this bug! | | +-----------+------------------------------------------+------------------+
您也可以使用 AI.IF 函式，找出與文字相關的評論：
SELECT
review_id,
review_text
FROM my_dataset.customer_feedback
WHERE
AI.IF(
(
"Does this review discuss difficulty setting up the product? Review: ",
review_text));
合併函式
建議您在單一查詢中合併使用這些函式。舉例來說，下列查詢會先篩選出負面情緒的評論，然後依挫折感類型分類：
SELECT
review_id,
review_text,
AI.CLASSIFY(
review_text,
categories => [
'Poor Quality', 'Bad Customer Service', 'High Price', 'Other Negative']) AS negative_topic
FROM my_dataset.customer_feedback
WHERE
AI.IF(
("Does this review express a negative sentiment? Review: ", review_text));
建立可重複使用的提示 UDF
如要確保查詢內容清晰易讀，您可以建立使用者定義函式，重複使用提示邏輯。下列查詢會建立函式，透過自訂提示呼叫 AI.IF，偵測負面情緒。然後呼叫該函式，依負面評論進行篩選。
CREATE OR REPLACE FUNCTION my_dataset.is_negative_sentiment(review_text STRING)
RETURNS BOOL
AS (
AI.IF(
("Does this review express a negative sentiment? Review: ", review_text))
);
SELECT
review_id,
review_text
FROM my_dataset.customer_feedback
WHERE my_dataset.is_negative_sentiment(review_text);
清除所用資源
如要避免產生費用，您可以刪除含有您所建立資源的專案，或保留專案並刪除個別資源。
刪除專案
如要刪除專案，請進行以下操作：
- 前往 Google Cloud 控制台的「Manage resources」(管理資源) 頁面。
- 在專案清單中選取要刪除的專案，然後點選「Delete」(刪除)。
- 在對話方塊中輸入專案 ID，然後按一下 [Shut down] (關閉) 以刪除專案。
刪除資料集
如要刪除資料集和其中包含的所有資源 (包括所有資料表和函式)，請執行下列查詢：
DROP SCHEMA my_dataset CASCADE;