Analysing customer revenue is a critical aspect of understanding the performance of any business. Being able to segment customers based on their revenue allows businesses to gain insights into their customer base and develop targeted marketing and sales strategies. One way to segment customers is by dividing them into quartiles based on their total revenue. In this blog post, we will explore how to use SQL and the AdventureWorksDW2019 database to group customers into four quartiles based on their total revenue. We will walk through a step-by-step process, starting with calculating the total revenue for each customer and then using the NTILE function to divide the customers into quartiles. By the end of this post, you will have a solid understanding of how to use SQL to group customers into quartiles based on their total revenue, and how this can help businesses gain valuable insights into their customer base.
To group customers into four quartiles based on total revenue, we first need to calculate the total revenue for each customer. We can do this using a SQL query in AdventureWorksDW2019. Assuming that we are interested in the total revenue for each customer for all time, we can use the following query:
SELECT
c.CustomerKey,
c.FirstName,
c.LastName,
SUM(f.SalesAmount) as TotalRevenue
FROM
dbo.FactInternetSales f
JOIN dbo.DimCustomer c ON f.CustomerKey = c.CustomerKey
GROUP BY
c.CustomerKey,
c.FirstName,
c.LastName
ORDER BY
TotalRevenue DESC
This query joins the FactInternetSales and DimCustomer tables on the CustomerKey column, calculates the sum of sales amount for each customer using the SUM function, groups the results by customer key, first name, and last name, and orders the results by total revenue in descending order.
To divide the customers into quartiles based on total revenue, we can use the NTILE function. The NTILE function divides a result set into a specified number of groups, assigning a group number to each row. For example, to divide the customers into four quartiles based on total revenue, we can use the following query:
SELECT
CustomerKey,
FirstName,
LastName,
TotalRevenue,
NTILE(4) OVER (
ORDER BY
TotalRevenue DESC
) as Quartile
FROM
(
SELECT
c.CustomerKey,
c.FirstName,
c.LastName,
SUM(f.SalesAmount) as TotalRevenue
FROM
dbo.FactInternetSales f
JOIN dbo.DimCustomer c ON f.CustomerKey = c.CustomerKey
GROUP BY
c.CustomerKey,
c.FirstName,
c.LastName
) as CustomerRevenue
This query uses the previous query as a subquery to calculate the total revenue for each customer and then applies the NTILE function to divide the customers into four quartiles based on total revenue. The Quartile column in the result set indicates which quartile each customer belongs to, with Quartile=1 being the top quartile (highest revenue) and Quartile=4 being the bottom quartile (lowest revenue).
We can now save this as CSV if we wanted to and show this as a consolidated table in something like Excel:
From this, we can gain a better understanding of our customer profile at high level. It's clear to see that half of our entire customer base is only generating 1.9% of our total revenue...
❤️ Enjoyed this article?
Forward to a friend and let them know where they can subscribe (hint: it's here).
