Custom Forecast ARR Field and Subscriptions

[DCrinnion1]

Hi All,

Slightly niche one here as Ihave started using Subscriptions so my company can track multi year deals in revenue i.e. 1 year discounted then 2 full price and I have created a couple of Custom fields for ARR and Forecast ARR. 

They are both Roll up fields from the Subscription object.

In an ideal world I would love my ARR field to rollup Annual Recurring Revenue from the Subscription Object when the deal is active OR the deal is scheduled but its the first subscription. Then Forecast ARR to only show when its Scheduled and its not the first subscription.

Bearing in mind we don't have Ops Hub and my company seems to want to keep it this way any help would be amazing please. Happy to create a video or go on a call with someone to talk 🙂

[danmoyle · Accepted solution]

Hey there @DCrinnion1! Interesting use case here. I know HubSpot has had limitations over the years with managing ARR. Let me see if I can help here a bit. 

I think that to do this without Operations Hub, you're on the right track with custom properties. As Pam mentioned, workflows will also help. Plus I'd look at calculated properties.

Here's what I'd start with based on some research. I'd start with these custom properties.

• Create a custom property on the Subscription object called "Subscription Order" (Number).
• Create a custom property on the Deal object called "First Subscription ARR" (Number).
• Create a custom property on the Deal object called "Subsequent Subscriptions ARR" (Number).

Next I'd suggest three workflows. 

First: Set Subscription Order. 

Trigger: Subscription is created

Action: Set "Subscription Order" to the count of associated subscriptions for the deal

Next: Update First Subscription ARR.

Trigger: Subscription is created or updated

Conditions:

Subscription Order = 1

Subscription Status = Active OR Scheduled

Action: Copy Subscription ARR to Deal "First Subscription ARR"

Then: Update Subsequent Subscriptions ARR.

Trigger: Subscription is created or updated

Conditions:

Subscription Order > 1

Subscription Status = Scheduled

Action: Sum of all Subscription ARR where Order > 1 and Status = Scheduled, set to Deal "Subsequent Subscriptions ARR"

With all of this, you should be able to have some calculated properties to help with reporting.

• ARR (Calculated): Formula: [First Subscription ARR]
• Forecast ARR (Calculated): Formula: [First Subscription ARR] + [Subsequent Subscriptions ARR]

This setup should ensure that the first subscription's ARR is always included in the ARR field, regardless of its status, and subsequent subscriptions are only included in the Forecast ARR when they are scheduled.

One final thought. To help handle deals closed with future subscriptions, you can create an additional "Close Deal Workflow."

Trigger: Deal stage changes to Closed Won

Action: Set the first subscription's status to Active

This will ensure that when a deal is closed, the first subscription is always considered active, even if it's scheduled for the future. And of course my usual advice to clients when I suggest a solution: Remember to test these workflows thoroughly and adjust as needed for your specific use case 😊

I hope this helps! 

[danmoyle · Accepted solution]

Hey there @DCrinnion1! Interesting use case here. I know HubSpot has had limitations over the years with managing ARR. Let me see if I can help here a bit. 

I think that to do this without Operations Hub, you're on the right track with custom properties. As Pam mentioned, workflows will also help. Plus I'd look at calculated properties.

Here's what I'd start with based on some research. I'd start with these custom properties.

• Create a custom property on the Subscription object called "Subscription Order" (Number).
• Create a custom property on the Deal object called "First Subscription ARR" (Number).
• Create a custom property on the Deal object called "Subsequent Subscriptions ARR" (Number).

Next I'd suggest three workflows. 

First: Set Subscription Order. 

Trigger: Subscription is created

Action: Set "Subscription Order" to the count of associated subscriptions for the deal

Next: Update First Subscription ARR.

Trigger: Subscription is created or updated

Conditions:

Subscription Order = 1

Subscription Status = Active OR Scheduled

Action: Copy Subscription ARR to Deal "First Subscription ARR"

Then: Update Subsequent Subscriptions ARR.

Trigger: Subscription is created or updated

Conditions:

Subscription Order > 1

Subscription Status = Scheduled

Action: Sum of all Subscription ARR where Order > 1 and Status = Scheduled, set to Deal "Subsequent Subscriptions ARR"

With all of this, you should be able to have some calculated properties to help with reporting.

• ARR (Calculated): Formula: [First Subscription ARR]
• Forecast ARR (Calculated): Formula: [First Subscription ARR] + [Subsequent Subscriptions ARR]

This setup should ensure that the first subscription's ARR is always included in the ARR field, regardless of its status, and subsequent subscriptions are only included in the Forecast ARR when they are scheduled.

One final thought. To help handle deals closed with future subscriptions, you can create an additional "Close Deal Workflow."

Trigger: Deal stage changes to Closed Won

Action: Set the first subscription's status to Active

This will ensure that when a deal is closed, the first subscription is always considered active, even if it's scheduled for the future. And of course my usual advice to clients when I suggest a solution: Remember to test these workflows thoroughly and adjust as needed for your specific use case 😊

I hope this helps! 

[PamCotton]

Hey @DCrinnion1, thank you for posting in our Community!

It sounds like you're trying to create custom logic for ARR and Forecast ARR fields based on deal status and subscription type. Without Ops Hub, you'll have limited customization, but you can still achieve something similar using workflows and custom properties. You could create workflows to update custom properties when a deal's status changes or when a subscription is marked as the first or subsequent one. 

For example, workflow triggers based on deal stages and subscription dates can be used to roll up values only when certain conditions are met.

To our top experts, @TomM2 and @danmoyle any recommendations for @DCrinnion1?

Thank you,

Pam

[danmoyle]

