Using the movement activities that are generated every time there is a change to a customers MRR, it's possible to construct a report that displays what each customers MRR was at a certain point in time. The following instructions will explain how to generate that report in an Excel spreadsheet.
The template needed is attached below.
There are a few requirements for this report to work
- A newer version of Excel
- Some knowledge of Excel and pivot tables
The first task will be to obtain all of the historic movements for each customer. Since the movements of the previous month are required to build the MRR for the next month, you need to do a complete activity export in order to get an accurate MRR across the board even if you only need the report for a specific period.
The easiest way to get this information from the Dashboard is as follows:
- Go to the Reports > Heat Map
- Click on the Net MRR Movements (lower right-hand corner)
EXPORT(CSV)and then click OK on the pop-up; a CSV will be emailed to you
- You should receive an email with a link to download a CSV containing all activities for the history of your account.
Note: Depending on the number of activities that are being exported, it could take a bit of time before you receive the email.
Setting up the report
Once you have obtained a complete list of activities from your customers in one CSV file, choose the appropriate template based on how your timestamps are exported:
If the date timestamp in your activities export looks similar to this: 2018-01-01 01:00:00 +0100 Use the following template: MRR_by_Customer_by_Month_-_CM_Test_Data_-Verison_1.xlsx
If the date timestamp in your activities export looks more similar to this instead: 2018-01-01 1:00 Use the following template: MRR_by_Customer_by_Month_-_CM_Test_Data_-Verison_2.xlsx
Follow the below steps to correctly fill out the template.
- Open the CSV export and copy columns A:I. Paste those columns in the Excel template in columns A:I in the sheet labelled "Raw Export"
- Extend the formulas in columns J & K in the sheet "Raw Export" so that they cover all lines from the export
- Refresh the pivot table in the sheet "Pivot" by right-clicking anywhere in the pivot table and then selecting "Refresh"
- Double-check that the pivot filter for the fields "Customer Name & UUID" and "Currency" are marked as "Select all"
You should now see a list of all your customers with their individual MRR broken out historically month over month, split by billing currency (if applicable).
Note: There are a few limitations within Excel that you may encounter depending on the number of activities you are exporting:
- Excel has an upper limit of ~1 Million rows per sheet meaning that if you have over a million activities this template may not produce accurate results.
- Starting around ~100k movements, Excel may begin to slow down. You can work around this by copying & pasting values in columns J & K after you have extended the formulas.