@microsoft/pipeline-health-analyzer

---
name: pipeline-health-analyzer
description: Reviews opportunities in the sales pipeline to identify stuck deals, missing next steps, weak qualification, and unbalanced stages. Use when user asks "how is my pipeline looking", "analyze pipeline health", "which deals are stuck", "pipeline review", "find at-risk deals", "identify stalled opportunities", or "audit my open deals".
metadata:
  author: Dataverse
  version: 1.0.0
  category: sales-analytics
---

# Pipeline Health Analyzer

Sales teams often review pipelines weekly to identify at-risk deals manually. This skill automates that process by analyzing opportunity records against configurable health criteria, flagging issues, and providing prioritized recommendations to improve deal progression.

## Instructions

### Step 1: Determine Analysis Scope
Identify the pipeline scope to analyze:

**Option A - Full Pipeline:**
- All open opportunities (statecode = 0)

**Option B - Filtered View:**
- By owner (specific sales rep or team)
- By time period (opportunities created/modified in range)
- By value threshold (deals above $X)
- By sales stage

**Query Example for Full Pipeline:**
```
SELECT opportunityid, name, estimatedvalue, estimatedclosedate, createdon, modifiedon,
       salesstage, stepname, customerid, accountid, contactid, ownerid,
       budgetstatus, need, purchasetimeframe, purchaseprocess, decisionmaker,
       msdyn_opportunityscore, msdyn_opportunitygrade, msdyn_forecastcategory,
       closeprobability, description, currentsituation, customerneed, customerpainpoints
FROM opportunity
WHERE statecode = 0
```

#### Step 2: Analyze Individual Opportunity Health

For each opportunity, evaluate against these health dimensions:

**2.1 Stuck Deal Detection**
Query activity history for each opportunity:
```
SELECT activityid, activitytypecode, createdon, actualend, statecode
FROM activitypointer
WHERE regardingobjectid = '[opportunityid]'
ORDER BY createdon DESC
```

**Stuck Deal Criteria:**
| Stage | Days Without Activity = Warning | Days Without Activity = Critical |
|-------|--------------------------------|----------------------------------|
| Qualify | 7 days | 14 days |
| Develop | 10 days | 21 days |
| Propose | 5 days | 10 days |
| Close | 3 days | 7 days |

**Flag as Stuck if:**
- No activities in threshold period
- Only automated activities (emails) but no calls/meetings
- Last activity was over 2 weeks ago regardless of stage

**2.2 Missing Next Steps Analysis**
Check for scheduled future activities:
```
SELECT activityid, activitytypecode, scheduledstart, scheduledend, subject, statecode
FROM activitypointer
WHERE regardingobjectid = '[opportunityid]'
AND statecode = 0
```

**Note:** Filter results where scheduledstart > current date. Activity statecodes:
- 0 = Open (scheduled activities)
- 1 = Completed
- 2 = Canceled
- 3 = Scheduled (specific to appointments)

**Flag if:**
- No scheduled future tasks or appointments
- Next scheduled activity is more than 2 weeks away
- Scheduled activity is past due (scheduledend < today, still open)

**2.3 Qualification Weakness Assessment**
Evaluate BANT (Budget, Authority, Need, Timeline) signals:

| Signal | Field | Weak | Moderate | Strong |
|--------|-------|------|----------|--------|
| **Budget** | budgetstatus | 0 (No Budget) | 1 (May Buy) | 2-3 (Can/Will Buy) |
| **Authority** | decisionmaker | NULL/false | - | true |
| **Need** | need | 3 (No need) | 2 (Good to have) | 0-1 (Must/Should have) |
| **Timeline** | purchasetimeframe | 4 (Unknown) | 2-3 (This/Next Year) | 0-1 (Immediate/This Qtr) |
| **Process** | purchaseprocess | 2 (Unknown) | 1 (Committee) | 0 (Individual) |

**Qualification Score Calculation:**
- Strong signal = 2 points
- Moderate signal = 1 point
- Weak signal = 0 points
- Total: 0-10 points (10 = fully qualified)

**Flag as Under-qualified if:**
- Score < 5 for deals in Propose/Close stage
- Budget is unknown for deals > $50K
- No decision maker identified for deals in Committee process

