How to Build a Sales Pipeline Report from HubSpot Data
HubSpot gives you a pipeline board. Drag deals between columns, see the total at each stage. It's fine for a standup. Not enough for a pipeline review.
Coverage ratio against quota, stage conversion rates, deals stuck too long — those questions require math HubSpot's report builder doesn't do. You end up exporting to a spreadsheet and rebuilding it next week.
Here are the SQL queries that replace that spreadsheet. I'm assuming your HubSpot data is queryable — via a warehouse sync, Fastero's HubSpot connection, or a local DuckDB store. Tables: deals(id, deal_name, amount, stage, pipeline, owner_id, create_date, close_date, days_in_stage), contacts(id, email, lifecycle_stage, source), owners(id, name, team).
Pipeline snapshot with weighted value
The most basic report, and the one most teams get wrong. A raw snapshot sums deal amounts by stage. A useful one weights each stage by its historical close probability.
SELECT
d.stage,
COUNT(*) AS deal_count,
SUM(d.amount) AS raw_value,
ROUND(AVG(d.amount), 0) AS avg_deal_size,
SUM(d.amount * CASE d.stage
WHEN 'Qualified' THEN 0.10
WHEN 'Discovery' THEN 0.20
WHEN 'Proposal Sent' THEN 0.40
WHEN 'Negotiation' THEN 0.70
WHEN 'Contract Sent' THEN 0.90
ELSE 0
END) AS weighted_value
FROM deals d
WHERE d.pipeline = 'default'
AND d.stage NOT IN ('Closed Won', 'Closed Lost')
GROUP BY d.stage
ORDER BY weighted_value DESC;Those probability weights should come from your own historical data. A $100k deal at 10% probability is a $10k pipeline contribution. Reporting raw totals is how teams tell the board they have 4x coverage when the real number is 1.5x.
Pipeline coverage ratio
Open weighted pipeline divided by quota. Below 3x and sales leaders worry. Above 5x and your pipeline is probably full of dead deals nobody cleaned out.
WITH open_pipeline AS (
SELECT
o.name AS rep,
SUM(d.amount * CASE d.stage
WHEN 'Qualified' THEN 0.10
WHEN 'Discovery' THEN 0.20
WHEN 'Proposal Sent' THEN 0.40
WHEN 'Negotiation' THEN 0.70
WHEN 'Contract Sent' THEN 0.90
ELSE 0
END) AS weighted_pipeline
FROM deals d
JOIN owners o ON d.owner_id = o.id
WHERE d.stage NOT IN ('Closed Won', 'Closed Lost')
AND d.close_date >= DATE_TRUNC('quarter', CURRENT_DATE)
AND d.close_date < DATE_TRUNC('quarter', CURRENT_DATE) + INTERVAL '3 months'
GROUP BY o.name
)
SELECT
p.rep,
p.weighted_pipeline,
q.quota,
ROUND(p.weighted_pipeline / NULLIF(q.quota, 0), 2) AS coverage_ratio
FROM open_pipeline p
JOIN rep_quotas q ON p.rep = q.rep_name
AND q.quarter = DATE_TRUNC('quarter', CURRENT_DATE)
ORDER BY coverage_ratio ASC;Sorted ascending so the reps who need attention show up first. If you don't have a rep_quotas table, hardcode it as a CTE.
Fastero
Connect your database. Ask questions. Get dashboards.
Postgres, BigQuery, Snowflake, and 10+ sources — live-connected, AI-powered, no dashboard builder learning curve.
Try free →Stage velocity and conversion rates
How long do deals sit in each stage, and what percentage advance? Two questions, one query.
WITH stage_transitions AS (
SELECT
d.stage,
COUNT(*) AS deals_entered,
COUNT(CASE WHEN d.stage != 'Closed Lost' THEN 1 END) AS deals_not_lost,
AVG(d.days_in_stage) AS avg_days,
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY d.days_in_stage) AS median_days,
PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY d.days_in_stage) AS p90_days
FROM deals d
WHERE d.create_date >= CURRENT_DATE - INTERVAL '6 months'
AND d.stage NOT IN ('Closed Won')
GROUP BY d.stage
)
SELECT
stage,
deals_entered,
avg_days,
median_days,
p90_days,
ROUND(deals_not_lost::numeric / NULLIF(deals_entered, 0) * 100, 1) AS advance_rate_pct
FROM stage_transitions
ORDER BY avg_days;Median matters more than average — one deal stuck for 90 days will blow up the mean. PERCENTILE_CONT works in Postgres, Snowflake, BigQuery, and DuckDB. MySQL needs PERCENT_RANK instead.
Aging deals — stuck in stage
Deals sitting longer than the p75 for their stage are either dead or need executive intervention.
WITH stage_benchmarks AS (
SELECT
stage,
PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY days_in_stage) AS p75_days
FROM deals
WHERE create_date >= CURRENT_DATE - INTERVAL '12 months'
AND stage NOT IN ('Closed Won', 'Closed Lost')
GROUP BY stage
)
SELECT
d.deal_name,
d.amount,
d.stage,
d.days_in_stage,
b.p75_days AS stage_benchmark,
d.days_in_stage - b.p75_days AS days_over,
o.name AS rep,
d.close_date
FROM deals d
JOIN stage_benchmarks b ON d.stage = b.stage
JOIN owners o ON d.owner_id = o.id
WHERE d.stage NOT IN ('Closed Won', 'Closed Lost')
AND d.days_in_stage > b.p75_days
ORDER BY d.amount DESC;p75 instead of p90 because by the time only 10% of deals trigger the alert, they've been stuck for weeks. Sorted by amount so the big deals surface first.
Win rate by rep, source, and deal size
The report that tells you whether your pipeline actually converts, not just whether it exists.
SELECT
o.name AS rep,
c.source,
CASE
WHEN d.amount < 10000 THEN 'Small (<$10k)'
WHEN d.amount < 50000 THEN 'Mid ($10k-$50k)'
ELSE 'Enterprise ($50k+)'
END AS size_bucket,
COUNT(*) AS total_deals,
COUNT(CASE WHEN d.stage = 'Closed Won' THEN 1 END) AS won,
COUNT(CASE WHEN d.stage = 'Closed Lost' THEN 1 END) AS lost,
ROUND(
COUNT(CASE WHEN d.stage = 'Closed Won' THEN 1 END)::numeric /
NULLIF(COUNT(CASE WHEN d.stage IN ('Closed Won', 'Closed Lost') THEN 1 END), 0) * 100
, 1) AS win_rate_pct,
COALESCE(AVG(CASE WHEN d.stage = 'Closed Won' THEN d.amount END), 0) AS avg_won_deal
FROM deals d
JOIN owners o ON d.owner_id = o.id
LEFT JOIN contacts c ON d.id = c.id
WHERE d.create_date >= CURRENT_DATE - INTERVAL '6 months'
AND d.stage IN ('Closed Won', 'Closed Lost')
GROUP BY o.name, c.source,
CASE
WHEN d.amount < 10000 THEN 'Small (<$10k)'
WHEN d.amount < 50000 THEN 'Mid ($10k-$50k)'
ELSE 'Enterprise ($50k+)'
END
ORDER BY o.name, win_rate_pct DESC;The denominator only counts closed deals (won + lost), not open ones — including open deals deflates win rate for recent cohorts. The source dimension is where it gets interesting: if outbound closes at 15% and inbound at 35%, that changes how much pipeline you need by channel.
Why HubSpot can't build these natively
HubSpot's forecast feature uses deal-level categories (Commit, Best Case, Pipeline), not stage-level probability weights. The funnel report shows deals entered per stage but won't calculate conversion rates or slice by rep. The "deals open longer than N days" filter treats Discovery and Contract Sent the same. None of this is exotic — it's what a VP of Sales asks for every week, and it's why teams end up reporting outside the CRM.
If your data is already in a warehouse, copy these queries and adjust table names. If not, Fastero's HubSpot connection syncs deals, contacts, and owners into a DuckDB store you can query directly, build dashboards on, or push to Slack on a schedule. Cross-join with Stripe for CRM-to-billing reconciliation in the same workspace. No warehouse required.
Try Fastero free — connect HubSpot and get pipeline reports with weighted value, stage velocity, and aging alerts in minutes. No credit card required.