Excel 2007 : Customer retention models

**gibmini** · 11-11-2008, 04:52 AM

Hi all,

First time poster, so appologies if you have already covere this one - although I was unable to find it.

I'm using Excel 2007 and have a very large, but simple table with sequential months along the top of the first row. Then in each column I have a list of data (customers). I'm trying to find out how long each customer stays.

I can use an ISNA(match(... to find out the retention of the first month's customers in each of the subsequent months by just counting up how many of the 1st month's customers are still there in the following months. The problem I have is how to handle the following months new customers as the ISNA(match(... route will only work if all the customers in the month in question are new.

In month 2 there will be a combination of customers from month 1 and new ones in month 2. In month 3 there will be new customers, and customers from months 1 and 2...and so on...

Any ideas of how to acheive this? I've been told to use COUNTIF and complicated count tables, but this is beyond my skill levels.

Any tips would be most appreciated!

Thanks!

**JBeaucaire** · 11-11-2008, 05:14 AM

Can you add to that example of what you want the final output to look like? How do you want this tracking displayed? Is this on Sheet2?

Just do it manually / no formulas and we can replicate your output with formulas.

**gibmini** · 11-11-2008, 05:51 AM

I've attached the kind of output that would be great to get. If I can get that I can graph it, combine it to produce mean curves etc.

**JBeaucaire** · 11-11-2008, 06:24 PM

I couldn't find a way to make it all happen in one cell on your Output sheet, I had to add some helper cells under your raw data on the Working Sheet.

Take a look at the array formulas and you'll see how it all works. There is a one formula for identifying all the unique values in a month, then another formula used to compare subsequent month's list of unique values back to that first base set.

The formulas on the Output sheet leave incomplete months blank. They will appear as data appears.

Thanks to Domenic who helped me with a problem I adapted to your solution as well.

Does this work for you? If so, EDIT your original post and mark the thread SOLVED (the box to the left of the title).

**gibmini** · 11-12-2008, 01:02 PM

Wow.

thanks so much for your help! It appears to do exactly what I asked so many thanks! Sadly, I don't have much of an idea of how your formulas really work so I will have to spend a good while going through them.

Thanks again.

**ProjectWJ** · 03-28-2012, 06:57 PM

Hi everyone,

I came across this post and found a striking resemblance to the issue I am having. I am also a first time poster and would like to call on the amazing wealth of knowledge found in the members of this forum to provide me with some assistance.

I am using excel to find out how long each of my customers stay with my company. The model posted in this thread was very helpful but now I want to add to it and am lost.

I would like to have a column for 2009 clients, a column for the revenue they generated, then a column for 2010 clients and the revenue generated. Overall what I am trying to achieve is an output that shows the customer retention rate as well as the $$ value increase or decrease of revenue for those retained clients. I want to see on average if my retained clients are spending more or less on my service.

Any and all assistance will be appreciated.

Thank you