The Renewal Report That Returns 3,980 Rows for 1,050 Deals
Vertex Services needs a renewal report for its customer success team. HubSpot has Deals, Companies, Contacts, Line Items, and a custom object called Renewal Checkpoint. Each deal has one primary company, often two associated contacts, and an average of four line items. The team wants one row per deal with deal owner, primary company name, renewal date, total line-item net revenue, gross margin percentage, and the latest checkpoint status. A first attempt in the custom report builder produces 3,980 rows for 1,050 deals. The total net revenue is inflated. The report also cannot show the latest checkpoint status reliably; it shows every checkpoint. The team needs a scheduled report for the VP every Monday and a dashboard card for the current quarter. HubSpot Sales Hub Enterprise and custom report builder are available.
1. Why does the report return 3,980 rows for 1,050 deals, and why is net revenue inflated? Explain the report grain and the fix.
Read the worked answer
The report grain is not “one row per deal.” It is “one row per deal × line item × checkpoint × contact association,” or whatever combination of joined objects is included as row-level fields. With an average of four line items per deal, 1,050 deals can easily produce more than 4,000 line-item rows; checkpoints and contacts add more. The 3,980 rows are a symptom of one-to-many joins in the custom report builder.
Net revenue is inflated for the same reason. If the report includes line-item rows and also includes a deal-level revenue measure, that measure can repeat on every line-item row. If the measure is a line-item net revenue sum but the report also joins checkpoints, the same line-item revenue can repeat for each checkpoint. The result is not a data error in HubSpot; it is aggregation at the wrong grain.
Fix: decide the report grain before choosing fields. For this report, the grain must be one row per deal. Use Deals as the primary object, include only the primary company association, and do not include line-item name or checkpoint name as row-level dimensions. Use Sum of line-item net revenue as an aggregated measure, not as a row-level field. Put checkpoint detail in a separate report or roll it up to a deal property. Validate by counting distinct deal IDs and comparing the report total to a deal-level control total.
2. Design the data model and report logic for one row per deal with the requested fields. Include how to get the latest checkpoint status.
Read the worked answer
Report design: primary object = Deals. Join to Companies using the primary company association label only, so each deal returns one company. Do not join Contacts as a row-level dimension; if contact count is needed, expose it as an aggregate measure or leave it out. Join Line Items only as an aggregate measure, not as a row dimension: Sum of line item net revenue. For gross margin, create a formula property at the line-item or deal level using (net revenue − cost) ÷ net revenue, and aggregate it consistently; do not average percentages across line items without weighting by revenue. Filter to the renewal pipeline and the relevant renewal date range. Group by deal ID, deal owner, primary company name, and renewal date.
Latest checkpoint: the custom report builder will show every checkpoint if checkpoint fields are included at row level. Do not try to solve this with a report filter for “latest” unless the data model supports it. Instead, create a deal property such as Latest renewal checkpoint status and Latest checkpoint date. Use a workflow or a calculated rollup to stamp the most recent checkpoint onto the deal whenever a checkpoint is created or updated. Then report on the deal property. If checkpoints have a status field and a date, the workflow should compare dates and update only when the new checkpoint is later. This gives one stable value per deal and keeps the report at deal grain.
Add a validation card: count of deals, count of distinct deal IDs, and total net revenue compared with a line-item control report.
3. How would you deliver the Monday scheduled report and the current-quarter dashboard card without duplicating logic?
Read the worked answer
I would build one certified report and reuse it in both delivery paths.
First, save the deal-grain renewal report with the standard filters: renewal pipeline, active deals, and the required date property. Create a second saved view or report filter for “current quarter” rather than rebuilding the logic. Add both the full report and the current-quarter view to a renewal dashboard. Use the dashboard’s date filter carefully: if the report uses Renewal date, the dashboard filter must use the same property and the same quarter definition. Do not mix renewal date with close date.
For Monday delivery, schedule the dashboard or the saved report to the VP and the customer success leaders. Confirm that the recipients have permission to see the underlying deals and that the schedule uses the same filters as the dashboard. If the schedule sends a CSV, include a definition tab or footnote so the VP knows the grain, the revenue measure, and the checkpoint logic.
To avoid duplication, document the report as the single source of truth: report name, owner, primary object, association label, date property, measures, and refresh expectations. Any ad hoc renewal request should start from this report, not from a new custom report. Finally, add a data-quality check for deals with no primary company, multiple primary companies, or no checkpoint, because those will undermine trust in the Monday number.