How to Calculate Cumulative Monthly ARR

[IneedHelp]

Hello, 

I'd like to create a property that calculates cumulative ARR so I can use this on the reporting dashboard. 

How would I go about building a custom equation for cumulative monthly ARR?

[StepChange]

Hi @IneedHelp 

• Create "Monthly ARR" Property: Go to Settings > Objects > Deals > Properties, click Create Property, name it "Monthly ARR," select Number as the field type, and save.

• Create "Cumulative ARR" Property: Go to Settings > Objects > Deals > Properties, click Create Property, name it "Cumulative ARR," select Calculation as the field type, choose Roll-Up type, and set it to sum all "Monthly ARR" properties over time.

• Set Up a Workflow to Update Cumulative ARR: Go to Automation > Workflows, create a new deal-based workflow, set the trigger to activate on deal stage changes or new deals, and add an action to recalculate and update the "Cumulative ARR" property dynamically.

• Add "Cumulative ARR" to Reporting Dashboard: Go to Reports > Dashboards, create a new dashboard or edit an existing one, add a custom report, and use the "Cumulative ARR" property to visualize ARR trends over time.

[IneedHelp]

Thank you for the response. How do I get it to show the growth of MRR overtime?

[andrewzistler · Accepted solution]

Hey @IneedHelp,

The (general) formula for ARR/MRR = (Total revenue from new subscriptions) + (Recurring revenue from existing subscriptions) - (Churned revenue from existing subscriptions) + (Upgrades or downgrades to existing subscriptions) / Year or Month. 

Quick Note: If you have Sales or Service hub Professional, there is an automated feature to calculate ARR for you. You can learn more about Tracking Recurring Revenue in this documentation. There are also some relatively new features to HubSpot Deal Properties ARR and MRR, that use Line Items to automatically calculate. However, these features are newish and I have found them lacking in the past for detailed reporting. 

So, if it were me, I would cook up a manual solution.... This can be somewhat difficult to achieve, since you need several custom properties to track new vs old subscriptions, churn, and upgrades / downgrades.

There are multiple ways to do this, but below is one solution I've used in the past that basically uses new Deals in a separate pipeline to track recurring revenue. I've also seen Custom Objects be used for this purpose too. Either way, this should get you most of the way there:

How to Achieve Manual MRR / ARR Reporting in HubSpot

Before we begin, you'll need to ensure:

1. The Deal property "Amount" is correctly updated for all Deals, by either Sales or automation.
2. All Deals only have one Company associated.

3. When a customer churns, the churn date is noted.
4. If a customer upgrades or downgrades, a new deal in the main pipeline should be created with the new "Amount" reflective of their payment change.

Company records in HubSpot have the default property "Total revenue", which is automatically updated by HubSpot. When tracked properly, "Total revenue" will accurately display the cumulative revenue from every Deal, without the need for using up a valuable calculated property slot. We will use separate custom properties to determine if someone is a new/old/churn/downgrade/upgrade customer.

Note: The Company property "Total revenue" automatically adds up all values from the Deal property "Amount", for all associated Deals, when those Deals are set to a Closed Won Stage.

First, you will need to get the monthly subscription value per Company record in the Company object. However, your revenue data should be stored by default in the Deals object, under the Deal Property "Amount", or another custom property. Ensure the Deal property "Amount" is being properly updated via automation or the sales team to all Deals.

So, with that out of the way, the first hurdle is ensuring any recurring revenue is tracked with a Deal that is automatically moved to "Closed Won" every month. While we're building this automation, we may as well also factor in new vs old subscriptions and churn. Note: Isolating downgrades/upgrades will be more tricky, and will need to be tracked via separate automation, but they can be easily tracked in aggregate with this method - which is essentially a running total from creating new deals every month.

