1 Reply Latest reply: Sep 14, 2018 9:13 AM by Felip Drechsler RSS

    Please help :( Identifying multiple subscriptions from customer events table

    Alex Tomlins

      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)

       

       

      CustomerIDEngagementTypeLoggedDateTimeUtc
      1Subscribe

      2018-08-01 11:46:08.210

      1Cancellation2018-08-05 11:46:08.210
      1Subscribe2018-08-10 11:46:08.210
      1Cancellation2018-08-15 11:46:08.210
      2Subscribe2018-09-01 11:46:08.210
      2Cancellation2018-09-10 11:46:08.210
      2Subscribe2018-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)

       

      SubscriptionIDCustomerIDSubscriptionNoStartDateTimeEndDateTime

      Active

      1112018-08-01 11:46:08.2102018-08-05 11:46:08.2100
      2122018-08-10 11:46:08.2102018-08-15 11:46:08.2100
      3212018-09-01 11:46:08.2102018-09-10 11:46:08.2100
      3222018-09-12 11:46:08.210getdate()1
        • Re: Please help :( Identifying multiple subscriptions from customer events table
          Felip Drechsler

          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
          [C:\Users\sbt43959\Desktop\data.xlsx]
          (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:

          sample.png