HubSpot Custom Report Builder Deep Dive
The custom report builder is the power tool of HubSpot reporting. The exam tests data-source design, aggregation, filters, and the double counting that joins introduce.
Building a report
- Choose a primary data source: contacts, deals, tickets, or a custom object.
- Add associated data sources; each join follows a defined association path.
- Choose metrics: count of records, sum, average, minimum, maximum of properties.
- Add filters and filter groups, with AND inside a group and OR across groups.
- Pick a visualisation: table, pivot table, bar, line, area, pie, donut, or KPI.
Joins and fan-out
Joining deals to contacts multiplies rows: one deal with three contacts appears three times, and summing deal amount triples the total. The fix is to aggregate at the primary-object level or use distinct counts where available.
Filters across objects
Filtering on an associated object's property removes primary records with no matching association. A deal filter on contact source silently drops deals without a contact. Check totals before and after adding cross-object filters.
Worked example
Closed-won revenue by original source this quarter. Primary: deals. Join: contacts. Filters: stage equals closed won, close date is this quarter, contact original source is known. Metric: sum of amount, deduplicated by deal. Reasoning: revenue lives on deals and source lives on contacts; without deduplication the sum inflates whenever a deal has multiple contacts.
Traps
- Fan-out double counting on one-to-many joins.
- Using create date when the question is about close date.
- OR filters that accidentally widen the population.
- A pie chart with 40 categories.
- Forgetting that reports inherit the viewer's permissions.