Building your ARR

• By
• Chris Burgner

  Chris Burgner

  12+ years of experience, most recently as a Director of Finance at Intercom.

  

Bobby’s original script for building out ARR at Intercom was thousands of lines of Python.

Here’s how that script calculated expansion. It would sort customers’ charges by billing number, calculate the difference between the periods, and return the delta or the expansion amount.

charges['expansionMRR'] = 0  
charges['billingNum'] = 0  
for x in set(charges['stripe_customer_id']):
  temp = charges[charges['stripe_customer_id'] == x]
  temp = temp.sort(['created_at'], ascending=[1])
  for y in range(len(temp)):
      charges.ix[temp.index[y], 'billingNum'] = (y + 1)
      if y != 0:
          charges.ix[temp.index[y], 'expansionMRR'] = (
              temp.ix[temp.index[y], 'mrr_amount']
              - temp.ix[temp.index[y-1], 'mrr_amount']
          )  

expansionMRR = charges[['unixMonth', 'expansionMRR']].groupby('unixMonth').sum().reset_index()  
expansionMRR = expansionMRR.sort('unixMonth')

This ultimately turned into a multi-thousand-line Python script that pieced various pricing models together. It accounted—pretty horrifically and manually—for all the ways in which somebody could pay us (monthly, quarterly, annually) using all the different methods.

In some ways, it was a pure miracle that this worked, and we were able to keep it all together. The script was brittle, though. It’d break anytime we changed something about our business. Worst of all, nobody could use it but Bobby, meaning nobody else could do ARR analysis—which became a major problem as we scaled.

The most intimidating part was not knowing how to turn this Python script and its output into something that would scale. For Bobby, the Finance team, and everyone else that would ultimately need to report in some way on ARR.

We ultimately settled on the following format—the table we outline in this chapter. It was a complete game changer in how we and the rest of the company accessed ARR reporting.

How to build your ARR tables

Now it’s time to turn all the logic you’ve defined into a source of truth. This exercise aims to create a set of tables that you and your team can use to report on ARR easily and efficiently. All of your logic will be applied in various SQL queries.

As you get stuck into this, here are three principles to remember:

1. You are ultimately building towards a one-stop-shop reporting table. This table should be comprehensive enough to quickly answer +90% of ARR questions. Be thoughtful about what fields that output table should include to streamline reporting pulls.
2. The queries in this exercise will be complex. Be intentional about making them comprehensible. Stay organized in how you write your query. Drop in comments. Modularize the query into as many bite-size components as possible. The goal is to make this query approachable to others. There’s no way to avoid it—this query will be long, but that doesn’t mean it should be impossible for others to understand.
3. Your ending ARR table should play nicely with other tables you build around it. This ARR table will help you answer the large majority of questions related to ARR, but it’s also the centerpiece for other questions you’ll want to ask about the business. That’s why you’ll want to create a design framework that makes it simple to join additional datasets to the ARR Build.

First, we’ll outline the process and pieces you’ll put together as part of this exercise. If this isn’t quite obvious or intuitive at first, don’t worry. We’ll walk through each table in more detail and show examples to help illustrate.

1. It starts with your Base ARR table. Think of this as creating the ingredients and all the raw data from which you are going to transform your data.
2. Within your query, you’ll apply the considerations and business logic outlined in the previous table. For example, here, you’ll define start and end dates, trial periods, etc.
3. That creates a Clean ARR table, which now has all of your considerations and business logic applied.
4. Next, you’ll create a simple Date table. This table outputs every day of every year across which you have ARR data.
5. Now, join your Clean ARR table to your Date table, which creates what we call a Padded ARR table. In other words, it creates a table that shows your Cleaned ARR table with all its business logic applied by customer, for every day.
6. From this Padded ARR table, you’ll calculate your Net New ARR—or the net differences for each customer daily. You’ll use those net calculations to categorize ARR into its component parts: Gross New, Expansion, Contraction, and Churn.
7. From here, you create your Final ARR table. This is the end state. It serves as your source of truth—housing by customer, ARR in its component parts, for every day.

Here’s how it looks at a surface level:

Let’s dig into each table in more detail.