Find HubSpot deals that need Intercom support context
Connect open HubSpot pipeline to recent unresolved Intercom conversations, with customer-message rules, a conversation map and tested SQL.
Give your sales agent the support context behind an open deal. Combined lets it query HubSpot and Intercom together, while explicit conversation mapping and message filters keep the result grounded. Count conversations once, then put their urgency beside the account's open pipeline.
Start with the business decision
Ask: “Which companies with open deals have an open support conversation containing a customer message in the last 14 complete days?” This is a sales-preparation question. It helps an owner understand what the customer is asking before discussing an expansion, proposal or renewal.
A conversation updated yesterday may only have been reassigned internally. It is not necessarily a recent customer interaction. The workflow therefore checks customer-authored messages rather than treating every conversation update as customer activity.
Connect the records the question needs
Connect HubSpot and Intercom in Combined. The relevant references are HubSpot companies, deals and pipeline metadata; Intercom conversations, conversation_parts, contacts and companies.
Inspect how your source represents the opening message as well as later conversation parts. Intercom distinguishes open, closed and snoozed conversation states, and its conversation object carries participant contacts. Conversation reference. The fixture normalizes all relevant message evidence into one teaching table so its customer-message rule is explicit.
Make the identity map explicit
Build a stable company map, then resolve each relevant conversation to its intended reporting company. A contact can participate in multiple contexts, so do not assign every company associated with the contact to every conversation. Use explicit company linkage, a maintained bridge or reviewed account attributes; leave ambiguous matches for review.
Normalize opening-message and part identifiers without duplicating the same message. Reduce qualifying messages to distinct conversation IDs, reduce conversations to a company count, and aggregate deals independently. This prevents two customer replies or two open deals from doubling the count.
Define the metric and reporting window
The fixture's message window is August 27 inclusive through September 10 exclusive, 2026 UTC. A conversation qualifies when at least one customer-authored message falls inside that interval and the conversation is currently open. Closed conversations and recent internal-admin messages do not qualify. Snoozed conversations can be a separate follow-up queue; the example does not count them as open.
Pipeline is the current sum of open USD deals. Map HubSpot's internal stage IDs to closed/open meaning from your actual pipeline settings. Deal associations establish the CRM company relationship. HubSpot deal and association reference. The pipeline and message window intentionally answer different parts of the decision.
Run the worked example
Atlas has two open deals totaling $20,000. Two recent customer messages belong to the same open conversation, so its support count is one. Another recently active conversation is closed and excluded. Birch has a $6,000 deal, but only an old customer message plus a recent admin message, so its qualifying count is zero.
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.
| Company | Open deals | Open pipeline | Recent open conversations |
|---|---|---|---|
| Atlas | 2 | $20,000 | 1 |
| Birch | 1 | $6,000 | 0 |
Give your agent an exact assignment
Use my HubSpot and Intercom sources in Combined.
Discover schemas, actual deal stages and conversation/message datasets.
Verify the company map and resolve conversations to reporting companies.
Normalize opening messages and later parts, preserving author type.
Find distinct currently open conversations with customer messages in
[2026-08-27, 2026-09-10), UTC. Internal updates do not qualify.
Aggregate conversations by company and open USD deals separately.
Join those summaries, preserving companies with no qualifying conversation.
Return pipeline, conversation count, linked evidence, owner if available,
ambiguous matches, coverage timestamps and query receipt. Read only.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
Conversation bodies and message history can have different coverage from conversation headers. Confirm both before saying a customer has been silent. The current conversation state is a snapshot; it does not prove the conversation was open on every day in the message window. Use the evidence links to review the actual request, then let the account owner decide how to respond.
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.