Skip to main content

Documentation Index

Fetch the complete documentation index at: https://docs.nekt.com/llms.txt

Use this file to discover all available pages before exploring further.

​
The problem

You have hundreds of open deals in the pipeline. What does that mean in terms of expected revenue? If you sum up all deal values assuming everyone will close, you get a number nobody believes. The issue: a signup from yesterday that hasn’t even configured their account gets the same weight as a deal that passed through the entire funnel and is about to close. That’s not a forecast, it’s a wish. Applying the global funnel conversion rate doesn’t help either. As we showed in Post 05 and Post 06, conversion varies dramatically by stage and channel. A single rate hides both extremes.

​
What we did

We computed the real conversion rate for each funnel stage using historical data: of all deals that ever reached stage X, what percentage eventually closed-won? Then we applied those probabilities to each open deal in the pipeline, weighted by expected deal value. For deals without a set amount, we used the expected value from our plan distribution (70% Starter + 25% Growth + 5% Custom) instead of a simple average, which better reflects the revenue potential of the pipeline. The result: two numbers side by side.
MetricValue
Naive pipeline (sum all deals, assume 100% close)922 deals
Weighted forecast (each deal × its stage probability)~71 deal equivalents
Gap13x
The gap isn’t a sign of a weak pipeline. It means most deals are concentrated in early stages with near-zero conversion. The weighted number represents the portion that, statistically, has a real chance of closing.

​
What we found

Conversion probability changes dramatically as deals advance through the funnel:

Stage 1: 0%

200 deals that only created an account. None at this stage has ever converted historically. Expected value: zero.

Stage 3: 57%

Deals that connected their first data source. These 32 deals concentrate 27% of the forecast.

Stage 6: 44%

Deals approaching the free tier limit. High probability, converting in an average of 69 days.
We added a second dimension: time to close. Average days from each stage to closed-won, based on historical deals that actually converted.
StageProbabilityAvg days to close
New Workspace0%—
Cloud Setup2.8%67 days
First Source57.1%33 days
First Run16.2%53 days
Complete Pipeline50.0%60 days
80% Free Tier44.2%69 days
The time dimension changes the forecast completely. Deals in advanced stages close in ~33 days. Early-stage deals that eventually convert take 60+ days. A 30-day forecast looks very different from a 90-day forecast.

​
The takeaway

A forecast built on real probabilities per stage is transparent, auditable, and defensible. Each line has a calculated probability, an expected value, and a time window. No gut feeling required. This is still Stage 03 of the maturity model: predictive. Using historical patterns to project future outcomes. Combined with the lead scoring from Post 06, you now know both which deals to focus on and how much revenue to expect from the pipeline.

​
The Skill

Download the file below and add it to your Claude Skills. It will query your HubSpot pipeline data at Nekt and generate the revenue forecast automatically. ⬇ Download Skill file (.md)

​
SQL — Weighted forecast by activation depth

