Churn Rate

The churn rate refers to the percentage of customers who have stopped using a particular product or service over time. A high churn rate can negatively affect a business by losing customers faster than acquiring new ones. It is widely known that retaining an existing customer is more cost-effective than acquiring a new one. As a Data Analyst, you may be required to calculate the churn rate to measure performance, gain insight into customer retention, and provide valuable information to maintain customer loyalty. 

By identifying patterns in churn rate, data analysts can help businesses develop strategies to reduce it and forecast future revenue and growth. If you are working with a churn rate and want to level up, know common ways to minimize it before analyzing data. Here we have some: 

1. Providing excellent customer support 
2. Offer discounts, free trials, or loyalty programs to encourage customers to stay 
3. Understand the customer’s needs and preferences. You could bring this information to tailor the product or service to meet customers’ needs. 
4. Continuously improving the product or service based on customer feedback  
5. Keep customers informed about new features, updates, and changes to the product or service 
6. Building long-term relationships with customers rather than just making a sale. 

If the churn rate is not great, you might identify a gap that touches one of the topics above.

For example, consider the following subscription_activity table.

Using this table, we can write a query to calculate churn rate for a specific period (such as January 2022) by identifying the number of users whose subscriptions have ended, divided by the total number of users in the period.

Here’s an example query

SELECT COUNT(DISTINCT CASE WHEN subscription_end_date >= '2022-01-01' AND subscription_end_date < '2022-02-01' THEN user_id ELSE NULL END) AS churned_users, 
  	COUNT(DISTINCT CASE WHEN subscription_start_date <= '2022-01-31' AND subscription_end_date > '2022-01-01' THEN user_id ELSE NULL END) AS total_users, 
  	(COUNT(DISTINCT CASE WHEN subscription_end_date >= '2022-01-01' AND subscription_end_date < '2022-02-01' THEN user_id ELSE NULL END) / COUNT(DISTINCT CASE WHEN subscription_start_date <= '2022-01-31' AND subscription_end_date > '2022-01-01' THEN user_id ELSE NULL END)) AS churn_rate 
FROM subscription_activity
WHERE subscription_start_date <= '2022-01-31' AND subscription_end_date >= '2022-01-01';

This query first counts the total of unique users who canceled their subscriptions (churned) and the second count brings the total of unique users who kept active. The third count divides the number of churned users by the total number of users during the period. The result is the churn rate.
Note that the WHERE clause filters the data only to include users who had active subscriptions during January 2022. This is necessary to ensure that we’re only counting churn for active users during the period we’re interested in.

Here’s the result:

Consider this a starting point for calculating churn rate in SQL, as the exact definition of churn rate may vary depending on the business and the context. Also, there may be additional factors to regard (such as customers who churned and returned, for example).

Extra Credit

There is a lot of repetition in the SQL statement above.  CoginitiScript macros may be used to accelerate development and simplify.  The macro below takes two arguments: a date, and a whence parameter to specify the active-vs-expiring subscription logic, and returns a composed SQL column spec calculating the number of subscribers.  The resulting SQL will reflect the input parameters to produce the desired count aggregation.

#+macro num_subscribers_in_month(date, whence)
#+meta { 
  :doc "Return number of active subscribers ...
          whence = ENDING :  whose subscription end in the month containing [date] 
          otherwise:         whose subscription ends after the month containing [date]"
} 
#+begin 
  SUM(
    IFF(
      subscription_start_date <= '{{ date }}'
      AND
        #+if whence == "ENDING" then
          subscription_end_date BETWEEN 
            DATE_TRUNC(MONTH, '{{ date }}' ) 
            AND 
            LAST_DAY('{{ date }}')
        #+else
          subscription_end_date > LAST_DAY('{{ date }}')
        #+end
      ,1,0
    )
  )
#+end

SELECT {{ num_subscribers_in_month("2022-02-01", "ENDING") }} AS churned_users, 
  	{{ num_subscribers_in_month("2022-02-01", "ACTIVE") }} AS total_users, 
        churned_users / total_users AS churn_rate 
FROM subscription_activity
;