Commercial Performance & Promotion Intelligence

Decision: where should a fictional commercial team reallocate next-quarter promotion investment while protecting net revenue and gross margin?

Deterministic synthetic portfolio data · snapshot 30 Jun 2026 · no employer or customer information.

Net revenue

$12.55M

Revenue after returns and discounts.

Revenue after returns and discounts.Source: Commercial KPI summaryTables: fact_sales, fact_budget_forecast

Deterministic query executed by scripts/build_analytics_projects.py.

WITH actual_by_slice AS (
  SELECT month, sku_id, market, channel,
         SUM(net_units) AS actual_units,
         SUM(net_revenue_eur) AS net_revenue,
         SUM(gross_profit_eur) AS gross_profit
  FROM fact_sales GROUP BY month, sku_id, market, channel
), actual AS (
  SELECT SUM(net_revenue) AS net_revenue,
         SUM(gross_profit) AS gross_profit,
         SUM(actual_units) AS net_units,
         SUM(ABS(actual_units - forecast_units)) AS absolute_forecast_error,
         SUM(actual_units) AS actual_units
  FROM actual_by_slice
  JOIN fact_budget_forecast USING (month, sku_id, market, channel)
), plan AS (
  SELECT SUM(budget_revenue_eur) AS budget_revenue FROM fact_budget_forecast
)
SELECT ROUND(net_revenue, 2) AS net_revenue,
       ROUND((net_revenue - budget_revenue) / budget_revenue, 6) AS budget_variance_rate,
       ROUND(gross_profit / net_revenue, 6) AS gross_margin_rate,
       net_units,
       ROUND(absolute_forecast_error * 1.0 / actual_units, 6) AS forecast_wape
FROM actual CROSS JOIN plan;

Vs budget

-0.9%

Actual net revenue versus the complete-period budget.

Actual net revenue versus the complete-period budget.Source: Commercial KPI summaryTables: fact_sales, fact_budget_forecast

Deterministic query executed by scripts/build_analytics_projects.py.

WITH actual_by_slice AS (
  SELECT month, sku_id, market, channel,
         SUM(net_units) AS actual_units,
         SUM(net_revenue_eur) AS net_revenue,
         SUM(gross_profit_eur) AS gross_profit
  FROM fact_sales GROUP BY month, sku_id, market, channel
), actual AS (
  SELECT SUM(net_revenue) AS net_revenue,
         SUM(gross_profit) AS gross_profit,
         SUM(actual_units) AS net_units,
         SUM(ABS(actual_units - forecast_units)) AS absolute_forecast_error,
         SUM(actual_units) AS actual_units
  FROM actual_by_slice
  JOIN fact_budget_forecast USING (month, sku_id, market, channel)
), plan AS (
  SELECT SUM(budget_revenue_eur) AS budget_revenue FROM fact_budget_forecast
)
SELECT ROUND(net_revenue, 2) AS net_revenue,
       ROUND((net_revenue - budget_revenue) / budget_revenue, 6) AS budget_variance_rate,
       ROUND(gross_profit / net_revenue, 6) AS gross_margin_rate,
       net_units,
       ROUND(absolute_forecast_error * 1.0 / actual_units, 6) AS forecast_wape
FROM actual CROSS JOIN plan;

Gross margin

61.5%

Weighted gross profit divided by net revenue.

Weighted gross profit divided by net revenue.Source: Commercial KPI summaryTables: fact_sales, fact_budget_forecast

Deterministic query executed by scripts/build_analytics_projects.py.

