E-commerce Demand Forecasting

Retailers globally lose $1.1 trillion per year from inventory distortion — the combined cost of overstocking and stockouts. This figure, reported by IHL Group, represents one of the largest addressable inefficiencies in the global economy. For a mid-size e-commerce retailer with $200M in annual revenue, poor demand forecasting can erode 8–12% of gross margin.

The Scale of Impact

Overstocking ties up working capital, increases warehousing costs, and inevitably leads to markdowns that destroy margin. Fashion retailers markdown 30–40% of inventory every season. Perishable goods face even worse outcomes — grocery retailers discard $18.2 billion worth of unsold food annually in the US alone.

Stockouts are equally devastating. When a customer arrives at your store (physical or digital) and finds the product unavailable, 21–43% will purchase from a competitor instead. Beyond the immediate lost sale, stockouts erode brand trust and customer lifetime value. Amazon attributes 35% of its revenue to demand-driven recommendations and inventory optimization — a competitive moat built on forecasting accuracy.

Limitations of Traditional Methods

Legacy forecasting approaches — moving averages, exponential smoothing, basic ARIMA — were designed for a simpler retail landscape. They fail in modern e-commerce because:

Challenge	Traditional Methods	ML-Based Approach
SKU Scale (10,000+)	Manual tuning per series; doesn't scale	Automated hyperparameter search across all series
Multiple Seasonality	Single seasonal period only	Daily, weekly, monthly, yearly patterns simultaneously
External Regressors	Limited or no support	Promotions, weather, holidays, economic indicators
Non-linear Interactions	Assumes linear relationships	Tree-based and neural models capture complex patterns
Cold-start Products	Requires long history	Transfer learning from similar products
Promotion Cannibalization	Not modeled	Cross-item features capture substitution effects

Modern e-commerce generates petabytes of transactional, behavioral, and contextual data. The opportunity is to feed all of this into scalable ML pipelines that forecast demand at the SKU x location x day granularity — exactly what GCP's managed ML services are built for.

The architecture follows GCP best practices for ML pipelines: ingest into Cloud Storage, transform and model in BigQuery, orchestrate training with Vertex AI, serve batch predictions via scheduled queries, and monitor with Vertex AI Model Monitoring.

End-to-End Pipeline Diagram

GCP Components Overview

Every component in this pipeline is a fully managed GCP service. There are no VMs to maintain, no clusters to tune, and no infrastructure to patch. The entire pipeline scales automatically with data volume and can be deployed in under two weeks.

Component	GCP Service	Role in Pipeline
Raw Storage	Cloud Storage	Landing zone for POS exports, promotion calendars, external data feeds
Data Warehouse	BigQuery	Central store for all sales history, feature tables, and prediction outputs
Feature Management	Vertex AI Feature Store	Versioned, point-in-time correct feature serving for training and inference
Statistical Model	BigQuery ML ARIMA_PLUS	Automated time-series forecasting with seasonality detection and holiday effects
Custom Model	Vertex AI Training	Prophet and LightGBM ensembles for complex non-linear patterns
Orchestration	Cloud Composer / Scheduler	Daily pipeline triggers, dependency management, failure alerting
Monitoring	Vertex AI Model Monitoring	Data drift detection, prediction skew alerts, feature attribution drift
Visualization	Looker / Data Studio	Inventory planning dashboards, forecast accuracy reports, alert summaries

Data Sources & Ingestion

Demand forecasting quality is directly proportional to the richness of input signals. We combine six data sources to give the model comprehensive context for each SKU and each day:

1. POS / Transaction Data — The core signal. Daily unit sales per SKU per store, including returns and exchanges. Two years of history provides enough cycles to capture annual seasonality patterns.

2. Promotion Calendar — Discount depth, promotion type (BOGO, percentage-off, bundle), channel (email, display, in-store), and duration. Promotions can cause 3–10x demand spikes and their effects must be explicitly modeled.

3. Holiday Calendar — National holidays, regional events, school schedules. These create predictable demand shifts that differ by product category.

4. Weather Data — Temperature, precipitation, and severe weather alerts from NOAA or OpenWeatherMap API. Critical for seasonal categories (apparel, outdoor, beverages).

5. Inventory Snapshots — Daily on-hand quantities. Essential for distinguishing true zero-demand days from stockout-induced zeros (censored demand).

6. Web Traffic — Product page views, search queries, add-to-cart events from Google Analytics. Leading indicators that predict demand 1–3 days ahead.

