FOR YOUR ANALYST

The formulas

Contribution per unit, by SKU

-- last 90 days; sku_costs is a landed-cost table kept by finance
WITH lines AS (
  SELECT l.sku,
         SUM(l.quantity)                                   AS units,
         SUM(l.quantity * l.price - l.discount)            AS net_revenue,
         SUM(l.quantity * (c.landed_cost + c.fulfillment_cost + c.packaging_cost)) AS unit_costs,
         SUM((o.shipping_cost - o.shipping_charged + o.payment_fee)
             * (l.quantity * l.price - l.discount)
             / NULLIF(o.subtotal, 0))                      AS allocated_order_costs
  FROM order_lines l
  JOIN orders o     ON o.id = l.order_id
  JOIN sku_costs c  ON c.sku = l.sku
  WHERE o.created_at >= CURRENT_DATE - INTERVAL '90 days'
  GROUP BY l.sku
)
SELECT sku, units,
       net_revenue / units                                          AS avg_price,
       (net_revenue - unit_costs - allocated_order_costs) / units   AS contribution_per_unit,
       (net_revenue - unit_costs - allocated_order_costs) / net_revenue AS contribution_margin
FROM lines
ORDER BY net_revenue DESC;

Order-level costs are shared across lines by revenue, an approximation; use it consistently. The margin column is m in the break-even formula.

Volume against break-even

-- weekly units per SKU, 12 weeks before and after the rise
-- :rise_date and the SKU list come from the plan
SELECT l.sku,
       DATE_TRUNC('week', o.created_at)          AS week,
       SUM(l.quantity)                           AS units,
       (o.created_at >= :rise_date)              AS after_rise
FROM order_lines l
JOIN orders o ON o.id = l.order_id
WHERE o.created_at BETWEEN :rise_date - INTERVAL '12 weeks'
                      AND :rise_date + INTERVAL '12 weeks'
  AND l.sku IN (:raised_skus)
GROUP BY 1, 2, 4
ORDER BY 1, 2;

Leave out the stock-up weeks, compare four-week averages against each SKU’s break-even, and do the same for SKUs you didn’t raise.

Repeat rate at 90 and 180 days

-- customers active in the 90 days before the rise, and the same window a year earlier
WITH base AS (
  SELECT customer_id, 'this_year' AS cohort, :rise_date AS start_date
  FROM orders
  WHERE created_at >= :rise_date - INTERVAL '90 days' AND created_at < :rise_date
  GROUP BY customer_id
  UNION ALL
  SELECT customer_id, 'last_year', :rise_date - INTERVAL '1 year'
  FROM orders
  WHERE created_at >= :rise_date - INTERVAL '1 year' - INTERVAL '90 days'
    AND created_at <  :rise_date - INTERVAL '1 year'
  GROUP BY customer_id
)
SELECT b.cohort,
       COUNT(*)                                                          AS customers,
       AVG(CASE WHEN EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = b.customer_id
             AND o.created_at >= b.start_date
             AND o.created_at <  b.start_date + INTERVAL '90 days')  THEN 1 ELSE 0 END) AS repeat_90,
       AVG(CASE WHEN EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = b.customer_id
             AND o.created_at >= b.start_date
             AND o.created_at <  b.start_date + INTERVAL '180 days') THEN 1 ELSE 0 END) AS repeat_180
FROM base b
GROUP BY b.cohort;

Read the first column at day 90 and the second at day 180. If the stock-up window pulled orders forward, count from the day after it closes, for both cohorts.

The loyalty tax: discount depth by customer type

-- average discount as a share of list, first orders against repeat orders, last 90 days
SELECT CASE WHEN o.customer_order_number = 1 THEN 'first order' ELSE 'repeat order' END AS type,
       COUNT(DISTINCT o.id)                                        AS orders,
       SUM(l.discount) / NULLIF(SUM(l.quantity * l.price), 0)      AS discount_share,
       AVG(CASE WHEN o.discount_code IS NOT NULL THEN 1 ELSE 0 END) AS share_with_code
FROM orders o
JOIN order_lines l ON l.order_id = o.id
WHERE o.created_at >= CURRENT_DATE - INTERVAL '90 days'
GROUP BY 1;

Deeper first-order discounts are expected. Look for repeat customers using public codes meant for strangers, and, grouping by l.sku too, repeat orders at a worse effective price than first orders.