SQL-Python-E-Commerce-Project

E-Commerce Data Analysis

Project Overview

This project involves a comprehensive analysis of e-commerce sales and customer data to uncover various trends and insights. Using SQL for querying and Python for visualization, the project aims to explore a range of queries from basic to advanced, providing a detailed understanding of sales performance, customer behavior, and revenue distribution. The analysis utilizes libraries such as Matplotlib, Seaborn, and NumPy for visualizing results and deriving actionable insights.

Key Features

Basic Queries

1. Unique Cities: Identify all unique cities where customers are located.
2. Orders Count (2017): Count the number of orders placed in 2017.
3. Total Sales per Category: Calculate total sales for each product category.
4. Installment Payments Percentage: Determine the percentage of orders paid in installments.
5. Customer Count by State: Count the number of customers from each state.

Intermediate Queries

1. Orders per Month (2018): Calculate the number of orders per month for the year 2018.
2. Average Products per Order: Find the average number of products per order, grouped by customer city.
3. Revenue by Product Category: Calculate the percentage of total revenue contributed by each product category.
4. Price and Purchase Correlation: Identify the correlation between product price and the number of times a product has been purchased.
5. Revenue by Seller: Calculate and rank total revenue generated by each seller.

Advanced Queries

1. Moving Average of Order Values: Calculate the moving average of order values for each customer over their order history.
2. Cumulative Sales per Month: Calculate cumulative sales per month for each year.
3. Year-over-Year Sales Growth: Determine the year-over-year growth rate of total sales.
4. Customer Retention Rate: Calculate the retention rate of customers who make another purchase within 6 months of their first purchase.
5. Top Spending Customers: Identify the top 3 customers who spent the most money in each year.

Insights

• Customer Locations: Understand the geographical distribution of customers and their impact on sales.
• Order Trends: Analyze how order volumes and sales vary by time period and product category.
• Revenue Distribution: Gain insights into which categories and sellers contribute most to total revenue.
• Customer Behavior: Explore customer purchasing patterns and retention rates.
• Sales Growth: Track sales growth over time to identify trends and forecast future performance.

How to Use

1. Setup: Ensure you have the necessary Python packages and database connection set up.
2. Run Queries: Execute the SQL queries to extract data from the database.
3. Analyze Data: Use Python scripts to process and visualize the data.
4. Explore Visualizations: Review the visualizations generated to gain insights into sales and customer behavior.

Data Source

The dataset for this analysis is provided in a text file. Please refer to the attached text file for the dataset link and additional details on accessing the data.

Conclusion

This project provides a detailed approach to analyzing e-commerce data, offering valuable insights into performance trends, revenue distribution, and customer behavior. By leveraging SQL and Python, users can explore a variety of queries and visualizations to better understand their data.

Author

Aishwarya S Patil - [email protected]

---