BigQuery Schema Design

We organize data into a star schema optimized for time-series queries. The fact table is partitioned by sale_date and clustered by sku_id for efficient range scans during training.

-- Core sales fact table (partitioned + clustered)
CREATE TABLE demand_forecast.daily_sales (
  sale_date       DATE,
  sku_id          STRING,
  store_id        STRING,
  units_sold      INT64,
  revenue         FLOAT64,
  unit_price      FLOAT64,
  on_hand_qty     INT64,
  is_promotion    BOOL,
  promo_discount  FLOAT64,
  promo_type      STRING,
  category        STRING,
  subcategory     STRING
)
PARTITION BY sale_date
CLUSTER BY sku_id, store_id
OPTIONS(
  description = 'Daily sales fact table for demand forecasting',
  require_partition_filter = TRUE
);

-- External regressors table
CREATE TABLE demand_forecast.external_features (
  feature_date    DATE,
  store_id        STRING,
  is_holiday      BOOL,
  holiday_name    STRING,
  temp_max_f      FLOAT64,
  temp_min_f      FLOAT64,
  precipitation   FLOAT64,
  day_of_week     INT64,
  week_of_year    INT64,
  month           INT64,
  is_weekend      BOOL,
  is_payday       BOOL
)
PARTITION BY feature_date
CLUSTER BY store_id;

Feature Engineering in BigQuery SQL

Feature engineering is the single highest-leverage activity in demand forecasting. We create features directly in BigQuery SQL for maximum efficiency — no data movement, no external compute, and the features are automatically versioned as views.

