なぜSQLは「秘伝のタレ」になるのか ― 整えるという土台

計測も分析手法も学んだのに、なぜ同じKPIの数字が人とツールでぶれ、誰も再現できないのか。問題は手法ではなく、ローデータを「使える形」に整える層が無いからです。整っていないSQLは秘伝のタレと化し、その上に載る予測も因果も活性化も、土台のぶれをそのまま引き継ぎます。この記事では、秘伝のタレSQLが生まれる構造、アナリティクスエンジニアリング（分析に使うデータを整える工程に、ソフトウェア開発の作法を持ち込む考え方）と dbt が何を持ち込むのか、整える層がなぜ下流の上限を決めるのか、そして本連載の地図を示します。

最初に、対になる連載との棲み分けを示します。計測設計の連載（なぜ計測は静かに壊れるのか ― 計測設計という土台）は「データが生まれる前」——収集・同意・識別・送信の設計を扱いました。本連載はその一段下流、「データが入った後」——ローデータをマートに整える工程を扱います。計測設計が蛇口の設計なら、本連載は貯水と浄水です。接合率（媒体由来の成果を、サイト上の行動まで辿れた割合）も、計測設計の連載が設計し、本連載がマートに保持してテストで監視する、と役割を分けています。

「秘伝のタレSQL」が生まれる構造

現場で発生しがちな事象をそのまま書きます。秘伝のタレSQLは、誰かの怠慢で生まれるものではありません。むしろ真面目に手を動かす現場ほど、構造的に生まれます。

その場しのぎのSQLとスプレッドシートの限界

GA4のBigQuery Export、広告各媒体、Shopify、CRM——これらが別々のスキーマで降ってきます。横断して何かを出そうとすると、最初は誰もが同じことをします。その場でSQLを書き、足りない部分をスプレッドシートで補い、CSVを手で取り込み、突き合わせる。一度は動きます。

問題は、その「一度動いたクエリ」が捨てられず、月初のたびに少しずつ手で直されながら生き延びることです。下は、ある現場で実際に回っていた「真のCAC（成約あたりの獲得コスト）」の突合クエリの要点を抜粋したものです。

-- 月初に毎回手で直して回している「真のCAC」突合クエリ（抜粋）
-- 媒体の出稿・サイト行動・CRMの成約を一本に
select
  ad.campaign,
  case
    when lower(ad.utm_source) in ('google','google_ads','g-ads') then 'google'
    when lower(ad.utm_source) in ('meta','facebook','fb','ig')   then 'meta'
    else ad.utm_source                              -- 新媒体が増えるたびここに追記
  end as channel,
  sum(ad.cost)               as cost,
  count(distinct crm.deal_id) as deals
from `proj.dataset.ads_export_2026` as ad           -- 月が変わると表名を手で差し替え
left join `proj.dataset.ga4_events_*` as ga
  on ga.user_pseudo_id = ad.click_id                -- 突合キーが噛み合わない箇所あり
left join crm_export_sheet as crm                   -- スプレッドシートを都度CSVで取り込み
  on crm.email = ga.user_email
where ad._table_suffix between '20260501' and '20260531'
group by 1, 2;

このクエリには、秘伝のタレの要素が全部入っています。表名がベタ書きで月ごとに手で差し替わる。channel の正規化が case 式に埋め込まれ、新しい媒体が増えるたびにこの一箇所を「知っている人」が追記する。突合キーが本当は噛み合っていない箇所がコメントで放置される。CRMはスプレッドシート由来で、取り込み手順は書いた人の頭の中にしかない。動いてはいますが、これは資産ではなく負債です。

同じKPIなのに数字がぶれる（定義の散逸）

「CVは何を数えているのか」を三人に聞くと、三者三様の答えが返ってくる状況。媒体管理画面のCV、GA4上のCV、CRM上の実成約——どれも「CV」と呼ばれ、別の数を指している。channel の正規化ロジックも、上のクエリ・別の人のクエリ・Looker Studio・スプレッドシートに、少しずつ違う形で四回書かれています。