**2.4 Stage Age Analysis**
Calculate time in current stage:
- Use stage transition tracking or estimate from modifiedon
- Compare against expected stage duration benchmarks

**Typical Stage Duration Benchmarks:**
| Stage | Small Deal (<$25K) | Medium Deal ($25K-$100K) | Enterprise (>$100K) |
|-------|-------------------|--------------------------|---------------------|
| Qualify | 7 days | 14 days | 21 days |
| Develop | 14 days | 30 days | 60 days |
| Propose | 7 days | 14 days | 30 days |
| Close | 14 days | 30 days | 45 days |

**Flag if:** Time in stage > 1.5x benchmark

**2.5 Close Date Validity**
Check estimated close date reasonableness:

**Flag if:**
- `estimatedclosedate` is in the past
- `estimatedclosedate` has been pushed out 3+ times (if tracking available)
- Close date is within 7 days but stage is Qualify/Develop
- Close date is > 6 months out but forecast category is "Committed"

**2.6 Competitive Risk Assessment**
Analyze competitive signals from opportunity fields:
- Check `description`, `currentsituation` for competitor mentions
- Evaluate against known competitor patterns

**Flag if:**
- Competitor explicitly mentioned
- "Evaluating alternatives" or similar language detected
- Multiple vendors mentioned

#### Step 3: Pipeline Balance Analysis

**3.1 Stage Distribution**
```
SELECT salesstage, COUNT(opportunityid) as count, SUM(estimatedvalue) as total_value
FROM opportunity
WHERE statecode = 0
GROUP BY salesstage
```

**Healthy Pipeline Ratios:**
| Stage | Ideal Count % | Ideal Value % |
|-------|--------------|---------------|
| Qualify | 40-50% | 30-40% |
| Develop | 25-35% | 25-35% |
| Propose | 15-20% | 20-25% |
| Close | 5-15% | 10-20% |

**Flag Imbalances:**
- **Top-Heavy:** Too many deals in early stages (lead gen working, conversion not)
- **Bottom-Heavy:** Too few deals in early stages (future quarter risk)
- **Propose Jam:** Deals stuck in proposal phase (pricing or competitive issues)

**3.2 Close Date Clustering**
Analyze deals by expected close timing:
```
SELECT estimatedclosedate, COUNT(opportunityid) as count, SUM(estimatedvalue) as total_value
FROM opportunity
WHERE statecode = 0
GROUP BY estimatedclosedate
```

**Flag if:**
- >50% of pipeline value closing in last week of quarter ("hockey stick")
- Current month has more committed deals than capacity
- Next quarter has insufficient pipeline coverage

**3.3 Value Distribution**
Analyze deal size distribution using separate queries (CASE not supported in Dataverse SQL):

**Small Deals (<$25K):**
```
SELECT COUNT(opportunityid) as count, SUM(estimatedvalue) as total_value
FROM opportunity
WHERE statecode = 0 AND estimatedvalue < 25000
```

**Medium Deals ($25K-$100K):**
```
SELECT COUNT(opportunityid) as count, SUM(estimatedvalue) as total_value
FROM opportunity
WHERE statecode = 0 AND estimatedvalue >= 25000 AND estimatedvalue < 100000
```

**Enterprise Deals (>$100K):**
```
SELECT COUNT(opportunityid) as count, SUM(estimatedvalue) as total_value
FROM opportunity
WHERE statecode = 0 AND estimatedvalue >= 100000
```

**Flag Concentration Risk:**
- Single deal > 30% of pipeline value
- Top 3 deals > 50% of pipeline value
- Over-reliance on one deal size category

#### Step 4: Generate Health Scores and Insights

**Individual Opportunity Health Score (0-100):**
| Component | Weight | Max Points |
|-----------|--------|------------|
| Activity Recency | 25% | 25 |
| Next Steps Defined | 20% | 20 |
| Qualification Strength | 25% | 25 |
| Stage Velocity | 15% | 15 |
| Close Date Validity | 15% | 15 |

**Score Interpretation:**
- 80-100: Healthy - On track
- 60-79: At Risk - Needs attention
- 40-59: Critical - Immediate action required
- 0-39: Stalled - May need to be disqualified

#### Step 5: Output Pipeline Health Report

