Recurring revenue over a long period of time is difficult to track mostly because it is registered only once at a single point in time but that amount needs to be amortized over a certain period of time (the length of the subscription or service) and at some point in time removed from the calculation, unless it is renewed.
In ClicData we can calculate recurring revenue easier than writing Excel macros. Although everyone’s data is different, the basic steps are the same.
Firstly, you will need a table of your invoices or billed items as follows:
Account ID – A unique ID identifying the account
Date – best if it is a date but always on the 1st of each month even if it took place on any other day.
Amount paid – the full amount of the subscription regardless.
Period – the number of months that the amount above covers.
Note: You may need to add currency and other items if you deal in multiple currency situations. Also if your invoices have multiple items such as subscription amounts and also credits or services, make sure the amount above is just for the subscription.
Now in ClicData we will create an end date for the above subscription and add a few more columns using transformation.
End Date – Here we will add the number of Periods to the Date to get the end date. For example, if our start date was March 1, 2016 and it was a yearly subscription (period=12) we calculate the EndDate to be February 1, 2017.
Amount Per Month – Amount / Period – simple right?
Type – This transformation (using LAG) will basically calculate for you if this is a new or a recurring amount.
Alright, now you are ready for the final phase, we need to defer the amounts where the period is greater than 1 until the End Date.
So create a table using the custom table editor with a date for each month start.
Create a view on this table and add a transform to aggregate all invoices where the period date falls between the start and end date.
Recurring revenue reporting is one of the hardest items to put on a dashboard. With ClicData we will continuously improve our functionality to make the process easier and simpler.
Don’t have time to do it yourself? Get in touch with our Professional Services team and they can build it out for you.