-- Rolling window features (7d, 14d, 28d)
CREATE OR REPLACE VIEW demand_forecast.features_rolling AS
SELECT
  sale_date,
  sku_id,
  store_id,
  units_sold,

  -- Lag features (previous day/week/month sales)
  LAG(units_sold, 1) OVER (w) AS lag_1d,
  LAG(units_sold, 7) OVER (w) AS lag_7d,
  LAG(units_sold, 28) OVER (w) AS lag_28d,

  -- Rolling averages
  AVG(units_sold) OVER (w ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS avg_7d,
  AVG(units_sold) OVER (w ROWS BETWEEN 13 PRECEDING AND CURRENT ROW) AS avg_14d,
  AVG(units_sold) OVER (w ROWS BETWEEN 27 PRECEDING AND CURRENT ROW) AS avg_28d,

  -- Rolling std deviation (volatility signal)
  STDDEV(units_sold) OVER (w ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS std_7d,

  -- Trend: ratio of short to long moving average
  SAFE_DIVIDE(
    AVG(units_sold) OVER (w ROWS BETWEEN 6 PRECEDING AND CURRENT ROW),
    AVG(units_sold) OVER (w ROWS BETWEEN 27 PRECEDING AND CURRENT ROW)
  ) AS trend_ratio,

  -- Same day last year
  LAG(units_sold, 364) OVER (w) AS lag_52w

FROM demand_forecast.daily_sales
WINDOW w AS (PARTITION BY sku_id, store_id ORDER BY sale_date);

-- Promotion interaction features
CREATE OR REPLACE VIEW demand_forecast.features_promo AS
SELECT
  f.*,
  e.is_holiday,
  e.temp_max_f,
  e.precipitation,
  e.is_weekend,
  e.is_payday,

  -- Days since last promotion
  DATE_DIFF(
    f.sale_date,
    MAX(IF(f.is_promotion, f.sale_date, NULL)) OVER (
      PARTITION BY f.sku_id ORDER BY f.sale_date
      ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
    ),
    DAY
  ) AS days_since_promo,

  -- Promotion + weekend interaction
  CAST(f.is_promotion AND e.is_weekend AS INT64) AS promo_weekend,

  -- Holiday + promotion interaction
  CAST(f.is_promotion AND e.is_holiday AS INT64) AS promo_holiday

FROM demand_forecast.features_rolling f
LEFT JOIN demand_forecast.external_features e
  ON f.sale_date = e.feature_date AND f.store_id = e.store_id;

We train two complementary model families and ensemble their predictions. The statistical model (BQML ARIMA_PLUS) provides robust baselines with interpretable components; the ML model (Vertex AI custom with LightGBM) captures non-linear feature interactions. This dual approach hedges against the weaknesses of either approach alone.

BigQuery ML ARIMA_PLUS

ARIMA_PLUS is BigQuery ML's flagship time-series model. Under the hood, it combines ARIMA with automatic seasonality detection (Fourier terms), holiday effects, spike-and-dip detection, and step-change adaptation. It automatically performs differencing, selects (p,d,q) orders via AIC, and handles multiple seasonal periods.

-- Train ARIMA_PLUS model with external regressors
CREATE OR REPLACE MODEL demand_forecast.arima_demand_model
OPTIONS(
  model_type = 'ARIMA_PLUS',
  time_series_timestamp_col = 'sale_date',
  time_series_data_col = 'units_sold',
  time_series_id_col = 'sku_id',
  auto_arima = TRUE,
  data_frequency = 'DAILY',
  holiday_region = 'US',
  clean_spikes_and_dips = TRUE,
  adjust_step_changes = TRUE,
  decompose_time_series = TRUE
) AS
SELECT
  sale_date,
  sku_id,
  units_sold
FROM demand_forecast.daily_sales
WHERE sale_date BETWEEN '2022-01-01' AND '2023-12-31';

-- Inspect model coefficients and diagnostics
SELECT *
FROM ML.ARIMA_COEFFICIENTS(
  MODEL demand_forecast.arima_demand_model
)
ORDER BY sku_id;

-- Evaluate model fit statistics (AIC, variance, etc.)
SELECT *
FROM ML.ARIMA_EVALUATE(
  MODEL demand_forecast.arima_demand_model
);

Vertex AI Custom Training (Prophet + LightGBM)

For capturing non-linear interactions between features (e.g., how promotion effect varies by day-of-week and weather), we train a custom model on Vertex AI. The training script combines Prophet decomposition with a LightGBM model that uses Prophet's trend and seasonality as additional features alongside our engineered feature set.

# Vertex AI custom training script (simplified)
import pandas as pd
from prophet import Prophet
import lightgbm as lgb
from google.cloud import bigquery, aiplatform

# Load features from BigQuery
client = bigquery.Client()
query = """
  SELECT * FROM demand_forecast.features_promo
  WHERE sale_date BETWEEN '2022-01-01' AND '2023-12-31'
"""
df = client.query(query).to_dataframe()

# Train Prophet for decomposition
prophet_model = Prophet(
    yearly_seasonality=True,
    weekly_seasonality=True,
    daily_seasonality=False,
    holidays=holidays_df,
    changepoint_prior_scale=0.05
)
prophet_model.add_regressor('is_promotion')
prophet_model.add_regressor('temp_max_f')
prophet_model.fit(train_df)

# Extract Prophet components as features for LightGBM
components = prophet_model.predict(train_df)
train_features['prophet_trend'] = components['trend']
train_features['prophet_yearly'] = components['yearly']
train_features['prophet_weekly'] = components['weekly']

# Train LightGBM on full feature set + Prophet components
lgb_model = lgb.LGBMRegressor(
    n_estimators=500,
    learning_rate=0.05,
    max_depth=8,
    num_leaves=63,
    min_child_samples=20,
    subsample=0.8,
    colsample_bytree=0.8,
    reg_alpha=0.1,
    reg_lambda=0.1
)
lgb_model.fit(X_train, y_train)

# Register model in Vertex AI Model Registry
aiplatform.init(project='my-project', location='us-central1')
model = aiplatform.Model.upload(
    display_name='demand-forecast-lgbm-v1',
    artifact_uri='gs://my-bucket/models/demand-lgbm/',
    serving_container_image_uri='us-docker.pkg.dev/vertex-ai/prediction/sklearn-cpu.1-0:latest'
)

Evaluation Metrics

We evaluate forecasting models on multiple metrics because no single metric tells the full story. MAPE is the primary business metric (interpretable as "percentage error"), but we also track RMSE (penalizes large errors), bias (detects systematic over/under-prediction), and coverage (prediction interval reliability).

-- Evaluate ARIMA_PLUS forecasts in BigQuery
SELECT
  sku_id,
  AVG(ABS(actual - forecast) / NULLIF(actual, 0)) * 100 AS mape,
  SQRT(AVG(POW(actual - forecast, 2))) AS rmse,
  AVG((forecast - actual) / NULLIF(actual, 0)) * 100 AS bias_pct,
  COUNTIF(
    actual BETWEEN forecast_lower AND forecast_upper
  ) / COUNT(*) * 100 AS coverage_95
FROM demand_forecast.holdout_evaluation
GROUP BY sku_id;

Model Comparison

Model	MAPE	RMSE	Bias	Coverage (95%)	Training Cost
Legacy (Moving Avg)	28.4%	142	+6.2%	71%	$0
BQML ARIMA_PLUS	15.2%	78	+1.1%	91%	~$5
Vertex AI LightGBM	13.8%	71	-0.8%	88%	~$15
Ensemble (ARIMA + LightGBM)	12.1%	63	+0.2%	94%	~$20

Batch Prediction Pipeline

Demand forecasting is a batch use case — predictions are consumed by inventory planning systems once per day. We use BigQuery scheduled queries for the ARIMA_PLUS model and Vertex AI Batch Prediction for the custom model, orchestrated by Cloud Scheduler.

-- Generate 14-day ahead forecasts with ARIMA_PLUS
SELECT *
FROM ML.FORECAST(
  MODEL demand_forecast.arima_demand_model,
  STRUCT(
    14 AS horizon,
    0.95 AS confidence_level
  )
);

-- Scheduled query: daily forecast refresh + write to predictions table
CREATE OR REPLACE TABLE demand_forecast.latest_predictions AS
WITH arima_preds AS (
  SELECT
    forecast_timestamp AS forecast_date,
    time_series_id_col AS sku_id,
    forecast_value AS arima_forecast,
    prediction_interval_lower_bound AS arima_lower,
    prediction_interval_upper_bound AS arima_upper
  FROM ML.FORECAST(MODEL demand_forecast.arima_demand_model,
       STRUCT(14 AS horizon, 0.95 AS confidence_level))
),
lgbm_preds AS (
  SELECT forecast_date, sku_id, lgbm_forecast
  FROM demand_forecast.lgbm_batch_predictions
),
ensemble AS (
  SELECT
    a.forecast_date,
    a.sku_id,
    a.arima_forecast,
    l.lgbm_forecast,
    -- Weighted ensemble: 40% ARIMA + 60% LightGBM
    ROUND(0.4 * a.arima_forecast + 0.6 * l.lgbm_forecast) AS ensemble_forecast,
    a.arima_lower,
    a.arima_upper,
    CURRENT_TIMESTAMP() AS generated_at
  FROM arima_preds a
  JOIN lgbm_preds l USING (forecast_date, sku_id)
)
SELECT * FROM ensemble;

Model Monitoring

Forecasting models degrade over time as consumer behavior shifts, new competitors emerge, and macroeconomic conditions change. We implement three layers of monitoring:

1. Data Drift Detection — Vertex AI Model Monitoring tracks statistical distribution changes in input features. If the distribution of promo_discount or temp_max_f shifts beyond a configurable threshold (Jensen-Shannon divergence > 0.1), an alert triggers automatic retraining.

2. Prediction Skew Monitoring — Compare the distribution of predictions against the distribution of actuals (once actuals become available). A consistent bias in one direction signals model decay.

3. Business Metric Tracking — Track rolling MAPE, stockout rate, and overstock rate in a Looker dashboard. Set automated alerts when MAPE exceeds 18% (our retraining threshold) for more than 3 consecutive days.

# Configure Vertex AI Model Monitoring
from google.cloud import aiplatform

monitoring_job = aiplatform.ModelDeploymentMonitoringJob.create(
    display_name='demand-forecast-monitor',
    endpoint=endpoint,
    logging_sampling_strategy={
        'random_sample_config': {'sample_rate': 1.0}
    },
    schedule_config={
        'monitor_interval': {'seconds': 86400}  # Daily
    },
    alert_config={
        'email_alert_config': {
            'user_emails': ['ml-team@company.com']
        }
    },
    objective_configs=[{
        'training_dataset': training_dataset,
        'training_prediction_skew_detection_config': {
            'skew_thresholds': {
                'promo_discount': {'value': 0.1},
                'temp_max_f': {'value': 0.15},
                'units_sold': {'value': 0.1}
            }
        }
    }]
)

After deploying the ensemble forecasting pipeline for a mid-size e-commerce retailer ($200M annual revenue, 12,000 SKUs, 45 fulfillment locations), we observed the following results over a 6-month measurement period:

Moving from a proof-of-concept to a production-grade forecasting system introduces challenges that are rarely addressed in tutorials. These are the real-world issues that separate deployed ML systems from notebook experiments.

Cold-Start Problem for New Products

New SKUs have no sales history. You cannot train a time-series model on zero data. Solutions include: (a) category-level models that forecast at the subcategory level and allocate to new SKUs based on attribute similarity; (b) transfer learning from analogous products identified by product embedding similarity; (c) Bayesian priors from the product's category that get updated as sales data accumulates (typically 2–4 weeks to converge to the product-level model).

Handling Novel Promotions

When the marketing team creates a promotion type the model has never seen (e.g., a new "flash sale" format), the model's promotion features cannot capture its effect. Mitigation: encode promotions as a combination of discount depth + channel + duration rather than categorical promotion types. This allows the model to generalize to new promotion formats by decomposing them into known dimensions.

Multi-Store Forecasting

Training separate models per store is computationally expensive and statistically weak for low-volume stores. A hierarchical approach is preferred: train a global model with store-level embeddings, then reconcile forecasts top-down (total → region → store) using the MinT (Minimum Trace) reconciliation method to ensure coherent forecasts across all levels.

Cost Optimization

BigQuery ML costs scale with data processed. Optimization strategies: (a) use partitioned tables and always filter by partition column; (b) purchase flat-rate BigQuery slots ($2,000/month for 500 slots) if you run multiple ML workloads; (c) use incremental training (WARM_START in BQML) to avoid reprocessing the full history on every retrain; (d) set maximum_bytes_billed on all queries to prevent runaway costs.

Real-Time vs. Batch Predictions

Most demand forecasting is batch (daily). However, some use cases require near-real-time adjustments: flash sales, sudden weather events, or viral social media mentions. For these, implement a two-layer architecture: the daily batch model provides the baseline forecast, and a lightweight online model (hosted on Vertex AI endpoints) applies real-time adjustments based on streaming signals from Pub/Sub.

Cannibalization Effects

Promoting Product A may steal sales from Product B (substitute) or boost sales of Product C (complement). Ignoring cannibalization leads to systematically overestimating the total inventory need. Model this by adding cross-item features: promotion status of substitute/complement products as regressors. Identify substitutes and complements through market basket analysis or product embedding similarity.

Censored Demand

When a product stocks out, observed sales = 0, but true demand > 0. Training on censored data causes systematic underforecasting. Solutions: (a) filter out stockout days (risky — loses data); (b) impute censored demand using the average of nearby non-stockout days; (c) use Tobit regression or survival models that explicitly model censoring.

This use case exercises many of the core competencies tested on the GCP Professional Machine Learning Engineer certification. Here are the key skills demonstrated:

Fork & Extend

Turn this notebook into a portfolio project in 5 steps:

1. Fork the notebook — Clone the repo and open in Google Colab or locally.
2. Swap in real data — Replace the synthetic dataset with the Walmart Store Sales dataset from Kaggle (45 stores, 3 years of weekly sales with holidays and markdowns). Download it at kaggle.com/c/walmart-recruiting-store-sales-forecasting.
3. Add external regressors — Incorporate weather data (Open-Meteo API) and Google Trends search volume for product categories. Show how external signals improve MAPE by 10-20% over a univariate baseline.
4. Deploy it — Wrap it in a Streamlit app with a date-range picker, store selector, and interactive Plotly charts that overlay actuals vs. forecasts with confidence intervals.
5. Write a README — Include architecture diagram, setup instructions, sample outputs, and metrics.

What Hiring Managers Look For

Public Datasets to Use

• Walmart Store Sales Forecasting — 421K rows of weekly department-level sales across 45 stores with holiday flags and markdown events. Available on Kaggle. Great for multi-store, multi-department forecasting with promotional effects.
• Corporación Favorita Grocery Sales — 125M rows of daily store-item sales from an Ecuadorian grocery chain. Available on Kaggle. Excellent for large-scale hierarchical forecasting with oil price and earthquake event features.
• UCI Online Retail II — 1M+ transaction records from a UK online retailer (2009-2011). Available at the UCI ML Repository. Good for SKU-level demand forecasting with customer segmentation.

Deployment Options

Platform	Best For	Effort
Streamlit	Interactive forecast dashboard with date pickers and store filters	Low
Gradio	Quick demo with CSV upload for custom sales data forecasting	Low
FastAPI	REST endpoint returning JSON forecasts for integration with inventory systems	Medium
Docker + Cloud Run	Production-grade forecasting microservice with scheduled batch predictions	High