Pipeline Health Analyzer

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:

• Query all open opportunities owned by current user
• Analyze each deal against health criteria (stuck, missing next steps, qualification gaps)
• Calculate pipeline coverage vs. quota
• 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:

• Query all open opportunities
• For each, check last activity date against stage-specific thresholds
• Flag deals exceeding warning/critical thresholds
• 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:

• Calculate BANT score for each open opportunity
• Flag deals in late stages (Propose/Close) with low qualification scores
• 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