0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@satsat(Mats)

BQ MLで実装するCVR予測モデル

0
Posted at

Disclaimer
本記事は、BigQuery ML (BQML) および DNNモデルの技術的知見の共有を目的として構成された架空のケーススタディです。登場する企業、商材、数値データはすべて実在しない架空のものであり、実在の組織やサービスとは一切関係ありません。

1. 「精度そのまま、配信数100倍」という無理ゲー

マーケティング担当からの相談は、いつも唐突で、そして無茶苦茶です。

「今の『富裕層向けプラン』の配信、CVR（精度）はそのままで、配信ボリュームを100倍にできない？」

対象は、高所得者層を狙ったニッチな高単価商材。
現状は「会員ランク：プラチナ以上」かつ「購入額上位5%」というガチガチのルールベースで運用しており、確かにCVRは高い。しかし、対象ユーザーは全会員の わずか0.2% 。これではビジネスがスケールしません。

  • 現状: 精度は高いが、規模が出ない。
  • ミッション: 高い精度（Precision）を維持したまま、まだ見ぬ「隠れ優良顧客」を掘り起こす。

結局、何をやったのか？

結論から言うと、今回実施したのは「過去のCVデータを教師データとして全会員をスコアリングし、CV見込みの高い潜在層（Look-alike）を特定する」というアプローチです。

▼ 実施内容の比較

手法 ロジック 対象ユーザー 課題・成果
Before ルールベース 「会員ランク：プラチナ」かつ「購入額上位 5%」 顕在層
全会員の 0.2%)		 精度は高いが、在庫が枯渇してしまう
After AIスコアリング(Look-alike) 過去のCVユーザーと行動特徴」が似ている人を抽出 潜在層(全会員の 約16%) 精度を維持したまま、配信規模を80倍に拡大

▼ 技術的なプロセス

この難題に、BigQuery ML (BQML) の Deep Neural Network (DNN) で挑みました。
本稿では、数万次元を超える「スパースデータ」を扱う際の落とし穴と、SQLだけで完結させる特徴量エンジニアリング、そしてビジネスインパクトの試算まで、泥臭い試行錯誤の全容を共有します。

2. 制約：Python禁止、データ持ち出し禁止

今回のプロジェクトを難しくしていたのは、インフラ運用上の制約でした。

  1. No Data Movement: セキュリティポリシー上、DWH（BigQuery）からデータを持ち出せない。
  2. SQL Only: 運用リソースの都合上、Python環境（Vertex AI Custom Jobs等）の管理ができない。

つまり、pandas も scikit-learn も使えません。すべての工程を、BigQuery上のSQLだけで完結させる必要がありました。

3. データ戦略：99.9%が「0」の世界

データの特性と「次元の呪い」

手元にあるデータは大きく2種類です。

  1. Dense Feature（属性）: 年齢、性別、居住地など。
  2. Sparse Feature（行動ログ）: 商品閲覧履歴、検索キーワード。

厄介なのは後者です。ECサイトの商品IDは数万〜数十万に及びます。これを単純にOne-Hot Encodingすれば、次元数が爆発し、まともな学習は望めません。

モデルを作る前に、まずは ML.FEATURE_INFO でデータの健康診断を行います。スパースデータはNULLや異常値が混入しやすいため、ここでのチェックは必須です。

📝 公式ドキュメント: ML.FEATURE_INFO 関数

SELECT * FROM ML.FEATURE_INFO(MODEL `project.dataset.high_value_user_prediction_dnn`)

【Core Concept】なぜ XGBoost ではなく DNN なのか？

ここがモデル選定の最大の分岐点です。
BQMLの仕様上、STRING 型のカテゴリ変数は自動的に One-Hot Encoding されます。10万種類の商品IDがあれば、10万次元の疎行列が生成されます。

このデータを扱う上で、我々は定石の 決定木（XGBoost/GBDT） を捨て、DNN (Deep Neural Network) を採用しました。理由はシンプルに以下の2点です。

1. スパースデータに対する「学習効率」と「精度」

10万次元のスカスカなOne-Hot入力に対し、決定木が有効な分割点を見つけるのは計算量的に非常に効率が悪く、過学習を起こしやすくなります。
対してDNNは、高次元入力を低次元の密ベクトルに圧縮して表現する能力（Embedding的な挙動）に長けており、スパースデータでも安定した精度が出せます。

2. 未知の組み合わせへの「汎化性能」

決定木は「学習データにある組み合わせ」を記憶するのは得意ですが、見たことがないパターンには弱いです。
DNNは学習過程で「商品Aと商品Bは似ている」という潜在的な特徴を捉えるため、学習データにない「商品B × 30代男性」という組み合わせに対しても、妥当な予測（汎化）を行えます。

