To analyze an insurance book of business for cross-sell and churn, put every active policy in one place, group it by client and household, and run "has X, not Y" filters to find clients missing an obvious product. Then compare this month's active policies with last month's, and this period's commission statements with the last ones, to catch churn signals early. Hand the resulting lists to the agents who know those clients before anyone sends a message.
Most agency owners already have the data for this. It sits in the policy export from your back office, in carrier portals and in weekly commission sheets. Nobody has lined it up. This walkthrough shows how to do that with a spreadsheet, in the order we run it.
What data do you need to analyze your book?
Before you filter anything, pull these files. A CSV is much easier to work with than a PDF, so ask for CSV exports wherever a carrier or your back office offers them.
- Active-policy export, this month and last month. One row per policy: policy number, client name, date of birth, address, phone, email, carrier, product, product category, issue date, status, annual premium, writing agent and split.
- Premium history. Last year's annual premium per policy, or an older export you can compare against.
- Two consecutive commission statements per agent, or the principal's master sheet if your back office sends one.
- Your current agent roster, so you can spot clients whose agent has left.
Write down the earliest date your data covers. Policies written before that date won't show up, and an agent will ask you where their clients went.
The steps at a glance
- Put the whole book of business in one sheet and clean up product categories.
- Group clients into households.
- Run "has X, not Y" filters.
- Pull single-product clients.
- Compare this month's active policies with last month's.
- Compare commission statements for churn signals.
- Flag Medicare supplement policies in years 5 to 7.
- List clients whose premium went up.
- Hand each list to the agent to vet.
Step 1: Put the book in one sheet and clean up product categories
Stack every policy export into one tab. Add a column called category and map each product to a short list: Medicare supplement, Medicare Advantage, Part D, life, final expense, annuity, long-term care, hospital indemnity, health. Keep the list short. Ten categories you trust beat thirty you don't.
Expect mistakes in the source. Products get filed under the wrong type, and a hybrid life/LTC policy may show up as plain life. Fix what you can now and note the rest. Those mistakes produce false opportunities later.
Step 2: Build a household view
A spouse with a life policy changes the cross-sell conversation for the other spouse. A household view groups clients who share a home so you see the whole picture before you filter.
The simple way to do it in a sheet:
- Normalize addresses (same abbreviations, no apartment-number noise) and phone numbers (digits only).
- Give each client a
household_id. Clients who share a normalized address, or a phone number, or an email, get the same ID. - Review the matches by eye where a shared email might be an adult child helping a parent.
Now you can run each filter at the client level and the household level. "Has Medicare supplement, no life" means something different when the husband already holds a large life policy with you.
Step 3: Run "has X, not Y" filters
Cross-selling starts as a filtering problem. You define a combination of products a client has and products they don't, sometimes with an age band, and you run it across every agent's clients at once. Your agents can name a few of these clients from memory. The filter finds the rest.
These are the scenarios we run first:
"has annuity but no life, has annuity but no long-term care, has Medicare but no annuity between the age of 55 to 70, has Medicare but no life, no final expense but is over the age of 75, or simply has just one product. All of them should pose an opportunity for you to sell something." — Kevin Beeftink, founder of RevEngine
In a spreadsheet, build a pivot table with one row per client and one column per category, counting active policies. Then each cross-sell opportunity is a filter on that table:
| Scenario | Has | Doesn't have | Age band |
|---|---|---|---|
| Annuity, no life | annuity ≥ 1 | life = 0 | any |
| Annuity, no LTC | annuity ≥ 1 | long-term care = 0 | any |
| Life, no Medicare | life ≥ 1 | any Medicare = 0 | 65+ |
| Medicare, no annuity | any Medicare ≥ 1 | annuity = 0 | 55 to 70 |
| Medicare, no life or final expense | any Medicare ≥ 1 | life = 0 and final expense = 0 | 75+ |
| Health, no hospital indemnity | health ≥ 1 | hospital indemnity = 0 | any |
Count the results per agent. The count tells you which agents have enough opportunities to justify a campaign and which ones need only a short call list.
Step 4: Pull single-product clients
The single-product client is the easiest list to build and often the most useful. Filter your pivot table for clients with exactly one active policy, then break it down by agent and by that one product.
Two things show up fast. You see which agents carry a large number of one-policy clients, and you find clients whose agent is no longer on your roster. Cross-reference the writing agent column against your current roster. Clients whose agent left are the ones nobody calls at renewal time, and they are the first to drift to someone else.
Step 5: Compare this month's active policies with last month's
This is the simplest churn check you can run, and many agencies skip it. Take last month's active-policy export and this month's, and match them on policy number.
Every policy falls into one of three groups:
- On both lists: still active.
- On last month's list, missing from this month's: a lapse or cancellation candidate.
- On this month's list only: possibly new business. Check it against what you already had before you count it. A policy you logged months ago can reappear after a data fix or a carrier re-issue, and if you count it as new you overstate growth.
For example, say last month's export had 1,840 active policies and this month's has 1,812. The totals suggest you lost 28. When you match on policy number, you find 1,790 on both lists, 50 missing and 22 that appear only this month. So the real picture is 50 policies to check and 22 to confirm as new. The net figure of 28 hid most of the movement.
Put the 50 missing policies on a list with the agent's name, carrier, product and last known premium, and assign each one for a follow-up call this week. A client who cancelled a month ago can sometimes still be helped. A client who cancelled a year ago has a new agent.
Step 6: Use commission statements as a second churn check
Your policy export tells you what is active. Your carrier commission statements tell you what is paying. Comparing two periods of statements catches things the policy file misses.
Line up last period's statement and this period's by policy number. Look for:
- A policy that paid last period and shows nothing now. Check the policy status before you call it churn.
- A negative line or a chargeback. The carrier is taking back advanced commission, which usually means the client cancelled early.
- A lower amount on the same policy. This can be a premium change, a plan change or a payment error.
One caution: a policy can stop paying because the commission period ended while the client stays put. That leads straight into the next step.
Step 7: Flag Medicare supplement policies in years 5 to 7
On many Medicare supplement policies, the renewal commission stops somewhere around policy year 5, 6 or 7, depending on the carrier. When it stops, the client still has the policy and still calls you with questions. Nothing in your reporting tells you this group exists unless you look for it.
To find them, add a policy_year column: the number of full years since the issue date, plus one. A policy issued in March 2020 is in year 7 by March 2026. Filter for Medicare supplement policies in years 5 to 7 and check each carrier's rate table to confirm where the renewal ends.
These clients are a reason to schedule a review. Some will be in the best plan for them and should stay. For others, a lower-priced plan or a change in health needs may make a different plan the better choice. Any change has to serve the client first, and switching a Medigap policy can involve medical underwriting, so check the current rules on Medicare.gov's Medigap pages before the agent brings it up.
Step 8: List clients whose premium went up
Premium history turns into a call list. A Medicare supplement client whose premium jumped this year is a client who is already unhappy, and possibly already shopping.
"I want to be able to say, here's a list of med subs that their premium increased by 10% year over year. Because that's a huge opportunity for us to go back." — an agency principal we work with
The math in a sheet: (this year's premium − last year's premium) ÷ last year's premium. Say a client paid $180 a month last year and pays $205 now. That's $25 more, or a 13.9% increase, so the client goes on the list. Filter for 10% or more, sort by the dollar increase, and give the top of the list to agents first. A $40 monthly jump gets a client's attention faster than a $6 one.
This list overlaps with Step 7. A client in policy year 6 whose premium rose 15% should be one of the first calls.
Step 9: Hand the list to agents to vet
The list is a draft. Your data can't see everything the agent knows, and a message sent to the wrong client costs you more than the opportunity was worth.
Send each agent their own list and ask them to cross out names. These are the errors they catch:
- Coverage held elsewhere. A client may have kept a life policy at a former firm because of health or cost. Your export shows "no life", and a quote that ignores the existing policy makes you look careless.
- Policies older than your data. If your export starts in a given year, older policies are invisible.
- Products in the wrong category. One product filed as another creates a false gap.
- Split cases. If another agent is listed as writing agent on a commission split, the client may show up on the wrong agent's list or not at all.
- Personal context. Recent bereavement, a claim in progress, a client who asked not to be contacted.
Ask agents to be specific about each correction: what they expected, what the list showed and where the gap is. Those notes tell you which data fixes to make before the next run. Then the vetted list goes into a campaign. We cover how to run that campaign in cross-selling your existing book on the Kevlex blog.
What this analysis can't tell you yet
A few limits are worth stating up front, so your agents trust the results:
- It shows what clients hold with you. Coverage held at other agencies stays invisible until the agent or the client tells you.
- A missing policy is a signal. Someone still has to confirm whether it lapsed, cancelled, moved to another agent or reached the end of its commission period.
- Commission amounts depend on the rate schedule and agent level at the time of sale. If you don't have that history, older policies can't be priced accurately. Say so in the report and show those rows separately.
- Split cases need clean data on both sides. If your splits don't add up, your per-agent counts won't either. Our post on setting up commission splits covers how to fix that.
If a number in your report doesn't match what a manager already knows, they will doubt the rest of it. Start with figures people can check against their own statements, and add the harder ones once those hold up.
What to do this week
- Ask your back office or carriers for this month's and last month's active-policy exports as CSV files.
- Build the client-by-category pivot table and run the annuity-no-life and single-product filters.
- Match the two months on policy number and list every missing policy with its agent.
- Add
policy_yearto your Medicare supplement policies and pull years 5 to 7. - Send one agent their list and ask for corrections by Friday.
- Once statements are in, run the same comparison on two months of commission data. Our month-end reconciliation walkthrough shows how to line them up.
RevEngine reports on what your agency earns, checks that carriers paid what they owed, and finds opportunities in the book you already have. If you want to see your own book laid out this way, look at how RevEngine works or book a short RevEngine demo.
