BigQuery ML Retail Forecasting

SQL-based retail sales forecasting using BigQuery ML. Predicts 30-day revenue across multiple store locations with 85%+ accuracy no Python, no infrastructure, no complexity.

---

Project Overview

Problem: Retail businesses need accurate sales forecasts for inventory planning and resource allocation, but traditional ML solutions require significant engineering overhead and cost.

Solution: A pure SQL forecasting pipeline using BigQuery ML ARIMA_PLUS. Any data analyst with SQL knowledge can run, maintain, and extend it.

Results:

• 85-90% forecast accuracy (MAPE < 15%) across 5 store locations
• 30-day forecast horizon with 95% confidence intervals
• $5-10/month operating cost 80% cheaper than Vertex AI AutoML
• 20-minute implementation time, 5-15 minutes model training
• 100% SQL no Python or ML expertise required

---

Architecture

Component	Technology	Purpose
Data Storage	BigQuery	Transaction data warehouse
Data Transformation	SQL	Daily revenue aggregation
ML Training	BigQuery ML ARIMA_PLUS	Time series forecasting
Predictions	ML.FORECAST	30-day forecasts with confidence intervals
Visualization	Looker Studio	Business dashboard

---

Repository Structure

bigquery-ml-retail-forecasting/
 README.md
 ARCHITECTURE.md
 sql/
    01_create_training_view.sql
    02_train_model.sql
    03_evaluate_model.sql
    04_generate_forecast.sql
    05_create_unified_view.sql
    06_monitoring_queries.sql
 docs/
    architecture.svg
    BQML_vs_AutoML.md
 data/
     sample_retail_sales.xlsx

---

Quick Start

Prerequisites

• GCP project with billing enabled
• BigQuery API enabled
• Transaction data in BigQuery (or an Excel/CSV file to upload)

Setup (20 Minutes)

Step 1: Upload data

# Create dataset
bq mk --dataset --location=US retail_ds

# Upload Excel file
bq load --autodetect --source_format=XLSX \
  retail_ds.retail_sales \
  retail_sales.xlsx

Step 2: Run SQL scripts in order

1. sql/01_create_training_view.sql prepare and aggregate data
2. sql/02_train_model.sql train the ARIMA_PLUS model (5-15 min)
3. sql/03_evaluate_model.sql review accuracy metrics
4. sql/04_generate_forecast.sql generate 30-day predictions
5. sql/05_create_unified_view.sql combine historical and forecast data

Step 3: Connect Looker Studio

1. Open Looker Studio
2. Add a BigQuery data source pointing to the unified_forecast view
3. Build a time series chart with store-location filters

---

Model Performance

Evaluation by Store

Store	MAPE	MAE	Status
Austin	12.4%	$520	Good
Chicago	14.1%	$610	Acceptable
Denver	11.8%	$480	Good
Miami	16.2%	$720	Monitor
Seattle	13.5%	$580	Good

Average MAPE: 13.6% (target: < 15%)

What BQML Handles Automatically

• Seasonality detection (daily, weekly, yearly)
• US public holiday adjustments
• ARIMA parameter tuning (p, d, q)
• Outlier and anomaly handling
• Confidence interval generation

---

Cost Analysis

Component	Monthly Cost
BigQuery Storage	$0.02
Model Training (2x/month)	$26
Forecast Queries (4x/month)	$0.50
Dashboard Queries	$0.50
Total	$37

Annual cost: ~$50

Solution	Annual Cost
BigQuery ML	$50
Vertex AI AutoML	$150
Custom Python Infrastructure	$6001,200

---

Retraining Strategy

• Frequency: Every 24 weeks
• Trigger: MAPE exceeds 15%, or significant new data is available
• Automation: BigQuery Scheduled Query (every Monday at 2 AM)

-- Automated retraining via Scheduled Query
CREATE OR REPLACE MODEL `retail_ds.retail_forecast_arima`
OPTIONS(...) AS SELECT ...;

---

Advanced Usage

Experimenting with Other Model Types

BigQuery ML supports multiple algorithms. Swap model_type to compare:

-- Current: ARIMA_PLUS (recommended for time series)
model_type = 'ARIMA_PLUS'

-- Alternatives for comparison
model_type = 'DNN_REGRESSOR'
model_type = 'BOOSTED_TREE_REGRESSOR'

Adding Custom Features

CREATE OR REPLACE VIEW `retail_ds.daily_revenue_enhanced` AS
SELECT
  date,
  store_location,
  revenue,
  EXTRACT(DAYOFWEEK FROM date) AS day_of_week,
  CASE
    WHEN date IN ('2023-12-25', '2024-01-01') THEN 1
    ELSE 0
  END AS is_major_holiday
FROM `retail_ds.daily_revenue`;

Monitoring for Model Drift

WITH recent_performance AS (
  SELECT
    AVG(ABS((actual - predicted) / actual)) * 100 AS recent_mape
  FROM actual_vs_predicted
  WHERE forecast_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
)
SELECT
  recent_mape,
  CASE
    WHEN recent_mape > 15 THEN 'RETRAIN NEEDED'
    ELSE 'OK'
  END AS status
FROM recent_performance;

---

BigQuery ML vs Alternatives

vs Vertex AI AutoML

Aspect	BigQuery ML	Vertex AI AutoML
Interface	SQL	Console + Python
Training time	5-15 min	30120 min
Cost per run	$13	$35
Infrastructure	None (serverless)	Vertex AI platform
Best for	Fast iteration, SQL teams	Production at scale

vs Custom Python ML

Aspect	BigQuery ML	Custom Python
Infrastructure	None (serverless)	VMs, containers
Maintenance	Managed	Self-managed
Annual cost	~$50	$600+
Flexibility	Standard BQML models	Unlimited
Best for	Standard forecasting	Custom algorithms

Use BigQuery ML when:

• Data already lives in BigQuery
• Team is SQL-proficient but not Python-proficient
• Fast iteration or proof-of-concept is required
• Cost is a constraint
• Standard forecasting use case

Move to AutoML or custom tooling when:

• Maximum possible accuracy is required
• Complex feature engineering is needed
• Model export or serving outside BigQuery is required
• Real-time prediction latency is critical

---

Documentation

• ARCHITECTURE.md Full architecture and design decisions
• BQML vs AutoML Detailed comparison and decision guide
• SQL Reference Annotated SQL scripts

---

Contributing

Contributions are welcome.

1. Fork the repository
2. Create a feature branch
3. Submit a pull request

See CONTRIBUTING.md for guidelines.

---

Learning Resources

• BigQuery ML Documentation
• ARIMA_PLUS Tutorial
• Time Series Forecasting on GCP

---

License

MIT License see LICENSE

---