CASE STUDY // PLAYBOOK V1.10.0 & CM1→CM3 PROFIT WATERFALLEnterprise DTC, Omnichannel Retail & 28-Scenario Certified Engine

Reconciling the Gap Between Inflated Ad-Platform ROAS and True Net Contribution Margin (CM3)

Executive Summary: Across 20+ years leading analytics inside high-velocity D2C subscription, consumer wellness, two-sided marketplace, and national omnichannel retail product environments, we kept encountering the same boardroom conflict: Marketing dashboards reported 4.0x+ paid media ROAS while Finance saw shrinking net cash. We engineered the v1.10.0 GA4 Conversion Journey Attribution Playbook and M1–M4 Marketing Analytics Skill Suite to unify 5-stage click-to-session telemetry debugging, 6-model precision multi-touch attribution, geo-holdout incrementality, and order-line CM1 → CM2 → CM3 margin waterfalls.

+20.4%
Marketing ROI Lift

Driven by shifting spend out of negative-CM3 SKUs and non-incremental retargeting.

~70%
Retargeting Mirage Exposed

Share of paid retargeting & promo conversions proven non-incremental in matched geo-holdout testing.

28 / 28
Canonical Parity Tests

Automated SQL/Python suite certifying 6 attribution models and edge-case invariants.

6 Models
One Partitioned Fact Table

First-Click, Last-Click, Linear, U-Shaped, W-Shaped & Time-Decay in conversion_journey.

OPERATIONAL BRIDGE // THREE HIGH-LEVERAGE PROFIT & ATTRIBUTION DISCOVERIES

Where Do High-Growth Ecommerce & Subscription Brands Actually Leak Margin?

DISCOVERY 01 // UNIT ECONOMICS

The “Hero SKU” Negative-CM3 Inversion

First implemented at enterprise scale inside a Fortune 50 national retail and pharmacy environment (connecting true unit COGS, fulfillment expenses, and channel marketing spend to manage category contribution margin per customer) and codified in Module 3: a campaign boasting 4.4x gross ROAS frequently loses net cash when 20–25% promo stacking collides with bulky 3PL shipping fees and high SKU return rates.

DISCOVERY 02 // SESSION STITCHING

The 4-Minute Welcome Popup Cannibalization Trap

A new visitor clicks a Paid Social ad, triggers a “10% Off” Klaviyo/SMS popup within 60 seconds, clicks the email coupon link inside 15 minutes, and buys. Default last-click analytics assigns 100% of credit to Email, making Paid Social nCAC look 25–35% worse than reality. Our conversion journey SQL stitches intra-window email clicks back to the originating paid acquisition touchpoint.

DISCOVERY 03 // CAUSAL INCREMENTALITY

Geo-Holdout Calibration of Retargeting & MMM

Multi-touch attribution is descriptive, not causal. In a matched-market geo-holdout and go-dark incrementality test for a DTC brand, we proved that ~70% of conversions credited to paid retargeting and heavy promo touches occurred at the exact same rate in holdout markets. Presenting side-by-side cohort curves unlocked 30% of trapped budget for net-new acquisition.

TECHNICAL CRUCIBLE // PLAYBOOK V1.10.0 SQL ARCHITECTURE

How Is ga4_marts.conversion_journey Engineered for Mathematical Precision?

Instead of maintaining six separate attribution tables, our canonical model stores one table partitioned by conversion_date at the grain of one conversion instance × one eligible cookied session touchpoint, certified against 28 automated test scenarios.

sql- 07_conversion_journey_attribution.sql // Same-Session Preservation & Lookback Hard Override
-- Playbook v1.10.0: Same-Session Multi-Conversion Preservation (ATTR-P0-01)
-- & Calendar-Day Lookback Hard Override Over Next-Session Boundary (ATTR-P1-02)
eligible_touchpoint_sessions AS (
  SELECT
    c.source_dataset_id,
    c.conversion_key,
    c.user_pseudo_id,
    c.event_name,
    c.conversion_ts,
    c.conversion_date,
    c.conversion_revenue,
    c.conversion_value_type, -- 'ACTUAL_REVENUE' vs 'ESTIMATED_VALUE'
    s.session_key,
    s.session_id,
    s.session_start_ts,
    s.marketing_channel,
    ROW_NUMBER() OVER (
      PARTITION BY c.source_dataset_id, c.conversion_key
      ORDER BY s.session_start_ts ASC, s.session_id ASC
    ) AS touchpoint_ordinal
  FROM canonical_conversions_with_lag c
  INNER JOIN `your_project.ga4_marts.fct_ga4_sessions` s
    ON c.source_dataset_id = s.source_dataset_id
   AND c.user_pseudo_id = s.user_pseudo_id
  WHERE s.traffic_type = 'COOKIED_SESSION'
    AND s.session_start_ts <= c.conversion_ts
    AND s.session_id <= c.converting_session_id
    AND (
      -- ATTR-P0-01: NEVER drop the converting session when 2+ orders occur in the same session
      s.session_key = c.converting_session_key
      OR (
        -- ATTR-P1-02: Lookback ceiling overrides next-session boundary
        DATE_DIFF(c.conversion_date, s.session_date_est, DAY) BETWEEN 0 AND v_lookback_days
        AND (c.prev_converting_session_id IS NULL OR s.session_id > c.prev_converting_session_id)
      )
    )
)
Excerpts from Playbook v1.10.0 enforcing ATTR-P0-01 (same-session multi-conversion preservation) and ATTR-P1-02 (calendar-day lookback hard override).

Canonical Purchase Isolation & Cookieless Reconciliation (ATTR-01 to ATTR-04)

Purchase attribution reads exclusively from canonical deduplicated orders (ga4_marts.fct_ga4_transactions) rather than re-scanning raw events_* tables. Purchases lacking a cookied session are never assigned fake synthetic sessions; they reconcile cleanly inside vw_conversion_journey_reconciliation so canonical_purchases = attributed_cookied + unattributed_cookieless.

3-Route Production Deployment (With or Without dbt)

Not every analytics team runs dbt. Playbook v1.10.0 compiles identically across Route A (Bounded BigQuery Console SQL), Route B (Native BigQuery Stored Procedures with zero dbt required), and Route C (dbt Core / Cloud incremental marts).

RUN THIS ENGINE AGAINST YOUR STACK IN 5 DAYS

Book the $5,000–$7,500 5-Day Attribution & Margin Diagnostic

100% of your diagnostic fee is creditable toward a full production deployment or fractional retainer within 30 days.