The $6.1M Revenue Report That Should Have Been $4.7M
Northwind Analytics is a B2B SaaS company with 3,400 company records, 8,200 contacts, and 1,100 open deals in HubSpot. Sales also uses a custom object called Implementation Project, linked to deals and companies, and line items on deals. The RevOps lead built a “Revenue by Company” report in the custom report builder using Deals as the primary object, joined to Companies and Line Items. The total closed-won amount is $4.7M, but the same report shows $6.1M. A spot check finds that 18% of contacts are associated with two or more companies, several deals are associated with both the end customer and the reseller, and 240 deals have three or more line items. The CFO wants a company-level revenue number that matches finance. Sales ops insists deal amount is the source of truth; finance uses line-item net revenue after discount. The team has two weeks before board reporting.
1. The CFO asks why the report totals $6.1M when closed-won deals total $4.7M. Identify the two most likely report-grain problems and describe a test report you would run to prove each one.
Read the worked answer
The two likely problems are many-to-many fan-out at the company join and one-to-many fan-out at the line-item join.
- Company fan-out. If the report includes every associated company, a deal linked to both the end customer and the reseller appears once under each company. The report then sums the full deal amount twice for the same deal. This is not a duplicate deal record; it is the report grain expanding to company-deal pairs.
- Line-item fan-out. If the report includes line-item fields, a deal with four line items can appear four times. If the measure is
Deal amount, HubSpot may repeat the deal amount on every line, inflating the total. That would also explain why the inflation is larger in the segment with 240 multi-line deals.
Test for company fan-out: build a deal-level report filtered to closed-won, show Deal ID, Deal amount, and count of associated companies. Filter to deals with more than one company association. Compare the sum of deal amount with the sum of distinct deal amount. If the first is higher, the join is fanning out.
Test for line-item fan-out: build a report with Deal ID, Deal amount, and line-item count. Compare sum of deal amount to sum of distinct deal amount. Then replace Deal amount with Line item net revenue and aggregate at deal level. If the deal amount scales with line-item count, the report grain is wrong. Also check for duplicate company records by domain, because duplicate companies can create the same symptom.
2. Recommend the report design and data model for (a) a CFO-grade company revenue number and (b) a sales pipeline number by deal owner. State the primary object, grain, association filters, and measure for each.
Read the worked answer
(a) CFO-grade company revenue
Use Line Items as the primary object for finance revenue, joined to Deals and then to the primary company only. Set the grain to one row per line item, because finance revenue is recognized at line-item net revenue after discount. Filter to closed-won deals and the relevant close-date period. Measure Sum of line item net revenue. Group by primary company. Do not also join contacts or implementation projects at this grain, because those joins will multiply rows. If finance needs a deal-level view, create a second report at deal grain and reconcile the two with a control total.
(b) Sales pipeline by deal owner
Use Deals as the primary object, filter to open deals, and use Deal amount as the measure. Group by Deal owner. Do not join line items; pipeline is a deal-level commitment, not a line-item sum. If the business needs a primary-company view, include only the primary company association label.
Key point: one report cannot simultaneously be at line-item grain and deal grain without a deliberate aggregation step. The custom report builder will not guess which object should set the row count. Document the grain, the association label, and the measure definition on every certified revenue report.
3. What governance changes would you make in the next 30 days to keep the revenue number stable?
Read the worked answer
I would treat this as a data-model governance problem, not a one-time report fix.
First, enforce association labels. Make Primary company mandatory on every deal through a required property or workflow validation. Train sales to use a reseller association label rather than adding the reseller as an equal company association. Review the 18% of contacts with multiple companies and determine whether those are legitimate buying committees or duplicate records.
Second, deduplicate companies. Match on domain, not name. Merge duplicate company records and standardize parent-child relationships. Duplicate companies are a common hidden cause of inflated company-level totals.
Third, certify the revenue report. Publish a report definition: primary object, grain, date property, association filters, measure, currency handling, and reconciliation owner. Add a control total card that compares the report to the CRM closed-won total. Investigate any variance over a stated threshold.
Fourth, separate finance and sales definitions. Finance revenue uses line-item net revenue; sales pipeline uses deal amount. Put both on the dashboard with labels, but never blend them into one “revenue” number. Finally, add a monthly RevOps review of deals with multiple company associations, missing primary company, and more than five line items. The goal is to catch fan-out before the board deck, not after.