💬 RevOps Discussions

drafeedie
Contributor

Projected ARR Calculations

SOLVE

I am trying to created a projected ARR number for each of our customers. We bill out customers a variable fee each month via Quickbooks and use the integraiton to sync invoices to Hubspot.

 

Currently we have a roll up custom property that takes the average amount billed to get an MRR number. We then have a calculated field that takes the MRR x 12 to give us the projected ARR.

 

However, the problem is that the roll up MRR property looks back at ALL invoices to get the average. This skews numbers unfavorably for customers that have material growth or reductions in seat licenses. 

 

We are looking for a way to limit the MRR Rollup calculation to ONLY include the last x number of months in the average calculation so that we bias the more recent invoices and disregard older ones. Anybody have experience doing this, or a creative warkaround to set the average MRR property? 

 

 

0 Upvotes
1 Accepted solution
GSriram
Solution
Participant

Projected ARR Calculations

SOLVE

Hi @BérangèreL ,

 

Thanks for following up, and happy to clarify!

 

Yes, the integration I’m referring to is indeed the "Google Sheets Connector" you've mentioned, listed on the HubSpot Marketplace. Here’s how @drafeedie  can use it to solve this specific use case:

1. Pull Recent Invoice Data into Google Sheets:

The connector has a data preview option in which @drafeedie can apply filters and sync the most recent invoices (for example, last 3-6 months) from HubSpot into Google Sheets. The key here is filtering invoices to focus on the last “X” months only.

2. Calculate the Average MRR using a formula:
=AVERAGEIF(DateRange, ">=TODAY()-90", InvoiceAmount) 
- Replace DateRange with the column containing invoice dates
- Replace InvoiceAmount with the column containing the billed amounts

- Adjust 90 for a different number of days

3. Send the Accurate MRR Back to HubSpot:
Once the recent MRR average is calculated in Sheets, the connector can sync this value back to the custom MRR property in HubSpot. From there, the calculated ARR property (MRR x 12) will update automatically.

Let me know if you’d like a more detailed step-by-step breakdown! I hope this helps.

 

Best,

Guru

View solution in original post

5 Replies 5
GSriram
Participant

Projected ARR Calculations

SOLVE

Hey @drafeedie 

My solution to solve this problem would be to use a tool like Superjoin. It offers a 2-way sync between HubSpot and Google Sheets. I’ve been using it for a similar usecase as yours and it’s been working really well for me. Would recommend it strongly for your workflow too.

You could bring in the Average Amount Billed data to Google Sheets using Superjoin so that you can do the MRR Rollup Calculation accurately on Sheets and send it right back to HubSpot.


Hope this helps! Have a great day :))

Best,
Guru

BérangèreL
Community Manager
Community Manager

Projected ARR Calculations

SOLVE

Hi @GSriram,

Thank you for your contribution in the Community and for your help!

I can see that this is the "Google Sheets Connector" integration from the HubSpot Marketplace. Please let me know if that's not the right one.

Could you please share a concrete example of how @drafeedie could use this integration to solve for this specific use case?
Which formula are you using for the calculation, please?

Thank you and have a great day!

Best,
Bérangère


Saviez-vous que la Communauté est disponible en Français ?
Rejoignez les discussions francophones en changeant votre langue dans les paramètres !

Did you know that the Community is available in other languages?
Join regional conversations by changing your language settings!


0 Upvotes
GSriram
Solution
Participant

Projected ARR Calculations

SOLVE

Hi @BérangèreL ,

 

Thanks for following up, and happy to clarify!

 

Yes, the integration I’m referring to is indeed the "Google Sheets Connector" you've mentioned, listed on the HubSpot Marketplace. Here’s how @drafeedie  can use it to solve this specific use case:

1. Pull Recent Invoice Data into Google Sheets:

The connector has a data preview option in which @drafeedie can apply filters and sync the most recent invoices (for example, last 3-6 months) from HubSpot into Google Sheets. The key here is filtering invoices to focus on the last “X” months only.

2. Calculate the Average MRR using a formula:
=AVERAGEIF(DateRange, ">=TODAY()-90", InvoiceAmount) 
- Replace DateRange with the column containing invoice dates
- Replace InvoiceAmount with the column containing the billed amounts

- Adjust 90 for a different number of days

3. Send the Accurate MRR Back to HubSpot:
Once the recent MRR average is calculated in Sheets, the connector can sync this value back to the custom MRR property in HubSpot. From there, the calculated ARR property (MRR x 12) will update automatically.

Let me know if you’d like a more detailed step-by-step breakdown! I hope this helps.

 

Best,

Guru

BérangèreL
Community Manager
Community Manager

Projected ARR Calculations

SOLVE

Hi @GSriram, thank you so much for the details about how to use the integration for this specific use case! ❤️

Would that help @drafeedie? Is that what you were looking for?

Thanks,
Bérangère


Saviez-vous que la Communauté est disponible en Français ?
Rejoignez les discussions francophones en changeant votre langue dans les paramètres !

Did you know that the Community is available in other languages?
Join regional conversations by changing your language settings!


BérangèreL
Community Manager
Community Manager

Projected ARR Calculations

SOLVE

Hi @drafeedie, I hope that you are well!

I'd like to share the solution from @danmoyle on this similar post "Custom Forecast ARR Field and Subscriptions" that might guide you!

Also, I'd love to put you in touch with some of our Top Experts: Hi @HubSpot_Corey, @HFisher7 and @louischausse do you have other suggestions to help @drafeedie, please?

Have a lovely day and thanks so much!

Best,
Bérangère


Saviez-vous que la Communauté est disponible en Français ?
Rejoignez les discussions francophones en changeant votre langue dans les paramètres !

Did you know that the Community is available in other languages?
Join regional conversations by changing your language settings!