Retail Analytics Pipeline

An end-to-end retail analytics project that loads sales and product data, transforms it with Pandas, writes a SQLite star schema, and visualizes revenue insights in a Streamlit dashboard.

This project was prepared for Digital Business Analytics (DS-464). The repository includes the ETL script, SQLite database, dashboard app, SQL DDL/views, source data files, and dashboard screenshots.

Product case study at a glance

Dimension	Evidence
User decision	Which revenue movements, categories, and customer IDs deserve an operator's attention?
Product choice	Define a small trusted metric layer in SQLite views before building dashboard visuals
Implemented outcome	A reproducible path from CSV/JSON inputs to a star schema and decision-ready dashboard
Important guardrail	Revenue is not profit; the dataset has no cost, return, margin, or business-target fields
Data-quality learning	Two distinct customer IDs share the name Zain Butt, so the dashboard preserves and displays the ID instead of merging by name
Next validation	Add operator interviews, date/category/region filters, metric definitions, and source-column quality checks

Project Purpose

The goal is to turn raw retail order data into a small analytical model that can answer practical business questions:

• How much revenue was generated over time?
• Which product categories drive the most revenue?
• Who are the top customers by total spend?
• How can CSV/JSON data be transformed into a structured database for dashboarding?

Data Sources

File	Role	Verified Contents
superstore_sales.csv	Sales transactions	507 rows with order, customer, product, price, quantity, discount, and region fields
superstore_products.json	Product lookup	14 products with product name and category
retail.sqlite	Generated SQLite database	4 tables and 3 analytical views

Sales date range in the included database: 2025-05-01 to 2025-06-09.

Workflow

superstore_sales.csv + superstore_products.json
  -> load with Pandas
  -> standardize column names
  -> parse order dates
  -> convert unit_price, quantity, and discount to numeric values
  -> calculate line_item_total
  -> join sales with product metadata
  -> create fact and dimension tables
  -> write tables and views to SQLite
  -> visualize the SQLite views in Streamlit

Tech Stack

• Python
• Pandas
• SQLite
• Streamlit
• Altair

ETL Script

pipeline_sqlite.py performs the data pipeline:

1. Reads sales data from CSV and products data from JSON.
2. Standardizes column names by trimming spaces, lowercasing, and replacing spaces with underscores.
3. Parses order_date and coerces numeric fields.
4. Interpolates and fills missing unit_price and quantity values if present.
5. Calculates line_item_total using discount when available.
6. Builds dimension tables and a fact table.
7. Writes the model to SQLite and creates analytical views.

Run it from the repository root:

python -m pip install -r requirements.txt
python pipeline_sqlite.py --sales superstore_sales.csv --products superstore_products.json --db retail.sqlite

SQLite Model

Tables

Table	Purpose
fact_sales	Order-line facts with date, customer, product, price, quantity, and line total
dim_product	Product names and categories
dim_customer	Customer names
dim_date	Calendar fields for the date range

The included retail.sqlite currently contains:

Object	Row Count
fact_sales	507
dim_product	14
dim_customer	25
dim_date	40

Views

View	Purpose
v_daily_revenue	Daily revenue trend
v_sales_by_category	Revenue by date and product category
v_top5_customers	Top five customers by total spend

Indexes are created on date_key, customer_key, and product_key in fact_sales.

Dashboard

streamlit_app.py reads the SQLite views and displays:

• Total revenue
• Average daily revenue
• Days tracked
• Top customer spend
• Daily revenue trend line chart
• Sales by category bar chart
• Top five customers table and bar chart

Run the dashboard:

streamlit run streamlit_app.py

In the sidebar, keep the database path as retail.sqlite or enter another SQLite file created by the pipeline, then click Load Data.

Current Results From Included Database

Metric	Value
Total revenue	Rs. 7,365,206.77
Average daily revenue	Rs. 184,130.17
Highest daily revenue	Rs. 448,249.34 on 2025-05-20
Top customer spend in v_top5_customers	Rs. 509,308.92

Category totals from the included database:

Category	Revenue
Furniture	Rs. 4,266,557.64
Technology	Rs. 2,805,346.19
Office Supplies	Rs. 293,302.93

These results describe the included dataset; they are not evidence of production business impact. Furniture leads on revenue, but the available data does not establish whether it also leads on margin, retention, or inventory efficiency.

Dashboard Screenshots

These are existing screenshots from the Screenshots/ folder.

Additional launch and cropped dashboard captures are stored as Screenshots/Picture1.png through Screenshots/Picture7.png.

Repository Structure

.
|-- pipeline_sqlite.py
|-- streamlit_app.py
|-- requirements.txt
|-- ddl_and_queries_sqlite.sql
|-- retail.sqlite
|-- superstore_sales.csv
|-- superstore_products.json
`-- Screenshots/
    |-- Picture1.png
    |-- Picture2.png
    |-- ...
    `-- Picture9.png

Troubleshooting

Issue	Fix
Database file not found	Rerun pipeline_sqlite.py and confirm the --db path.
Import errors	Install dependencies with python -m pip install -r requirements.txt.
Empty dashboard	Make sure retail.sqlite contains the three views used by streamlit_app.py.
Streamlit port is busy	Run streamlit run streamlit_app.py --server.port 8502.

Limitations

• The ETL uses full-table replacement, not incremental loading.
• The Streamlit dashboard loads data only after the sidebar button is clicked.
• The current model does not use the region field in dashboard views.
• Customer identity is keyed by customer_id. The included data contains the same display name for customer IDs 1002 and 1008; the dashboard disambiguates them rather than assuming they are one person.
• There are no automated tests for the pipeline or dashboard.

Future Improvements

• Add filters for date range, category, customer, and region.
• Add export options for filtered dashboard tables.
• Add incremental load support instead of replacing tables each run.
• Add validation checks for missing or malformed source columns.
• Extend the model with regional analysis, forecasting, or customer segmentation.

Team

Name	Registration #
Muhammad Arsal	2022350
Ahmed Musharaf	2022067
Saaim Ali Khan	2022519
Manal Ahsan	2022279
Amaan Zahir Sadiq	2022902