Customer retention models

gibmini · #1 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 · #2 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 · #3 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 · #4 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 · #5 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.

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)