Compute the probability-weighted forecast for each open deal based on historical conversion rates per funnel stage. Replace pipeline and stage IDs with your own: 
WITH deal_depth AS (
    SELECT
        id,
        properties.dealname,
        properties.hs_is_closed_won,
        properties.hs_is_closed_lost,
        COALESCE(
            CAST(properties.amount AS FLOAT64),
            -- Expected value: 70% Starter + 25% Growth + 5% Custom
            0.70 * 745 + 0.25 * 2495 + 0.05 * 15000  -- = 1895
        ) AS deal_value,
        CASE
            WHEN properties.hs_v2_date_entered_STAGE6ID IS NOT NULL THEN 6
            WHEN properties.hs_v2_date_entered_STAGE5ID IS NOT NULL THEN 5
            WHEN properties.hs_v2_date_entered_STAGE4ID IS NOT NULL THEN 4
            WHEN properties.hs_v2_date_entered_STAGE3ID IS NOT NULL THEN 3
            WHEN properties.hs_v2_date_entered_STAGE2ID IS NOT NULL THEN 2
            WHEN properties.hs_v2_date_entered_STAGE1ID IS NOT NULL THEN 1
            ELSE 0
        END AS max_stage
    FROM `nekt_raw.hubspot_deals`
    WHERE properties.pipeline = 'YOUR_PIPELINE_ID'
),
stage_win_rates AS (
    SELECT
        max_stage,
        COUNT(*) AS total,
        COUNT(CASE WHEN hs_is_closed_won = 'true' THEN 1 END) AS won,
        ROUND(1.0 * COUNT(CASE WHEN hs_is_closed_won = 'true' THEN 1 END)
              / COUNT(*), 4) AS win_rate
    FROM deal_depth
    GROUP BY 1
),
stage_velocity AS (
    SELECT
        max_stage,
        ROUND(AVG(CAST(properties.days_to_close AS FLOAT64)), 0) AS avg_days_to_close
    FROM (
        SELECT
            properties.days_to_close,
            CASE
                WHEN properties.hs_v2_date_entered_STAGE6ID IS NOT NULL THEN 6
                WHEN properties.hs_v2_date_entered_STAGE5ID IS NOT NULL THEN 5
                WHEN properties.hs_v2_date_entered_STAGE4ID IS NOT NULL THEN 4
                WHEN properties.hs_v2_date_entered_STAGE3ID IS NOT NULL THEN 3
                WHEN properties.hs_v2_date_entered_STAGE2ID IS NOT NULL THEN 2
                WHEN properties.hs_v2_date_entered_STAGE1ID IS NOT NULL THEN 1
                ELSE 0
            END AS max_stage
        FROM `nekt_raw.hubspot_deals`
        WHERE properties.pipeline = 'YOUR_PIPELINE_ID'
          AND properties.hs_is_closed_won = 'true'
    )
    GROUP BY 1
)
SELECT
    d.max_stage,
    COUNT(*) AS open_deals,
    ROUND(SUM(d.deal_value), 0) AS naive_value,
    s.win_rate AS probability,
    ROUND(SUM(d.deal_value * s.win_rate), 0) AS weighted_value,
    v.avg_days_to_close
FROM deal_depth d
JOIN stage_win_rates s ON s.max_stage = d.max_stage
LEFT JOIN stage_velocity v ON v.max_stage = d.max_stage
WHERE d.hs_is_closed_won = 'false'
  AND d.hs_is_closed_lost = 'false'
GROUP BY d.max_stage, s.win_rate, v.avg_days_to_close
ORDER BY d.max_stage

​
SQL — Forecast by stage x channel

Cross activation depth with acquisition channel for more precise probabilities: 
WITH deal_depth AS (
    SELECT
        id,
        properties.hs_is_closed_won,
        properties.hs_is_closed_lost,
        COALESCE(
            CAST(properties.amount AS FLOAT64),
            0.70 * 745 + 0.25 * 2495 + 0.05 * 15000
        ) AS deal_value,
        CASE
            WHEN properties.hs_analytics_source = 'OFFLINE'
                 AND properties.hs_analytics_source_data_1 = 'INTEGRATION' THEN 'Self-Serve'
            WHEN properties.hs_analytics_source = 'OFFLINE'
                 AND properties.hs_analytics_source_data_1 IN ('IMPORT', 'CRM_UI') THEN 'Sales-Assisted'
            ELSE 'Other'
        END AS channel,
        CASE
            WHEN properties.hs_v2_date_entered_STAGE6ID IS NOT NULL THEN 6
            WHEN properties.hs_v2_date_entered_STAGE5ID IS NOT NULL THEN 5
            WHEN properties.hs_v2_date_entered_STAGE4ID IS NOT NULL THEN 4
            WHEN properties.hs_v2_date_entered_STAGE3ID IS NOT NULL THEN 3
            WHEN properties.hs_v2_date_entered_STAGE2ID IS NOT NULL THEN 2
            WHEN properties.hs_v2_date_entered_STAGE1ID IS NOT NULL THEN 1
            ELSE 0
        END AS max_stage
    FROM `nekt_raw.hubspot_deals`
    WHERE properties.pipeline = 'YOUR_PIPELINE_ID'
),
channel_stage_rates AS (
    SELECT
        channel, max_stage,
        COUNT(*) AS total,
        COUNT(CASE WHEN hs_is_closed_won = 'true' THEN 1 END) AS won,
        ROUND(1.0 * COUNT(CASE WHEN hs_is_closed_won = 'true' THEN 1 END)
              / COUNT(*), 4) AS win_rate
    FROM deal_depth
    GROUP BY 1, 2
    HAVING COUNT(*) >= 5
)
SELECT
    d.channel, d.max_stage,
    COUNT(*) AS open_deals,
    ROUND(SUM(d.deal_value), 0) AS naive_value,
    cs.win_rate AS probability,
    ROUND(SUM(d.deal_value * COALESCE(cs.win_rate, 0)), 0) AS weighted_value
