topic Customer Retention in QlikView

Customer Retention

tmumaw — Thu, 28 Oct 2010 13:45:02 GMT

My company has asked me to put together a model which shows customer retention. They want to know how many customers they have retained by type of service over a 5 year period. Here is an example:

2007

Service Customers Retained from 2006 Customers Lost from 2006 Customers New from 2006

123

They want the same information for 2007, 2008, 2009 and 2010. Sort of stumped on this one.

Thanks

Thom

Customer Retention

johnan — Thu, 28 Oct 2010 15:10:22 GMT

Hi, use Set Analysis to view this.

Br John

Customer Retention

johnw — Thu, 28 Oct 2010 20:24:03 GMT

Attached is one approach. The main data contains a Year, ServiceType and Customer. I create an AsOf table to connect an AsOfYear to the Year field by a YearType of 'Current' or 'Previous'. I create a chart with ServiceType and AsOfYear as dimensions. Then:

New = -sum(aggr(only(YearType)='Current' ,AsOfYear,ServiceType,Customer))
Lost = -sum(aggr(only(YearType)='Previous',AsOfYear,ServiceType,Customer))
Retained = -sum(aggr(count(distinct Year)=2 ,AsOfYear,ServiceType,Customer))

Customer Retention

tmumaw — Fri, 29 Oct 2010 12:46:38 GMT

Thanks John, I will give it a try and let you know if it works.

Thom