📊 Customer Analytics & Segmentation Report — SQL

📌 Project Overview

This project focuses on analyzing customer purchasing behavior using SQL Server. The goal is to transform raw sales and customer data into a structured customer-level report that can be used to understand purchasing patterns, customer value, and overall customer behavior.

The analysis combines transactional sales data with customer demographic information and applies SQL transformations and aggregations to create meaningful business metrics.

🎯 Project Objectives

The report was designed to:

• Analyze customer purchasing behavior
• Segment customers based on their purchasing history and value
• Group customers by age
• Measure customer sales and order activity
• Identify customer recency and lifespan
• Calculate average order value
• Calculate average monthly spending
• Create a reusable SQL reporting view for further analysis

📊 Key Metrics

The report generates several customer-level KPIs, including:

Metric	Description
Total Orders	Number of unique orders placed by the customer
Total Sales	Total revenue generated by the customer
Total Quantity	Total number of products purchased
Total Products	Number of distinct products purchased
Customer Lifespan	Number of months between the customer's first and last order
Recency	Number of months since the customer's most recent order
Average Order Value	Average revenue generated per order
Average Monthly Spend	Average customer spending per month

👥 Customer Segmentation

Customers are classified into three segments based on customer lifespan and total sales:

• VIP — Customers with at least 12 months of activity and more than 5,000 in total sales
• Regular — Customers with at least 12 months of activity and 5,000 or less in total sales
• New — Customers with less than 12 months of activity

Customers are also grouped into age categories:

• Under 20
• 20–29
• 30–39
• 40–49
• 50 and above

🛠️ Technologies Used

• SQL Server
• T-SQL
• CTEs (Common Table Expressions)
• Aggregate Functions
• CASE Statements
• DATEDIFF
• COUNT DISTINCT
• GROUP BY
• SQL Views
• Data Aggregation & Transformation

🔍 SQL Approach

The analysis is structured into two main stages:

1. Base Query

The first CTE combines sales transactions with customer information and prepares the core fields required for analysis.

2. Customer Aggregation

The second CTE aggregates transactional data at the customer level to calculate:

• Orders
• Sales
• Quantity
• Products
• First and last order dates
• Customer lifespan

The final query then derives customer segments, age groups, recency, average order value, and average monthly spending.

💡 Business Value

This report can help businesses understand who their customers are, how frequently they purchase, how much revenue they generate, and how long they remain active.

The resulting dataset can also serve as a foundation for further analysis such as:

• Customer retention analysis
• RFM analysis
• Customer lifetime value (CLV)
• Churn analysis
• Sales performance dashboards
• Customer segmentation in Power BI

📁 Project Structure

Customer-Analytics-SQL/
│
├── README.md
│
├── scripts/
│   └── customer_report.sql
│
└── screenshots/
    └── customer_report.png

👨‍💻 Author

Sheta Ibrahim

Data Analyst | SQL | Power BI | Excel | Python

This project demonstrates practical SQL skills in data transformation, aggregation, customer segmentation, and business-oriented analytics.