Actiondesk Tutorial: Create a Fully Automated Sales Rep Dashboard

Ready to get started? Let’s build a simple dashboard that will enable you to:

• Change the name of the sales representative
• See the value of deals lost and won per month for the given sales representative
  

We’ll keep it simple for the sake of the example, but as an Actiondesk user, you should feel empowered to incorporate any metrics you like into this dashboard!

Step 1: Import your deals

Import your deals into the spreadsheet by following these steps:

1. Click the “Import” button
2. In the dropdown menu, select your HubSpot account
3. In the “Table to Import” dropdown menu, select “Deals”
4. Choose the columns that you’d like to use, keeping in mind that you’ll at least need to use the “Deal Id,” “Created At,” “ownerId,” and “Deal Stage” columns
   

Step 2: Get owners’ names

At this point, you should see owner IDs but not their names. To get owners’ names, we’ll need to do the following:

1. Import the table titled “Owners” using, at minimum, the “ownerId,” “firstName,” and “lastName” columns.
2. Add a calculated column with the formula:

             =#firstName&" "&#lastName

Note that when you enter a formula into the first cell of the column, the rest of the cells in the column populate automatically. And they update whenever a change is made in your original data source!

Step 3: Add a “Month” column

Next, you need to add a calculated column to obtain the respective month of each deal. Follow these steps:

1. In the sheet “Hubspot deals,” place your cursor on the column to the right of the existing table headers
2. Name your new column “Month”
3. Enter this formula:

             =YEAR(#'Created At')&"_"&MONTH(#'Created At')

Step 4: Look up the sales representative’s full name

Now that you have each owner’s full name, you can look it up to import it into the “Deals” table by following these steps:

1. From the “Deals” table, add a column and name it “Full Name”
2. Enter this formula:

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

3. Either copy and paste the formula into the cells below, or drag your cursor down the full length of the column.
   

Step 5: Build the layout of your dashboard

Build the layout of your dashboard with these steps:

     1. Create a new sheet called “Sales rep dashboard”

     2. Copy the layout of this screenshot:

     3. If you’re using your own HubSpot data, enter the name of an actual sales representative in cell B3

Step 6: Calculate the value of deals lost and won

To calculate the value of deals both lost and won by the given sales representative, follow these steps:

1. In cell C6, enter this formula to calculate the amount lost:

             =SUMIFS('Hubspot deals'!#Amount,'Hubspot deals'!#'Deal Stage',"Closed lost",'Hubspot deals'!#Month,B6,'Hubspot deals'!#'Full name',$B$3)

2. Copy and paste the formula or drag your cursor downward to populate the entire column
3. Repeat the process to calculate the amount won, using this formula:

             =SUMIFS('Hubspot deals'!#Amount,'Hubspot deals'!#'Deal Stage',"Closed won",'Hubspot deals'!#Month,B6,'Hubspot deals'!#'Full name',$B$3)