ビジネス/PM — クーポン施策 KPI ツリー設計 × OKR 接続 × ダッシュボード戦略（6W3H）

概要

問題

ECサイトの販促チームから「クーポン施策の費用対効果が見えない」というオーダーが来た。現在、月次で手動集計したExcelレポートを出しているが、数値が各部署バラバラで信頼性に欠ける。あなたはMOpsエンジニア兼PM補佐として、クーポン施策の KPI 体系を再設計し、ダッシュボード化戦略と OKR への接続方法を提案せよ。

前提条件

• 月間ユニークユーザー: 約80万人
• クーポン月間発行数: 約50万枚 / 使用率: 約22%（業界平均 25〜30%）
• 現在の割引コスト: 月額約1,200万円
• EC売上のノースタースター指標: 月次 GMV
• BigQuery + dbt + Looker Studio 環境が整備済み

現状分析 (Before) — 4つの構造的問題

📋# ❌ 問題1: データが分散・バラバラ
割引率  → スプレッドシートA（マーケティング部）
発行数  → スプレッドシートB（システム部）
使用数  → スプレッドシートC（CRM部）
売上   → スプレッドシートD（EC部）

# ❌ 問題2: 判断基準が存在しない
「クーポン施策が効いているかどうか」
→ 担当者の感覚判断のみ
→ ROI の定義すらなし

# ❌ 問題3: クーポンコードの使い回し
coupon_code: "SUMMER10"
  → 夏季キャンペーンで使用
  → 翌月の離脱防止施策でも使用
  → どちらの効果か不明（Attribution汚染）

# ❌ 問題4: 月次レポートのタイムラグ
施策実施 → 月末集計 → 翌月5日にレポート
→ 効果がわかる頃には次施策が走っている
→ PDCAサイクルが月1回しか回らない

📋# ✅ 解決1: 単一の真実のソース（SSOT）
BigQuery: raw.orders + raw.coupons
  → dbt: stg_ → int_ → mart_coupon_kpi_daily
  → 全部署が同じ数値を参照

# ✅ 解決2: KPI ツリーで判断基準を明確化
クーポン ROI = 追加GMV / 割引コスト
  → 1.8x（現状） → 2.5x（目標）

# ✅ 解決3: キャンペーン ID で完全分離
coupon_code に campaign_id を紐付け
  → 同一コードでも campaign 別に効果測定
  → Attribution が明確

# ✅ 解決4: 日次自動レポート
dbt run → mart_coupon_kpi_daily 更新（毎朝6時）
  → Looker Studio 自動更新（9時配信）
  → Slack Webhook で日次サマリ通知
  → PDCAサイクルが翌日に回る

ヒント（段階的開示）

-- mart_coupon_kpi_daily.sql の骨格
SELECT
    order_date,
    coupon_code,
    campaign_name,
    SUM(net_sales)                                     AS incremental_gmv,
    SUM(discount_amount)                               AS discount_cost,
    SAFE_DIVIDE(SUM(net_sales), SUM(discount_amount))  AS coupon_roi,  -- KR1
    COUNT(DISTINCT order_id)                           AS order_count,
    COUNT(DISTINCT user_id)
        / MAX(daily_total_users)                       AS usage_rate   -- KR2
FROM {{ ref('int_coupon_orders_joined') }}
WHERE order_date >= DATE_SUB(CURRENT_DATE('Asia/Tokyo'), INTERVAL 30 DAY)
GROUP BY order_date, coupon_code, campaign_name

問題点分析

KPI ツリー図 — GMV 逆算型

模範解答

1. OKR（Q2 2026）

2. ダッシュボード設計方針（6W3H）

3. dbt モデル構成

📋-- models/marts/mart_coupon_kpi_daily.sql
{{ config(
    materialized='incremental',
    incremental_strategy='insert_overwrite',
    partition_by={'field': 'order_date', 'data_type': 'date', 'granularity': 'day'},
    cluster_by=['campaign_name', 'coupon_code']
) }}

WITH base AS (
    SELECT
        order_date,
        coupon_code,
        campaign_name,
        order_id,
        user_id,
        net_sales,
        gross_sales,
        gross_sales - net_sales AS discount_amount,
        -- バスケットサイズ: 注文内アイテム合計
        net_sales / NULLIF(COUNT(*) OVER (PARTITION BY order_id), 0)
                                AS basket_size
    FROM {{ ref('int_coupon_orders_joined') }}
    {% if is_incremental() %}
    WHERE order_date >= DATE_SUB(CURRENT_DATE('Asia/Tokyo'), INTERVAL 3 DAY)
    {% endif %}
)

SELECT
    order_date,
    coupon_code,
    campaign_name,

    -- ──── 効果指標（KR1: クーポン ROI） ────
    SUM(net_sales)                                     AS incremental_gmv,
    SUM(discount_amount)                               AS discount_cost,
    -- ROI = 追加GMV / 割引コスト。2.5x 以上が目標
    SAFE_DIVIDE(SUM(net_sales), SUM(discount_amount))  AS coupon_roi,

    -- ──── 規模指標（KR2: 使用率） ────
    COUNT(DISTINCT order_id)                           AS order_count,
    COUNT(DISTINCT user_id)                            AS unique_users,
    AVG(basket_size)                                   AS avg_basket_size,

    -- ──── コスト管理指標（上限 3%） ────
    SAFE_DIVIDE(
        SUM(discount_amount),
        SUM(gross_sales)
    )                                                  AS discount_rate

FROM base
GROUP BY order_date, coupon_code, campaign_name

4. dbt モデル依存関係

📋-- dbt モデルの依存関係（DAG）
source(raw.orders)           → stg_orders         ─┐
source(raw.order_items)      → stg_order_items     ─┤ → int_coupon_orders_joined
source(raw.coupons)          ─────────────────────  ─┘
                                                       │
                                            ┌──────────┴──────────┐
                                            ↓                     ↓
                                  mart_coupon_kpi_daily   mart_coupon_kpi_last30d
                                  （全期間・増分更新）      （30日固定窓・数MB）
                                                                   │
                                                                   ↓
                                                           Looker Studio
                                                           （スキャン 50MB 以下）

ポイント解説

実務への応用

• MOps文脈での自動化: クーポン発行システムのパラメータ（割引率・対象SKU・上限枚数）を変更するたびに、mart_coupon_kpi_daily が翌日朝に自動更新される仕組みを作ると、施策→効果の PDCAサイクルが翌日に完結する
• BigQueryコスト連携: 前日の問題（2026-05-20 C）で設計した mart_coupon_sales_last30d（30日固定窓・数MB）を Looker Studio に接続することで、1日100回更新されてもスキャンコスト $31/月 に抑えられる
• DataDog連携: coupon_roi が 1.5x を下回ったら Slack アラートを自動送信するモニターを設定。予算超過・効果不足の早期検知が可能
• 経営報告の自動化: Looker Studio のスケジュール配信機能で月次レポートを PDF で自動生成 → Gmail 配信。Excel 手作業ゼロへ。毎月 3〜5 時間の作業コスト削減
• Argo Workflows でのバッチ管理: dbt run を Argo Workflows で毎朝 6:00 JST に実行し、完了通知を Slack Webhook で送信。失敗時は PagerDuty へエスカレーション

今日のまとめ

自己評価

自分の回答

気づき・メモ