今回の「隠れ優良顧客の発掘（＝見たことのない組み合わせの発見）」という目的には、DNNが最適解でした。

【技術解説】DNNの重みが「実質的なEmbedding」になる理由（クリックして展開）

なぜ「Embedding層」を明示的に作らなくても、DNNが高次元データを圧縮できるのでしょうか？
数学的な挙動を見ると、DNNの入力層は 実質的な埋め込み層（Embedding Layer） として機能していることがわかります。

① 入力（One-Hot）
[0, 0, 1, 0, ...] のように、商品IDに対応する1箇所だけが「1」のベクトル。

② 重み行列
DNNの入力層には、商品数分の行を持つ巨大な重み行列が存在します。

③ 演算（行列積）
この2つを掛け算すると、「0」の部分はすべて消え、「1」に対応する行だけがそのまま出力されます。

つまり、計算上は行列積を行っていますが、結果として起きているのは「重み行列という辞書から、その商品の特徴ベクトルを抜き出す（Look-up）」という操作です。

これにより、Word2VecのようなEmbedding層を明示的に作らなくても、DNNが学習過程で重み行列を更新することで、**「商品の意味的な類似性」**を自動的に獲得してくれます。

実装：TRANSFORM句による前処理の封じ込め

MLOpsの基本ですが、学習時と推論時のロジック乖離（Training-Serving Skew）を防ぐため、TRANSFORM 句を使って前処理をモデルに内包させます。
詳しくは公式ドキュメント：TRANSFORM 句の構文を参照してください。

preprocess.sql
CREATE OR REPLACE MODEL `project.dataset.high_value_user_prediction_dnn`
TRANSFORM (
  flag, -- 目的変数

  -- 【Dense】属性データ
  CAST(age_range AS STRING) AS age_range,
  
  -- 【Sparse】行動データ（カテゴリ変数）
  -- 数万次元をあえてOne-Hotせず、明示的なフラグとして入力
  IFNULL(log_view_category_camping, 0) AS log_view_category_camping,
  IFNULL(log_view_brand_high_end, 0) AS log_view_brand_high_end,
  
  -- 検索ワードのハッシュ化（カーディナリティ対策）
  IFNULL(search_term_hash_1, 0) AS search_term_hash_1
)
OPTIONS(...)

4. モデリング：DNN × FTRL で「過学習」を防ぐ

FTRLオプティマイザの採用

スパースデータの学習において、一般的な Adam などを使うと、不要な特徴量の重みが完全に0にならず、ノイズが残りやすいという問題があります。
そこで採用したのが、Googleのお家芸とも言える FTRL (Follow The Regularized Leader) です。

L1正則化と組み合わせることで、「効かない特徴量の重みを完全にゼロにする（スパース性を維持する）」ことが可能です。これはクリック率予測などの大規模スパースタスクにおける定石です。

📝 公式ドキュメント: DNN モデルの CREATE MODEL

データ分割と学習曲線の監視

教師あり学習の鉄則として、評価データは学習データから厳密に分離する必要があります。
今回は DATA_SPLIT_METHOD = 'AUTO_SPLIT' を指定し、BQMLに自動分割を任せました。

training.sql
OPTIONS(
  model_type = 'DNN_CLASSIFIER',
  -- 【重要】スパースデータに強い最適化手法
  optimizer = 'FTRL', 
  l1_reg = 0.5,       -- 不要な変数をバッサリ落とす
  l2_reg = 0.1,
  hidden_units = [128, 64, 32],
  
  -- 【重要】過学習を防ぐ自動分割
  data_split_method = 'AUTO_SPLIT',
  early_stop = TRUE
)

学習後は必ず ML.TRAINING_INFO でLossの推移を確認します。loss（学習データ）と eval_loss（評価データ）が乖離せず収束していれば、Overfittingは防げています。

💡 発展：Wide & Deep モデルへの拡張
今回はシンプルさを優先して DNN_CLASSIFIER を採用しましたが、さらなる精度向上には Wide & Deep Learning (DNN_LINEAR_COMBINED_CLASSIFIER) が有効です。
「特定の組み合わせ」を記憶するWide部と、「類似性」を汎化するDeep部を組み合わせることで、分類精度をさらに高められます。

5. 解釈の罠：「平均」に騙されない

モデルの精度は出ましたが、中身がブラックボックスではマーケターを説得できません。
そこで ML.EXPLAIN_PREDICT を叩いたのですが、ここで問題が発生しました。「年齢」や「居住地」ばかりが上位に来て、肝心の行動ログが出てこないのです。

原因：スパースデータの「シグナル消失」