定義がコードとシートに散らばっている限り、突き合わせれば数字は合いません。そして合わないことに気づいた会議は、意思決定ではなく「どの数字が正しいのか」の論争に時間を溶かします。これは精度の問題ではなく、定義の置き場が無いという構造の問題です。

再現できない・壊れても気づかない

秘伝のタレSQLは、書いた本人に紐づきます。担当者が抜けた瞬間、誰も再現できなくなる。属人化したクエリは資産に見えて、実際には個人にしか復元できない負債です。

さらに悪いのは、壊れても誰も教えてくれないことです。上流の媒体がスキーマを変えた、ある日からデータが半分欠けている——こうした異常はテストが無ければ静かに下流へ流れ、間違った数字のまま予算を動かす根拠になります。気づくのは、たいてい動かした後です。

ここで一次データを例に、地に足の着いた話にできればと思います。以前に扱った都内のオフィス仲介の受託案件（匿名化・数値はレンジで丸めています）では、案件開始時、真のCACを出すための突合クエリとスプレッドシートが十数本単位で散在し、同じ「CV」が三〜四通りの定義で並存していました。媒体由来の成果をサイト上の行動まで辿れた割合（接合率）は一度として継続監視できておらず、月次のたびに誰かがその場のSQLで概算し直していました。接合率が分からなければ、その先の予測モデルに使える実弾が成果の何割なのかも分かりません。

整える層とは何か（raw data → staging → intermediate → marts）

ここまでの症状は、どれも一つの欠落から来ています。ローデータを「使える形」に変換する工程を、その場しのぎのクエリではなく、定義を一元化した恒常的な仕組みとして持っていないこと。これが整える層です。

整える層は、ローデータを次の三段で積み上げます。

[ ローデータ ]                GA4 BQ Export / 広告各媒体 / Shopify / CRM
     │
     ▼
[ staging ]      生に最初に触れて正規化する層（1ソース1ステージング）
     │
     ▼
[ intermediate ] 再利用される結合・集計を置く中間層
     │
     ▼
[ marts ]        事業の語彙で表現した最終形（customers / orders / sessions …）
     │
     ▼
[ 下流 ]          予測（Kaggle編）/ 因果（測る）/ 活性化（動かす）

そしてこの整える層は、もっと大きな流れの一部です。価値連鎖でいえば、計測（入口）→ 整える（本連載）→ 作る → 測る → 活性化（動かす） の二番目。計測①が「正しく入ってくるデータ」を作り、本連載が「使える形」に整え、その上で初めて作る・測る・動かすが立ちます。本連載は分析の話ではなく、分析が立つ土台を作る話です。

アナリティクスエンジニアリングと dbt の登場

整える層をその場のクエリでなく仕組みにする、という発想に名前を与えたのがアナリティクスエンジニアリング（analytics engineering）です。中身は新しくありません。ソフトウェア工学が当たり前にやってきた作法——version 管理・テスト・モジュール化・単一の真実（single source of truth）——を、分析の変換工程に持ち込むだけです。

その実装として広く使われているのが dbt です。dbt は「データはすでに DWH にある」前提で、ローデータからマートへの変換、つまり ELT の T（Transform）を宣言的に書きます。先ほどの秘伝のタレSQLとの違いは、思想に出ます。表名をベタ書きする代わりに依存を宣言し、channel 正規化のようなロジックを一箇所に集約し、変換結果に制約（テスト）を付ける。たとえば staging はこう書けます。

-- stg_ads__spend.sql ：ローデータに触れるのはここだけ・channel 正規化を一度だけ書く
select
  campaign,
  {{ normalize_channel('utm_source') }} as channel,  -- 正規化を1か所（マクロ）に集約
  cost,
  spend_date
from {{ source('ads', 'spend') }}                     -- 表名でなく source で宣言

source() でローデータを宣言し、channel の正規化ロジックをマクロに切り出して一度だけ書く。下流は表名でなく ref() でこのモデルを参照するので、依存関係（系譜）が自動で立ちます。媒体が増えても直すのはマクロ一箇所、月が変わっても表名を手で差し替える必要はありません。

