End of Summer - hours only!Up to 80% off on all courses and bundles.-Close
Introduction
Churned customers
Customer retention charts
7. The customer retention chart
Summary

Instruction

Perfect! The last report we'll create will be a customer retention chart. For each weekly registration cohort, we want to see the percentage of customers still active 30, 60, and 90 days after the registration date. Take a look:

SELECT
  DATEPART(Year, RegistrationDate) AS Year,
  DATEPART(Week, RegistrationDate) AS Week,
  COUNT(CASE WHEN DATEDIFF(Day, RegistrationDate, LastOrderDate) > 30 THEN CustomerID END) * 100.0 / COUNT(CustomerID) AS PercentActive30d,
  COUNT(CASE WHEN DATEDIFF(Day, RegistrationDate, LastOrderDate) > 60 THEN CustomerID END) * 100.0 / COUNT(CustomerID) AS PercentActive60d,
  COUNT(CASE WHEN DATEDIFF(Day, RegistrationDate, LastOrderDate) > 90 THEN CustomerID END) * 100.0 / COUNT(CustomerID) AS PercentActive90d
FROM Customers
GROUP BY
  DATEPART(Year, RegistrationDate),
  DATEPART(Week, RegistrationDate)
ORDER BY
  DATEPART(Year, RegistrationDate),
  DATEPART(Week, RegistrationDate);

Result:

Year Week PercentActive30d PercentActive60d PercentActive90d
2019 7 28.571428571428 28.571428571428 14.285714285714
2019 8 50 33.333333333333 16.666666666666
2019 9 14.285714285714 14.285714285714 0

By looking at a single row, we can analyze a given signup cohort and see what percentage of customers were still active after one, two, or three months. Customer retention figures will inevitably decrease over time. Comparing these values can help us determine if our customers are leaving us too quickly.

As you can see, we used the COUNT(CASE WHEN ...) construction three times, each time with a slightly different condition. Because of that, we can include multiple metrics in a single query. Other than this, the query used all the standard features we've already gotten to know.

Exercise

Create a similar customer retention chart for weekly signup cohorts from 2017. Show the following columns: Week, PercentActive10d, PercentActive30d, and PercentActive60d. Order the results by week.

Stuck? Here's a hint!

Add a WHERE clause. Modify the COUNT(CASE WHEN ...) constructions by changing the number of active days.