hi guys and welcome to the template on metrics and dashboards for software as a service companies
so i want to quickly show you the single steps that you need to take in order to customize
this template and to suit your needs and i can guarantee you already it's going to be super
simple to customize so first let's take a quick look at the different sheets we have one sheet
that's called mapping now by now we are very well aware of what these sheets are about and
about this template really you just need to update the
month that you would like to display so in this particular case we're looking at
24 months of data june 2019 until june 2021 so we're including a bit of a projection in here
then the prior month is also to be maintained right this is also going to be super important
especially as we look at the visualization of different data and also to look at growth rates
the other part about this template is that you need to update your particular business model
so in this 10-day with the pre-populated data we actually have three different subscription models
basic advanced and premium and all three of them have different monthly prices
this is going to be super important as we try to calculate the mrr and arr going forward but
apart from that this is pretty much it on the mapping sheet so let's move on to the metrics
now the matrix as such i would say 90 of it is calculated automatically you don't need to take
any action here all that you need to do actually is update the cells in white so i want to quickly
walk you through the different sections in here the first one is an email newsletters tracker
so basically with people signing up for a newsletter we start off with 45 in june
and then we just added the new signups and the people who actually unsubscribe to whom we refer
to as the churn that will automatically calculate the total number of email subscribers at period
end and this will then be the beginning number of subscribers in the next period
so you see that all you need to update in fact is just these two lines because everything else will
be calculated automatically now the churn rate is calculated automatically actually taking into
consideration the people unsubscribing versus the total signups that we had for the month
and then we also have a so-called annualized retention rate in which we basically just
deduct the churn rate and we multiply to the power of 12 right so this is giving us
an annualized retention rate which as we increase the number of subscribers of course gets bigger
meaning that more and more people are actually staying with us now let's move on to the next
section which is actually subscriptions now these are the bare number of subscriptions
that people sign up for now as i said before we have the basic advanced and premium package
and for all these three different sections we essentially have the same setup as with
the email newsletters we have a beginning number of customers new customers coming in and a churn
so that gives us with an ending number of customers as well as the churn rate this
will be important because we need to keep track of the subscribers first we want to understand
how many people actually opt for which kind of subscription service
and then later on as we want to calculate revenues this will of course also be very important
now everything else is calculated automatically no need for you to take any action here
we can actually close this one because once we've populated all this information we will
see a summary down here at the bottom which also gives us the total number of customers overall
now once we have this information available we can actually calculate the subscription revenue now
the subscription revenue is the combination of the number of subscriptions multiplied by the monthly
price so you see that we are actually taking the number at the top for the subscriptions right over
here and then we're using a vlookup formula to bring in the information from the mapping table
which is basic 15 bucks per month that will give us the subscription revenue for each and every
month now of course for our kind of business it's also important to actually calculate the monthly
recurring revenue assuming that these people will stay with us so that would just be the sum of all
the three different subscription models now if you want to look at the full year assuming that none
of these are actually leaving us and there will be no change in the number of subscriptions then
we can actually multiply this by 12 and we will get the annual recurring revenue or arr so this
is it for the metrics once you've done this part pretty much everything else will be calculated
automatically and what i mean by that is if we just quickly look at the dashboard over here
you will have one selection right here in which you can just select a certain period
april 2020 and at one glance you will see all of the relevant information that's coming right here
total number of email subscribers the churn rate total number of customers mrr arr
and then also visualizations thereof right so down here at the bottom you have a couple of charts
that really display the changes from one period to the other
if we move over to the charts i just want to give you a brief outlook as to what you can do with the
different data that is provided to you we can visualize the email newsletter subscriptions by
saying this is our target that we want to achieve in terms of the churn rate it's about two point
it's 2.3 percent and then here in the light green we see the evolution of the churn rate across
the periods and you see that it's actually going down right as we increase the number
of email subscriptions the other hand side right here we can have a look at the mrr and
customer overview you see that the mrr actually remains quite stable it's at least more stable
than the number of customers so of course we would like to know what's behind that what's
driving that because our revenue seems to be more stable than the actual subscriptions so for this
we could actually look at the bottom chart right here which actually displays the mrr by category
basic advanced and premium and you will also see that there are certain trend lines behind it for
example in the advanced subscription you have a slight increase in the tendency across the periods
the premium ones are slightly going downward and then the basic ones essentially remain flat
you could also show this in terms of monthly subscriptions by category right and if we're
just showing a bar chart giving you the total number of subscriptions each and every month
alternatively if you don't like these views there's another one down here at the bottom
where you can see the monthly subscriptions by category this time again giving you a kind of
different view at the data and also the mrr by category shownus bar charts so overall
i hope that this template will be useful for your business especially as you
try to customize it with your different offerings and maybe you want to bring in
one or two more views but these charts and the dashboard will give you some
great inspiration to really get started with your business and track your revenues accordingly