**Executive Summary:**
```
PIPELINE HEALTH REPORT
Generated: [Date]
Scope: [Owner/Team] Open Opportunities

PIPELINE SNAPSHOT
═══════════════════════════════════════════════════
Total Pipeline Value: $2,450,000
Opportunity Count: 47
Weighted Pipeline: $1,225,000 (based on close probability)
Average Deal Size: $52,128
Average Health Score: 68/100

HEALTH DISTRIBUTION
═══════════════════════════════════════════════════
Healthy (80+):     18 deals  $890,000   (36%)
At Risk (60-79):   15 deals  $720,000   (29%)
Critical (40-59):  10 deals  $540,000   (22%)
Stalled (<40):      4 deals  $300,000   (12%)

STAGE BALANCE
═══════════════════════════════════════════════════
Qualify:    23 deals  $680,000   ⚠️  (49% - slight excess)
Develop:    12 deals  $720,000   ✓  (26% - healthy)
Propose:     8 deals  $650,000   ✓  (17% - healthy)
Close:       4 deals  $400,000   ⚠️  (9% - needs more)
```

**Priority Action List:**
```
IMMEDIATE ATTENTION REQUIRED
════════════════════════════════════════════════════

1. 🔴 Contoso Enterprise Deal - $150,000
   Health Score: 35/100 (Stalled)
   Issues:
   - No activity in 23 days
   - No scheduled next steps
   - Close date was yesterday (past due)
   Recommendation: Schedule urgent call to re-engage
   
2. 🟠 Fabrikam Cloud Migration - $85,000  
   Health Score: 52/100 (Critical)
   Issues:
   - Budget not confirmed (deal in Propose stage)
   - Decision maker not identified
   - Competitor mentioned in notes
   Recommendation: Qualify budget and identify champion

3. 🟡 Acme Corp Expansion - $65,000
   Health Score: 64/100 (At Risk)
   Issues:
   - Stage age: 45 days in Develop (benchmark: 30 days)
   - Next scheduled call is 3 weeks away
   Recommendation: Accelerate next steps, check for blockers
```

**Pipeline Risks:**
```
STRATEGIC RISKS IDENTIFIED
════════════════════════════════════════════════════

⚠️  CONCENTRATION RISK
    Top 3 deals = 52% of pipeline value
    - Contoso Enterprise: $150,000
    - Fabrikam Cloud: $85,000
    - Global Industries: $75,000

⚠️  CLOSE DATE CLUSTERING  
    67% of Q1 value expected in final 2 weeks
    High probability of slip to Q2

⚠️  QUALIFICATION GAP
    12 deals in Propose stage without confirmed budget
    Combined value at risk: $340,000

⚠️  PIPELINE COVERAGE
    Q2 pipeline coverage: 1.8x target
    Recommended: 3x minimum
    Action: Increase early-stage prospecting
```

#### Step 6: Drill-Down Recommendations

**For Stuck Deals:**
- Suggest specific re-engagement messages
- Recommend alternative contacts to try
- Propose disqualification if multiple attempts failed

**For Under-Qualified Deals:**
- Generate discovery questions to fill BANT gaps
- Suggest qualification call agenda
- Recommend resources (case studies, ROI calculators)

**For Pipeline Imbalance:**
- If top-heavy: Focus on conversion activities
- If bottom-heavy: Increase prospecting
- If propose jam: Review pricing strategy, competitive positioning

### Dataverse Tables Used
| Table | Purpose |
|-------|---------|
| `opportunity` | Primary entity for pipeline analysis |
| `activitypointer` | Activity history for engagement analysis |
| `task` | Scheduled tasks for next steps |
| `appointment` | Scheduled meetings |
| `account` | Customer context |
| `contact` | Stakeholder information |
| `systemuser` | Owner/rep information |

