📊 E-Commerce Sales Analytics using SQL 📌 Project Overview

This project focuses on analyzing an e-commerce sales database using SQL to extract meaningful business insights from customer, product, and order data.

The objective of this project is to simulate a real-world Data Analyst workflow — starting from database creation and data exploration to advanced analytics using SQL techniques such as CTEs, Window Functions, Ranking, Cohort Analysis, and Retention Analysis.

The analysis helps understand:

Sales performance Customer behavior Product performance Revenue trends Customer retention patterns 🎯 Business Objectives

The project aims to answer key business questions:

What is the overall sales performance? Which products and categories generate the highest revenue? Who are the most valuable customers? How do customer purchasing patterns change over time? What is customer retention behavior? Which business areas require improvement? 🗂️ Dataset Description

The database consists of three relational tables:

👥 Customers Table

Contains customer information:

Column Description customer_id Unique customer identifier customer_name Customer name city Customer location state Customer state signup_date Customer registration date 📦 Products Table

Contains product details:

Column Description product_id Unique product identifier product_name Product name category Product category price Product price 🛒 Orders Table

Contains transaction data:

Column Description order_id Unique order identifier customer_id Customer reference product_id Product reference order_date Purchase date quantity Number of items purchased sales_amount Revenue generated 🛠️ Tools & Technologies Used MySQL MySQL Workbench SQL Relational Database Concepts Data Analysis Techniques 📂 Project Workflow 1️⃣ Database Creation & Data Loading

Created a relational database structure:

Created database Designed tables Established relationships using primary and foreign keys Imported CSV datasets into MySQL 2️⃣ Data Exploration (EDA)

Performed exploratory analysis to understand the dataset.

Tasks performed:

✔ Dataset size analysis ✔ Table structure inspection ✔ Missing value checking ✔ Duplicate record detection ✔ Date range analysis ✔ Category distribution analysis

3️⃣ Sales Analysis

Analyzed business sales performance:

Key Analysis: Total revenue generated Total orders Average order value Monthly revenue trends Category-wise sales performance Top-selling products Revenue contribution analysis 4️⃣ Customer Analysis

Analyzed customer purchasing behavior:

Key Analysis: Total customers Customer location analysis Top spending customers Customer purchase frequency Repeat customers Customer segmentation Customer revenue ranking 5️⃣ Advanced SQL Analytics

Applied advanced SQL techniques:

Concepts Used: Common Table Expressions (CTEs) Subqueries Window Functions Ranking Functions RANK() Running Totals LAG() Growth Analysis Product Ranking

Example use cases:

Ranking customers by revenue Finding top-performing products Calculating sales growth 6️⃣ Customer Lifecycle Analysis

Performed customer behavior analysis:

Cohort Analysis

Analyzed customers based on their first purchase month.

Retention Analysis

Measured:

Returning customers Customer engagement over time Purchase continuation patterns Customer Lifetime Value

Calculated revenue generated by individual customers.

📈 Key Insights Generated

The analysis helps identify:

✔ Highest revenue-generating categories ✔ Best performing products ✔ Most valuable customers ✔ Sales growth patterns ✔ Customer retention behavior ✔ Revenue contribution distribution

📁 SQL File Structure SQL Queries

01_database_setup.sql 02_data_exploration.sql 03_sales_analysis.sql 04_customer_analysis.sql 05_advanced_sql_analysis.sql 06_customer_lifecycle_analysis.sql 07_final_business_insights.sql 🚀 Skills Demonstrated SQL Query Writing Database Design Data Cleaning Exploratory Data Analysis Business Analytics Customer Analysis Advanced SQL Data-Driven Decision Making 📌 Future Improvements

Possible enhancements:

Build an interactive dashboard using Power BI/Tableau Automate reporting Add Python-based data visualization Perform predictive customer analysis 👨‍💻 Author

Gaurav Rawat

Aspiring Data Analyst | SQL | Python | Data Visualization