Last updated: January 29, 2024
Available with any of the following subscriptions, except where noted:
Operations Hub
Enterprise
|
A dataset is a collection of data from across your HubSpot account that can then be used in custom reports. A dataset can include properties for CRM objects and HubSpot assets, along with formulas to calculate your data as needed.
Summary functions enable you to aggregate data across multiple rows within datasets. It also enables you to calculate rates and ratios.
Learn more about summary functions and view videos on the HubSpot Community.
Metrics that can be calculated using summary functions include:
- Conversion rate
- Average order size
- Average margin
- Close ratio
- Lifetime value
- Customer acquisition cost
- Churn rate
- SLA adherence
- Custom NPS calculations
Row-level vs summary formulas
Row-level formulas: execute row-level operations and make calculations across a single record. The formula result displays one value per row, and all context for the calculation exists at a row level. Find more information in the syntax section of the datasets knowledge base.
Summary formulas: execute multi-row data operations and calculate metrics across multiple records. The formula result displays one value per row, but the context for the calculation may contain more than one row of data.
Use summary functions
When creating a Formula field, you can use the below summary functions to create functions:
List of summary functions
| Function | Definition | Input/Output | Syntax | Example |
|
|
Adds values in a given column and returns an aggregate value. |
Input: Number/Null Output: Number/Null |
|
|
|
|
Returns the number of rows in a given column. |
Input: Number/Null Output: Number/Null |
|
|
|
|
Calculates the average value of a column of numbers. The average value of a column is equal to the sum of the values divided by the number of values. |
Input: Number/Null Output: Number/Null |
|
|
|
|
Returns the lowest value in a given column or the lowest value between two values on the same row. |
Input: Number/Date/Datetime/Null Output: Number/Date/Datetime/Null |
|
|
|
|
Returns the highest value in a given column or the highest value between two values on the same row. |
Input: Number/Date/Datetime/Null Output: Number/Date/Datetime/Null |
|
|
Create a summary function
Columns calculated with summary functions can be used in the custom report builder as a measure. Measures are numerical or quantitative values that can be broken down using dimensions.
Within the dataset builder, summary measures will repeat results on each row. The calculation defaults to a table calculation until used in the custom report builder where the report configuration defines the summary level of the measure.
Learn more about summary formulas in datasets and view examples.
Create row level summary results
You can also use summary functions to create row level results that will ignore any reporting dimensions. This requires using a FIXED() function.
| Function | Input/Output | Syntax | Example |
|
|
Input: Summary expression Output: Row-level result |
|
|
Please note: if no optional value is defined in the FIXED() function, the calculation will be computed at the table level and will ignore all dimensions in the report view.
Example
Track conversion or win rate
Win rate refers to the proportion of successful deals out of the total number of opportunities. By tracking win rates based on product, market, target audience, and other factors, you can pinpoint the chances of success for each opportunity. This will enable you to strategically direct your resources toward those with the highest conversion potential.
- Data source needed: Deals
- Type of metric: Flexible measure
- Formula:
DISTINCT_COUNT(IF(LABEL([DEAL.dealstage]) = "Invoiced", [DEAL.hs_object_id], NULL)) / DISTINCT_COUNT(IF([DEAL.hs_is_closed] = true, [DEAL.hs_object_id], NULL))
View this example below in the datasets editor:
This conversion rate formula can then be used in reports, for example:
