How to Calculate ARR (Annual Recurring Revenue) in SQL

Today there are more revenue models than ever before, whether it’s advertising, subscriptions, licensing, e-commerce, etc. You likely have a few or many different products, depending on your business model. For example, B2B SaaS companies usually provide several business tiers for the core product. Either way, you need an easy way to analyze the revenue across your customers.

What is ARR?

No matter what industry you work in or what product you sell, annual recurring revenue (ARR) is one of your business’s most essential and relevant metrics. ARR provides an accurate representation of your yearly recurring revenue. You can calculate ARR for specific accounts or individual customers, or you can even analyze your data at a broader level to calculate the total ARR across all your accounts and customers. At its core, ARR is basically an annualized version of monthly recurring revenue (MRR).

How to Calculate ARR?

Believe it or not, calculating ARR is actually surprisingly simple. Although the factors you need to analyze when calculating ARR might differ based on your business model, the formula largely remains the same across industries. The simplest way to calculate ARR is simply to take your MRR and multiply it by 12.

Calculating your MRR can be somewhat challenging. To calculate MRR, you simply multiply your total number of customers by your average revenue per user.

You can calculate your average revenue per user by dividing your total revenue by your total number of users.

Why Does ARR Matter?

Customer acquisition costs (CAC) are only increasing. ARR is important for several reasons. Firstly, you can analyze the overall health of your company and see exactly how your revenue is trending, whether it’s increasing or decreasing. Secondly, ARR enables you to measure your return on your investment (ROI) because you can compare your ARR directly against your CAC to optimize your spending and understand your break-even point. All of this translates into better sales forecasting and increased revenue.

At face value, knowing your ARR is extremely useful because it directly correlates your customer spend data and the common trends across your accounts. For example, you might have identified that your highest-value customers have high utilization of a specific product feature, or maybe you’re spending too much money to acquire new customers. When you deeply understand your ARR, you can do more accurate forecasting, calculate your burn rate, and look for ways to increase your revenue.

Ultimately your goal should be to achieve net revenue retention (NRR) rate of 110% because this is a good indicator of how sticky your product or service is because it’s the percentage of recurring revenue retained over a set time period. You can calculate NRR using the following formula:

An NRR rate of 100% means that if you never onboarded another customer, your business would still continue to grow. Anything less means you’ll be trending downwards.

How to Grow Your ARR

Knowing your ARR is one thing, but acting on it is another, and your business teams can drive more value if they have access to this data. Suppose your Sales team has access to ARR in their CRM (e.g., Hubspot or Salesforce). In that case, they can compare product usage data to revenue and look for expansion opportunities or accounts with upsell potential.

Likewise, if your marketing teams have access to ARR data in their email automation platforms or their lifecycle marketing tools, they can create relevant campaigns using spend data. For example, you might want to send a special promotion to your most loyal customers or incentivize low-value customers who haven’t ordered in the last 30-days. You might even want to create customized in-app experiences for your audiences.

ARR data also has huge implications for customer support. One of the biggest challenges with support teams is prioritizing the right tickets as they come in. If your support team has access to ARR data, you can prioritize tickets by ARR to ensure you always address your most important customers first to reduce churn.

Calculating ARR Using SQL

Most of your transactional billing data lives in a finance tool like Netsuite or Stripe. The problem is none of this data is actionable because it’s only accessible to your finance team. The good news is that most of your customer data already lives in your data warehouse, which means you can calculate your ARR using simple SQL.

The very first step is to ETL (extract, transform, and load) data from your billing tool (ex; Stripe) into your warehouse.

It’s likely that your Billing tool has objects such as Subscriptions, Plans, and Products/Items, where a customer can have a subscription to a plan in order to receive access to a certain combination of Products.

Next, you need to combine these different types of Objects into a single table.

Here’s how we do that in Stripe by joining Subscriptions, Plans, and Products/Items. These tables all have a common Organization or Customer ID that we can use to determine which customer or organization is tied to a given plan.

SELECT 
  * 
from 
  subscription 
  left join subscription_item on subscription.id = subscription_item.subscription_id 
  left join plan on subscription_item.plan_id = plan.id 
  left join product on plan.product_id = product.id

Your Plan or Subscription object might already have an ARR field for you to use directly, unless your business relies on Invoices. If so, you can run a sum on Invoices to get the ARR. Here’s an example CTE on how to grab that:

With stripe_invoice_arr_by_date as (
  select 
    'invoice_id', 
    customer_id, 
    organization_id, 
    subscription_id, 
    invoice_id, 
    day, 
    greatest(
      sum(arr), 
      0
    ) as arr 
  From 
    < table_name >
)

Then, we group the arr column by each organization (with a group_by), filtering out customers with an ARR value of zero

 select 
  'Stripe' as source, 
  organization_id, 
  day, 
  arr, 
  0 as potential_arr 
from 
  stripe_invoice_arr_by_date 
where 
  arr is distinct 
from 
  0

How to Implement ARR

Once you’ve built your data model for calculating ARR, you simply need a way to sync that information to your downstream business tools. To do this, you’ll most likely need to create a new field for your accounts (e.g., ARR).

The problem is that building custom pipelines to move data out of the warehouse is hard. You must manage API integrations and build a pipeline for every SaaS application in your technology stack. You have to manage and maintain each of these pipelines. Going this route means you’ll inevitably spend more resources and money.

The good news is that Hightouch eliminates all of these problems. As a Reverse ETL platform, Hightouch runs on top of your data warehouse and syncs data to 100+ destinations. Hightouch enables you to leverage the existing data models in your warehouse and sync that information to the downstream tools your business teams rely on. With Hightouch, you simply define your data and map it to the proper fields in your destinations. Best of all, you can get started with a free Hightouch Workspace today!