FROM deal_depth d
LEFT JOIN channel_stage_rates cs
    ON cs.channel = d.channel AND cs.max_stage = d.max_stage
WHERE d.hs_is_closed_won = 'false' AND d.hs_is_closed_lost = 'false'
GROUP BY d.channel, d.max_stage, cs.win_rate
HAVING COUNT(*) >= 3
ORDER BY d.channel, d.max_stage

​
Variations

Compare two extremes: conservative (all deals in denominator) vs optimistic (only closed deals in denominator). Reality is between the two:
-- Conservative: includes open deals in denominator (understates probability)
-- Optimistic: only closed deals in denominator (overstates probability)
-- Run both and present as a range
WITH deal_depth AS (
    SELECT id, properties.hs_is_closed_won, properties.hs_is_closed_lost,
        COALESCE(TRY_CAST(properties.amount AS DOUBLE),
                 0.70 * 745 + 0.25 * 2495 + 0.05 * 15000) AS deal_value,
        CASE
            WHEN properties.hs_v2_date_entered_STAGE6ID IS NOT NULL THEN 6
            WHEN properties.hs_v2_date_entered_STAGE5ID IS NOT NULL THEN 5
            WHEN properties.hs_v2_date_entered_STAGE4ID IS NOT NULL THEN 4
            WHEN properties.hs_v2_date_entered_STAGE3ID IS NOT NULL THEN 3
            WHEN properties.hs_v2_date_entered_STAGE2ID IS NOT NULL THEN 2
            WHEN properties.hs_v2_date_entered_STAGE1ID IS NOT NULL THEN 1
            ELSE 0
        END AS max_stage
    FROM nekt_raw.hubspot_deals
    WHERE properties.pipeline = 'YOUR_PIPELINE_ID'
),
rates AS (
    SELECT max_stage,
        ROUND(1.0 * COUNT(CASE WHEN hs_is_closed_won = 'true' THEN 1 END)
              / COUNT(*), 4) AS conservative_rate,
        ROUND(1.0 * COUNT(CASE WHEN hs_is_closed_won = 'true' THEN 1 END)
              / NULLIF(COUNT(CASE WHEN hs_is_closed_won = 'true'
                                  OR hs_is_closed_lost = 'true' THEN 1 END), 0), 4) AS optimistic_rate
    FROM deal_depth GROUP BY 1
)
SELECT d.max_stage, COUNT(*) AS open_deals,
    r.conservative_rate, r.optimistic_rate,
    ROUND(SUM(d.deal_value * r.conservative_rate), 0) AS conservative_forecast,
    ROUND(SUM(d.deal_value * COALESCE(r.optimistic_rate, 0)), 0) AS optimistic_forecast
