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?
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.
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.
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?
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.