E-Commerce SQL Business Case Study

🛒 E-Commerce SQL Business Case Study

📌 Project Overview

This project demonstrates the use of MySQL for solving real-world e-commerce business problems. The analysis focuses on customer purchasing behavior, revenue trends, product performance, customer engagement, and session-to-purchase behavior.

The project uses a relational database containing information about users, products, orders, order items, and user sessions.

---

🗃️ Database Structure

The database consists of five tables:

• users – Customer information, country, join date, and membership tier
• products – Product details, categories, and prices
• orders – Customer orders, order dates, amounts, and order status
• order_items – Individual products and quantities within each order
• user_sessions – User browsing sessions, session duration, and page views

---

🎯 Business Questions

The analysis answers the following business questions:

1. Month-over-Month Revenue Growth Calculate monthly revenue from completed orders and analyze MoM revenue growth.

2. First-Order Conversion Bottleneck Identify how many users placed a completed order within 10 days of joining, grouped by country.

3. Customer Lifetime Value (CLV) Tiering Rank customers within each country based on their total spending.

4. Sticky Categories Identify users who purchased products from multiple categories within the same calendar month.

5. Top Products per Category Identify the top two revenue-generating products within each product category.

6. User Session Engagement Metrics Compare average session duration and average page views across membership tiers.

7. Purchase Interval Analysis Calculate the number of days between consecutive orders for each user.

8. Session-to-Purchase Conversion Attribution Identify the most recent user session before each completed purchase.

---

🛠️ SQL Skills Demonstrated

• SQL Joins
• Aggregate Functions
• GROUP BY and HAVING
• Subqueries
• Window Functions
• LAG()
• ROW_NUMBER()
• DENSE_RANK()
• PARTITION BY
• Date and Time Functions
• TIMESTAMPDIFF()
• DATEDIFF()
• COALESCE()
• Customer and Revenue Analytics

---

📂 Repository Structure

ecommerce-sql-business-case-study/
├── README.md
├── schema.sql
├── sample_data.sql
├── questions.md
└── solutions.sql

schema.sql

Contains the SQL statements used to create the database tables.

sample_data.sql

Contains sample e-commerce data used for the analysis.

questions.md

Contains the business problems addressed in the project.

solutions.sql

Contains the SQL queries used to solve each business problem.

---

🚀 How to Run the Project

1. Create a MySQL database.
2. Run schema.sql to create the required tables.
3. Run sample_data.sql to insert the sample data.
4. Review the business problems in questions.md.
5. Run the queries in solutions.sql to perform the analysis.

---

💡 Key Takeaways

This project demonstrates how SQL can be used to transform transactional and behavioral data into meaningful business insights. The analysis covers important areas such as revenue growth, customer conversion, customer lifetime value, product performance, purchasing patterns, and user engagement.

---

👤 Author

Emmanuel Thomas

Aspiring Data Analyst | SQL | Python | Pandas | Excel | Power BI | Snowflake