Let's first define what we mean by a "churned customer." In our business, we'll define a churned customer as a customer that hasn't placed an order in more than 30 days. This definition will be a starting point – we'll use other criteria in some of the other examples too. In your own business, you can use criteria that fit your business better.
The simplest question related to customer churn is:
As of today, how many churned customers are there in total?
Here's a query that will provide the answer:
SELECT COUNT(*) AS ChurnedCustomers FROM Customers WHERE DATEDIFF(Day, LastOrderDate, GETDATE()) > 30;
In the WHERE clause, we used the DATEDIFF() function to find the number of days between LastOrderDate and today. To get the current date, we used the GETDATE() function.
Out of customers registered in 2017, find the number of churned customers. Define a churned customer as one who hasn't placed an order in more than 60 days. Show the count in a column named ChurnedCustomers.
Does this number seem large?
Add the following condition in the WHERE clause:
RegistrationDate >= '20170101' AND RegistrationDate < '20180101'
