Thread: How to calculate the average number of months a customer is retained

I have calculated the averaged 1 month, 2 month, 3 month etc retention rate for customers based on their sign up month using data going back to 1/07. I now want to see how the average number of months retained differs by start date, but don't know how to calculate.

My data table looks like this:

Sign Up 1 2 3 4 5 6 7 8 9 10 11 12
Jan Av 68% 60% 49% 41% 33% 30% 25% 22% 19% 19% 17% 15%
Feb Av 69% 59% 47% 39% 33% 28% 23% 21% 18% 16% 15% 14%
March Av 62% 53% 43% 35% 27% 24% 20% 17% 15% 14% 12% 8%
April Av 73% 61% 50% 40% 35% 30% 26% 22% 20% 18% 16% 13%
May Av 70% 61% 50% 42% 36% 31% 27% 24% 21% 17% 15% 14%
June Av 74% 59% 49% 39% 32% 28% 24% 22% 17% 16% 14% 13%
July Av 69% 64% 51% 43% 35% 33% 30% 25% 21% 19% 18% 15%
Aug Av 73% 65% 54% 46% 41% 35% 29% 26% 23% 19% 18% 17%
Sep Av 64% 56% 44% 39% 34% 27% 24% 22% 19% 17% 15% 14%
Oct Av 63% 53% 48% 40% 31% 27% 24% 20% 18% 16% 15% 15%
Nov Av 60% 59% 50% 42% 34% 31% 25% 20% 17% 16% 14% 13%
Dec Av 72% 55% 41% 32% 28% 25% 20% 16% 14% 12% 10% 9%

Thank you for your help!

This is a little confusing, can you give some more detail/context?

Originally Posted by pickslides

This is a little confusing, can you give some more detail/context?

Maybe this is too much info, but...

Customers can sign up for this subscription in January, February, March etc. We track their retention rates by month so for instance, if a customer signs up in Jan 07, the % that are still on the program at the end of the February will appear under the first cell in the Jan '07 row under the column labeled "1". In the second cell in the Jan '07 row, the percent still with the program at the end of March will be in the cell labelled "2".

I was trying to see if there are seasonal differences in number of people retained and number of months retained. In other words do we have higher retention percentage rates for people who sign up in Jan versus August? Also, do they stay with the program for more months?

To come up with the average percentages for 1st, 2nd, 3rd etc periods of retention by sign-up month, I averaged the historical data which goes back to Jan '07. So the data in the column labeled "5" for the Row labelled March is the average of 5 periods of retention for people who signed up in Mar 07, Mar 08, Mar 09, Mar 10.

Now I want to figure out how many months on average they stay with the program. I've figured out I can't do it from the averages in the table in the original post. Instead I need to go back to the original data. Here's what I did - is it right? For Jan '07, for instance - took retention percent x number of months retained for each cell and summed. Figured out what percent that result represents of 100% retention in every period. Multiplied that result by 12. This answer is number of months retained, I hope. Then I did this for Jan '08, Jan '09, Jan '10 Sign ups and averaged the result to come up with the average number of months a customer is retained if they sign up in January. Repeated for rest of months in the year.

Am I close?