TRACE — Retail Revenue Investigation Lab

“What changed in recorded revenue, which transactions explain that change, and how sensitive is the result to our assumptions?”

TRACE is a retail revenue investigation lab built with React, TypeScript, DuckDB-Wasm, and Python. It transforms 1,067,371 historical transaction records into an auditable, interactive analytics workspace where any chart variance leads directly to the underlying transaction evidence, analytical SQL, and inclusion governance rules.

Designed for a first-year BCA Data Analytics portfolio demonstrating deep analytical judgment, denominator discipline, reproducible data engineering, and zero-paid-service client architecture.

---

Visual Walkthrough

Recorded revenue timeline, 4 top-line KPIs, and variance decomposition waterfall bridge.

Auditable transaction ledger and in-browser SQL Calculation Drawer powered by DuckDB-Wasm.

---

Key Features & Demonstrated Skills

Capability	Engineering & Analytical Substance
Reproducible Source-to-Parquet	Python, Pandas, and PyArrow pipeline ingests the official UCI Online Retail II raw Excel sheets, computes SHA-256 manifests, and exports compact columnar Parquet.
In-Browser Analytical SQL	DuckDB-Wasm runs in a dedicated Web Worker, executing complex analytical SQL directly in the client with sub-50ms warm filter query latencies.
100% Reconciliation Invariant	Mutually exclusive disposition buckets (POS_SALE, CANCELLATION, INVALID_PRICE, NON_MERCH) reconcile exactly to the 1,067,371 raw source records.
Variance Decomposition Bridge	Additive current-minus-prior revenue variance decomposed by country or product with an explicit "Other" bucket guaranteeing penny-perfect reconciliation.
Denominator Discipline	22.77% of rows lack Customer IDs. Preserved in Gross/Net revenue totals (guest checkouts) but strictly excluded from customer retention denominators.
Customer RFM & Retention Cohorts	Quintile RFM segmentation and monthly acquisition cohort heatmaps distinguishing unobserved future periods from observed 0% repeat rates.
Transparent What-If Scenarios	Relative cancellation reduction sensitivity slider ($r \in [0%, 50%]$) with explicit mathematical contracts segregated from historical observed KPIs.
Zero-Paid-Service Architecture	Zero runtime APIs, no backend database bill, no credit card required. Universal static deployment to GitHub Pages.

---

Architectural Flow

[ UCI Online Retail II: 1,067,371 rows across 2 Excel Sheets ]
                        │
                        ▼ (Python + Pandas + PyArrow)
[ pipeline/prepare_data.py: Normalization, Classification & SHA-256 Manifest ]
                        │
                        ├──────────────────────────┐
                        ▼                          ▼
           public/data/trace_demo.parquet    public/data/trace_full.parquet
              (224,250 rows • 1.51 MB)          (1,067,371 rows • 7.46 MB)
                        │
                        ▼ (Client Browser Web Worker)
           [ DuckDB-Wasm Engine (Non-Threaded EH Bundle) ]
                        │
                        ▼ (Parameterized SQL Registry: sql/queries.ts)
     ┌──────────────────┼──────────────────┬──────────────────┐
     ▼                  ▼                  ▼                  ▼
 Overview View     Investigate View  Customers View     Scenarios View
(Timeline/Bridge) (Evidence & SQL)  (Cohorts & RFM)   (What-If Sensitivity)

---

Getting Started & Local Execution

Prerequisites

• Node.js: v18+ (tested on Node v24.14.1)
• PowerShell / Bash
• Python: Optional (only required to rebuild Parquet from raw UCI Excel; shipped demo Parquet runs immediately without Python).

1. Installation

# Clone or open project directory
cd d:\Ran\trace

# Install dependencies
npm install

2. Development Server

npm run dev

Open http://localhost:5173 in your browser.

3. Production Build & Static Preview

npm run build
npm run preview

Open http://localhost:4173.

4. Running the Metric Invariant Test Suite

npm run test

Executes 8 automated contract verification tests in tests/metric_contract.test.js.

---

Full Dataset Rebuild (Optional)

To regenerate the Parquet assets from the official UCI Machine Learning Repository:

# Using uv (recommended fast standalone runner):
uv run --with pandas --with duckdb --with pyarrow --with openpyxl python pipeline/prepare_data.py

# Or using standard python:
pip install pandas duckdb pyarrow openpyxl requests
python pipeline/prepare_data.py

---

Free GitHub Pages Deployment

TRACE is configured with base: './' in vite.config.ts, making it deployable to GitHub Pages or any static web host without custom server configurations.

1. Push this repository to GitHub.
2. In GitHub repository settings, navigate to Pages.
3. Under Build and deployment, select GitHub Actions and use the standard static Vite deployment workflow, or run:

   npm run build

   and deploy the dist/ folder to the gh-pages branch.

---

Verified Dataset Attribution & License

• Source Dataset: Online Retail II
• Repository: UCI Machine Learning Repository
• DOI: 10.24432/C5CG6D
• License: Creative Commons Attribution 4.0 International (CC BY 4.0)
• Attribution Statement: The source data reflects real transactions from a UK-based online gifts and homewares retailer between 01/12/2009 and 09/12/2011. Prices are in British Pounds (GBP / £).