1. Property & Pipeline Setup:
   
   
   • Create a separate Deal pipeline. (This will help keep your main pipeline clear, this separate recurring revenue pipeline will be fully automated for reporting. Nobody needs access except to tidy up if an upgrade/downgrade/churn isn't caught.)
     
     
   • Create a Company property, type text, format currency, that lists the monthly revenue tier from that Company, such as "Subscribed Payment Tier."
     
     
     • Note: You'll need a separate function not detailed here to update the "subscribed payment tier." Without knowing your system, I can't tell you the best way to do that... but you'll probably be able to just have a one-time run Deal workflow copy the latest Deal Amount to the company property "subscribed payment tier" on Deal closed won.
       
       
     • Since upgrades/downgrades are tracked with a new Deal & Amount in your main pipeline, this will automatically update the rest of the function detailed below. You could also then make another Deal property called "Upgrade/Downgrade" with dropdown options for each - and then report on upgrades/downgrades with Deal reporting.
       
       
   • Create another Company property (dropdown options yes/no) called "New Customer This Month."
     
     
2. Track Recurring Revenue: Create time-based company workflow, set to trigger at the beginning or end of each month:
   
   
   • Trigger: "Subscribed Payment Tier" is greater than $0, AND "New Customer This Month" is "No". Turn re-enrollment on.
     
     
   • Action: Create Deal record in separate pipeline, stage closed won, amount = "Subscribed Payment Tier."
     
     
     • This way, every month, a new Deal will auto-populate with the correct amount, for recurring customers, which will be automatically added to the Company record in the "Total revenue" property. 
       
       
     • If they upgrade or downgrade, a new Deal in your main pipeline will update the "Subscribed Payment Tier", which will in turn update to the new auto-generated Deal amount, so any new auto-populated deals will also automatically adjust.
       
       
3. Track New Revenue: Create a separate company workflow, trigger "Total revenue" is known, re-enrollment turned off.
   
   
   • Sets "New Customer This Month" to "Yes" when company property "Total revenue" is known for the first time. Delay 31 days, then set "New Customer This Month" to "No".
     
     
     • So, when the first deal closes, they'll be set to a new customer, then next month, they'll be counted as recurring.
       
       
     • You can get this more exact than 31 days if you create a workflow that triggers on a certain time each month too.
       
       
4. After all that, it's time to make the reports. Now all new customers are marked, and all recurring revenue, including upgrades or downgrades, automatically rolls up to the company property "Total revenue".
   
   
   • For MRR: Simply create a Custom Company report with with KPI chart, and use "Total revenue". Edit the "Total revenue" property in the report to "Sum" you can also exclude new revenue by using with "New Customer This Month" is none of "Yes". 
     
     
   • For ARR: Create a Custom Deal report, KPI, use "Amount" and change it to "Sum". Filter by "Close Date is This Year / Last Year."
     
     
5. Add in Churn: If we want to dig deeper for proper ARR reporting, we'll need another custom property: "Churn Date".
   
   
   • You'll also want to add an automation to copy company property "Subscribed Payment Tier" to "Previous Payment Tier", and then clear "Subscribed Payment Tier". Churned revenue will now no longer be added to to the running total.
     
     
   • Add "Churn Date" is "Unknown" to the first workflow filter too, so no new deals are created. If a subsequent new deal in the main pipeline comes in from them resubscribing, similarly backup "Churn Date" to a "Previous Churn Date" and then clear the "Churn Date" property.
     
     
   • You can then make another report with "Previous Payment Tier", Summed, filtered by "Churn Date is this month / year" to visualize churn.
     
     
6. Tracking upgrades / downgrades: This gets a bit more tricky, but I would accomplish this via simply adding another custom property on the deal that denotes which tier of subscription is currently active, and then summing those options in reporting. If you want to go truly granular though, you might want to use a Custom Object to track your revenue instead. 
   
   

Anyway, I'm well beyond the scope of creating a simple cumulative recurring revenue property... so I'll stop for now. Calculating proper ARR/MRR can be difficult to build out manually.

The solution above may not be perfect for your company, but the info should be a good start nonetheless, and I've seen it be successful. One benefit of creating multiple Deal records to track revenue is that they can be exported and easily cross-referenced.

I hope all this info helped - cheers!