hubspot-revops-skill

Build revenue analytics infrastructure on HubSpot API + SQL data warehouse. Covers ICP validation, ML lead scoring, competitive intelligence, activity analysis, and pipeline forecasting — bridging CRM data into actionable intelligence products.

<quick_start> Epiphan-native path (default — no Private App needed):

1. Read crm://schema.md (Epiphan AI resource), then execute_sql / query_dataset against the HubSpot tables (hubspot_contacts, hubspot_companies, hubspot_deals, …) for every aggregate.
2. For any use case that produces a list of people or companies (UC1 segments, UC2 scored leads): pull candidates by SQL, then qualify_lead (Epiphan AI) on each row — Gate Q — before enrichment, scoring, or list membership.
3. Enrich gated rows: enrich_contact (Epiphan AI) → Apollo apollo_people_match → Clay waterfall.
4. Deliver per use case (table below): XLSX/HTML, export_query_csv, or a HubSpot property write via hubspot_update_contact (confirm-first). Raw SDK / Private App setup only when the MCP cannot reach something — appendix in reference/api-guide.md. </quick_start>

<house_rules> Customer-facing skill. Before output, run the gates in order (details: ~/.claude/skills/epiphan-house-rules-skill/reference/gates.md): S: search_product_catalog (Epiphan AI) or Epiphan Knowledge search before any product claim. B: check_my_copy on every customer-facing string. Q: qualify_lead right after the candidate pull, before tiering/enrichment/drafts. D: outbound text becomes a Gmail draft, never a send. Identifiers (owner IDs, portal, emails, seats) come from reference/identifiers.md in that skill — do not restate them here. On any gate failure follow reference/self-healing.md (retry, degrade with a flag, alert Tim, halt) and append a line to ~/.claude/skill-runs/.jsonl. </house_rules>

<success_criteria>

• Warehouse reachable via Epiphan AI execute_sql (schema read first) and data freshness validated (sync lag < 24h)
• At least one use case (ICP, scoring, competitive, activity, forecast) producing results with a named delivery artifact
• Every list-producing output (UC1, UC2) passed qualify_lead row by row; no inline Golden Rules / ATL-BTL / AE-owner re-implementation
• Lead scoring model trained on 200+ historical closed deals with measurable AUC
• Score write-back to HubSpot confirm-first and deduped
• Outcome sidecar + run-log line written </success_criteria>

HubSpot RevOps Analytics

Revenue analytics infrastructure on HubSpot API + SQL data warehouse. Bridges CRM data → analytics → intelligence products → revenue impact.

Scope: HubSpot-specific analytics stack. For basic CRM CRUD, use crm-integration-skill. For generic dashboards, use data-analysis-skill.

---

Setup Checklist

1. Epiphan AI warehouse (default)

Tim's HubSpot data is replicated into the Epiphan AI PostgreSQL warehouse. Access path:

1. Read crm://schema.md (or get_schema) — guessed column names are the #1 query failure.
2. execute_sql for rows (JSON, fast) · export_query_csv when the answer is a file · query_dataset for saved datasets · ask_agent only for questions SQL cannot express (slow, metered).
3. Validate freshness: SELECT max(hs_lastmodifieddate) FROM hubspot_contacts — lag > 24h → flag every result as stale.

2. Private App / raw SDK (appendix — only if the MCP cannot reach it)

A HubSpot Private App (contacts/companies/deals/owners/timeline scopes) and the Python SDK pattern are documented in reference/api-guide.md. Not needed for any use case below; do not start there.

---

Core Use Cases

#	Use Case	Input	Output	Tools	Delivery artifact
1	ICP Validation	Contact + company data	Segment conversion rates + gated target list	SQL → qualify_lead → Clay/Apollo	XLSX (segments sheet + gated contacts sheet) via export_query_csv/openpyxl
2	Lead Scoring	Historical deals	Win probability per lead (gated)	SQL + ML → qualify_lead	HubSpot custom property write (hubspot_update_contact, confirm-first) + XLSX audit
3	Competitive Intel	Deal close reasons	Win/loss by competitor	SQL	HTML matrix in ${CLAUDE_OUTPUTS_DIR:-./outputs/} + Slack DM summary to Tim
4	Activity Analysis	Engagement data	Activity→outcome correlation	SQL	HTML report
5	Pipeline Forecast	Open deals + stage history	Weighted revenue forecast	SQL	XLSX forecast (by stage, by rep) + one-paragraph summary

Use Case Details

