🏎️

生成AIで商談メモから「次の一手」を出す

dach

2025/08/21に公開

 概要皆さんのデータ基盤には、どんなデータが入ってますか？

ヘイシャアのデータ基盤には、自社サービスデータだけでなく Salesforce のようなビジネスメンバーが利用する SaaS データも保持しています。が、非構造データについてはまだまだ利活用が進んでいないのが実情です。そこで、先日社内で実施した「AI ハッカソン」の機会を利用して、前から可能性を感じていた「ビジネスメンバーのメモ書きを AI が分析して推論してくれたら、振り返りとかに使えるいい感じのアイテムになるのでは？」というアイデアを試しに実装してみることにしました。結果、それっぽいものが動くところまで実装できました。
※注1: 精度等の品質の確認や実運用レベルに使える程度のものまでは時間が足りず出来なかったので、あくまで PoC レベルです(2025/08/28追記)

※注2: 生成AIを活用する際には利用規約や情報管理ポリシーに従い、機密情報が外部に流出しないよう配慮しています(2025/08/28追記)

※注3: 以下の成果物イメージ画像は ChatGPT が作ってくれた偽物です

本記事では、商談時の会話メモ（Salesforce に記録してあるデータ）を素材に、「要因分析」と「次アクション提示」を自然言語で分析が出来るシステムを「BigQuery × Gemini × Looker」で実現した際の大変だったポイント、特に 「BigQuery × Gemini」部分で行った特徴量算出でのポイントについて紹介できればと思います。

 アーキテクチャ「会話メモから、よく出る論点・懸念・成功要因・次アクションを取りたい」ということを実現するために、「BigQuery × Gemini × Looker を組み合わせたらぱっとを出来るんじゃね？」ということを思いついたワイ。

最初は「Gemini がいい感じにやってくれるから ML.GENERATE_TEXT に雑にプロンプト入れればええやろ」と思ってました。が、実際に試してみた結果、直接 LLM に最頻語等の抽出プロンプトを投げる方式は、日本語の分かち書き問題／出力ゆらぎ／JSON崩れで安定しなかったため、前処理としてルール系特徴量を SQL で算出するようにしました。その結果、以下の構成・処理でなんとかそれっぽいデータが出る構成となりました。
[構成図]

[Looker が参照するデータの IPO]

入力：
商談時の会話メモ（Salesforceに記録されたデータ）


処理：
ルール系特徴量（質問記号数、肯定/否定語、Q/A比近似）を SQL で算出
“数えられるもの”は SQL（決定論） で確定させる
“意味づけ”は 軽量スキーマのJSON を LLM で抽出
すべてを 1テーブル にまとめ、Looker から迷わず使える形にする

LLM（BigQuery ML / Gemini）で「論点・懸念・成功要因・次アクション」を軽量JSONとして抽出

flatten_json_output = TRUE で生成テキストを素の文字列として受領

SAFE.PARSE_JSON + 先頭 {…} 抽出で堅牢パース

モニタ列（is_valid_json, has_min_keys, finish_reason）で品質を可視化


出力：
1商談データに対し、特徴量を算出したデータ（1行=1会話メモ）
↑の分析結果から算出した、次アクションの1–3文の要約データ


 特徴量算出 Query-- ================================
-- 会話メモ → 特徴量テーブル
-- ================================
CREATE OR REPLACE TABLE `sample_analytics.sales_convo_features`
-- 今回はデータ量少なめで検証しているのでコメントアウト
-- PARTITION BY DATE(activity_date)
-- CLUSTER BY stage_signal, agency
AS
WITH base AS (
  SELECT
    id,
    activity_date,
    note,
    -- ★ LLMに渡す本文は“長すぎると出力が空になりがち”なので上限カット
    SUBSTR(COALESCE(note, ''), 1, 4000) AS desc_for_llm,
    -- ルール系指標用の軽い正規化（URL/メール削除・改行タブ除去・小文字化）
    LOWER(
      REGEXP_REPLACE(
        REGEXP_REPLACE(
          REGEXP_REPLACE(COALESCE(note, ''), r'https?://\S+', ' '),
          r'[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}', ' '
        ),
        r'[\t\r]', ' '
      )
    ) AS desc_norm
  FROM `your_project.crm.sfdc_activities`   -- ← SFDC 行動オブジェクト（サンプル）
  WHERE activity_date >= DATE_SUB(CURRENT_DATE('Asia/Tokyo'), INTERVAL 10 DAY)
),

