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.
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.