5. Breaking Down Changes in MRR

 
Subtitles Enabled

Sign up for a free trial to access more free content.

Free trial

Overview

There are several different components of MRR for Interslice. In this lesson, we’ll create a report allowing users to analyze each of these components individually

Summary

Things that can cause a change in MRR

  • New: A new customer is one who has just signed up. In their first month, all of their MRR represents an increase in MRR for the company
  • Expansion: This occurs when an existing customer adds more licenses or upgrades to a more expensive plan
  • Contraction: This occurs when an existing customer reduces the number of licenses or downgrades to a cheaper plan
  • Churn: This occurs when a customer unsubscribes and leaves the company. All of their MRR is lost to the company.

Analyzing MRR Changes

  • We create a measure to identify rates of churn, expansion etc. instead of monetary amounts
  • We also analyze annual rates of change instead of monthly rates

MRR Changes for Interslice

  • There is some evidence that churn rates for Interslice could be seasonal, although with only two years of data this is not certain
  • Expansion is generally larger than contraction, which is positive
  • The churn rate is relatively high, but this is not an immediate concern as the growth rate of new customers is large and is contributing to the growth in MRR

Transcript

In the previous lesson, we started our MRR analysis at a high level. In this lesson, we'll dig a little deeper and create charts that analyze the various components of MRR.

The Transactions table contains a column called MRR Change. This column measures the difference between a customer's MRR this month and their MRR in the previous month. This figure will be positive for a new or expanding customer and negative for a churning or contracting customer. We'll use this field as a base for our analysis.

We'll return to Report View and add a line chart to the bottom left part of the canvas.

We'll then navigate to the transactions table, add the MRR Change field to the Values Well, the Transaction Date field to the Access Well and the Locations field from the Customer table to the Legend Well. As before, we'll expand the chart down to the monthly level.

We now have a different line for each region. The MRR change appears to be positive for each region in each month reflecting the positive MRR trends we've seen previously. However, we want to look at the various components of this MRR change separately.

To accomplish this, we'll add a slicer to the canvas and place it above the chart.

We'll then add the MRR category field to the slicer, select Churn and see that the line chart now displays revenue loss to churn in each month for each region.

It's worth noting that churn can be analyzed from a revenue perspective where we measure the amount of revenue lost in a particular month or a customer perspective, where we measure the number of customers who churn in a particular month. In this case, we'll focus on the revenue perspective.

We'll consider the customer perspective in a later lesson.

It's generally best practice to chart percentages instead of monetary values when analyzing churn. To accomplish this, we'll create a new measure, and call it Churn Rate.

This will be the MRR loss to churn divided by the total MRR for the previous month.

I'll place in the formula and quickly run through it. The main argument for this measure is the divide function. As the slicer already filters the dataset to only show churn, the numerator is sum of MRR change. The denominator is the calculate function.

We're calculating the sum of the previous month MRR field and the filter condition is an all function applied to the MRR Category field. This percentage will be negative, so we've wrapped an ABS function around the whole formula to get the absolute value.

We'll press enter to create the measure. We'll then navigate to the Modeling tab and format the measure as percentage.

We'll now create a chart of this churn rate.

We'll create another line chart and place it to the right of the existing one.

We'll add Churn Rate and Transaction Date. We'll then click the branched arrows twice to expand down to the monthly level. From this chart, we can see that churn was high in January 2016 with over 1% of revenue lost in a single month. However, subsequent months all show lower churn rates.

There also appears to be a seasonal element to churn rates. In both 2016 and 2017, churn rate is particularly low in July, August and September in our slicer to account for this seasonal dip if they plan on taking action to combat churn.

Note that our measure will also calculate the expansion or contraction rate if we adjust the slicer. Let's change the slicer value to Expansion.

We can now see Amount and the percentage of MRR in each month which comes from companies expanding. Calling the measure Churn Rate is now misleading, so we'll double click the Measure Name and rename it MRR Change Rate.

This measure calculates the monthly expansion rate but management would prefer to see annual rates.

We'll create a new measure called Annual MRR Change Rate.

This will simply be the MRR change rate multiplied by 12.

We'll press enter to accept the measure.

Again, navigate to the Modeling tab and format it as a percentage.

Let's now set the slicer back to Churn and replace the MRR Change Rate in our second line chart with the Annual MRR Change Rate.

This shows us the same chart as we saw previously but with a different scale.

Let's go to the Analytics pane and add an Average line.

We can see the Average Annual Churn Rate is almost 6% over the course of 2016 and 2017.

This is relatively high especially considering the growth in MRR we've seen during this period.

If we change the slicer value to Contraction, we can see the contraction rate averages about 2.5%.

Selecting Expansion shows an average annual expansion rate just below 5%.

However, when we select New, we can see that new customers are contributing a significant amount of revenue.

On an annual basis about 40% of revenue is coming from new customers which indicates a company is expanding rapidly.

This is clearly the main factor in the growing MRR we've seen previously. Note that this method of visualization is not perfect since we can only look at one MRR category at a time.

In the next two lessons, we'll look at other methods of visualizing all the MRR categories together.

Power BI Case Study

Contents

My Notes

You can take notes as you view lessons.

Sign in or start a free trial to avail of this feature.

Free Trial

Download our training resources while you learn.

Sign in or start a free trial to avail of this feature.

Free Trial