@microsoft/account-risk-early-warning

---
name: account-risk-early-warning
description: Identifies accounts showing warning signs of churn by analyzing activity trends, support cases, and engagement signals. Scores risk and prioritizes intervention targets. Use when user asks "which accounts are at risk", "churn risk analysis", "find accounts that might leave", "customer health check", "at-risk customers", "retention warning signs", or "account health score".
metadata:
  author: Dataverse
  version: 1.0.0
  category: customer-success
---

# Account Risk Early Warning

Retaining existing customers is more cost-effective than acquiring new ones. This skill monitors key accounts for early warning signals that indicate potential churn or relationship issues, enabling proactive intervention before problems escalate.

## Instructions

### Step 1: Determine Scope
When user asks "Which of my top accounts are showing warning signs?":

**1.1 Define Account Set:**
```
Options:
A) All active accounts owned by user
B) Specific account list (e.g., enterprise tier)
C) Accounts with revenue above threshold
D) Accounts up for renewal in next X months
```

**1.2 Query Target Accounts:**
```
SELECT accountid, name, revenue, numberofemployees, industrycode,
       createdon, ownerid, customertypecode, territoryid,
       openrevenue, openrevenue_date
FROM account
-- Note: openrevenue and openrevenue_date are rollup fields that may not exist in all orgs.
-- If absent, calculate open pipeline value by querying the opportunity table directly.
WHERE statecode = 0
AND ownerid = '[current_user_id]'  -- or specific criteria
ORDER BY revenue DESC
```

#### Step 2: Analyze Risk Signals for Each Account

**Important: Dataverse SQL Limitations**
Dataverse SQL does NOT support: subqueries, DATEADD(), GETUTCDATE(), HAVING, DISTINCT, UNION, CASE statements. 
Use separate queries and calculate date filters programmatically (e.g., calculate '2025-09-01' for 6 months ago).

**2.1 Activity Trend Analysis**
Query recent activities for each account (run separately per account):
```
SELECT activityid, activitytypecode, createdon, statecode
FROM activitypointer
WHERE regardingobjectid = '[accountid]'
AND createdon > '2025-09-01'
ORDER BY createdon DESC
```

**Calculate Activity Metrics:**
```
For each account:
- Total activities (last 6 months)
- Activities per month trend
- Activity types breakdown
- Days since last activity
- Comparison to account's historical average
```

**Activity Risk Indicators:**
| Signal | Warning | Critical |
|--------|---------|----------|
| Days Since Last Activity | 30+ days | 60+ days |
| Month-over-Month Decline | 25%+ decline | 50%+ decline |
| No Meetings/Calls | 45+ days | 90+ days |
| Only Automated Emails | 30+ days | 60+ days |

**2.2 Support Case Pattern Analysis**
Query recent cases (run per account, calculate date filter programmatically):
```
SELECT incidentid, title, createdon, prioritycode, severitycode, 
       statecode, statuscode, caseorigincode, customersatisfactioncode, 
       msdyn_casesentiment, escalatedon
FROM incident
WHERE customerid = '[accountid]'
AND createdon > '2025-09-01'
ORDER BY createdon DESC
```

**Calculate Case Metrics:**
```
For each account:
- Total cases (last 6 months)
- Cases per month trend
- Case severity distribution
- Average resolution time
- Escalation count
- Customer satisfaction scores (if available)
- Sentiment analysis (msdyn_casesentiment)
```

**Case Risk Indicators:**
| Signal | Warning | Critical |
|--------|---------|----------|
| Case Volume Increase | 50%+ increase | 100%+ increase |
| High Severity Cases | 2+ in 30 days | 5+ in 30 days |
| Escalated Cases | 1 in 60 days | 2+ in 60 days |
| Negative Sentiment | Score 9 (Slightly negative) | Score 7-8 (Very negative / Negative) |
| Low Satisfaction | Score 2-3 | Score 1 (Very Dissatisfied) |
| Unresolved Cases | 3+ open > 7 days | 5+ open > 14 days |

