Marketing ROI: formula and example.

WITH margin AS (
  SELECT date_trunc('year', o.order_date) AS year,
         SUM(ol.quantity * (ol.unit_price - ol.unit_cost) - ol.discount_amount)
           / NULLIF(SUM(ol.quantity * ol.unit_price - ol.discount_amount), 0) AS margin_rate
  FROM order_lines ol JOIN orders o USING (order_id) GROUP BY 1),
attributed AS (
  SELECT date_trunc('year', spend_date) AS year, SUM(conversion_value) AS revenue
  FROM ad_spend GROUP BY 1),
cost AS (
  SELECT date_trunc('year', month) AS year, SUM(amount) AS cost
  FROM marketing_costs GROUP BY 1)
SELECT year, a.revenue, c.cost,
       ROUND(100.0 * (a.revenue * m.margin_rate - c.cost) / NULLIF(c.cost, 0), 1) AS marketing_roi_pct
FROM attributed a JOIN cost c USING (year) JOIN margin m USING (year)
ORDER BY year;

Marketing ROI =
VAR MarginRate =
    DIVIDE(
        SUMX(SalesLines, SalesLines[Quantity] * (SalesLines[UnitPrice] - SalesLines[UnitCost])),
        SUMX(SalesLines, SalesLines[Quantity] * SalesLines[UnitPrice])
    )
VAR Cost = SUM(MarketingCosts[Amount])
RETURN
    DIVIDE(SUM(AdSpend[ConversionValue]) * MarginRate - Cost, Cost)