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