**2.3 Opportunity Velocity Analysis**
Query opportunity history (per account, calculate date filter programmatically):
```
SELECT opportunityid, name, estimatedvalue, actualvalue,
       statecode, statuscode, createdon, actualclosedate,
       salesstage, msdyn_forecastcategory
FROM opportunity
WHERE accountid = '[accountid]'
AND createdon > '2024-03-01'
ORDER BY createdon DESC
```

**Calculate Opportunity Metrics:**
```
For each account:
- New opportunities (last 6 months vs prior 6 months)
- Win rate trend
- Average deal size trend
- Pipeline coverage
- Time since last won deal
- Lost opportunities (recent)
```

**Opportunity Risk Indicators:**
| Signal | Warning | Critical |
|--------|---------|----------|
| No New Opportunities | 90+ days | 180+ days |
| Declining Win Rate | 20%+ decline | 50%+ decline |
| Declining Deal Size | 25%+ decline | 50%+ decline |
| Recent Lost Deals | 1 in 60 days | 2+ in 60 days |
| Stalled Pipeline | 60+ days no movement | 90+ days |

**2.4 Engagement Pattern Analysis**
Analyze contact engagement:
```
SELECT c.contactid, c.fullname, c.jobtitle, c.accountrolecode
FROM contact c
WHERE c.accountid IN ([target account ids])
AND c.statecode = 0

SELECT a.regardingobjectid, COUNT(*) as activity_count,
       MAX(a.createdon) as last_activity
FROM activitypointer a
JOIN contact c ON a.regardingobjectid = c.contactid
WHERE c.accountid IN ([target account ids])
AND a.createdon >= '[6_months_ago]'
GROUP BY a.regardingobjectid
```

**Engagement Risk Indicators:**
| Signal | Warning | Critical |
|--------|---------|----------|
| Key Contact Disengaged | 45+ days no response | 90+ days |
| Champion Gone Dark | 30+ days | 60+ days |
| Executive Engagement Drop | 50%+ decline | 75%+ decline |
| New Decision Maker | Not yet engaged | 60+ days not engaged |
| Contact Left Company | Detected | Champion left |

**2.5 Relationship Signals from Notes**
Analyze annotations and activity descriptions:
```
SELECT a.objectid, a.subject, a.notetext, a.createdon
FROM annotation a
WHERE a.objecttypecode = 'account'
AND a.objectid IN ([target account ids])
AND a.createdon >= '[6_months_ago]'
ORDER BY a.createdon DESC
```

**Keyword Detection:**
Look for risk keywords in notes:
- **Negative:** "unhappy", "frustrated", "dissatisfied", "complaint", "issue", "problem", "leaving", "cancel", "competitor"
- **Competitive:** "evaluating alternatives", "RFP", "comparing options", "other vendors"
- **Organizational:** "budget cut", "freeze", "layoffs", "restructuring", "merger", "acquisition"
- **Turnover:** "leaving company", "new role", "replacement", "transition"

**2.6 Renewal and Contract Signals**
If contract/renewal data available:
```
Query opportunities or custom entities for:
- Upcoming renewal dates
- Contract end dates
- Renewal discussions (or lack thereof)
```

**Renewal Risk Indicators:**
| Signal | Warning | Critical |
|--------|---------|----------|
| Renewal in 90 days | No discussion started | Negative feedback received |
| Renewal in 60 days | No commitment | Competitor being evaluated |
| Renewal in 30 days | Not verbal confirmed | At risk / declining |

#### Step 3: Calculate Composite Risk Score

**Risk Score Model (0-100, higher = more risk):**

| Risk Category | Weight | Max Points |
|---------------|--------|------------|
| Activity Decline | 20% | 20 |
| Support Case Patterns | 25% | 25 |
| Opportunity Velocity | 20% | 20 |
| Engagement Patterns | 20% | 20 |
| Relationship Signals | 15% | 15 |

**Score Interpretation:**
- 0-25: **Healthy** - No significant risk signals
- 26-50: **Monitor** - Early warning signs, watch closely
- 51-75: **At Risk** - Multiple warning signals, intervention needed
- 76-100: **Critical** - Immediate action required

#### Step 4: Prioritize Intervention Targets

