こんにちは、one_inaです。 今回は、架空ではありますが、実際にありそうなユースケースを想定しながら、データマート構築について触れたいと思います。
※ここからはあくまでも架空のユースケースですので、架空のストーリーとしてお楽しみください。
- 1. 突然降ってきた「特命」
- 2. なぜ「生データ」だけではインサイトが出ないのか？
- 3. 「点」のデータを「面（セグメント）」に変える必要性
- 4. 実践ステップ：まずは「ユーザー軸」で攻めることを決める
- 5. 実践ステップ：DWHとデータマートのテーブルの作成
- 6.最終的に提出したレポートイメージ
- 7.まとめ
1. 突然降ってきた「特命」
舞台は、あるペット用品ECを手掛ける企業。
新規獲得コスト(CPA)の高騰に頭を抱える社長から、
企画本部長に特命が下ります。
「 これからは既存顧客のLTVを伸ばす。
その為に、クロスセルを最大化する商品を開発せよ」
会議の後、担当者であるあなたに、本部長は詰め寄ります。
「分析用のデータがあるはずだ。SQLをパパっと叩いて、
商品開発につながるインサイトを出してくれ」
- 数百万行の「注文履歴」
- 膨大な「サイト閲覧履歴」
- 整然とならんだ「製品マスター」
- 住所が書かれた「配送先情報」など
たしかに手元には、蓄積された大量のデータはあります。
しかし、あなたは画面を前にして立ち尽くします。
「注文データはある。
でも……このデータ、クロスセルのための『意味』が全く付いていない……」
2. なぜ「生データ」だけではインサイトが出ないのか？
あなたは悩みます。
クロスセルを成功させるためのアプローチには、大きく分けて2つの「軸」があるからです。
① 商品ベースの軸（Item-based）
実現したいこと：
「ドッグフードAを買った人は、おやつBをよく買っている」という商品同士の相関を見つける。
課題:
生の注文履歴をJOINしただけでは、
「たまに一緒に買われるもの」と
「必然的に買われるもの」の区別がつかない。
統計的に意味のある「合わせ買いの法則」を導き出すための計算層が、
今のDWHには存在しない。
② ユーザーベースの軸（User-based）
実現したいこと：
「大型犬を飼っている人は、半年後にこういう悩みを抱えるから、
この商品が必要になる」という顧客セグメントを特定する。
課題:
注文履歴には「どの商品を買ったか」はあるが、
「その人が何者か（大型犬オーナーか、多頭飼いか）」
という属性情報がどこにもない。
配送先住所から「一戸建てかマンションか」を推測し、
製品データから「大型犬用か」を紐付け、
それらを一人一人のユーザーに集計し直さなければ、ターゲット（セグメント）すら作れない。
3. 「点」のデータを「面（セグメント）」に変える必要性
今の状態は、
「畑に野菜（データ）は埋まっているが、キッチンも調理器具（基盤）もないので、すぐには料理（インサイト）が出せない」状態です。
ここで必要になるのが、単なる保管庫ではない
「真のDWH」と「データマート」の構築です。
- DWH（データウェアハウス）の役割:
注文履歴、閲覧履歴、配送先情報をすべて「ユーザーID」で串刺しにする。
バラバラの「点」を繋ぎ合わせる作業。 - データマート（意味付けの層）の役割:
繋ぎ合わせたデータに、
「この人は『シニア犬・大型犬』セグメントである」
というラベルを貼る。
これがあってはじめて、
「シニア犬セグメントの人は、フードAを買った3ヶ月後に、高確率で『関節ケア』の単語をサイトで検索している」
といった、商品開発に直結するインサイトが見えてくるのです。
では、実際に作業をイメージしてみます。
4. 実践ステップ：まずは「ユーザー軸」で攻めることを決める
クロスセルの分析には「商品同士の相関」もありますが、
今回はあえて「ユーザー軸（User-based）」を主軸に据えることにしました。
理由はシンプルです。
経営層に「Aを買った人はBも買います」とデータを見せても、
「ふーん、それで？」で終わるリスクがあります。
一方で、「今、うちのサイトには『大型犬の足腰の悩み』を抱えた顧客が3万人いて、彼らが他社へ流出しています」という顧客像ベースの報告は、
商品開発の強い動機付け（恣意的な納得感）を生み出しやすいからです。
少し乱暴ですが、「アクションを起こすためのインサイト」を提案する為には、
分析する人の意思が少なからず必要です。
では、実際に生データから「顧客セグメント」を作り出すまでの具体的な作業工程を見ていきましょう。
ステップ1：バラバラのIDを「一人の顧客」に統合する(名寄せ)
まず着手したのは、バラバラのテーブルを「ユーザーID」で繋ぐことです。
しかし、単にJOINすればいいわけではありません。
- 作業内容： 注文履歴の
user_idと、サイト訪問ログのcookie_id、配送先情報のaddressを紐付けます。 - 苦労ポイント： 「ゲスト購入したユーザー」や「複数のメールアドレスを持つユーザー」を、 配送先住所や電話番号のクレンジング（正規化）によって同一人物として特定する 「アイデンティティ・レゾリューション（身元統合）」のロジックをSQLで組みますが、データの欠損や不整合などもあるのが当然で なかなか思い通りにはならないことが一般的です。
ステップ2：購入履歴から「どんなペットか」を推論・ラベル化する
次に、製品マスターの情報を使い、ユーザーに「ラベル」を貼っていきます。
- 作業内容： ユーザーAが過去に買った商品の属性（例：
size: Large、category: Puppy）をカウントし、 そのユーザーを [大型犬オーナー] 且つ [子犬期] と定義します。 - ロジックの工夫： 「一度だけプレゼント用に小型犬用を買った」というノイズを除去するため、直近3回の購入履歴や、最も購入金額が多いカテゴリを優先するような重み付けの集計を行います。
ステップ3：「月齢」を算出し、未来の需要を予測可能にする
ここが今回の「クロスセル」を考える際のデータマート構築のキモです。犬や猫は成長します。
- 作業内容： 「最初に『子犬用フード』を購入した日」を起算日として、現在の推定月齢を算出するカラムをマートに追加します。
- インサイトの源泉： これにより、「生データ」には存在しない「そろそろ成犬用に切り替えるタイミングのユーザー」という動的なセグメントが抽出可能になります。
ステップ4：閲覧ログ（行動データ）とのクロス分析
最後に、作成した「ユーザー属性マート」に、Webサイトの閲覧ログをぶつけます。
- 作業内容： 「大型犬・シニア」と定義されたセグメントのユーザーが、直近1ヶ月でサイト内のどのコンテンツ（あるいは検索ワード）に反応しているかを抽出します。
5. 実践ステップ：DWHとデータマートのテーブルの作成
元になった注文履歴データや閲覧履歴データ、製品マスターなどがあります。 それらをETL（Extract, Transform, Load）して、DWHとデータマートを構築し、 以下のようなテーブル群を作りました。
テーブル一覧
| No. | レイヤー | テーブル名 | 役割 | 本レポートでの用途 |
|---|---|---|---|---|
| 1 | DWH | dim_user | 名寄せ済みユーザーマスター（user_identity の代表キー） | 全マートの基点 |
| 2 | DWH | dim_user_identity | user_key と cookie_id / email_hash 等のID統合ブリッジ | fact_web_events と user_key の紐付け |
| 3 | DWH | dim_product | 製品マスター（concern_tag・life_stage・target_size 整備済） | ペット属性推論・悩み軸別 自社品数カウント |
| 4 | DWH | dim_address | 正規化住所＋住居タイプ推定（戸建/マンション） | housing_type 付与 |
| 5 | DWH | fact_orders | 注文ファクト（ライン粒度に統合：1行=1注文明細） | LTV・初回購入日・ペット属性推論・購入UU |
| 6 | DWH | fact_web_events | Web閲覧・検索イベント | 検索UU・検索ワード抽出 |
| 7 | Mart | mart_user_segment | 多軸セグメント（種・サイズ・ライフステージ・住居） | FACT1：セグメント定義 |
| 8 | Mart | mart_user_rfm_ltv | RFM / LTV / 注文頻度 | FACT1：LTV算出、FACT3：想定金額算定 |
| 9 | Mart | mart_whitespace | 悩み軸別 需給ギャップ（検索UU vs 自社品数） | FACT2：需給ギャップ |
| 10 | Mart | mart_lifestage_transition | ライフステージ遷移タイミング候補リスト | FACT3：遷移人数・推奨商品 |
例) dim_user テーブル
| No. | カラム名 | データ型 | キー区分 | 説明 |
|---|---|---|---|---|
| 1 | user_key | STRING | PK | 統合ユーザーID（名寄せ後の代表キー） |
| 2 | email_hash | STRING | メールアドレスのハッシュ | |
| 3 | phone_hash | STRING | 電話番号のハッシュ | |
| 4 | first_order_at | TIMESTAMP | 初回注文日時 | |
| 5 | last_order_at | TIMESTAMP | 最終注文日時 | |
| 6 | is_member | BOOLEAN | 会員登録有無 | |
| 7 | created_at | TIMESTAMP | レコード作成日時 | |
| 8 | updated_at | TIMESTAMP | レコード更新日時 |
例）mart_user_segment テーブル
| No. | カラム名 | データ型 | キー区分 | 説明 |
|---|---|---|---|---|
| 1 | user_key | STRING | PK | → dim_user.user_key |
| 2 | segment_id | STRING | セグメントID（例：DOG_LRG_SNR_HOUSE） | |
| 3 | pet_type | STRING | 推論：dog / cat / multi | |
| 4 | size_class | STRING | 推論：small / medium / large | |
| 5 | lifestage | STRING | 現ライフステージ（puppy / adult / senior） | |
| 6 | housing_type | STRING | 住居タイプ（戸建 / マンション / 不明） | |
| 7 | multi_pet_flag | BOOLEAN | 多頭飼いフラグ | |
| 8 | estimated_age_month | INT | 推定月齢（旧 mart_user_lifestage を内包） | |
| 9 | lifestage_origin_at | DATE | 起算日（初回パピー用フード購入日 等） | |
| 10 | confidence_score | NUMERIC | セグメント推論の信頼度（0-1） | |
| 11 | assigned_at | TIMESTAMP | セグメント付与日時 |
例）mart_whitespace テーブル
| No. | カラム名 | データ型 | キー区分 | 説明 |
|---|---|---|---|---|
| 1 | segment_id | STRING | PK | セグメントID（全体集計時は 'ALL'） |
| 2 | concern_tag | STRING | PK | 悩み軸タグ（関節/皮膚/認知症 等） |
| 3 | search_uu_30d | INT | 30日 検索ユニークユーザー数 | |
| 4 | own_product_cnt | INT | 自社該当商品数 | |
| 5 | purchase_uu_30d | INT | 30日 該当タグ商品の購入UU | |
| 6 | gap_score | NUMERIC | ギャップスコア（需要−供給を 0〜10 で正規化） | |
| 7 | computed_at | TIMESTAMP | 計算実行日時 |
例）mart_user_rfm_ltv テーブル
| No. | カラム名 | データ型 | キー区分 | 説明 |
|---|---|---|---|---|
| 1 | user_key | STRING | PK | → dim_user.user_key |
| 2 | recency_days | INT | 最終購入からの日数 | |
| 3 | frequency_12m | INT | 直近12ヶ月の購入回数 | |
| 4 | monetary_12m | NUMERIC | 直近12ヶ月の購入金額 | |
| 5 | ltv_total | NUMERIC | 累計購入金額（LTV） | |
| 6 | rfm_rank | STRING | RFMランク | |
| 7 | computed_at | TIMESTAMP | 計算実行日時 |
6.最終的に提出したレポートイメージ
では、先ほどの処理で作成したデータマートで最終的にどんなレポートが作れるのかイメージです。
いかがでしょうか？ この情報があれば、提案の体裁としては良さそうです。
なお、それぞれの表を出すSQLは以下のようなイメージです。
FACT 1：高LTVセグメントの特定
使うテーブル: mart_user_segment + mart_user_rfm_ltv
-- セグメント別 顧客数・平均LTV・年間注文回数
SELECT
CASE
-- 多頭飼いは他属性より優先（重複防止）
WHEN s.multi_pet_flag = TRUE AND s.pet_type = 'dog'
THEN '多頭飼い(犬複数)'
WHEN s.pet_type='dog' AND s.size_class='large' AND s.lifestage='senior' AND s.housing_type='戸建'
THEN '大型犬×シニア×戸建'
WHEN s.pet_type='dog' AND s.size_class='large' AND s.lifestage='adult' AND s.housing_type='戸建'
THEN '大型犬×アダルト×戸建'
WHEN s.pet_type='dog' AND s.size_class='small' AND s.lifestage='senior' AND s.housing_type='マンション'
THEN '小型犬×シニア×マンション'
WHEN s.pet_type='dog' AND s.size_class='small' AND s.lifestage='adult' AND s.housing_type='マンション'
THEN '小型犬×アダルト×マンション'
WHEN s.pet_type='cat' AND s.lifestage='senior'
THEN '猫×シニア'
ELSE 'その他'
END AS segment_label,
COUNT(DISTINCT s.user_key) AS customer_cnt, -- 顧客数(人)
ROUND(AVG(r.ltv_total)) AS avg_ltv_yen, -- 平均LTV(円)
ROUND(AVG(r.frequency_12m), 1) AS avg_orders_per_year -- 年間注文回数
FROM mart_user_segment s
LEFT JOIN mart_user_rfm_ltv r
ON r.user_key = s.user_key
GROUP BY segment_label
HAVING segment_label <> 'その他'
ORDER BY avg_ltv_yen DESC;
FACT 2：悩み軸別の需給ギャップ
使うテーブル: mart_whitespace（segment_id='ALL' の全体集計行を利用）
-- 悩み軸別 月間検索UU・自社該当商品数・ギャップスコア（全セグメント横断）
SELECT
concern_tag AS concern, -- 悩み軸
search_uu_30d AS search_uu_monthly, -- 月間検索UU(人)
own_product_cnt AS own_product_cnt, -- 自社該当商品数
gap_score AS gap_score -- ギャップスコア
FROM mart_whitespace
WHERE segment_id = 'ALL' -- 全体集計行
AND concern_tag IN (
'関節サプリ','体重管理','皮膚ケア','消化器ケア','デンタルケア','認知症ケア'
)
ORDER BY gap_score DESC;
FACT 3：今後3ヶ月のライフステージ遷移
使うテーブル: mart_lifestage_transition + mart_user_rfm_ltv
-- 遷移パターン別 対象顧客数・想定クロスセル金額・推奨商品タグ
WITH base AS (
SELECT
CASE
WHEN t.current_lifestage='puppy' AND t.next_lifestage='adult' THEN 'パピー → アダルト'
WHEN t.current_lifestage='adult' AND t.next_lifestage='senior' THEN 'アダルト → シニア'
WHEN t.current_lifestage='senior' AND t.next_lifestage='care' THEN 'シニア → 介護期'
ELSE 'その他'
END AS transition_pattern,
t.user_key,
t.recommended_concern_tag,
r.monetary_12m
FROM mart_lifestage_transition t
LEFT JOIN mart_user_rfm_ltv r
ON r.user_key = t.user_key
WHERE t.expected_transition_at
BETWEEN CURRENT_DATE()
AND DATE_ADD(CURRENT_DATE(), INTERVAL 3 MONTH)
)
SELECT
transition_pattern,
COUNT(DISTINCT user_key) AS target_user_cnt,
-- 想定クロスセル金額 = 月額相当(年購買額/12) × 3ヶ月 × 想定転換率30% を千円表記
ROUND(SUM(monetary_12m) * (3.0/12.0) * 0.30 / 1000) AS expected_amt_kyen,
STRING_AGG(DISTINCT recommended_concern_tag, ' / ' ORDER BY recommended_concern_tag)
AS recommended_concern_tags
FROM base
WHERE transition_pattern <> 'その他'
GROUP BY transition_pattern
ORDER BY
CASE transition_pattern
WHEN 'パピー → アダルト' THEN 1
WHEN 'アダルト → シニア' THEN 2
WHEN 'シニア → 介護期' THEN 3
END;
▎ 想定金額は 年購買額 × 3/12 × 転換率30%
上記の情報から 最終的には以下の提案が出せるかと思います。
提案
| 優先 | 提案 | 対象 | 根拠 / メモ |
|---|---|---|---|
| ① 最優先 | 大型犬向け 関節サプリの自社開発（PB） | 大型犬×シニア×戸建（32,500人）＋アダルト→シニア遷移者（8,900人） | FACT2 で需給ギャップ最大／FACT1 で対象LTVが平均比 約1.7倍 |
| ② 次点 | シニア移行アラート × 同梱クロスセル の仕組み化 | （推定月齢ベース） | 推定月齢ベースで自動レコメンド配信（mart_lifestage_transition を活用） |
| ③ 追加 | 多頭飼い向け 定期便SKU | 多頭飼いセグメント | LTV 71,000円 / 注文頻度7.8回の最高LTVセグメントを取り逃さない |
7.まとめ
いかがでしたでしょうか？
今回は「クロスセルで売上を伸ばす商品開発」をテーマに、 生データ（注文・閲覧・製品・住所など）だけではインサイトが出にくい理由と、 そこから提案に落とし込むためのDWH／データマート構築の考え方を、架空ユースケースで整理しました。
ポイントは以下です。
- まずは ユーザーIDでの統合（名寄せ／紐付け） を行い、バラバラの点データを繋げる（DWHの役割）
- その上で セグメントラベル・ライフステージ・住居タイプ等の推論 を行い、「面（セグメント）」として扱える状態にする（データマートの役割）
- 行動ログ（検索・閲覧）まで重ねることで、需給ギャップ や 遷移タイミング など、商品開発や施策に直結する論点が見える
最終的には、これらのマートを使って「高LTVセグメント特定」「悩み軸の需給ギャップ」「ライフステージ遷移によるクロスセル機会」を定量で示し、 優先順位付きの提案に繋げられるイメージを提示しました。 もちろん今回の例ではない他の提案方法もあるとは思いますし、 「こんな場合はどうするの？」もあると思います。 ぜひそう言ったユースケースがあれば、ご連絡いただければと思います。 最後まで読んでいただきありがとうございました。
この記事を書いた人
