Deal Health + Agent

The problem

You built the funnel, the scoring, the forecast. But all of it still depends on someone opening the dashboard, reading the numbers, and deciding what to do. A deal sitting in the same stage for 90 days doesn’t raise a flag. A high-scoring lead with no meeting scheduled doesn’t trigger an alert. The intelligence is there, but nobody acts on it until it’s too late. The difference between intelligence and autonomy is who initiates the action: the human or the system.

What we did

We created a Deal Health Score that classifies every open deal into one of four levels based on how long it has been in its current funnel stage:

Health Status	Criteria	Meaning
Healthy	< 30 days in stage	Moving as expected
Watch	30-60 days	Slowing down, keep an eye on it
At Risk	60-90 days	Significantly slower than average
Critical	90+ days	Stuck, likely needs intervention

Then we built automated alerts that flag deals requiring action, grouped by type of recommended intervention.

What we found

18% Healthy

Only 18% of open deals are progressing within normal timeframes. The majority of the pipeline needs attention.

30% Watch

Deals slowing down but still within an acceptable window. Monitoring these prevents them from becoming critical.

52% At Risk or Critical

More than half the pipeline is stalled. Without automated alerts, these deals would go unnoticed until the next manual review.

The alerts identified three categories of deals that need action:

Never advanced - deals created 60+ days ago that never moved past the first stage. Likely abandoned.
Stalled mid-funnel - deals that advanced but have been stuck in their current stage for 30+ days.
Post-meeting stall - deals where a meeting happened but no progress followed. Warm leads cooling off.

Each alert can generate a Slack message, an email, or a CRM task. The agent runs on a defined schedule (daily, weekly) and notifies when something needs attention. Nobody needs to remember to open the dashboard.

The takeaway

This is the jump from Stage 03 (Predictive) to Stages 04-05 (Prescriptive/Autonomous). In Post 06 and Post 07, the system answers when you ask. Here, it tells you before you ask. The “agent” is simply this Skill running on a schedule. No complex infrastructure, no ML model. A prompt, SQL, and a cron job.

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 health score and alerts automatically. ⬇ Download Skill file (.md)

SQL — Deal Health Score

Classify every open deal by time in current stage. Replace pipeline and stage IDs with your own:

Nekt Express / GCP (BigQuery)
AWS (Amazon Athena)

SELECT
    properties.dealname,
    properties.dealstage,
    DATE_DIFF(
        CAST(SUBSTR(properties.hs_v2_date_entered_current_stage, 1, 10) AS DATE),
        CURRENT_DATE(), DAY) AS days_in_stage,
    DATE_DIFF(
        CAST(SUBSTR(properties.createdate, 1, 10) AS DATE),
        CURRENT_DATE(), DAY) AS deal_age_days,
    CASE
        WHEN DATE_DIFF(
            CAST(SUBSTR(properties.hs_v2_date_entered_current_stage, 1, 10) AS DATE),
            CURRENT_DATE(), DAY) > 90 THEN 'Critical'
        WHEN DATE_DIFF(
            CAST(SUBSTR(properties.hs_v2_date_entered_current_stage, 1, 10) AS DATE),
            CURRENT_DATE(), DAY) > 60 THEN 'At Risk'
        WHEN DATE_DIFF(
            CAST(SUBSTR(properties.hs_v2_date_entered_current_stage, 1, 10) AS DATE),
            CURRENT_DATE(), DAY) > 30 THEN 'Watch'
        ELSE 'Healthy'
    END AS health_status,
    properties.first_meeting_date,
    (CASE WHEN properties.hs_v2_date_entered_STAGE1ID IS NOT NULL THEN 1 ELSE 0 END
    + CASE WHEN properties.hs_v2_date_entered_STAGE2ID IS NOT NULL THEN 1 ELSE 0 END
    + CASE WHEN properties.hs_v2_date_entered_STAGE3ID IS NOT NULL THEN 1 ELSE 0 END
    + CASE WHEN properties.hs_v2_date_entered_STAGE4ID IS NOT NULL THEN 1 ELSE 0 END
    ) AS stages_reached