WITH actual_by_slice AS (
  SELECT month, sku_id, market, channel,
         SUM(net_units) AS actual_units,
         SUM(net_revenue_eur) AS net_revenue,
         SUM(gross_profit_eur) AS gross_profit
  FROM fact_sales GROUP BY month, sku_id, market, channel
), actual AS (
  SELECT SUM(net_revenue) AS net_revenue,
         SUM(gross_profit) AS gross_profit,
         SUM(actual_units) AS net_units,
         SUM(ABS(actual_units - forecast_units)) AS absolute_forecast_error,
         SUM(actual_units) AS actual_units
  FROM actual_by_slice
  JOIN fact_budget_forecast USING (month, sku_id, market, channel)
), plan AS (
  SELECT SUM(budget_revenue_eur) AS budget_revenue FROM fact_budget_forecast
)
SELECT ROUND(net_revenue, 2) AS net_revenue,
       ROUND((net_revenue - budget_revenue) / budget_revenue, 6) AS budget_variance_rate,
       ROUND(gross_profit / net_revenue, 6) AS gross_margin_rate,
       net_units,
       ROUND(absolute_forecast_error * 1.0 / actual_units, 6) AS forecast_wape
FROM actual CROSS JOIN plan;

Net units

4.16M

Gross units less returned units.

Gross units less returned units.Source: Commercial KPI summaryTables: fact_sales, fact_budget_forecast

Deterministic query executed by scripts/build_analytics_projects.py.

WITH actual_by_slice AS (
  SELECT month, sku_id, market, channel,
         SUM(net_units) AS actual_units,
         SUM(net_revenue_eur) AS net_revenue,
         SUM(gross_profit_eur) AS gross_profit
  FROM fact_sales GROUP BY month, sku_id, market, channel
), actual AS (
  SELECT SUM(net_revenue) AS net_revenue,
         SUM(gross_profit) AS gross_profit,
         SUM(actual_units) AS net_units,
         SUM(ABS(actual_units - forecast_units)) AS absolute_forecast_error,
         SUM(actual_units) AS actual_units
  FROM actual_by_slice
  JOIN fact_budget_forecast USING (month, sku_id, market, channel)
), plan AS (
  SELECT SUM(budget_revenue_eur) AS budget_revenue FROM fact_budget_forecast
)
SELECT ROUND(net_revenue, 2) AS net_revenue,
       ROUND((net_revenue - budget_revenue) / budget_revenue, 6) AS budget_variance_rate,
       ROUND(gross_profit / net_revenue, 6) AS gross_margin_rate,
       net_units,
       ROUND(absolute_forecast_error * 1.0 / actual_units, 6) AS forecast_wape
FROM actual CROSS JOIN plan;

Forecast WAPE

2.2%

Absolute locked-forecast unit error divided by actual units; lower is better.

Absolute locked-forecast unit error divided by actual units; lower is better.Source: Commercial KPI summaryTables: fact_sales, fact_budget_forecast

Deterministic query executed by scripts/build_analytics_projects.py.

WITH actual_by_slice AS (
  SELECT month, sku_id, market, channel,
         SUM(net_units) AS actual_units,
         SUM(net_revenue_eur) AS net_revenue,
         SUM(gross_profit_eur) AS gross_profit
  FROM fact_sales GROUP BY month, sku_id, market, channel
), actual AS (
  SELECT SUM(net_revenue) AS net_revenue,
         SUM(gross_profit) AS gross_profit,
         SUM(actual_units) AS net_units,
         SUM(ABS(actual_units - forecast_units)) AS absolute_forecast_error,
         SUM(actual_units) AS actual_units
  FROM actual_by_slice
  JOIN fact_budget_forecast USING (month, sku_id, market, channel)
), plan AS (
  SELECT SUM(budget_revenue_eur) AS budget_revenue FROM fact_budget_forecast
)
SELECT ROUND(net_revenue, 2) AS net_revenue,
       ROUND((net_revenue - budget_revenue) / budget_revenue, 6) AS budget_variance_rate,
       ROUND(gross_profit / net_revenue, 6) AS gross_margin_rate,
       net_units,
       ROUND(absolute_forecast_error * 1.0 / actual_units, 6) AS forecast_wape
FROM actual CROSS JOIN plan;

Net revenue and budget by month

Source: Monthly revenue and budgetTables: fact_sales, fact_budget_forecast

Deterministic query executed by scripts/build_analytics_projects.py.

SELECT s.month,
       ROUND(SUM(s.net_revenue_eur), 2) AS actual_revenue,
       ROUND(MAX(b.budget_revenue), 2) AS budget_revenue,
       ROUND(SUM(s.gross_profit_eur), 2) AS gross_profit
