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?  

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