Introduction

Customer segmentation is a powerful use case of SQL for data analysis. It helps businesses divide customers into groups based on behavior, spending patterns, and activity. In this lesson, you will learn how to perform customer segmentation using SQL and generate actionable business insights.

Project Overview

You will work with a customer dataset containing:

• customer_id
• name
• total_orders
• total_spent
• last_purchase_date
• city

Your goal is to segment customers into meaningful categories such as high-value, medium-value, and low-value customers.

Step 1: Segment Customers by Spending

You can categorize customers based on their total spending using CASE.

Example:
SELECT name, total_spent,
CASE
WHEN total_spent > 50000 THEN ‘High Value’
WHEN total_spent BETWEEN 20000 AND 50000 THEN ‘Medium Value’
ELSE ‘Low Value’
END AS customer_segment
FROM customers;

This helps identify top customers who contribute most to revenue.

Step 2: Identify Active and Inactive Customers

You can segment customers based on their recent activity.

Example:
SELECT name, last_purchase_date,
CASE
WHEN last_purchase_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY) THEN ‘Active’
ELSE ‘Inactive’
END AS activity_status
FROM customers;

This helps track customer engagement.

Step 3: Analyze Customer Distribution

To count customers in each segment:

SELECT customer_segment, COUNT(*) AS total_customers
FROM
(SELECT
CASE
WHEN total_spent > 50000 THEN ‘High Value’
WHEN total_spent BETWEEN 20000 AND 50000 THEN ‘Medium Value’
ELSE ‘Low Value’
END AS customer_segment
FROM customers) AS temp
GROUP BY customer_segment;

This gives a clear overview of customer distribution.

Step 4: City-wise Customer Analysis

To analyze customers by location:

SELECT city, COUNT(*) AS total_customers
FROM customers
GROUP BY city
ORDER BY total_customers DESC;

This helps identify high-value regions.

Step 5: Top Customers Identification

To find top customers:

SELECT name, total_spent
FROM customers
ORDER BY total_spent DESC
LIMIT 5;

This helps businesses focus on their most valuable customers.

Insights You Can Generate

Using customer segmentation, you can:

• Identify high-value customers
• Improve marketing strategies
• Increase customer retention
• Target specific customer groups

Why This Project is Important

This project helps you:

• Apply SQL in business scenarios
• Understand customer behavior
• Build real-world data analysis skills
• Prepare for data analyst roles

Best Practices

• Use clear segmentation logic
• Keep categories meaningful
• Validate your queries
• Combine multiple factors for better insights

Summary

In this lesson, you learned how to perform customer segmentation using SQL. You categorized customers, analyzed activity, and generated business insights. In the next lesson, you will build a business dashboard using SQL concepts.

FAQs

1. What is customer segmentation in SQL?
It is the process of dividing customers into groups based on behavior and data.

2. Why is customer segmentation important?
It helps businesses target customers effectively and improve sales.

3. Which SQL feature is used for segmentation?
The CASE statement is commonly used.

4. Can SQL be used for marketing analysis?
Yes, SQL is widely used for customer and marketing analysis.

Internal Link

Want to explore more courses?
Click here for more free courses