FROM fact_sales s
JOIN (SELECT month, SUM(budget_revenue_eur) AS budget_revenue
      FROM fact_budget_forecast GROUP BY month) b ON b.month = s.month
GROUP BY s.month ORDER BY s.month;

Net revenue and budget by month data
Month	Actual Revenue	Budget Revenue
2025-01	$575.79K	$581.95K
2025-02	$539.3K	$544.21K
2025-03	$651.74K	$656.77K
2025-04	$664.03K	$670.14K
2025-05	$759.24K	$764.11K
2025-06	$770.1K	$775.86K
2025-07	$828.45K	$833.53K
2025-08	$799.63K	$806.81K
2025-09	$673.83K	$679.47K
2025-10	$669.53K	$674.28K
2025-11	$711.18K	$714.43K
2025-12	$812.8K	$817.48K
2026-01	$601.76K	$606.45K
2026-02	$561.46K	$565.62K
2026-03	$674.52K	$681.26K
2026-04	$691.32K	$696.45K
2026-05	$765.59K	$794.43K
2026-06	$800.77K	$806.12K

Market performance

Source: Market performanceTables: fact_sales, fact_budget_forecast

Deterministic query executed by scripts/build_analytics_projects.py.

WITH budgets AS (
  SELECT market, SUM(budget_revenue_eur) AS budget_revenue
  FROM fact_budget_forecast GROUP BY market
)
SELECT s.market,
       ROUND(SUM(s.net_revenue_eur), 2) AS net_revenue,
       ROUND(SUM(s.gross_profit_eur) / SUM(s.net_revenue_eur), 6) AS gross_margin_rate,
       ROUND((SUM(s.net_revenue_eur) - MAX(b.budget_revenue)) / MAX(b.budget_revenue), 6) AS budget_variance_rate
FROM fact_sales s JOIN budgets b ON b.market = s.market
GROUP BY s.market ORDER BY net_revenue DESC;

Market performance
Market	Net revenue	Gross margin	Vs budget
Central	$3.7M	61.8%	-0.9%
North	$3.62M	63.6%	-0.8%
South	$2.8M	59.3%	-1%
West	$2.44M	60.6%	-1.1%

Promotion decision

Recommendation: Run a bounded next-quarter test in the 10% band (modeled weighted ROI 24.5%); keep the modeled baseline and margin guardrail visible before scaling.

Source: Promotion effectivenessTable: fact_promotion

Deterministic query executed by scripts/build_analytics_projects.py.

SELECT CASE
         WHEN discount_pct < .125 THEN '10%'
         WHEN discount_pct < .175 THEN '15%'
         WHEN discount_pct < .25 THEN '20%'
         ELSE '30%'
       END AS discount_band,
       ROUND(AVG(discount_pct), 4) AS discount_pct,
       ROUND(SUM(incremental_profit_eur), 2) AS incremental_profit_eur,
       ROUND(SUM(promotion_investment_eur), 2) AS promotion_investment_eur,
       ROUND(SUM(incremental_profit_eur) / SUM(promotion_investment_eur), 6) AS promotion_roi,
       ROUND(SUM(actual_units - baseline_units) * 1.0 / SUM(baseline_units), 6) AS unit_uplift_pct
FROM fact_promotion GROUP BY discount_band ORDER BY discount_pct;

Weighted modeled ROI by discount band

Source: Promotion effectivenessTable: fact_promotion

Deterministic query executed by scripts/build_analytics_projects.py.

SELECT CASE
         WHEN discount_pct < .125 THEN '10%'
         WHEN discount_pct < .175 THEN '15%'
         WHEN discount_pct < .25 THEN '20%'
         ELSE '30%'
       END AS discount_band,
       ROUND(AVG(discount_pct), 4) AS discount_pct,
       ROUND(SUM(incremental_profit_eur), 2) AS incremental_profit_eur,
       ROUND(SUM(promotion_investment_eur), 2) AS promotion_investment_eur,
       ROUND(SUM(incremental_profit_eur) / SUM(promotion_investment_eur), 6) AS promotion_roi,
       ROUND(SUM(actual_units - baseline_units) * 1.0 / SUM(baseline_units), 6) AS unit_uplift_pct