道具の固有の手順——インストール、profiles.yml、コマンド体系——は version で変わりやすく、公式 docs が最も正確です。本連載はそこを追いかけません。扱うのは、概念（三層・テスト・系譜・モデリング）と、自社の一次データでそれがどう効くか、です。

「整える層」の系譜 ― 誰の発明を継いでいるのか

ここで一つ、言及しておきたいことがあります。既製の dbt パッケージ（媒体やSaaSごとに用意された変換テンプレート）を使うと、SCD Type 2・サロゲートキー・きれいな層分け・ワイドな最終テーブルが、最初から「当たり前の型」として出てきます。これを「dbt が発明した新しいやり方」と受け取るのは誤解です。あの型は、単一の発明ではなく三つの系譜の合成です。

第一に、概念の根幹はディメンショナルモデリング（Kimball）です。SCD Type 2（履歴を上書きせず残す）・サロゲートキー・グレイン（粒度）の宣言・ディメンション/ファクトの分離——marts 層で使う語彙のほとんどは、数十年前に整理された古典に由来します。新刊でも流行でもありません。

第二に、フォルダをどう切るか——staging / intermediate / marts という層分けの作法は、モダンデータスタックの中で標準化された別系統の知恵です。Kimball が「どうモデリングするか」を担うのに対し、これは「どう整理するか」を担います。担当領域が違うので、混同すると話が噛み合いません。

第三に、最終形の太さ。列指向 DWH を前提にすると、厳密に正規化したスター・スキーマよりも、結合済みで分析にすぐ使えるワイドなテーブル（OBT 的な折衷）が好まれる場面が増えました。既製パッケージの最終形はこちら寄りで、ここは Kimball の原典から一部離れています。

だから「既製パッケージ＝ Kimball そのもの」でも「 dbt の新発明」でもありません。Kimball の語彙を、層分けの作法とモダンスタックのワイドテーブルがどう取り込み、どこで離れたか——その合成として読むのが正確です。スター・スキーマを採るのか、ワイドテーブルを採るのか、という判断は marts を組む第5回で正面から扱います。本回ではまず、自分が使っている型の出どころを取り違えないこと、を押さえてください。

整える層が下流の上限を決める

この節でお話しすることが、本記事がこの連載の「根幹」である理由です。予測（リードスコアリング）も、因果（施策が本当に効いたかの測定）も、活性化（セグメントへの自動配信）も、整ったマートがある前提で初めて成立します。土台がぶれていれば、その上にどれだけ高度な手法やAIを載せても、出力は土台のぶれを増幅して返します。

冒頭の接合率がいい例です。整える層が無ければ、成果を行動まで辿れた割合すら継続して測れない。すると、その先の予測に使える実弾が成果の何割かも分からず、モデルの精度を語る土俵にすら上がれません。整える層は、下流でできることの天井を決めます。Kaggle 連載・因果連載・活性化連載が「customers や orders といったマートがある前提」で書けるのは、その正典をこの連載が作るからです。

この連載の地図

本連載は、ローデータからマートへ、そして属人性から組織の資産へ、という順で積み上げます。

中心に来るのは第5回です。架空のD2C「SOLNA」を素材に、customers / orders / sessions / subscriptions / ad_spend / messages / reviews を実際に組み、結合キー customer_id × ga_client_id の置き場と接合率の保持を marts に固定します。ここが他連載の使うスキーマの正典になります。

章末チェックリスト

• 自社のSQLが秘伝のタレ化していないか（表名ベタ書き・正規化ロジックの重複・属人化）
• 同じKPIの定義が一元化されているか（「CV」が一つの計算式を指しているか）
• 整える層が下流（予測・因果・活性化）の上限を決める、と言えるか

「そもそもデータ基盤に投資すべきか」という決裁者向けの判断は、対になる insights ピラーデータ基盤に投資すべきか ― 整える層が無いと何が壊れるかへ。整えた土台の上で「何を測り、何を動かすか」を考える段階は記述・予測編へ送り出します。次回は、dbtの考え方とプロジェクト構造（3層・source/ref）を具体的に見ます。