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

Instruction

Great! It's time to wrap things up. First, a quick review:

  1. To count the number of churned customers (defined as people who haven't placed an order in the last 30 days), use:
    SELECT COUNT(*)
    FROM Customers
    WHERE DATEDIFF(Day, LastOrderDate, GETDATE()) > 30;
    
  2. To count the number of churned customers in weekly registration cohorts, use:
    SELECT
      DATEPART(Year, RegistrationDate) AS Year,
      DATEPART(Week, RegistrationDate) AS Week,
      COUNT(*) As ChurnedCustomers
    FROM Customers
    WHERE DATEDIFF(Day, LastOrderDate, GETDATE()) > 30
    GROUP BY
      DATEPART(Year, RegistrationDate),
      DATEPART(Week, RegistrationDate)
    ORDER BY
      DATEPART(Year, RegistrationDate),
      DATEPART(Week, RegistrationDate);
    
  3. To calculate the percentage of churned customers in weekly registration cohorts, use:
    SELECT
      DATEPART(Year, RegistrationDate) AS Year,
      DATEPART(Week, RegistrationDate) AS Week,
      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);
    
  4. To create a customer retention chart, use:
    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);
    

Okay, time for a short quiz!

Exercise

Click Next exercise to continue.