FROM `nekt_raw.hubspot_deals`
WHERE properties.pipeline = 'YOUR_PIPELINE_ID'
  AND properties.hs_is_closed_won = 'false'
  AND properties.hs_is_closed_lost = 'false'
ORDER BY days_in_stage DESC

SELECT
    properties.dealname,
    properties.dealstage,
    DATE_DIFF('day',
        CAST(SUBSTR(properties.hs_v2_date_entered_current_stage, 1, 10) AS DATE),
        CURRENT_DATE) AS days_in_stage,
    DATE_DIFF('day',
        CAST(SUBSTR(properties.createdate, 1, 10) AS DATE),
        CURRENT_DATE) AS deal_age_days,
    CASE
        WHEN DATE_DIFF('day',
            CAST(SUBSTR(properties.hs_v2_date_entered_current_stage, 1, 10) AS DATE),
            CURRENT_DATE) > 90 THEN 'Critical'
        WHEN DATE_DIFF('day',
            CAST(SUBSTR(properties.hs_v2_date_entered_current_stage, 1, 10) AS DATE),
            CURRENT_DATE) > 60 THEN 'At Risk'
        WHEN DATE_DIFF('day',
            CAST(SUBSTR(properties.hs_v2_date_entered_current_stage, 1, 10) AS DATE),
            CURRENT_DATE) > 30 THEN 'Watch'
        ELSE 'Healthy'
    END AS health_status,
    properties.first_meeting_date,
    (CASE WHEN properties.hs_v2_date_entered_STAGE1ID IS NOT NULL THEN 1 ELSE 0 END
    + CASE WHEN properties.hs_v2_date_entered_STAGE2ID IS NOT NULL THEN 1 ELSE 0 END
    + CASE WHEN properties.hs_v2_date_entered_STAGE3ID IS NOT NULL THEN 1 ELSE 0 END
    + CASE WHEN properties.hs_v2_date_entered_STAGE4ID IS NOT NULL THEN 1 ELSE 0 END
    ) AS stages_reached
FROM nekt_raw.hubspot_deals
WHERE properties.pipeline = 'YOUR_PIPELINE_ID'
  AND properties.hs_is_closed_won = 'false'
  AND properties.hs_is_closed_lost = 'false'
ORDER BY days_in_stage DESC

SQL — Health summary

Aggregate the health distribution across the pipeline:

Nekt Express / GCP (BigQuery)
AWS (Amazon Athena)

SELECT
    CASE
        WHEN DATE_DIFF(
            CAST(SUBSTR(properties.hs_v2_date_entered_current_stage, 1, 10) AS DATE),
            CURRENT_DATE(), DAY) > 90 THEN 'Critical (90+ days)'
        WHEN DATE_DIFF(
            CAST(SUBSTR(properties.hs_v2_date_entered_current_stage, 1, 10) AS DATE),
            CURRENT_DATE(), DAY) > 60 THEN 'At Risk (60-90 days)'
        WHEN DATE_DIFF(
            CAST(SUBSTR(properties.hs_v2_date_entered_current_stage, 1, 10) AS DATE),
            CURRENT_DATE(), DAY) > 30 THEN 'Watch (30-60 days)'
        ELSE 'Healthy (< 30 days)'
    END AS health_status,
    COUNT(*) AS deals,
    ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 1) AS pct_pipeline
FROM `nekt_raw.hubspot_deals`
WHERE properties.pipeline = 'YOUR_PIPELINE_ID'
  AND properties.hs_is_closed_won = 'false'
  AND properties.hs_is_closed_lost = 'false'
GROUP BY 1
ORDER BY 1

SELECT
    CASE
        WHEN DATE_DIFF('day',
            CAST(SUBSTR(properties.hs_v2_date_entered_current_stage, 1, 10) AS DATE),
            CURRENT_DATE) > 90 THEN 'Critical (90+ days)'
        WHEN DATE_DIFF('day',
            CAST(SUBSTR(properties.hs_v2_date_entered_current_stage, 1, 10) AS DATE),
            CURRENT_DATE) > 60 THEN 'At Risk (60-90 days)'
        WHEN DATE_DIFF('day',
            CAST(SUBSTR(properties.hs_v2_date_entered_current_stage, 1, 10) AS DATE),
            CURRENT_DATE) > 30 THEN 'Watch (30-60 days)'
        ELSE 'Healthy (< 30 days)'
    END AS health_status,
    COUNT(*) AS deals,
    ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 1) AS pct_pipeline
