RootSignal

A sales metric moved. RootSignal names the segment behind it, puts a number on what it cost, and rates its own confidence.

Root-cause analytics for sales and supply operations. Given a movement in a business metric, it identifies the segment responsible, separates a genuine performance change from a shift in demand mix, estimates the money involved, reports confidence against named criteria, and recommends the next thing to check.

It is built to be checkable. Every headline number below is reproduced by a committed script or test, and the system declines to answer questions its data cannot support.

Movements ranked by estimated impact, each with a confidence rating and the evidence behind it (top). Weekly headline metrics with data-quality actions, and forecasting methods compared against a naive baseline (bottom). Generated sample data.

---

What it actually produces

Given nothing but a fill-rate movement across every region and category — no hint of Bengaluru, of fruit, or of supply:

python scripts/detect_signals.py

BLR | Fruits — fill rate moved −0.2889 (−29.1%)

Consistent with a fulfilment or supply constraint: fulfilled units fell while ordered units did not. Fulfilled units fell 16.2%; ordered units rose 18.2%; available stock fell 47.7%; stockout rate rose.

Estimated impact 8,970.60, from units not fulfilled relative to the segment's prior fill rate, valued at its realised average selling price.

Confidence: high — 5 of 6 criteria met. Met: movement stands out (19.9× the segment's typical swing) · segment carries 26.8% of the movement · 4 other metrics agree · the alternative is not supported · movement is directional. Not met: it has moved this way for one period, and the threshold is two.

Alternative considered: demand weakened and fulfilment simply followed it. Orders rose 18% while shipments fell 16%, so that reading is not available.

Recommended investigation: review inventory availability and replenishment for BLR | Fruits, starting with the SKUs carrying the largest unfulfilled volume.

The interesting part is the criterion it will not claim. The alternative is dismissed on evidence, but the engine refuses to call a one-week step a persistent trend — because on the period a step change happens, it isn't one yet. Five of six, on the one criterion only time can settle.

What makes it different

It shows its working, not a score. Confidence is six named criteria you can read and disagree with, not a tuned number. Impact ships with its assumptions attached — no substitution, no recovery, associated revenue rather than measured loss.

It separates performance from mix. A fill rate can fall while every segment improves, purely because demand moved toward segments that fill less well. Rate effect, mix effect and interaction are decomposed so they reconstruct the observed movement exactly — and the code refuses to return effects that do not add back.

It refuses rather than guesses. An adapter declares which analyses its data can support and which it cannot, with the missing fact named. The LLM layer never calculates: it rewrites numbers the deterministic layer already computed, and a guard verifies every figure and rejects causal phrasing.

It runs on data it was not built for. The same pipeline, unchanged, runs on a million real invoice lines from a UK retailer — which broke three things the generated data never could.

Measured results

Reproduced by the committed test suite and evaluation scripts.

	Result
Scenario detection	5 situations planted (supply constraint, demand decline, mix shift, and two controls where nothing happens). 3 of 3 correctly classified at a medium confidence floor, at the cost of 12 false-alarm signals — 9 of them on the two controls, 3 raised alongside correct findings; 2 of 3 with zero false alarms and silence on both controls at a high floor
Real public data	UCI Online Retail II, 1,044,420 invoice lines over 739 days. 3 defects found in the published data, 2,590 unsellable lines quarantined, then zero validation errors
Forecast accuracy	seasonal_mean_7 beats naive by +28.9% WAPE on daily net sales and +19.1% on units; ses leads orders at +22.2%. On the real dataset, +38.8% across 49 folds — the same model wins on both
SQL / Python parity	The commercial mart is implemented twice and a test asserts the two agree across 3,663 rows and 11 metric columns. The SQL watchlist and the Python engine — sharing no code — independently flag the same two disrupted segments
Data quality	8 errors detected across 10 raw tables; cleaning imputes 3 fields, drops 2 exact duplicates, quarantines 1 impossible line; zero errors after
Plan vs actual	Attainment spans 87.4% to 125.6% across 64 region/category/channel segments, evenly split 32 above plan and 32 below. KAM quota attainment runs 89.8% to 106.0% across four managers, three ahead and one behind
Reproducibility	Identical results on numpy 1.26 under Windows and numpy 2.5 under Linux — same signal, same impact to the cent, same forecast improvement. The container resolves the top of the declared dependency range rather than a lockfile, which is how the numpy 2.x break was found
Data pipeline	The same 1,067,371 lines landed in S3, conformed by a PySpark job, cleaned, and loaded into PostgreSQL (996,528 sales lines) and Redshift Serverless (996,531, loaded before stock codes were made case-insensitive). Spark's output is identical to the pandas adapter's in all six tables, compared column by column on the full dataset
Warehouse parity	Every staging view and all seven analytical queries return the same rows on PostgreSQL and Redshift as on SQLite. The one tolerance is aov, which may differ by a cent on an exact half cent because the engines round ties differently
Tests	427 automated tests, passing on both dependency sets. One asserts that no output ever claims causation
Mutation testing	scripts/mutation_test.py applies 37 operator and constant mutations to the KPI layer; the suite kills 34 (91.9%). The 3 survivors are equivalent mutants (> 0 vs >= 0 on a count that is never zero, or on a ratio where 0/0 is already NaN). On the pipeline, scripts/mutation_test_pipeline.py applies 28 realistic bugs (Spark's rounding, a lost CSV option, a dropped tie-breaker, a spending cap that only logs) and the suite catches all 28, up from 22 before the gaps it found were tested. Operator mutants on the pipeline code: 93 of 108 caught; the 15 survivors are wait times, output file counts and print formatting

On that false-alarm figure. The 12 counts every signal that matched nothing planted, wherever it was raised: 9 on the two control windows and 3 alongside findings that were themselves correct.

The two controls disagree, and that is the more useful number. One raises three false alarms at a medium floor and the other six, so the rate varies two-fold between windows that both have nothing planted in them.

Quick start

Either install it:

pip install -e ".[dev,dashboard]"
python scripts/generate_sample_data.py --output-dir data/raw/generated

Or don't, and check the claims instead:

docker build -t rootsignal . && docker run --rm rootsignal pytest

Or run the whole pipeline on the real dataset, from download to warehouse:

docker compose up -d warehouse
docker compose run --rm pipeline          # land, conform in Spark, load, publish

Then, in rough order of what is worth seeing first:

python scripts/detect_signals.py

python scripts/evaluate_signals.py

streamlit run app/Overview.py

python scripts/run_external_dataset.py

The first prints the worked example above; the second measures whether the engine tells the five scenarios apart; the third opens the dashboard; the fourth runs the same pipeline against real public data.

The rest

python scripts/explain_signals.py        # signals written up in plain English
python scripts/evaluate_forecasts.py     # forecast accuracy, five models
python scripts/build_excel_reports.py    # six operational workbooks
python scripts/run_sql_query.py          # the seven analytical SQL queries
pytest                                   # the full suite
python scripts/mutation_test.py          # mutation score for the KPI layer

Architecture

CSV / Excel / DB  ·  or a declared adapter for an outside dataset
       |
       v
Ingestion -> Validation -> Cleaning (quarantine-first, audited) -> Consolidation
       |
       v
Business data model  —  grains and composite keys enforced in SQL and Python
       |
       +--> KPI engine
       +--> Trend / variance analysis
       +--> Forecasting (rolling-origin backtested)
       +--> Driver decomposition (exact; rate and mix separated)
       +--> Impact estimation (assumptions attached)
       |
       v
Evidence package  —  evidence, pattern, impact, confidence criteria, alternative
       |
       +--> Dashboard  ·  Excel  ·  SQL
       +--> Optional LLM rewrite, numerically verified
       |
       v
Recommended investigation

Stack: Python, pandas, NumPy, PySpark, SQL (SQLite, PostgreSQL, Amazon Redshift), AWS S3, Streamlit, Plotly, Power BI, pytest, Docker, GitHub Actions, and an optional OpenAI-compatible LLM that the system works fully without.

Data pipeline

UCI workbook -> S3 raw zone -> Spark conform job -> S3 curated zone (Parquet)
     -> cleaning + validation (nothing loads while an error remains)
     -> PostgreSQL warehouse   + S3 clean zone -> Redshift Serverless (COPY)
     -> report tables (rate/mix split, drivers, forecast accuracy) -> Power BI

jobs/conform_online_retail.py is a PySpark version of the Online Retail adapter with no imports from the package, so the same file runs locally, in the container and on a Spark cluster. scripts/compare_spark_to_pandas.py checks it against the pandas adapter on the full dataset. Cleaning stays in pandas: it owns the audit trail and the quarantine, and a second implementation would mean a second definition of every rule.

Locally the lake is a folder and the warehouse a PostgreSQL container (docker compose run --rm pipeline). scripts/run_pipeline_aws.py runs the same stages with the lake in S3 and a Redshift Serverless warehouse, capped at 8 RPU with a usage limit that switches it off, and a teardown step that removes everything that bills. Glue and EMR Serverless subcommands exist for the Spark step but have not been run: the account used is not yet permitted Glue jobs, and its EMR Serverless quota had not taken effect. The Spark job itself has only run locally so far. power_bi.md describes the report on the warehouse.

What's built

Layer	What it does	Docs
Data model + SQL schema	Dimensions and facts with grains and composite keys enforced in both SQL and Python	data_model.md
Sample-data generator	Deterministic 60-day dataset (seed 42) with multi-SKU baskets, controlled defects, and five labelled scenarios	—
Ingestion	CSV, Excel and database loading by table name	—
Validation	Columns, keys, ranges, referential integrity, cross-table reconciliation	—
Cleaning	Quarantine-first repair with a full audit trail	cleaning.md
Consolidation	Grain-safe enrichment and the commercial mart	consolidation.md
KPI engine	Deterministic sales, order, fill-rate, mix, growth and variance metrics	kpis.md
Forecasting	Five deterministic daily models with rolling-origin backtesting	forecasting.md
Trend and variance	Day/week/month movement against prior period, target and forecast under one schema	analysis.md
Driver decomposition	Exact attribution to segments, with rate and mix separated for ratios	decomposition.md
Impact estimation	Fulfilment shortfall valued at realised prices, every assumption stated	signals.md
RootSignal engine	Evidence, pattern, impact, confidence and recommended investigation, ranked	signals.md
Scenario evaluation	Measures whether the engine tells three planted situations apart and stays silent on two controls	signals.md
SQL layer	Staging views, commercial mart and seven business queries, all executed by tests	sql.md
Warehouse	The same SQL on PostgreSQL and Redshift, translated from one source and held row for row to SQLite	power_bi.md
Pipeline	Raw, curated and clean zones on disk or in S3, a PySpark conform job, and report tables for BI	power_bi.md
Excel reporting	Six operational workbooks, each opening with what its figures mean	reporting.md
Dashboard	Five Streamlit pages over a Streamlit-free, tested data layer	dashboard.md
Presentation	One map from schema keys to language a business reader already has	dashboard.md
Explanation	Written briefings with no API key, plus an optional verified LLM rewrite	explanation.md
External data	Adapters that declare what a dataset can and cannot answer	external_data.md
Pipeline contract	End-to-end test from generation through mart reconciliation	pipeline_contract.md
Container	Dashboard, scripts and the full suite runnable without installing anything	docker.md

What it refuses to do

Enforced in code and covered by tests, not stated as intentions.

• Claim causation. Patterns are described as consistent with a cause, and a test rejects causal phrasing in any output.
• Return effects that do not reconcile. A rate decomposition that fails to reconstruct the observed movement raises, naming the offending segments — a guard a real dataset earned, not a hypothetical one.
• Answer without the facts. Fulfilment analysis on a dataset with no order book is refused, with the missing fact named, rather than approximated from returns.
• Let a language model do arithmetic. Every figure in an LLM rewrite is checked against the computed value, and the briefing falls back to the deterministic text — recording why — if it fails.
• Average a rate. Ratios are rebuilt from summed components at every level, never averaged up from a finer grain.

external_data.md records what running on real data broke, including a decomposition that silently reconstructed a movement that never happened, and why a widely used public supply-chain dataset was evaluated and rejected as synthetic.

Not yet built

A second external adapter is scoped and deliberately deferred — see external_data.md.

One-paragraph summary

RootSignal is a root-cause analytics system for sales and supply operations. Given a movement in a business metric, it attributes the change to segments exactly, separates genuine performance change from shifts in demand mix, estimates the revenue involved with assumptions stated, and reports confidence against six named criteria rather than a tuned score. It is validated against five planted scenarios — classifying three of three correctly at a medium confidence floor, with zero false alarms at a high one — and runs unchanged on a million real invoice lines from a public dataset, where it beats a naive forecast baseline by 38.8% across 49 backtest folds. That dataset moves through a pipeline from S3 through a PySpark job into PostgreSQL and Redshift, with Spark's output checked against the pandas implementation on every row. Python, pandas, PySpark, SQL, AWS, Streamlit; 427 tests, including one asserting that no output ever claims causation.