Happy Business Starts Here

Community Manager

How to pull a report of "Today's MRR" in Data Sources Export

 

"Today's MRR" is not included in our exports because the number can change every day, since "today" is a moving target.


Here are steps to pull a report of "Today's MRR" in Data Sources:

1. Data Source: Rate Plan Charge

2. Fields:

     a. Rate Plan Charge - MRR, Name
     b. Account - Account Number, CMRR, Name
     c. Rate Plan - Name
     d. Subscription - Name

3. Filters:

     a. Subscription.Status = 'Active'
     b. RatePlanCharge.EffectiveStartDate <= date today (or preferred date)
     c. RatePlanCharge.EffectiveEndDate >= date today (or preferred date)

On the generated csv file, you will need to sum up the MRR to compute the value of the account's "Today's MRR". (In Data Sources, the MRR or Monthly Recurring Revenue is also known as the Monthly Recurring Charges, or MRC as used on the subscription export tool)

1. To calculate the value of the "Today's MRR" for a specific billing account, select the applicable customer account by using the custom filters in column C, Account: Account Number.” Then sum up all MRR for the said account.

2. To calculate the total MRR for all customer accounts, leave column C, Account: Account Number: as-is (without filtering) and sum up all MRR.

You may also refer to the attached file for more info on how MRR is calculated in Z-Billing and other recommendations on how to pul "Today's MRR" on Data Sources.

 



Subscribe to Zuora System Updates at Zuora Trust
Follow Zuora Global Support on Twitter and LinkedIn

3 REPLIES 3
Community Manager

Re: How to pull a report of "Today's MRR" in Data Sources Export

There is some great additional material on this topic over on this community article. This includes a great object relationship diagram clearly mapping out how the multiple versions of the subscription object impact finding the appropriate rate plan charge objects so you can then get the MRR and TCV for each charge.



Subscribe to Zuora System Updates at Zuora Trust
Follow Zuora Global Support on Twitter and LinkedIn

Community Manager

Re: How to pull a report of "Today's MRR" in Data Sources Export

In trying to build an MRR report I came across a couple of additional complexities that might help others. These deal with evergreen subscriptions and the rate plan charge effective end date.

With an evergreen subscription there is no effective end date on the last rate plan charge segment! So instead of  the effective rate plan charge effective end date being greater or equal to a specific date you need to query for effective end 'is null'. And if you have a blend of termed and evergreen you need two reports if using the Zuora UI, as you've got two different filters for effective end date that you can't OR together in the UI. If you are using the API and can feed in the data source query through the API you can actually specify an OR clause! So your query might be:

 

select Account.AccountNumber, Account.Name, Subscription.Name as SubNumber, 
sum(RatePlanCharge.MRR) as SubMRR, sum(RatePlanCharge.TCV) as SubTCV, count(RatePlanCharge.Id) as SubCharges
from RatePlanCharge
where Subscription.Status = 'Active'
and RatePlanCharge.EffectiveStartDate &lt;= '2014-07-01'
and (RatePlanCharge.EffectiveEndDate &gt; '2014-07-01' or RatePlanCharge.EffectiveEndDate is null)
group by Account.AccountNumber, Account.Name, Subscription.Name
order by Account.Name, Subscription.Name

In Zuora if you start a 12 month initial term subscription on Jan 1, that subscription has a subscription and term end date of Jan 1 the following year and NOT Dec 31st. An invoice item service period for an annual charge starting on Jan 1 will end Dec 31st, but the subscription object end date fields are tagged Jan 1. This is perhaps not the most intuitive approach Zuora could have taken here. But this has a big impact on query outlined above, the original post asked to use a filter of effective end date >= the specified date, but I believe this should be effective end date > the specified date and so that is what I used in the above query (in the first part of the OR clause).

By the way the Subscription Status field doesn’t mean what you probably think it means, ‘Expired’ does NOT mean the subscription ended and wasn’t renewed, if it ends and isn’t renewed it’ll still have a status of ‘Active’! Expired means that this particular subscription object is no longer the most up to date subscription definition (someone has filed an amendment and there’s a more recent definition of the subscription). ‘Active’ actually means that this is the most recent (highest) version of the subscription.



Subscribe to Zuora System Updates at Zuora Trust
Follow Zuora Global Support on Twitter and LinkedIn

Community Manager

Re: How to pull a report of "Today's MRR" in Data Sources Export

An addendum to my last note, I found another gap in the query listed. If the date you're trying to establish the MRR for isn't today, but say the 1st of the year, the "Subscription.Status='Active'" isn't sufficient, you should be including any cancelled subscriptions that have cancelled since Jan 1st. So Status = 'Active' OR Status = 'Cancelled' should be used instead. Once cancelled, the rate plan charge effective end date changes to be the cancellation date so the Effective Start Date filter will filter out any  cancelled subscriptions that were cancelled BEFORE Jan 1st. So the full query should be:

select Account.AccountNumber, Account.Name, Subscription.Name as SubNumber, 
sum(RatePlanCharge.MRR) as SubMRR, sum(RatePlanCharge.TCV) as SubTCV, count(RatePlanCharge.Id) as SubCharges
from RatePlanCharge
where (Subscription.Status = 'Active' OR Subscription.Status = 'Cancelled')
and RatePlanCharge.EffectiveStartDate &lt;= '2014-07-01'
and (RatePlanCharge.EffectiveEndDate &gt; '2014-07-01' or RatePlanCharge.EffectiveEndDate is null)
group by Account.AccountNumber, Account.Name, Subscription.Name
order by Account.Name, Subscription.Name

 

By the way, saying Subscription.Status != Expired won't work as you'll start to pull in MRR/TCV from pending or draft subscriptions. 



Subscribe to Zuora System Updates at Zuora Trust
Follow Zuora Global Support on Twitter and LinkedIn