Actiondesk Tutorial: Calculate the Number of HubSpot Deals by Stage by Sales Rep

Ready to get started? Here’s how to build a report in Actiondesk that calculates the number of deals per stage for each of your sales reps:

Step 1: Import deals

1. Create a new sheet called "HubSpot deals"
2. Click the “Import” button
3. In the data source dropdown menu, select your HubSpot account
4. In the "Table to import" dropdown menu, select "Deals"
5. Choose the columns you want to use: you'll need at least the "dealId," "Create Date," "Deal owner," and "Deal Stage" columns
   

Step 2: Import owners

To get the deal owners’ names, you'll need to import the owners table. To do so:

1. Create a new sheet called "HubSpot owners"
2. Click the “Import” button
3. In the data source dropdown menu, select your HubSpot account
4. In the "Table to import" dropdown menu, select "Owners"
5. Import the following columns: "ownerId," "firstName," and "lastName"
   

Step 3: Add a calculated column for owners’ full names

To get owners’ full names (instead of separate first names and last names):

1. Place your cursor on the cell to the right of your table headers
2. Title the column "Full Name"
3. In the first cell of the column, add the formula:

             =#firstName&" "&#lastName

Step 4: Look up owners’ full names

Now that you have your owners’ full names, you need to get them into the deals table. Navigate to your "HubSpot deals" sheet, then:

1. Add a column called "Full Name" (you know the drill)
2. Add the formula:

             =LOOKUP(#'Deal owner', 'Hubspot owners'!#ownerId, 'Hubspot owners'!#'Full Name')

Step 5: Build the layout of your dashboard

1. Create a new sheet called "Number of deals by deal stage by sales rep"
2. 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"
3. In your "Number of deals by deal stage by sales rep" sheet, paste the unique values in cell B3
4. In cells C2 to I2, write your stages, for example:

• Appointment scheduled
• Closed lost
• Closed won
• Contract sent
• Decision maker bought-in
• Presentation scheduled
• Qualified to buy
  

Step 6: Write your countifs formula

     1. In cell C3, write the following formula:

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

     2. Copy/paste or drag your formula downward and to the right to fill the whole table