Thanks @PamCotton 😊 I'll see what I can come up with to inspire a solution for @DCrinnion1. 

[DCrinnion1 · Accepted solution]

Hey Dan! I created this using ChatGPT I tried to correct where I knew it was wrong, believe this may be feasible as my company has engineers who can write API endpoints. Does this look like a feasible idea to you? To give context this is a Custom Object that will take information from Line items, record it and then push to the deal object, on multi year deals.

Step 1: Create the ARR Custom Object

1. Navigate to HubSpot Settings:
• Go to Settings > Objects > Custom Objects.
2. Create a New Custom Object:
• Click Create Custom Object.
• Name it ARR (Annual Recurring Revenue).
3. Create Custom Properties for the ARR Custom Object:
• ARR Value (Number): This field will store the recurring revenue value for the ARR record.
• Implementation Fee (Number): This field will store any one-time payments (e.g., implementation fees).
• Total Value (Number): This field will calculate the total value of the contract (ARR value * Term).
• Start Date (Date): The start date of the recurring revenue period.
• End Date (Date): The end date of the recurring revenue period.
• Status (Dropdown): The status of the ARR record. Options: Scheduled, Active, Expired.
• Associated Deal (Association): Link the ARR custom object to the Deal object.
4. Optional Properties:
• Line Item ID (Text): Store Line Item ID for tracking purposes.
• SKU (Text): Store the SKU for the line item (e.g., CCI0 for Implementation Fees).

Step 2: Create Custom Properties on the Deal (if necessary)

1. Create custom properties on the Deal object to hold ARR-related data if not already available:
• ARR Value (Number): This field will hold the sum of the ARR values from associated ARR records.
• Start Date (Date): The start date of the ARR period.
• End Date (Date): The end date of the ARR period.

Step 3: Set Up Workflow for Creating ARR Records

Since Line Items cannot be directly associated with the ARR Custom Object, we will use Deal-based workflows and custom properties to store the data from Line Items.

1. Create a Deal Workflow Trigger:
• Go to Automation > Workflows > Create Workflow.
• Select Deal-based Workflow.
• Set the trigger to When a deal is moved to 'Closed Won'.
2. Fetch and Populate Line Item Data:
• Use a custom API integration (if available) or manually create custom properties on the Deal to track Line Item data. Key properties to extract:
• ARR Value (from Line Item)
• Term (Term of the subscription in months)
• SKU (For identifying if it's a one-time fee or subscription).
3. API Integration (Optional):
• Use the Deals API to fetch associated Line Items and populate the relevant custom properties on the Deal (like ARR Value, SKU, Term, etc.).

Step 4: Create ARR Records Based on Line Items

1. Action 1: Create ARR Record for Subscription Line Items:
• When a Deal is marked as Closed Won, create a new ARR Custom Object record for each subscription-based Line Item:
• If the Line Item SKU is a subscription, use the ARR Value, Term, and Billing Start Date to create the ARR record.
• Set the ARR Value to the Line Item's ARR Value.
• Calculate the Total Value: ARR Value * (Term / 12).
• Set the Start Date based on the Line Item Billing Start Date.
• Set the End Date based on the Line Item Term (e.g., if Term is 12 months, set End Date to Start Date + 12 months).
2. Action 2: Create ARR Record for One-Time Payments (Implementation Fees):
• If the Line Item SKU is CCI0 (Implementation Fee), create an ARR Record with the following:
• Set Implementation Fee to the Line Item amount.
• Set ARR Value to 0.
• Set Total Value to the Implementation Fee.
• Set Start Date to the Line Item Billing Start Date.
• Set End Date to the same as the Start Date.
• Set Status to Active.

Step 5: Update the Deal with ARR Record Information

1. For the First Non-One-Time Payment ARR Record:
• When the first ARR record is created (i.e., it's a subscription-based Line Item, not an Implementation Fee), immediately:
• Update the Deal’s ARR Value with the ARR Value from the first created ARR record.
• Set the Deal’s Start Date to the Start Date of the first non-one-time payment ARR record.
• Set the Deal’s End Date to the End Date of this ARR record.
2. For Subsequent ARR Records:
• For subsequent ARR records (with Status = Active), add the ARR Value to the Deal’s ARR field.
3. Recalculate the Deal’s End Date:
• Use a workflow to check all associated ARR records and determine the latest End Date.
• Update the Deal’s End Date to reflect the latest End Date among the associated ARR records.

Step 6: Workflow to Recalculate Deal End Date

1. Create a Workflow to Recalculate the Deal End Date:
• Trigger: This workflow should be triggered when an ARR record is created, updated, or deleted.
• The workflow will fetch all associated ARR records linked to the Deal.
• Identify the latest End Date from all associated ARR records.

Update the Deal’s End Date with the latest End Date.

[danmoyle]

@DCrinnion1 this looks like a pretty good road map. I think it could work. I'd test as much as I could for sure, maybe even using a tool like Zapier in place of the API temprarily. 

[DCrinnion1]

Hey Pam!

Effectively I am trying to create a situation where- When a subscription is created it adds the start date the end date the ARR and perhaps the forecast ARR. 

At the moment on multi year discount deals we add multiple subscriptions per deal so that we can get the different amounts of recurring revenue at deal level. However now I am struggling to differentiate the two amounts when both subscriptions are scheduled. As my two fields for ARR and Forecast ARR are roll up fields that take the ARR from Active and Scheduled Subscription Status'. There are times where we close deals and the subscriptions is in the future so both subscriptions are scheduled therefore nothing shows in ARR and both in Forecast ARR but my boss wants the first one to always be active from closing. Apologies I know this is stupidly complex but if anyone has any idea I would love to take any advice 🙂