行動ログはスパース（例: 1%の人しかフラグが立っていない）なので、全データの平均をとると、残り99%の「0」に引っ張られて重要度が限りなくゼロに近づいてしまいます。

公式ドキュメント「BigQuery Explainable AI の概要」には、Global Explainabilityについてこう書かれています。

*ML.GLOBAL_EXPLAIN は... **平均絶対アトリビューション（average absolute attribution）*に基づいて、グローバルな説明可能性を返します。

対策：「強さ」と「向き」を分離して見る

公式の定義（絶対値平均）に従いつつ、それが「プラス（購買促進）」なのか「マイナス（抑制）」なのかを知るために、以下の2指標を手動で集計しました。

  1. Magnitude (重要度) = AVG(ABS(attribution))
  • ユーザー数は少なくても、刺さればデカい「ニッチな行動」を拾う。
  1. Direction (方向性) = AVG(attribution)
  • それが購買要因なのか、阻害要因なのかを見る。

▼ 解釈クエリの実装

explain.sql
SELECT
  feature_info.feature AS feature_name,
  -- 【重要】絶対値をとって平均し、ニッチなシグナルを救う
  AVG(ABS(feature_info.attribution)) AS importance_magnitude,
  AVG(feature_info.attribution) AS directional_impact
FROM
  ML.EXPLAIN_PREDICT(MODEL `...`, (SELECT * FROM `test_data` LIMIT 100000), ...)
  , UNNEST(top_feature_attributions) AS feature_info
GROUP BY 1
ORDER BY 2 DESC;

結果、「高級時計（メジャー）」の閲覧よりも、「ワインセラー（ニッチ）」の検索履歴の方が、CVRへの寄与度（Magnitude）が高いことが判明しました。これはDNNが見つけた「隠れ富裕層」のシグナルです。

6. ビジネスインパクト：技術を「価値」に翻訳する

「AUCが0.8でした」と言ってもビジネスサイドには響きません。
我々は ML.ROC_CURVE を使って**「精度と配信ボリュームのトレードオフ」**を可視化し、クライアントの事業成長にどれだけ貢献できるかを試算しました。

📝 公式ドキュメント: ML.ROC_CURVE 関数

① 閾値シミュレーション

ML.ROC_CURVE は、様々な閾値における Precision / Recall を一覧で返します。
クライアントの必達目標である「精度25%」を維持できるラインを探索しました。

閾値 (Threshold) 推定配信UU数 予測精度 (Precision) 判定
0.80 10,000 45.2% 少なすぎる
0.55 500,000 25.8% 【採用】ここがスイートスポット
0.40 1,200,000 18.0% 精度不足

② クライアントへの提供価値（Sales Uplift）

この「50万UU」への拡張が、クライアントのビジネスにどのようなインパクトを与えるのか。
単純な配信数ではなく、「獲得できる見込みコンバージョン数（Expected CV）」で試算を行いました。

【試算の前提】

  • 拡張配信数: 500,000 UU
  • 予測CVR: 25.8% （モデルのPrecisionに基づく期待値）

【年間インパクト試算】

  1. Before（ルールベース運用）
  • 配信対象が極小（数千UU）のため、獲得できるCV数は月間数百件程度で頭打ちでした。
  1. After（AIによる拡張）
  • 約12.9万件 の見込みCV機会 を創出。
  • これまでアプローチできていなかった「隠れ優良顧客」に対し、精度を担保した状態で大規模なアプローチが可能になりました。

エンジニアが泥臭くモデルの精度を1%上げることは、単なる数字遊びではなく、クライアントの売上（Top-line）を数千万円〜数億円規模で押し上げる可能性に直結します。
また、本モデルの学習にかかるBigQuery計算コストは数千円程度であり、ROI（投資対効果）は極めて高い結果となりました。

7. まとめ

  • 制約: BQMLのみ、Python禁止。
  • 手法: スパースデータを TRANSFORM で処理し、DNN × FTRL でモデリング。
  • 評価: DATA_SPLIT_METHOD で過学習を防ぎ、ML.ROC_CURVE で閾値を最適化。
  • 解釈: 公式のGlobal Explain定義を理解した上で、「Magnitude」と「Direction」を分離して可視化。
  • 成果: 精度25%を維持したまま、配信対象を約80倍に拡大。

技術的な詳細は多岐にわたりましたが、本質は「CVデータを用いて潜在顧客をスコアリングし、ビジネス価値を可視化した」。これに尽きます。

「Pythonが使えないから高度なことができない」というのは言い訳でした。
公式ドキュメントを読み込み、BigQuery MLの仕様（特にDNNやFTRL、XAIの挙動）を深く理解すれば、SQLだけでビジネスを動かす強力なMLパイプラインは構築可能です。

0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?