Tableau: Dynamic Customer Churn Rate

If you've stumbled upon Andy Kriebel's YouTube tutorial on calculating Customer Churn Rate in Tableau, you might be wondering how to do it dynamically using parameters. In this brief blog, I'll demonstrate how you can achieve this using similar four calculations bundled up with a parameter and a core logic, allowing users to control the "Last N months" value for calculating churn rates.

Before we hop on to parameterising it, let's recap Andy' four basic calculations which he discussed in the video.

Months
//Creating Custom Date with Months Date Value from Order Date

Now, for this example let's say we do Last 3 months churn rate

1. Customers Last 3 Months
// Counting customers from 0 index till 3 months so last 0 to -2
WINDOW_SUM(COUNTD([Customer Name]),-2,0)

2. Customers Last 6 Months
// Counting customers from 0 index till 6 months so last 0 to -5
WINDOW_SUM(COUNTD([Customer Name]),-5,0)

Diagram — Core Logic: Looking Back Months

3. Churn Rate
// % Customers churned out in last 3 months
([Customers Last 6 Months]- [Customers Last 3 Months])
/ [Customers Last 6 Months]

4. Last 3 Months
// to hide the first 3 null values
INDEX()<=3

Moving forward, we aim to make the customer churn rate calculation dynamic by introducing a single parameter named "Select Last N Months." This parameter empowers users to specify the desired number of months for calculating customer churn rate.

Let's first create this parameter,

Select Last N Months

Note: Ensure that the maximum Range is set up correctly to fit the range of dates of available data, to allow users the relevant values of selection.

Centred around this user defined parameter, we will modify the aforementioned 4 calculations, to set up the dynamic customer churn rate calculations.

Let's first look at the definition of customer churn rate which would again help us in understanding the logic behind these two formulas:

Customer Churn Rate = (Customers at the Beginning of Period - Customers at End of Period)/ Customers at the Beginning of Period

If customers at the beginning of last 3 months is 500 and at end of last 3 months is 450, then the churn rate is (500-450)/500 = 10%

N Months (End of Period)
// Reducing the Selected period by 1 since our indexing starts from 0 and introducing a negative sign since we are looking back n months
-([Select Last N Months]-1)

N+n Months (Beginning of Period)
// Beginning of the Time Period.  Based on the core logic shown above, we would go back twice to reach the beginning of that period.

// and -1 is again for indexing purposes.

-((2*([Select Last N Months])-1))

1a) Customers Last N Months
WINDOW_SUM(COUNTD([Customer Name]),[N Months],0)

2a) Customers Last N+n Months
WINDOW_SUM(COUNTD([Customer Name]),[N+n Months],0)

3a) Churn Rate (N Months)
([Customers Last N+n Months]- [Customers Last N Months])
/[Customers Last N+n Months]

4a) Last N Months
INDEX()<=[Select Last N Months]

By leveraging these derived formulas, we have the capability to create a dynamic customer churn rate calculation. This can be visually represented on a graph, enabling users to interact with parameters, similar to the embedded Tableau Public Dashboard provided as an example.

Feel free to reach out if you come across any errors or have suggestions for improvement. I'm always open to feedback!

Signing off,

Yash

Tableau: Dynamic Customer Churn Rate

Recent Posts