How to Create Commission Reports in HubSpot Based on Lifecycle Stage Conversion Rates?

[Ye_Sithu]

Hi Community,

I'm trying to figure out how to create commission reports in HubSpot based on the Lifecycle Stage conversion rates of contacts assigned to each insurance agent (sales rep).

Here’s our commission structure (sample figures for reference):

• Each agent is assigned 100 contacts per month.

• The total commission amount is $500, distributed equally across 5 lifecycle stages:

  • Lead

  • MQL

  • SQL

  • Customer

  • Evangelist

• So, each stage is worth $100 if there's 100% conversion at that stage.

For example:

If an agent has the following conversion rates for the month:

• Lead – 100% → $100

• MQL – 60% → $60

• SQL – 35% → $35

• Customer – 20% → $20

• Evangelist – 5% → $5

Then their total commission would be $220.

What we need:
We want to automatically generate a report in HubSpot (agent-wise or team-wise) showing:

• How many contacts were assigned

• Conversion % at each lifecycle stage

• Commission earned at each stage

• Total commission earned

These reports are primarily for tracking the performance and activities of our insurance agents.

Has anyone built a report or automation for something similar in HubSpot?
Would love to hear suggestions on how to approach this—whether it’s through custom reports, workflows, calculated properties.

Thanks in advance!

[RubenBurdin · Accepted solution]

This situation requires multiple elements to be present before even attempting to build the necessary reports.

Assuming that when you mention conversion rates, such as Lead 100%, you mean Subscriber to Lead at 100%, you need to start by making sure that your customer journey is ideally automated and configured in such a way that each lifecycle stage is tracked at both the contact and the company level.

To do this, you need to set up lifecycle stage definitions and automation criteria, set up segmentation lists (i.e., Lead to MQL, MQL to SQL, etc.), and then automate these lifecycle stage progressions via workflows, making sure a contact that is supposed to be a customer goes through all the previous lifecycle stages before being set as customer.  This will ensure that you can build funnel reports that don't have contacts or companies skipping stages, and thus, you can measure the conversion rates between these stages. 

You need to make sure that agents own their respective contacts and companies to make reporting easier. You can filter funnel reports by users, if needed. You will probably need to have at least a Pro-tier hub in order to have access to custom reports, inside which you can build formulas.

To create custom user properties (to calculate commission at the user level), you need Sales Hub Enterprise. Assuming you don't have access to this, you can use other data objects, such as Deals or Tickets, to map the agent commission and reports based on it. You can also create custom properties at the contact and company level for each agent, but that might become cumbersome.

If you don't have access to most of the tools mentioned above, you can also try to export data into a third-party system like Google Sheets, Airtable, or even a database (data warehouse) and do the commission calculations there. We have built Stacksync.com for this kind of use case (CRM to data warehouse), so if you need help with this, drop me a message.

Overall, it's not an easy feat what you're trying to achieve, but I hope you make it work. Good luck!

[h-recker · Accepted solution]

This is a really thoughtful breakdown. Stacksync is a solid solution for enterprise teams centralizing CRM data into a warehouse. Definitely a strong option if you're building long-term infrastructure across multiple systems.

That said, if your goal is more targeted like this use case, you likely don’t need something that complex or enterprise-tier.

A simpler, more flexible option for SMBs is using Coefficient and you can actually get this setup very quickly without the need to build anything extra in HubSpot.

Here's how you'll handle this with Coefficient (it's certified on HubSpot's marketplace here).

• Pull your contact data from HubSpot into Google Sheets or Excel, including fields like Lifecycle Stage, Contact Owner, and Create Date.

• Use spreadsheet logic to calculate conversion percentages and commissions per agent, just like your example: $100 per stage, scaled by % conversion.

• Build charts in Sheets to visualize commission per rep, stage-by-stage performance, and total payouts.

• Then here’s the cool part: you can embed those same live Google Sheets charts directly inside your HubSpot dashboard.

• With Coefficient’s live data sync, those charts will stay updated in real-time in both Sheets and your HubSpot dashboard without any duplicate effort.

This gives you all the power of custom reporting without needing HubSpot Enterprise or maintaining a full data warehouse.

Here's a quick video that shows how the chart embed works step-by-step. Let me know if you’d like a sample commission tracker sheet too, we’ve helped a bunch of teams set this up in minutes.

[RubenBurdin · Accepted solution]

This situation requires multiple elements to be present before even attempting to build the necessary reports.

Assuming that when you mention conversion rates, such as Lead 100%, you mean Subscriber to Lead at 100%, you need to start by making sure that your customer journey is ideally automated and configured in such a way that each lifecycle stage is tracked at both the contact and the company level.

To do this, you need to set up lifecycle stage definitions and automation criteria, set up segmentation lists (i.e., Lead to MQL, MQL to SQL, etc.), and then automate these lifecycle stage progressions via workflows, making sure a contact that is supposed to be a customer goes through all the previous lifecycle stages before being set as customer.  This will ensure that you can build funnel reports that don't have contacts or companies skipping stages, and thus, you can measure the conversion rates between these stages. 

You need to make sure that agents own their respective contacts and companies to make reporting easier. You can filter funnel reports by users, if needed. You will probably need to have at least a Pro-tier hub in order to have access to custom reports, inside which you can build formulas.

To create custom user properties (to calculate commission at the user level), you need Sales Hub Enterprise. Assuming you don't have access to this, you can use other data objects, such as Deals or Tickets, to map the agent commission and reports based on it. You can also create custom properties at the contact and company level for each agent, but that might become cumbersome.

If you don't have access to most of the tools mentioned above, you can also try to export data into a third-party system like Google Sheets, Airtable, or even a database (data warehouse) and do the commission calculations there. We have built Stacksync.com for this kind of use case (CRM to data warehouse), so if you need help with this, drop me a message.

Overall, it's not an easy feat what you're trying to achieve, but I hope you make it work. Good luck!