E-Commerce Orders — SQL Insights Project

📌 Project Overview

This project uses SQL to explore and extract business insights from an e-commerce orders dataset. The analysis covers order patterns, revenue by product, customer payment behavior, and marketing referral performance using core SQL operations.

📂 Dataset

File: Cleaned_Dataset.csv Table Name: cleaned_dataset Total Records: 1,200 orders

Column	Description
OrderID	Unique identifier for each order
Date	Order placement date
CustomerID	Unique identifier for each customer
Product	Product purchased (e.g., Monitor, Phone, Chair)
Quantity	Number of units ordered
UnitPrice	Price per unit
ShippingAddress	Delivery address
PaymentMethod	Mode of payment (Credit Card, Debit Card, Online, etc.)
OrderStatus	Order state (Shipped, Cancelled, Returned, etc.)
TrackingNumber	Shipment tracking ID
ItemsInCart	Number of items in cart at checkout
CouponCode	Discount coupon applied, if any
ReferralSource	Marketing channel that drove the order (Instagram, Email, Facebook, etc.)
TotalPrice	Final order value

🛠️ Tools Used

• MySQL Workbench — database management and query execution
• SQL — data querying and aggregation

🎯 Objective

To practice and demonstrate core SQL skills by answering real business questions such as:

• Which products generate the most orders and revenue?
• What is the average order value by payment method?
• Which marketing channels bring the most customers?
• How many orders are cancelled or returned?

⚙️ Setup Instructions

1. Open MySQL Workbench and connect to a local server.
2. Create a new schema (database), e.g. ecommerce_orders.
3. Import Cleaned_Dataset.csv into a table named cleaned_dataset using the Table Data Import Wizard.
4. Verify the import:

   SELECT COUNT(*) FROM cleaned_dataset;

   This should return 1200.
5. Confirm column data types are correct:

   DESCRIBE cleaned_dataset;

   Numeric columns (Quantity, ItemsInCart, UnitPrice, TotalPrice) should be INT/DECIMAL, and Date should be DATE.

🧩 SQL Concepts Covered

Concept	Used For
SELECT	Retrieving specific columns
WHERE	Filtering rows by condition
ORDER BY	Sorting results
GROUP BY	Aggregating data by category
HAVING	Filtering aggregated groups
COUNT()	Counting records
SUM()	Total revenue calculations
AVG()	Average order value calculations
MIN() / MAX()	Range of order values

📊 Queries & Insights

1. Sample records — Preview of raw order data.

2. Cancelled orders — Filters all orders with OrderStatus = 'Cancelled' to assess order fulfillment issues.

3. High-value orders — Orders above ₹2,000, useful for identifying premium customers.

4. Top 10 expensive orders — Ranks the highest-value transactions.

5. Orders per product — Counts how many orders exist for each product.

6. Revenue per product — Sums total revenue generated by each product.

7. Average order value by payment method — Reveals which payment method is associated with higher spending.

8. Status breakdown by product — Shows the distribution of order statuses (Shipped/Cancelled/Returned) for a specific product.

9. Top referral sources — Identifies marketing channels driving more than 200 orders.

10. Overall KPI summary — Total orders, total revenue, average/min/max order value at a glance.

All 10 queries are available in insights.sql.

📈 Key Findings

• Total orders: 1,200
• Total revenue: ₹12,64,762
• Average order value: ₹1,053.97
• Credit Card users have the highest average order value.
• Chair and Printer are the top revenue-generating products.

📁 Project Files

• Cleaned_Dataset.csv — Raw dataset
• Ecommerce_SQL_Data_Analytics.sql — All 10 SQL queries used for analysis
• README.md — Project documentation (this file)

✅ Conclusion

This project demonstrates practical SQL skills — filtering, sorting, grouping, and aggregating data — to convert raw transactional records into actionable business insights.