-- 1) ルールベース（完全決定論）の“数える系”
rule_feats AS (
  SELECT
    id,
    activity_date,
    note,
    desc_norm,
    -- 質問記号の出現回数（? / ？）
    ARRAY_LENGTH(REGEXP_EXTRACT_ALL(desc_norm, r'\?')) +
    ARRAY_LENGTH(REGEXP_EXTRACT_ALL(desc_norm, r'？')) AS question_marks,
    -- 肯定／否定語の簡易辞書（現場語彙に合わせて育てる前提）
    ARRAY_LENGTH(REGEXP_EXTRACT_ALL(desc_norm, r'\b(はい|了解|可能|できます|良い|ok|承知)\b')) AS affirm_cnt,
    ARRAY_LENGTH(REGEXP_EXTRACT_ALL(desc_norm, r'\b(難しい|不可|できない|否|無理|懸念|課題)\b')) AS neg_cnt,
    -- ざっくり Q/A 比：質問記号 ÷ 文区切り（近似）
    SAFE_DIVIDE(
      ARRAY_LENGTH(REGEXP_EXTRACT_ALL(desc_norm, r'[？\?]')),
      GREATEST(1, ARRAY_LENGTH(REGEXP_EXTRACT_ALL(desc_norm, r'[。．\.\!\?]')))
    ) AS qa_ratio_approx
  FROM base
),

-- 2) LLM抽出：軽量JSON“ひとつだけ”返させる
--    flatten_json_output=TRUE で「生成テキスト」を素の文字列として受ける
llm_raw AS (
  SELECT
    id,
    t.ml_generate_text_llm_result AS raw_text,    -- ← 生成テキスト（JSON文字列想定）
    t.ml_generate_text_status     AS status       -- ← ステータス（空=成功、値あり=失敗）
  FROM ML.GENERATE_TEXT(
    MODEL `your_project.ml.gemini_model`,         -- ← Gemini モデル（サンプル名義）
    (
      SELECT
        CONCAT(
          '次の会話メモから、以下スキーマに厳密一致する JSON を1個だけ返してください。',
          ' 不明値は null、配列は最大5件まで。',
          ' スキーマ:{',
          ' "agency":"customer|us|balanced|unknown",',
          ' "stage_signal":"情報収集|比較検討|稟議準備|決裁直前|保留|失注リスク|不明",',
          ' "topics":[string], "bottlenecks":[string], "positive_factors":[string],',
          ' "actions":{"customer":[string], "us":[string]}',
          ' }',
          '\n会話メモ:\n', desc_for_llm
        ) AS prompt,
        id
      FROM base
    ),
    STRUCT(
      1024 AS max_output_tokens,      -- JSONだけならこの程度でOK（必要に応じ増減）
      0.0  AS temperature,            -- 決定性重視
      TRUE AS flatten_json_output,    -- ★ 生成テキストを素の文字列として受ける
      ['```'] AS stop_sequences       -- コードフェンス等を止める保険
    )
  ) AS t
),

-- 3) JSON抽出＆堅牢パース（壊れても落とさない）
llm_json AS (
  SELECT
    id,
    -- 万一 JSON前後に余白や注釈が混じっても、最初の {…} を抜く
    COALESCE(REGEXP_EXTRACT(raw_text, r'(?s)\{.*\}'), raw_text) AS json_str,
    SAFE.PARSE_JSON(COALESCE(REGEXP_EXTRACT(raw_text, r'(?s)\{.*\}'), raw_text)) AS js,
    status
  FROM llm_raw
),

-- 4) バリデーション（最低限のキーがあるか）
validated AS (
  SELECT
    id,
    js,
    status,
    -- topics または actions が取れていれば最小要件クリア、と定義
    (JSON_QUERY(js, '$.actions') IS NOT NULL OR JSON_QUERY(js, '$.topics') IS NOT NULL) AS has_min_keys,
    js IS NOT NULL AS is_valid_json
  FROM llm_json
),

