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. HubSpot Private App
Note: Tim's HubSpot is accessed via the Epiphan CRM MCP connector — no Private App setup needed. All hubspot_* tools are available directly.
Create at Settings → Integrations → Private Apps:
| Scope |
Permission |
Why |
crm.objects.contacts.read/write |
Read/Write |
Contact enrichment |
crm.objects.companies.read |
Read |
Company data |
crm.objects.deals.read/write |
Read/Write |
Pipeline analytics |
crm.schemas.custom.read |
Read |
Custom objects |
crm.objects.owners.read |
Read |
Rep attribution |
timeline |
Read |
Activity data |
2. SQL Replica Access
Discovery questions for your data warehouse:
| Question |
Options |
| Where is HubSpot data replicated? |
Snowflake / BigQuery / Postgres / Redshift |
| What ETL tool syncs it? |
Fivetran / Airbyte / Stitch / HubSpot Data Sync |
| Sync frequency? |
Real-time / Hourly / Daily |
| Schema prefix? |
hubspot. / raw_hubspot. / custom |
3. Python Environment
pip install hubspot-api-client pandas scikit-learn requests
# SDK initialization
from hubspot import HubSpot
client = HubSpot(access_token="pat-na1-xxxxx")
# Or raw requests
import requests
HEADERS = {"Authorization": "Bearer pat-na1-xxxxx", "Content-Type": "application/json"}
BASE = "https://api.hubapi.com"
Core Use Cases
| # |
Use Case |
Input |
Output |
Tools |
| 1 |
ICP Validation |
Contact + company data |
Segment conversion rates |
SQL + Clay |
| 2 |
Lead Scoring |
Historical deals |
Win probability per lead |
SQL + ML + API |
| 3 |
Competitive Intel |
Deal close reasons |
Win/loss by competitor |
SQL + webhook |
| 4 |
Activity Analysis |
Engagement data |
Activity→outcome correlation |
SQL |
| 5 |
Pipeline Forecast |
Open deals + stage history |
Weighted revenue forecast |
SQL |
Use Case Details
UC1 — ICP Validation: Join contacts + companies + deals in SQL, segment by industry/size/geo, compute conversion rates per segment. Feed results to Clay MCP waterfall for enrichment:
find-and-enrich-company or find-and-enrich-contacts-at-company to identify target contactsadd-contact-data-points / add-company-data-points to queue enrichment jobsget-task to poll for results and check state: completed- 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. Deploy scores back to HubSpot as custom property.
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.
Golden Rules for Prospect Quality
Tim's BDR targeting criteria (as of March 2026) — Apply these filters before outreach:
-- Exclude existing customers and channels
WHERE lifecyclestage NOT IN ('customer')
AND custom.first_conversion NOT LIKE '%Pearl%'
AND custom.first_conversion NOT LIKE '%setup%'
AND custom.first_conversion NOT LIKE '%Connect%'
AND custom.first_conversion NOT LIKE '%signup%'
AND device_count < 1
AND is_channel = false
-- Target only AE territories (Lex Evans, Ron Epstein, Phillip Sandler)
AND hubspot_owner_id IN (82625923, 423155215, 190030668)
-- Optionally segment by company size, industry, location
Use this filter in:
- ICP Validation queries (UC1) before Clay enrichment
- Lead scoring model (UC2) training data
- Prospect research cadence (prospect-research-to-cadence-skill)
Note: See phone-verification-waterfall-skill for full Golden Rules implementation with Clay MCP integration.
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 CRM |
hubspot_search_companies, hubspot_search_contacts, hubspot_search_deals, hubspot_get_company, hubspot_get_contact, hubspot_get_deal, crm_search_customers, crm_get_customer, crm_get_order, crm_get_customer_orders, analytics_get_device, analytics_search_by_email, ask_agent (AI queries) |
| 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
- Set up Private App with required scopes
- Confirm SQL replica access and schema
- Run schema discovery queries
- Validate data freshness (sync lag)
Phase 2: Analytics
- Build ICP validation queries (UC1)
- Create pipeline velocity dashboard (UC2, UC5)
- Set up competitive intelligence tracking (UC3)
Phase 3: Intelligence
- Train lead scoring model on historical deals
- Deploy scores to HubSpot via API
- Build enrichment pipelines (Clay → HubSpot)
- 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.
Emit Outcome Sidecar
As the final step, write to ~/.claude/skill-analytics/last-outcome-hubspot-revops.json:
{"ts":"[UTC ISO8601]","skill":"hubspot-revops","version":"1.0.0","variant":"default",
"status":"[success|partial|error]","runtime_ms":[estimated ms from start],
"metrics":{"queries_executed":[n],"reports_generated":[n],"insights_found":[n]},
"error":null,"session_id":"[YYYY-MM-DD]"}
Use status "partial" if some stages failed but results were produced. Use "error" only if no output was generated.
1---2name: hubspot-revops-skill3description: Use when building revenue analytics on HubSpot — SQL warehouse queries, API enrichment pipelines, lead scoring models, pipeline forecasting, competitive intelligence. Triggers on "hubspot analytics", "revops dashboard", "lead scoring", "pipeline forecast", "ICP analysis", "hubspot SQL".4---5
6<objective>
7Build 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.
8</objective>
9
10<quick_start>
111. Create a HubSpot Private App with required CRM scopes (contacts, companies, deals, owners, timeline)
122. Confirm SQL replica access and schema prefix for your data warehouse
133. Run ICP validation query (UC1) to segment conversion rates
144. Build pipeline forecast (UC5) using stage-specific historical win rates
15</quick_start>
16
17<success_criteria>
18- HubSpot Private App authenticated with all required scopes
19- SQL warehouse connected and data freshness validated (sync lag < 24h)
20- At least one use case (ICP, scoring, competitive, activity, forecast) producing results
21- Lead scoring model trained on 200+ historical closed deals with measurable AUC
22- Enrichment pipeline writing scores back to HubSpot without duplicates
23</success_criteria>
24
25# HubSpot RevOps Analytics
26
27Revenue analytics infrastructure on HubSpot API + SQL data warehouse.
28Bridges CRM data → analytics → intelligence products → revenue impact.
29
30**Scope:** HubSpot-specific analytics stack. For basic CRM CRUD, use `crm-integration-skill`. For generic dashboards, use `data-analysis-skill`.
31
32---
33
34## Setup Checklist
35
36### 1. HubSpot Private App
37
38**Note:** Tim's HubSpot is accessed via the Epiphan CRM MCP connector — no Private App setup needed. All hubspot_* tools are available directly.
39
40Create at Settings → Integrations → Private Apps:
41
42| Scope | Permission | Why |
43|-------|-----------|-----|
44| `crm.objects.contacts.read/write` | Read/Write | Contact enrichment |
45| `crm.objects.companies.read` | Read | Company data |
46| `crm.objects.deals.read/write` | Read/Write | Pipeline analytics |
47| `crm.schemas.custom.read` | Read | Custom objects |
48| `crm.objects.owners.read` | Read | Rep attribution |
49| `timeline` | Read | Activity data |
50
51### 2. SQL Replica Access
52
53Discovery questions for your data warehouse:
54
55| Question | Options |
56|----------|---------|
57| Where is HubSpot data replicated? | Snowflake / BigQuery / Postgres / Redshift |
58| What ETL tool syncs it? | Fivetran / Airbyte / Stitch / HubSpot Data Sync |
59| Sync frequency? | Real-time / Hourly / Daily |
60| Schema prefix? | `hubspot.` / `raw_hubspot.` / custom |
61
62### 3. Python Environment
63
64```bash
65pip install hubspot-api-client pandas scikit-learn requests
66```
67
68```python
69# SDK initialization
70from hubspot import HubSpot
71client = HubSpot(access_token="pat-na1-xxxxx")
72
73# Or raw requests
74import requests
75HEADERS = {"Authorization": "Bearer pat-na1-xxxxx", "Content-Type": "application/json"}
76BASE = "https://api.hubapi.com"
77```
78
79---
80
81## Core Use Cases
82
83| # | Use Case | Input | Output | Tools |
84|---|----------|-------|--------|-------|
85| 1 | ICP Validation | Contact + company data | Segment conversion rates | SQL + Clay |
86| 2 | Lead Scoring | Historical deals | Win probability per lead | SQL + ML + API |
87| 3 | Competitive Intel | Deal close reasons | Win/loss by competitor | SQL + webhook |
88| 4 | Activity Analysis | Engagement data | Activity→outcome correlation | SQL |
89| 5 | Pipeline Forecast | Open deals + stage history | Weighted revenue forecast | SQL |
90
91### Use Case Details
92
93**UC1 — ICP Validation:** Join contacts + companies + deals in SQL, segment by industry/size/geo, compute conversion rates per segment. Feed results to Clay MCP waterfall for enrichment:
94 1. `find-and-enrich-company` or `find-and-enrich-contacts-at-company` to identify target contacts
95 2. `add-contact-data-points` / `add-company-data-points` to queue enrichment jobs
96 3. `get-task` to poll for results and check `state: completed`
97 4. Write enriched data back to HubSpot via API or Epiphan CRM integration
98
99 Alternative: Use Apollo MCP (`apollo_people_match`) for direct enrichment without waterfall wait.
100
101**UC2 — Lead Scoring:** Train GradientBoostingClassifier on historical won/lost deals. Features: company size, industry, engagement score, days in pipeline. Deploy scores back to HubSpot as custom property.
102
103**UC3 — Competitive Intel:** Extract competitor mentions from deal `closed_lost_reason`. Build win/loss matrix by competitor. Trigger webhook alerts on competitive displacement patterns.
104
105**UC4 — Activity Analysis:** Correlate email opens, meetings booked, calls logged with deal outcomes. Identify which activities actually move deals forward.
106
107**UC5 — Pipeline Forecast:** Calculate weighted forecast using stage-specific win rates from historical data. Factor in deal age, velocity, and rep performance.
108
109> **Reference:** See `reference/sql-analytics.md` for complete SQL templates per use case.
110
111---
112
113## Golden Rules for Prospect Quality
114
115**Tim's BDR targeting criteria (as of March 2026)** — Apply these filters before outreach:
116
117```sql
118-- Exclude existing customers and channels
119WHERE lifecyclestage NOT IN ('customer')
120 AND custom.first_conversion NOT LIKE '%Pearl%'
121 AND custom.first_conversion NOT LIKE '%setup%'
122 AND custom.first_conversion NOT LIKE '%Connect%'
123 AND custom.first_conversion NOT LIKE '%signup%'
124 AND device_count < 1
125 AND is_channel = false
126
127-- Target only AE territories (Lex Evans, Ron Epstein, Phillip Sandler)
128 AND hubspot_owner_id IN (82625923, 423155215, 190030668)
129
130-- Optionally segment by company size, industry, location
131```
132
133**Use this filter in:**
134- ICP Validation queries (UC1) before Clay enrichment
135- Lead scoring model (UC2) training data
136- Prospect research cadence (prospect-research-to-cadence-skill)
137
138**Note:** See `phone-verification-waterfall-skill` for full Golden Rules implementation with Clay MCP integration.
139
140---
141
142## Quick Reference: HubSpot API Endpoints
143
144| Object | Endpoint | Key Operations |
145|--------|----------|----------------|
146| Contacts | `/crm/v3/objects/contacts` | Search, create, update, batch |
147| Companies | `/crm/v3/objects/companies` | Search, associate to contacts |
148| Deals | `/crm/v3/objects/deals` | Pipeline, stage history |
149| Engagements | `/crm/v3/objects/engagements` | Emails, calls, meetings |
150| Properties | `/crm/v3/properties/{object}` | Custom property CRUD |
151| Associations | `/crm/v4/associations/{from}/{to}` | Object linking |
152| Search | `/crm/v3/objects/{object}/search` | Filter + sort (max 10k) |
153
154> **Reference:** See `reference/api-guide.md` for auth, SDK patterns, batch operations.
155
156---
157
158## Quick Reference: SQL Object Model
159
160| HubSpot Object | SQL Table (typical) | Key Columns | Join Key |
161|----------------|---------------------|-------------|----------|
162| Contacts | `hubspot.contacts` | email, lifecycle_stage, lead_score | contact_id |
163| Companies | `hubspot.companies` | domain, industry, employee_count | company_id |
164| Deals | `hubspot.deals` | amount, stage, close_date, pipeline | deal_id |
165| Deal Stages | `hubspot.deal_stage_history` | stage, timestamp, duration | deal_id |
166| Engagements | `hubspot.engagements` | type, created_at, contact_id | engagement_id |
167| Owners | `hubspot.owners` | email, first_name, team | owner_id |
168
169**Join pattern:** contacts → associations → companies/deals (via association tables)
170
171---
172
173## Integration Points
174
175| Skill | Relationship |
176|-------|-------------|
177| `crm-integration-skill` | Base CRUD patterns, auth setup |
178| `data-analysis-skill` | Visualization, Streamlit dashboards |
179| `sales-revenue-skill` | Pipeline metrics, MEDDIC context, forecasting |
180| `research-skill` | Market/competitive research methodology |
181| `cost-metering-skill` | Track API calls + Clay enrichment spend |
182| `prospect-research-to-cadence-skill` | Automated deal flow, Golden Rules filter |
183| `deal-momentum-analyzer-skill` | Pipeline health scoring |
184
185## MCP Integration Points
186
187| MCP Connector | Tools Available |
188|---------------|----------------|
189| **Epiphan CRM** | hubspot_search_companies, hubspot_search_contacts, hubspot_search_deals, hubspot_get_company, hubspot_get_contact, hubspot_get_deal, crm_search_customers, crm_get_customer, crm_get_order, crm_get_customer_orders, analytics_get_device, analytics_search_by_email, ask_agent (AI queries) |
190| **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 |
191| **Apollo** | apollo_people_match, apollo_contacts_create, apollo_contacts_search, apollo_organizations_enrich, apollo_mixed_companies_search, apollo_emailer_campaigns_* |
192
193---
194
195## Common Mistakes
196
197| Mistake | Fix |
198|---------|-----|
199| Exceeding 100 requests/10s rate limit | Use batch endpoints, add exponential backoff |
200| Using Search API for >10k results | Switch to SQL warehouse for bulk analytics |
201| Hardcoded property internal names | Fetch property definitions first: `GET /crm/v3/properties/{object}` |
202| Missing association API for object links | Use v4 associations: `POST /crm/v4/associations/{from}/{to}/batch/read` |
203| SQL `DATEDIFF` in Postgres | Use `AGE()` or `EXTRACT(EPOCH FROM ...)` — see dialect notes |
204| Not handling HubSpot's `hs_object_id` | Always include `hs_object_id` in property requests |
205| 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. |
206| Scoring model trained on small dataset | Need 200+ closed deals minimum for reliable ML scores |
207| 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` |
208
209---
210
211## Workflow Phases
212
213### Phase 1: Foundation
2141. Set up Private App with required scopes
2152. Confirm SQL replica access and schema
2163. Run schema discovery queries
2174. Validate data freshness (sync lag)
218
219### Phase 2: Analytics
2205. Build ICP validation queries (UC1)
2216. Create pipeline velocity dashboard (UC2, UC5)
2227. Set up competitive intelligence tracking (UC3)
223
224### Phase 3: Intelligence
2258. Train lead scoring model on historical deals
2269. Deploy scores to HubSpot via API
22710. Build enrichment pipelines (Clay → HubSpot)
22811. Set up automated alerts and webhooks
229
230> **Reference:** See `reference/enrichment-pipelines.md` for ML scoring and Clay integration.
231> **Reference:** See `reference/architecture.md` for deployment patterns and cost estimates.
232
233## Emit Outcome Sidecar
234
235As the final step, write to `~/.claude/skill-analytics/last-outcome-hubspot-revops.json`:
236```json
237{"ts":"[UTC ISO8601]","skill":"hubspot-revops","version":"1.0.0","variant":"default",
238 "status":"[success|partial|error]","runtime_ms":[estimated ms from start],
239 "metrics":{"queries_executed":[n],"reports_generated":[n],"insights_found":[n]},
240 "error":null,"session_id":"[YYYY-MM-DD]"}
241```
242Use status "partial" if some stages failed but results were produced. Use "error" only if no output was generated.
scientiacapital