topic Re: Customer Retention in QlikView

Customer Retention

Mon, 04 May 2015 17:57:59 GMT

I would like to find the following

Set A - customers who purchased products same period last year

Set B - customers who purchased products same period this year

(Set A) * (Set B) ==> Intersection/common i.e. retained customers

A snapshot/report date is common between the two sets along with other dimensions.

I can have cases, where the customer is Lost in one region but gained in another Region but globally a retained customer for the company as they never left the company. But on a deeper dive, I can see that distinction.

My expression is a simple

COUNT(DISTINCT {<CustomerID = P({<BoughtLastPeriod = {1}>} CustomerID ) * P({<BoughtThisPeriod = {1}>} CustomerID) >} CustomerID)

the problem I am having is when I have a dimension other than time (for eg. Region) the numbers are not correct when there are no filters applied in the Region field. When there are filters like Europe, the number gets reduced for the same dimension value. The filtered value is the right number.

The BoughtLastPeriod and BoughtThisPeriod never change but the Retained Customer could changes for the filters.

Any way to understand what is missing in my expression?

Thanks,

Re: Customer Retention

Oleg_Troyansky — Mon, 04 May 2015 18:52:57 GMT

Keep in mind that your Set Analysis condition cannot be sensitive to the dimension values (e.g. Regions) because Set Analysis is only calculated once per chart, not once per line.

So, your Set Analysis will always select all Customers that satisfy the condition, disregarding of the Regions. Then, in a chart by Region, Customers that are associated with the Regions, get aggregated and counted.

If you need your condition to be sensitive to your dimension values, then Set Analysis may not be your solution.

cheers,

Oleg Troyansky

Come and learn Set Analysis and AGGR with me at the Masters Summit for QlikView.

Re: Customer Retention

Mon, 04 May 2015 19:17:25 GMT

So are you saying that this is a problem that needs to be resolved in the load script whereby I compute JOINs for all possible dimension combinations and then test for

BoughtLastPeriod = 1 and BoughtThisPeriod = 1 AS RetainedCustomer (i.e. [Set A] * [Set B])

and then using a master variable that selects the right RetainedCustomer column based on fields selected?

This sounds very inefficient and goes into saying that such a metric cannot be solved as a Generic expression

Re: Customer Retention

ramoncova06 — Mon, 04 May 2015 20:02:54 GMT

Hey Amir,

Can you share your data model ?

they are several way to get the intersection to work, though each one of them could work better depending of the data model

COUNT(DISTINCT {<CurrentPeriod = {1},PreviousPeriod = {1}>} User)

COUNT(DISTINCT {<User = P({<CurrentPeriod = {1}>} User ) >*< User = P({<PreviousPeriod = {1}>} User) > } User)

Re: Customer Retention

Mon, 04 May 2015 20:14:26 GMT

The sample data and sample chart are attached in excel.

I am looking to do 1&1 = 1 type output and then capture the Customers with 1 and count them as my retained customers.

Similarly, I can obtain, New Customers, Lost Customers and so on.

Re: Customer Retention

Oleg_Troyansky — Mon, 04 May 2015 20:59:34 GMT

Amir,

no, this is not at all what I was trying to say... In your original question, you mentioned that the expression returns "wrong" results in a chart by Region, unless a single Region is selected. This tells me that your expression needs to be "sensitive" to the dimension value (Region in this case). Set Analysis cannot be sensitive to the dimension values, and therefore it can't be used for such calculation.

As much as I dislike using IF() functions, it may be necessary in your particular case.

Looking at your data example, I'd recommend creating a combo key that combines Customer and Region and using that field in your Set Analysis condition, instead of the Customer. That should work much better for your needs.

cheers,

Oleg Troyansky

Re: Customer Retention

Mon, 04 May 2015 21:06:11 GMT

I now understand. Yes, that is the conclusion I am arriving at too.

I am surprised that this problem couldn't be resolved easily but has to change dynamically based on each scenario because in my case, same customer can be in

1. multiple countries

2. multiple sub-regions

3. multiple products

4. multiple reps

So it adds to the complexity.

COUNT(DISTINCT AGGR(IF( NUM#((Current) & (Base)) = 11, ([CustomerID])), [CustomerID], [Sub-Region]))

similarly, one for Region, Product etc. and depending on the X-axis, use the appropriate expression. That is extremely slow but I guess it is what it is. QlikView has met its match.

Re: Customer Retention

Oleg_Troyansky — Mon, 04 May 2015 21:14:23 GMT

this formula may not work if you eventually have multiple values of Current and Base by Customer and Sub-Region. How about this:

sum(aggr(max(Current) * max(Base), [CustomerID], [Sub-Region]))

Re: Customer Retention

Tue, 05 May 2015 20:38:18 GMT

Have you tried simple expression like

=floor((sum(Base)+sum(Current))/2), It will show you only retained customer, but not

"APAC ex Japan" ... If you want to show the Result with Comments

Then you can do in this way

create a variable vConcat = ' if(isnull(Base),'-',Base)&if(isnull(Current),'-',Current) '

in Expression pick(Match($(vConcat),'11','10','01','-1','1-'),'R','N','L','NULL','NoBusin')