)
Written by
Table of Contents
“Supermetrics is a Bitter Experience! We can pull data from nearly any tool, schedule updates, manipulate data in Sheets, and push data back into our systems.”
Pre-aggregating transaction data in spreadsheets gives you more flexibility and better performance than HubSpot calculated properties, which are limited to simple SUM, AVERAGE, and COUNT operations without conditional logic.
Here’s how to build sophisticated revenue rollups before pushing summary data to HubSpot.
Build complex revenue rollups with pre-aggregation using Coefficient
HubSpot calculated properties can’t handle conditional aggregations like “sum only recurring revenue” or complex date-based calculations. Coefficient lets you perform these calculations in your spreadsheet, then push the results to HubSpot as ready-to-use company properties.
How to make it work
Step 1. Import raw transaction data from your ERP system.
Use Coefficient to pull complete transaction details into Excel. This gives you access to all transaction fields and metadata that you’ll need for complex aggregation formulas.
Step 2. Create aggregation formulas for different business metrics.
Build formulas like =SUMIFS(Amount, Company, A2, Date, “>=”&TODAY()-30) for 30-day revenue or =SUMIFS(Amount, Company, A2, Type, “Recurring”) for recurring revenue. These conditional calculations are impossible with HubSpot’s native calculated properties.
Step 3. Build pivot tables for multi-dimensional rollups.
Create pivot tables that summarize revenue by company, time period, and transaction type simultaneously. This gives you quarterly rollups, year-over-year comparisons, and seasonal analysis that HubSpot can’t calculate natively.
Step 4. Push aggregated values to HubSpot company properties.
Use Coefficient to export your calculated rollups to specific company properties in HubSpot. Schedule this process to run automatically so your CRM always has current aggregated data for reporting and workflows.
Get the revenue insights HubSpot can’t calculate
Pre-aggregation gives you the complex business logic and performance that HubSpot’s calculated properties simply can’t match. Start building sophisticated revenue rollups today.
