20221120 revenue forecasting

How to Forecast Monthly Recurring Revenue (MRR) with HubSpot Operations Hub Professional

My step-by-step guide on how to build a MRR forecasting solution on your own HubSpot portal without an Enterprise subscription.

Originally built in Oct 2022.
Working as of 6 Dec 2022.
Last updated on 6 Dec 2022

Table of Contents

Quick Note About This Guide

This guide assumes that you are a seasoned HubSpot Admin who knows your way around the platform fairly well. Ideally, you should have experience in the following:

  • Creating complex workflows with multiple branches
  • Setting up custom properties, including calculated fields
  • Setting up sales pipelines and their automation settings
  • Creating custom reports and dashboards
  • Working with HubSpot APIs (using low-code platforms like Zapier and Make counts too)
  • Some JavaScript coding

You can still try to follow the guide even if you aren't as experienced, but you may need to use HubSpot's knowledge base or other resources for additional help.

INTRODUCTION

Why use Ops Hub Pro for MRR Forecasting?

221205 MRR Forecasting Video Excerpt

HubSpot's Monthly Recurring Revenue (MRR) tools are only available on Enterprise plans. But even if you have access to this, these MRR fields cannot be tied to forecasting reports on your dashboard or HubSpot's sales goals tool. This means no oversight on how you're actually progressing to monthly revenue goals and you cannot set up notifications on when goals are started, missed or hit.

A workaround to enable forecasting would be to create a separate deal for each monthly payment. However, it would be unrealistic to expect a sales rep to create 12 separate deals for an annual project, for example. And having so many records could mean a greater chance of making mistakes in activity logs and data entry fields.

The Solution

Using "Custom Code" actions available in Operations Hub Pro workflows, we can automate the creation of these forecasting deals in a read-only pipeline that will specifically be used for MRR forecasting and reporting. We will maintain the original pipeline as it is, so sales teams do not experience any major changes to their process and they can focus on logging activity on only one deal.

Maintaining the original pipeline also means that you can still report, forecast and set revenue goals based on Total Contract Value (TCV) of your MRR deals, and maintain any activity-based automation among many other things.


Return to top

THE DEETS

How this MRR Forecasting Solution Works

20221122 Oxygen Forecasting Pipeline Documentation-1

MRR FORECASTING FLOWCHART & ENTITY RELATIONSHIP DIAGRAM (ERD)

Get a copy of the full process flowchart and ERD, which details the purpose of each custom property and workflow that we will be building as part of our forecasting solution.

DOWNLOAD PDF

Step 1: Creating Your Triggers & Custom Properties

In total, we will need to create a total of 14 custom deal properties to make this solution work. This is assuming that you will add 3 one-off payments as we did in our own pipeline. Feel free to add/remove one-off payment fields based on your use case. 

Below is a list of all the custom properties you'll need to set up. In each tab, you'll find the exact field type you need to select as well as a suggested description to help your team understand what each property is for.

To keep things organized, I highly recommend creating a new property group for all of these and storing them together like so:

Forecasting Pipeline Deal Property Group

 

Forecasting Triggers

MRR Amount

Field Type: Number
Format:
Currency
Description:
The total amount of monthly recurring revenue for the deal. Any changes to this will trigger all forecasting workflows.

MRR Start Date

Field Type: Date picker
Description:
The first date when the "MRR Amount" for the deal will be collected. Filling/updating this will trigger all forecasting workflows.

MRR End Date

Field Type: Date picker
Description:
The last date when the "MRR Amount" for the deal will be collected. Filling/updating this will trigger all forecasting workflows.

One-Off Payment 1 Amount

Field Type: Number
Format:
Currency
Description:
The first one-off payment amount we will invoice for this project. Usually the upfront payment or the payment for hitting the first milestone. Filling/updating this will trigger all forecasting workflows.

One-Off Payment 1 Date

Field Type: Date picker
Description:
The invoice date for the "One-Off Payment 1 Amount". Filling/updating this will trigger all forecasting workflows.

One-Off Payment 2 Amount

Field Type: Number
Format:
Currency
Description:
The second one-off payment amount we will invoice for this project. Filling/updating this will trigger all forecasting workflows.

One-Off Payment 2 Date

