Pipeline coverage and quota gap check

Coverage is arithmetic, but the arithmetic is only as good as the two numbers nobody checks: which open deals can realistically land inside the period, and what coverage this specific team actually needs. The widely quoted 3x target is the reciprocal of a one-in-three win rate, so quoting it to a team that wins 15 percent of its deals understates the requirement by more than half.

Steps

1. Discover the pipelines and stages before filtering anything. Call hubspot.list_pipelines for deals and capture every pipeline id, every stage id, each stage's label, displayOrder, the stage probability metadata and the closed and won flags. Stage ids are opaque and scoped per pipeline: closedwon only exists in default portals, custom pipelines use numeric strings, and "Negotiation" in one pipeline is a different id from "Negotiation" in another. Then call hubspot.list_properties for deals to confirm amount, amount_in_home_currency, deal_currency_code, closedate, createdate, hs_deal_stage_probability, hs_manual_forecast_category and hubspot_owner_id exist, and to find any custom ARR, MRR or segment property. Read the options array of every enumeration you plan to filter on, because enumeration filters are case-sensitive.

2. Establish the period, the target and the pipeline in scope. Ask the user for the quota or revenue target and the period boundaries, and if hubspot.list_pipelines returned more than one pipeline, ask which one or run the analysis once per pipeline. Never pool pipelines: a new-business pipeline and a renewals pipeline have different win rates, different cycle lengths and often different amount semantics, so pooled coverage is meaningless.

3. Pull the trailing history first and compute your own benchmarks. hubspot.search_deals over a trailing 12 months (or three times the median cycle length, whichever is longer) with hs_is_closed EQ true, requesting createdate, closedate, amount, amount_in_home_currency, dealstage, hs_is_closed_won, days_to_close and the segment property, limit: 200, one sorts rule on closedate. From this compute win rate as won divided by won plus lost, average deal size, and the median and 75th percentile cycle length. State that definition in the output. Never take the win rate from the user or from a published benchmark, because required coverage of 1 divided by win rate is only valid when the win rate is measured on the same population as the pipeline being covered.

4. Pull open in-period pipeline, and pull the invisible buckets explicitly. hubspot.search_deals with pipeline EQ the pipeline id, dealstage NOT_IN the won and lost stage ids, and closedate BETWEEN the period start and end in epoch milliseconds, requesting dealname, amount, amount_in_home_currency, deal_currency_code, closedate, createdate, dealstage, hs_deal_stage_probability, hubspot_owner_id and hs_manual_forecast_category. Then run two more queries that the period filter would otherwise hide: open deals with closedate LT today, and open deals with NOT_HAS_PROPERTY on closedate. Report both counts separately and exclude them from in-period coverage.

5. Narrow the window until the result set fits one page, then say so. The HubSpot search tools in this connector return only the first page and do not hand back the next cursor, so the maximum you can see in one call is 200 rows and the default is 100. Shard every query by createdate month, by closedate fortnight or by hubspot_owner_id until each shard comes back under the page limit, and sum the shards. Report the row count you actually retrieved for each shard, and if any shard hits the limit exactly, label that figure as potentially truncated rather than presenting it as a complete total. Pace calls at or below 5 requests per second: the search API returns no rate-limit headers, so you cannot self-throttle reactively.

6. Add what is already banked, then reconcile it against money. hubspot.search_deals with dealstage IN the won stage ids and closedate inside the period gives closed won to date. Leaving this out overstates the gap. Then check it against cash with stripe.list_charges, stripe.list_invoices with status: "paid", polar.list_orders, or dodo_payments.list_payments with status: "succeeded" over the same window. Stripe amounts are in the smallest currency unit, so divide by 100; HubSpot amount is a decimal in major units, so do not. Where the two disagree, report the discrepancy: a coverage report built on a closed-won number finance does not recognise is worthless. Use google_sheets.add_sheet and google_sheets.append_rows with a run date if the user wants coverage tracked week over week, which is the only way to see it deteriorating.

7. Report. One row per owner plus a total row, with columns: quota, closed won to date, open in-period pipeline, stage-weighted pipeline, history-weighted pipeline, unweighted coverage, required coverage (1 divided by measured win rate), gap in currency, and deals needed at the current average deal size. Show the excluded buckets (past close date, no close date, zero or missing amount, aged beyond twice the median cycle) as their own lines with amounts. Then one sentence of judgement: whether the gap is closeable with existing pipeline or requires new creation, and how many days of pipeline creation at the current observed rate that represents.

Gotchas

• The connector returns one page only, and the cursor is unrecoverable. hubspot.search_deals accepts an after parameter but the response never carries paging.next.after back to you, and HubSpot's cursor is opaque so it cannot be reconstructed. Any total summed from a single call silently caps at 200 rows and looks completely plausible. Narrow or shard until each result set fits one page, and state the retrieved row count with a truncation caveat.
• dealstage is an opaque per-pipeline internal id. Never pattern-match a stage label to decide what is open, won or lost. Resolve the ids and the closed and won flags from hubspot.list_pipelines, per pipeline, every run. Stage ids change when someone edits a pipeline.
• hs_deal_stage_probability is almost always unmaintained. It defaults from the stage configuration and nobody revisits it, so stage-weighted pipeline is a restatement of your stage mix rather than a forecast. Flag any stage where the configured probability differs from the observed stage-to-won rate by more than 15 points.
• HubSpot forecast category labels are positional, not semantic. HubSpot's own documentation states the category descriptions "are based on the sequential order of the forecast categories, not the forecast category's name", so in a customised portal a category named Commit sitting second carries the definition "low likelihood of closing". Read the portal's actual ordering from the property options before trusting hs_manual_forecast_category. HubSpot also has no Omitted category; that is Salesforce terminology.
• closedate on an open deal is a rep's guess, and often a stale one. Ebsta and Pavilion's 2024 report, built on 4.2 million opportunities, found 31 percent of all open opportunities are already past their close date, and their 2023 report found 89 percent of close dates are set to the last day of a calendar month. Both are detectable here and both mean the in-period numerator is softer than it looks.
• Exclude pipeline that cannot land in time. Salesloft's rule is to remove deals aged beyond twice the average cycle from qualified coverage. Ebsta and Pavilion put a number on why: opportunities open longer than twice the average sales cycle have roughly a 3 percent chance of closing. Report them separately as "needs to be created".
• Test, demo and integration-created deals inflate the numerator. Sample deals shipped with the portal, Zapier or form-created deals with amount unset, and bulk-import duplicates all sit in open pipeline. Count open deals with zero or missing amount as a separate line and exclude them from weighted pipeline rather than treating them as real zero-value deals. Archived records never appear in search at all, so they cannot be used to explain a discrepancy.