ecommerce-customer-segmentation

SQL Project - Customer Segmentation & Revenue Analysis

E-Commerce Customer Segmentation & Revenue Analysis

✅ Overview

This project analyzes customer behavior, revenue, and sales patterns using SQL and Google BigQuery on an e-commerce dataset. The goal is to identify top customers, understand purchasing trends, and build a foundation for customer segmentation.

✅ Objectives

• Find top customers per country
• Calculate total revenue, average basket size, and number of purchases
• Analyze product-level performance
• Build KPIs for reporting and visualization

✅ Tools

• Google BigQuery (SQL)
• Google Looker Studio (Dashboard)
• GitHub (for documentation & sharing)

✅ Deliverables

• SQL Scripts → /SQL/
• Dashboard Screenshots → /Dashboard/
• Mini Report (PDF) → /Report/

✅ Sample KPIs

Metric	Value
Total Revenue	$279,489
Total Customers	1,340
Total Orders	4,500
Average Basket Size	5.2 units

✅ Insights

• Top 10% of customers drive more than 50% of revenue
• Certain countries have significantly larger average basket sizes
• Identified top-performing products by country

✅ Author

Your Name
[LinkedIn Profile link]
[Optional: Personal Portfolio link]

🧾 Customer Value Analysis (SQL Project)

📌 Project Summary

In this project, I analyzed customer purchasing behavior using a relational e-commerce dataset. I created a summary of each customer’s total spending, number of orders, average order value, and shopping frequency. Customers were then segmented into value tiers (High, Medium, Low) based on their spending. This type of analysis supports decision-making in marketing, retention, and revenue targeting.

---

🛠 Tools Used

• Google BigQuery (SQL)
• Google Looker Studio (for dashboard – optional)
• GitHub (project documentation)

---

📊 Metrics Calculated

Metric	Description
Total Orders	Number of orders made by each customer
Total Quantity Purchased	Total items (units) bought
Total Amount Spent	Revenue generated by each customer
Avg Order Value (AOV)	How much the customer spends per order
Avg Basket Size	Items bought per order
First / Last Purchase Date	Customer lifetime timeline
Duration (Days)	Days active from first to last order
Avg Days Between Orders	Buying frequency
Customer Segment	Auto-labeled: High / Medium / Low Value

---

💡 Sample Insights

• Most valuable customers placed more than 15 orders and spent over $10,000.
• Some customers were only active for a short period but placed large, high-value orders.
• Average basket size varies significantly between countries.

---

🗂 File Structure