topic Re: Find new subscripers or subscripers lost, previous(), Churn in QlikView

Find new subscripers or subscripers lost, previous(), Churn

Anonymous — Sun, 15 Mar 2015 15:16:58 GMT

Hi,

I am trying to find new subscribers per day and subscribers lost per day. I have been using previous() do it. I think I have found a way to find new customers but I cannot crack the issue with lost subscribers. The data is basically as

DagsStads, KennitalaRetthafa

2015-02-01, 1 Start of subscription

2015-02-02, 1

2015-02-28, 1 Still a subscriber.

2015-02-01, 2 Start of subscription

2015-02-01, 2

2015-02-10, 2 - Lost customer

So the min(date) is the start date of the subscription , so I would like to mark him as new, the last max(date) is the day he stops, but if the max(date) is last day of month he is still a customer.

Here is my code as is, any hints would be greatly appreciated. On top of this I was thinking of calculating subscriper churn rate.

MaxDags:
LOAD maxString(DagsStads) as MaxDags,
MinString(DagsStads) as MinDags

FROM
(qvd);

LET vMaxDags = PEEK('MaxDags');
LET vMinDags = Peek('MinDags');
DROP TABLE MaxDags;

Regards Birkir

[SubsData]:
LOAD DagsStads,
      KennitalaRetthafa
FROM
(qvd);

[NewSubs]:
LOAD DagsStads,
      KennitalaRetthafa,
      if(Previous(KennitalaRetthafa) = KennitalaRetthafa and Previous(DagsStads) <> $(vMaxDags), 'Áskrift','Nýr') as Previ
     Resident SubsData
Order by KennitalaRetthafa, DagsStads;

DROP Table SubsData;

Re: Find new subscripers or subscripers lost, previous(), Churn

Anonymous — Sun, 15 Mar 2015 15:28:19 GMT

any way you can make a test QVW with test data?

Re: Find new subscripers or subscripers lost, previous(), Churn

Anonymous — Sun, 15 Mar 2015 15:45:19 GMT

Hi Boris,

Yes just added a zip file.

Thanks regards

Birkir

Re: Find new subscripers or subscripers lost, previous(), Churn

Anonymous — Mon, 16 Mar 2015 13:28:26 GMT

oh wow Icelandic:)

I think you are on the right track with your code. I remember I had issues before with ORDER BY and previous peek functions in the same load statement. Try to do ORDER BY first and then resident load ordered table and use previous function at that point - so you would do ORDER BY on SubsData table not for NewSubs.

Also why your max/min dates are global and not subscriber related?

Re: Find new subscripers or subscripers lost, previous(), Churn

Anonymous — Mon, 16 Mar 2015 13:57:51 GMT

Hi Boris,

Yes sorry for the Icelandic, I was getting a bad from my wife when I was replying

I will try the Order By trick you mentioned.

Yes regarding the max/min dates, I thought about having it on a per subscriber base, how would you do that?

Regards

Birkir

Re: Find new subscripers or subscripers lost, previous(), Churn

Anonymous — Tue, 17 Mar 2015 01:53:16 GMT

there are many ways...you could use RANK function and GROUP BY for example to rank group of rows that would relate to one SubscriberID.

OR you can just get away with GROUP BY on a resident table and storing min/max date in the third table by subscriberID and join it back to your main table.

Even with your test QVD (thanks for uploading it), it is a bit tough to understand your data and what you are trying to do.

Re: Find new subscripers or subscripers lost, previous(), Churn

Anonymous — Tue, 17 Mar 2015 22:42:04 GMT

Hi,

I created a new file, this time not Icelandic added a qvw file. So maybe this will help to explain what I am trying to achieve.

So this is more or less the result I am after, but I am kind of lost in how to get there. The is to look at the first date in a snapshot table and mark a customer as new, if found in previous which is not new then it is an established subscription. If previous subsId changes the previous subscriber quit that day.

Again all hints are appreciated.

Boris thanks again for all your help.

Regards

Birkir

Re: Find new subscripers or subscripers lost, previous(), Churn

Anonymous — Wed, 18 Mar 2015 14:54:13 GMT

you forgot to attach your QVD. also in your sample data, only New subscribers are present so there is no Quit or Subscription.

Re: Find new subscripers or subscripers lost, previous(), Churn

Anonymous — Wed, 18 Mar 2015 15:19:01 GMT

Hi Boris,

Sorry for this my mistake, I added the QVD file and a bit more data, this customer 2212862789 stops his subscription on the 2014-03-11 and starts again on the 2015-01-26.

I'm still a puzzled with this,

Regards

Birkir

Re: Find new subscripers or subscripers lost, previous(), Churn

Anonymous — Wed, 18 Mar 2015 18:23:11 GMT

your QVD has only two fields

Re: Find new subscripers or subscripers lost, previous(), Churn

Anonymous — Wed, 18 Mar 2015 21:19:29 GMT

Hi All,

I think I have found the solution. This seems to do the trick.

Special thanks to Boris to pitch in.

MaxSubscriptionDate:
LOAD maxString(SubscriptionDate) as MaxDags,
MinString(SubscriptionDate) as MinDags

FROM
(qvd);

LET vMaxDags = PEEK('MaxDags');
LET vMinDags = Peek('MinDags');
DROP TABLE MaxSubscriptionDate;

[SubsData]:
LOAD SubscriptionDate,
      SubsId,
      Subscription
FROM
(qvd);


[NewSubs]:
LOAD SubscriptionDate,
      SubsId,
      Subscription,
      if(if(Previous(SubsId) = SubsId and previous(SubscriptionDate) <= SubscriptionDate-30, 'Quit','New')='New' and Previous(SubsId) = SubsId and Previous(Subscription) = Subscription and previous(SubscriptionDate) <= SubscriptionDate-1,'Áskrift','Nýr') as SubscriptionStatus
      Resident SubsData
Order by SubsId, Subscription, SubscriptionDate;
//
//
[Churn]:
LOAD SubscriptionDate,
      SubsId,
      SubscriptionStatus,
      Subscription,
if(Peek('SubscriptionStatus',RowNo(),'NewSubs')='Nýr' and Peek('SubsId',RowNo(),'NewSubs')=SubsId,'Hættur','Áskrift') as sbla,
      if(SubscriptionStatus='Nýr',SubscriptionStatus,if(Peek('SubscriptionStatus',RowNo(),'NewSubs')='Nýr' and Peek('SubsId',RowNo(),'NewSubs')=SubsId,'Hættur','Áskrift')) as bahaha
      Resident NewSubs
Order by SubsId, Subscription, SubscriptionDate;

MaxSubDags:
Concatenate (NewSubs)
LOAD maxString(SubscriptionDate) as SubMaxDags,
MinString(SubscriptionDate) as SubMinDags,
SubsId,
Subscription
Resident SubsData
Group by SubsId,Subscription;

DROP Tables SubsData;

Re: Find new subscripers or subscripers lost, previous(), Churn

Anonymous — Wed, 18 Mar 2015 21:20:21 GMT

Hi Boris,

Yes that was all I needed, I managed to find a solution. Check it out and see if it makes any sense.

And thanks for all your help Boris.