FROM deal_depth d JOIN rates r ON r.max_stage = d.max_stage
WHERE d.hs_is_closed_won = 'false' AND d.hs_is_closed_lost = 'false'
GROUP BY d.max_stage, r.conservative_rate, r.optimistic_rate
ORDER BY d.max_stage
Estimate how much revenue should land in each time window based on average days to close per stage:
-- Uses historical avg days to close from each stage
-- to bucket the weighted forecast into time windows
WITH deal_depth AS (
    SELECT id,
        COALESCE(TRY_CAST(properties.amount AS DOUBLE),
                 0.70 * 745 + 0.25 * 2495 + 0.05 * 15000) AS deal_value,
        TRY_CAST(properties.days_to_close AS DOUBLE) AS days_to_close,
        DATE_DIFF('day', TRY_CAST(properties.createdate AS TIMESTAMP),
                  CURRENT_TIMESTAMP) AS deal_age_days,
        CASE
            WHEN properties.hs_v2_date_entered_STAGE6ID IS NOT NULL THEN 6
            WHEN properties.hs_v2_date_entered_STAGE5ID IS NOT NULL THEN 5
            WHEN properties.hs_v2_date_entered_STAGE4ID IS NOT NULL THEN 4
            WHEN properties.hs_v2_date_entered_STAGE3ID IS NOT NULL THEN 3
            WHEN properties.hs_v2_date_entered_STAGE2ID IS NOT NULL THEN 2
            WHEN properties.hs_v2_date_entered_STAGE1ID IS NOT NULL THEN 1
            ELSE 0
        END AS max_stage,
        properties.hs_is_closed_won, properties.hs_is_closed_lost
    FROM nekt_raw.hubspot_deals
    WHERE properties.pipeline = 'YOUR_PIPELINE_ID'
),
stage_stats AS (
    SELECT max_stage,
        ROUND(1.0 * COUNT(CASE WHEN hs_is_closed_won = 'true' THEN 1 END)
              / COUNT(*), 4) AS win_rate,
        ROUND(AVG(CASE WHEN hs_is_closed_won = 'true' THEN days_to_close END), 0) AS avg_days
    FROM deal_depth GROUP BY 1
)
SELECT
    CASE
        WHEN (s.avg_days - d.deal_age_days) <= 30 THEN 'Next 30 days'
        WHEN (s.avg_days - d.deal_age_days) <= 60 THEN '30-60 days'
        WHEN (s.avg_days - d.deal_age_days) <= 90 THEN '60-90 days'
        ELSE '90+ days'
    END AS time_window,
    COUNT(*) AS deals,
    ROUND(SUM(d.deal_value * s.win_rate), 0) AS weighted_forecast
FROM deal_depth d
JOIN stage_stats s ON s.max_stage = d.max_stage
WHERE d.hs_is_closed_won = 'false' AND d.hs_is_closed_lost = 'false'
GROUP BY 1 ORDER BY 1
Compare the forecast against what actually closed in recent months to calibrate the model:
SELECT
    SUBSTR(properties.closedate, 1, 7) AS month,
    COUNT(*) AS deals_won,
    ROUND(SUM(TRY_CAST(properties.amount AS DOUBLE)), 0) AS revenue,
    ROUND(AVG(TRY_CAST(properties.amount AS DOUBLE)), 0) AS avg_deal_size
FROM nekt_raw.hubspot_deals
WHERE properties.pipeline = 'YOUR_PIPELINE_ID'
  AND properties.hs_is_closed_won = 'true'
  AND properties.closedate >= DATE_FORMAT(DATE_ADD('month', -6, CURRENT_DATE), '%Y-%m-01')
GROUP BY 1
ORDER BY 1

​
Watch the walkthrough (PT-BR)

​
Implementation notes

  • Why “activation depth” instead of current dealstage? In HubSpot, won deals move to a “Closed Won” stage. If you use current dealstage, all won deals end up in one bucket and you can’t compute per-stage conversion rates. Activation depth (the highest stage a deal ever reached, via hs_v2_date_entered_*) solves this.
  • Conservative vs optimistic estimates. The denominator matters. Using ALL deals (including open) gives a conservative rate because it treats open deals as if they’ll never close. Using only closed deals (won + lost) gives an optimistic rate because it ignores deals still in progress. The conservative estimate is safer for planning; the optimistic one is useful as an upper bound.
  • Expected deal value uses plan distribution, not simple average. Most closed deals are on the Starter plan, so the historical average (~817) understates the revenue potential. The weighted expected value (70% Starter + 25% Growth + 5% Custom = 1,895) better reflects the mix of plans open deals might convert to.
  • Time to close varies significantly. The average per stage is useful as a reference, but standard deviation is high (0 to 300+ days). For a more precise monthly forecast, consider using the median instead of the mean, or filtering outliers (deals > 180 days).
  • Stage x channel cross adds precision but requires enough closed deals per cell (minimum 5) to be statistically meaningful. Small cells with 2-3 deals produce unreliable rates.
  • Pipeline and stage IDs are specific to each HubSpot account. Use the pipeline overview query to discover yours.
  • The methodology applies to any funnel type. Replace activation stages with your pipeline stages (Lead, MQL, SQL, Opportunity, Negotiation, Won). The logic of “depth reached = conversion probability” is universal.

​
Next up

With scoring to prioritize deals and a forecast to project revenue, the next step is building the first autonomous agent: a system that monitors deals, detects anomalies, and acts on its own.

08 · Deal Health + Agent

Stages 04-05 · Prescriptive/Autonomous — AI flags deals off the winning pattern, suggests next actions, and triggers alerts automatically.