-- 5) 列化（NULL安全）
parsed AS (
  SELECT
    id,
    JSON_VALUE(js, '$.agency')       AS agency,
    JSON_VALUE(js, '$.stage_signal') AS stage_signal,
    ARRAY(SELECT JSON_VALUE(x) FROM UNNEST(JSON_EXTRACT_ARRAY(js, '$.topics'))           x) AS topics,
    ARRAY(SELECT JSON_VALUE(x) FROM UNNEST(JSON_EXTRACT_ARRAY(js, '$.bottlenecks'))      x) AS bottlenecks,
    ARRAY(SELECT JSON_VALUE(x) FROM UNNEST(JSON_EXTRACT_ARRAY(js, '$.positive_factors')) x) AS positive_factors,
    ARRAY(SELECT JSON_VALUE(x) FROM UNNEST(JSON_EXTRACT_ARRAY(js, '$.actions.customer')) x) AS actions_customer,
    ARRAY(SELECT JSON_VALUE(x) FROM UNNEST(JSON_EXTRACT_ARRAY(js, '$.actions.us'))       x) AS actions_us
  FROM validated
)

-- 6) 最終出力：1行=1会話メモ（SQLの“数える系”＋LLMの“意味づけ”）
SELECT
  f.id,
  f.activity_date,
  f.note,                -- 原文（権限に応じて非表示運用推奨）
  -- ルール系
  f.question_marks, 
  f.affirm_cnt, 
  f.neg_cnt, 
  f.qa_ratio_approx,
  -- LLM系
  p.agency, 
  p.stage_signal, 
  p.topics, 
  p.bottlenecks, 
  p.positive_factors, 
  p.actions_customer, 
  p.actions_us,
  -- 監視用
  v.is_valid_json, 
  v.has_min_keys, 
  v.status AS llm_status
FROM rule_feats f
LEFT JOIN validated v USING(id)
LEFT JOIN parsed    p USING(id);

 実装で大変だったポイントとりあえずで進めていくと、多分以下のポイントでハマると思います。上記Queryから、ワイが工夫したポイントを解説します。
Gemini の返却結果がない
処理が返ってこない

 Gemini の返却結果がないどっちもよく起きたのですが、まずはこの問題について。

覚えてる限りですが、本当にたくさんの理由で起こります。兎に角起こります。

Gemini Connector ほんとに繋がってるんかワレ、と思うくらいに返ってきません。が、悪いのは僕です。ごめんなさい。

反省の色を表すため、表でまとめてみました。


原因
対策
解説


入力メモが長すぎて MAX_TOKENS 到達

SUBSTR(description, 1, 2000–4000) で事前カット／スキーマを最小化／max_output_tokens を 1024 付近に
思考トークンを食い切って出力側の枠が残らず空返しになりがち。入力を短く＆出力JSONは軽量に。

返却経路の取り違え

flatten_json_output = TRUE を使い ml_generate_text_llm_result を読む（未対応環境なら candidates[0].output_text 等にフォールバック）
BQMLは環境で返却構造が揺れる。flattenで「生成文字列ひとつ」に統一するのが一番安定。

JSONが壊れている／前後に余白や注釈が混入

REGEXP_EXTRACT(raw_text, '{…}') で最初のJSONだけ抜く → SAFE.PARSE_JSON

「JSONだけ返して」と言っても一言まじることがある。壊れても落ちない実装に。

出力にコードフェンスや説明文が混ざる
プロンプトで「JSON 1個のみ・配列は最大5件・他の文不要」を明記＋stop_sequences=['```']＋temperature=0

“余計な親切”を封じる。作らせない／選ばせるがコツ。

Safety ブロック（NG語・個人情報など）
入力を事前にマスキング（URL・メール・電話）／safety_settings を緩める（使える環境のみ）
センシティブ語で候補ゼロになることがある。前処理で無毒化してから入れる。

スキーマが重い・プロパティ多すぎ

最小スキーマ（agency/stage/topics/bottlenecks/positive_factors/actions）にし、段階追加
欲張ると出力が長くなり壊れやすい。まずは動く最小から。

言語指定ぶれ（英語で返る）
「日本語で」「値も日本語ラベル（例：情報収集 等）」を明記
ラベルが英語化すると後段の集計・表示が崩れる。固定化する。


candidate_count を増やしている

candidate_count=1 に固定
複数候補はムダに揺らぐ＆コスト増。1本に絞る。

出力トークンが足りない

max_output_tokens を必要分だけ増やす（JSONなら 512–1024 目安）
入力を削っても、出力枠が小さすぎると途中で切れてJSON壊れる。

取り出しパスの JSON_PATH ミス

ml_generate_text_llm_result を第一選択。フォールバックに $.candidates[0].output_text

返却のキー構造が違うと常に NULL(参照)。経路は優先順位つけて多段COALESCEが安全。

