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

[Anonymous]

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;

[Anonymous]

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

[Anonymous]

Hi Boris,

Yes just added a zip file.

Thanks regards

Birkir

[Anonymous]

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?

[Anonymous]

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

[Anonymous]

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.

[Anonymous]

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

• 

[Anonymous]

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

[Anonymous]

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

[Anonymous]

your QVD has only two fields