UC1 — ICP Validation: Join contacts + companies + deals in SQL, segment by industry/size/geo, compute conversion rates per segment (aggregates stay in SQL). When the segment becomes a target list: run qualify_lead on every candidate row first (Gate Q — customers, channel, product-only, active AE-owned, suppressed drop out; stale AE leads surface as STALE AE LEAD; tier from the verdict), then feed survivors to the enrichment waterfall:

1. find-and-enrich-company or find-and-enrich-contacts-at-company to identify target contacts
2. add-contact-data-points / add-company-data-points to queue enrichment jobs
3. get-task to poll for results and check state: completed
4. Write enriched data back to HubSpot via API or Epiphan CRM integration

Alternative: Use Apollo MCP (apollo_people_match) for direct enrichment without waterfall wait.

UC2 — Lead Scoring: Train GradientBoostingClassifier on historical won/lost deals. Features: company size, industry, engagement score, days in pipeline, qualify_lead tier. Score only rows that passed Gate Q; deploy scores back to HubSpot as a custom property via hubspot_update_contact (confirm-first, dedupe on hs_object_id).

UC3 — Competitive Intel: Extract competitor mentions from deal closed_lost_reason. Build win/loss matrix by competitor. Trigger webhook alerts on competitive displacement patterns.

UC4 — Activity Analysis: Correlate email opens, meetings booked, calls logged with deal outcomes. Identify which activities actually move deals forward.

UC5 — Pipeline Forecast: Calculate weighted forecast using stage-specific win rates from historical data. Factor in deal age, velocity, and rep performance.

Reference: See reference/sql-analytics.md for complete SQL templates per use case.

---

Prospect Quality: Gate Q owns the Golden Rules

qualify_lead is the source of truth for every list this skill produces (UC1 target lists, UC2 scored leads): customers, channel partners, product-only engagers, AE-owned-active, suppressed, and duplicates are its verdict, and ATL/BTL tier comes from the same call. The full rule text and the owner IDs live in CLAUDE.md "Golden Rules" and the house-rules skill's reference/identifiers.md; they are not re-implemented here.

SQL may pre-filter to cut the candidate volume before Gate Q, as long as it stays looser than the gate (never tighter) and the AE clause matches the 90-day stale exception rather than a hard exclude:

-- candidate pre-filter only; qualify_lead still runs on every surviving row
WHERE lifecyclestage <> 'customer'
  AND COALESCE(device_count, 0) < 1
  AND COALESCE(is_channel, false) = false
  AND country IN ('United States', 'Canada')
  -- AE-owned: keep if last activity > 90 days (surfaces as STALE AE LEAD); drop if worked recently
  -- mirror of identifiers.md (Lex 82625923 NA-only, Ron 423155215 all, Phil 190030668 NA-only)
  AND NOT (hubspot_owner_id IN (82625923, 423155215, 190030668)
           AND notes_last_updated >= NOW() - INTERVAL '90 days')

The previous version's hubspot_owner_id IN (…) read as target only AE-owned contacts and hard-excluded stale ones; both contradicted the 2026-03-21 stale-AE exception and are gone. qualify_lead unavailable for a list-producing run → halt the list (aggregates may still ship), alert Tim.

---

Quick Reference: HubSpot API Endpoints

Object	Endpoint	Key Operations
Contacts	/crm/v3/objects/contacts	Search, create, update, batch
Companies	/crm/v3/objects/companies	Search, associate to contacts
Deals	/crm/v3/objects/deals	Pipeline, stage history
Engagements	/crm/v3/objects/engagements	Emails, calls, meetings
Properties	/crm/v3/properties/{object}	Custom property CRUD
Associations	/crm/v4/associations/{from}/{to}	Object linking
Search	/crm/v3/objects/{object}/search	Filter + sort (max 10k)

Reference: See reference/api-guide.md for auth, SDK patterns, batch operations.

---

Quick Reference: SQL Object Model

HubSpot Object	SQL Table (typical)	Key Columns	Join Key
Contacts	hubspot.contacts	email, lifecycle_stage, lead_score	contact_id
Companies	hubspot.companies	domain, industry, employee_count	company_id
Deals	hubspot.deals	amount, stage, close_date, pipeline	deal_id
Deal Stages	hubspot.deal_stage_history	stage, timestamp, duration	deal_id
Engagements	hubspot.engagements	type, created_at, contact_id	engagement_id
Owners	hubspot.owners	email, first_name, team	owner_id

Join pattern: contacts → associations → companies/deals (via association tables)

---

Integration Points

