Project Overview and Business Context
The client was a UK-based e-commerce retailer with approximately 2,000 active SKUs across home furnishings and garden categories. Their replenishment process was driven by a category manager who reviewed sales reports weekly, checked warehouse stock levels, and placed orders based on experience and supplier lead time knowledge. The process worked reasonably well for core SKUs with stable demand, but failed during seasonal transitions, promotional periods, and when new SKUs lacked sufficient sales history to inform intuitive judgment. The brief was to build a forecasting tool that the category manager could use without data science expertise: upload sales history, review AI-generated forecasts with confidence intervals, see the top risks to forecast accuracy flagged in plain English, and export replenishment recommendations to a spreadsheet for purchase order preparation. The complexity of the build is at the data processing and modelling layer. The UI can be relatively simple because the audience is a single category manager who wants reliable recommendations, not a fully featured BI dashboard. AWS was chosen for the compute layer because of the client's existing AWS infrastructure and the need for reliable batch processing of the weekly forecast run without persistent server costs.
Technical Architecture and Stack Decisions
The system architecture separates the weekly batch forecasting run from the interactive user interface. The batch pipeline runs on an AWS Lambda function triggered by an EventBridge weekly schedule. It ingests sales data from PostgreSQL (loaded from the client's Shopify export via a daily ETL job), applies data quality checks, and processes each SKU through the forecasting pipeline. The forecasting pipeline uses a Python implementation of STL (Seasonal-Trend decomposition using LOESS) for time series decomposition, extracting trend and seasonal components from the sales history. Forecasts are generated using an ensemble of two methods: a simple Exponential Smoothing model (strong baseline for stable SKUs) and a Facebook Prophet model (handles seasonality and holiday effects). The ensemble weights are calibrated per SKU based on historical holdout accuracy. For new SKUs with fewer than 12 weeks of history, the pipeline falls back to cluster-based forecasting: grouping the new SKU with similar established SKUs based on category, price band, and seasonal pattern, and borrowing the cluster's forecast. GPT-4o receives the per-SKU forecast outputs, confidence intervals, current stock level, and supplier lead time, and generates a buyer narrative summary: the forecast recommendation, the key drivers (seasonal uplift expected, promotional risk, low history warning), and flagged SKUs requiring buyer attention this week. The Next.js frontend presents the weekly report with a sortable SKU table, individual SKU forecast charts, and the GPT-4o narrative at the top of each report.
Key AI and ML Components
The forecasting models (STL decomposition, Exponential Smoothing, Prophet) are the core ML components. They are statistical time series models, not LLMs, and are appropriate for this task because demand forecasting requires numerical precision and consistent reproducibility that LLMs cannot provide. GPT-4o contributes the interpretation and communication layer: given structured forecast data, it generates the buyer-facing narrative that translates statistical outputs into plain-English buying guidance. The narrative prompt includes the SKU name, current stock in weeks cover, forecast demand for the next 8 weeks, the 80% confidence interval, the identified seasonal component magnitude, and any flags raised by the data quality checks (missing data periods, outlier weeks, recent trend breaks). GPT-4o is instructed to produce a two-to-three sentence recommendation per flagged SKU and a one-paragraph executive summary for the weekly report. This layer is what makes the tool accessible to a category manager without statistical training: they can read the AI narrative to understand the key actions without needing to interpret confidence interval charts themselves. The ensemble calibration process, which selects weights for the two forecasting models per SKU based on historical holdout accuracy, runs as part of each weekly batch job, automatically adjusting as more sales history accumulates.
Challenges Solved and How
Sparse data is the most common challenge in retail demand forecasting. New product launches, discontinued SKUs, and products with strong seasonal concentration (Christmas decorations, garden furniture) all produce sales histories that break standard time series assumptions. The pipeline handles this through the cluster-based fallback for low-history SKUs, conservative confidence intervals that widen automatically with less history, and explicit data quality flags in the GPT-4o narrative when a forecast is based on limited data. External event integration was a specific client requirement: promotional events, bank holidays, and planned marketing campaigns affect demand significantly and are not captured in historical sales data alone. The pipeline accepts a manually maintained events calendar from the category manager, and Prophet's built-in regressors incorporate holiday and event effects into the forecast. Shopify export format changes broke the ETL pipeline twice during the pilot period. The ingestion layer was refactored with schema validation on every import and an alert to the client if the column structure changes, preventing silent data quality failures from reaching the forecasting models.
Outcome and Measurable Results
The client ran the forecasting MVP for one full seasonal cycle covering the spring-summer and Christmas planning periods. Mean absolute percentage error (MAPE) across the 200 core SKUs with at least 52 weeks of history was 14.3%, compared to a MAPE of 22.1% for the category manager's previous manual forecast process on the same SKUs. Overstock events (weeks where stock cover exceeded 12 weeks) fell by 31% in the forecast-assisted period versus the prior year. Stockout events (zero stock for a listed product) fell by 24%. The category manager reported spending approximately 2 hours per week reviewing and acting on AI forecasts, compared to 6 hours previously on manual analysis. The GPT-4o narrative summaries were cited as the most valued feature: the ability to review AI-generated written recommendations for the week's most important SKU decisions, rather than scanning tables of numbers, made the tool practical for a buyer with limited statistical background.
Lessons for Similar Projects
Use the right tool for the job. Demand forecasting requires statistical time series models, not LLMs, for the numerical prediction layer. LLMs contribute communication and interpretation, not forecasting. Confusing these roles leads to unreliable numerical outputs. Invest in data quality infrastructure early. The ETL pipeline, data validation, and outlier detection are unglamorous but determine forecast quality. A forecasting system is only as good as its input data. Build the events calendar integration before launch. Promotional periods, bank holidays, and seasonal events are predictable demand drivers that statistical models cannot infer from historical data alone. Getting that calendar integration right is often worth more than model sophistication. Design for the buyer's workflow, not for statistical elegance. A MAPE of 14% that the buyer can act on efficiently is more valuable than a MAPE of 12% buried in a dashboard they cannot navigate quickly.