Skip to content

README.md

Latest commit

 

History

History
197 lines (144 loc) · 7.12 KB

README.md

File metadata and controls

197 lines (144 loc) · 7.12 KB
Raw

Commercial Sales & Market Dynamics

Python MySQL Power BI Kaggle

Uncovering customer behavior, sales trends, and product performance through advanced SQL and Power BI.

Overview

An in-depth analysis of e-commerce data using Python, SQL, and Power BI. By utilizing advanced SQL queries for detailed analysis and creating an interactive Power BI dashboard, this project uncovers valuable insights into customer behavior, sales trends, and product performance.

Inspired by the Target Dataset on Kaggle, which includes essential data about products, sales, and customer demographics.

Table of Contents

Dataset

The dataset consists of multiple files detailing products, transactions, and customer information, located in the market_data_extracts/ folder.

File Description
commercial_transactions.csv Core transaction records across all sales channels
fulfillment_records.csv Order fulfillment and shipping details
market_demographics.csv Customer demographic and segmentation data
regional_spatial_data.csv Geographic and regional mapping data
sku_catalog.csv Product catalog with SKU-level details
transaction_line_items.csv Granular line-item breakdown per transaction
vendor_network.csv Vendor and supplier relationship data

Schema

Project Structure

COMMERCIAL-SALES-MARKET-DYNAMICS/
│
├── assets/
│   └── Schema.png                          # Database schema diagram
│
├── commercial_data_modeling/               # SQL analysis scripts
│   ├── aggregate_regional_revenue.sql      # Regional revenue aggregation
│   ├── build_market_dynamics_matrix.sql    # Market dynamics and trend matrix
│   ├── calc_cumulative_commercial_sales.sql# Cumulative sales calculations
│   └── extract_vendor_metrics.sql          # Vendor performance metrics
│
├── executive_reporting/                    # Power BI dashboard assets
│   ├── Market_Overview_View.png            # Dashboard page 1 screenshot
│   └── Regional_Performance_View.png       # Dashboard page 2 screenshot
│
├── market_data_extracts/                   # Raw dataset (CSV files)
│   ├── commercial_transactions.csv
│   ├── fulfillment_records.csv
│   ├── market_demographics.csv
│   ├── regional_spatial_data.csv
│   ├── sku_catalog.csv
│   ├── transaction_line_items.csv
│   └── vendor_network.csv
│
├── commercial_dashboard_app.py             # Python dashboard application
├── etl_pipeline_ingestion.py              # ETL pipeline for data ingestion
├── exploratory_market_analysis.ipynb      # Jupyter notebook for EDA
└── README.md

Project Workflow

Step 1 — Data Preparation

Converted CSV files to SQL format, enabling efficient querying and data manipulation.

Step 2 — SQL Analysis

Executed complex SQL queries to derive insights on e-commerce metrics such as top-selling products, customer purchasing patterns, and sales trends.

Step 3 — Visualization

Created a Power BI dashboard for intuitive, interactive exploration of the insights obtained from the data.

Data Preparation

Raw CSV files from market_data_extracts/ were ingested and converted into SQL tables using the etl_pipeline_ingestion.py pipeline. This involved:

  • Loading data into SQL-compatible formats
  • Cleaning and transforming data to ensure consistency and accuracy in analysis

SQL Analysis

Advanced SQL queries in the commercial_data_modeling/ folder provided insights into 4 key business areas:

File Analysis Description
aggregate_regional_revenue.sql Regional Revenue Evaluated revenue by region for targeted growth
build_market_dynamics_matrix.sql Market Dynamics Analyzed seasonal and annual sales patterns and customer segmentation
calc_cumulative_commercial_sales.sql Cumulative Sales Identified bestsellers by revenue and assessed retention rates
extract_vendor_metrics.sql Vendor Metrics Measured discount effects, shipping timeliness, and return rates

Power BI Dashboard

The Power BI dashboard provides a user-friendly, interactive experience enabling users to:

  • View key performance indicators (KPIs) and metrics at a glance
  • Filter data by product category, time period, and region
  • Understand trends and patterns intuitively through rich visualizations

Dashboard — Page 1

Dashboard Page 1

Dashboard — Page 2

Dashboard Page 2

Technologies Used

Technology Purpose
Python Data cleaning and conversion of CSV files to SQL format
SQL Advanced querying for in-depth data analysis
Power BI Dynamic dashboard to visualize analysis results

Getting Started

Step 1 — Clone the repository to your local machine.

git clone https://github.com/Acelake123/Commercial-Sales-Market-Dynamics.git

Step 2 — Run the ETL pipeline to ingest the raw CSV files from market_data_extracts/ into your SQL environment.

python etl_pipeline_ingestion.py

Step 3 — Use the SQL scripts in commercial_data_modeling/ to recreate the insights in a SQL environment.

Step 4 — (Optional) Open exploratory_market_analysis.ipynb to explore the data interactively via Jupyter Notebook.

Conclusion

This project demonstrates a comprehensive data analysis workflow using SQL for advanced querying and Power BI for visualization. The insights derived can aid in making data-driven decisions, improving customer targeting, and optimizing product and inventory management.

Built with Python, SQL & Power BI  |  Acelake123