AdventureWorks Sales Prediction Pipeline

A comprehensive machine learning pipeline for predicting sales using AdventureWorks data, featuring both Linear Regression and Artificial Neural Network models.

🚀 Quick Start

Prerequisites

1. MySQL Database: Ensure MySQL server is running and create a database named dev_db
2. Python Environment: Python 3.8+ with required packages
3. Data Files: AdventureWorks CSV files in the Class 1 - Data Files and Code File/ directory

Installation

1. Install Python packages:

   pip install -r requirements.txt

2. Set up MySQL database:

   CREATE DATABASE dev_db;

3. Update database credentials in the configuration sections of the Python scripts if needed.

Running the Complete Pipeline

Option 1: Automated Pipeline (Recommended)

cd "Class 2 - Code Files"
python run_complete_pipeline.py

Option 2: Manual Step-by-Step

# 1. Load data into MySQL
cd "Class 1 - Data Files and Code File"
python run_all_etl.py

# 2. Clean and transform data (run in MySQL)
mysql -u root -p dev_db < "Class 2 - Code Files/data_cleaning_procedures.sql"
# Then in MySQL: CALL sp_run_all_cleaning();

# 3. Feature engineering
cd "Class 2 - Code Files"
python feature_engineering.py

# 4. Train Linear Regression model
python linear_regression_model.py

# 5. Train ANN model
python ann_model.py

# 6. Evaluate and compare models
python model_evaluation.py

📁 Project Structure

Phase 3 - GoML Training/
├── Class 1 - Data Files and Code File/
│   ├── AdventureWorks_Customers.csv
│   ├── AdventureWorks_Products.csv
│   ├── AdventureWorks_Sales_2015.csv
│   ├── AdventureWorks_Sales_2016.csv
│   ├── AdventureWorks_Sales_2017.csv
│   ├── AdventureWorks_Territories.csv
│   ├── AdventureWorks_Product_Categories.csv
│   ├── AdventureWorks_Product_Subcategories.csv
│   ├── tbl_stg_customers.py
│   ├── etl_*.py (ETL scripts for each data source)
│   └── run_all_etl.py
├── Class 2 - Code Files/
│   ├── sp_customer_loadtrans1.sql
│   ├── data_cleaning_procedures.sql
│   ├── feature_engineering.py
│   ├── linear_regression_model.py
│   ├── ann_model.py
│   ├── model_evaluation.py
│   └── run_complete_pipeline.py
├── requirements.txt
└── README.md

🔧 Pipeline Components

1. Data Loading (ETL)

• Purpose: Load CSV data into MySQL database
• Scripts: etl_*.py files for each data source
• Output: Raw data tables in dev_db

2. Data Cleaning

• Purpose: Clean and transform raw data
• Script: data_cleaning_procedures.sql
• Output: Clean tables (customers_clean, products_clean, sales_clean)

3. Feature Engineering

• Purpose: Create ML-ready features
• Script: feature_engineering.py
• Output: ml_features table with engineered features

4. Linear Regression Modeling

• Purpose: Train linear regression models
• Script: linear_regression_model.py
• Output: Trained models and performance metrics

5. ANN Modeling

• Purpose: Train artificial neural network models
• Script: ann_model.py
• Output: Trained ANN models and performance metrics

6. Model Evaluation

• Purpose: Compare and evaluate all models
• Script: model_evaluation.py
• Output: Comprehensive comparison reports and visualizations

📊 Features Created

Temporal Features

• Date components (year, month, quarter, day)
• Seasonality indicators
• Holiday proximity
• Weekend indicators

Customer Features

• Demographics (age, income, education)
• Purchase history metrics
• Customer segmentation
• Lifetime value indicators

Product Features

• Product characteristics
• Price categories
• Profit margins
• Popularity scores

Territory Features

• Geographic performance
• Market activity scores
• Regional characteristics

Lag Features

• Historical sales patterns
• Rolling averages
• Trend indicators

Interaction Features

• Customer-product affinity
• Price sensitivity
• Demographic interactions

🤖 Models Implemented

Linear Regression Models

1. Basic Linear Regression
2. Ridge Regression (L2 regularization)
3. Lasso Regression (L1 regularization)
4. Elastic Net (L1 + L2 regularization)
5. Polynomial Regression

Artificial Neural Network Models

1. Basic ANN (3 hidden layers)
2. Deep ANN (5 hidden layers with batch normalization)
3. Wide & Deep ANN (parallel architecture)
4. Regularized ANN (with dropout and regularization)

📈 Model Evaluation

The pipeline evaluates models using multiple metrics:

• R² Score: Coefficient of determination
• RMSE: Root Mean Square Error
• MAE: Mean Absolute Error
• MAPE: Mean Absolute Percentage Error

Visualizations Generated

• Actual vs Predicted scatter plots
• Residual analysis plots
• Model comparison charts
• Feature importance plots
• Training history plots (for ANN)

📋 Output Files

Model Files

• best_linear_model_*.joblib: Best linear regression model
• best_ann_model_*.h5: Best ANN model
• selected_features.joblib: Selected features for linear regression
• ann_scaler.joblib: Scaler for ANN model

Reports

• linear_regression_report.txt: Linear regression analysis
• ann_model_report.txt: ANN model analysis
• model_evaluation_report.txt: Comprehensive comparison

Visualizations

• linear_regression_comparison.png: Linear model comparison
• ann_training_history.png: ANN training progress
• ann_model_comparison.png: ANN model comparison
• model_comparison_plots.png: Overall model comparison
• metrics_comparison.png: Metrics comparison
• feature_importance.png: Feature importance plot

🔍 Database Schema

Raw Tables

• customers: Customer data
• products: Product information
• sales_2015, sales_2016, sales_2017: Sales transactions
• territories: Territory information
• product_categories: Product categories
• product_subcategories: Product subcategories

Clean Tables

• customers_clean: Cleaned customer data
• products_clean: Cleaned product data
• sales_clean: Unified sales data

ML Tables

• ml_features: Engineered features for ML
• v_sales_master: Master view combining all data

⚙️ Configuration

Database Configuration

Update these variables in the Python scripts:

HOST = "localhost"
PORT = 3306
USER = "root"
PASSWORD = "Atharv2210$"  # Update with your password
DATABASE = "dev_db"

Model Configuration

• Feature Selection: Adjust k parameter in feature selection
• Train/Test Split: Modify test_size parameter
• ANN Architecture: Customize layer sizes and activation functions
• Hyperparameters: Adjust learning rates, regularization, etc.

🐛 Troubleshooting

Common Issues

1. Database Connection Failed

   • Ensure MySQL server is running
   • Verify database credentials
   • Check if dev_db database exists

2. Missing Packages

   • Run pip install -r requirements.txt
   • For TensorFlow issues, check Python version compatibility

3. Memory Issues

   • Reduce batch size in ANN training
   • Use feature selection to reduce dimensionality
   • Process data in chunks

4. ETL Failures

   • Check CSV file paths
   • Verify file encoding (latin-1)
   • Ensure sufficient disk space

Performance Optimization

1. For Large Datasets

   • Use data sampling for initial experiments
   • Implement incremental learning
   • Consider distributed computing

2. For Faster Training

   • Use GPU acceleration for ANN
   • Optimize hyperparameters
   • Use early stopping

📚 Next Steps

1. Model Deployment

   • Create API endpoints
   • Set up model serving infrastructure
   • Implement monitoring

2. Advanced Techniques

   • Ensemble methods
   • Time series forecasting
   • Deep learning architectures

3. Business Integration

   • A/B testing framework
   • Real-time predictions
   • Business metrics tracking