6 Replies Latest reply on Sep 2, 2014 6:39 AM by bright_guy

    Calculating a Subscription's Expiration Date

    bright_guy

      This is a good one I think. Any help is much appreciated.

       

      Goal

      Report the status of a user's subscription.

       

      Background / Use Case

      A mobile file is free on FM Go. Paid subscriptions are offered on the hosted file. The hosted file allows the user to sync the mobile file and share data with other users. Three types of subscriptions are offered: one month, six months, or twelve months. Users can purchase any type of subscription at any time.

       

      Sample Data

      The hosted file includes a [User] table and a [UserLog] table. The latter records user "actions" and corresponding time stamp. For example:

       

      timestamp | action | monthsPurchased | c_expirationDate

      2012-08-28 17:33:46 | Paid one month. | 1 | 2012-09-28

      2012-08-28 17:53:22 | Paid six months. | 6 | 2013-02-28

      2013-08-28 23:31:36 | Paid six months. | 6 | 2014-02-28

      2013-08-28 23:34:14 | Paid twelve months. | 12 | 2014-08-28

       

      Commentary on Sample Data

      For the sake of defining some terms, and also for the sake of using plain language...

      Line 1 - the user "subscribes"

      Line 2 - the user "renews" subscription

      Line 3 - the user "resubscribes"

      Line 4 - the user "renews" subscription

       

      Problem

      Notice the calculated result in line 2? The expiration date is wrong because it fails to take into account the user's prior purchase. The correct result should be 2013-03-28.

       

      Line 3 is correct because the user's subscription lapsed before she re-subscribed.

       

      Line 4 is also wrong for the same reason as line 2. The correct result for the expiration date should be 2015-02-28

       

      The existing calculation incorrectly adds the [monthsPurchased] to the [timeStamp] without accounting for prior [monthsPurchased]. Renewals are consistently wrong.

       

      Discussion

      Am I overthinking this? I mean, it's not exactly a unique use case. Maybe I don't need to bother with expiration dates in the [UserLog]. Maybe the calculation should reside in the parent [User] table. I'll need one there anyway to report the user status: Are they paid up or not?

       

      I just think it's cleaner to see a -- correct -- expiration date in the log.

       

       

      Thanks in advance for your help,

      =L=

        • 1. Re: Calculating a Subscription's Expiration Date
          Mike_Mitchell

          Leo -

           

          I don't know what you're using to calculate the expiration date, but you should be able to do something like this:

           

          Let ( [

          lastEntry = GetValue ( log ; ValueCount ( Log )) ;

          lastDate = GetValue ( Substitute ( lastEntry ; "|" ; "<cr>" ) ; 4 ) ;           // where "<cr>" is the pilcrow (carriage return) character

          baseDate = Case ( type = "Renew" ; lastDate ; type = "New" ; Get ( CurrentDate )) ;

          term = {number of months} ;

          expireDate = Date ( Month ( baseDate ) + term ; Day ( baseDate ) ; Year ( baseDate ))

          ] ;

           

          Get ( CurrentTimestamp ) & "|" & {term as text} & "|" & {term as number} & "|" & expireDate

           

          )

           

          You'll need to do some error trapping in case the log is empty, and you might want to check to see if the last entry has expired, but that should get you started.

           

          HTH

           

          Mike

          • 2. Re: Calculating a Subscription's Expiration Date
            erolst

            The crucial question is whether the new subscription date takes place before the most recent one expires.

             

            If you have the fields dateStart, runtime and dateExpiration, then to calculate the expiration date for a new entry, you could use this calculation as auto-enter calc:

             

            Let ( [

              newStart = dateStart ; // from current record

              oldExpiration = SelfJoin::Max ( dateExpiration ) // a self join where CustomerID = CustomerID AND primaryID ≠ primaryID; or use ExecuteSQL()

              pDate = Max ( newStart ; oldExpiration ) // whatever is the later date; if it's newStart, you have a “resubscription”, else it's a “renewal” (“extension” might be a better term …)

              ] ;

              Date ( Month ( pDate ) + newRuntime ; Day ( pDate ) ; Year ( pDate ) )

            )

            • 3. Re: Calculating a Subscription's Expiration Date
              bright_guy

              Hi Mike,

               

              Thank you for your suggestion. Your calculation includes "type", which is not available. I think calculting whether a subscription is new or renewed, is the trickiest part.

               

              Thanks again,

              =L=

              • 4. Re: Calculating a Subscription's Expiration Date
                bright_guy

                Hi erolst,

                 

                Thank you for your suggestion. I prototyped the calcultion and it works. I had to modify it a bit because the dateExpiration auto-enter calculation in the UserLog table behaves differently depending on whether a record exists for a particular user.

                 

                The calculation works fine for the first record, but returned "?" thereafter. So I added a custom function to handle the SQL date format. Then I had to add a case statement because the custom function doesn't work for zero records.

                 

                I posted details and a link to the prototype file on my blog.

                 

                Thanks again,

                =L=

                • 5. Re: Calculating a Subscription's Expiration Date
                  erolst

                  Leo Di Croce wrote:


                  I prototyped the calcultion and it works. I had to modify it a bit because the dateExpiration auto-enter calculation in the UserLog table behaves differently depending on whether a record exists for a particular user.

                   

                  Hi Leo,

                   

                  see attached file with a number of modifications to your prototype.

                  • 6. Re: Calculating a Subscription's Expiration Date
                    bright_guy

                    Hi erolst,

                     

                    Thank you for modifying the file. It's much better now.

                     

                    I like how you consolidated the Payment script, how you added a "simulated now-date", and how you used it to calculate $thisExpirationDate. I also like that it's all scritped, as opposed to the auto-enter calcualtion I had. My favorite is the nasty bug you found in the Clear Log script.

                     

                    Thanks again,

                    =L=