Promotional lift,
measured. Not guessed.

Move 02

Build the SKU spine across nine storefronts.

Identity resolution, but for inventory. A canonical SKU spine that survived storefront-specific punctuation, banner-suffixed handles, and three legacy product taxonomies. Fivetran landed nine Shopify schemas, three ad platforms, and the POS SFTP drop in bronze load-as-is. Silver was where the spine got resolved — a deterministic crosswalk on UPC plus a fuzzy fallback for the long tail of POS items missing UPCs. Every row downstream of silver keys to one canonical SKU.

Move 03

Build the lift mart. SKU × banner × week, against a real baseline.

One row per acquisition cohort isn't the grain retail needs. Retail needs lift, and lift needs a baseline. We modeled an eight-week trailing baseline per SKU × banner (promo weeks excluded from the baseline window), then compared in-market promo weeks against it. The mart attributed paid and co-op spend onto the same grain via the campaign-naming convention we finally got the agencies to enforce. This is the model that ended the argument:

-- models/marts/mart_promotional_lift.sql
-- One row per (promo_id, sku_id, banner, fiscal_week).
-- Reads net revenue against an 8-week trailing baseline
-- (promo weeks excluded), attributes paid + co-op spend,
-- returns lift_ratio per the signed metric contract.

with sales as (
    select
        sku_id,
        banner,
        fiscal_week,
        sum(net_revenue_ex_tax)              as net_revenue,
        sum(units_sold)                      as units_sold
    from {{ ref('silver_sales_unified') }}    -- POS + ecom on one SKU spine
    where fiscal_week >= date_sub(current_date(), interval 18 month)
    group by 1, 2, 3
),
calendar as (
    select
        promo_id,
        promo_name,
        vehicle,
        banner,
        sku_id,
        fiscal_week,
        is_in_market
    from {{ ref('dim_promo_calendar') }}      -- one row per promo-touch
),
spend as (
    select
        promo_id,
        sku_id,
        banner,
        fiscal_week,
        sum(paid_spend_usd)                  as paid_spend,
        sum(coop_spend_usd)                  as coop_spend
    from {{ ref('silver_promo_spend') }}      -- tagged by campaign convention
    group by 1, 2, 3, 4
),
baseline as (
    -- 8-week trailing baseline per sku x banner,
    -- promo weeks excluded so promo lift can't inflate its own baseline.
    select
        s.sku_id,
        s.banner,
        s.fiscal_week,
        avg(case when c.is_in_market then null else s.net_revenue end)
            over (
                partition by s.sku_id, s.banner
                order by s.fiscal_week
                rows between 8 preceding and 1 preceding
            )                                as baseline_revenue
    from sales s
    left join calendar c
           on c.sku_id      = s.sku_id
          and c.banner      = s.banner
          and c.fiscal_week = s.fiscal_week
)
select
    c.promo_id,
    c.promo_name,
    c.vehicle,
    s.banner,
    s.sku_id,
    s.fiscal_week,
    s.net_revenue,
    s.units_sold,
    b.baseline_revenue,
    s.net_revenue - b.baseline_revenue                as gross_lift,
    p.paid_spend + p.coop_spend                       as total_spend,
    safe_divide(
        s.net_revenue - b.baseline_revenue,
        nullif(p.paid_spend + p.coop_spend, 0)
    )                                                 as lift_ratio,
    safe_divide(s.net_revenue, b.baseline_revenue)    as index_vs_baseline
from sales s
join calendar c
       on c.sku_id      = s.sku_id
      and c.banner      = s.banner
      and c.fiscal_week = s.fiscal_week
      and c.is_in_market = true
left join spend p
       on p.promo_id    = c.promo_id
      and p.sku_id      = s.sku_id
      and p.banner      = s.banner
      and p.fiscal_week = s.fiscal_week
left join baseline b
       on b.sku_id      = s.sku_id
      and b.banner      = s.banner
      and b.fiscal_week = s.fiscal_week

Move 04

Wire one Looker dashboard against the mart. Kill the quarterly deck.

Five views, all sourced from mart_promotional_lift: promo-level lift, banner roll-up, SKU outliers, week-over-week deltas, and an open-questions panel where the merch team flagged anomalies for the analyst to investigate before Tuesday. The quarterly status deck became a weekly attributed-lift review. Promotions that didn't earn their lift inside two consecutive cycles got cut by the merch director in the meeting. Six did, in the first quarter.

Move 05

Hand it to merchandising. Document the contract.

Every dbt model carries a YAML description, a freshness test, and a row-count assertion. Every metric in the dashboard names an owner. The handover packet is a forty-page Notion any new analyst can read on day one, with the metric contract pinned to the front. POS by 04:00 ET Monday, Shopify by 06:00, ad platforms by 07:00, Tuesday meeting at 10:00. We were out by week eleven. The merch team owns the model.