FROM fact_promotion GROUP BY discount_band ORDER BY discount_pct;

Weighted modeled ROI by discount band data
Discount Band	Promotion Roi
10%	24.5%
15%	7.9%
20%	1.4%
30%	-41.6%

Promotion decision detail

Source: Promotion effectivenessTable: fact_promotion

Deterministic query executed by scripts/build_analytics_projects.py.

SELECT CASE
         WHEN discount_pct < .125 THEN '10%'
         WHEN discount_pct < .175 THEN '15%'
         WHEN discount_pct < .25 THEN '20%'
         ELSE '30%'
       END AS discount_band,
       ROUND(AVG(discount_pct), 4) AS discount_pct,
       ROUND(SUM(incremental_profit_eur), 2) AS incremental_profit_eur,
       ROUND(SUM(promotion_investment_eur), 2) AS promotion_investment_eur,
       ROUND(SUM(incremental_profit_eur) / SUM(promotion_investment_eur), 6) AS promotion_roi,
       ROUND(SUM(actual_units - baseline_units) * 1.0 / SUM(baseline_units), 6) AS unit_uplift_pct
FROM fact_promotion GROUP BY discount_band ORDER BY discount_pct;

Promotion decision detail
Discount band	Weighted ROI	Modeled unit uplift	Incremental profit	Investment
10%	24.5%	45.4%	$1.78K	$7.28K
15%	7.9%	57.8%	$761.42	$9.66K
20%	1.4%	71.5%	$185.82	$12.89K
30%	-41.6%	54.6%	-$7.65K	$18.41K

Net revenue by product

Source: Product decision detailTables: fact_sales, fact_budget_forecast

Deterministic query executed by scripts/build_analytics_projects.py.

WITH budgets AS (
  SELECT sku_id, SUM(budget_revenue_eur) AS budget_revenue
  FROM fact_budget_forecast GROUP BY sku_id
)
SELECT s.sku_id, MAX(s.product_name) AS product_name, MAX(s.category) AS category,
       ROUND(SUM(s.net_revenue_eur), 2) AS net_revenue,
       ROUND(SUM(s.gross_profit_eur) / SUM(s.net_revenue_eur), 6) AS gross_margin_rate,
       ROUND((SUM(s.net_revenue_eur) - MAX(b.budget_revenue)) / MAX(b.budget_revenue), 6) AS budget_variance_rate,
       SUM(s.net_units) AS net_units
FROM fact_sales s JOIN budgets b ON b.sku_id = s.sku_id
GROUP BY s.sku_id ORDER BY net_revenue DESC;

Net revenue by product data
Product Name	Net Revenue
Luma Granola	$982.48K
Luma Oats	$885.71K
Orbit Bites	$874.86K
Vela Mix	$873.26K
Aster Crunch	$864.71K
Halo Muesli	$857.83K
Northstar Porridge	$794.52K
Aster Sparkling	$755.2K
Orbit Bar	$736.67K
Vela Citrus	$735.08K
Vela Protein Shake	$726.03K
Aster Still	$724.21K
Luma Oat Drink	$715.86K
Vela Berry	$709.9K
Halo Almond Drink	$683.03K
Orbit Plant Snack	$631.69K

Product decision detail

Source: Product decision detailTables: fact_sales, fact_budget_forecast

Deterministic query executed by scripts/build_analytics_projects.py.

WITH budgets AS (
  SELECT sku_id, SUM(budget_revenue_eur) AS budget_revenue
  FROM fact_budget_forecast GROUP BY sku_id
)
SELECT s.sku_id, MAX(s.product_name) AS product_name, MAX(s.category) AS category,
       ROUND(SUM(s.net_revenue_eur), 2) AS net_revenue,
       ROUND(SUM(s.gross_profit_eur) / SUM(s.net_revenue_eur), 6) AS gross_margin_rate,
       ROUND((SUM(s.net_revenue_eur) - MAX(b.budget_revenue)) / MAX(b.budget_revenue), 6) AS budget_variance_rate,
       SUM(s.net_units) AS net_units
