Renewal Radar exists because a commercial book does not renew evenly. In a 900-policy book, maybe 60 renewals in the next quarter carry most of the retention risk, and the account managers working them find out which ones at 45 days, when the carrier's renewal terms land and there is no time left to remarket.
This tutorial builds the ranking step: a nightly job that reads a renewal window out of the agency management system, scores each expiring policy on facts you can point at, and hands an account manager an ordered list. No model decides the ranking. The score is arithmetic, the inputs are AMS fields, and every number on the list can be traced back to a row.
You need read access to your AMS data (Applied Epic via the Applied API or a reporting replica, HawkSoft via the Partner API, EZLynx via its API or scheduled export), a Postgres database to stage extracts in, and Python. Nothing here writes back yet.
Pick the window first
120 days before expiration, not 90 and not 60. That is the last point where remarketing a mid-size commercial account is still a normal piece of work rather than a scramble: submission out at 90, carrier responses by 60, proposal to the insured by 30, and slack for the loss runs that arrive late.
So the job runs daily and asks one question: which policies expire between 118 and 122 days from today, and what do we know about them? A five-day band, rather than a single day, means a job that fails on a Sunday does not create a hole in the book.
SELECT p.policy_id,
p.account_id,
a.account_name,
p.line_of_business,
p.carrier,
p.expiration_date,
p.annual_premium,
p.producer_code,
p.account_manager
FROM policy p
JOIN account a ON a.account_id = p.account_id
WHERE p.status = 'ACTIVE'
AND p.expiration_date BETWEEN CURRENT_DATE + 118 AND CURRENT_DATE + 122;
That is the query against a staged extract, not against your live AMS. Stage it. A nightly pull into your own Postgres gives you a stable snapshot to score, a place to keep yesterday's scores for comparison, and no risk of a scoring bug generating API load against the system your CSRs are typing into.
Score facts, not vibes
The temptation is to hand the whole book to a model and ask which accounts look shaky. Resist it. A renewal ranking has to survive an account manager asking "why is Brennan Mechanical above Delta Freight?" and the answer has to be four sentences of fact, not an inference.
Six signals do most of the work. They are all in the AMS or in the mailbox already feeding your other agents.
| Signal | Source | Why it moves the score |
|---|---|---|
| Premium change at last renewal | prior-term premium vs. current | A double-digit increase last year predicts shopping this year |
| Claim activity in term | claims table, incurred in the last 24 months | Loss ratio drives the carrier's terms and the insured's mood |
| Service friction | count of activity-log entries typed SERVICE or COMPLAINT | Accounts that generate complaints leave |
| Contact silence | days since last logged outbound touch | Nobody has spoken to this insured since last renewal |
| Coverage change requests | endorsement activity in term | The business changed; the program may not fit any more |
| Account concentration | premium share of the producer's book | Not risk, but consequence: what it costs if it goes |
Each becomes a 0-to-1 component with an explicit rule, then a weighted sum. Keep the weights in a config file, not in the code, because you will change them in the first month.
from dataclasses import dataclass
from datetime import date
WEIGHTS = {
"premium_shock": 0.25,
"claims": 0.20,
"service_friction":0.15,
"silence": 0.15,
"coverage_change": 0.10,
"concentration": 0.15,
}
def clamp(x): return max(0.0, min(1.0, x))
@dataclass
class Policy:
policy_id: str
annual_premium: float
prior_premium: float | None
incurred_24mo: float
service_activities: int
complaint_activities: int
days_since_outbound: int | None
endorsements_in_term: int
producer_book_premium: float
def components(p: Policy) -> dict[str, float]:
if p.prior_premium:
change = (p.annual_premium - p.prior_premium) / p.prior_premium
else:
change = 0.0
loss_ratio = p.incurred_24mo / (p.annual_premium * 2) if p.annual_premium else 0.0
silence = p.days_since_outbound if p.days_since_outbound is not None else 365
return {
# 0 at flat, 1 at +25% or worse
"premium_shock": clamp(change / 0.25),
# 0 at no losses, 1 at a 60% two-year loss ratio
"claims": clamp(loss_ratio / 0.60),
# complaints count triple; 1 at six weighted touches
"service_friction": clamp((p.service_activities + 3 * p.complaint_activities) / 6),
# 0 inside 90 days, 1 at a full year of silence
"silence": clamp((silence - 90) / 275),
# 1 at three or more mid-term changes
"coverage_change": clamp(p.endorsements_in_term / 3),
"concentration": clamp(p.annual_premium / p.producer_book_premium) if p.producer_book_premium else 0.0,
}
def score(p: Policy) -> tuple[float, dict[str, float]]:
c = components(p)
total = sum(WEIGHTS[k] * v for k, v in c.items())
return round(100 * total, 1), c
Three details that matter more than the formula.
Return the components, not just the total. The list an account manager reads shows the score and the two components that contributed most to it. A score with no reason attached gets ignored by week three.
Missing data is not zero risk. days_since_outbound = None means nobody logged a touch, which is exactly the situation worth flagging, so it becomes 365, not 0. Decide the null behaviour for every signal deliberately and write it in a comment next to the rule.
Thresholds are per-book. A 25% premium increase is a shock in workers' comp in a soft year and unremarkable in a hard property market. Set the denominators against the agency's own history, then revisit them at the first quarterly review.
Turn the score into a work queue
A ranked list of 60 items is still not work. Cut it into tiers with defined actions, so the output is a decision the account manager accepts or overrides rather than a report they interpret.
def tier(score_value: float, premium: float) -> str:
if score_value >= 65 or (score_value >= 50 and premium >= 50_000):
return "REMARKET" # start a submission now
if score_value >= 35:
return "TOUCH" # producer call before carrier terms arrive
return "MONITOR" # standard renewal handling
The premium clause in the first branch is deliberate. A moderate score on a $75,000 account deserves the same 120-day start as a high score on a $6,000 one, because the work of remarketing is roughly fixed and the exposure is not.
Now the agent has something to draft against: for REMARKET, a submission packet and a producer note; for TOUCH, a call agenda with the three facts that raised the score. Both go to a person. Nothing on this list leaves the building unread, and the tier is a recommendation the account manager can downgrade in one click, with the override stored.
Where the model earns its place
There is one job here a language model does well: writing the two-sentence explanation that goes at the top of each item, from the component values and the underlying activity text. "Premium rose 31% at the 2025 renewal, two GL claims incurred $84k, and the last logged outbound contact was 11 months ago" reads better generated than templated, and it stays honest as long as you pass it only the numbers you computed and instruct it to use nothing else.
Do not let it adjust the ranking. If the model can nudge scores, you lose the property that makes the list defensible: run it twice on the same data, get the same order.
Compare against yesterday
Keep every night's scores. The delta is often more useful than the level: an account that moved from 30 to 58 in a week had something happen, usually a claim posting or a complaint activity, and that is worth an alert even if it never reaches the top of the list.
CREATE TABLE renewal_score (
policy_id text NOT NULL,
scored_on date NOT NULL,
score numeric(5,1) NOT NULL,
tier text NOT NULL,
components jsonb NOT NULL,
PRIMARY KEY (policy_id, scored_on)
);
Storing the components as JSONB means that when you change the weights in month two, you can rescore history without re-extracting anything, and show the operations lead what the new weights would have done to last quarter's list.
What this does not do
- It does not predict retention. It ranks accounts by known risk factors so limited hours go to the right files. Calling it a prediction invites a question about accuracy nobody can answer on a 900-policy book.
- It does not see the things that actually lose accounts: the CFO who changed jobs, the competitor's producer at the same church, the fee dispute settled over the phone. Account managers do. The override matters.
- It does not write to the AMS. Adding activities safely is its own problem; see writing agent output back to the AMS without duplicates.
- It does not send anything. The packet and the outreach draft wait for a licensed person, on the pattern in build a human approval gate with Step Functions.
Before you trust the order
Run it silently for a quarter against the live book, and each week, ask two account managers to rank their own upcoming renewals from memory before they see the list. Where the lists disagree, one of you is wrong and the reason is usually a signal you have not encoded or a weight set by taste. That is the same discipline we use for accuracy in general: score a silent run before go-live.
This ranking is the front half of Renewal Radar. If you want a senior engineer to build it against your Epic, HawkSoft, or EZLynx data, contact us with your AMS and roughly how many commercial renewals you handle a month.