### Key Fields Reference
**opportunity:**
- `salesstage` (CHOICE) - Qualify(0), Develop(1), Propose(2), Close(3)
- `stepname` (NVARCHAR) - Current business process step
- `estimatedvalue` (MONEY) - Deal value
- `estimatedclosedate` (DATE) - Expected close
- `closeprobability` (INT) - Win probability % (0-100)
- `budgetstatus` (CHOICE) - No Committed Budget(0), May Buy(1), Can Buy(2), Will Buy(3)
- `need` (CHOICE) - Must have(0), Should have(1), Good to have(2), No need(3)
- `purchasetimeframe` (CHOICE) - Immediate(0), This Quarter(1), Next Quarter(2), This Year(3), Unknown(4)
- `purchaseprocess` (CHOICE) - Individual(0), Committee(1), Unknown(2)
- `decisionmaker` (BIT) - Decision maker confirmed (true/false)
- `msdyn_opportunityscore` (INT) - AI predictive score (0-100)
- `msdyn_opportunitygrade` (CHOICE) - Grade A(0), B(1), C(2), D(3)
- `msdyn_opportunityscoretrend` (CHOICE) - Improving(0), Steady(1), Declining(2), Not enough info(3)
- `msdyn_forecastcategory` (CHOICE) - Pipeline(100000001), Best case(100000002), Committed(100000003), Omitted(100000004), Won(100000005), Lost(100000006)
- `statecode` (STATE) - Open(0), Won(1), Lost(2)
- `statuscode` (STATUS) - In Progress(1), On Hold(2) [Open]; Won(3) [Won]; Canceled(4), Out-Sold(5) [Lost]

**activitypointer:**
- `regardingobjectid` (LOOKUP) - Polymorphic link to opportunity, account, contact, lead, etc.
- `activitytypecode` (NVARCHAR) - Type: phonecall, email, appointment, task
- `createdon` (DATETIME) - When activity was created
- `actualstart`, `actualend` (DATETIME) - Actual activity times
- `statecode` (STATE) - Open(0), Completed(1), Canceled(2), Scheduled(3-appointments only)
- `scheduledstart`, `scheduledend` (DATETIME) - Planned times

### Configurable Thresholds
Organizations can customize these parameters:
- Days without activity thresholds per stage
- Stage duration benchmarks by deal size
- Qualification score minimums per stage
- Pipeline coverage targets
- Concentration risk percentages

## Examples

### Example 1: Weekly Pipeline Review

**User says:** "How is my pipeline looking this week?"

**Actions:**
1. Query all open opportunities owned by current user
2. Analyze each deal against health criteria (stuck, missing next steps, qualification gaps)
3. Calculate pipeline coverage vs. quota
4. Generate prioritized action list

**Result:**
```
PIPELINE HEALTH SUMMARY
- Total Pipeline: $1.2M (42 opportunities)
- Healthy Deals: 28 (67%)
- Needs Attention: 10 (24%)
- Critical: 4 (9%)

TOP ACTIONS:
1. Re-engage Contoso deal - no activity in 12 days
2. Schedule discovery call with Fabrikam - missing BANT
3. Update close date on 3 past-due opportunities
```

### Example 2: Find Stuck Deals

**User says:** "Which of my deals are stuck?"

**Actions:**
1. Query all open opportunities
2. For each, check last activity date against stage-specific thresholds
3. Flag deals exceeding warning/critical thresholds
4. Sort by days stuck and deal value

**Result:**
```
STUCK DEALS (4 found):
1. Northwind Traders - $150K - 21 days in Propose stage (CRITICAL)
2. Alpine Ski House - $75K - 14 days in Develop stage (WARNING)
```

### Example 3: Qualify Under-Qualified Deals

**User says:** "Find deals with weak qualification"

**Actions:**
1. Calculate BANT score for each open opportunity
2. Flag deals in late stages (Propose/Close) with low qualification scores
3. Identify specific missing qualification elements

**Result:**
```
UNDER-QUALIFIED DEALS IN LATE STAGES:
1. Contoso Enterprise Deal - $200K in Propose
   Missing: Budget not confirmed, Decision maker unknown
   Recommendation: Schedule qualification call before proposal review
```

## Troubleshooting

### Error: No opportunities found
**Cause:** User has no open opportunities or filter criteria too restrictive
**Solution:**
- Check that statecode = 0 filter is correct
- Verify owner filter matches user's systemuserid
- Expand date range or remove value thresholds

### Error: Activity data appears incomplete
**Cause:** Activities not linked to opportunity via regardingobjectid
**Solution:**
- Check if activities are linked to account instead of opportunity
- Query activities for both opportunity and related account
- Remind users to link activities to opportunities

### Error: Pipeline coverage calculation incorrect
**Cause:** Quota data not available or forecast categories not set
**Solution:**
- Accept quota as user-provided input
- Check msdyn_forecastcategory values are populated
- Default to estimatedvalue if forecast fields unavailable