BI でセグメント抽出の相談を受けるときにつらい話です。
3行
- ポジティブリスト (条件にマッチしたデータ) を取り出すのは簡単だけど、ネガティブリスト (条件にマッチしないデータ) を取り出すのは難しい
- だけど、この難しさが伝わらない
- 伝えるための文章
この記事は「顧客の注文データ (顧客 1 : 注文 N)」や「ユーザのアクセスデータ (ユーザ 1 : ページ N)」のような構造のデータの場合の話です
よくあるリスト抽出
「商品Xを買った人」「直近30日で予約した人」——この手のリストは一瞬で出せる。Looker でも b→dash でも、フィルタをポチポチやれば終わり。
ところが「商品Xを買っていない人」になった瞬間、急に出せなくなります。というか出るんだけど、それはあってないのです。
言葉の上では「した／していない」をひっくり返しただけなのに、データの世界ではまったく難易度が違う。これ、使い方が悪いわけでも、ツールがショボいわけでもなくて、構造的にそうなっている。
「ある」の確認と「ない」の確認は対称じゃない
まず計算の話をする。
「買った」を確認するのは簡単で、購入履歴を上から見ていって1件でも見つかった時点で「はい、買ってます」と確定できる。途中で打ち切れる。
-- 1件見つかれば即確定 SELECT DISTINCT customer_id FROM orders WHERE product_id = 'X';
一方「買っていない」は、その人の購入履歴を全部舐めて、どこにも該当がないことを確認して初めて言える。途中で止められない。
-- 全顧客 × 全注文を突き合わせないと言えない SELECT c.customer_id FROM customers c WHERE NOT EXISTS ( SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id AND o.product_id = 'X' );
EXISTS と NOT EXISTS。SQL なら書き方はほぼ対称だけど、計算量は非対称。これが第一の理由です。 つまり、
- 「買った」は1件あればOK
- 「買ってない」は「顧客の全リストを集約」して始めてわかる
という構造的な違いがある (これを集約の集約と呼んだりします)。
BI ツールは「集約の集約」ができない
BI ツールは基本的に「2段階の絞り込み」ができない (derived table の話ではないです)
「〜していない人」を出すには、
- 顧客ごとに、その行動が何件あるか数える（GROUP BY + COUNT）
- その結果が0件の人だけ残す（HAVING count = 0）
という2段階の処理が要る。SQL でいう副問い合わせとか HAVING の世界。
Looker や b→dash のセルフサーブ画面は、この「一度集計して、その結果でもう一度絞る」ができない設計になっている。これはバグじゃなくて、わかりやすさと安全性のためにあえてそうしている。
（「SQLなら一瞬なのに」と言われることがあるけど、それは SQL にその機能があるからであって、BI ツールの役割が違うだけの話）
よくある誤解：「X以外」で絞ればいいのでは
ここ、直感が裏切られるポイント。
「商品Xを買っていない人」を出したくて、「商品 ≠ X」でフィルタをかける。一見よさそうだけど、これだとXもYも買った人が Y の行でヒットして残ってしまう。
-- これは「Xを買っていない人」ではない -- 「X以外の何かを買った人」であり、XとYを両方買った人が残る SELECT DISTINCT customer_id FROM orders WHERE product_id != 'X';
NOT filter ≠ ネガティブリスト。ここを混同すると、抽出結果がサイレントに間違ってしまうことが多いです。
でこれに対する回避策ですが、「フラグを先に焼いておく」というのがあります。 現実的な方法は1つで、難しい2段階目を、データチーム側で先にやっておく。
具体的には mart 層で「この顧客は商品Xを買ったことがあるか？ はい/いいえ」のフラグを事前に計算しておく。
SELECT c.customer_id, MAX(CASE WHEN o.product_id = 'X' THEN 1 ELSE 0 END) AS has_purchased_x FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id GROUP BY c.customer_id;
こうしておけば、BI ツール側は has_purchased_x = 0 で絞るだけ。セルフサーブで「Xを買っていない顧客」が出せるようになる。
ただしこの方法は全然筋が良くなくて
- 固定条件（「初回購入」「最新予約」など）→ フラグ化できる。いまの mart はだいたいこっち
- パラメトリック条件（「○月以降、予約していない」「△円以上買っていない」）→ 日付や金額の組み合わせが無限にあるので、フラグとして事前に焼けない。都度データチームに個別依頼するしかない
さらに複合条件になると話はもっと厄介で、「キャンセルして再予約していない」みたいなのは——人間が見れば一目でわかるんだけど——フラグ化の難易度が跳ね上がる。
壁を「仕様」にする
運用で一番効くのは、何がセルフサーブで出せて、何が出せないかの線引きを明文化しておくこと。
- フラグが用意してある条件 → セルフサーブで抽出可能
- それ以外 → データチームに依頼
このカタログがあるだけで、「できるはずなのにできない」というモヤモヤが「これは依頼するもの」という納得に変わる。
壁を仕様に変える。地味だけど、これがいちばんの近道だと思っている。
補足
集約の集約が難しい話とかは割と議論はありますが、 「構造の違い」「BIツール」のかみ合わせの悪さを書いた記事がなかったので書きました
