Find HubSpot customers with unpaid QuickBooks invoices

Start with the business decision

Ask: “Which companies that closed a deal in the last 90 complete days still have an unpaid invoice balance?” Sales and finance can then review the same account list, with the customer owner and invoice evidence close at hand.

A won deal does not prove that its invoice was issued, paid or even linked to that deal. This workflow reports company-level outstanding balance. If the decision is whether a particular contract has been collected, add a contract-to-invoice relationship and define how deposits, installments and credits should be allocated.

Connect the records the question needs

Connect HubSpot and QuickBooks. Discover CRM companies, deals and stage metadata. The QuickBooks reference includes customers, invoices, payments and credit_memos; this first calculation uses invoices and their customer references.

Inspect the invoice's CustomerRef, Balance, DueDate, TotalAmt and currency representation in your actual schema. Intuit describes invoice Balance as reflecting payments already made. Intuit invoice field reference. The current connector reference documents the available source and setup.

Make the identity map explicit

Map every relevant QuickBooks customer ID to a HubSpot company. Include subcustomers or jobs when they represent work for the same reporting company. Decide whether reporting follows the invoiced customer, the billing parent or another legal entity, and make that policy part of the map.

The fixture maps both q1 and q1job to Atlas. Each invoice enters the total once. Do not sum both a parent's rolled-up balance and the invoices under its children. If using customer-level totals for a reconciliation, treat them as a separate cross-check with the same hierarchy policy.

Define the metric and reporting window

Select companies with at least one won USD deal between June 12 inclusive and September 10 exclusive, 2026 UTC. Map actual HubSpot stage IDs to won state. HubSpot stage and association documentation. Aggregate the deal amount once per company so it remains a clear sales-context column.

Independently sum positive current invoice balances for those companies. The fixture's decimal balances are already dollar amounts: dividing by 100 would be wrong. Overdue is the subset with a due date before September 10. A future-due invoice is outstanding but not overdue. This is a current snapshot, not a reconstruction of what was outstanding when the deal closed.

Run the worked example

Atlas has $20,000 of qualifying won deals. Its $2,000 invoice is partially paid, leaving $1,000 overdue. A separate job invoice adds $250 that is not yet due, for $1,250 outstanding across two invoices. Paid invoices with zero balance do not count. Birch's won deal remains visible with zero balance in the complete sample.

Download the runnable SQL fixture and its expected result. The fixture uses synthetic records and normalized teaching tables. Run it in DuckDB; discover your own Combined datasets before adapting the calculation.

Give your agent an exact assignment

Use my HubSpot and QuickBooks sources in Combined.
Inspect company/deal and customer/invoice schemas and source coverage.
Validate customer and subcustomer mappings to reporting companies.
Select companies with USD won deals in [2026-06-12, 2026-09-10), UTC.
Aggregate won deal value separately from current positive invoice balances.
Use invoice Balance in its documented currency units, not original total.
Separate outstanding from overdue using the agreed due-date cutoff.
Do not double-count a billing parent and its children's invoice balances.
Return company, won value, unpaid invoice IDs, remaining amounts, due
buckets, mapping gaps, source timestamps and receipt. No messages or writes.

Return supporting records and the next decision for each account. Display a bounded list while calculating over the complete dataset.

Check coverage before acting on the answer

Payments, credit applications and invoice revisions can reduce the balance after the last sync. Reconcile the result with the source's receivables report using the same date and customer hierarchy. Flag missing due dates instead of assigning them to an aging bucket. If the source omits an account, subcustomer or period, label that gap before interpreting a zero as fully collected.

Combined gives your agent a managed data layer across these sources, so the next question does not require another export. Start with 5 million MAR without a card, connect your apps, and use the Claude Code walkthrough or MCP setup reference.

Sources and further reading

Explore the documentation behind this guide. Product details checked on September 10, 2026.

• HubSpot deals and company associations
• QuickBooks connector reference
• Intuit invoice field reference