FROM fact_sales s JOIN budgets b ON b.sku_id = s.sku_id
GROUP BY s.sku_id ORDER BY net_revenue DESC;

Product decision detail
Product	Category	Net revenue	Gross margin	Vs budget	Net units
Luma Granola	Breakfast	$982.48K	59.3%	-0.8%	215.66K
Luma Oats	Breakfast	$885.71K	61.2%	-0.7%	235.15K
Orbit Bites	Snacks	$874.86K	64%	-0.2%	304.65K
Vela Mix	Snacks	$873.26K	61.9%	-0.7%	244.69K
Aster Crunch	Snacks	$864.71K	62.4%	-0.6%	264.74K
Halo Muesli	Breakfast	$857.83K	59.5%	-0.9%	176.73K
Northstar Porridge	Breakfast	$794.52K	60.4%	-3.4%	191.4K
Aster Sparkling	Hydration	$755.2K	64.6%	-0.6%	347.39K
Orbit Bar	Snacks	$736.67K	65.1%	-1.3%	437.15K
Vela Citrus	Hydration	$735.08K	63.5%	-0.6%	284.97K
Vela Protein Shake	Plant-Based	$726.03K	56.3%	-1.2%	166.57K
Aster Still	Hydration	$724.21K	64.8%	-1%	405.7K
Luma Oat Drink	Plant-Based	$715.86K	59.1%	-0.6%	225.69K
Vela Berry	Hydration	$709.9K	63.2%	-0.5%	265.41K
Halo Almond Drink	Plant-Based	$683.03K	58.3%	-0.9%	186.3K

16 results · Showing first 15

Quality and reconciliation evidence

Source: Executed commercial QA checksTable: quality_checks

Deterministic query executed by scripts/build_analytics_projects.py.

SELECT "check" AS "check", status, result FROM quality_checks ORDER BY "check";

Quality and reconciliation evidence
Check	Status	Evidence
All four planned discount bands are represented	PASS	4 bands
Daily sales grain is unique	PASS	139,776 rows; 0 duplicates
Dashboard and SQL totals agree	PASS	Exact to €0.01
Discount depth is not confounded with one execution mechanic	PASS	4 mechanics represented in every discount band
Every sales slice has one budget and locked forecast	PASS	4,608 matched slices
Forecast is locked before each reporting month	PASS	0 invalid lock dates
Net revenue formula reconciles	PASS	0 rows outside €0.01 tolerance
Promotion dates are valid	PASS	0 invalid windows
Returns never exceed gross units	PASS	0 violations

Method and limitations

Promotion baselines are modeled, so ROI is scenario analysis rather than a causal experiment. Budget and actual cover the same complete period. Exact formulas, SQL, data dictionary, notebook and QA evidence are included beside this dashboard.

Sources

Commercial KPI summarysql/analytics.sql · SQLite 3 · 2026-06-30T18:00:00Z

Deterministic query executed by scripts/build_analytics_projects.py.

SQL query

WITH actual_by_slice AS (
  SELECT month, sku_id, market, channel,
         SUM(net_units) AS actual_units,
         SUM(net_revenue_eur) AS net_revenue,
         SUM(gross_profit_eur) AS gross_profit
  FROM fact_sales GROUP BY month, sku_id, market, channel
), actual AS (
  SELECT SUM(net_revenue) AS net_revenue,
         SUM(gross_profit) AS gross_profit,
         SUM(actual_units) AS net_units,
         SUM(ABS(actual_units - forecast_units)) AS absolute_forecast_error,
         SUM(actual_units) AS actual_units
  FROM actual_by_slice
  JOIN fact_budget_forecast USING (month, sku_id, market, channel)
), plan AS (
  SELECT SUM(budget_revenue_eur) AS budget_revenue FROM fact_budget_forecast
)
SELECT ROUND(net_revenue, 2) AS net_revenue,
       ROUND((net_revenue - budget_revenue) / budget_revenue, 6) AS budget_variance_rate,
       ROUND(gross_profit / net_revenue, 6) AS gross_margin_rate,
       net_units,
       ROUND(absolute_forecast_error * 1.0 / actual_units, 6) AS forecast_wape