**Sort accounts by:**
1. Risk Score (highest first)
2. Revenue Impact (larger accounts prioritized)
3. Renewal Proximity (sooner renewals first)
4. Relationship Investment (longer tenure/more history)

**Calculate Priority Score:**
```
priority = risk_score * 0.5 + revenue_weight * 0.3 + renewal_urgency * 0.2

Where:
- risk_score: 0-100 from Step 3
- revenue_weight: Normalized account revenue
- renewal_urgency: Days until renewal (inverted scale)
```

#### Step 5: Generate Risk Report

**Output Format:**
```
ACCOUNT RISK EARLY WARNING REPORT
════════════════════════════════════════════════════
Generated: [Date]
Scope: [User]'s Accounts
Accounts Analyzed: [N]

════════════════════════════════════════════════════
RISK SUMMARY
════════════════════════════════════════════════════
Critical Risk (76-100):   [N] accounts  $[revenue at risk]
At Risk (51-75):          [N] accounts  $[revenue at risk]
Monitor (26-50):          [N] accounts  $[revenue at risk]
Healthy (0-25):           [N] accounts  $[revenue value]

TOTAL REVENUE AT RISK: $[sum of Critical + At Risk]

════════════════════════════════════════════════════
PRIORITY INTERVENTION LIST
════════════════════════════════════════════════════

1. 🔴 CONTOSO CORPORATION
   Risk Score: 85/100 (Critical)
   Annual Revenue: $500,000
   Renewal: 45 days
   ─────────────────────────────────────────────────
   
   WARNING SIGNALS:
   ⚠️  Activity Decline: 67% fewer touchpoints vs prior quarter
   ⚠️  Support Cases: 8 cases in 30 days (up 300% from average)
   ⚠️  Escalation: 2 escalated cases in past month
   ⚠️  Sentiment: Case sentiment trending negative (avg 7.5)
   ⚠️  Competitor: "Evaluating alternatives" mentioned in notes
   ⚠️  Contact: VP Champion hasn't responded in 45 days
   
   DETAILED ANALYSIS:
   
   Activity Trend:
   • 6 months ago: 12 activities/month
   • Current: 4 activities/month
   • Trend: ↓ 67% decline
   
   Support Cases:
   • Total (6 mo): 15 cases
   • Last 30 days: 8 cases (spike)
   • High Severity: 3 cases
   • Avg Resolution: 5.2 days (above SLA)
   
   Opportunity Status:
   • No new opportunities in 120 days
   • Last won deal: 8 months ago
   • Pipeline: $0 (no active deals)
   
   Key Contacts:
   • Sarah Chen (VP Ops) - Last contact: 45 days ago ⚠️
   • John Smith (IT Dir) - Last contact: 12 days ago ✓
   
   RECOMMENDED INTERVENTIONS:
   
   Priority 1 (Immediate):
   📞 Executive outreach from sales leadership to Sarah Chen
      • Acknowledge support issues
      • Request relationship review meeting
      • Bring solutions, not sales pitch
   
   Priority 2 (This Week):
   📋 Support case review with CS team
      • Identify root cause of case spike
      • Escalate to product/engineering if needed
      • Prepare remediation plan
   
   Priority 3 (Within 2 Weeks):
   📅 Renewal discussion meeting
      • Don't wait - engage on renewal now
      • Address concerns before they finalize decision
      • Consider retention incentives if needed

   ─────────────────────────────────────────────────

2. 🟠 FABRIKAM INDUSTRIES
   Risk Score: 62/100 (At Risk)
   Annual Revenue: $250,000
   Renewal: 90 days
   ─────────────────────────────────────────────────
   
   WARNING SIGNALS:
   ⚠️  Champion Left: Primary contact left company 30 days ago
   ⚠️  New Decision Maker: Replacement not yet engaged
   ⚠️  Activity: 45 days since meaningful engagement
   
   RECOMMENDED INTERVENTIONS:
   
   Priority 1: Identify and engage new decision maker
      • Research LinkedIn for replacement
      • Get warm intro from remaining contacts
      • Schedule introductory meeting

[Continue for additional at-risk accounts...]
```

#### Step 6: Create Intervention Tasks

