Please help :( Identifying multiple subscriptions from customer events table

[alextomlins]

Hi all,

I've posted (poorly) about this question already and so i've decided the best way to go about it is to show you what table i have and what i want to transform it to.

The table I have is an events table

LicenseHistoryTable:

CustomerID

//(e.g 1, unique to every customer but NOT subscription)

,EngagementType

//(e.g. 'Subscribed' or 'Cancelled', Customer can have multiple subscriptions and have multiple subscribes and Cancellations in tat //Customer ID)

,LoggedDateTimeUtc

//(Date Time format, give you date and time of event)

CustomerID	EngagementType	LoggedDateTimeUtc
1	Subscribe	2018-08-01 11:46:08.210
1	Cancellation	2018-08-05 11:46:08.210
1	Subscribe	2018-08-10 11:46:08.210
1	Cancellation	2018-08-15 11:46:08.210
2	Subscribe	2018-09-01 11:46:08.210
2	Cancellation	2018-09-10 11:46:08.210
2	Subscribe	2018-09-12 11:46:08.210

So the reason i can't just do min(susbcribe) and max(cancellation) in EngagementType field is because if a user has 'subscribe - cancel - subscribe - cancel' scenario we would count that as one subscription but we want to count that as two subscriptions. The table i would like to get to is as follows;

Subscriptions Table:

SubscriptionID

//(INT - Detailing a unique key for every 'subscribe - cancel' pairing or just subscribe if already after a cancellation)

,CustomerID

//(INT - as in the tale previously linking to each customer)

,SubscriptionNo

//This will detail if this is the first subscription, second subscription and so per CustomerID)

StartDateTime:

//(DateTime - This will be the start Date Time for that SubscriptionNo for the relevant CustomerID)

,EndDateTime

//(DateTime- This will be either the current Date if the customer has not cancelled in this subscription or the Cancellation for that //subscription)

Active

//(boolean - this will detail whether that SubscriptionNo has a cancellation i.e Is that customer still live)

SubscriptionID	CustomerID	SubscriptionNo	StartDateTime	EndDateTime	Active
1	1	1	2018-08-01 11:46:08.210	2018-08-05 11:46:08.210	0
2	1	2	2018-08-10 11:46:08.210	2018-08-15 11:46:08.210	0
3	2	1	2018-09-01 11:46:08.210	2018-09-10 11:46:08.210	0
3	2	2	2018-09-12 11:46:08.210	getdate()	1

[felipedl · Accepted solution]

HI Alex,

Im pasting the code here and attaching the QVW i used to get what you need:

CustomerTable:
LOAD CustomerID, 
     EngagementType, 
     // Transforms the date you have into a numeric date
     Date#(subfield(LoggedDateTimeUtc,'.',1),'YYYY-MM-DD hh:mm:ss') as LoggedDateTimeUtc
FROM

(ooxml, embedded labels, table is Plan1);


NoConcatenate
Ordered:
Load 
*,
// id so i can create key for getting the end date to row directly above the current one
RowNo() as Id,
if (EngagementType='Subscribe',1,0) as [Flag Subscription]
Resident CustomerTable
order by
CustomerID,
LoggedDateTimeUtc;


drop table CustomerTable;

FinalData:
Load *,
// key to identify the current row
CustomerID & '_' & EngagementType & '_' & Id as #KeyJoin,
// key to identify the previous line on the current row
CustomerID & '_' & peek(EngagementType) & '_' & (Id-1) as #Key
Resident Ordered;


drop table Ordered;

// only gets the rows for subscribed values
NoConcatenate
Subscriptions:
Load
*
Resident FinalData
where EngagementType = 'Subscribe';

// joins with the cancelled values, by the #KeyJoin key
left join (Subscriptions)
Load
#Key as #KeyJoin,
LoggedDateTimeUtc as EndDateTime
Resident FinalData
where EngagementType = 'Cancellation';


drop table FinalData;

// creates all the fields you need.
FinalTable:
Load
if(not IsNull(LoggedDateTimeUtc) and not IsNull(EndDateTime),Alt(peek(SubscriptionId)+1,1),peek(SubscriptionId)) as SubscriptionId,
if(CustomerID=peek(CustomerID),peek(SubscriptionNo)+1,1) as SubscriptionNo,
CustomerID,
LoggedDateTimeUtc as StartDateTime,
EndDateTime,
if(isNull(EndDateTime),1,0) as Active
Resident Subscriptions
order by 
CustomerID,
LoggedDateTimeUtc;


drop table Subscriptions;

This will get me the following: