Target E-Commerce Data Analysis Project

This project focuses on analyzing the Target e-commerce dataset obtained from Kaggle. The project utilizes Python, SQL, and Jupyter Notebook to load, query, and visualize data, providing insights into customer behavior, sales trends, and more.

Features

• Dataset Loading: Multiple CSV files loaded into a MySQL database using SQLAlchemy, pymysql, os, and pandas.
• Data Analysis: Queries range from basic to advanced to extract actionable insights from the dataset.
• Visualization: Insights visualized using seaborn and matplotlib.

Dataset

The dataset contains multiple CSV files, including:

• customers.csv
• orders.csv
• sellers.csv
• products.csv
• geolocation.csv
• payments.csv
• order_items.csv

Prerequisites

• Python 3.7+
• MySQL Server
• Jupyter Notebook
• VS Code
• Required Python libraries:
  • pandas
  • os
  • sqlalchemy
  • pymysql
  • matplotlib
  • seaborn

Installation

1. Clone the repository:

   git clone https://github.com/RafiQamar/target-ecommerce-analysis.git
   cd target-ecommerce-analysis
   

2. Install dependencies:

   pip install -r requirements.txt
   

3. Set up MySQL database:
   • Create a database named commerce.
   • Use the script provided in the csv_to_sql.py file to load CSV files into the database.

Queries Solved

Basic Queries

1. List all unique cities where customers are located.
2. Count the number of orders placed in 2017.
3. Find the total sales per category.
4. Calculate the percentage of orders that were paid in installments.
5. Count the number of customers from each state.

Intermediate Queries

1. Calculate the number of orders per month in 2018.
2. Find the average number of products per order, grouped by customer city.
3. Calculate the percentage of total revenue contributed by each product category.
4. Identify the correlation between product price and the number of times a product has been purchased.
5. Calculate the total revenue generated by each seller and rank them by revenue.

Advanced Queries

1. Calculate the moving average of order values for each customer over their order history.
2. Calculate the cumulative sales per month for each year.
3. Calculate the year-over-year growth rate of total sales.
4. Calculate the retention rate of customers (percentage of customers who make another purchase within 6 months of their first purchase).
5. Identify the top 3 customers who spent the most money in each year.

Findings

Key Insights:

1. Unique Cities: Customers are spread across numerous unique cities, providing opportunities for region-specific marketing.
2. Order Trends: Orders peak during certain months, highlighting potential seasonality.
3. Revenue Contribution: Certain product categories contribute significantly more revenue, indicating high-performing segments.
4. Installment Payments: A notable percentage of orders are paid in installments, suggesting a preference for flexible payment options.
5. Top Customers and Sellers: Identifying high-value customers and top-performing sellers can guide retention and partnership strategies.

Visualization

Key insights are visualized using seaborn and matplotlib, including:

• Monthly order trends.
• Revenue contribution by product categories.
• Seller revenue rankings.

Usage

1. Open the Python file (csv_to_sql.py).
   • Load data into MySQL.
2. Open the Jupyter Notebook file (Target_Ecommerce_Analysis_SQL_Python.ipynb).
3. Run the cells sequentially to:
   • Execute SQL queries to answer the provided questions.
   • Visualize the results.

Results

This project provides insights into customer behavior, product performance, and sales trends, which can be used to optimize operations and marketing strategies for e-commerce platforms.

Contributing

Contributions are welcome! Please fork this repository, make changes, and submit a pull request.

License

This project is licensed under the MIT License.

---