Is the distinct count correct? You first count the distinct numer for each month an then use the window sum function. If a customer bought in two month would you not count him twice?
Hey Andy, since, I am beginner It's hard to interpret what's going on, on that viz. Little bit of explanation would have add so much value to it. Waiting for another video on customer churn. Thank you
Love this thank you for this and all of your videos. Question, for me I would have to do something more complex ie looking at guest ids in the 6 month but not in the 3 month and vice versa (ie new customers). Any suggestions on how to approach that in Tableau?
Can we have a negative retention value? I think with this formula, if there are new users acquired, will might get a negative retention. I can be wrong!
This video is great. Could you explain why you in 2:07 when calculating the churn rate are taking customers from the last 6 month - customers from the 3 months? 3:50 how is the 3 month churn rate and number of customers from the last 6 month related? 4:00 where does the 12 months come from? Why 12 months when you said let's calculate the 6 months churn rate?
How can I calculate the churn if a customer is paying a flat fee for my service and, depending on the service, they could be an active customer for 2+ years?
Does the 60-day Churn rate represent: 1) The percentage of customers who left during the last 60 days relative to the total number of customers 60 days ago. 2) The percentage of customers who were active within the first 60 days and left during the last 60 days, relative to the total number of customers 60 days ago. This clarifies whether the churn rate is based on the total customers who left in the last 60 days or specifically those who were active in the initial 60 days of their contract.
Thanks Andy, amazing to the point illustration. what if I want to calculate churn rate for a month over month, what I need to put in windows function instead of -2, is it -1?
Hey Andy, awesome video!! Thank you! I have a small doubt about this chart. If I acquire 200 customers in a 3 month window and if I am losing 100 customers in that same window, my churn will be negative. Is there some way we can prepare a chart that factors in the new acquisitions when calculating churn?
Hi Andy, thanks this is really good. Can you please create for the SaaS products? I think the way we can do SaaS products is different. Due to the active and canceled products, we cannot get a churn rate.
Please correct me if I am wrong but it seems there are 2 mistakes in the video: - recurring customers aren't taken into account - percentages aren't calculated correctly
With all due respect, this is just a nice example of calculating the moving ratio of the number of customers in the first half of the period to the whole period. This example does not calculate the number of customers who have left the company, gone into full churn, and no longer plan to do business. The real customer churn rate is calculated using a different method.
What method is that? I'd like to know what I did wrong. As for those that no longer plan to do business, that's impossible to know for any business. A customer can always come back.
@@vizwiz Andy, thank you for your quick response. I fully share your opinion that it is impossible to know if a client is lost or not. Even the client himself does not know whether he will continue working with the company next year or not. - The method that I want to give as an example requires that company managers are not afraid to admit the fact that the company loses customers regularly. - This method is suitable for companies that operate in the transactional b2b market, without obligatory contracts with clients. - To get a more accurate result, you need to have information on orders for a period of two years or more. Step 1: Define clearly Who the company's Active (Operating) customer is. For example, we recognize a client as active (operating) if No More Than 6 Months have passed from the date of his last transaction to the date of the report. Step 2. Calculating number of active (operating) customers - We calculate the number of active (operating) clients at the beginning of the reporting period (for instance, a quarter). Let's call it "AC-Start". - Similarly, count the number of active (operating) clients as of the last date of the quarter and subtract the number of new clients acquired during that quarter. Let's call this metric "AC-End-Net". Step 3. Calculate the customer churn rate (CCR) CCR = ( [AC-End-Net] - [AC-Start] ) / [AC-Start] Note: For these numbers to make business sense, it is highly recommended to segment the CCR number. For example, calculate the CCR for each region separately. Or pre-segment the customer base by the average duration of the interval between purchases.
A question for seasoned Tableau practitioners, what are the tips you could give to those who are new to Tableau? Also, what keeps you motivated or how do you keep motivated to learn more about tableau?
Simple, practice. That’s the only way to get better. Other than that, find people who’s work you like and follow them. Download workbooks you find interesting and see how they were created. Participate in community projects like Makeover Monday; this will give you practice every week. Check out the Tableau Starter Kit too. It has a really useful guide.
Also I once I put a fillter for country when I choose only one country its fillter the presentage lower then 100% . only when I choose all countries it show 100%. How should I approach this?
Hi.. I have a question. In filters (wildcard search) , we have to give certain constraints that the user needs to select a minimum of 3 to 4 chars/nums in the wildcard search. then only the related info needs to display in the dashboard. Issue:- if the user enters 1 char also then the data is displaying and there will be large info.... starts with and ends with will not work out here because there will be multiple id's which starts with different numbers.
I’m trying to understand the L3M and L6M calculations… it seems like if you have the same 100 customers over the entire history the calcs would give you 300 and 600 respectively. This would give you a 50% churn rate, which isn’t correct. You would want to know how many distinct customers exist over the 3 and 6 month periods (100 and 100 in my example), not the sum of the count in each month, right?
@@vizwiz but the COUNTD will only count it per month. The WIND_SUM will then only count the COUNTD per month. What if a customer bought two times? Then he will be counted twice?
@@vizwiz I'm finding the same as @JensGrafe if you have a customer with multiple purchases it's summing the count by month and over stating the number of customers.
For some things yes, for others no. I can think of tons of things that are painstakingly difficult in PBI. No tool is perfect. They each have benefits, which is great for all of us.
You are a STAR!! Thank you so much! Yes, Please, teach us as much as you can!!
More to come!
Great Job, Andy. Can we please see the parameterised version?
Appreciate how efficiently you've done this.
Thanks!
Is the distinct count correct? You first count the distinct numer for each month an then use the window sum function. If a customer bought in two month would you not count him twice?
Agree! That’s exactly what my concern is about this calculation.
Agree. This is my concern as well. Churn rate doesn't seem to be right this way.
@@jeromevanbeek9070 I think the 6 month calculation needs to be WINDOW_SUM(COUNTD([field]),-5,-3) to avoid the double count
great dashboarding skills. simple and concise. Hats off from data analytics guy
Hey Andy, since, I am beginner It's hard to interpret what's going on, on that viz.
Little bit of explanation would have add so much value to it.
Waiting for another video on customer churn.
Thank you
Are you able to give more detail about the part that wasn't clear? Also, what would you have said differently? That would be helpful to me.
Love this thank you for this and all of your videos. Question, for me I would have to do something more complex ie looking at guest ids in the 6 month but not in the 3 month and vice versa (ie new customers). Any suggestions on how to approach that in Tableau?
Can we have a negative retention value? I think with this formula, if there are new users acquired, will might get a negative retention. I can be wrong!
Wow this is so useful, thanks appreacite it!
Hey Andy,
Always Inspire by your new learning videos. Thanks you so much :)
Can we use "FIRST()>= -2 " to hide First 3 Data points.
Yes you can! Great job! You would exclude TRUE in that case.
Fantastic video as always. And thanks especially for the final tip about parametrization!
You’re welcome
OMG Excellent Video and it was so easy to follow along. Do you by any chance have a video for Rentention Rate similar to this?
I do indeed.
#Tableau - Calculate Customer Retention & Cohort Analysis
ruclips.net/video/PwL8EySm8Yo/видео.html
Please create the video for custom churn rate, Thanks
Hi Andy, this is awesome. 1 question thou. How would I do this if I had a parameter showing the financial year and calendar year?
This video is great. Could you explain why you in 2:07 when calculating the churn rate are taking customers from the last 6 month - customers from the 3 months?
3:50 how is the 3 month churn rate and number of customers from the last 6 month related?
4:00 where does the 12 months come from? Why 12 months when you said let's calculate the 6 months churn rate?
How can I calculate the churn if a customer is paying a flat fee for my service and, depending on the service, they could be an active customer for 2+ years?
Hi Andy ,but just want to compare with my last 3,6 months but in the viz we are seeing the whole data ?,again do we need to filter on month ??
Does the 60-day Churn rate represent:
1) The percentage of customers who left during the last 60 days relative to the total number of customers 60 days ago.
2) The percentage of customers who were active within the first 60 days and left during the last 60 days, relative to the total number of customers 60 days ago.
This clarifies whether the churn rate is based on the total customers who left in the last 60 days or specifically those who were active in the initial 60 days of their contract.
Thank you Andy
hi andy, please do custom churn rate with paramaters
Thanks Andy, amazing to the point illustration.
what if I want to calculate churn rate for a month over month, what I need to put in windows function instead of -2, is it -1?
Correct!
Hey ! Can you create a video calculate Customer life time value? this would be amazing!
Great suggestion! I'll add it to my list.
Hey Andy, awesome video!! Thank you! I have a small doubt about this chart. If I acquire 200 customers in a 3 month window and if I am losing 100 customers in that same window, my churn will be negative. Is there some way we can prepare a chart that factors in the new acquisitions when calculating churn?
Thanks so much for these! parameter video would be great!
How about this? ruclips.net/video/opfVV1maNVw/видео.html
You can also search the channel for "parameters" and you'll find lots more. Does that help?
I dont have any data for the date or time period!
awesome, so helpful
Could we also get something similar for support cases . New cases ,case being worked ,cases closed, cases rejected
Of course! It should be a similar method. Swap out your dimensions for what I demo.
Hi Andy, thanks this is really good. Can you please create for the SaaS products?
I think the way we can do SaaS products is different.
Due to the active and canceled products, we cannot get a churn rate.
I don't have data for that.
@@vizwiz I can share with the data set. Please have a look at it. drive.google.com/file/d/10AG35cHs-BG8wajwBgsIIVYPBcvpP-Qc/view?usp=share_link
Please correct me if I am wrong but it seems there are 2 mistakes in the video:
- recurring customers aren't taken into account
- percentages aren't calculated correctly
With all due respect, this is just a nice example of calculating the moving ratio
of the number of customers in the first half of the period to the whole period.
This example does not calculate the number of customers
who have left the company, gone into full churn, and no longer plan to do business.
The real customer churn rate is calculated using a different method.
What method is that? I'd like to know what I did wrong.
As for those that no longer plan to do business, that's impossible to know for any business. A customer can always come back.
@@vizwiz Andy, thank you for your quick response.
I fully share your opinion that it is impossible to know if a client is lost or not.
Even the client himself does not know whether he will continue working with the company next year or not.
- The method that I want to give as an example requires that company managers
are not afraid to admit the fact that the company loses customers regularly.
- This method is suitable for companies that operate in the transactional b2b market,
without obligatory contracts with clients.
- To get a more accurate result, you need to have information on orders for a period of two years or more.
Step 1: Define clearly Who the company's Active (Operating) customer is.
For example, we recognize a client as active (operating) if No More Than 6 Months
have passed from the date of his last transaction to the date of the report.
Step 2. Calculating number of active (operating) customers
- We calculate the number of active (operating) clients at the beginning of the reporting period (for instance, a quarter).
Let's call it "AC-Start".
- Similarly, count the number of active (operating) clients as of the last date of the quarter
and subtract the number of new clients acquired during that quarter.
Let's call this metric "AC-End-Net".
Step 3. Calculate the customer churn rate (CCR)
CCR = ( [AC-End-Net] - [AC-Start] ) / [AC-Start]
Note: For these numbers to make business sense, it is highly recommended to segment the CCR number.
For example, calculate the CCR for each region separately.
Or pre-segment the customer base by the average duration of the interval between purchases.
A question for seasoned Tableau practitioners, what are the tips you could give to those who are new to Tableau?
Also, what keeps you motivated or how do you keep motivated to learn more about tableau?
Simple, practice. That’s the only way to get better. Other than that, find people who’s work you like and follow them. Download workbooks you find interesting and see how they were created. Participate in community projects like Makeover Monday; this will give you practice every week.
Check out the Tableau Starter Kit too. It has a really useful guide.
@@vizwiz You’re simply amazing! Thank you so much for the advice!
@@JP_-oq3xn my pleasure!
Amazing 💯
Thanks for watching
Is there dynamic date parameter churn rate with score card
I don't understand.
Also I once I put a fillter for country when I choose only one country its fillter the presentage lower then 100% . only when I choose all countries it show 100%. How should I approach this?
Try making the country filter a context filter.
Hi..
I have a question.
In filters (wildcard search) , we have to give certain constraints that the user needs to select a minimum of 3 to 4 chars/nums in the wildcard search. then only the related info needs to display in the dashboard.
Issue:- if the user enters 1 char also then the data is displaying and there will be large info.... starts with and ends with will not work out here because there will be multiple id's which starts with different numbers.
I believe this has been answered on the forums. Have a look there.
@@vizwiz Thanks for reply Andy..
I checked the forums also but did not get the right answer.
Please help me out on this. My work has been stuck.
Cool
I’m trying to understand the L3M and L6M calculations… it seems like if you have the same 100 customers over the entire history the calcs would give you 300 and 600 respectively. This would give you a 50% churn rate, which isn’t correct. You would want to know how many distinct customers exist over the 3 and 6 month periods (100 and 100 in my example), not the sum of the count in each month, right?
He is using a distinct count (COUNTD) of customer names.
The COUNTD will only count each customer once.
@@vizwiz but the COUNTD will only count it per month. The WIND_SUM will then only count the COUNTD per month. What if a customer bought two times? Then he will be counted twice?
@@vizwiz I'm finding the same as @JensGrafe if you have a customer with multiple purchases it's summing the count by month and over stating the number of customers.
@@JensGrafe-rc4fw I found the same issue, were you able to find a way to only count one customer per month?
No wonders powerbi is far , far , far in front of tableau , what takes 20 secondes on powerbi takes 10 minutes on tableau !
For some things yes, for others no. I can think of tons of things that are painstakingly difficult in PBI. No tool is perfect. They each have benefits, which is great for all of us.