Amazon Sales Forecasting

Amazon Sales Forecasting

ABOUT THE PROJECT

Turning volatile marketplace transactions into precision supply chain capital

In high-volume e-commerce and wholesale food distribution, gut-feel inventory management is a direct path to margin erosion. Every surplus case of perishable food sitting in a fulfillment center burns working capital; every stockout hands lifelong customers directly to rivals.

We set out to engineer a complete, production-grade analytics and machine learning pipeline that transforms millions of dollars in historical sales records into clear, forward-looking operational directives.

Starting with raw transactional ledgers, we normalized data into an enterprise star schema inside Google BigQuery—creating an immutable single source of truth. We then paired advanced SQL business intelligence (cohort retention, ABC product classification) with gradient-boosted machine learning (LightGBM) to forecast demand velocity 30 to 90 days ahead.

To bridge the gap between supply chain logistics and marketing, we applied unsupervised K-Means clustering to classify the entire buyer base into four distinct personas—allowing leadership to protect high-margin VIP accounts and automate win-back campaigns for dormant buyers.

COLLABORATOR / SPONSORExecutive LeadershipSupply Chain & Operations Steering
COLLABORATOR / SPONSORShakeel Ahamed KPrincipal Data Architect & ML Engineer
01

The Perishable Inventory Dilemma

Balancing working capital against warehouse stockout penalties

Wholesale food distribution operates on tight margins where spoilage destroys profitability. The project established a quantitative framework to forecast product velocity, allowing procurement teams to schedule replenishment orders with suppliers based on statistical run-rates rather than intuition.

Fig 4.1 — Architectural topology: transactional ingestion into BigQuery star schema.
Fig 4.1 — Architectural topology: transactional ingestion into BigQuery star schema.
02

The BigQuery Dimensional Star Schema

Normalizing flat spreadsheets into an auditable cloud data warehouse

Replaced error-prone flat tables with a normalized relational star schema: Dimension tables for Customers, Products, and Orders, centered around an indexed Fact table for Sales. Uploaded directly to Google BigQuery, enabling lightning-fast analytical queries across complex multi-table joins.

Fig 4.2 — Star schema entity relational model with primary and surrogate keys.
Fig 4.2 — Star schema entity relational model with primary and surrogate keys.
03

Advanced SQL Revenue Intelligence

Cohort retention analysis, ABC classification, and RFM scoring

Executed over 20 advanced SQL procedures utilizing Window Functions, Common Table Expressions (CTEs), and dynamic percentiles. Identified that 80% of revenue was generated by top-tier 'Category A' products and mapped month-over-month customer retention curves.

Fig 4.3 — SQL window functions computing RFM percentiles and customer retention curves.
Fig 4.3 — SQL window functions computing RFM percentiles and customer retention curves.
04

Gradient-Boosted Demand Forecasting

Predicting SKU-level velocity with LightGBM regression

Engineered predictive features including 1-month to 3-month sales lag metrics, rolling averages, and list price discount ratios. LightGBM delivered an outstanding 0.919 R² fit, outperforming linear baselines and accurately mapping non-linear order velocity across individual food categories.

Fig 4.4 — Interactive model evaluation showing LightGBM test predictions vs actual sales.
05

Macro Seasonality & S&OP Planning

Modeling the 35% Q4 holiday surge with Facebook Prophet

Decomposed macro-level sales trends to expose severe seasonal cycles: third-quarter baselines consistently surge into a 35% volume spike in Q4. Provided sales and operations planning (S&OP) teams with the lead time needed to lock in freight contracts and warehouse labor before peak season rates spike.

Fig 4.5 — Additive time-series decomposition exposing Q4 volume spikes.
Fig 4.5 — Additive time-series decomposition exposing Q4 volume spikes.
06

Unsupervised Behavioral Segmentation

Classifying four distinct buyer personas via K-Means clustering

Standardized customer purchase metrics (total spend, order frequency, recency, average discount rate) and applied PCA 2D dimensionality reduction. The K-Means model isolated four distinct commercial segments: VIP Wholesalers, Steady Repeat Buyers, Budget-Conscious Accounts, and a critical 23% cohort of At-Risk customers requiring immediate re-engagement.

Fig 4.6 — PCA scatter visualizer plotting 4 distinct behavioral customer clusters.
Fig 4.6 — PCA scatter visualizer plotting 4 distinct behavioral customer clusters.
MEASURED VELOCITY & IMPACT
DEMAND ACCURACY

0.919 R²

LightGBM gradient boosting captured non-linear price elasticity and multi-month rolling lag velocity.

SEASONAL FORESIGHT

+35% Q4 Spike

Early-warning S&OP triggers established in Q3 to secure bulk carrier capacity and prevent stockouts.

CHURN MITIGATION

23% Identified

K-Means clustering tagged dormant 90+ day accounts for targeted retention workflows before customer loss.

ARCHITECTURAL RETROSPECTIVE

What did I learn?

01

Feature engineering beats algorithm complexity every time.

Deploying temporal lag variables, rolling 3-month averages, and price elasticity ratios drove model accuracy far higher than tuning hyper-parameters on raw time-series data.

02

Physical supply chains demand non-negative domain boundaries.

Standard statistical models can mathematically predict negative sales during post-holiday slumps. Enforcing real-world business constraints and zero-bounded Bayesian priors is essential to prevent inventory planning errors.

03

Data warehousing is the true foundation of predictive AI.

Scattered flat spreadsheets create data leakage and calculation drift. Normalizing data into an auditable dimensional star schema in BigQuery cut analytical query times from minutes to sub-second execution.

Let's work together!

Partnering with ambitious founders and businesses to design, build, and scale world-class digital products.

Whether you're taking a traditional business online, launching an ambitious new product, or fixing operational bottlenecks with intelligent software, let's talk directly at

Keep scrolling or click to go next project

AWS Streaming Pipeline

AWS Streaming Pipeline

AWS Streaming Pipeline