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:

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