Happy Business Starts Here

Highlighted
Valued Scholar

Reporting on MRR by Customer and Product Over 24 Month Period

I am trying to figure out how to create a report that shows MRR by customer and then by the products each customer has over a period of 24 months

 

For clarity, rows down the side for every customer with sub-rows for their products and columns across the top for each of the past 24 months with whatever MRR was for that month. The MRR Trend report seems close but doesn't have the granularity to show product.

 

Thanks!

1 REPLY 1
Valued Scholar

Re: Reporting on MRR by Customer and Product Over 24 Month Period

It all depends on if you have Orders enabled or not.  And you'll need to build some excel formulas to do this.

 

If you don't have Orders enabled.  I think its as simple as pulling in these five columns

 

Account: Account Number

Rate Plan Charge: Name

Subscription: Subscription Start Date

Subscription: Subsription End Date

Rate Plan harge: MRR

 

Once you have these, just build a bunch of columns on the export

Column 6 header: eomonth(min(subscription start date),1)

row 1 column 7: eomonth(column 6 header, 1)

 

The above two formulas sets out the MRR months

 

Then this is the formula you need for MRR per month per account (entered below the header, obviously)

=if(and("start date"<= header, "end date">= header), MRR, 0)

(Definitely check the formula bove, I use it for Orders MRR, not sure if it's totally applicable to pure RPC, although it should).

  

After the sheet calculates, just pivot this whole data set in a separate worksheet.  Throw in all the months as columns and rpc_mrr as value.  And you should be to slice and dice however you want (i.e., account number, product, whatever other attribute you can associate to the account).  Region and Plan is usually a good crowd pleaser Smiley Wink