FROM nekt_raw.hubspot_deals
WHERE properties.pipeline = 'YOUR_PIPELINE_ID'
  AND properties.hs_is_closed_won = 'false'
  AND properties.hs_is_closed_lost = 'false'
GROUP BY 1
ORDER BY 1

SQL — Automated alerts

Flag deals that need action, grouped by alert type:

Nekt Express / GCP (BigQuery)
AWS (Amazon Athena)

-- Alert 1: Deals created 60+ days ago that never advanced
SELECT
    'Never advanced' AS alert_type,
    'Created 60+ days ago, never moved past first stage' AS action,
    properties.dealname,
    properties.dealstage,
    DATE_DIFF(
        CAST(SUBSTR(properties.createdate, 1, 10) AS DATE),
        CURRENT_DATE(), DAY) AS days_stalled
FROM `nekt_raw.hubspot_deals`
WHERE properties.pipeline = 'YOUR_PIPELINE_ID'
  AND properties.hs_is_closed_won = 'false'
  AND properties.hs_is_closed_lost = 'false'
  AND DATE_DIFF(
      CAST(SUBSTR(properties.createdate, 1, 10) AS DATE),
      CURRENT_DATE(), DAY) > 60
  AND properties.hs_v2_date_entered_STAGE2ID IS NULL

UNION ALL

-- Alert 2: Deals that advanced but stalled 30+ days
SELECT
    'Stalled mid-funnel' AS alert_type,
    'Advanced in funnel but stuck for 30+ days' AS action,
    properties.dealname,
    properties.dealstage,
    DATE_DIFF(
        CAST(SUBSTR(properties.hs_v2_date_entered_current_stage, 1, 10) AS DATE),
        CURRENT_DATE(), DAY) AS days_stalled
FROM `nekt_raw.hubspot_deals`
WHERE properties.pipeline = 'YOUR_PIPELINE_ID'
  AND properties.hs_is_closed_won = 'false'
  AND properties.hs_is_closed_lost = 'false'
  AND properties.hs_v2_date_entered_STAGE2ID IS NOT NULL
  AND DATE_DIFF(
      CAST(SUBSTR(properties.hs_v2_date_entered_current_stage, 1, 10) AS DATE),
      CURRENT_DATE(), DAY) > 30

UNION ALL

-- Alert 3: Deals with meeting that stalled after
SELECT
    'Post-meeting stall' AS alert_type,
    'Meeting happened but deal stalled' AS action,
    properties.dealname,
    properties.dealstage,
    DATE_DIFF(
        CAST(SUBSTR(properties.hs_v2_date_entered_current_stage, 1, 10) AS DATE),
        CURRENT_DATE(), DAY) AS days_stalled
FROM `nekt_raw.hubspot_deals`
WHERE properties.pipeline = 'YOUR_PIPELINE_ID'
  AND properties.hs_is_closed_won = 'false'
  AND properties.hs_is_closed_lost = 'false'
  AND properties.first_meeting_date IS NOT NULL
  AND DATE_DIFF(
      CAST(SUBSTR(properties.hs_v2_date_entered_current_stage, 1, 10) AS DATE),
      CURRENT_DATE(), DAY) > 30

ORDER BY alert_type, days_stalled DESC

-- Alert 1: Deals created 60+ days ago that never advanced
SELECT
    'Never advanced' AS alert_type,
    'Created 60+ days ago, never moved past first stage' AS action,
    properties.dealname,
    properties.dealstage,
    DATE_DIFF('day',
        CAST(SUBSTR(properties.createdate, 1, 10) AS DATE),
        CURRENT_DATE) AS days_stalled
