A renewal agent that ranks a book by risk at 120 days needs one thing before it needs a model: an expiration date on every in-force commercial policy. A certificate agent needs a holder list that is not half free text. A service-request agent needs email addresses on contacts that match the people actually writing in.
Agencies rarely know which of those they have. The AMS reports were built for accounting and for the carrier, not for a program reading 4,000 rows at 2am. So the first week of every engagement is a read-only profile of the data, before a single prompt is written. It takes two to three days, it costs a fraction of the build, and it has changed the scope of roughly every engagement we have scoped.
This tutorial is the profile itself: how to pull the snapshot, the checks we run, the thresholds we hold each workflow to, and what to do with a field that fails.
What the profile is and is not
The profile answers one question per field: on what percentage of the records this agent will touch is this field present, well-formed, and internally consistent. Nothing about model accuracy. Accuracy comes later, from a silent run scored against real cases. This is the step before that, and it is cheaper.
It is read-only. No writes, no cleanup, no merges. Fixing data mid-profile destroys your baseline and irritates the people whose records you changed without asking.
Step 1: pull a snapshot you can query
Get the data out of the AMS and into something you can run SQL against. Postgres in the agency's own account is fine, and the volumes here are small: a 40-person agency with a $12M commercial book is usually under 30,000 policy rows and under 200,000 activities.
How you extract depends on the system.
- Applied Epic: a scheduled report export, or the published integration APIs if the agency has credentials issued for them. Ask for policy, client, contact, policy line, and activity extracts covering 36 months.
- HawkSoft: the Partner API, once the agency has API access enabled for the integration, returns client, policy, and log-note data in JSON. Page it and land it raw.
- EZLynx: the management-system export plus API access on the agency's plan.
Land the raw payloads first, unchanged, then transform. When a check produces a surprising number, and one will, you need the original bytes to argue with.
Scope the snapshot to what the agent will actually touch. For a commercial renewal agent that is in-force commercial policies with an expiration date in the next 18 months, plus their clients, contacts, and activities. Profiling the whole database inflates every failure rate with records nobody will process, and you end up debating personal-lines rows that are out of scope anyway.
Step 2: run the field checks
Ten checks cover most of what breaks. Run each as a single query returning a rate, not a list, and keep the list behind it for the ones that fail.
-- 1. Expiration date present and plausible on in-force commercial policies
select
count(*) as rows_total,
count(*) filter (where expiration_date is null) as missing_exp,
count(*) filter (where expiration_date < current_date - 365) as stale_exp,
count(*) filter (where expiration_date > current_date + 730) as implausible_exp
from stg_policy
where status = 'in force'
and line_of_business_type = 'commercial';
The remaining nine, in the order they usually hurt:
- Policy number format. Group by a normalised shape (
regexp_replace(policy_number, '[0-9]', 'N')). A book with 300 distinct shapes across 12 carriers is a book where matching a carrier document to a policy by number will need fuzzy logic and a confidence threshold. - Carrier naming. Count distinct
carrier_namevalues, then count them again after lowercasing and stripping punctuation and suffixes. If 210 collapses to 96, every carrier-keyed rule you write needs a normalisation table, and you should build that table now. - Duplicate insureds. Cluster clients on normalised name plus FEIN plus street address. Duplicates are the reason an agent writes an activity onto the account nobody reads.
- Contact email coverage. Percentage of in-scope accounts with at least one contact that has a syntactically valid email, and the percentage where that contact also carries a role. Missing roles is why outreach drafts get addressed to the accounts-payable clerk.
- Producer and account-manager assignment. Null owners mean the routing step has nowhere to send the item, and routing failures are the ones a CSR notices first.
- Line-of-business coding. Rate of policies with a usable line code. Hand-typed descriptions in a code field break every per-line rule.
- Activity codes. Distribution of activity or log-note codes over 24 months. If 70% of activities carry one generic code, you cannot train or evaluate classification on history, and the agent's own codes will need to be agreed with the ops lead rather than learned.
- Document store linkage. Percentage of in-force policies with at least one attached document, and the percentage where the attachment carries a type. An endorsement check cannot cite a form the agent cannot find.
- Timestamp sanity. Records whose created date is after their modified date, or dated in the future. A small rate here is harmless; above 1% it usually means a migration wrote the fields, and any recency logic you build is fiction.
Run all ten against the same snapshot, on the same day, and record the snapshot date in the output. These numbers move.
Step 3: hold each workflow to its own threshold
A rate on its own means nothing. What matters is whether the workflow you are scoping can run on it. We use a table like this, and we show it to the principal before quoting hours.
| Check | Renewal Radar | COI Agent | Quote-Intake Agent | Service-Request Agent |
|---|---|---|---|---|
| Expiration date present | 99% | 95% | not used | 90% |
| Policy number parseable | 90% | 95% | 90% | 95% |
| Carrier normalisable | 95% | 90% | 90% | 80% |
| No duplicate insured | 97% | 97% | 95% | 97% |
| Contact email + role | 85% | 80% | 70% | 90% |
| Owner assigned | 95% | 90% | 90% | 98% |
| Documents linked | 70% | 95% | 60% | 70% |
The numbers are deliberately uneven. A certificate agent lives or dies on document linkage and can tolerate thin contact data. A service-request agent is the reverse: it has to match an inbound sender to a person and route to an owner, so email, role, and ownership carry the weight.
Below threshold is not a verdict that the work cannot be done. It is a fork with three branches, and picking the branch is the point of the profile.
Step 4: fix, work around, or descope
Fix when the gap is bounded and mechanical. 400 policies missing expiration dates that exist on the declarations page in the document store is two days of extraction plus a CSR review queue. Do it before the build, not during, and write the corrections through the same approved-by-a-human path the agent will use later.
Work around when the gap is structural. Carrier naming never gets clean on its own, so you build the normalisation table, version it, and treat additions as maintenance. Ambiguous policy-number matches get a confidence score and a cannot-determine outcome that goes to a person rather than a guess that goes to a holder.
Descope when the data cannot carry the feature. If 30% of in-force policies have no linked document, an endorsement-checking step will return cannot-determine on a third of requests, and a CSR who sees that three times stops reading the output. Ship the part that works, say plainly that the rest waits on document coverage, and revisit it with a number rather than a feeling.
Write all three columns into the same document as the rates. It becomes the scope conversation, and later it becomes the reason a limitation was expected rather than discovered in week six.
Step 5: keep the profile running
Data quality is not a milestone. Schedule the same ten queries weekly against the live snapshot and store one row per check per week.
insert into data_profile_history (run_date, check_name, scope, rate, denominator)
values (current_date, 'expiration_date_present', 'commercial_in_force', 0.987, 4132);
Then alert on movement, not on level. A 4-point drop in owner assignment inside one week usually means a staffing change nobody told you about, and the routing step is about to start failing. Agents degrade quietly when the data underneath them changes; this is the cheapest instrument that catches it.
What the profile will not tell you
It will not tell you whether the agent's judgment is right. A book can pass all ten checks and still produce a renewal ranking the account managers disagree with, because ranking is judgment and the profile only measures the inputs. That answer comes from a silent run against the live book, scored on real cases, before anything goes to a client.
It also will not tell you that the data is true. An expiration date can be present, well-formed, plausible, and wrong. The profile measures shape and coverage. Truth is what the reconciliation against carrier downloads is for.
What it does buy is a scope built on numbers, in the first week, for a few days of work. Every engagement we have run has changed shape after this step, and the agencies that saw the numbers early were the ones who were not surprised later.
If you want to know which of your workflows your data can carry today, tell us your AMS and the workflow taking the most staff hours. A senior engineer replies within one business day.