Charity membership - How to discover a monthly percentage chance?

[leonidas]

Firstly I'd like to say 'hi' ? as I'm new to the forum. I will no doubt be visiting often because my maths is over twenty years rusty, and I'm starting a new business in the UK.

Down to the maths problem.

One of the major sources of income for charities is signing-up new members to make a monthly donation; typically between £5 to £10 per month. One of the biggest problems for charities is people who sign-up and then cancel their membership prematurely; sometimes in a matter of days. Charities refer to the rate of cancellation as attrition.


Here is the maths problem:

I would like to discover a formula which tells me the monthly percentage chance of an average customer cancelling their charity donation. I will know how many months the average donation is active for. I therefore need to discover the percentage chance of a customer membership cancelling in any given month. By the way, this works upon the assumption that a customer's chance of cancelling their membership is the same each month.

Here is an example: The average amount of time that a customer donates to a charity is 20 months. What is the percentage chance of an average customer cancelling their membership each month?

Ultimately, I need to discover a formula (which can be implemented in a spreadsheet) which converts a given number of months (average length of donation activity) into a percentage (the chance of cancellation, per month).

Here's another example:

• Customer-A cancels their charity membership after donating for 10 months
• Customer-B cancels their charity membership after donating for 20 months
• Customer-C cancels their charity membership after donating for 30 months

(10+20+30)/3=20
Therefore, the average charity membership is cancelled after donating for 20 months.
... But what is the percentage chance that any given customer will cancel their membership in any given month? And how to we reach this conclusion?


Please excuse the layman's terminology! I hope this was explained clearly enough. And thanks for any help!

 

[tkhunny]

There is nowhere near enough information given. What is the DISTRIBUTION of the attrition? Is it mostly up front and then dragging out for 80 more months? Having ONLY the average is quite inadequate. Is it the same amount of cancellations each month? This is what your example assumes, but with only three customers, it's not very convincing. (1 + 1 + 58)/3 = 20 -- VERY different distributions can produce the same average.

Why does this problem seem so familiar? Seems like there was a very similar golf club membership problem not long ago.

 

[leonidas]

Hi tkhunny and thank you for the reply

In answer to your question, the chance of cancellation is the same each month (in reality the chance of cancellation is greater in the first 12 months. But as I do not have access to the data - yet - I am constructing a simple model for the time being).

• The chance of cancellation is the same for each month
• Each customer has an equal chance of cancellation each month

In reality, anywhere between 50-1000 new members will be recruited each month (but the number of new memberships doesn't have any bearing on this maths problem). Three members were only referenced earlier for the purposes of providing a simple example.

Incidentally, each customer will have a monthly chance of cancellation of anywhere between 1% - 5%.

I simply need a mathematical rule which allows me to convert 'average number of months customer is active' to 'average monthly percentage chance of cancellation'.

 

[tkhunny]

Hi tkhunny and thank you for the reply

In answer to your question, the chance of cancellation is the same each month (in reality the chance of cancellation is greater in the first 12 months. But as I do not have access to the data - yet - I am constructing a simple model for the time being).

• The chance of cancellation is the same for each month
• Each customer has an equal chance of cancellation each month

In reality, anywhere between 50-1000 new members will be recruited each month (but the number of new memberships doesn't have any bearing on this maths problem). Three members were only referenced earlier for the purposes of providing a simple example.

Incidentally, each customer will have a monthly chance of cancellation of anywhere between 1% - 5%.

I simply need a mathematical rule which allows me to convert 'average number of months customer is active' to 'average monthly percentage chance of cancellation'.

Well, we have a little problem, here. You state clearly that you are not up to the mathematical task and then you follow up by insisting that you have a full understanding of what is needed to solve your problem. You must see that this is contradictory.

The ONLY way to get an average of 20 under your present definition, for everyone, is to DEFINE that the maximum is 40 - no one EVER goes over 40 months. Are you willing to make that definition? If not, you must rethink your problem's parameters.

 

[leonidas]

Well, we have a little problem, here. You state clearly that you are not up to the mathematical task and then you follow up by insisting that you have a full understanding of what is needed to solve your problem. You must see that this is contradictory.

To be clear, I had some very good maths tuition during college years - and still retain some of the learning - but have forgotten much of it. I'm not sure where that places my ability relative to an average person. This forum is a great place to start that journey again.

The ONLY way to get an average of 20 under your present definition, for everyone, is to DEFINE that the maximum is 40 - no one EVER goes over 40 months. Are you willing to make that definition? If not, you must rethink your problem's parameters.

The amount of time that our organisation will be assisting a charity will be finite; say, between 5 to 20 years. During that period of time it is likely that small percentage of customers will not cancel their donations at all. Having worked in the charity sector for a long time, I am aware of people who have been making a continuous donation for well over 3 decades and have not yet cancelled. Although these people are rare, they do exist. Therefore I am unable to define a maximum.

 

[tkhunny]

To be clear, I had some very good maths tuition during college years - and still retain some of the learning - but have forgotten much of it. I'm not sure where that places my ability relative to an average person. This forum is a great place to start that journey again.


The amount of time that our organisation will be assisting a charity will be finite; say, between 5 to 20 years. During that period of time it is likely that small percentage of customers will not cancel their donations at all. Having worked in the charity sector for a long time, I am aware of people who have been making a continuous donation for well over 3 decades and have not yet cancelled. Although these people are rare, they do exist. Therefore I am unable to define a maximum.

Clear enough. Keep working at it.

Actually, you DID define a maximum by your assumptions. Perhaps it is sufficient for you to define 1/40 of the original population as the monthly amount of decline. This will create a straight line from 0 to 40, but perhaps it will be a useful model. You may wish to define (1 - 1/40) as the monthly rate of survival. This will create a different pattern that will account for a longer tail and may also represent what you mean by "same chance every month".

Start with 120

1/40 * 120 = 3 <== Monthly Decline in Count

0-120
1-117 = 120 - 3
2-114 = 117 - 3
3-111
4-108
5-105
6-102
7-99
8-96
...
20-60
...
30-30
...
40-0
The average of this is 20.5, a little more than 20, but with simpler numbers than if I had made it exactly 20. There is no allowance for long-time donors.

1 - 1/20 = 0.95 <== Monthly Rate of Survival

0-120.0
1-114.0 = 120.0 * 0.95
2-108.3 = 120.0 * 0.95 * 0.95
3-102.9 = 120.0 * 0.95 * 0.95 * 0.95 = 120 * 0.95^3
4-97.7
5-92.9
6-88.2
7-83.8
8-79.6
...
20-43.0
...
30-25.8
...
40-15.4 <== You still have some people left after 40 months.
...
50-9.2 <== You still have some people left after 50 months.
etc...
The average of this is 20.0 and it allows for the long-time donors. However, it may allow for too many long-time donors.

Try to remember that it's just a model, not reality. The real choice is in the consideration of what you do or don't find useful. Good luck!

 

[leonidas]

Thank you!

First off, this works perfectly in an excel spreadsheet: 0.95^3 And has made life a lot easier! I'm referring specifically to the ^ symbol. Great.

Secondly, of the two models you propose above, the one which is the most useful is the latter model. This is because it creates a percentage decline rather than a straight line. I work on the assumption that each month a certain percentage of the entire membership of donors cancels the donation. One question I have for you is this: What is the best way to obtain the average number of months that a donation is active? I need a simple mathematical rule which can be programmed into excel.

Thanks again!