Hi,
I tried to create a trend line in Tableau to show the churn rate over last 6 months.
What I got is a sales table with sales order raw data.
Churn rate is defined as: No. of Customers with no sales more than 6 months / No. of Customers with sales in last 12 months
As shown in below example, the churned rate for June 2015 is 20%
Below is the example of the churned rate in last 6 months I would like to create in Tableau
I can calculate the current month's churn rate, but don't know how to calculate the churn rate in the past 6 months.
Could anyone help with this? Thanks,
Alex
Hi Alex -
Wish I had seen this sooner... I've had to do similar views in the past... hope this isn't too late to help! This uses table calc's to first find the status for each customer and then uses more table calc's to roll everything up. I would be very interested to know if anyone has a more elegant solution...
... looks like SuperStore's churn rate is not only seasonal, but has seen some nice improvement over the last few years!! Who knew??
The first tab shows the canvas of calculations required to determine the status for each customer. Initially, we need the following table calc's (set to calc across date):
[Sales - Prior 6 Months]: window_sum(sum([Sales]),-5,0)
[Sales - Prior 12 Months]: window_sum(sum([Sales]),-11,0)
[Active Customer - 6 Month]: if [Sales - Prior 6 Months]>0 then 1 else 0 end
[Active Customer - 12 Month]: if [Sales - Prior 12 Months]>0 then 1 else 0 end
From the formulas above, we can determine if each customer has had sales in the past 6 or 12 months. From here, we can then roll them up and calc the churn rate with more table calc's (set to calc across customer):
[Active Customers - 6 Month]: window_sum([Active Customer - 6 Month])
[Active Customers - 12 Month]: window_sum([Active Customer - 12 Month])
[Churn %]: 1 - ([Active Customers - 6 Month]/[Active Customers - 12 Month])
Unfortunately, this leaves us with a bunch of stats across a bunch of customer rows. The second tab shows how to filter the canvas to something more usable:
[Filter - Rows] is an Index() table calc to allow us to remove all but the first "row" of marks from the view.
[Filter - Columns] is another Index() table calc to allow us to remove the first 12 months of data from the view (the churn calc isn't defined until we build up 12 months of history). Note: if only the most recent x months are desired in the view, this filter can be 'reversed' using "window_max(index())-index()+1"... the filter would then be set to take months 1-x.
The third tab has the dual axis chart with the churn rate as a red trendline. I added blue bars for the # of customers that have purchased within the last 6 months and gray reference bars to show the # of customers that have purchased in the last 12 months. It was really neat to see the customer growth and seasonality in the churn rate over time in addition to the churn rate itself...
The SuperStore example didn't seem to suffer any performance issues... but, by adding "if first()=0 then..." to the consolidating table calc's that run across customer, performance may be improved.
Hope this helps!
Brian
