Building A Custom Report
Report Builder is a tool for building tables out of your HubSpot objects such as deals, contacts, companies, etc. Select what rows and columns you want and we buid the table for you. The table will automatically be kept up to date so you can use it to power other Sheets features suh as Pivot tables.
Example: Deals in pipeline by rep
In this example, we will demonstrate building a table of how many deals each owner has across all pipelines.
1. Open the Report Builder
Report library can be accessed via the Add-ons menu by selecting: Add-ons > Demand Sage > Build Custom Report
This will cause a sidebar to pop out containing the report builder.
2. Select the entity you wish to report on
Select the object in HubSpot you wish to build a table on. Currently Companies, Contacts, Deals, Content, and Marketing Emails are supported. Because we are building a table of all the deals, split out by various properties, select Deals from the list:
3. Pick a metric
Upon selecting the Deals entity, the Metric list will automatically get filled with with
Deals (Count). This means that the numbers in the table will be the number of deals that have these properties, split up by the properties you put into rows and columns in the next steps.
Metrics Deep Dive:
It is possible to select any deals property to use for the values in your table. For each property, we'll show you all the possible ways to use that property. For example, suppose you selected
Number of Contacts. This represents the number of contacts associated with each deal. This could be computed as the total contacts across all deals, or the average number for all the deals, or the mininum, etc. For this reason, if you select Number of Contacts, you can click on it and a drop down will appear letting you select what type of value you wish. We will only show you values that make sense given the property selected
Each property in the list will have an icon next to it. The letter A signifies the type is a string, and counts are the only operation that makes sense. A calendar is a date so only counts, mins and maxes will be available. The hash tag (#) is a number and supports all operations. A checkmark is a binary option which supports counts (each true value will be counted as 1, and false are 0 so won't be in the count).
Finally, it is possible to select multiple metrics. This will cause the values to be displayed in cells next to each other. One common use case for this is to view a count alongside an average in the same table.
4. Rows and Columns
The next two selectors are for rows and columns. Both selectors behave the same and let you choose what properties will be used to split up the rows and columns of your table.
In our example, we want one row for every pipeline in the system. So click in the Group Rows selector and a menu will pop up. Start typing:
and it will list all properties with the word pipeline in it. You will see
Pipeline ID, which is the interal ID for a pipeline but instead scroll down to just
Pipeline which is the name of the pipeline.
Do the same with columns and select
Deal Owner. The result should look like this:
Tips for picking rows and columns:
- As with metrics, each property has an icon next to it indicating the type. Letters are strings, calendars are dates, hash tags are numbers, and checkboxes are binary options.
- Also as with metrics, you may select multiple properties for rows and colunmns.
- We recommend picking properties with very few values (lower cardinality) such as Deal Owner for columns and putting the items with higher cardinality (Such as dates) into rows.
5. Insert the Table
Clicking insert table will create a sheet with the table in it. This table will be updated daily and you can use it as a source to build pivot tables or perform any other Sheets operations. This is an example output that you might see:
We create a new sheet every time you press Insert Table. This way you don't accidentally edit your current sheet. But this means that after you press the button you have to select the new sheet to see the data. It is also a good habit to delete the old sheets if you are no longer using them.
6. Filtering (optional)
It is possible to limit the results in your table to only ones meeting certain requirements. For example, you may only wish to see Deals created in the last week.
To do this, click into
Add/Edit Filters. This will expand the filters section. You may select a property to create a filter on. Just like with the other drop downs, each property has its type and this will control which values you may filter on. For example, number properties such as
Annual Revenue support many operations such as greater than, less than, and equal to but a date property such as
Close Date will only support range operations like greater than or in between and won't support equal to.
For date fields, there are a number of predefined periods such as
Previous 365 Days or
Week to Date. This will automatically be updated such that the table is always showing the up-to-date values. So,
Previous 365 Days will always show data of the last 365 from the current date, and
Week to Date will always show data from the start of the week to the current date, changing every week.
It is possible to add multiple filters. Doing so will cause only the results that meet all the filter criteria to appear in your final table.
HubSpot Default Properties
HubSpot provides many propeties out of the box for each of the objects listed above. For some information on the default values, see the HubSpot docs: