Salesforce: Calculating Churn Rate
We’re going to be looking at how to calculate annual churn rate in Salesforce with just a couple of custom fields on the account object, and a custom formula on a report. First, go to reports in the top menu. Then, click into the annual churn rate. You can see that I have a churned field, a customer acquisition date, and an annual churn rate. The annual churn rate is just a grand total summary at the end of the report.
Two Key Fields for Calculating Churn Rate
If we go to filters, I have customer acquisition date and churn date as filters. Let’s go and look at both of these fields first to see how we’ve set them up. On customer acquisition date, you can see it’s a roll up summary. It’s going to be the earliest close date. We’re having a roll up to the first close won date of an opportunity related to an account. If you win six opportunities with an account, we’re looking for that first win, which is going to be our customer acquisition date.
Now, if we go to churn date, you can see this is just a date field. So nothing too special here. We’re just using a date field. I’ll show you some automation that we have populating that. We also have churned as the checkbox. This is also just a checkbox field. This can be automated or you can do it manually.
If I go to workflows here, you can see that I have just a churn date workflow rule. When the churn checkbox is checked, it automatically stamps the churn date with the date of today. So this happens when an account is churned. It stamps a churn date.
Can I Automatically Calculate a Churn Rate?
There is a simple way to automate that. I didn’t want to do it on this example because it really depends on what your structure looks like in Salesforce if you can automatically calculate a churn. For example, maybe you have an opportunity where the type is renewal. If you lose a renewal, then this is automatically a churn. So there are circumstances where you can automate this whole process.
Forumla for Calcuating Churn Rate
For today’s example, I have both churn date and churned as manual entry fields. And then the only other item in this is customer acquisition date. Let’s go back over to the report and check out what we’ve done. We’re looking at customer acquisition date – all time for our main filter, because I want to see all of our customers throughout time. That’s the main filter I want to set before we move into these specific date filters.
Then, I want to check customer acquisition date and churn date equals this year. The reason why I have the customer acquisition date filter first is that there might be a circumstance where you want to see a quarterly churn rate or monthly churn rate. You may want to play with this final number and check it out in different types of measurements.
Our first filter, customer acquisition date, has to be set to equal this year and churn date has to set to equal this year. This is an OR function. So as long as one of these is true, which again, we already have customer acquisition date set to all time , so this one’s always going to be true, then we’re really just pulling in the churn date. The records that had a churn date within the year as well.
If we look at the outline, I’ve added some standard fields. Then, I threw in the churn as the grouping, because ultimately we’re going to want to check how many of these are true versus the total number of records. If we go to annual churn rate, you can see that the formula that we’re using is PARENTGROUPVAL, accounts that have churned as the summary, divided by row count. I want to take all the accounts that have churned, divided by the total amount of accounts that populate in my report. I want to calculate this at the grand summary level. You can see I’m not doing it at the road level here in the background.
And what you end up with is a report with your churn rate. So again, there could be some different caveats just depending on how you have opportunities and accounts set up. But really you just need to identify which accounts have churned, and then within your formula, be able to divide that into the amount of accounts that you have acquired that year. So that’s why this customer acquisition date, which is a simple roll up, becomes very important.