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.
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.

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.

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.

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.
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.

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.

0.919 R²
LightGBM gradient boosting captured non-linear price elasticity and multi-month rolling lag velocity.
+35% Q4 Spike
Early-warning S&OP triggers established in Q3 to secure bulk carrier capacity and prevent stockouts.
23% Identified
K-Means clustering tagged dormant 90+ day accounts for targeted retention workflows before customer loss.
What did I learn?
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.
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.
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.