補足
「入力カット」→ SUBSTR(description, 1, 4000)
「返却経路統一」→ flatten_json_output=TRUE ＆ ml_generate_text_llm_result
「壊れJSON対応」→ REGEXP_EXTRACT(raw_text, r'(?s)\{.*\}') → SAFE.PARSE_JSON
「最小スキーマ」→ agency/stage_signal/topics/bottlenecks/positive_factors/actions_* のみ

 処理が返ってこない今回対象としたレコードは、PoC としてやったので「3000レコード」程度を扱ってました。

はい、返ってこなかったです。ので、「50レコード」で妥協してやることにしました。はい、それでもめっちゃ時間かかりました。
もーーーーー！！！！！重いよーーーーーー！！！！！やだよーーーーーーー！！！！！！！！！
と叫びたくなります。実装が悪いのです。設計が悪いのです。諦めます。

と、お布団に帰りそうになりましたが、ハッカソンだったので頑張りました。

嘘です、普段も頑張ります。

とりあえずできるところからということで、原因とやった対策をまとめます。


原因
対策
解説


1クエリで大量行×ML.GENERATE_TEXT を一気に回している

バッチ分割（例: 50–200行ずつ）／日付で絞って段階投入／チェックポイント列で増分処理

LLM呼び出しは行ごとに重い。小分けにして失敗リトライしやすくするのが正解。

入力メモが長く、各行のレイテンシが高い
事前に上限カット／長文は要約→本抽出の2パス
「要約（200–400字）」を挟むだけで待ち時間と失敗率が激減。

出力トークンが過大

max_output_tokens を必要最小へ（要約は ~192、JSONは ~512–1024）
出力枠が大きいほど遅く高コスト。用途に合わせて絞る。

正規表現が重い／バックトラック地獄
前処理を単純置換に寄せる（URL/メール除去など）／lookaround乱用を避ける

ルール系は軽いREGEXPで。重い式は別CTEに切り出して再利用。

UNNEST を本処理で多用（行爆発）
特徴量テーブルでは配列のまま保持、分析時はビュー側でUNNEST

計算段階でUNNESTするとデータ量が爆発。表示用のUNNESTに分離。

データとモデルのリージョン不一致

同一リージョンにそろえる
跨ると待ち時間が増える。可能ならストレージ・モデル・実行を同リージョンへ。

同時ジョブが詰まっている（スロット/並列制御）
夜間など空いてる時間にスケジューリング／並行本数を制限

他処理とバッティングすると待たされる。時間帯と並列数の設計が効く。

毎回ゼロから再計算している

中間テーブルを永続化（llm_raw, llm_json）／失敗行だけ再実行

成果物を段階保存しておくと、途中失敗でも差分再開できる。

取得列が多くI/O過多
使う列だけに絞ってSELECT／早めに不要列を落とす

I/Oもレイテンシの敵。最小列×早い段で削減が効率的。

監視・再実行の導線が無い

status（空=成功）・is_valid_json・has_min_keys を常設し、ダッシュボードで監視 → 失敗行のみ再投下

見える化がないと丸ごとやり直しになりがち。失敗だけ拾って回す運用に。

補足
「2パス推論」→ 長文だけ 要約(256 tokens) → 本抽出(1024 tokens)
「監視列」→ status / is_valid_json / has_min_keys をテーブルに残す

 まとめ
決定論で“数える”、LLMは“最小限に意味づけ”。この分業だけで運用安定性が一気に上がります。
BigQuery ML は flatten_json_output＋SAFE.PARSE_JSON、そして監視列の常設が肝。
Looker は 内部名=英数字／表示=日本語ラベル、配列は UNNEST ビューで迷子を防止。
まずは 軽量スキーマで回し、安定を確認してから sentiment / tone_shift / talk_balance などを段階追加していくのがおすすめです。

 参考文献https://zenn.dev/google_cloud_jp/articles/e4e713d2d20e92
https://cloud.google.com/bigquery/docs/generate-text?hl=ja
https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-generate-text
https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-create-remote-model

