HubSpot: create custom funnel reports

Let’s build a simple sales funnel report for each month that tracks these metrics:
•  Number of new deals
•  Number of contacts
•  Number of deals won
•  Number of deals lost
•  Close, win, and loss rates

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

The first step is to import your deals data from HubSpot into Actiondesk. Follow these steps:
 1.  Create a sheet called “Hubspot deals”
 2.  Click the “Import” button
 3.  In the dropdown menu, select your HubSpot account
 4.  In the “Table to Import” dropdown menu, select “Deals”
 5.  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
 6.  Click “Import”

Note that if you cannot yet connect your HubSpot account, you can follow this tutorial by using the dataset in your Actiondesk onboarding database titled “hubspot_deals.”

Step 2: Import your contacts

Follow these steps to import your contacts from HubSpot:
 1.  Create a sheet called “Hubspot contacts”
 2.  Import your contacts using the same steps you used to import your deals
 3.  Choose the columns that you’d like to use, keeping in mind that you’ll at least need to use the “Contact_ID” and “Create_Date” columns

Step 3: Build the layout of your report

Build the layout of your report by following these steps:
 1.  Create a new sheet called "Hubspot custom funnel report"
 2.  In cells B4 to B6, enter the months for the data you’d like to see (e.g. 2021_2 ; 2021_1 ; 2020_12 ; 2020_11)
 3.  Input the metrics you want to track in cells C3 to F3:
            • # of deals
            • # of contacts
            • # of won deals
            • # of lost deals
            • Close rate
            • Win rate
            • Loss rate

Step 4: 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(#'Create Date')&"_"&MONTH(#'Create Date')

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 5: Calculate the number of deals created per month

To calculate the number of deals per month:
 1.  Navigate to the sheet titled “Hubspot custom funnel report”
 2.  In cell C4, enter the following formula:
            =COUNTIF('Hubspot deals'!#Month,B4)
 3.  Copy and paste the formula or drag your cursor downward to populate the entire column

Step 6: Calculate deals won and lost

To determine the number of deals won and the number of deals lost, follow these steps:
 1.  In cell E4, enter the following formula:
           =COUNTIFS('Hubspot deals'!#'Deal Stage',"Closed won",'Hubspot deals'!#Month,B4)
 2.  In cell F4, enter the following formula:
            =COUNTIFS('Hubspot deals'!#'Deal Stage',"Closed lost",'Hubspot deals'!#Month,B4)

Step 7: Calculate the win rate, loss rate, and close rate

To obtain the rate of wins, losses, and closed deals, follow these steps:
 1.  To determine the win rate, navigate to cell H4 and enter the following formula:
            =E4/C4
 2.  To determine the loss rate, navigate to cell I4 and enter the following formula:
           =F4/C4
 3.  To determine the close rate, navigate to cell G4 and enter the following formula:
          =H4+I4

Step 8: Calculate the number of contacts

You know the drill:
 1.  In “Hubspot contacts,” create a calculated column to obtain the month
 2.  In “Hubspot custom funnel report,” write a countif to calculate the number of contacts per month