Deals Of The Week - 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
  DATE_PART('year', registration_date) AS year,
  DATE_PART('week', registration_date) AS week,
  COUNT(CASE
    WHEN last_order_date - registration_date > INTERVAL '30' day
    THEN customer_id
  END) * 100.0 / COUNT(customer_id) AS percent_active_30d,
  COUNT(CASE
    WHEN last_order_date - registration_date > INTERVAL '60' day
    THEN customer_id
  END) * 100.0 / COUNT(customer_id) AS percent_active_60d,
  COUNT(CASE
    WHEN last_order_date - registration_date > INTERVAL '90' day
    THEN customer_id
  END) * 100.0 / COUNT(customer_id) AS percent_active_90d
FROM customers
GROUP BY
  DATE_PART('year', registration_date),
  DATE_PART('week', registration_date)
ORDER BY
  DATE_PART('year', registration_date),
  DATE_PART('week', registration_date);

Result:

year week percent_active_30d percent_active_60d percent_active_90d
2019 7 28.571428571428 28.571428571428 14.285714285714
2019 8 50.000000000000 33.333333333333 16.666666666666
2019 9 14.285714285714 14.285714285714 0.000000000000

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, percent_active_10d, percent_active_30d, and percent_active_60d. 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.