x

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

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!

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.”

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

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

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!

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

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)**

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**

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!

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.”

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

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

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!

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

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)**

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**

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

X

Oops! Something went wrong while submitting the form.