End of Summer - hours only!Up to 80% off on all courses and bundles.-Close
Introduction
Churned customers
5. Percentage of churned customers in weekly cohorts
Customer retention charts
Summary

Instruction

Good job! We already know how many churned customers there are in each registration cohort. However, we would like to compare the number of churned customers to the total number of customers in a given registration cohort. How can we do that? Let's look:

SELECT
  DATEPART(Year, RegistrationDate) AS Year,
  DATEPART(Week, RegistrationDate) AS Week,
  COUNT(CustomerID) AS AllCustomers,
  COUNT(CASE WHEN DATEDIFF(Day, LastOrderDate, GETDATE()) > 30 THEN CustomerID END) AS ChurnedCustomers,
  COUNT(CASE WHEN DATEDIFF(Day, LastOrderDate, GETDATE()) > 30 THEN CustomerID END) * 100.0 / COUNT(CustomerID) AS ChurnedPercentage
FROM Customers
GROUP BY
  DATEPART(Year, RegistrationDate),
  DATEPART(Week, RegistrationDate)
ORDER BY
  DATEPART(Year, RegistrationDate),
  DATEPART(Week, RegistrationDate);

As you can see, we used COUNT(CustomerID) to count all the customers in a given cohort. Then we used COUNT(CASE WHEN ...) to include only customers that placed an order no later than 30 days ago.

Next, we calculated the percentage of churned customers in the cohort by dividing the number of churned customers by the number of all customers. Note that we multiplied the numerator by 100.0 (a float) and not 100 (an integer) to avoid integer division.

Exercise

Modify the template so that it shows the following columns: Month, AllCustomers, ChurnedCustomers, and ChurnedPercentage.

Previously, we only saw churned customer counts with no reference to total customer counts. Now we can clearly see the relation between these figures. What do you think about the churn values? Are they high?

Stuck? Here's a hint!

Move the DATEDIFF() part from the WHERE clause into a COUNT(CASE WHEN ...) clause.