BigQueryと大規模言語モデル(LLM)の統合で始めるテキストエンべディングを用いたSEO改善の事例

-- 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_TEXT_EMBEDDING_MODEL_V1');

CREATE TEMP FUNCTION
  CreatePrompt(text STRING)
  RETURNS STRING
  LANGUAGE js AS r"""
  return `
以下のtextをintentに分類しなさい。intentのみ出力すること。

intent:
病気について
症状について
診断について
検査について
治療について
薬について
健康診断について
手続きや支援について
受診について
病院について
その他

出力例:
text: "帯状疱疹"
intent: 病気について

text: "ユビー"
intent: その他

text: "お腹が痛い"
intent: 症状について

text: ${text}
intent: 
`.trim();
""";
CREATE OR REPLACE TABLE `<your-project-id>.<your-dataset-id>.searchdata_url_impression_with_intent`
SELECT
  *
FROM
  -- このモデルは `CLOUD_AI_LARGE_LANGUAGE_MODEL_V1` を利用してください。
  ML.GENERATE_TEXT( MODEL `<your-project-id>.<your-dataset-id>.<your-model-id>`,
    (
    SELECT
      CreatePrompt(query) AS prompt,
      *,
    FROM
      `<your-project-id>.<your-dataset-id>.searchdata_site_impression`
    QUALIFY
      data_date = MAX(data_date) OVER() -- 全件だと流石に多すぎるので最新のデータを使う
    ),
    STRUCT( 0.8 AS temperature,
      100 AS max_output_tokens,
      0.95 AS top_p,
      40 AS top_k,
      TRUE AS flatten_json_output ) )

CREATE OR REPLACE TABLE `<your-project-id>.<your-dataset-id>.searchdata_url_impression_with_embedding` AS
SELECT
  *
FROM
  -- このモデルは `CLOUD_AI_TEXT_EMBEDDING_MODEL_V1` を利用してください
  ML.GENERATE_TEXT_EMBEDDING( MODEL `<your-project-id>.<your-dataset-id>.<your-model-id>`,
    (
    SELECT
      *,
      query AS content
    FROM
      `<your-project-id>.<your-dataset-id>.searchdata_url_impression_with_intent`
    WHERE
      ml_generate_text_llm_result LIKE "%症状について%" -- 症状に関連するキーワードのみ
		),
    STRUCT(TRUE AS flatten_json_output))

CREATE OR REPLACE TABLE `<your-project-id>.<your-dataset-id>.content_table_with_embedding` AS
SELECT
  *
FROM
  -- このモデルは `CLOUD_AI_TEXT_EMBEDDING_MODEL_V1` を利用してください
  ML.GENERATE_TEXT_EMBEDDING( MODEL `<your-project-id>.<your-dataset-id>.<your-model-id>`,
    (
    SELECT
      *,
      html AS content
    FROM
      `<your-project-id>.<your-dataset-id>.content_table`),
    STRUCT(TRUE AS flatten_json_output))

WITH
  calc_ml_distance AS (
  SELECT
    a.content AS keyword,
    a.url AS actual_url,
    b.content AS similar_content,
    b.url AS similar_url,
    ML.DISTANCE(a.text_embedding, b.text_embedding, 'COSINE') AS distance,
  FROM
    `<your-project-id>.<your-dataset-id>.searchdata_url_impression_with_embedding` AS a,
    `<your-project-id>.<your-dataset-id>.content_table_with_embedding` AS b
)
SELECT
  *,
  ROW_NUMBER() OVER(PARTITION BY keyword ORDER BY distance) AS rank
FROM
  calc_ml_distance
QUALIFY
  rank = 1