Operations Performance Analytics with SQL

This project analyzes retail operations data to understand revenue trends, regional performance, product profitability, and fulfillment efficiency.

The analysis simulates a real-world analyst workflow where raw transactional data is transformed into actionable business insights using SQL and lightweight visualization techniques.

Key focus areas include:

• KPI-driven sales trend analysis
• regional revenue performance
• product category profitability
• shipping delay diagnostics
• cumulative revenue tracking

---

Business Context

Operations and commercial teams need timely insights into how revenue is evolving, which product segments are driving performance, and where logistics inefficiencies may impact customer satisfaction.

This project demonstrates how SQL-based analysis can be used to:

• monitor revenue performance over time
• identify top-performing regions and product categories
• detect shipping delays that may signal fulfillment bottlenecks
• support data-driven operational decision making

---

Dataset

This project uses the Sample Superstore retail dataset.

The dataset includes fields such as:

• order dates
• ship dates
• sales
• profit
• region
• product category
• product sub-category
• product details

For this project, the Orders sheet was converted from Excel to CSV and saved as:

data/superstore.csv

---

Repository Structure

operations-sql-analytics/
│
├── data/
│   └── superstore.csv
├── sql/
│   └── queries.sql
├── images/
├── README.md
├── requirements.txt
├── .gitignore
└── run_analysis.py

---

Analytical Approach

The workflow combines SQL-driven metric computation with Python-based visualization.

Steps include:

1. Loading transactional data into a lightweight SQLite environment
2. Writing business-focused SQL queries to compute operational KPIs
3. Exporting query outputs for validation and reporting
4. Generating visual summaries of revenue trends and logistics performance
5. Translating results into operational insights

This approach mirrors early-stage analytics engineering and reporting pipelines used in production environments.

---

SQL Questions Answered

• How are sales and profit trending over time?
• Which regions generate the highest revenue?
• Which product categories and sub-categories perform best?
• What is the average shipping delay by product category?
• Which products generate the highest sales?
• How does revenue accumulate over time?

---

SQL Techniques Demonstrated

• multi-level aggregation using GROUP BY
• time-based KPI analysis using date transformations
• window functions for cumulative trend tracking
• business metric engineering (sales, profit, delay KPIs)
• dimensional slicing across region, category, and product levels
• exporting query outputs for downstream reporting workflows

---

Sample Visualizations

Monthly Sales Trend

Regional Sales Performance

Shipping Delay by Product Category

---

Key Insights

• Revenue growth shows identifiable trend shifts across months, suggesting seasonality and campaign impact.
• Regional sales concentration highlights geographic dependency risk.
• Certain product categories exhibit longer average shipping times, indicating potential fulfillment constraints.
• A small subset of products contributes disproportionately to total revenue.

---

How to Run

Create a virtual environment:

Mac / Linux:

python3 -m venv .venv
source .venv/bin/activate

Windows:

python -m venv .venv
.venv\Scripts\activate

Install dependencies:

pip install -r requirements.txt

Run the analysis:

python run_analysis.py

Outputs will be written to the project root and images/ folder.

---

Project Evolution

This project began as a quick SQL exploration exercise and was later expanded with visualization and structured documentation to better communicate operational insights.