FROM nekt_raw.hubspot_deals
WHERE properties.pipeline = 'YOUR_PIPELINE_ID'
  AND properties.hs_is_closed_won = 'false'
  AND properties.hs_is_closed_lost = 'false'
  AND DATE_DIFF('day',
      CAST(SUBSTR(properties.createdate, 1, 10) AS DATE),
      CURRENT_DATE) > 60
  AND properties.hs_v2_date_entered_STAGE2ID IS NULL

UNION ALL

-- Alert 2: Deals that advanced but stalled 30+ days
SELECT
    'Stalled mid-funnel' AS alert_type,
    'Advanced in funnel but stuck for 30+ days' AS action,
    properties.dealname,
    properties.dealstage,
    DATE_DIFF('day',
        CAST(SUBSTR(properties.hs_v2_date_entered_current_stage, 1, 10) AS DATE),
        CURRENT_DATE) AS days_stalled
FROM nekt_raw.hubspot_deals
WHERE properties.pipeline = 'YOUR_PIPELINE_ID'
  AND properties.hs_is_closed_won = 'false'
  AND properties.hs_is_closed_lost = 'false'
  AND properties.hs_v2_date_entered_STAGE2ID IS NOT NULL
  AND DATE_DIFF('day',
      CAST(SUBSTR(properties.hs_v2_date_entered_current_stage, 1, 10) AS DATE),
      CURRENT_DATE) > 30

UNION ALL

-- Alert 3: Deals with meeting that stalled after
SELECT
    'Post-meeting stall' AS alert_type,
    'Meeting happened but deal stalled' AS action,
    properties.dealname,
    properties.dealstage,
    DATE_DIFF('day',
        CAST(SUBSTR(properties.hs_v2_date_entered_current_stage, 1, 10) AS DATE),
        CURRENT_DATE) AS days_stalled
FROM nekt_raw.hubspot_deals
WHERE properties.pipeline = 'YOUR_PIPELINE_ID'
  AND properties.hs_is_closed_won = 'false'
  AND properties.hs_is_closed_lost = 'false'
  AND properties.first_meeting_date IS NOT NULL
  AND DATE_DIFF('day',
      CAST(SUBSTR(properties.hs_v2_date_entered_current_stage, 1, 10) AS DATE),
      CURRENT_DATE) > 30

ORDER BY alert_type, days_stalled DESC

Variations

Alert summary for Slack/email

Generate a compact summary ready to send as a notification:

SELECT
    alert_type,
    COUNT(*) AS deals,
    action
FROM (
    SELECT 'Never advanced' AS alert_type,
        'Created 60+ days ago, never advanced' AS action
    FROM nekt_raw.hubspot_deals
    WHERE properties.pipeline = 'YOUR_PIPELINE_ID'
      AND properties.hs_is_closed_won = 'false'
      AND properties.hs_is_closed_lost = 'false'
      AND DATE_DIFF('day',
          CAST(SUBSTR(properties.createdate, 1, 10) AS DATE),
          CURRENT_DATE) > 60
      AND properties.hs_v2_date_entered_STAGE2ID IS NULL
    UNION ALL
    SELECT 'Stalled mid-funnel',
        'Advanced but stuck for 30+ days'
    FROM nekt_raw.hubspot_deals
    WHERE properties.pipeline = 'YOUR_PIPELINE_ID'
      AND properties.hs_is_closed_won = 'false'
      AND properties.hs_is_closed_lost = 'false'
      AND properties.hs_v2_date_entered_STAGE2ID IS NOT NULL
      AND DATE_DIFF('day',
          CAST(SUBSTR(properties.hs_v2_date_entered_current_stage, 1, 10) AS DATE),
          CURRENT_DATE) > 30
    UNION ALL
    SELECT 'Post-meeting stall',
        'Meeting happened but deal stalled'
    FROM nekt_raw.hubspot_deals
    WHERE properties.pipeline = 'YOUR_PIPELINE_ID'
      AND properties.hs_is_closed_won = 'false'
      AND properties.hs_is_closed_lost = 'false'
      AND properties.first_meeting_date IS NOT NULL
      AND DATE_DIFF('day',
          CAST(SUBSTR(properties.hs_v2_date_entered_current_stage, 1, 10) AS DATE),
          CURRENT_DATE) > 30
)
GROUP BY alert_type, action
ORDER BY deals DESC

