My request here is around issues I am having in respect of churn analysis.
If the solution is simple, then great, if not and it requires some paid support I am happy to engage with anyone competent to help. I can be contacted by email on mikebergin27@gmail.com
Have connected PowerBi to a bespoke sales system on a SQL server.
Have used the main churning set of formulae from the Enterprise DNA examples on churning.
Have modified the definition of churn to be “date customer marked as removed”, which is not ideal as it is not an independent churn measure. (There is a field in the database which can be edited to mark a customer as “removed”)
So would prefer to adapt this to what is generally accepted as churn being no sale in a defined period following original sale. Could do with some help on that, (also to ensure I understand what that means, ie if the customer returns outside of the initial pre-determined period re churn?)
Secondly, would like to split dynamically my customer base to “probationary – being customers on boarded in last 90 days” and “established – Customers on boarded greater than 90 days” and then be able to slice and dice churn and potentially other data by this grouping. Tried a couple of different approaches here, using a disconnected table and a calculated table but to no avail.
Note when anonymising the PBIX file, somehow a connection is breaking, or it could be that I created the cohort/churn through measures and in the process of copying data table for anonymity purposes I am bringing in a column instead! Hence the pdf which shows the visuals pre breaking the source.
Churn definition depends on many things - but some similarity
EOP Users = BOP Users + Net Add = BOP + New* (divided New and Returnee) -Churn
You can also have Active users / Inactive (Probations) - so Total number of users can be different from Active ones - dividing it you can get better insights
Voluntary/Involuntary churn – if it is applicable to you
more context will provide better answer – some questions that maybe help you:
How do you calculate number of customers (maybe in some other department that can help) ?
When you start counting – some kind of first onboarding date ? Is it Join date ? initial date? Some other date ?
Do you have that date in your Customer table already or you need bring in ?
If you have it in Customer table – you can use it to calculate your Customer Probation Period
Also LastPurchaseDate or something like that can also be helpful – but it depends of the sell cycles and business process
Do you have some cycles that you need involved? Maybe it can help you to define that time window
Seasonality that you also take into count?
When you stop counting - I suppose you have additional information besides “date customer marked as removed”
Could you find out what is the rule for “date customer marked as removed” – in some industries you need to keep information 5 years minimum (so in that case it is obvious that you need to use something else)
Maybe you can calculate some average window from historical data and see how that fit to your business case
Additional questions
Is it one-shot sales or there is some subscription (monthly/yearly) that you need also include in your analysis?
Do you have also Involuntary churn (that you need also include) – when you for some reasons need to cancel your service
Maybe you have late payment and you want classify also that kind of users as well