Calculating NRR

[timo1]

Hey team, 

I figured out how to dynamically track Net Revenue Retention / team members as a percentage via custom code. 

The only problem is that to get it accurate at a company level, I need to divide the sum of all NRRs by the total amount of deals in our company CSM's portfolios. 

Is there a way to create a dynamic property that adds up the total number of deals and can I store this as a value under each deal? Or store somewhere in hubspot that can be pulled by custom code?  

[Anonymous]

To achieve this in HubSpot, you can follow these steps to track and store the total number of deals dynamically:

1. Create a Custom Property for Deals:

   • Create a custom property for deals in HubSpot where you can store the deal count. This property will be updated programmatically.

2. Use HubSpot Workflows or API:

   • Workflows: Set up a workflow to automatically update the custom property with the total number of deals. You might need to use custom code actions in workflows or integrate with an external tool that can calculate and update this property.
   • API: Use HubSpot's API to programmatically calculate and update the deal count. You can create a script that queries all deals and calculates the total, then updates the custom property.

3. Store and Retrieve Data:

   • Storing: You can store the total number of deals in the custom property you created.
   • Retrieving: Your custom code can then pull this property value to perform calculations for NRR.

4. Use Custom Code Actions:

   • In HubSpot workflows, you can use custom code actions (via HubSpot’s Operations Hub) to write custom scripts that interact with your data and perform the necessary calculations.

5. Reports and Dashboards:

   • If you need to visualize or analyze the data, consider using HubSpot’s reporting tools or integrate with external BI tools where you can pull in this custom data.

By setting up a custom property and using workflows or the API to update it, you can dynamically track the total number of deals and use this data in your NRR calculations.

[HHana]

I’m currently using HubSpot to calculate NRR and came across a method to track the total number of deals dynamically. Does anyone have experience using custom properties for this? I’m considering using it for my tool, Calculadora Alicia , but I’m curious if there are any better alternatives or tips for optimizing this process.

[HHana]

Alicia calculadora helped me a lot.

[HPeake]

Hi @timo1 any chance you could give a quick recap of how you calculated NRR / team member with custom code? 

[timo1]

@HPeake It took some time but we got it working perfectly 👍

1. It starts with two inputs: the 'amount' of the deal and the 'individual' who is responsible for renewing it (normally the deal owner but it depends on your companies layout).

2. We created a data set of individuals, each with a name, ID, and a 'Target' value representing their retention goal for the year.

3. When a deal is enrolled, the code checks through the list of individuals. It matches the deal owner (we used company owner which made it way more difficult) to the 'individual' in the dataset. When it finds a match, it takes that individual's target value and stops searching.

4. If a match is found, it calculates how much the deal contributes to that individual's goal by dividing the deal's amount by the target value. This calculation is stored in the 'result' variable.

5. Once the code has calculated the result, I copy the value into a propety called NRR Decimal on the deal. e.g if the deal was worth 100 and my target was 10,000, this deal would count for 0.01 of my target or 1%. 

After the codes run I craete a report adding up the sum of the NRR propertys and break it down by indivudal. It creates a report like the one below. We've sinced moved to using API's and Custom Objects but I set this up for Indivudals, states, countries and teams and it works a treat! 

If you need a hand with setting something similiar up for your business I'm happy to help.

Heres our code:

exports.main = async (event, callback) => {
  const dealAmount = event.inputFields['amount'];
  const dealOwner_ID = event.inputFields['deal_owner_ID'];

  const teamData = [
    { Name: "Person A", ID: "1", Target: "10000" },
    { Name: "Person B", ID: "2", Target: "8000" },
    { Name: "Person C", ID: "3", Target: "7000" },
    { Name: "Person D", ID: "4", Target: "9000" }
  ];

  let individualTarget = 0;

  let targetID = dealOwner_ID;

  for (const row of teamData) {
    if (row.ID === targetID) {
      console.log(person.Name + " Matched");
      individualTarget = parseFloat(person.Target);
      break;
    }
  }

  if (individualTarget !== 0) {
    console.log(individualTarget);
    const nrr = dealAmount / individualTarget;
    console.log(contribution);
    callback({
      outputFields: {
        nrrDecimal: nrr
      }
    });
    return contribution;
  } else {
    console.log("No person with a target found");
  }
}

[Mike_Eastwood]

Nice work @timo1 !

[Mike_Eastwood · Accepted solution]

Hi @timo1 

There's a default Company Property called Number of Associated Deals (num_associated_deals) that you can add to your calculation. 

Have fun

Mike

Here to learn more about HubSpot and share my HubSpot Knowledge. I'm the founder of Webalite a HubSpot Partner Agency based in Wellington, New Zealand and the founder of Portal-iQ the world's first automated HubSpot Portal Audit that helps you work smarter with HubSpot.