Health score by acquisition channel

Break down health distribution by how deals entered the pipeline:

SELECT
    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'
        WHEN properties.hs_analytics_source = 'DIRECT_TRAFFIC' THEN 'Direct Traffic'
        ELSE 'Other'
    END AS channel,
    COUNT(*) AS deals,
    COUNT(CASE WHEN DATE_DIFF('day',
        CAST(SUBSTR(properties.hs_v2_date_entered_current_stage, 1, 10) AS DATE),
        CURRENT_DATE) <= 30 THEN 1 END) AS healthy,
    COUNT(CASE WHEN DATE_DIFF('day',
        CAST(SUBSTR(properties.hs_v2_date_entered_current_stage, 1, 10) AS DATE),
        CURRENT_DATE) BETWEEN 31 AND 60 THEN 1 END) AS watch,
    COUNT(CASE WHEN DATE_DIFF('day',
        CAST(SUBSTR(properties.hs_v2_date_entered_current_stage, 1, 10) AS DATE),
        CURRENT_DATE) BETWEEN 61 AND 90 THEN 1 END) AS at_risk,
    COUNT(CASE WHEN DATE_DIFF('day',
        CAST(SUBSTR(properties.hs_v2_date_entered_current_stage, 1, 10) AS DATE),
        CURRENT_DATE) > 90 THEN 1 END) AS critical
FROM nekt_raw.hubspot_deals
WHERE properties.pipeline = 'YOUR_PIPELINE_ID'
  AND properties.hs_is_closed_won = 'false'
  AND properties.hs_is_closed_lost = 'false'
GROUP BY 1
ORDER BY deals DESC

Watch the walkthrough (PT-BR)

Implementation notes

The thresholds (30/60/90 days) are a starting point. Adjust based on your funnel velocity. If your average sales cycle is 120 days, 30 days in a stage may be normal. Use the average time-in-stage for won deals as your baseline.
Alerts are composable. Each alert query is independent. Run only the ones that make sense for your context, add new ones, or adjust criteria. Examples: deals with no activity in X days, deals with a proposal sent but no response, high-value deals stalled.
The agent is a recurring prompt. In practice, the “agent” is this Skill running on a defined schedule (daily or weekly) via Claude schedule or a cron job. The output can be directed to Slack, email, or any webhook.
HubSpot timestamps. The field hs_v2_date_entered_current_stage records when the deal entered its current stage. The format includes milliseconds (2025-10-27T19:37:04.929Z), so we use SUBSTR(..., 1, 10) to extract the date only.
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 stage IDs with your pipeline stages (Lead, MQL, SQL, Opportunity, Negotiation, Won). The logic of “time in stage = deal health” is universal.

The series

This is the final post. Here’s what we built across the 8 posts:

Connected CRM, billing, and ads in one place
Built a funnel the whole company reads the same way
Diagnosed where each channel breaks
Created a lead scoring model with 6 factors
Built a probability-weighted revenue forecast
Set up a health score and an agent that monitors on its own

All of it with SQL and a prompt. The data was already there. It just needed to be in the right place.

Back to series overview

See all 8 posts and how to run the Skills on your own data.

Documentation

Get started

Context

Activate

Workspace

Resources

Deal Health + Agent

The problem

What we did

What we found

18% Healthy

30% Watch

52% At Risk or Critical

The takeaway

The Skill

SQL — Deal Health Score

SQL — Health summary

SQL — Automated alerts

Variations

Watch the walkthrough (PT-BR)

Implementation notes

The series

Back to series overview

​The problem

​What we did

​What we found

18% Healthy

30% Watch

52% At Risk or Critical

​The takeaway

​The Skill

​SQL — Deal Health Score

​SQL — Health summary

​SQL — Automated alerts

​Variations

​Watch the walkthrough (PT-BR)

​Implementation notes

​The series

Back to series overview

The problem

What we did

What we found

The takeaway

The Skill

SQL — Deal Health Score

SQL — Health summary

SQL — Automated alerts

Variations

Watch the walkthrough (PT-BR)

Implementation notes

The series