Overview: HubSpot CRM Query Builder

In this article, we will explain elements and functionality of the Query Builder for HubSpot CRM through an example. Our goal is to filter the number of open contacts by owner, where the amount is greater than 50.

You can find Query Builder under Data manager (link to Query Builder):

add_user_popup@2x

IN THIS ARTICLE

  1. Basic Options
  2. Advanced Options
  3. Filter operators
  4. Data preview
  5. Transformations

1. Basic Options

add_user_popup@2x

A
  Custom Query Name: Write a name for your custom query in this field. You will find this name/title as a metric option in the designer after saving the query.
We chose "Amount by Deal Stage (Sales Pipeline - John)" for our query name.
B
  Metric (required) : Select a metric from your connected HubSpot CRM data source, such as number of contacts, count of deals, amount, etc. A metric can be used in combination with other dimensions or metrics, but only where valid combinations apply for that metric. 
C
  Dimension (optional)  The  dimension parameter breaks down metrics by common criteria, such as by  Deal nameDeal stage or  Deal Owner. When dimensions are requested, values are segmented by dimension value. If a query has no  dimensions parameter, the returned metrics will provide aggregate values for the requested date range, such as overall Deal Amount or Deal Count.
D
  Date field (required) : Select the deal property you want to filter by, such as Close date, Create date, Last Activity date, etc. You can also choose a Custom Deal Property, which you have set up in HubSpot CRM.
E
  Date range (required) : To display your data the way you like, you will need to choose a date range. Just click on the "Date range" field. Selected intervals will be enabled for the new metric.
F
  Compare With (optional) : Select a date range to compare your previously selected interval to. This date range will be used for "previous line" on line and bar charts, and for calculating change %. Options are: None and Previous period.

2. Filters

Filters (optional) : You can filter results by dimension or by metric. Filters can be combined using  OR and  AND boolean logic. Filtered queries restrict the rows that do (or do not) get included in the result. Each row in the result is tested against the filter; if the filter matches, the row is retained, and if it doesn't match, the row is dropped.

Filter Operators

add_user_popup@2x

3. Data preview

In Data Preview you will see all values for selected metrics and date ranges. At the bottom you can check total values. Just click on tabs for switching date range.

If you want to sort values by date/time just click on the arrow next to "Date"

When your query is ready, click "Save query." Now a new metric has been created, with the metric name the same as the custom query name.

By default, a metric will be added in the datasource where data is pulled. It is possible to change the target datasource by clicking on "Change target data source" on the top right.

Transformations

Rename Dimension:

It is possible to rename dimensions (best practice is to rename them before saving the query) by clicking  "..." next to "Dimension" and then selecting "Rename dimension".

Skip Dimension:

This function is useful when you want to exclude one or more dimensions. (For example if you want to show data for all Countries except one). Click on  "..." next to "Dimension" and select "Skip dimension".

Round values:

Click on  "..." next to "Value" and select "Round values." Enter the number of decimal places to be rounded to into field in popup, and values will be rounded accordingly.

Additional Info: 

  • The Dimensions "HubSpot Team" and "Deal Owner" return IDs rather than names. 

Still need help? Contact Us Contact Us