--- id: casos/saude-de-deals area: RevOps usage: common effort: medium grain: Open deal x day --- # Deal health + alert agent Every open deal classified as healthy, watch or at risk by its last real interaction and its time in stage, with one alert per owner that only reports what changed. ## Executive summary **For:** Sales managers who only find forgotten deals during the forecast review. **What it solves** - A stalled deal shows up the week it stalls, not at the end of the quarter. - Each owner gets only their own deals, with the reason and what to do. - The alert respects each stage's pace and does not fire for nothing. **Why it matters:** A stalled deal inflates the forecast and goes cold without anyone noticing. The CRM has the dates, but nobody opens a report every day. A daily alert with rules per stage turns pipeline hygiene into a routine and makes the forecast more honest. **Business questions it answers** - Which deals have been stalled longer than the stage allows? - Which open deals have no owner or a deactivated owner? - How many deals became at risk this week and how many started moving again? - Which deals have no next activity scheduled? - Which rep has the most deals at risk? **What you need** - CRM connected with deals, stage history, activities, tasks and users - The acceptable time per stage and each pipeline's exceptions, agreed with the team - An output channel: Slack, WhatsApp, Google Chat or a CRM task **What you get** - A table per open deal with status, reasons, days without interaction and days in stage - A daily message per owner with the deals that changed status - A weekly panel of deals entering and leaving risk - An AI agent that answers questions about stalled deals ## Detailed ### The problem A deal can sit in the same stage for months without anyone noticing. Measuring stalled by the last modified date makes deals look alive when an automation only edited a field, and a single 30 day cutoff asks the same of a proposal stage and a signature stage. The alert turns into noise and the team stops reading it. ### Business rules 1. **Stalled means no real interaction.** The stalled yardstick is the last interaction: a completed activity (call, meeting, email, message) or a stage change, whichever is more recent. The last modified date does not work: automation, syncs and field edits change the date without the deal moving. (Avoid: Days stalled = today - last modified date.) 2. **Time in stage comes from the real entry.** Calculate the entry into the current stage from history: hs_v2_date_entered_current_stage or hs_v2_date_entered_ in HubSpot, stage history in other CRMs. The deal's update date only approximates the entry and resets with every edit. (Avoid: Use updated_at as the stage entry date.) 3. **Each stage has its own threshold.** Set the acceptable time per stage and, if possible, per deal size, from history: a good cutoff is the 75th or 90th percentile of the time won deals spent in the stage. In final stages, some teams use a hard rule: above the cutoff, the deal is at risk whatever the rest. (Avoid: A single 30 day cutoff for every stage.) 4. **A future task and automation suspend the alert.** A deal with a meeting or task scheduled in the future is not stalled, even without recent activity. Stages covered by automation (a 7 day WhatsApp cadence, a booking that goes straight to the calendar) have their own deadline, agreed with the team, or the alert chases what the machine is already doing. (Avoid: Alert a deal with a meeting booked two weeks from now.) 5. **Hygiene counts as much as time.** Besides time, flag: no owner or a deactivated owner (matching the owner against the active users list by email), no next activity, overdue task, zero amount, past expected date, expected date pushed two or more times, and owner changes. Each signal is a separate reason, so the owner knows what to do. (Avoid: Show only days stalled.) 6. **Three states, always with a reason.** Classify as healthy, watch or at risk by fixed rules and show every reason that applies. The status given by the manager or by a follow up form can override the rule, with a record of who and when. A 0 to 100 index, if there is one, supports the reading; it is not a probability of closing. (Avoid: A score with no reason, presented as the chance of winning.) 7. **Alert only what changed.** Send each owner the deals that became at risk since the last send and the ones that left risk, not the whole list every day. Keep a send history and do not repeat the same alert within the agreed window. Send on business days, with the holiday calendar. (Avoid: Send every critical deal every day.) 8. **Close the loop.** From the date the alert started, classify each alerted deal as resolved (moved or closed), engaged (had activity but is still in the stage) or still stalled. That is what shows whether the alert works. (Avoid: Measure only how many alerts were sent.) ### Reasons a deal can receive Each reason is a column. The status is the agreed rule over them. - **days since the last interaction above the stage threshold** → No interaction - **days in the current stage above the won deals percentile** → Long stage - **owner blank or deactivated in the users list** → No owner - **no future task or meeting** → No next activity - **task past its due date** → Overdue task - **expected date before today or pushed two or more times** → Broken expected date - **zero or blank amount** → No amount - **two or more owner changes** → Unstable owner ### Definitions | Metric | Definition | |---|---| | Last interaction | the latest date between the last completed activity and the deal's last stage change | | Days without interaction | today - last interaction, in calendar days | | Days in stage | today - entry into the current stage, from the stage history | | Stage threshold | 75th percentile of the time won deals of the last 12 months spent in the stage, by deal size when available | | At risk | open deal above the stage threshold in days in stage or days without interaction, or with an agreed knockout signal | | Entering risk | deal that became at risk since the previous send | | Leaving risk | at risk deal that had a completed activity or changed stage since the previous send | ### Pitfalls - **Nobody read the alert.** A daily list with every critical deal repeated the same names and the team stopped opening it. How to avoid: Send only deals entering and leaving risk. - **False alert in an automated stage.** A stage covered by a 7 day automatic WhatsApp cadence was held to a 2 day deadline and fired an alert every day. How to avoid: Own deadline for automated stages, agreed with the team. - **Conversations outside the CRM.** Service done through the WhatsApp app, outside the CRM, created no activity, and deals in the middle of a conversation showed up as stalled. How to avoid: Bring conversations in as activities or declare the stages where the CRM cannot see the contact. - **Blank owner as text.** The owner came as an empty string, not as null, and orphan deals did not show up in the alert. How to avoid: Treat empty strings as null before testing the owner. - **A merged deal stalled forever.** Deals merged or deleted in the CRM stayed in the raw data and showed up as stalled every day. How to avoid: Exclude merged ids (hs_merged_object_ids in HubSpot) and check the active ids regularly. Validated on real data: In Nekt's own HubSpot account, the deal's last activity and next activity fields come blank on every deal, so the interaction yardstick has to come from meetings, emails and tasks. And about 4 in 10 open deals stuck for more than 30 days in their stage had been modified in the last week: the modified date is no sign the deal moved. ### Acceptance test - [ ] Every open deal in an acquisition pipeline has one row per day with status, days without interaction, days in stage and reasons. - [ ] No deal with a future task or meeting appears as without interaction. - [ ] For one owner and one day, the message sent holds exactly the deals that entered and left risk since the previous send. - [ ] No won or lost deal appears in the table. - [ ] In a sample of 20 at risk deals checked in the CRM, the last interaction and the stage entry match the screen. ### Context document ```text Deal health - Source: health table (open deal x day), with status, days without interaction, days in stage, stage threshold and reasons. - Stalled = no real interaction: a completed activity or a stage change. The last modified date does not count. - Days in stage come from the stage history, never from updated_at. - Threshold per stage: = . Automated stages: = . - A future task or meeting suspends the interaction alert. - Status: healthy, watch or at risk, always with the reasons. A 0 to 100 index, if there is one, is not a probability of closing. - When listing at risk deals, group by owner and show the reason and the last interaction. - To assess the alert's effect, use resolved, engaged and still stalled since . Client exceptions: - : the follow up form status overrides the rule. - : send Monday to Thursday and on Sunday; Friday, Saturday and holidays do not send. ``` ### Variations - **Summary written by AI:** A model reads the day's table and the team playbook and writes the leadership summary, without citing deals outside the table. - **Customer journey:** The same rules on the CS pipeline: days without contact, long stage and zero tasks as churn risk signals. - **Operational workflow:** In lending and post-sale, a deadline per workflow phase instead of a sales stage.