For each high-priority account, offer to create:

```
Use create_record with tablename: task
{
  "subject": "⚠️ Risk Intervention: [Account Name]",
  "description": "Risk Score: [X]/100\n\nKey Warning Signals:\n- [Signal 1]\n- [Signal 2]\n\nRecommended Actions:\n1. [Action 1]\n2. [Action 2]",
  "regardingobjectid": "[accountid]",
  "scheduledend": "[today or tomorrow]",
  "prioritycode": 1 (High)
}
```

Add note to account record:
```
Use create_record with tablename: annotation
{
  "subject": "Risk Assessment - [Date]",
  "notetext": "Account flagged as [Risk Level].\n\nWarning Signals:\n[Details]\n\nIntervention Plan:\n[Actions]",
  "objectid": "[accountid]",
  "objecttypecode": "account"
}
```

### Dataverse Tables Used
| Table | Purpose |
|-------|---------|
| `account` | Primary entity for risk analysis |
| `activitypointer` | Activity trend analysis |
| `phonecall` | Engagement tracking |
| `appointment` | Meeting frequency |
| `email` | Communication patterns |
| `incident` | Support case patterns |
| `opportunity` | Revenue velocity |
| `contact` | Stakeholder engagement |
| `annotation` | Notes for signal detection |
| `task` | Create intervention tasks |

### Key Fields Reference
**account:**
- `revenue` (MONEY) - Account value for prioritization
- `ownerid` (OWNER) - Account owner for filtering
- `createdon` (DATETIME) - Tenure calculation
- `customertypecode` (CHOICE) - Relationship type
- `openrevenue` (MONEY) - Total open pipeline value *(rollup field; availability depends on org configuration)*
- `msdyn_accountkpiid` (LOOKUP → msdyn_accountkpiitem) - Relationship analytics KPI
- `statecode` (STATE) - Active(0), Inactive(1)

**msdyn_accountkpiitem (Sales Insights - Relationship Analytics):**
- `msdyn_relationshiphealthscorevalue` (INT) - Health score (0-100)
- `msdyn_relationshiphealthscorestate` (CHOICE) - Good(0), Fair(1), Poor(2)
- `msdyn_relationshiphealthtrend` (CHOICE) - Improving(0), Steady(1), Declining(2), Not enough info(3)
- `msdyn_emailssent` (INT) - Outgoing emails count
- `msdyn_emailsreceived` (INT) - Incoming emails count
- `msdyn_phonecallsmade` (INT) - Outbound calls count
- `msdyn_phonecallsreceived` (INT) - Inbound calls count
- `msdyn_meetingssent` (INT) - Meetings scheduled count
- `msdyn_lastactivitybyteam` (DATETIME) - Last team outreach
- `msdyn_nextactivitybyteam` (DATETIME) - Next scheduled activity
- `msdyn_facetimewithcustomer` (DURATION) - Total meeting time
- `msdyn_timespentbyteam` (DURATION) - Total time invested
- `msdyn_averagefirstresponsetimebyusinhrs` (FLOAT) - Avg response time (hours)

**incident:**
- `prioritycode` (CHOICE) - High(1), Normal(2), Low(3)
- `severitycode` (CHOICE) - Severity level
- `casetypecode` (CHOICE) - Question(1), Problem(2), Request(3)
- `caseorigincode` (CHOICE) - Source channel
- `customersatisfactioncode` (CHOICE) - Very Dissatisfied(1), Dissatisfied(2), Neutral(3), Satisfied(4), Very Satisfied(5)
- `msdyn_casesentiment` (CHOICE) - Sentiment scale (lower = more negative): Very negative(7), Negative(8), Slightly negative(9), Neutral(10), Slightly positive(11), Positive(12), Very positive(13)
- `escalatedon` (DATETIME) - Escalation tracking
- `statecode` (STATE) - Active(0), Resolved(1), Cancelled(2)
- `statuscode` (STATUS) - In Progress(1), On Hold(2), Waiting(3), Researching(4) [Active]; Problem Solved(5), Info Provided(1000) [Resolved]; Cancelled(6), Merged(2000) [Cancelled]

