Stalled and slipping deal audit

A single staleness rule produces a useless list: an enterprise deal silent for 30 days inside a nine-month cycle is normal, while a 14-day silence on a 30-day cycle is terminal. Classify every open deal against four independent signals, prefer HubSpot's own per-owner stall definition over any threshold you invent, and rank the output by forecast exposure rather than by deal count, because a rotten deal nobody is forecasting costs nothing and a rotten deal in Commit is why the quarter misses.

Steps

1. Discover the pipeline shape and the timing properties this portal actually has. Call hubspot.list_pipelines for deals to get every stage id, label, displayOrder and the closed and won flags. displayOrder is the only non-guessing way to establish what "late stage" means, and it must be computed within each pipeline separately. Then call hubspot.list_properties for deals and check explicitly whether hs_is_stalled_after_timestamp, hs_v2_time_in_current_stage and hs_v2_date_entered_current_stage exist: all hs_v2_* stage calculated properties are Professional and Enterprise only, and stage calculated properties are off by default on new pipelines. Also look for a custom close-date push counter and any custom next-step or MEDDIC field. Branch the method on what exists.

2. Prefer HubSpot's own stall threshold. If hs_is_stalled_after_timestamp is present, use it as the primary stall signal and say in the report that the threshold is HubSpot's, not ours: HubSpot defines it as the "timestamp when time in stage became 20% longer than the deal owner's closed-won average for that stage". That is already normalised per owner and per stage. Fall back to a manual threshold only when the property is absent, and when you do, derive it from this portal's own median cycle length per segment rather than a fixed 30 days. Do not use hs_v2_cumulative_time_in_<stageId> or hs_v2_latest_time_in_<stageId> for the current stage: HubSpot documents that both are null for the stage a deal is sitting in right now, which is exactly the deals you care about.

3. Pull every open deal in one narrow pass. hubspot.search_deals with dealstage NOT_IN all closed stage ids, requesting dealname, amount, amount_in_home_currency, deal_currency_code, dealstage, pipeline, closedate, createdate, hs_lastmodifieddate, notes_last_contacted, num_contacted_notes, num_notes, hs_next_step, hubspot_owner_id, hs_manual_forecast_category, plus hs_v2_time_in_current_stage, hs_v2_date_entered_current_stage and hs_is_stalled_after_timestamp where they exist. Because this connector returns only the first page and never hands back paging.next.after, shard by pipeline and then by createdate quarter or by hubspot_owner_id until each shard returns under the page limit of 200. Do the classification in the agent, not in the filter, so one pull serves all four signals. Then run the two queries the date filter hides: open deals with closedate LT today, and open deals with NOT_HAS_PROPERTY on closedate.

4. Build the baseline from this portal's own closed deals. hubspot.search_deals for deals with hs_is_closed EQ true over a trailing 12 months, requesting days_to_close, amount, amount_in_home_currency, dealstage, hs_is_closed_won, createdate and closedate, plus the hs_v2_date_entered_<stageId> properties you discovered by prefix in step 1 if the tier provides them. Compute the median and 75th percentile cycle length per size band and the median time in each stage. Thresholds must come from the portal, never from a benchmark.

5. Classify, then test whether a silent deal is really silent. Assign each open deal a verdict across four independent signals: activity silence (notes_last_contacted older than the derived threshold, floored at 14 days), overdue close date, excessive time in current stage, and total age beyond the 75th percentile cycle for its size band. Then apply the late-stage reality test: a deal in a late displayOrder stage with no hs_next_step and no recent logged contact is mis-staged, which is a different recommendation from "chase it". Use hubspot.search_contacts for the contacts associated with the deal, and where an Intercom connection is available intercom.search_conversations filtered to the account, to check whether the buyer is still talking to someone else at your company. A deal with no sales activity but an active support conversation is a very different verdict from one that has gone genuinely quiet.

6. Rank by forecast exposure and write the exception list somewhere durable. Rank by amount at risk and especially by amount sitting in Commit and Best case under hs_manual_forecast_category. Use google_sheets.add_sheet and google_sheets.append_rows with a run date, because the value of this audit is a named, owned, shrinking exception list tracked week over week, not a one-off report.

7. Report. One row per at-risk deal: deal name, owner, amount, stage, forecast category, days since last logged contact, days in current stage, days overdue on close date, and a single verdict column of Rotten, Slipping, Mis-staged or Watch. Above it a summary: total open pipeline, amount classified at risk, and amount at risk inside Commit and Best case. Below it the orphan buckets (no owner, no close date, no amount, no associated company) as their own counts. Then one sentence of judgement naming the single largest at-risk amount sitting in Commit. Where you used HubSpot's hs_is_stalled_after_timestamp, state that the threshold is HubSpot's own, normalised per owner and per stage.

Gotchas

• Close date history is not filterable. HubSpot keeps property history for closedate, but the CRM search API cannot query it, so you cannot ask for "pushed three times". Do not claim you detected repeated pushes unless the portal carries a custom counter property. Name whichever proxy you used instead.
• Four property names in wide circulation do not exist on deals and fail silently. hs_time_in_dealstage, hs_forecast_category, hs_num_times_contacted and hs_last_sales_activity_timestamp all return nothing rather than erroring, so a stall report built on them reports "no stalled deals" forever. Use hs_v2_time_in_current_stage, hs_manual_forecast_category, num_contacted_notes and notes_last_contacted. Note also that num_contacted_notes counts contact attempts only and excludes tasks and notes, while num_notes is the broader "Number of Sales Activities" count that includes them. They are not interchangeable.
• notes_last_updated is labelled "Last Activity Date" but HubSpot's own description text says it is the date of the next upcoming activity. The label and the description contradict each other, so do not build the silence threshold on it. Prefer notes_last_contacted, and if you use notes_last_updated at all, say which interpretation you assumed.
• The connector caps at one page and gives you no cursor. Every HubSpot search here returns at most 200 rows with no paging.next.after, so an unsharded "total open pipeline at risk" is wrong in a plausible-looking way. Shard until each query fits a page, report the retrieved row count, and caveat any shard that hits the limit exactly. Pace at 5 requests per second or slower; the search API returns no rate-limit headers.
• A long-cycle business is not a rotting business. A fixed 30-day rule flags an entire enterprise pipeline as dead and destroys the report's credibility. Derive thresholds per segment from this portal's median cycle length, and prefer hs_is_stalled_after_timestamp where the tier provides it precisely because it is already owner-normalised.
• Integration-created and duplicated deals will dominate a staleness ranking forever. Form integrations, Zapier flows and bulk imports create open deals with no owner, no amount and no activity. Report them as orphaned records, because the action is deletion rather than a sales call.
• Reopened deals corrupt the age calculation. Some teams reopen a closed-lost deal rather than creating a new one, which makes createdate to today a meaningless age. Where hs_is_closed is false but the deal has been in a lost stage before, flag it rather than aging it.