3 Replies Latest reply on Jul 13, 2018 4:29 PM by Shinichiro Murakami

    Calculate Utilization Rate - For given period

    Francisco del Valle

      Hi All:

       

       

      I'm trying to calculate the utilization rate (UR) of customers.

      To give you a little background, customers can purchase subscriptions by the day, week, month, quarter, semester and year.

      We've defined the utilization rate as "active days" within a period.

      The DB has the following fields:

      Value = Days purchased (in the attached sample you'll see 1, 7 and 30 (so single days, weeks and months)

      Date= Date of the purchase

      ID=Customer ID

      System ID= The system ID which receives the purchase (related to the customer id but not the same)

       

      The other variables are calculated fields I created to try to determine the UR for the customer.

      The challenge lies in this particular dynamic:

       

      A customer might purchase several days in a given day. So his system would be active for the X days he purchased. After these days expire the following purchase should be loaded.

      So as an example a customer might be active 30 days in a month by purchasing 30 individual days, but not necessarily in subsequent days but could be purchased all in 5 different days.

      I think I got the "expiration calculation" and UR right. However since I used table calculations I cannot use the created variables without the other variables present in the viz, which sort of limit my options to get cool visualizations.

       

      Any ideas on how to work around this?

       

      Attached the sample I'm wotking with.

        • 1. Re: Calculate Utilization Rate - For given period
          Shinichiro Murakami

          HI Francisco,

           

          From givne info, it's not clear enough how you want to cal the ratio against, (ID, name???) anyways.

          I count IDs based on the contract date.

           

           

           

          Thanks,

          Shin

          • 2. Re: Calculate Utilization Rate - For given period
            Francisco del Valle

            Thank you Shin!

            It should be at the ID level to be able to tell at a customer level what his usage is, and you would calculate in 30 days periods.

            So lets say a user is active 20 days in a 30 days period, his UR is 67%.

            The issue with your calculation is that you're not considering the true expiration date of the subscription. So if a customer buys 3 days in a particular day, he would have his subscription expire 3 days after said purchase. In your calculation you assume the expiration date is the date purchased plus the value, which in an environment in which customers only buy one subscription per date, it would work, but the truth is customers purchase with so many different scenarios. For instance there's customers who purchase a week, and purchase another week before the previous week has expired, so their true expiration date is 14 days after their first purchase, not seven days after their last.

             

            This is an example of what happens with the model. Lets suppose you have an active customer who subscribed for a month on June 1st, and we're trying to find out the UR between 6/10/18-7/10/18.

            The customer's original subscription expires after 7/1/18. He buys a weekly subscription on June 30th.

             

            Original monthly subscription from 6/10/18 to 7/1/18 = 21 active days

            Weekly subcription from 7/2/18 to 7/9/18 = 7 days

            Total active days= 28 --> UR 28/30= 93%

             

            The challenge is to consider the previous subscription (s) purchased to see if its (they're) still active for a period within the period you're analyzing; and also to exclude the days of the last subscription of the period beyond the period you're analyzing.

             

            Thank you, hope this last bit sheds some more light on the issue.

            • 3. Re: Calculate Utilization Rate - For given period
              Shinichiro Murakami

              HI Francisco,

               

              If that's the case, you need to prepare another date table to judge day by day.

              I don't have time right now and will try at next convenient, but no guarantee as forum's feature.

              You can ping me if long time no reply.

               

              Thanks,

              Shin