**activitypointer:**
- `activitytypecode` (NVARCHAR) - Type: phonecall, email, appointment, task
- `createdon` (DATETIME) - Activity date
- `regardingobjectid` (LOOKUP) - Polymorphic link to account, contact, etc.
- `statecode` (STATE) - Open(0), Completed(1), Canceled(2), Scheduled(3-appointments)

### Risk Signal Weights (Configurable)

| Signal Category | Default Weight | Adjustable Range |
|-----------------|----------------|------------------|
| Activity Decline | 20% | 10-30% |
| Support Cases | 25% | 15-35% |
| Opportunity Velocity | 20% | 10-30% |
| Engagement Patterns | 20% | 10-30% |
| Relationship Signals | 15% | 10-25% |

### Best Practices

1. **Early intervention wins:** Act on Monitor-level risks before they become Critical
2. **Don't assume:** Verify signals with direct customer conversation
3. **Cross-functional response:** Involve Success, Support, and Sales in intervention
4. **Document everything:** Track intervention actions for pattern learning
5. **Regular cadence:** Run risk analysis weekly for proactive management

## Examples

### Example 1: Weekly Account Health Check

**User says:** "Which of my accounts are at risk?"

**Actions:**
1. Query all active accounts owned by user
2. Analyze activity trends, support cases, opportunity velocity for each
3. Calculate composite risk score
4. Rank accounts by risk level

**Result:**
```
ACCOUNT RISK SUMMARY
Critical (2): Immediate intervention required
Warning (5): Schedule check-in this week
Monitor (8): Watch for changes
Healthy (35): No action needed

CRITICAL ACCOUNTS:
1. Contoso Ltd - Risk Score: 85/100
   - 60+ days since last activity
   - 3 escalated support cases
   - Renewal in 45 days
   ACTION: Executive outreach immediately

2. Fabrikam Inc - Risk Score: 78/100
   - Activity declined 70% vs prior quarter
   - Champion contact left company
   ACTION: Re-establish relationships
```

### Example 2: Specific Account Health Analysis

**User says:** "Check the health of the Northwind account"

**Actions:**
1. Retrieve Northwind account details
2. Analyze all risk signals in depth
3. Provide detailed breakdown with recommendations

**Result:**
```
NORTHWIND TRADERS - HEALTH ASSESSMENT
Overall Risk: WARNING (62/100)

ACTIVITY ANALYSIS:
- Last interaction: 28 days ago (email)
- Trend: Declining (45% fewer touchpoints vs. prior 90 days)

SUPPORT ANALYSIS:
- Open cases: 2 (1 high priority)
- Recent escalation: Yes (3 days ago)
- Sentiment: Slightly negative

OPPORTUNITY ANALYSIS:
- No new opportunities in 6 months
- Last deal: Won 8 months ago

RECOMMENDATION:
Schedule customer success call to address open cases and explore expansion opportunities.
```

### Example 3: Renewal Risk Analysis

**User says:** "Show me accounts with renewals coming up that are at risk"

**Actions:**
1. Filter accounts with renewals in next 90 days
2. Cross-reference with risk signals
3. Prioritize by revenue and risk score

**Result:**
```
AT-RISK RENEWALS (Next 90 Days):
1. Alpine Ski House - $150K ARR - Renews in 30 days
   Risk: High engagement drop + unresolved escalation
   
2. Tailspin Toys - $80K ARR - Renews in 60 days
   Risk: Budget freeze mentioned in recent call notes
```

## Troubleshooting

### Error: No risk data available
**Cause:** Sales Insights or Relationship Analytics not enabled
**Solution:**
- Fall back to activity count analysis
- Use support case data as primary signal
- Calculate manual engagement score from activitypointer

### Error: Too many false positives
**Cause:** Thresholds too sensitive for organization's engagement patterns
**Solution:**
- Adjust activity threshold based on account tier
- Consider industry-specific baselines
- Weight signals based on historical churn correlation

### Error: Missing support case data
**Cause:** Customer service not using Dynamics 365 for cases
**Solution:**
- Focus on activity and opportunity signals
- Consider integrating external support data
- Use activity notes for sentiment analysis