Building an AI‑driven inventory forecasting tool for a micro‑retail shop in six weeks

Week 1 – Define scope and gather data

Goal: Understand the shop’s inventory pain points and collect the raw data needed for forecasting.

1. Stakeholder interview – Talked to the owner and part‑time staff to list:

   • Products sold (≈150 SKUs)
   • Sales frequency (daily receipts)
   • Lead times from suppliers (varies 2‑14 days)
   • Current reorder rule (manual “when shelf looks low”)

2. Data sources – Identified three usable streams:

   • Point‑of‑sale (POS) export CSV (date, SKU, quantity, price)
   • Supplier purchase orders PDF (converted to CSV with tabula-py)
   • Calendar file (holidays, local events)

3. Data extraction script – Wrote a Python script using pandas to:

   • Normalize date columns to UTC
   • Fill missing sales days with zeros (important for intermittent demand)
   • Merge POS and purchase data on SKU and date

4. Storage – Loaded the cleaned dataset into a local SQLite database for quick querying during experimentation.

Outcome: A reproducible ETL pipeline that runs nightly via a simple cron job, producing a table sales_daily(sku, date, qty, promo_flag).

Week 2 – Exploratory analysis and baseline

Goal: Quantify demand patterns and establish a naïve forecast to beat.

1. Time‑series plots – For top 20 SKUs, plotted daily sales; observed:

   • Strong weekly seasonality (higher on weekends)
   • Sporadic spikes tied to local events
   • Many SKUs with zero‑inflated distribution

2. Statistical summaries – Computed:

   • Mean daily demand per SKU
   • Coefficient of variation (CV)
   • Intermittent demand index (IDI = proportion of zero days)

3. Baseline model – Implemented a simple moving average (SMA) of the last 7 days and a seasonal naïve (last same weekday) forecast.

4. Evaluation metric – Chose Mean Absolute Percentage Error (MAPE) on a hold‑out last 4 weeks. Baseline MAPE ranged from 22% (fast‑moving) to 68% (slow‑moving).

Outcome: Clear evidence that a model capable of capturing weekly seasonality and handling zeros would improve accuracy.

Week 3 – Model selection and prototyping

Goal: Test a few candidate algorithms and pick the best trade‑off between accuracy and interpretability.

Model	Library	Handles seasonality?	Zero‑inflated?	Training time (per SKU)	Validation MAPE (avg)
Prophet	prophet	Yes (additive)	No (needs tweak)	~2s	18%
ARIMA	statsmodels	Yes (via differencing)	No	~1.5s	20%
Random Forest Regressor	scikit‑learn	No (feature‑engineered)	Yes (via lag features)	~0.8s	16%
Gradient Boosting (XGBoost)	xgboost	No	Yes	~1s	15%
DeepAR (PyTorch)	pytorch-forecasting	Yes	Yes	~5s	14%

Decision:

• For the majority of SKUs (≈80) with enough data (>60 days), we chose Prophet because it provides:
  • Automatic weekly seasonality detection
  • Easy addition of holidays/events as regressors
  • Interpretable trend and seasonality components
• For low‑volume SKUs (<10 sales/month) we fell back to a zero‑inflated Poisson model implemented via statsmodels’ ZeroInflatedPoisson.

Implementation: Wrapped each model in a forecast_sku(sku_id, horizon=14) function that returns a pandas DataFrame with date, yhat, yhat_lower, yhat_upper.

Week 4 – Feature engineering and model tuning

Goal: Improve Prophet performance and reduce over‑fitting on noisy SKUs.

1. Regressors – Added:

   • promo_flag (from POS)
   • temperature_avg (open‑weather API, optional)
   • is_holiday (custom calendar)

2. Seasonality adjustments – Turned off daily seasonality (too noisy) and kept weekly + yearly.

3. Hyperparameter search – Used a simple grid over changepoint_prior_scale (0.001, 0.01, 0.1) and seasonality_prior_scale (5, 10, 15). Selected the combo minimizing MAPE on a rolling‑origin validation (train 8 weeks, test 2 weeks, slide).

4. Zero‑inflated Poisson tuning – Estimated psi (zero inflation) and mu (count) via maximum likelihood; used statsmodels’ fit(method='nm').

Outcome:

• Prophet MAPE dropped to 13.4% (average across top 50 SKUs).
• Zero‑inflated Poisson achieved 21% MAPE for the tail SKUs, acceptable given low volume.

Week 5 – System integration

Goal: Turn the forecasting functions into a service the shop can call from their existing inventory spreadsheet.

1. API layer – Built a minimal FastAPI app with two endpoints:

   • POST /forecast – accepts { "sku": "ABC123", "horizon": 14 } and returns JSON forecast.
   • GET /health – simple liveness check.

2. Dependency isolation – Containerized the app with Docker:

   FROM python:3.11-slim
   WORKDIR /app
   COPY requirements.txt .
   RUN pip install --no-cache-dir -r requirements.txt
   COPY . .
   CMD ["uvicorn", "main:app", "--host", "0.0.0.0", "--port", "8000"]
   

   requirements.txt pinned prophet, fastapi, uvicorn, pandas, scikit-learn, xgboost.

3. Authentication – Since the shop runs the service on a local Raspberry Pi, we used a simple API key header (X-API-Key) checked against an environment variable.

4. Scheduled updates – Added a cron job that runs the ETL script, then calls /forecast for each SKU and writes results to a local forecasts table in SQLite.

5. Front‑end hook – Exported the forecast table as a CSV nightly; the shop owner imports it into Google Sheets via the “File → Import” menu, replacing the previous month’s forecast sheet.

Outcome: A self‑contained service that can be started with docker compose up -d and provides forecasts within <200ms per request.

Week 6 – Testing, rollout, and documentation

Goal: Verify accuracy in production, train the owner, and leave a clear hand‑off.

1. Live test – Ran the system in parallel with the manual process for two weeks. Recorded:

   • Stock‑outs reduced from 3 incidents/week to 0.5/week.
   • Overstock (excess inventory > 2 weeks of demand) decreased by 18%.

2. Error monitoring – Added a simple logging middleware that writes forecast errors (actual vs predicted) to a log file; reviewed weekly to detect drift.

3. User guide – Created a markdown README.md covering:

   • How to start/stop the service
   • How to add a new SKU (just ensure it appears in the POS export)
   • How to adjust holidays/events (edit holidays.csv)
   • How to retrain models (run python train_all.py)

4. Knowledge transfer – Held a 30‑minute walkthrough with the owner, demonstrating:

   • Where to find the forecast CSV
   • How to interpret the confidence interval bands
   • When to override the suggestion (e.g., sudden supplier delay)

5. Fall‑back plan – Documented the manual SMA method as a backup if the service ever fails.

Outcome: After six weeks the shop has a repeatable, automated forecasting pipeline that cuts stock‑outs by ~80% and requires <1 hour of weekly maintenance from the owner.