Happy Business Starts Here

MRR Metric in Data Source Question

Hello,

 

I am trying to build a report to represent the MRR Change over the period of time for a life cycle of a customer. I created an account, subscription and series of amendments (new product, remove product, update, renew, cancel)

 

Created Account# A00000573 and Subscription# A-S00000185 with start and end date 3/10/2017 to 3/10/2019

I exported data out of Rate Plan Charge Data source where I found many records for single charge which made me confused.

 

I ran MRR Trend and Change Report by month and account groups.
MRR Changes Report - it is showing all MRR metric in the month of Mar-2017. Not sure why?
MRR Trend Report - though it is showing metric over the time it seems incorrect. For example when this subscription started MRR is $100 and when it ends after 3/10/2019 MRR should show $0

 

So, wanted to understand how to

  1. Currently I can see it rate plan charge store record only when there is a change MRR i.e. with effective start and end date. Is there a way we can get records from any other data sources for each month how much MRR?

  2. When and which scenarios last segment and segment fields in rate plan charge object is used in reporting?

  3. When customer cancels from the cancellation effective date onwards MRR is $0. However I do not see this entry in data source?

7 REPLIES 7
Zuora Staff

Re: MRR Metric in Data Source Question

  1. Currently I can see it rate plan charge store record only when there is a change MRR i.e. with effective start and end date. Is there a way we can get records from any other data sources for each month how much MRR?

    No, not in the standard reporting package. This information is available in the Insights add-on. 
  2. When and which scenarios last segment and segment fields in rate plan charge object is used in reporting?

    Update product amendments generate segments and so segments track the changes before and after the update product amendment. So as a simple example assume an annual charge for 5 units at $20/unit that is first billed on Jan 1 2018. The rate plan charge data source object will have an effective start/end date of 1/1/18 and 1/1/19. There is always a value for segment, that's alway set to 1 inititially. If there are never any update product amendments applied to the charge, the segment value will always be one. But for our example let's assume that on 3/21/18 I create an update product amendment, effective immediately and change that quantity from 5 to 8. Now when you query the rate plan charge data source for the charge number you'll see two segments, there's the original segment, still numbered, 1, but which now has an effective end of 3/21/18. There is a new segment, numbered 2, that has an effective start and end date of 3/21/18 and 1/1/19 and that also has the new quantity value of 8 (that first segment still shows 5 for the quantity). So now you can see the charge before and after the update product amendment as two charge segments. Add another update product amendment and you'll create a third segment with the new value of either quantitity or price or both and the appropriate effective start and end dates. The 'old' segment always get's it effective end date ratcheted back to the effective date of the update product amendment. While this seems a little byzantine the result is you can always see what the MRR was on any particular day - you search for the rate plan charge and charge segments that have an effective start/end date that straddles the date you care about. 
  3. When customer cancels from the cancellation effective date onwards MRR is $0. However I do not see this entry in data source?

    I'd argue this is implied, the charges all have their effective end date set to the cancellation date from whatever their original end date way. Any date after this should assume that this charge has no impact on MRR. You should always be filtering on effective start/end date straddling the date you care about, this eliminates the MRR of charges that weren't active on that day. 

 

A couple of other general issues:

An old colleague of mine once pointed out that the rate plan charge object and data source is incorrectly named, it should be called, the rate plan charge SEGMENT object and data source, most charges only ever have one segment, but once you hit the parent rate plan with an update product amendment the rate plan charges start to divide, amoeba, cell like, into multiple segments.

 

Charges in evergreen subscriptions never have an effective end date, if there are multiple charge segments that highest numbered charge segment always has no effective end date. When you cancel an evergreen subscripton the effective end date gets set to the cancellation effective date. 

 

None of the above discussion considers the impact of subscripton versions where charges are copied to a new subscription after every amendment. You should always look at the most recent version of the subscription, it's tagged as either 'active' or 'cancelled', ignore the versions of the subscription tagged as 'expired'. You can see a great example of how to determine MRR accurately for any day by reviewing the out of the box reports, 'MRR by Account' or 'MRR by Product'. These standard reports are coded to run for 'today', but if you modify the report and click the box to allow the user to specify the date when the report is run you can see MRR on that date so long as you use the SAME DATE for both input parameters. Do not attempt to run the MRR for 'March', you can run it for March 1st and again for April 1st and you can then review the differences by exporting them into Excel or Google Sheets but since MRR can change from one day to the next you can only report MRR on a specific day in a single query/report. Going back to your original question, part of the value of Insights is that it does the tedious job of determining MRR for each day and putting that into a new data source that is then used to display the MRR trends over time that Insights supports. This is all standard data warehouse stuff, which many customers have implemented themselves, but which Insights now takes care off automatically. And that's also why we charge extra for Insights, that warehouse consumes way more computing resources than basic reporting. 

Re: MRR Metric in Data Source Question

Thanks, Richard. This helps

Re: MRR Metric in Data Source Question

@Richard is there any other option available for customers who do not wish to buy Insights or do not have warehouse. Is there anything out of the box report/data source export mechanisms that they can see how thier future pass MRR trend looked like?

Zuora Staff

Re: MRR Metric in Data Source Question

Advanced Tutor

Re: MRR Metric in Data Source Question

Hi Richard,

 

Yes, this report is very useful one thing that I notice is that if the Account Status is equal to Canceled the customer won't be in this export, even though we invoice them in the past. Is there a workaround?

Zuora Staff

Re: MRR Metric in Data Source Question

The MRR Trend export is completely self contained, beyond the filters on the export page there is no further customization possible. You should be able to get MRR as of any day, e.g. June 1st or July 1st using the Net MRR by Account page (you'll need to modify the date filters by checking the 'ask later' checkbox so you can pick your day when you run the report), but to get Net MRR over a series of months in a report that you can customize you need our Insights add-on.  

Honor Student

Re: MRR Metric in Data Source Question

Hi Richard,

 

MRR trend Export is very much helpful, is there a way to download this data through API rather than doing it manually. Any documentation around this would be helpful. Thanks in advance.