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