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]

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;




 

[Anonymous]

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.