Account Risk Early Warning

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:

Copy code
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:

Copy code
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):

Copy code
SELECT activityid, activitytypecode, createdon, statecode
FROM activitypointer
WHERE regardingobjectid = '[accountid]'
AND createdon > '2025-09-01'
ORDER BY createdon DESC

Calculate Activity Metrics:

Copy code
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:

2.2 Support Case Pattern Analysis Query recent cases (run per account, calculate date filter programmatically):

Copy code
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:

Copy code
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:

2.3 Opportunity Velocity Analysis Query opportunity history (per account, calculate date filter programmatically):

Copy code
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:

Copy code
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:

2.4 Engagement Pattern Analysis Analyze contact engagement:

Copy code
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:

2.5 Relationship Signals from Notes Analyze annotations and activity descriptions:

Copy code
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:

Copy code
Query opportunities or custom entities for:
- Upcoming renewal dates
- Contract end dates
- Renewal discussions (or lack thereof)

Renewal Risk Indicators:

Step 3: Calculate Composite Risk Score

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

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:

Copy code
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:

Copy code
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:

Copy code
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:

Copy code
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

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)

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:

Copy code
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:

Copy code
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:

Copy code
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