Automate and audit HubSpot deal pipeline operations without destroying real
pipeline — covering stage automation loops, stale-deal safe-close logic,
forecast reconciliation, custom property drift detection, quota dashboard
cache-busting, and multi-pipeline duplicate detection. Use when writing or
debugging workflow automations that move deals between stages, auditing
pipelines for stale or duplicated opportunities, reconciling forecast numbers
that disagree across reports, or hardening RevOps dashboards against property
deletions and reporting-cache lag. Trigger with "hubspot deal pipeline",
"hubspot stage automation", "hubspot stale deals", "hubspot forecast
reconciliation", "hubspot quota dashboard", "hubspot duplicate deals",
"revops pipeline audit".
Instrucciones de origen · Vista previa de solo lectura
name
hubspot-deal-pipeline-automation
description
Automate and audit HubSpot deal pipeline operations without destroying real
pipeline — covering stage automation loops, stale-deal safe-close logic,
forecast reconciliation, custom property drift detection, quota dashboard
cache-busting, and multi-pipeline duplicate detection. Use when writing or
debugging workflow automations that move deals between stages, auditing
pipelines for stale or duplicated opportunities, reconciling forecast numbers
that disagree across reports, or hardening RevOps dashboards against property
deletions and reporting-cache lag. Trigger with "hubspot deal pipeline",
"hubspot stage automation", "hubspot stale deals", "hubspot forecast
reconciliation", "hubspot quota dashboard", "hubspot duplicate deals",
"revops pipeline audit".
Automate HubSpot deal pipelines without blowing up real revenue. This is not a
walkthrough of workflow builder UI — it is the engineering behind six failure
modes that silently destroy RevOps data while every dashboard stays green until
a rep's quota call reveals the damage.
The six production failures this skill prevents:
Stage automation loops — a workflow moves a deal to "Demo Scheduled",
which triggers a second workflow enrolled on "Demo Scheduled" that moves it
back to "Qualified", which triggers the first workflow again. The deal
bounces between stages until HubSpot's 100-action daily workflow execution
limit for that deal is exhausted. RevOps sees "workflow failure" alerts but
no stage history that makes sense.
Stale-deal auto-closing — a cleanup workflow auto-closes deals older
than 90 days to clear pipeline bloat. The contact associated with deal #4872
responded to an email this morning. The deal is closed as Lost. The rep calls
the contact, who says "I just told someone we were ready to sign." This
failure costs quota attainment, not just data quality.
Forecast query inconsistency — amount, hs_projected_amount, and a
custom arr_value property all exist on the same deal. The CRO dashboard
queries amount. The ops team queries hs_projected_amount. RevOps built a
custom rollup on arr_value. At end of quarter they produce three different
forecast numbers for the same deal set with no canonical answer.
Custom property reporting gap — a RevOps engineer renames
deal_source_detail to original_lead_source in the property settings.
Every deal-source dashboard built on the old property name silently returns
zero. No error is thrown. The board sees a 100% collapse in deal source
tracking that is entirely an artifact, not a pipeline signal.
Quota dashboard staleness — HubSpot's reporting layer caches deal stage
aggregates. A deal closes at 4:47pm. The quota attainment dashboard doesn't
reflect it until 8:30pm. A rep who hit quota at EOD is told they're 3%
short. This is a 4-hour cache lag in the default reporting stack, and it
affects every quota conversation at month-end.
Multi-pipeline deal duplication — a new business opportunity becomes an
expansion after the initial close. Someone creates a deal in the Expansion
pipeline for the same company without deleting the original, or a workflow
auto-creates expansion deals. The same revenue is counted in two pipeline
forecasts with no cross-reference key linking them.
Prerequisites
All API calls use a HubSpot private app token or OAuth access token in the
Authorization: Bearer $HUBSPOT_TOKEN header. The hubspot-auth skill covers
token caching, rotation, and rate-limit backoff patterns. For deal pipeline
automation, the required scopes are listed below.
HubSpot Sales Hub Professional or Enterprise (workflows + custom properties
Workflow loops happen when a stage-change trigger has no memory of what caused
the change. The guard injects a sentinel property hs_pipeline_loop_guard
(type: string, format: workflowId:epochMs) that any workflow writes
immediately before it changes a stage, and reads before it fires. If the guard
was written by the same workflow within a debounce window, the workflow aborts.
Create the sentinel property first (one-time setup per portal):
hubspotGet and hubspotPatch are thin fetch wrappers — implementations in
implementation-guide.md.
Detecting existing loops via stage history audit:
# Pull stage change history for a specific deal — look for oscillation
DEAL_ID="12345678"
curl -s "https://api.hubapi.com/crm/v3/objects/deals/${DEAL_ID}/changelog" \
-H "Authorization: Bearer $HUBSPOT_TOKEN" | \
jq '.results[] | select(.propertyName == "dealstage") | {timestamp, from: .previousValue, to: .currentValue}'
If the output shows the same two stage IDs alternating more than twice in 24
hours, that deal has a live loop. Identify the responsible workflows by checking
hs_lastmodifieddate on the deal alongside the workflow enrollment history in
HubSpot UI (Contacts → Workflows → History tab for the deal).
Never auto-close a deal without first checking whether the associated contact
has recent email activity. The safe-close criterion is: deal older than 90 days
AND no inbound email in the last 30 days AND no open tasks.
The full stale_deal_audit.py script (with get_stale_open_deals, has_recent_inbound_email, has_open_tasks, and paginated search) lives in implementation-guide.md § Stale-Deal Audit.
Key points for the search query:
# CRM search filter for stale open deals (epoch ms cutoff)
CUTOFF=$(python3 -c "import datetime; print(int((datetime.datetime.utcnow()-datetime.timedelta(days=90)).timestamp()*1000))")
# filterGroups: createdate LT $CUTOFF AND hs_is_closed EQ false
The script outputs a CSV with safe_to_close boolean. Only rows where safe_to_close=True are candidates for batch close. A separate batch_close_stale.py script (also in the implementation guide) handles the actual close — always requires human review of the CSV before executing.
HubSpot exposes four distinct amount-like fields on a deal. Using the wrong one
in a report produces a different number without any error. The canonical
selection logic:
Field
When it is the right number
amount
The rep-entered deal value. Use for quota credit and pipeline value in all CRM views.
hs_projected_amount
Amount × current-stage probability. Use only for probability-weighted pipeline forecasts.
hs_deal_stage_probability
The stage probability (0–1). Multiply amount by this yourself if you need weighted values.
Custom arr_value, mrr_value, etc.
Only if your org has explicitly separated ARR/MRR from deal amount. Validate these exist on the deal before reading them.
Always request all four amount fields — pick one canonical field per report:
# Fetch all four amount fields for a deal and compare
curl -s "https://api.hubapi.com/crm/v3/objects/deals/$DEAL_ID" \
-H "Authorization: Bearer $HUBSPOT_TOKEN" \
--data-urlencode "properties=amount,hs_projected_amount,hs_deal_stage_probability,arr_value" | \
jq '.properties | {amount, projected: .hs_projected_amount, probability: .hs_deal_stage_probability, arr: .arr_value}'
Discrepancy rule: if |hs_projected_amount - (amount × hs_deal_stage_probability)| / amount > 0.05,
hs_projected_amount is stale. Force recalculation by patching dealstage to
the current stage value — HubSpot recomputes on every stage write.
The full Python forecast_reconcile.py script (with paginated open-deal search
and --fix flag for bulk recalculation) is in
implementation-guide.md § Forecast Reconciliation.
When a deal property used in a dashboard is renamed or deleted, dashboards
silently return zero without throwing an error. Run this check before every
sprint that touches property schema, and wire it into a nightly CI job.
#!/usr/bin/env bash# check-deal-properties.sh# Usage: HUBSPOT_TOKEN=... ./check-deal-properties.sh properties-in-use.txt# properties-in-use.txt: one property name per line (from dashboard/report definitions)set -euo pipefail
PROPERTIES_FILE="${1:-properties-in-use.txt}"
TOKEN="${HUBSPOT_TOKEN:?HUBSPOT_TOKEN required}"# Fetch all deal properties from the portal
ALL_PROPS=$(
curl -s "https://api.hubapi.com/crm/v3/properties/deals" \
-H "Authorization: Bearer $TOKEN" | \
jq -r '.results[].name'
)
echo"Checking properties against portal schema..."
MISSING=0
while IFS= read -r prop; do
[[ -z "$prop" ]] && continueif ! echo"$ALL_PROPS" | grep -qx "$prop"; thenecho"MISSING: $prop"
MISSING=$((MISSING + 1))
fidone < "$PROPERTIES_FILE"if [[ $MISSING -gt 0 ]]; thenecho""echo"ERROR: $MISSING properties used in reports do not exist in this portal."echo"Review recent property renames/deletions in Settings → Properties."exit 1
elseecho"All $( wc -l < "$PROPERTIES_FILE" | tr -d ' ' ) properties found in portal schema."fi
Generate properties-in-use.txt by extracting all property names from your
report/dashboard definitions. The list must be maintained alongside any schema
change — wire check-deal-properties.sh into your deployment pipeline so that
property renames fail the deploy before they break dashboards in production.
HubSpot's native reporting layer caches deal stage aggregates. The cache TTL
in the standard reporting stack is up to 4 hours. For real-time quota views at
month-end, bypass the reporting layer and query the CRM API directly with a
current-state search.
hubspotSearch is the same helper defined in section 1. Serve this function
from your internal ops dashboard and poll it on page load instead of embedding
a HubSpot report iframe. The CRM API reflects stage changes within seconds of
them happening. The full polling service (Express + 30-second in-memory cache
The canonical deduplication key is company_id + closedate_month +
deal_type_custom_field. Without this cross-reference, the same opportunity
appears in two pipelines and inflates ARR forecast.
The full findDuplicateDeals() and searchAllOpenDeals() TypeScript implementations live in implementation-guide.md § Multi-Pipeline Duplicate Detection. The algorithm:
Search all open deals, paginated (hs_is_closed EQ false)
For each deal, fetch associated companies via GET /crm/v3/objects/deals/$DEAL_ID/associations/companies
Group deals by companyId — companies with deals in more than one pipeline are duplicates
Once duplicates are identified, link them with a cross-reference association
before closing one:
# Link a new-business deal to its expansion deal (association type 5 = deal-to-deal)
curl -s -X PUT \
"https://api.hubapi.com/crm/v4/objects/deals/${NB_DEAL_ID}/associations/deals/${EXP_DEAL_ID}/5" \
-H "Authorization: Bearer $HUBSPOT_TOKEN" | jq '{fromObjectId, toObjectId, associationTypes}'
Error Handling
HTTP Status
Error
Root Cause
Action
400 BAD_REQUEST
INVALID_FILTER
Search filter uses a property name that doesn't exist or uses wrong operator for type
Validate property names via GET /crm/v3/properties/deals before building filter; use EQ for enum, GTE/LT for date/number
400 BAD_REQUEST
PROPERTY_DOESNT_EXIST
PATCH payload includes a property that was deleted or renamed
Run check-deal-properties.sh against the payload property list; catch and surface the missing property name to RevOps
403 FORBIDDEN
MISSING_SCOPES
Token lacks crm.objects.deals.write or automation scope
Verify scopes via GET /oauth/v1/access-tokens/$TOKEN; re-issue private app token with correct scopes
404 NOT_FOUND
OBJECT_NOT_FOUND
Deal ID in URL doesn't exist, or association target object doesn't exist
Confirm deal exists with GET /crm/v3/objects/deals/$DEAL_ID before writing; handle 404 in batch loops gracefully
409 CONFLICT
OBJECT_ALREADY_EXISTS
Batch create includes a deal that matches a unique property value
Deduplicate input set; use batch update instead of create for existing deals
429 TOO_MANY_REQUESTS
RATE_LIMIT
Exceeded 100 API calls/10s (Professional/Enterprise) or 250K/500K daily
Read Retry-After header and pause; add 100ms sleep between pages in search loops; use batch endpoints to collapse N updates into one call
Stage transition guard — hs_pipeline_loop_guard sentinel property
created in the portal, and a TypeScript safeStageTransition() wrapper that
prevents any workflow from re-triggering itself within a 60-second window
Stale-deal audit CSV — stale_deals.csv with a safe_to_close boolean
column; only rows with safe_to_close=True are candidates for auto-close
workflows
Forecast reconciliation log — structured WARN output for every deal where
hs_projected_amount diverges from amount × stage_probability by more than
5%, identifying deals where stage probability was changed without
recalculating projections
Property drift report — exit-1 CI gate that lists every report property
not present in the portal's current schema
Live quota snapshot endpoint — direct CRM API query returning
closedWon, closedWonCount, openPipeline, and asOf with sub-second
freshness versus the 4-hour reporting cache
Duplicate deal groups — list of companies with open deals in more than
one pipeline, with deal IDs to inspect and cross-reference associations to
create before merging
