Resale Analytics Warehouse

A small analytics warehouse for my fashion resale business, which I have run since I was 12. It loads item and sale data into Postgres, builds KPI tables with SQL, and trains scikit-learn models to estimate resale price and forecast demand. Everything runs locally.

The data in this repo is made up. My real sales and costs are private, so the project ships with a synthetic seed set that mimics a messy platform export. That means it runs end to end the moment you clone it, and every number and chart below comes from that made up data.

Dashboard

Every chart is generated straight from the warehouse and the trained models by python run.py --charts, which writes the images to docs/charts/. They are built from the synthetic seed data in this repo, so they look the same for anyone who clones it.

The model panel is honest about what works. The pricing model beats its baseline by a clear margin. The two demand models do not, their error lands just above the baseline line, and the chart shows that plainly rather than hiding it.

What it does

• Reads raw inventory and sales exports, cleans them, and sends bad rows to a quarantine table instead of dropping them silently.
• Builds KPIs with SQL window functions. Gross margin, ROI, sell through rate, inventory turnover, days to sell, and running inventory over time.
• Trains a pricing model and two demand models. Each one is compared against a simple baseline on a time based holdout, so any lift is honest.
• Writes a Markdown and HTML report with the headline numbers, recent trends, the top price drivers, and next month's demand forecast.
• Renders a visual dashboard of the KPIs, trends, and model results as clean PNG charts, ready to drop into a README or a slide.

Tech stack

PostgreSQL 16, Python 3.9 or newer, SQLAlchemy, pandas, pydantic, rapidfuzz, scikit-learn, joblib, and matplotlib. The full list is in requirements.txt.

Project layout

.
├── run.py                # full pipeline CLI, ingest to charts
├── config.yaml           # paths, data quality thresholds, model settings
├── docker-compose.yml    # local Postgres 16
├── db/
│   ├── connection.py     # SQLAlchemy engine from the .env file
│   └── schema.sql        # tables, date spine, platform list
├── ingestion/            # loaders, validators, cleaners, pipeline, seed data
├── sql/
│   ├── staging/          # light typing and renaming, one view per source
│   ├── intermediate/     # per item profit and loss line
│   ├── marts/            # kpi_*.sql tables
│   └── runner.py         # builds the layers in order
├── models/               # features, pricing, demand, evaluation
├── reports/
│   ├── report.py         # writes the summary report
│   └── charts.py         # draws the dashboard charts
├── docs/charts/          # dashboard images shown in this README
├── data/seed/            # synthetic inventory.csv and sales.csv
└── tests/                # cleaning, KPI, and model checks

Getting started

1. Start Postgres

Copy the example environment file and start the database container.

cp .env.example .env
docker compose up -d

Compose reads the database name, user, and password from .env, and the same file tells the Python code how to connect. The default host is localhost and the default port is 5432.

2. Set up the Python environment

python -m venv .venv
source .venv/bin/activate
pip install -r requirements.txt

3. Run the pipeline

python run.py --all

This runs the whole chain on the seed data and writes the report to reports/output/. The report is also printed as it is built.

Running one stage at a time

Every stage has its own flag. The stages always run in the order below, whatever order you pass the flags in, so a partial run still lines up with the data flow.

Flag	What it does
--ingest	Create the tables if needed, then load and clean the raw exports.
--transform	Build the staging, intermediate, and mart SQL layers.
--test	Run the data quality checks against the warehouse.
--train	Train and save the pricing and demand models.
--report	Write the Markdown and HTML report.
--charts	Draw the dashboard charts into docs/charts/.
--all	Run every stage in order.

For example, to reload data and rebuild the SQL without retraining:

python run.py --ingest --transform

Point at a different data folder with --source.

python run.py --ingest --transform --source data/raw

Adding your own data

Drop two CSV files into a folder, for example data/raw/, then run the ingest and transform stages against it.

inventory.csv needs these columns.

purchase_id, item_id, brand, category, condition, size, source_channel,
status, acquisition_date, cost_basis, fees_in, quantity

sales.csv needs these columns.

sale_id, item_id, platform, sale_price, fees_out, shipping_cost,
listed_date, sale_date

The cleaners handle mixed date formats, currency strings like $1,200.00, and messy brand and category spellings. The rules live in config.yaml, so you can adjust them without touching the code. Rows that fail validation, such as a negative price or a sale dated before its purchase, are written to the quarantine table so you can review them. To regenerate the seed data instead, run python -m ingestion.seed_data.

Database schema

The model is a small star. Two fact tables record purchases and sales, and they point at the item, platform, and date dimensions.

erDiagram
    dim_item ||--o{ fct_purchases : item_id
    dim_item ||--o{ fct_sales : item_id
    dim_platform ||--o{ fct_sales : platform_id

    dim_item {
        text item_id PK
        text brand
        text category
        text condition
        text size
        date acquisition_date
        text source_channel
        text status
    }
    dim_platform {
        int platform_id PK
        text name
        numeric fee_rate
    }
    dim_date {
        date date PK
        int year
        int month
        int quarter
        date month_start
    }
    fct_purchases {
        text purchase_id PK
        text item_id FK
        numeric cost_basis
        numeric fees_in
        date acquisition_date
        int quantity
    }
    fct_sales {
        text sale_id PK
        text item_id FK
        int platform_id FK
        numeric sale_price
        numeric fees_out
        numeric shipping_cost
        date listed_date
        date sale_date
        int days_listed
    }

Loading

dim_date is a full day by day spine used to join time series onto a complete calendar. A quarantine table holds any rows that failed validation.

Reports

The report stage writes reports/output/report.md and, on a full run, reports/output/report.html. Both cover the portfolio KPIs, the recent demand and sell through trends, each model's accuracy against its baseline, the top price drivers, and the next month demand forecast.

The charts stage writes the dashboard images shown near the top of this README into docs/charts/, built from the same KPI tables and model metrics.

Tests

pytest

The cleaning and model tests run without a database. The KPI tests need a running Postgres, so they build the warehouse from the seed data and check that the KPIs reconcile. If Postgres is not reachable those tests are skipped rather than failed.

Configuration

• .env holds the database credentials. It is not tracked in git.
• config.yaml holds the tunable settings. Data quality thresholds, cleaning rules and lookup maps, model parameters, and the training cutoff date.

VSCode extensions

• Python and Pylance for language support.
• Ruff for linting and format on save.
• A Postgres client for browsing the database, such as SQLTools with its PostgreSQL driver, or the PostgreSQL extension by Chris Kolkman.