In this article, we explain how to get the total number of deals from HubSpot, with their associated stage name and contact name. 3 examples are explored.
- All Deals with their related Contacts and their deal stage status
- Recent Deals with their recent Contact
- Stage names with their related pipeline, deal names and deal amount per stage
In this section, we visualize the connection between contacts, deals and stage in a table.
Queries need to be used for this part:
- All Contacts
- All Deals
Note1: properties should be mentioned in the query if you are looking for specific relation between deal and other properties. For example, if you would like to have deal Stage, deal amount, and deal name then they should be named in the query one by one.
Note2: the maximum number of deals in deal query is 250/page. In Klipfolio, we are able to get a maximum of 10 pages of data which is an equivalent of 2500 deal with 1 query.
- All Stages
In HubSpot, Property names are different in HubSpot UI and HubSpot API. In order to pass the right name in the query, you would need to use the HubSpot API names. You can find the list of all deal property or contact property names in your HubSpot account, using below queries:
Below shows a sample of the output data returned by the queries for contact, deal and stage:
Sales Pipeline Stages Query result can be something like below
Now, for each column you use XPATH formulas as outlined below:
- Contact Name:
In the above table we just used first name. However, if you want to make it look better, you can always concat first and last name of each contact. The first and last name nodes are selected from Contact datasource.
- Associated ID:
The IDs for each Contact is represented by “vid”
- Related Deal Name:
To get the deals related to each contact, we need to connect deal datasource and contact datasource via a common column. both deal and contact results have the contact id. Contact id in Deal Datasource is shown by “associatedVids”.
Note: Each deal might have several contacts associated with. In this section, we are assigning only the first contact to each deal.
Then you can use LOOKUP () function to connect these 2 datasource together.
- Deal Amount:
- Stage name:
In Deal query, we presented dealstage property in the query, that brings all the deal stage associated with each deal. If this is enough, you would need to use below LOOKUP () function
And if you want to go one step further and get the real stage name ( in the query, shows as label) then you can use below formula with double LOOKUP()
In this section, we are working to get the Recent Deals Corresponds to their Recent Contacts
This example can be considered as a sub-section of example1, when you want to get the recent deals and recent associated contacts.
Queries need to be used for this part:
When we use Recent deals API queries, we don’t need to mention the property names in the query anymore since by default all the properties already included in the result.
Note: This endpoint will only return records created in the last 30 days, or the 10k most recently created records.
For Recent Contact API, we still need to mention the required properties in the query as below:
Note: This endpoint returns contacts based on when they were created. If you need to check for recently modified records, use this endpoint.
A sample of output data for recent contacts, and recent deals shown below:
Recent Contacts output data:
- Recent Contact name:
This is coming from Recent Contacts Datasource.
- Deal Name:
@vid is from recent contact datasource, and associatedVids and dealname coming from recent deals datasource.
- Deal Stage:
@vid is from recent contact datasource, and associatedVids and dealstage coming from recent deals datasource.
Association VID in Recent Deals Query:
Please note that when you are pulling associatedVids from Recent Deals vs All Deals, the xpath is different. In Recent Deals, you can simply select the associatedVids node from the json data. But in All Deals as it is mentioned in option 1, you need to manipulate the xpath as below:
- associatedVids from Recent Deals:
- associatedVids from All Deals:
Stage names with their related pipeline, number of deals per stage and deal amount
The queries for Stage and Deal can be used from Queries we introduced in Example 1and therefore, the output data is similar to what we shown in example 1 as well.
Let’s make below table:
- StageID from stage datasource:
- Stage Name from stage datasource
The structure sometimes is different depending on how each person sets their HubSpot account.
- Pipeline from stage datasource:
- Deal Stage- Count
Hope this article points you to the right direction on how to visualize some of your HubSpot data.