Happy Bootstrapper
Forgot to Track SaaS Churn? Let’s Dig It Up from Your DB - Happy Bootstrapper

Forgot to Track SaaS Churn? Let’s Dig It Up from Your DB

If you forgot to measure your SaaS churn, you are not alone. The most common question people ask about my upcoming product is “Can I use SaaS Compass if I haven’t collected any metrics?”

SaaS churn, like most of the SaaS metrics, don’t need to be tracked similarly than you track traffic or marketing funnel conversions. People calculated metrics for subscription businesses year and years before Kissmetrics, Mixpanel and other SaaS analytics tools came along.

It’s pretty easy to dig up the metrics from your database. I’ll teach you how to get the data for calculating customer churn rate. It’s also possible to get the churn from your financial statements (when properly done) but getting it from the DB is often easier.

I use pseudo-SQL so you can choose whether to use joins or subqueries. This also makes it possible to use scripts if you want.

Monthly gross churn rate for paying customers 

Let’s calculate May’s monthly gross churn rate for paying customers.

There is no standard way to calculate SaaS churn. This is just one of the ways, but it’s quite commonly used. Let’s get started.

Select unique CUSTOMER IDs from SUBSCRIPTION PAYMENTS where DATE is between April 1st and April 30th

You just got CUSTOMERS WHO PAID IN APRIL. Count the customer ids that were returned.

Assuming your customers pay you once per month, these are the customers that had the possibility to stop paying in May.

Count unique CUSTOMER IDs from SUBSCRIPTION PAYMENTS where DATE is between May 1st and May 31st and filter by CUSTOMERS WHO PAID IN APRIL.

You just got CUSTOMERS WHO ALSO PAID IN MAY. Count them too.

These are the retained customers. They had a possibility to churn, but they paid you instead.

Now we are ready to calculate the SaaS churn rate using this data.

 “How do I calculate churn if my customers are not paying?”

You have a problem. You are not getting any money! Ok, that was low, I’m sorry.

Seriously talking, you too can use the approach from this post, but you need to come up your own way to define active and lost customer for the SaaS churn calculations.

Do you offer yearly subscriptions or payment options?

If the customer paid for the whole year in April then you shouldn’t count him as lost customer when he doesn’t pay in May.

When you are calculating monthly churn you should exclude people who paid for something else than their monthly subscription. How you identify and exclude these guys is up to you. One option is to use price as a filtering criteria.

Also, you should include churned customers who paid a yearly subscription in May – last year.

If only few people choose yearly subscription, you may decide to just forget them from monthly churn. They are included in the yearly churn rate after all. But if yearly subscriptions are common, you’ll need to fix the churn rate calculations.

You’d do that by also counting in the yearly payments from May 2012. Then you’d find out how many of these people also paid in May 2013.

So the “customers that had the possibility to stop paying in May” would be

CUSTOMERS WHO PAID IN APRIL + YEARLY PAYERS IN MAY 2012

and the “retained customers” would be

CUSTOMERS WHO ALSO PAID IN MAY + YEARLY RE-PAEYRS IN MAY

Calculate SaaS churn rate via retention rate

Now, let’s do the actual math. We’ll calculate your retention rate first. That’s the percentage of customers who didn’t leave you.

Retention Rate for SaaS Churn = CUSTOMERS WHO ALSO PAID IN MAY / CUSTOMERS WHO PAID IN APRIL * 100%

So if you had 100 customers in April and 90 customers in May, your monthly retention rate would be 90%. You’ll easily get your SaaS churn rate from the retention rate.

CHURN RATE = 100% – RETENTION RATE

Now there’s May’s monthly gross churn rate for paying customers.

But please don’t rush to compare yourself to others before you read my article about problems in churn rate comparisons.

Segmented monthly gross churn rate for paying customers

As soon as you have enough customers to do so, you’ll want to segment your calculations by plan. Let’s calculate May’s churn rate again, but now by plan.

Select CUSTOMER IDs from ACCOUNTs where PLAN ID is 1 

You just got PLAN 1 CUSTOMERS.

Select unique CUSTOMER IDs from SUBSCRIPTION PAYMENTS where DATE is between April 1st and April 30th  and filter by PLAN 1 CUSTOMERS 

You just got PLAN 1 CUSTOMERS WHO PAID IN APRIL. Count the customer ids that were returned. 

Next, we’ll get the customers you retained in May.

Count unique CUSTOMER IDs from SUBSCRIPTION PAYMENTS where DATE is between May 1st and May 31st and filter by PLAN 1 CUSTOMERS WHO PAID IN APRIL.

You just got PLAN 1 CUSTOMERS WHO ALSO PAID IN MAY. Count them too.

Ta-da! Now you can calculate your retention rate and SaaS churn rate like we did previously. Repeat for all plans.

“I want to calculate the yearly customer churn rate too!”

You can use the same principle. You’d just pick the customers who had the possibility to churn a year ago as your baseline.

Did you find this useful?

If so, don’t miss out my future articles. Just drop your name email to the box below and you’ll get them delivered straight to your inbox.

Share and Enjoy!

    2 comments

    1. Great Article!

      I’m working through how I’ll measure Churn at my work place at the moment. It’s not easy. The issue is, we are an eCommerce organisation and rather than having subscriptions, customers are either placing an order with us or they are not.

      Active and Cancelled aren’t reliable as you can be an active customer but not have a future order.

      Have you got any experience working in this situation?

      • Hi Bree,

        I don’t have any working experience on eCommerce organization metrics, so you’ll have to take what I say with a grain of salt.

        I think that eCommerce businesses might want to calculate repurchase rates, purchases per year and/or purchase frequency instead of churn. And if you want to calculate churn, or revenue churn (“how much money was lost because of the missed repurchases), you’ll have to go through those metrics as they represent the customer activity in eCommerce business.

        eCommerce definitely falls to the category where you have to measure customer’s real activity. One option is to use the repurchase rate to evaluate when the customer is “lost”. If most of your customers make their repurchase within 90 days, for example, you can count the customers who didn’t repurchase in 90 days as churned.

        I wrote an article about defining the active and lost customers couple of weeks ago. You might want to check it out.

    Leave a Reply

    Your email address will not be published. Required fields are marked *

    You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>