Skill	Relationship
crm-integration-skill	Base CRUD patterns, auth setup
data-analysis-skill	Visualization, Streamlit dashboards
sales-revenue-skill	Pipeline metrics, MEDDIC context, forecasting
research-skill	Market/competitive research methodology
cost-metering-skill	Track API calls + Clay enrichment spend
prospect-research-to-cadence-skill	Automated deal flow, Golden Rules filter
deal-momentum-analyzer-skill	Pipeline health scoring

MCP Integration Points

MCP Connector	Tools Available
Epiphan AI (mcp__claude_ai_Epiphan_Ai__)	execute_sql, query_dataset, export_query_csv, get_schema, qualify_lead, enrich_contact, hubspot_search_companies, hubspot_search_contacts, hubspot_search_deals, hubspot_get_company, hubspot_get_contact, hubspot_get_deal, hubspot_update_contact, hubspot_create_list, crm_search_customers, crm_get_customer, crm_get_order, crm_get_customer_orders, analytics_get_device, analytics_search_by_email, ask_agent (AI queries, read-only)
Clay MCP	find-and-enrich-company, find-and-enrich-contacts-at-company, find-and-enrich-list-of-contacts, add-contact-data-points, add-company-data-points, get-task
Apollo	apollo_people_match, apollo_contacts_create, apollo_contacts_search, apollo_organizations_enrich, apollo_mixed_companies_search, apollo_emailer_campaigns_*

---

Common Mistakes

Mistake	Fix
Exceeding 100 requests/10s rate limit	Use batch endpoints, add exponential backoff
Using Search API for >10k results	Switch to SQL warehouse for bulk analytics
Hardcoded property internal names	Fetch property definitions first: GET /crm/v3/properties/{object}
Missing association API for object links	Use v4 associations: POST /crm/v4/associations/{from}/{to}/batch/read
SQL DATEDIFF in Postgres	Use AGE() or EXTRACT(EPOCH FROM ...) — see dialect notes
Not handling HubSpot's hs_object_id	Always include hs_object_id in property requests
Missing phone numbers after enrichment	Use Clay waterfall after Apollo: Apollo first (fast, free), then Clay MCP (find-and-enrich-contacts-at-company → add-contact-data-points → get-task) for phone verification. Clay aggregates 50+ data providers for high match rates.
Scoring model trained on small dataset	Need 200+ closed deals minimum for reliable ML scores
Apollo-only enrichment missing data	Clay MCP as fallback: Create taskId with find-and-enrich-company, then add-contact-data-points for Email/phone/work history, poll results with get-task

---

Workflow Phases

Phase 1: Foundation

1. Read crm://schema.md; confirm the HubSpot tables via list_tables
2. Validate data freshness (sync lag)
3. Only if the MCP cannot reach a dataset: Private App per reference/api-guide.md

Phase 2: Analytics

5. Build ICP validation queries (UC1)
6. Create pipeline velocity dashboard (UC2, UC5)
7. Set up competitive intelligence tracking (UC3)

Phase 3: Intelligence

8. Train lead scoring model on historical deals
9. Deploy scores to HubSpot via API
10. Build enrichment pipelines (Clay → HubSpot)
11. Set up automated alerts and webhooks

Reference: See reference/enrichment-pipelines.md for ML scoring and Clay integration. Reference: See reference/architecture.md for deployment patterns and cost estimates.

<self_healing> success = query ran, artifact delivered, lists gated. partial = artifact delivered but a stage degraded (warehouse lag > 24h flagged, enrichment tier failed, HubSpot write not confirmed, sample too small for the model) — name the stage. error = no artifact (warehouse unreachable after retry, schema mismatch unresolved) or qualify_lead unavailable for a list-producing run → halt the list. Ladder per ~/.claude/skills/epiphan-house-rules-skill/reference/self-healing.md: retry once with backoff (rate limit / timeout) → degrade (SQL-only, skip enrichment) → alert Tim → halt. Append one line per run to ~/.claude/skill-runs/hubspot-revops-skill.jsonl. </self_healing>

Emit Outcome Sidecar

As the final step, write to ~/.claude/skill-analytics/last-outcome-hubspot-revops-skill.json:

{"ts":"[UTC ISO8601]","skill":"hubspot-revops-skill","version":"<frontmatter version>","variant":"default",
 "status":"[success|partial|error]","runtime_ms":[estimated ms from start],
 "metrics":{"queries_executed":[n],"reports_generated":[n],"insights_found":[n],"rows_gated":[n],"excluded_by_qualify":[n],"stale_ae_surfaced":[n]},
 "error":null,"session_id":"[YYYY-MM-DD]"}

Use status "partial" if some stages failed but results were produced; error must name the stage. Use "error" only if no output was generated.