FROM actual CROSS JOIN plan;

Monthly revenue and budgetsql/analytics.sql · SQLite 3 · 2026-06-30T18:00:00Z

Deterministic query executed by scripts/build_analytics_projects.py.

SQL query

SELECT s.month,
       ROUND(SUM(s.net_revenue_eur), 2) AS actual_revenue,
       ROUND(MAX(b.budget_revenue), 2) AS budget_revenue,
       ROUND(SUM(s.gross_profit_eur), 2) AS gross_profit
FROM fact_sales s
JOIN (SELECT month, SUM(budget_revenue_eur) AS budget_revenue
      FROM fact_budget_forecast GROUP BY month) b ON b.month = s.month
GROUP BY s.month ORDER BY s.month;

Market performancesql/analytics.sql · SQLite 3 · 2026-06-30T18:00:00Z

Deterministic query executed by scripts/build_analytics_projects.py.

SQL query

WITH budgets AS (
  SELECT market, SUM(budget_revenue_eur) AS budget_revenue
  FROM fact_budget_forecast GROUP BY market
)
SELECT s.market,
       ROUND(SUM(s.net_revenue_eur), 2) AS net_revenue,
       ROUND(SUM(s.gross_profit_eur) / SUM(s.net_revenue_eur), 6) AS gross_margin_rate,
       ROUND((SUM(s.net_revenue_eur) - MAX(b.budget_revenue)) / MAX(b.budget_revenue), 6) AS budget_variance_rate
FROM fact_sales s JOIN budgets b ON b.market = s.market
GROUP BY s.market ORDER BY net_revenue DESC;

Promotion effectivenesssql/analytics.sql · SQLite 3 · 2026-06-30T18:00:00Z

Deterministic query executed by scripts/build_analytics_projects.py.

SQL query

SELECT CASE
         WHEN discount_pct < .125 THEN '10%'
         WHEN discount_pct < .175 THEN '15%'
         WHEN discount_pct < .25 THEN '20%'
         ELSE '30%'
       END AS discount_band,
       ROUND(AVG(discount_pct), 4) AS discount_pct,
       ROUND(SUM(incremental_profit_eur), 2) AS incremental_profit_eur,
       ROUND(SUM(promotion_investment_eur), 2) AS promotion_investment_eur,
       ROUND(SUM(incremental_profit_eur) / SUM(promotion_investment_eur), 6) AS promotion_roi,
       ROUND(SUM(actual_units - baseline_units) * 1.0 / SUM(baseline_units), 6) AS unit_uplift_pct
FROM fact_promotion GROUP BY discount_band ORDER BY discount_pct;

Product decision detailsql/analytics.sql · SQLite 3 · 2026-06-30T18:00:00Z

Deterministic query executed by scripts/build_analytics_projects.py.

SQL query

WITH budgets AS (
  SELECT sku_id, SUM(budget_revenue_eur) AS budget_revenue
  FROM fact_budget_forecast GROUP BY sku_id
)
SELECT s.sku_id, MAX(s.product_name) AS product_name, MAX(s.category) AS category,
       ROUND(SUM(s.net_revenue_eur), 2) AS net_revenue,
       ROUND(SUM(s.gross_profit_eur) / SUM(s.net_revenue_eur), 6) AS gross_margin_rate,
       ROUND((SUM(s.net_revenue_eur) - MAX(b.budget_revenue)) / MAX(b.budget_revenue), 6) AS budget_variance_rate,
       SUM(s.net_units) AS net_units
FROM fact_sales s JOIN budgets b ON b.sku_id = s.sku_id
GROUP BY s.sku_id ORDER BY net_revenue DESC;

Executed commercial QA checkssql/analytics.sql · SQLite 3 · 2026-06-30T18:00:00Z
Deterministic query executed by scripts/build_analytics_projects.py.
SQL query
```
SELECT "check" AS "check", status, result FROM quality_checks ORDER BY "check";
```