There are three tables in my database:
test_bills, test_sites & test_subscriptions
In test_subscriptions table, there is "type" column which contains below values:
Free (free)
Free plus (free)
Pro Comped (free)
Pro (paid = $15/mo)
Growth (paid = $29/mo)
Enterprise (paid = $99/mo)
Pro Managed (paid = $custom)
Above in increasing order of price of subscription starting from Pro.
Over time our users have paid the varying amount within these subscriptions type. There is a “schedule” field where we tell you if it is a monthly or yearly plan.
You will also need to note that Bills have a column called “status.” The different statuses that a bill can have are:
Pending(this amount is considered as zero)
Paid(+)
Voided(this amount is considered as zero and end of subscription)
Failed(this amount is considered as zero)
Refunded (negative value)
Charged back (negative value)
For the amount, you can refer column "amount" in bills table.
Required Calculation:
Reactivation MRR: MRR from a site that had previously been paying, stop paying and then started paying again. For example, if a user was on a paid Growth subscription ($29/mo) for 6 months, then stopped paying for 6 months and then started paying for a Growth Subscription again. So we need to consider only reactivation amount(started paying again).
Please find the .pbix file link:
https://www.dropbox.com/s/jqmbrgtksostq3d/Test25Sep.pbix?dl=0
Please Guide
Thanks in advance