原因	対策	解説
入力メモが長すぎて `MAX_TOKENS` 到達	`SUBSTR(description, 1, 2000–4000)` で事前カット／スキーマを最小化／`max_output_tokens` を 1024 付近に	思考トークンを食い切って出力側の枠が残らず空返しになりがち。入力を短く＆出力JSONは軽量に。
返却経路の取り違え	`flatten_json_output = TRUE` を使い `ml_generate_text_llm_result` を読む（未対応環境なら `candidates[0].output_text` 等にフォールバック）	BQMLは環境で返却構造が揺れる。flattenで「生成文字列ひとつ」に統一するのが一番安定。
JSONが壊れている／前後に余白や注釈が混入	`REGEXP_EXTRACT(raw_text, '{…}')` で最初のJSONだけ抜く → `SAFE.PARSE_JSON`	「JSONだけ返して」と言っても一言まじることがある。壊れても落ちない実装に。
出力にコードフェンスや説明文が混ざる	プロンプトで「JSON 1個のみ・配列は最大5件・他の文不要」を明記＋stop_sequences=['```']＋`temperature=0`	“余計な親切”を封じる。作らせない／選ばせるがコツ。
Safety ブロック（NG語・個人情報など）	入力を事前にマスキング（URL・メール・電話）／`safety_settings` を緩める（使える環境のみ）	センシティブ語で候補ゼロになることがある。前処理で無毒化してから入れる。
スキーマが重い・プロパティ多すぎ	最小スキーマ（agency/stage/topics/bottlenecks/positive_factors/actions）にし、段階追加	欲張ると出力が長くなり壊れやすい。まずは動く最小から。
言語指定ぶれ（英語で返る）	「日本語で」「値も日本語ラベル（例：情報収集等）」を明記	ラベルが英語化すると後段の集計・表示が崩れる。固定化する。
`candidate_count` を増やしている	`candidate_count=1` に固定	複数候補はムダに揺らぐ＆コスト増。1本に絞る。
出力トークンが足りない	`max_output_tokens` を必要分だけ増やす（JSONなら 512–1024 目安）	入力を削っても、出力枠が小さすぎると途中で切れてJSON壊れる。
取り出しパスの JSON_PATH ミス	`ml_generate_text_llm_result` を第一選択。フォールバックに `$.candidates[0].output_text`	返却のキー構造が違うと常に NULL(参照)。経路は優先順位つけて多段COALESCEが安全。

原因	対策	解説
1クエリで大量行×`ML.GENERATE_TEXT` を一気に回している	バッチ分割（例: 50–200行ずつ）／日付で絞って段階投入／チェックポイント列で増分処理	LLM呼び出しは行ごとに重い。小分けにして失敗リトライしやすくするのが正解。
入力メモが長く、各行のレイテンシが高い	事前に上限カット／長文は要約→本抽出の2パス	「要約（200–400字）」を挟むだけで待ち時間と失敗率が激減。
出力トークンが過大	`max_output_tokens` を必要最小へ（要約は ~192、JSONは ~512–1024）	出力枠が大きいほど遅く高コスト。用途に合わせて絞る。
正規表現が重い／バックトラック地獄	前処理を単純置換に寄せる（URL/メール除去など）／lookaround乱用を避ける	ルール系は軽いREGEXPで。重い式は別CTEに切り出して再利用。
UNNEST を本処理で多用（行爆発）	特徴量テーブルでは配列のまま保持、分析時はビュー側でUNNEST	計算段階でUNNESTするとデータ量が爆発。表示用のUNNESTに分離。
データとモデルのリージョン不一致	同一リージョンにそろえる	跨ると待ち時間が増える。可能ならストレージ・モデル・実行を同リージョンへ。
同時ジョブが詰まっている（スロット/並列制御）	夜間など空いてる時間にスケジューリング／並行本数を制限	他処理とバッティングすると待たされる。時間帯と並列数の設計が効く。
毎回ゼロから再計算している	中間テーブルを永続化（`llm_raw`, `llm_json`）／失敗行だけ再実行	成果物を段階保存しておくと、途中失敗でも差分再開できる。
取得列が多くI/O過多	使う列だけに絞ってSELECT／早めに不要列を落とす	I/Oもレイテンシの敵。最小列×早い段で削減が効率的。
監視・再実行の導線が無い	`status`（空=成功）・`is_valid_json`・`has_min_keys` を常設し、ダッシュボードで監視 → 失敗行のみ再投下	見える化がないと丸ごとやり直しになりがち。失敗だけ拾って回す運用に。

Hacobuテックブログ

DXで物流の社会課題解決を目指す急成長スタートアップ「Hacobu（ハコブ）」が運営するテックブログです。エンジニアを積極採用中です！career.hacobu.jp/

概要

アーキテクチャ

特徴量算出 Query

実装で大変だったポイント

Gemini の返却結果がない

処理が返ってこない

まとめ

参考文献

Discussion