Curriculum
Customer Analytics Project is a real-world Business Analytics project that demonstrates how SQL can be used to understand customer behavior, improve customer retention, increase revenue, and support data-driven business decisions. Customer data is one of the most valuable assets for modern organizations because customer insights directly impact sales, marketing, profitability, and long-term growth.
Companies collect customer information from:
The objective of this Customer Analytics Project is to transform raw customer data into actionable business insights using SQL.
Organizations use Customer Analytics Projects to:
This project combines advanced SQL concepts including:
The Customer Analytics Project reflects real-world scenarios commonly encountered by Data Analysts, Business Analysts, Marketing Analysts, and Business Intelligence Professionals.
An e-commerce company wants to analyze customer behavior to improve marketing effectiveness and increase customer retention.
The company stores information about:
Management requires detailed customer insights to support strategic business decisions.
Management wants answers to the following questions:
SQL will be used to answer these questions.
The project uses the following tables.
Stores customer information.
| Column Name | Data Type |
|---|---|
| CustomerID | INT |
| CustomerName | VARCHAR(100) |
| City | VARCHAR(50) |
| RegistrationDate | DATE |
Stores customer transactions.
| Column Name | Data Type |
|---|---|
| OrderID | INT |
| CustomerID | INT |
| ProductID | INT |
| OrderAmount | DECIMAL(10,2) |
| OrderDate | DATE |
Stores product information.
| Column Name | Data Type |
|---|---|
| ProductID | INT |
| ProductName | VARCHAR(100) |
| Category | VARCHAR(50) |
These tables will be used throughout the project.
Management wants to know the total customer base.
Query:
SELECT COUNT(*)
AS TotalCustomers
FROM Customers;Business Value:
Customer growth monitoring.
Calculate total revenue generated by customers.
Query:
SELECT SUM(OrderAmount)
AS TotalRevenue
FROM Orders;Business Value:
Revenue visibility.
Executive reporting.
Management wants to understand customer value.
Query:
SELECT
SUM(OrderAmount) /
COUNT(DISTINCT CustomerID)
AS AverageRevenuePerCustomer
FROM Orders;Business Value:
Customer profitability analysis.
Customer Lifetime Value (CLV) measures the total revenue generated by a customer.
Query:
SELECT
CustomerID,
SUM(OrderAmount)
AS LifetimeValue
FROM Orders
GROUP BY CustomerID
ORDER BY LifetimeValue DESC;Business Value:
Identify valuable customers.
Applications:
Marketing optimization.
Identify the highest-value customers.
Query:
SELECT
C.CustomerName,
SUM(O.OrderAmount)
AS TotalRevenue
FROM Customers C
INNER JOIN Orders O
ON C.CustomerID = O.CustomerID
GROUP BY C.CustomerName
ORDER BY TotalRevenue DESC;Business Value:
VIP customer identification.
Customer retention strategies.
Analyze customer buying behavior.
Query:
SELECT
CustomerID,
COUNT(OrderID)
AS TotalOrders
FROM Orders
GROUP BY CustomerID
ORDER BY TotalOrders DESC;Business Value:
Customer engagement measurement.
Customers can be segmented based on spending.
Query:
SELECT
CustomerID,
TotalRevenue,
CASE
WHEN TotalRevenue > 100000 THEN 'High Value'
WHEN TotalRevenue > 50000 THEN 'Medium Value'
ELSE 'Low Value'
END AS CustomerSegment
FROM
(
SELECT
CustomerID,
SUM(OrderAmount)
AS TotalRevenue
FROM Orders
GROUP BY CustomerID
) CustomerSummary;Business Value:
Targeted marketing campaigns.
Improved customer engagement.
Use Ranking Functions to identify top customers.
Query:
SELECT
CustomerID,
TotalRevenue,
RANK()
OVER(
ORDER BY TotalRevenue DESC
)
AS CustomerRank
FROM
(
SELECT
CustomerID,
SUM(OrderAmount)
AS TotalRevenue
FROM Orders
GROUP BY CustomerID
) CustomerRevenue;Business Value:
Performance rankings.
Executive dashboards.
Identify how recently customers purchased.
Query:
SELECT
CustomerID,
MAX(OrderDate)
AS LastPurchaseDate
FROM Orders
GROUP BY CustomerID;Business Value:
Customer retention monitoring.
Find customers who have not purchased recently.
Query:
SELECT
CustomerID,
MAX(OrderDate)
AS LastPurchaseDate
FROM Orders
GROUP BY CustomerID
HAVING MAX(OrderDate)
< CURRENT_DATE - INTERVAL 90 DAY;Business Value:
Re-engagement campaigns.
Customer retention strategies.
Analyze customer growth trends.
Query:
SELECT
MONTH(RegistrationDate)
AS RegistrationMonth,
COUNT(*)
AS NewCustomers
FROM Customers
GROUP BY MONTH(RegistrationDate);Business Value:
Growth tracking.
Marketing effectiveness measurement.
Identify customer purchasing preferences.
Query:
SELECT
P.Category,
COUNT(*) AS TotalPurchases
FROM Orders O
INNER JOIN Products P
ON O.ProductID = P.ProductID
GROUP BY P.Category
ORDER BY TotalPurchases DESC;Business Value:
Product strategy development.
Measure repeat purchase behavior.
Query:
SELECT
CustomerID,
COUNT(OrderID)
AS PurchaseCount
FROM Orders
GROUP BY CustomerID
HAVING COUNT(OrderID) > 1;Business Value:
Retention measurement.
Loyalty program planning.
Analyze customer distribution.
Query:
SELECT
City,
COUNT(*)
AS CustomerCount
FROM Customers
GROUP BY City
ORDER BY CustomerCount DESC;Business Value:
Regional marketing planning.
Business expansion decisions.
Prepare data for Power BI dashboards.
Query:
SELECT
C.City,
COUNT(DISTINCT O.CustomerID)
AS ActiveCustomers,
SUM(O.OrderAmount)
AS Revenue
FROM Customers C
INNER JOIN Orders O
ON C.CustomerID = O.CustomerID
GROUP BY C.City;Business Value:
Business Intelligence reporting.
Reusable reporting structure.
Query:
CREATE VIEW CustomerAnalyticsView AS
SELECT
C.CustomerName,
COUNT(O.OrderID)
AS TotalOrders,
SUM(O.OrderAmount)
AS TotalRevenue
FROM Customers C
INNER JOIN Orders O
ON C.CustomerID = O.CustomerID
GROUP BY C.CustomerName;Business Value:
Reporting automation.
Automate customer reporting.
Query:
CREATE PROCEDURE CustomerSummary()
BEGIN
SELECT
COUNT(*)
AS TotalCustomers
FROM Customers;
END;Business Value:
Automated reporting.
Important KPIs include:
These KPIs support strategic business decisions.
The Customer Analytics Project helps organizations identify:
These insights improve business performance.
Industries using Customer Analytics include:
Customer segmentation.
Customer profitability analysis.
Patient engagement analysis.
Student retention analysis.
Purchase behavior analysis.
Customer churn prediction.
Customer analytics is essential across industries.
This project applies:
These skills are highly demanded in industry roles.
Generate actionable insights.
Ensure reporting accuracy.
Improve targeting.
Support customer growth.
Improve visualization.
Support scalability.
These practices improve analytical effectiveness.
After completing this lesson, you will be able to:
A Customer Analytics Project uses SQL to analyze customer behavior and generate business insights.
It helps improve retention, marketing effectiveness, and profitability.
Customer Lifetime Value measures the total revenue generated by a customer.
Joins, Aggregate Functions, Window Functions, Ranking Functions, Views, and Stored Procedures.
Yes. The generated datasets can be used directly in Power BI dashboards.
Customer Lifetime Value, retention rate, customer growth, and revenue metrics.
E-commerce, banking, healthcare, education, retail, and telecommunications.
It demonstrates how SQL can generate actionable customer insights and support data-driven decision-making.
Want to master SQL, reporting, and Data Analytics?