マーケティングデータのモデリング
クリーンで結合されたテーブルを作ります。
「マーケティングデータのモデリング」はCoddyKit上の無料Digital Marketing Academyレッスンです。 これはレッスン3/4です。 下記で完全なレッスンを無料で読むことができます。その後、ブラウザ内の組み込みコードエディタと24時間対応のAIチューターでハンズオン演習できます。 これはDigital Marketing Academy学習パスの一部であり、ウェブとCoddyKitアプリ全体で進捗が同期されます。 Digital Marketing Academyコースには全4レッスンが含まれています。
なぜモデリングするのか
コネクタが取り込んだ生データテーブルは、列名が統一されていなかったり、通貨が混在していたり、粒度が異なっていたり、プラットフォーム固有の癖があったりして扱いにくいものです。これらを直接クエリすると、誤った再現性のない数値になります。
データモデリングとは、生の行データをクリーンで一貫性のある、ビジネスで利用できるテーブルへ変換する取り組みです。ROAS、コンバージョン、収益を一度正しく定義し、すべてのレポートで同じ結果になるようにする場所です。
スタースキーマ
分析で主流のモデルはスタースキーマです。測定可能なイベントを集めた中央のファクトテーブルを、イベントを説明するディメンションテーブルが取り囲みます。ファクトには数値（支出、クリック数、収益）を格納し、ディメンションにはコンテキスト（キャンペーン、日付、チャネル、顧客）を格納します。
この形はマーケターにとって直感的で、BIツールにとっても効率的です。BIツールは1つのファクトを複数のディメンションと結合し、任意の属性で指標を切り分けられます。
dim_date
|
dim_channel -- fct_ad_spend -- dim_campaign
|
dim_account
fct_ad_spend (facts): impressions, clicks, cost, conversions
dims: who / what / when contextファクトとディメンション
ファクトテーブルは縦長で加算可能です。イベント単位、または日付とキャンペーン単位で1行を持ち、合計できる数値メジャーを含みます。ディメンションテーブルは横に広く説明的です。キャンペーン単位または顧客単位で1行を持ち、フィルタリングやグループ化に使う属性を含みます。
見分け方は、SUMするものならファクト、GROUP BYするものならディメンションです。支出はファクトで、キャンペーン名はディメンションです。
fct_ad_spend dim_campaign
---------------- ----------------
date campaign_id (PK)
campaign_id (FK) campaign_name
cost <-SUM-> channel
clicks <-SUM-> objective
conversions start_date粒度：最初に決めること
粒度とは、ファクトテーブルの1行が何を表すかです。最初に宣言することが、モデリングで最も重要な意思決定です。日次の行と累計値の行のように粒度を混在させると、二重計上が起こり、下流のすべての指標が壊れます。
粒度は「キャンペーンごと、日ごとに1行」のように平易な言葉で明示してください。そうすれば、すべての列がその粒度で正しくなり、すべてのロードもその粒度を守る必要があります。
Declared grain: one row per campaign per day
-- enforce uniqueness on the grain
SELECT date, campaign_id, COUNT(*)
FROM fct_ad_spend
GROUP BY 1,2
HAVING COUNT(*) > 1; -- must return 0 rowsステージングモデル
ファクトとディメンションの前に、ステージングモデルを構築します。ソーステーブルごとに1つ作成し、列名を標準化し、型を変換し、単位（セントからドル、UTCの日付）を統一します。1つのステージングモデルが対応する生データテーブルは1つだけです。
ステージングはクリーニングのレイヤーです。ソース固有の癖を隔離するため、下流のマートはMetaが支出と呼ぶものをGoogleがコストと呼んでいることを意識せずに済みます。
-- stg_google_ads__spend
SELECT
date AS spend_date,
campaign_id,
'google' AS channel,
cost_micros / 1000000 AS cost, -- micros -> dollars
clicks,
conversions
FROM raw.google_ads__campaign_stats;チャネルの統合
各広告プラットフォームは異なる形式でレポートしますが、ステージング後は共通の形になります。次のモデルでは、それらを1つのクロスチャネル支出ファクトにUNIONします。これは統合レポートの基盤です。
この1つのテーブルによって、全体のROASを計算できます。すべてのチャネルが同じ列に統一されているため、Google、Meta、TikTokの支出を1つのクエリで同時に合計できます。
-- fct_ad_spend: union all channels
SELECT * FROM stg_google_ads__spend
UNION ALL
SELECT * FROM stg_meta_ads__spend
UNION ALL
SELECT * FROM stg_tiktok_ads__spend;
-- now: SUM(cost) GROUP BY channel works適合ディメンション
クロスチャネル分析では、ディメンションを適合させる必要があります。つまり、すべてのファクトが同じ形で結合する共通のdim_dateとdim_channelを用意します。そうすれば、「月別・チャネル別の収益」は、広告、メール、ウェブのどのソースでも同じ意味になります。
適合ディメンションがあるからこそ、1つのグラフで支出と収益を並べて表示できます。これがなければ結合がずれて、合計値が気付かないうちに一致しなくなります。
Conformed dims shared across facts:
dim_date -> joined by every fact on date
dim_channel -> 'google','meta','email','organic'
dim_campaign -> unified campaign keys
-> spend and revenue line up on the same axesSQLでのアトリビューション
アトリビューションでは、コンバージョンへの貢献を各タッチポイントに割り当てます。ラストクリックは最も単純で、コンバージョン直前の最後のマーケティングソースにすべての貢献を与えます。ファーストクリック、線形、ポジションベースでは、貢献を異なる方法で配分します。
データウェアハウスでは、アトリビューションをプラットフォームのブラックボックスではなく、モデルとして実装します。GA4のイベントレベルデータがあれば、ユーザーごとにタッチポイントをウィンドウ関数で処理し、任意のルールを適用して、各モデルの結果を公平に比較できます。
-- last non-direct click per conversion
WITH touches AS (
SELECT user_id, channel, event_time,
ROW_NUMBER() OVER (PARTITION BY user_id
ORDER BY event_time DESC) AS rn
FROM web_touchpoints
WHERE channel <> 'direct'
)
SELECT channel, COUNT(*) FROM touches WHERE rn=1
GROUP BY 1;緩やかに変化するディメンション
ディメンションの属性は時間とともに変化します。たとえば、キャンペーンの予算責任者が変わったり、顧客のランクが上がったりします。Type 2の緩やかに変化するディメンションでは、既存の行を上書きせず、有効期間の日付を持つ新しい行を追加して履歴を保持します。
これは、ある時点での正確なレポートに重要です。コンバージョンした時点で顧客がどのセグメントに属していたかを知るには、現在のディメンションではなく、その時点で有効だったディメンションのバージョンが必要です。
dim_customer (SCD Type 2)
cust_id tier valid_from valid_to is_current
101 free 2026-01-01 2026-04-01 false
101 pro 2026-04-01 9999-12-31 true
-- join on event_date BETWEEN valid_from AND valid_toテストとドキュメント
モデルはコードなので、テストしてください。dbtなどのツールを使えば、キーが一意でNULLでないこと、チャネルの値が許可された集合に含まれること、テーブル間のリレーションが成立していることを検証できます。
テストによって、スキーマドリフトや誤った結合がダッシュボードに到達する前に検出できます。自動生成されたドキュメントやリネージュと組み合わせることで、モデルは壊れやすいブラックボックスではなく、信頼でき、引き継ぎやすいものになります。
# dbt schema test
models:
- name: fct_ad_spend
columns:
- name: campaign_id
tests: [not_null]
- name: channel
tests:
- accepted_values:
values: ['google','meta','tiktok']マート：最終レイヤー
最上位のレイヤーはマートです。マーケティング担当者など、特定の利用者向けに整形した、ビジネスでそのまま使えるテーブルです。たとえばmarketing_performanceマートには、支出と収益の結合や、チャネル・日別のROAS計算がすでに含まれています。
BIツールはマートだけを参照します。ここであらかじめ結合と集計を済ませておくと、ダッシュボードを高速かつ低コストに保て、すべてのアナリストが同じ正しい定義を利用できます。
-- marts.marketing_performance (1 row / day / channel)
SELECT s.spend_date, s.channel,
SUM(s.cost) AS spend,
SUM(r.revenue) AS revenue,
SAFE_DIVIDE(SUM(r.revenue), SUM(s.cost)) AS roas
FROM fct_ad_spend s
LEFT JOIN fct_revenue r USING (spend_date, channel)
GROUP BY 1,2;確認問題
広告パフォーマンスのファクトテーブルを構築しており、二重計上を避ける必要があります。列を1つでも作成する前に宣言すべき、最も重要なことは何でしょうか。
振り返り
モデリングでは、複雑で扱いにくい生のテーブルを、信頼できビジネスで使えるデータへと、複数の層を通じて変換します。ステージングでは各ソースをクリーンアップして形式を統一し、ファクトと統合ディメンションでスター スキーマを構成し、マートではBI用に必要なデータをあらかじめ結合します。
まず粒度を定義し、チャネルをUNIONして統合指標を作り、アトリビューションとSCD Type 2の履歴をSQLで実装します。さらに、すべてのモデルをテストして、誤った数値がダッシュボードに届く前に確実に検出できるようにします。
よくある質問
「マーケティングデータのモデリング」レッスンは無料ですか？
はい。「マーケティングデータのモデリング」の完全なテキストはこのウェブで無料で読めます。インタラクティブに演習し（組み込みコードエディタと24時間対応のAIチューター）、Digital Marketing Academyコースの残りをアンロックするには、CoddyKit PROにアップグレードしてください。 Digital Marketing Academyコースには全4レッスンが含まれています。
「マーケティングデータのモデリング」で何を学びますか？
クリーンで結合されたテーブルを作ります。 ブラウザで直接実行するハンズオンコードでDigital Marketing Academyを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。
Digital Marketing Academyを始めるのに経験は必要ですか？
事前経験は必要ありません。CoddyKitのDigital Marketing Academyは初級者から上級者向けに構成されているため、ここから始めるか最初から始めて、自分のペースで進むことができます。 これはレッスン3/4です。
「マーケティングデータのモデリング」レッスンにはどのくらい時間がかかりますか？
ほとんどのCoddyKitレッスンは約5～10分かかります。各レッスンはコンパクトでインタラクティブなので、着実に進歩し、ウェブとアプリ全体で正確に前回の場所から再開できます。
このDigital Marketing Academyレッスンでコードを書いて実行できますか？
はい。すべてのDigital Marketing Academyレッスンに組み込みコードエディタが含まれているため、ブラウザでリアルコードを書いて実行し、即座のAIフィードバックを取得できます。ローカル設定は不要です。
このコースのすべてのレッスン
- データウェアハウスが必要な理由
- ETLとコネクタ
- マーケティングデータのモデリング
- 施策につながるダッシュボード