Marketing Mix Modeling (MMM)¶

┌─────────────────┐    DAG handoff    ┌──────────────────────┐
│  mmm_analyst    │──────────────────▶│  mmm_causal_reviewer │
│                 │   (markdown table │                      │
│  spine→corr→    │    of nodes+edges)│  chronological /     │
│  baseline→      │                   │  non-inclusion /     │
│  adstock→fit    │                   │  identifiability     │
└─────────────────┘                   └──────────┬───────────┘
                                                 │ VERDICT
                      ┌──────────────────────────┴──────────┐
                      │                                     │
                      ▼ PASS                                ▼ BLOCK
              ┌───────────────────┐              ┌──────────────────────┐
              │  generate_charts  │              │  Apply prescribed    │
              │  (5 required)     │              │  fix; re-submit DAG  │
              │  generate_report  │              └──────────────────────┘
              └─────────┬─────────┘
                        │ optional
                        ▼
                ┌──────────────────┐
                │  mmm_simulator   │  (marginal ROI + ±30% reallocation)
                └──────────────────┘

| Check | Guidebook ref | Verdict | Evidence |
|---|---|---|---|
| Chronological (cause before effect) | p.91 | pass / fail | cite edges; flag any running backwards in time |
| Non-inclusion (no overlapping variables) | p.92 | pass / fail | list variable pairs checked |
| Identifiability (no unresolved confounding) | p.93, 120–129 | pass / fail + recommendation | list confounded edges + fix |

VERDICT: PASS  |  BLOCK

-- Marginal ROI = d(KPI)/d(spend) = β · r · spend^(r-1)
SELECT
  media, beta, r, current_spend,
  beta * r * pow(current_spend, r - 1) AS marginal_roi,
  (beta * pow(current_spend, r)) / current_spend AS avg_roi
FROM fitted_curves
ORDER BY marginal_roi DESC;

-- Flag incentive variables that move together (>0.9 correlation)
SELECT
  corr(emissions_protocol_a, emissions_protocol_b) AS corr_ab,
  corr(emissions_protocol_a, validator_rewards)    AS corr_av,
  corr(emissions_protocol_b, validator_rewards)    AS corr_bv
FROM weekly_incentives
WHERE week >= today() - INTERVAL 2 YEAR;
-- Any |corr| > 0.9 → merge, drop, or segment (Guidebook p.38)

-- FIRST fill missing weeks with 0 emissions (sparse event data is common)
WITH spine AS (
  SELECT
    toStartOfWeek(toDate(week)) AS week,
    coalesce(sum(emissions), 0) AS emissions
  FROM weekly_incentives
  WHERE week >= today() - INTERVAL 2 YEAR
  GROUP BY week
  ORDER BY week WITH FILL STEP toIntervalWeek(1)
),
-- THEN build variable-length window arrays per row
windowed AS (
  SELECT
    week, emissions,
    arrayReverse(groupArray(emissions) OVER (
      ORDER BY week ROWS BETWEEN 8 PRECEDING AND CURRENT ROW
    )) AS emissions_win
  FROM spine
)
-- FINALLY apply geometric decay. IMPORTANT: use range(length(arr)), not range(9) —
-- the window is shorter than 9 at the start of the series, and arrayMap raises
-- SIZES_OF_ARRAYS_DONT_MATCH if the index array is longer.
SELECT
  week, emissions,
  arraySum(arrayMap((x, i) -> x * pow(0.5, i),
    emissions_win,
    range(length(emissions_win))
  )) AS emissions_adstock
FROM windowed
ORDER BY week;

-- KPI-when-spend-is-near-zero: median KPI during bottom-decile adstock weeks.
-- Prevents log(0) and prevents the multiplicative model from implying KPI→0
-- when emissions→0 (which is false for TVL — organic demand still exists).
WITH thresholds AS (
  SELECT quantile(0.1)(emissions_adstock) AS p10 FROM transformed_weekly
)
SELECT quantile(0.5)(tvl) AS baseline_tvl
FROM transformed_weekly, thresholds
WHERE emissions_adstock <= p10;

-- Estimate β, r in (KPI − baseline) = β · adstock^r via log-log regression.
-- ClickHouse simpleLinearRegression returns Tuple(k, b) where:
--   k = SLOPE  → r (diminishing-returns exponent)
--   b = INTERCEPT  → log(β), so β = exp(b)
-- There is NO "AS (a, b)" tuple-destructure syntax — alias the whole tuple
-- and access via .1 (slope) and .2 (intercept).
SELECT
  fit.1 AS r,
  exp(fit.2) AS beta
FROM (
  SELECT
    simpleLinearRegression(
      log(emissions_adstock),
      log(greatest(tvl - {baseline_tvl:Float64}, 1))
    ) AS fit
  FROM transformed_weekly
  WHERE emissions_adstock > 0
    AND tvl > {baseline_tvl:Float64}
);
-- r < 1 → diminishing returns (concave curve). r > 1 is a red flag.

-- simpleLinearRegression cannot fit Hill directly.
-- IMPORTANT: mean-scale both axes before gridding — K ∈ [0.05..1] is meaningless
-- against raw-unit adstock in the millions.
WITH
  scales AS (
    SELECT avg(emissions_adstock) AS s_ad, avg(tvl) AS s_kpi
    FROM transformed_weekly WHERE emissions_adstock > 0
  ),
  scaled AS (
    SELECT
      emissions_adstock / (SELECT s_ad FROM scales)  AS x,
      tvl / (SELECT s_kpi FROM scales)               AS y
    FROM transformed_weekly WHERE emissions_adstock > 0
  ),
  grid AS (
    SELECT arrayJoin(range(1, 21)) * 0.05 AS K,   -- K in [0.05..1.0]
           arrayJoin(range(1, 11)) * 0.5  AS S    -- S in [0.5..5.0]
  )
SELECT K, S,
       avg(abs(y - 1.0 / (1.0 + pow(x / K, -S)))) AS mae
FROM scaled CROSS JOIN grid
GROUP BY K, S
ORDER BY mae ASC LIMIT 1;
-- K and S returned are in scaled-unit space; multiply back by scales to interpret.

-- Per-media predicted incremental KPI per week → backs the stacked-area chart
SELECT
  week,
  beta_a * pow(emissions_a_adstock, r_a) AS contrib_a,
  beta_b * pow(emissions_b_adstock, r_b) AS contrib_b,
  tvl - (beta_a * pow(emissions_a_adstock, r_a)
       + beta_b * pow(emissions_b_adstock, r_b)) AS residual
FROM transformed_weekly;

1. get_agent_persona("mmm_analyst")
2. Run the SOP: spine → corr check → baseline → adstock → concave + Hill fit → decompose
3. Synthesize the DAG as a markdown table:
   | Node | Variable | Source |
   | T    | claims_adstock | … |
   | P    | topups_adstock | … |
   | Y    | kpi_swap_volume | … |
   Edges: T→Y, P→Y, (optional: T→intermediate→Y)
   Flags: any co-launched / confounded pairs
4. get_agent_persona("mmm_causal_reviewer") — pass the DAG table verbatim
5. On BLOCK: apply prescribed fix, re-submit
   On PASS: proceed
6. generate_charts (batch, 5 required specs)
7. generate_report
8. Optional: get_agent_persona("mmm_simulator")
   - Pass fitted (β, r, λ, current_spend, baseline_kpi) per media
   - Returns marginal-ROI bar + ±30%-bounded allocation pie

uv run pytest tests/test_mmm_agents.py tests/test_persona_sql_hygiene.py