Hello and welcome to the fourth part of the course! This time, we're going to take a look at customer churn.
Inevitably, customers will stop using our services at some point. There can be many reasons for this: they may want to go to our competitors, or they may simply no longer need our services. This phenomenon is called "customer churn." On the other hand, "customer retention" is when we've succeeded in keeping a customer active during a given period.
It's not always easy to determine if a given customer has "churned." After all, customers don't usually explicitly tell us when they leave, especially in businesses like e-stores. Instead, we need to identify such customers ourselves and define our own churn criteria. Depending on the business type, you may want to use the last login date, the last purchase date, the last subscription renewal event, etc. Each business will approach customer churn differently.
When you apply SQL patterns covered in this part to your own business, you should adapt the "churn" criteria in the SQL queries to match the ones used in your business.
In this part, you'll learn how to compute the number of churned customers and how to prepare a customer retention chart. The customer retention chart might look like this:
| year | week | after_30_days | after_60_days | after_90_days |
|---|---|---|---|---|
| 2018 | 45 | 58.33 | 50 | 33.33 |
| 2018 | 46 | 71.43 | 57.14 | 42.86 |
| 2018 | 47 | 83.33 | 66.67 | 66.67 |
For each weekly registration cohort, the chart shows the percentage of customers still active 30, 60, and 90 days after the registration date. Customer retention figures will inevitably decrease over time. Comparing these values can help us determine if our customers are leaving us too quickly.
Now that we know what we're looking for, let's get started!
Click to continue.
