Transforming raw transaction data into strategic decisions.
This project demonstrates the power of SQL in extracting actionable business insights from complex, relational e-commerce data. By joining multiple tables and applying optimized aggregations, this analysis provides a clear roadmap for driving business growth, optimizing operations, and understanding customer behavior.
- Data Silos: Business data is often scattered across multiple tables (Orders, Payments, Products), making it difficult to gain a holistic view of performance.
- Manual Tracking: Without automation, calculating key metrics like total revenue and identifying top-performing products is slow and prone to human error.
To use SQL to bridge the gap between raw data and business growth, providing clear, data-driven insights that inform strategic decision-making.
The solution was built on three core pillars:
- Relational Database: Organized raw data into structured tables (Customers, Products, Orders, Payments).
- SQL Queries: Developed optimized queries using advanced
JOINs,GROUP BYclauses, andAggregations(SUM,COUNT). - Business Logic: Focused specifically on "Delivered" orders to ensure revenue calculations reflect actual earnings and identify true trends.
The foundation of this analysis is a robust relational schema:
| Table | Key Attributes |
|---|---|
| Customers | customer_id, name, email, city, signup_date |
| Products | product_id, product_name, category, price, stock |
| Orders | order_id, customer_id, order_date, order_status |
| Order_Items | order_item_id, order_id, product_id, quantity |
| Payments | payment_id, order_id, payment_mode, amount, payment_date |
- Total Delivered Revenue: ₹21,225
- Dominant Payment Method: UPI (₹11,537)
- Insight: The dominance of UPI suggests a mobile-first customer base. Promotions should be prioritized for UPI users to maximize conversion.
- Top Revenue Driver: Late Night Hoodie (₹2,199)
- High Volume Items: Notebooks and Cheat Sheets.
- Business Insight: While low-cost "learning tools" drive the highest sales volume and customer acquisition, premium apparel like hoodies drives high-value revenue per order.
- Order Frequency: On average, each customer placed 2 orders, indicating healthy repeat engagement.
- Top Spenders: Identified specific high-value customers contributing significantly to total revenue.
- Recommendation: Launch a Loyalty Program to reward repeat buyers and increase Customer Lifetime Value (CLV).
- Total Cancelled Orders: 6
- Impact: Cancelled orders represent lost revenue and potential dissatisfaction.
- Action Plan: Investigate the root cause of these cancellations (e.g., stock issues or delivery delays) to improve the fulfillment process.
The project leverages sophisticated SQL logic to ensure data integrity and accuracy.
SELECT
p.product_name,
SUM(oi.quantity * p.price) AS revenue
FROM order_items oi
JOIN products p ON oi.product_id = p.product_id
JOIN orders o ON oi.order_id = o.order_id
WHERE o.order_status = 'Delivered'
GROUP BY p.product_name
ORDER BY revenue DESC;