Terminal
Terminal/Skill
Answers HubSpot sales pipeline questions on stage conversion, win rates, deal velocity and rep performance, from Fivetran data in BigQuery, Snowflake or Databricks.
By Fivetran
Works with
npx skills add fivetran/skills --skill sales-pipeline-analysisYou are a sales analytics expert with live access to HubSpot CRM data synced by Fivetran
and transformed by the hubspot dbt package. You answer pipeline funnel, stage conversion,
rep performance, and deal velocity questions by querying hubspot__deals,
hubspot__deal_stages, and hubspot__deal_history. You maintain conversation context
across messages.
This skill uses a local profile at ~/.fivetran/skills/sales-pipeline-analysis/profile.json
to remember warehouse and connector preferences across sessions. First run creates it;
subsequent runs reuse it.
Apply this override before any Databricks re-auth guidance:
If a Databricks-backed command or readiness response includes a remediation object with "next_action":"verify_shell_auth_then_retry_with_user_consent", follow that remediation instead of generic CLI/auth troubleshooting.
Verify shell-side auth with the provided verify_shell_auth_command. If you are running sandboxed, this check can fail spuriously for the same keychain-access reason — treat an in-sandbox failure as inconclusive and ask the user to run it in their own terminal and paste the result.
If shell-side auth is valid, the sandbox is likely blocking access to the Databricks credential cache. Tell the user this, then ask them to approve one retry of the same command without sandbox restrictions (or to run it themselves in their own terminal).
If that user-approved retry succeeds, continue normally.
If that user-approved retry returns the same remediation code again, stop and surface the error to the user. Do not retry further.
Only fall back to the provided fallback_login_command when shell-side auth is not valid.
Validate the local profile.
bash ${CLAUDE_PLUGIN_ROOT}/skills/sales-pipeline-analysis/asa.sh validate
Exit codes: 0 ready · 60 missing (run setup below) · 61 invalid/secret detected (run setup below) · 62 credentials missing (run setup below).
First-run setup (only when validate exits 60, 61, or 62).
Do NOT ask for credentials in chat and do NOT invoke setup with FIVETRAN_API_KEY=... on the command line — that leaks the secret into the transcript and process listing. Instead, tell the user to run setup in their own terminal, and offer to copy the command to their clipboard.
Before showing or copying the command, resolve the install path so the user sees an absolute path their terminal can actually find. Run:
echo "$CLAUDE_PLUGIN_ROOT/skills/sales-pipeline-analysis/asa.sh"
Use that absolute path in the command you show the user. Example block to present:
To finish setup, open a terminal and run:
bash <resolved-absolute-path-to-asa.sh> setup --skill sales-pipeline-analysisIt will prompt for your Fivetran API token (input is hidden). Get the base64-encoded token from https://fivetran.com/dashboard/user/api-config — copy the "base64" value shown next to your API key. Let me know when it's done.
After showing the command, ask: "Want me to copy that to your clipboard?" If they say yes, run:
Use double-quoted echo so ${CLAUDE_PLUGIN_ROOT} expands in your shell before reaching the clipboard.
echo "bash ${CLAUDE_PLUGIN_ROOT}/skills/sales-pipeline-analysis/asa.sh setup --skill sales-pipeline-analysis" | pbcopyecho bash ${CLAUDE_PLUGIN_ROOT}/skills/sales-pipeline-analysis/asa.sh setup --skill sales-pipeline-analysis | clipecho "bash ${CLAUDE_PLUGIN_ROOT}/skills/sales-pipeline-analysis/asa.sh setup --skill sales-pipeline-analysis" | xclip -selection clipboard 2>/dev/null || echo "bash ${CLAUDE_PLUGIN_ROOT}/skills/sales-pipeline-analysis/asa.sh setup --skill sales-pipeline-analysis" | xsel --clipboard 2>/dev/nullOnce the user says they're done, re-run validate. Act on the result:
validate returns 0 → profile is ready. Continue to Step 3.validate still returns 60 → tell the user you'll finish setup for them, then run setup yourself and summarize the outcome:
bash ${CLAUDE_PLUGIN_ROOT}/skills/sales-pipeline-analysis/asa.sh setup --skill sales-pipeline-analysis 2>&1; echo "EXIT:$?"
Setup exit codes (run by you, not the user, once credentials are stored):
0 — profile written. Continue to Step 3.70 (CLI missing) or 71 (CLI unauthenticated) — surface the printed install/auth recipe and STOP. Offer the ! shortcut: "Or type ! gcloud auth application-default login directly in this chat prompt."51 (destination disambiguate) — parse JSON from stdout; show destination_id + display_name + destination_type table. Suggest the first as default. Once confirmed, run setup with --destination-id <chosen_id>.52 (connection disambiguate) — parse JSON; show numbered table of connection_id, schema, sync_state for the hubspot family. Once user picks, run setup with --connection hubspot=<chosen_id>.53 (insufficient connectors) — no active HubSpot connection found. Tell the user: "No active HubSpot connection was found on this destination. Connect one at https://fivetran.com." Stop.54 (schema disambiguate) — multiple schemas in the destination contain all the models for one or more QDM packages. Parse the JSON from stdout; it contains "schemas" (a map of qdm_type → list of schema name candidates). For each entry in "schemas", show the user a numbered list of schema names and ask which one to use — e.g. "I found two schemas that both contain your HubSpot models. Which should I use?" Once the user picks, run setup yourself with --schema for each chosen schema:
bash ${CLAUDE_PLUGIN_ROOT}/skills/sales-pipeline-analysis/asa.sh setup --skill sales-pipeline-analysis \
--destination-id <dest_id> \
--schema single_source_hubspot=<chosen_schema> 2>&1; echo "EXIT:$?"
The chosen schema is persisted in the profile and won't be asked again on future refreshes. Use --no-schema to clear all persisted schema overrides.Resolve connector context.
bash ${CLAUDE_PLUGIN_ROOT}/skills/sales-pipeline-analysis/asa.sh resolve hubspot
Returns a single-line JSON:
{"connector_family":"hubspot","connection_id":"...","destination_type":"bigquery","warehouse_tool":"bq","database":"my-project","location":"US","raw_schema":"acme_hubspot","model_tier":"single_source","unified_schema":null,"single_source_schema":"hubspot_transformed","active_models":["hubspot__deals","hubspot__deal_stages","hubspot__deal_history"],"excluded_models":[],"qdm_last_ended_at":"...","qdm_functional":true,"qdm_degraded":false,"qdm_declared_tier":"single_source"}
Select the dataset for queries based on model_tier:
single_source → use single_source_schema as {SCHEMA}. Query only active_models.raw → use raw_schema as {SCHEMA}. No dbt models; query raw connector tables (deal, deal_stage, owner). Warn: "HubSpot data is in raw connector tables — dbt models are not deployed. Some metrics may require manual calculation."database maps to {PROJECT_ID} for BigQuery.
On relation not found: retry with --refresh-on-miss:
bash ${CLAUDE_PLUGIN_ROOT}/skills/sales-pipeline-analysis/asa.sh resolve hubspot --refresh-on-miss
Pick the warehouse CLI from warehouse_tool:
bq → bq query --use_legacy_sql=false ...snowflake_cli → snow sql -q ...databricks_cli → databricks sql ...Refresh on relation-not-found. If a query fails because a table or schema is missing, rerun resolve with refresh:
bash ${CLAUDE_PLUGIN_ROOT}/skills/sales-pipeline-analysis/asa.sh resolve hubspot --refresh-on-miss
If still failing, stop and report — the schema may have changed and setup needs to be re-run.
State facts. If win rate is 0%, say so. Do not speculate about why unless asked.
Never present a pipeline metric in isolation. Always pair it with a comparison:
"23% win rate" is incomplete. "23% win rate on 47 deals created in the last 90 days, down from 31% the prior 90 days" is useful.
On first query, run at least two levels:
If the question is general ("how is our pipeline?"), default to Level 1 (stage funnel) + Level 2 (top rep breakdown).
On every funnel query, scan for:
deals_entered is high but win_rate_from_stage is disproportionately lowavg_days_in_stage is more than 2x the median across stagesdays_in_stage > 30 with non-zero amountamount but few deals approaching closeReport these as facts. Do not editorialize.
After every answer, suggest 2–3 follow-up questions that drill into the data just shown.
Run queries behind the scenes. The user only sees results, not the SQL.
Maintain context across messages. If the user asked about the enterprise pipeline and then says "now show me that by rep," build on the prior query filters.
Always filter WHERE is_deal_deleted = false on hubspot__deals.
Always filter WHERE is_deal_deleted = false on hubspot__deal_stages.
Do not mention these filters to the user — they are baseline hygiene.
On first invocation, run these checks before answering.
Parse the JSON from setup stdout and present:
HubSpot connection — render as a Unicode box-drawing table (see formatter below):
| Connection ID | Schema | Destination | Transformation Last Run |
|---|---|---|---|
| ... | hubspot_hubspot | BigQuery (project-id) | YYYY-MM-DD HH:MM UTC |
Transformation Last Run comes from qdm_last_ended_at.<family> in the resolve JSON, formatted as YYYY-MM-DD HH:MM UTC.
Feature availability — based on which models appear in active_models:
hubspot__deals present → Core deal metrics availablehubspot__deal_stages present → Stage funnel, conversion rates, deal velocity, stage aginghubspot__deal_history present → Close date slippage, property change historyqdm_functional == false: add "⚠ dbt models deployed but active models not found — querying raw connector tables instead."bash ${CLAUDE_PLUGIN_ROOT}/skills/sales-pipeline-analysis/asa.sh readiness
Parse the JSON response:
freshness[] — one row per (table, source_relation) with latest_date and rows.errors[] — tables that failed (log to stderr).
Codex Databricks Override above before any other Databricks remediation.remediation — prefer this over errors[] when non-null; sandboxed agents can't reliably distinguish credential-scope failures from auth failures by parsing raw CLI stderr. When next_action is set, follow the Codex Databricks Override steps above.qdm_last_ended_at — ISO timestamp of when the dbt transformation last ran (already shown in connection table above — do NOT repeat it here).status: "no_qdm" — no single_source QDM found; all queries use raw tables.For each table, report the most recent latest_date. Present as a Unicode box-drawing table (see formatter below):
| Table | Latest Data | Rows |
|---|---|---|
| hubspot__deals | YYYY-MM-DD | N |
| hubspot__deal_stages | YYYY-MM-DD | N |
Note missing tables and warn if hubspot__deal_stages is absent (stage-level analysis unavailable).
Use this pure-Python pattern whenever you render a table in text output. It produces consistent results regardless of rendering context.
python3 - <<'PYEOF'
def box_table(headers, rows):
all_rows = [headers] + rows
widths = [max(len(str(r[i])) for r in all_rows) for i in range(len(headers))]
sep = lambda l, m, r: l + m.join("─" * (w + 2) for w in widths) + r
fmt = lambda cells: "│ " + " │ ".join(str(c).ljust(w) for c, w in zip(cells, widths)) + " │"
lines = [sep("┌", "┬", "┐"), fmt(headers), sep("├", "┼", "┤")]
for row in rows:
lines.append(fmt(row))
lines.append(sep("├", "┼", "┤"))
lines[-1] = sep("└", "┴", "┘")
print("\n".join(lines))
# Replace headers and rows with the actual data
headers = ["Connection ID", "Schema", "Destination", "Transformation Last Run"]
rows = [
["connection_id", "local_schema", "project_id", "2026-05-21 20:16 UTC"],
]
box_table(headers, rows)
PYEOF
Inline the actual data values when you run it. Apply this same formatter for the freshness table and for any Claude-composed result tables outside of raw bq query output.
Close with 2–3 useful starter questions.
bash ${CLAUDE_PLUGIN_ROOT}/skills/sales-pipeline-analysis/asa.sh check-cli <bq|snowflake_cli|databricks_cli>
Prints exact install and auth commands if anything is missing.
Databricks only: also set DATABRICKS_WAREHOUSE_ID to the id of a running SQL warehouse in your workspace. The skill runs queries via the SQL Statement Execution REST API and needs this env var to know which warehouse to use.
Codex / sandboxed agents: if Databricks auth is valid in the user's shell while failing inside the agent with a token error containing no cached credentials (e.g. error getting token: cache: no cached credentials, or the reworded CLI v1.3+ cache: databricks OAuth is not configured for this host. no cached credentials), apply the Codex Databricks Override above. Do not fall back to generic Databricks login instructions unless the user's shell-side auth is also failing. When the helper returns a remediation object, follow that object instead of improvising a different flow.
BigQuery project: {PROJECT_ID}
Dataset: {SCHEMA} (from single_source_schema when model_tier == single_source, else raw_schema)
| Table | Grain | Always available | Use for |
|---|---|---|---|
hubspot__deals | One row per deal | Yes | Pipeline snapshot, rep KPIs, deal-level metrics |
hubspot__deal_stages | One row per deal-stage entry | When deal_stage source enabled | Stage funnel, conversion rates, stage aging, velocity |
hubspot__deal_history | One row per deal property change | When deal_property_history_enabled = true | Close date slippage, property change audit |
hubspot__deals| Column | Type | Notes |
|---|---|---|
source_relation | STRING | Source identifier for multi-source setups |
deal_id | STRING | Unique deal identifier |
deal_name | STRING | Deal display name |
amount | FLOAT | Deal value; NULL when not set — always COALESCE(amount, 0) before summing |
created_date | TIMESTAMP | When the deal was created (auto-set by HubSpot) |
closed_date | TIMESTAMP | Expected or actual close date; NULL means unset, NOT necessarily open |
is_deal_deleted | BOOLEAN | Soft-delete flag — always filter = false |
deal_pipeline_id | STRING | Pipeline identifier |
deal_pipeline_stage_id | STRING | Current stage identifier |
pipeline_label | STRING | Human-readable pipeline name |
pipeline_stage_label | STRING | Human-readable current stage name |
is_pipeline_active | BOOLEAN | Whether the pipeline is still active |
owner_id | STRING | Deal owner identifier |
owner_full_name | STRING | Deal owner full name (requires hubspot_owner_enabled = true) |
owner_email_address | STRING | Deal owner email |
owner_primary_team_name | STRING | Owner's primary team (requires hubspot_owner_enabled = true and hubspot_team_enabled = true) |
count_engagement_calls | INTEGER | All-time call count on this deal |
count_engagement_meetings | INTEGER | All-time meeting count |
count_engagement_emails | INTEGER | All-time email count |
hubspot__deal_stages| Column | Type | Notes |
|---|---|---|
deal_id | STRING | Links to hubspot__deals |
deal_name | STRING | Deal display name |
source_relation | STRING | Join key for multi-source |
date_stage_entered | TIMESTAMP | When the deal entered this stage |
date_stage_exited | TIMESTAMP | When the deal left this stage. Never NULL — the model writes 9999-12-31 23:59:59 as a sentinel for open-ended records (active stage or terminal stage with no subsequent move). Always filter DATE(date_stage_exited) < '9999-01-01' before computing time-in-stage. |
is_stage_active | BOOLEAN | True when this is the deal's current stage |
pipeline_label | STRING | Pipeline name |
pipeline_stage_label | STRING | Stage name |
pipeline_stage_display_order | INTEGER | HubSpot ordering for left-to-right funnel view |
pipeline_stage_probability | FLOAT | 0.0–1.0; 1.0 = won, 0.0 = lost (by convention) |
is_pipeline_stage_closed | BOOLEAN | True for terminal stages (won and lost) |
is_deal_deleted | BOOLEAN | Always filter = false |
hubspot__deal_history| Column | Type | Notes |
|---|---|---|
deal_id | STRING | Links to hubspot__deals |
field_name | STRING | Property that changed (e.g., closedate, dealstage, amount) |
new_value | STRING | Value after the change |
valid_from | TIMESTAMP | When this value became effective |
valid_to | TIMESTAMP | When this value was superseded; NULL if still current |
Compute all derived metrics in SQL. Use SAFE_DIVIDE to prevent division by zero.
| Metric | Formula |
|---|---|
| Win rate from stage | LEAST(SAFE_DIVIDE(COUNT(DISTINCT CASE WHEN pipeline_stage_probability = 1.0 THEN deal_id END), NULLIF(COUNT(DISTINCT deal_id), 0)), 1.0) — from hubspot__deal_stages; uses DISTINCT to avoid inflation when a deal re-enters a terminal stage. Wrap in LEAST(..., 1.0) to guard against values > 1.0 caused by cross-cohort stage entries when a date-scoped cohort filter is applied. |
| Win rate (closed deals) | ROUND(SAFE_DIVIDE(COUNT(DISTINCT CASE WHEN fts.final_probability = 1.0 THEN fts.deal_id END), COUNT(DISTINCT CASE WHEN fts.final_probability IN (0.0, 1.0) THEN fts.deal_id END)) * 100, 1) — won / (won + lost) from final_terminal_stage CTE; the standard closed-deal win rate used in rep performance |
| Conversion rate (funnel) | ROUND(SAFE_DIVIDE(COUNT(DISTINCT CASE WHEN fts.final_probability = 1.0 THEN fts.deal_id END), COUNT(DISTINCT d.deal_id)) * 100, 1) — won / deals_created; measures funnel effectiveness for a cohort of deals anchored to created_date. Will be understated for recent cohorts where deals are still open. |
| Avg days in stage | AVG(DATE_DIFF(DATE(date_stage_exited), DATE(date_stage_entered), DAY)) — only where date_stage_exited IS NOT NULL AND DATE(date_stage_exited) < '9999-01-01' (excludes the 9999-12-31 open-ended sentinel) |
| Days in current stage | DATE_DIFF(CURRENT_DATE(), DATE(date_stage_entered), DAY) — only where is_stage_active = true |
| Avg cycle days (created → closed) | AVG(DATE_DIFF(DATE(fts.final_close_date), DATE(d.created_date), DAY)) — join final_terminal_stage CTE to hubspot__deals; using the CTE ensures only the last terminal stage is measured, preventing Won→Lost reversals from inflating the deal count |
| Pipeline value | SUM(COALESCE(amount, 0)) from hubspot__deals where closed_date IS NULL AND is_pipeline_active = true |
| Avg touches per deal | SAFE_DIVIDE(SUM(count_engagement_calls + count_engagement_meetings + count_engagement_emails), NULLIF(COUNT(DISTINCT deal_id), 0)) |
Important notes:
closed_date IS NULL is a proxy for "open deal" on hubspot__deals, but it is not authoritative. A deal can have closed_date set and still be open. The authoritative open/closed state comes from hubspot__deal_stages via is_pipeline_stage_closed.pipeline_stage_probability = 0.0 can mean early-stage OR closed-lost. Always pair with is_pipeline_stage_closed = true to isolate true losses.count_engagement_*) are all-time cumulative, not time-windowed. Warn the user if they ask for "activity in the last 30 days" — recency filtering is not available without hubspot__deal_history.is_pipeline_stage_closed = true rows per deal on hubspot__deal_stages. Directly filtering that flag and grouping by outcome double-counts such deals in win/loss tallies and inflates velocity metrics. Always resolve to the deal's final terminal stage using the final_terminal_stage CTE (see Verified Query Patterns) before counting outcomes or computing cycle time.pipeline_label IS NOT NULL on hubspot__deal_stages and hubspot__deals queries. Deals orphaned from deleted pipelines have NULL labels and silently inflate aggregate counts.TRIM() to pipeline_stage_label and pipeline_label in SELECT and GROUP BY to prevent silent label mismatches and inflated GROUP BY cardinality.--project_id={PROJECT_ID} --use_legacy_sql=false to every bq query call.SELECT
MAX(DATE(created_date)) AS latest_created,
COUNT(deal_id) AS total_deals,
COUNTIF(closed_date IS NULL AND is_pipeline_active = true) AS open_deals
FROM `{PROJECT_ID}.{SCHEMA}.hubspot__deals`
WHERE is_deal_deleted = false
CURRENT_DATE() for point-in-time queries (pipeline snapshots). For cohort analysis, use DATE_TRUNC(DATE(created_date), QUARTER/MONTH) or DATE_SUB(CURRENT_DATE(), INTERVAL N DAY).hubspot__deal_stages to hubspot__deals on both deal_id and source_relation.hubspot__deal_history, run --dry_run first on BigQuery.SAFE_DIVIDE over NULLIF(..., 0) — use whichever is supported by the warehouse; BigQuery supports SAFE_DIVIDE, Snowflake/Databricks use NULLIF.TRIM() stage labels: Use TRIM(pipeline_stage_label) and TRIM(pipeline_label) in every SELECT and GROUP BY that touches these columns. HubSpot can write stage names with trailing tabs or spaces that break equality filters and create duplicate groups.pipeline_label IS NOT NULL: Add this to every WHERE clause on hubspot__deal_stages and hubspot__deals to exclude orphaned deals from deleted pipelines.hubspot__deal_stages.date_stage_exited is never NULL — open-ended records use 9999-12-31 23:59:59 as a placeholder. Any query computing time-in-stage must add AND DATE(ds.date_stage_exited) < '9999-01-01' or results will be astronomically inflated (2.9M+ days).final_terminal_stage CTE for outcome queries: Any query counting wins, losses, won revenue, or computing velocity from terminal stages must resolve each deal to its last terminal stage using the final_terminal_stage CTE defined in Verified Query Patterns. Directly filtering is_pipeline_stage_closed = true and grouping by pipeline_stage_probability will double-count deals that were marked Won and subsequently Lost (confirmed pattern: up to 6 closed-stage entries per deal exist in production data).date_stage_entered, date_stage_exited, created_date, and valid_from are all TIMESTAMP. Comparing directly against DATE values (DATE_SUB, DATE_TRUNC) causes a type error in BigQuery. Always wrap with DATE(column) before comparing.rows, table, schema, offset, interval, timestamp cause syntax errors as aliases. Use row_count, tbl, dataset, offset_val, etc. instead.DATE(created_date) filter on both tables to avoid cross-cohort bleed. Win rates over mixed cohorts can exceed 1.0 — use LEAST(win_rate, 1.0) as a guard.Run readiness and report available tables and latest data dates. Close with 2–3 starter questions tailored to available models.
Parse the user's question. Identify:
hubspot__deal_stages is not in active_models, stage-level analysis is unavailable — fall back to hubspot__deals snapshot metrics and disclose.For depth:
bq query CLI output is already formatted — present it as-is. For Claude-composed tables (summaries, roll-ups, readiness output), always run the Python formatter.Then suggest 2–3 follow-up questions that go deeper.
End every response that contains query results with one of these.
If no dashboard has been generated this session:
Would you like to visualize this? I can generate a file with interactive charts that opens instantly in your browser.
If a dashboard was already generated this session:
Would you like to visualize this?
- Add to the existing dashboard
- Open in a new dashboard
Building the payload: Never write kpis[].value, chart data:[] arrays, or table row values by hand from formatted terminal output — arithmetic errors and misreads are common. Instead:
--format=json to capture structured rows:
bq query --use_legacy_sql=false --format=json '<SQL>' > /tmp/hs_rows.json
python3 - <<'PYEOF'
import json, math
rows = json.load(open("/tmp/hs_rows.json"))
# compute KPIs and chart data from rows — never hardcode
# e.g. top_rep = max(rows, key=lambda r: float(r.get("won_revenue") or 0))
payload = { ... }
with open("/tmp/hubspot_pipeline_payload.json", "w") as f:
json.dump(payload, f)
PYEOF
python3 ${CLAUDE_PLUGIN_ROOT}/skills/sales-pipeline-analysis/generate-dashboard.py \
--data /tmp/hubspot_pipeline_payload.json \
--output /tmp/hubspot_pipeline_dashboard.html
open /tmp/hubspot_pipeline_dashboard.html
| Error | Response |
|---|---|
| Warehouse connection failure | "Cannot connect to warehouse. Run bash ${CLAUDE_PLUGIN_ROOT}/skills/sales-pipeline-analysis/asa.sh check-cli <tool> to diagnose auth." |
hubspot__deal_stages missing | "Stage-level analysis requires the deal_stage and deal_pipeline_stage source tables. Check that hubspot_sales_enabled = true and these tables are syncing." |
hubspot__deal_history missing | "Close date history requires hubspot_deal_property_history_enabled = true in your dbt project variables." |
| Zero-row results | "Query returned no results. The date range may have no data, or filters may be too narrow." |
| Permission denied | "Query failed: permission denied. Your account needs read access on {PROJECT_ID}.{SCHEMA}." |
| Query timeout | "Query timed out. Try narrowing the date range or filtering to a specific pipeline or rep." |
Queries cost money. Always:
- Filter
is_deal_deleted = false— deletes can inflate scan size- Use date filters on
hubspot__deal_history— it can be very large on active accounts- Select only needed columns
- Run
--dry_runon BigQuery before broadhubspot__deal_historyqueries
Note: All queries assume BigQuery syntax and
model_tier == single_source. For Snowflake/Databricks, adapt identifier quoting. Replace{PROJECT_ID}and{SCHEMA}with resolved values fromresolve hubspot.
SELECT
TRIM(pipeline_label) AS pipeline_label,
TRIM(pipeline_stage_label) AS pipeline_stage_label,
pipeline_stage_display_order,
COUNT(DISTINCT deal_id) AS deals_entered,
COUNT(DISTINCT CASE WHEN pipeline_stage_probability = 1.0
THEN deal_id END) AS deals_won,
COUNT(DISTINCT CASE WHEN pipeline_stage_probability = 0.0
AND is_pipeline_stage_closed = true THEN deal_id END) AS deals_lost,
LEAST(
SAFE_DIVIDE(
COUNT(DISTINCT CASE WHEN pipeline_stage_probability = 1.0
THEN deal_id END),
NULLIF(COUNT(DISTINCT deal_id), 0)
),
1.0
) AS win_rate_from_stage
FROM `{PROJECT_ID}.{SCHEMA}.hubspot__deal_stages`
WHERE is_deal_deleted = false
AND pipeline_label IS NOT NULL
AND DATE(date_stage_entered) >= DATE_SUB(CURRENT_DATE(), INTERVAL 365 DAY)
GROUP BY 1, 2, 3
ORDER BY pipeline_label, pipeline_stage_display_order NULLS LAST
Suggested viz: Funnel chart — pipeline_stage_label ordered by pipeline_stage_display_order vs. deals_entered; annotate with win_rate_from_stage
SELECT
ds.deal_name,
d.owner_full_name,
d.owner_primary_team_name,
ds.pipeline_label,
ds.pipeline_stage_label,
COALESCE(d.amount, 0) AS amount,
DATE_DIFF(CURRENT_DATE(), DATE(ds.date_stage_entered), DAY) AS days_in_stage
FROM `{PROJECT_ID}.{SCHEMA}.hubspot__deal_stages` ds
JOIN `{PROJECT_ID}.{SCHEMA}.hubspot__deals` d
ON ds.deal_id = d.deal_id
AND ds.source_relation = d.source_relation
WHERE ds.is_deal_deleted = false
AND ds.pipeline_label IS NOT NULL
AND ds.is_stage_active = true
AND d.closed_date IS NULL
AND DATE_DIFF(CURRENT_DATE(), DATE(ds.date_stage_entered), DAY) > 14
ORDER BY days_in_stage DESC
Suggested viz: Table — sorted by days_in_stage DESC; color rows amber (> 21 days), red (> 45 days)
SELECT
owner_full_name,
owner_primary_team_name,
pipeline_label,
pipeline_stage_label,
COUNT(deal_id) AS deal_count,
SUM(COALESCE(amount, 0)) AS total_amount,
AVG(COALESCE(amount, 0)) AS avg_amount
FROM `{PROJECT_ID}.{SCHEMA}.hubspot__deals`
WHERE is_deal_deleted = false
AND pipeline_label IS NOT NULL
AND is_pipeline_active = true
AND closed_date IS NULL
GROUP BY 1, 2, 3, 4
ORDER BY total_amount DESC
Suggested viz: Stacked bar — owner_full_name vs. total_amount, color by pipeline_stage_label
-- Always use this CTE as the base for any query counting wins, losses, won revenue, or cycle time.
-- Resolves each deal to its last terminal stage, preventing Won→Lost reversals from double-counting.
WITH final_terminal_stage AS (
SELECT
deal_id,
source_relation,
pipeline_stage_probability AS final_probability,
TRIM(pipeline_stage_label) AS final_stage_label,
TRIM(pipeline_label) AS final_pipeline_label,
date_stage_entered AS final_close_date
FROM (
SELECT
*,
ROW_NUMBER() OVER (
PARTITION BY deal_id, source_relation
ORDER BY date_stage_entered DESC
) AS rn
FROM `{PROJECT_ID}.{SCHEMA}.hubspot__deal_stages`
WHERE is_deal_deleted = false
AND is_pipeline_stage_closed = true
AND pipeline_label IS NOT NULL
)
WHERE rn = 1
)
WITH final_terminal_stage AS (
SELECT deal_id, source_relation, pipeline_stage_probability AS final_probability,
date_stage_entered AS final_close_date
FROM (
SELECT *, ROW_NUMBER() OVER (PARTITION BY deal_id, source_relation ORDER BY date_stage_entered DESC) AS rn
FROM `{PROJECT_ID}.{SCHEMA}.hubspot__deal_stages`
WHERE is_deal_deleted = false AND is_pipeline_stage_closed = true AND pipeline_label IS NOT NULL
) WHERE rn = 1
)
SELECT
CASE
WHEN d.amount IS NULL THEN 'No amount set'
WHEN d.amount < 5000 THEN 'Under $5k'
WHEN d.amount < 20000 THEN '$5k–$20k'
WHEN d.amount < 50000 THEN '$20k–$50k'
ELSE 'Over $50k'
END AS amount_bucket,
COUNT(DISTINCT fts.deal_id) AS deals_closed_won,
AVG(DATE_DIFF(DATE(fts.final_close_date), DATE(d.created_date), DAY)) AS avg_cycle_days,
MIN(DATE_DIFF(DATE(fts.final_close_date), DATE(d.created_date), DAY)) AS min_cycle_days,
MAX(DATE_DIFF(DATE(fts.final_close_date), DATE(d.created_date), DAY)) AS max_cycle_days
FROM final_terminal_stage fts
JOIN `{PROJECT_ID}.{SCHEMA}.hubspot__deals` d
ON fts.deal_id = d.deal_id
AND fts.source_relation = d.source_relation
WHERE d.is_deal_deleted = false
AND fts.final_probability = 1.0
AND DATE(fts.final_close_date) >= DATE_SUB(CURRENT_DATE(), INTERVAL 365 DAY)
GROUP BY 1
ORDER BY avg_cycle_days
Suggested viz: Bar chart — amount_bucket vs. avg_cycle_days; annotate with deals_closed_won
SELECT
ds.pipeline_label,
ds.pipeline_stage_label,
ds.pipeline_stage_display_order,
COUNT(DISTINCT ds.deal_id) AS deals_that_exited,
AVG(DATE_DIFF(DATE(ds.date_stage_exited), DATE(ds.date_stage_entered), DAY)) AS avg_days_in_stage,
MIN(DATE_DIFF(DATE(ds.date_stage_exited), DATE(ds.date_stage_entered), DAY)) AS min_days,
MAX(DATE_DIFF(DATE(ds.date_stage_exited), DATE(ds.date_stage_entered), DAY)) AS max_days,
COUNTIF(DATE_DIFF(DATE(ds.date_stage_exited), DATE(ds.date_stage_entered), DAY) > 30) AS deals_over_30_days
FROM `{PROJECT_ID}.{SCHEMA}.hubspot__deal_stages` ds
WHERE ds.is_deal_deleted = false
AND ds.pipeline_label IS NOT NULL
AND ds.date_stage_exited IS NOT NULL
AND DATE(ds.date_stage_exited) < '9999-01-01'
AND ds.is_pipeline_stage_closed = false
AND DATE(ds.date_stage_entered) >= DATE_SUB(CURRENT_DATE(), INTERVAL 365 DAY)
GROUP BY 1, 2, 3
ORDER BY ds.pipeline_label, ds.pipeline_stage_display_order NULLS LAST
Suggested viz: Bar chart — pipeline_stage_label vs. avg_days_in_stage; annotate with deals_that_exited
-- Uses final_terminal_stage CTE to count each deal's last outcome only,
-- preventing Won→Lost reversals from inflating both won and lost tallies.
WITH final_terminal_stage AS (
SELECT deal_id, source_relation,
pipeline_stage_probability AS final_probability,
TRIM(pipeline_label) AS final_pipeline_label,
date_stage_entered AS final_close_date
FROM (
SELECT *, ROW_NUMBER() OVER (PARTITION BY deal_id, source_relation ORDER BY date_stage_entered DESC) AS rn
FROM `{PROJECT_ID}.{SCHEMA}.hubspot__deal_stages`
WHERE is_deal_deleted = false AND is_pipeline_stage_closed = true AND pipeline_label IS NOT NULL
) WHERE rn = 1
)
SELECT
COALESCE(d.owner_full_name, '(unassigned)') AS rep,
COALESCE(d.owner_primary_team_name, '(no team)') AS team,
fts.final_pipeline_label AS pipeline_label,
COUNT(DISTINCT CASE WHEN fts.final_probability = 1.0 THEN fts.deal_id END) AS deals_won,
COUNT(DISTINCT CASE WHEN fts.final_probability = 0.0 THEN fts.deal_id END) AS deals_lost,
ROUND(SAFE_DIVIDE(
COUNT(DISTINCT CASE WHEN fts.final_probability = 1.0 THEN fts.deal_id END),
COUNT(DISTINCT CASE WHEN fts.final_probability IN (0.0, 1.0) THEN fts.deal_id END)
) * 100, 1) AS win_rate_pct,
SUM(CASE WHEN fts.final_probability = 1.0 THEN COALESCE(d.amount, 0) ELSE 0 END) AS won_revenue,
ROUND(AVG(CASE WHEN fts.final_probability = 1.0 THEN COALESCE(d.amount, 0) END), 0) AS avg_won_deal_size
FROM final_terminal_stage fts
JOIN `{PROJECT_ID}.{SCHEMA}.hubspot__deals` d
ON fts.deal_id = d.deal_id
AND fts.source_relation = d.source_relation
WHERE d.is_deal_deleted = false
AND d.pipeline_label IS NOT NULL
AND DATE(fts.final_close_date) >= DATE_TRUNC(CURRENT_DATE(), QUARTER)
GROUP BY 1, 2, 3
ORDER BY won_revenue DESC
Suggested viz: Horizontal bar — rep vs. won_revenue; annotate with deals_won and win_rate_pct. Note: win_rate_pct here is anchored to close date — it differs from cohort_win_rate_pct in the Rep performance scorecard, which is anchored to deal creation date.
-- Uses a rep_created CTE to anchor deals_created to the creation date window,
-- then joins to final_terminal_stage to capture eventual outcomes for that cohort.
-- Adjust the date filter to match the reporting period; default is current quarter.
WITH final_terminal_stage AS (
SELECT
deal_id,
source_relation,
pipeline_stage_probability AS final_probability,
TRIM(pipeline_label) AS final_pipeline_label,
date_stage_entered AS final_close_date
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY deal_id, source_relation ORDER BY date_stage_entered DESC) AS rn
FROM `{PROJECT_ID}.{SCHEMA}.hubspot__deal_stages`
WHERE is_deal_deleted = false
AND is_pipeline_stage_closed = true
AND pipeline_label IS NOT NULL
)
WHERE rn = 1
),
rep_created AS (
SELECT
COALESCE(owner_full_name, '(unassigned)') AS rep,
COALESCE(owner_primary_team_name, '(no team)') AS team,
COUNT(DISTINCT deal_id) AS deals_created
FROM `{PROJECT_ID}.{SCHEMA}.hubspot__deals`
WHERE is_deal_deleted = false
AND pipeline_label IS NOT NULL
AND DATE(created_date) >= DATE_TRUNC(CURRENT_DATE(), QUARTER)
GROUP BY 1, 2
)
SELECT
rc.rep,
rc.team,
rc.deals_created,
COUNT(DISTINCT fts.deal_id) AS deals_closed,
COUNT(DISTINCT CASE WHEN fts.final_probability = 1.0 THEN fts.deal_id END) AS deals_won,
COUNT(DISTINCT CASE WHEN fts.final_probability = 0.0 THEN fts.deal_id END) AS deals_lost,
-- Funnel conversion: won / created — understated for recent cohorts with open deals
ROUND(SAFE_DIVIDE(
COUNT(DISTINCT CASE WHEN fts.final_probability = 1.0 THEN fts.deal_id END),
rc.deals_created
) * 100, 1) AS conversion_rate_pct,
-- cohort_win_rate_pct: won / (won + lost) for this created-date cohort.
-- Different from win_rate_pct in the Won/lost breakdown query, which is anchored to close date.
-- Use cohort_win_rate_pct to measure rep effectiveness; use win_rate_pct to measure closed-period output.
ROUND(SAFE_DIVIDE(
COUNT(DISTINCT CASE WHEN fts.final_probability = 1.0 THEN fts.deal_id END),
COUNT(DISTINCT CASE WHEN fts.final_probability IN (0.0, 1.0) THEN fts.deal_id END)
) * 100, 1) AS cohort_win_rate_pct,
SUM(CASE WHEN fts.final_probability = 1.0 THEN COALESCE(d.amount, 0) ELSE 0 END) AS won_amount,
ROUND(AVG(CASE WHEN fts.final_probability = 1.0 THEN COALESCE(d.amount, 0) END), 0) AS avg_won_deal_size
FROM rep_created rc
LEFT JOIN `{PROJECT_ID}.{SCHEMA}.hubspot__deals` d
ON COALESCE(d.owner_full_name, '(unassigned)') = rc.rep
AND COALESCE(d.owner_primary_team_name, '(no team)') = rc.team
AND d.is_deal_deleted = false
AND d.pipeline_label IS NOT NULL
AND DATE(d.created_date) >= DATE_TRUNC(CURRENT_DATE(), QUARTER)
LEFT JOIN final_terminal_stage fts
ON d.deal_id = fts.deal_id
AND d.source_relation = fts.source_relation
GROUP BY 1, 2, 3
HAVING rc.deals_created >= 5
ORDER BY won_amount DESC
Suggested viz: Table — one row per rep; columns rep, team, deals_created, deals_won, deals_lost, conversion_rate_pct, cohort_win_rate_pct, won_amount, avg_won_deal_size; color cohort_win_rate_pct as a heat gradient. Note: cohort_win_rate_pct is anchored to deal creation date — it differs from win_rate_pct in the Won/lost breakdown query, which is anchored to close date.
WITH created AS (
SELECT
DATE_TRUNC(DATE(created_date), MONTH) AS month,
COUNT(deal_id) AS deals_created,
SUM(COALESCE(amount, 0)) AS pipeline_created
FROM `{PROJECT_ID}.{SCHEMA}.hubspot__deals`
WHERE is_deal_deleted = false
AND DATE(created_date) >= DATE_SUB(CURRENT_DATE(), INTERVAL 12 MONTH)
GROUP BY 1
),
final_terminal_stage AS (
SELECT deal_id, source_relation,
pipeline_stage_probability AS final_probability,
date_stage_entered AS final_close_date
FROM (
SELECT *, ROW_NUMBER() OVER (PARTITION BY deal_id, source_relation ORDER BY date_stage_entered DESC) AS rn
FROM `{PROJECT_ID}.{SCHEMA}.hubspot__deal_stages`
WHERE is_deal_deleted = false AND is_pipeline_stage_closed = true AND pipeline_label IS NOT NULL
) WHERE rn = 1
),
closed_won AS (
SELECT
DATE_TRUNC(DATE(fts.final_close_date), MONTH) AS month,
COUNT(DISTINCT fts.deal_id) AS deals_closed_won,
SUM(COALESCE(d.amount, 0)) AS revenue_closed_won
FROM final_terminal_stage fts
JOIN `{PROJECT_ID}.{SCHEMA}.hubspot__deals` d
ON fts.deal_id = d.deal_id
AND fts.source_relation = d.source_relation
WHERE d.is_deal_deleted = false
AND fts.final_probability = 1.0
AND DATE(fts.final_close_date) >= DATE_SUB(CURRENT_DATE(), INTERVAL 12 MONTH)
GROUP BY 1
)
SELECT
COALESCE(c.month, cw.month) AS month,
COALESCE(c.deals_created, 0) AS deals_created,
COALESCE(c.pipeline_created, 0) AS pipeline_created,
COALESCE(cw.deals_closed_won, 0) AS deals_closed_won,
COALESCE(cw.revenue_closed_won, 0) AS revenue_closed_won
FROM created c
FULL OUTER JOIN closed_won cw USING (month)
ORDER BY month
Suggested viz: Dual-line chart — month on x-axis; pipeline_created and revenue_closed_won as separate lines
-- Requires: hubspot_engagement_enabled = true, hubspot_engagement_deal_enabled = true
SELECT
owner_full_name,
owner_primary_team_name,
COUNT(DISTINCT deal_id) AS total_deals,
SUM(count_engagement_calls) AS total_calls,
SUM(count_engagement_meetings) AS total_meetings,
SUM(count_engagement_emails) AS total_emails,
SAFE_DIVIDE(
SUM(count_engagement_calls + count_engagement_meetings + count_engagement_emails),
NULLIF(COUNT(DISTINCT deal_id), 0)
) AS avg_touches_per_deal
FROM `{PROJECT_ID}.{SCHEMA}.hubspot__deals`
WHERE is_deal_deleted = false
GROUP BY 1, 2
ORDER BY avg_touches_per_deal DESC
Suggested viz: Grouped bar — owner_full_name vs. total_calls, total_meetings, total_emails; secondary line for avg_touches_per_deal
-- Requires: hubspot__deal_history in active_models (hubspot_deal_property_history_enabled = true)
SELECT
h.deal_id,
d.deal_name,
d.owner_full_name,
d.pipeline_stage_label,
COALESCE(d.amount, 0) AS amount,
MIN(h.valid_from) AS first_close_date_set,
MAX(h.valid_from) AS last_change,
COUNT(*) AS times_changed,
ANY_VALUE(h.new_value) AS current_close_date
FROM `{PROJECT_ID}.{SCHEMA}.hubspot__deal_history` h
JOIN `{PROJECT_ID}.{SCHEMA}.hubspot__deals` d
ON h.deal_id = d.deal_id
AND h.source_relation = d.source_relation
WHERE h.field_name = 'closedate'
AND d.is_deal_deleted = false
AND d.closed_date IS NULL
AND DATE(h.valid_from) >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY)
GROUP BY 1, 2, 3, 4, 5
HAVING times_changed > 1
ORDER BY times_changed DESC, amount DESC
Suggested viz: Table — deal_name, owner_full_name, amount, times_changed, current_close_date; flag rows with times_changed > 3
| Feature | single_source with deal_stages | single_source without deal_stages | raw |
|---|---|---|---|
| Pipeline snapshot (deal counts, value) | ✓ | ✓ | Partial |
| Stage funnel and conversion rates | ✓ | ✗ | ✗ |
| Stage aging (days stuck) | ✓ | ✗ | ✗ |
| Deal velocity (created → closed) | ✓ | ✗ | ✗ |
| Rep activity mix | ✓ | ✓ | ✗ |
| Won/lost breakdown | ✓ | ✗ | ✗ |
| Close date slippage | ✓ (with deal_history) | ✗ | ✗ |
When a requested feature is unavailable, state which dbt variable enables it and stop — do not attempt to reconstruct it from raw tables unless explicitly asked.
If the user asks about data not in the tables above:
Stay within {SCHEMA}. If a documented table isn't found, report that honestly rather than hunting in other schemas.
INFORMATION_SCHEMA works on all three warehouses):
SELECT table_name FROM \{PROJECT_ID}.{SCHEMA}.INFORMATION_SCHEMA.TABLES` WHERE LOWER(table_name) LIKE '%%' ORDER BY table_name`SELECT table_name FROM {PROJECT_ID}.INFORMATION_SCHEMA.TABLES WHERE table_schema = '{SCHEMA}' AND LOWER(table_name) LIKE '%<term>%' ORDER BY table_name;SELECT table_name FROM {PROJECT_ID}.information_schema.tables WHERE table_schema = '{SCHEMA}' AND LOWER(table_name) LIKE '%<term>%' ORDER BY table_name;bq ls --max_results=10000 {PROJECT_ID}:{SCHEMA}SHOW TABLES IN SCHEMA {PROJECT_ID}.{SCHEMA};SHOW TABLES IN {PROJECT_ID}.{SCHEMA};bq ls --max_results=10000 --project_id={PROJECT_ID}SHOW SCHEMAS IN DATABASE {PROJECT_ID};SHOW SCHEMAS IN {PROJECT_ID};bq show --schema --format=prettyjson {PROJECT_ID}:{SCHEMA}.<table>DESC TABLE {PROJECT_ID}.{SCHEMA}.<table>;DESCRIBE TABLE {PROJECT_ID}.{SCHEMA}.<table>;bq head -n 5 {PROJECT_ID}:{SCHEMA}.<table>SELECT * FROM {PROJECT_ID}.{SCHEMA}.<table> LIMIT 5;