Field Type: Date picker
Description:
The invoice date for the "One-Off Payment 2 Amount". Filling/updating this will trigger all forecasting workflows.

One-Off Payment 3 Amount

Field Type: Number
Format:
Currency
Description:
The third one-off payment amount we will invoice for this project. Filling/updating this will trigger all forecasting workflows.

One-Off Payment 3 Date

Field Type: Date picker
Description:
The invoice date for the "One-Off Payment 3 Amount". Filling/updating this will trigger all forecasting workflows.

Custom Properties

Parent Deal ID

Field type: Single-line text
Description: Record ID of the original deal that triggered the creation of this deal. This is filled automatically by a workflow. 

Forecast Pipeline Matching Stage ID

Field type: Single-line text
Description: Stage ID for the matching stage in the Forecast Pipeline. Informs the forecast deal creation workflow which stage to put the forecast deals. This is filled automatically by a workflow. 

Time Between MRR Start and End

Field type: Calculated / Equation
Calculated property type: Time Between
Start Date: MRR Start Date
End Date: MRR End Date
Description: Time between "MRR Start Date" and "MRR End Date" stored in milliseconds on HubSpot's backend.

Number of MRR Invoices

Field type: Number
Format: Formatted number
Description: [DO NOT UPDATE MANUALLY] The total number of invoices that will be collected for this deal, based on "MRR Start Date" and "MRR End Date". This is automatically filled by a workflow.

MRR Month

Field type: Number
Format: Formatted number
Description: Field used only in the Forecasting Pipeline. This indicates which number out of the total number of invoices the deal refers to.

Example: Deal for the first-month MRR will have "MRR Month" as 1. Second month as 2.

Step 2: Setting Up Your Forecasting Pipeline & Supporting Workflows

Forecast Pipeline

  1. Duplicate your original pipeline, ensuring that you have the same stages and forecast probabilities set up. HubSpot, unfortunately, does not give you the ability to clone sales pipelines so you will need to do this manually.

  2. Update the "Editing Access" for each stage to "Limited to super admins". This prevents other users from editing the automated deals and potentially causing issues with reports.

Take note of the ID for each stage in this new pipeline. We will need it for workflow number 3 below.

HubSpot - Get Pipeline Stage Internal ID

Workflows

The workflows below fulfil a number of data entry tasks to help with the automatic deal creation in step 3 and keep data consistent across both the original and forecasting pipelines. Again, try to keep all of these workflows within the same folder.

If you're having trouble seeing the workflow images in the tabs below, click on the images to open them in a new window or right-click to save them to your computer. That should make it easier to zoom in and see the text.

1. Fill "Number of MRR Invoices"

Type: Deal

Description: Reformats "Time Between MRR Start and End" calculated field (stored as milliseconds in the backend) into the exact number of invoices that will be sent out for the project.

Re-enrollment: On
✅ The time Between MRR Start and End is known
✅ Time Between MRR Start and End is unknown

Workflow - Fill Number of MRR Invoices


Format Data Settings

Format Data - Fill Number of MRR Invoices

 

2. Fill in unknown forecasting amounts with 0

Type: Deal

Description: Fills all unknown forecasting amount fields with a "0". This is used to enable the automatic deal Amount calculation.

Re-enrollment: On
✅ MRR Amount is unknown
✅ One-Off Payment 1 Amount is unknown
✅ One-Off Payment 2 Amount
is unknown
✅ One-Off Payment 3 Amount
is unknown

Workflow - Fill in Unknown Forecasting Amounts with 0

 

3. Update Deal Amount + Matching Stage ID

Type: Deal

Description: Calculates the total deal amount from the forecasting amount properties and inputs it into the "Amount" field. Also adds the correct Forecast Pipeline stage ID to match the stages between the two pipelines, then enrols the deal in the "4. Create Forecast Pipeline Deals" workflow.

Re-enrollment: On
Deal stage is known
One-Off Payment 1 Amount is known
One-Off Payment 2 Amount is known
✅ One-Off Payment 3 Amount
is known
✅ One-Off Payment 1 Date 
is known
✅ One-Off Payment 2 Date
is known
✅ One-Off Payment 3 Date
is known
✅ MRR Amount
is known
✅ Number of MRR Invoices
is known

