HubSpot: create custom funnel reports

For each month, calculate the # of contacts, deals, closed deals, lost deals, lost and win rate.

Forget third-party integration tools and complicated coding. Use Actiondesk to build custom HubSpot reports that illuminate what’s happening at every stage in your sales funnel. Simply import your HubSpot sales data into Actiondesk’s simple spreadsheet interface and get started. For each month, calculate the number of contacts, deals, closed deals, lost deals, plus the overall rate of deals lost vs. won.

The process

Try It for Yourself

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

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

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.