Hubspot: calculate the number of deals by stage by sales rep

Build reports to understand the performance of sales representative. Calculate the number of total deals by sales representative. ‍

The process

Try It for Yourself

The first step is to import your deals in a sheet

  • Create a new sheet (+ bottom near the tabs)
  • Call it "Hubspot deals"
  • Click on the import button
  • In the data source dropdown, choose your Hubspot account
  • In the "Table to import" dropdown, choose "Deals"
  • Choose the columns that you want to use: You'll need at least the "dealId", "Create Date", "Deal owner" and "Deal Stage" columns

Import Owners

As you can see, we have the id of the owner, and not their name. We'd like to get their name. To do that, we'll have to import the Owners table and lookup the name

  • Create a new sheet called "Hubspot owners"
  • Then, import the table Owners with the columns: "ownerId", "firstName", "lastName"

Adding a calculated column to get the full name

We'd like to get the full name instead of only the First Name and the Last name.

  • Place your cursor on the cell to the right of your table headers
  • Write the title of your column ("Full Name") and press enter
  • Then, in the first cell of the column, add the formula =#firstName&" "&#lastName

Looking up the Full Name

Great, now that we have the full Name, we're going to look it up to get it in the Deals table.

  • Go to the sheet "Hubspot deals"
  • Add a column called "Full Name" (you know the drill)
  • Write then the formula: =LOOKUP(#'Deal owner', 'Hubspot owners'!#ownerId, 'Hubspot owners'!#'Full Name')

Layout of the dashboard

  • Create a new sheet called "Number of deals by deal stage by sales rep"
  • Go back to "Hubspot deals" and copy the unique values of your "Full Name" column by right clicking on the "Full Name" column and choosing "Copy unique values"
  • In "Number of deals by deal stage by sales rep", paste the uniques values in B3
  • Then, horizontally from C2 to I2, write the various states you have: for example:
  • Appointment scheduled
  • Closed lost
  • Closed won
  • Contract sent
  • Decision maker bought-in
  • Presentation scheduled
  • Qualified to buy

Write your countifs formula

Now you can write your countifs formula:

=COUNTIFS('Hubspot deals'!#'Full Name',$B3,'Hubspot deals'!#'Deal Stage',C$2)


Then, copy paste or drag your formula downwards and to the right to fill the whole table.


Conclusion

Awesome, congrats on going through this use case. It showed some of the most important features of Actiondesk.

The first step is to import your deals in a sheet

  • Create a new sheet (+ bottom near the tabs)
  • Call it "Hubspot deals"
  • Click on the import button
  • In the data source dropdown, choose your Hubspot account
  • In the "Table to import" dropdown, choose "Deals"
  • Choose the columns that you want to use: You'll need at least the "dealId", "Create Date", "Deal owner" and "Deal Stage" columns

Import Owners

As you can see, we have the id of the owner, and not their name. We'd like to get their name. To do that, we'll have to import the Owners table and lookup the name

  • Create a new sheet called "Hubspot owners"
  • Then, import the table Owners with the columns: "ownerId", "firstName", "lastName"

Adding a calculated column to get the full name

We'd like to get the full name instead of only the First Name and the Last name.

  • Place your cursor on the cell to the right of your table headers
  • Write the title of your column ("Full Name") and press enter
  • Then, in the first cell of the column, add the formula =#firstName&" "&#lastName

Looking up the Full Name

Great, now that we have the full Name, we're going to look it up to get it in the Deals table.

  • Go to the sheet "Hubspot deals"
  • Add a column called "Full Name" (you know the drill)
  • Write then the formula: =LOOKUP(#'Deal owner', 'Hubspot owners'!#ownerId, 'Hubspot owners'!#'Full Name')

Layout of the dashboard

  • Create a new sheet called "Number of deals by deal stage by sales rep"
  • Go back to "Hubspot deals" and copy the unique values of your "Full Name" column by right clicking on the "Full Name" column and choosing "Copy unique values"
  • In "Number of deals by deal stage by sales rep", paste the uniques values in B3
  • Then, horizontally from C2 to I2, write the various states you have: for example:
  • Appointment scheduled
  • Closed lost
  • Closed won
  • Contract sent
  • Decision maker bought-in
  • Presentation scheduled
  • Qualified to buy

Write your countifs formula

Now you can write your countifs formula:

=COUNTIFS('Hubspot deals'!#'Full Name',$B3,'Hubspot deals'!#'Deal Stage',C$2)


Then, copy paste or drag your formula downwards and to the right to fill the whole table.


Conclusion

Awesome, congrats on going through this use case. It showed some of the most important features of Actiondesk.

Read more
Create My First Report
see all HubSpot use cases
The Advantages

Key Benefits

Eliminate the need for expensive third-party connectors and laborious copy-pasting.

Seamlessly import, organize, filter, and manipulate your HubSpot data. Analyze it alongside data from other sources, and build custom HubSpot dashboards and reports.

View and organize your HubSpot data within a familiar spreadsheet interface.

Seamlessly import, organize, filter, and manipulate your HubSpot data. Analyze it alongside data from other sources, and build custom HubSpot dashboards and reports.

Feel confident in knowing that your data is always accurate and up-to-date.

Thanks to real-time cloud-based updates, any changes to your data made from within HubSpot are automatically reflected in your Actiondesk spreadsheet.

Save time to analyze your data and make better decisions for your business
Create My First Report
Learn More

Our Use Cases

Create My First Report
X
Please wait...
Oops! Something went wrong while submitting the form.