NOTE: Please update the If/then branch based on your own deal stages, and add your own matching internal IDs from the Forecasting Pipeline.


Workflow - Update Deal Amount + Matching Stage ID

 

Step 3: Setting Up the Automatic Deal Creation Workflow

On to the main event. The workflow below will create X number of deals based on the "Number of MRR Invoices" field we setup above. Below, you'll find the workflow setup as well as the settings for each of the custom code actions in it.

Once you've set up the workflow, double-check that it and the previous workflows are all working as intended. If all works well:

  • The correct number of deals should be created in the forecast pipeline.
  • Every time the original deal is updated, it will clear the pipeline of any existing deals and then create new ones with the updated information.
  • For now, the close dates of all recurring deals will be equal to the "MRR Start Date". This is not an error and will be fixed in the Step 4.

Workflow

4. Create Forecast Pipeline Deals

Type: Deal

Description: Creates forecast deals in the Forecasting Pipeline after workflows 1-3 have completed their data entry actions. Deals are manually enrolled here after completing workflow 3.

Re-enrollment: On
Only for manually enrolled deals.

Workflow - Create MRR Forecast Deals

Custom Code Actions

Batch Archive Existing Deals

Language: Node.js 16.x

Properties to include in code:
No need to select anything here.

Code:

Create MRR Deals

Language: Node.js 16.x

Properties to include in code:
All properties below are required to enable this. You can add additional fields you need to enable further filtering in your forecast reports.

  • Deal Name
  • Number of MRR Invoices
  • MRR Amount
  • MRR Start Date
  • Forecast Pipeline Matching Stage ID
  • Record ID

Code:

Create One-Off Payments

The code for each one-off payment will mostly be the same. However, you will need to update the "Properties to include" based on the respective one-off payment you're creating, and update the code with the correct property IDs.

Language: Node.js 16.x

Properties to include:
All properties below are required to enable this. You can add additional fields you need to enable further filtering in your forecast reports.

  • Deal Name
  • One-Off Payment # Amount
  • One-Off Payment # Date
  • Forecast Pipeline Matching Stage ID
  • Record ID

Code:

Step 4: Adjusting Close Dates of Recurring Deals

The "Create MRR Deals" custom code I provided above creates recurring deals with the same close date, using the value in the original deal's "MRR Start Date". To adjust this, we will use the workflow below to increment the close day by the "MRR Month" we stored in each recurring deal.

You can test this workflow by enrolling any existing recurring deals that exist in your forecasting pipeline. You know it works as intended, when the months are incremented correctly.

NOTE: If you have a developer on your team, you can technically add this functionality within the custom code itself. That would eliminate the need for this workflow. But since I'm not an actual developer, I did this instead. :P

Workflow

5. Adjusting Close Dates of Recurring Deals

Type: Deal

Description: Updates the close dates of each MRR deal based on which month they should occur.

Re-enrollment: On
MRR Month is greater than 1

Workflow - Adjust Forecast Dates
Format Data Settings

Format Data - Adjust Forecast Dates

 

Step 5: Creating Revenue Goals & Dashboard Reports for Forecasting

Now that your recurring deals and one-off payments are properly automated in your forecasting pipeline, it's now time to set up your revenue goals. You can set it up as an individual or team goal and it should work the same way as it does on the original pipeline. You should also now be able to set notifications for the status of your goals.

Forecasting Pipeline Revenue Goals

When you create a revenue report for the forecast pipeline, you can select "Show revenue goals" in the "Display options" while customizing. The revenue goal value line will adjust based on the deal owner or HubSpot filtered in the report.

Forecasting Pipeline Revenue Goals Report Option

 

ADDENDUM 1

Creating a Workflow for Archiving Parent & Forecast Deals

The "Create MRR Deals" custom code I provided above creates recurring deals with the same close date, using the value in the original deal's "MRR Start Date". To adjust this, we will use the workflow below to increment the close day by the "MRR Month" we stored in each recurring deal.

You can test this workflow by enrolling any existing recurring deals that exist in your forecasting pipeline. You know it works as intended, when the months are incremented correctly.

Workflow

And there you have it!

If you have any comments or questions, do feel free to reach out to me on LinkedIn. Happy HubSpotting!