Insurance policy record keeping: what it should include and how to set it up

Every insurer runs its own system for policies, statements, and clients. An agent or broker who works with a single insurer might reasonably ask: why duplicate data the insurer already has?

The answer is simple. An insurer's system is built for its own needs — premium calculation, collections, claims handling. It isn't built to tell you when a policy expires, how much commission to expect, what you advised a client, or which products might still suit them. And if you work with more than one insurer, the question becomes moot: you won't get a unified view of your entire client portfolio from any single one of their systems.

A record kept by the insurer is the insurer's record. A record you keep yourself is the only one that looks at the business from your side — the side of the agent or broker who is accountable to the client.

What your own record gives you

• Oversight of commissions. Without your own policy list, an insurer's statement can't be checked.
• Timely renewals. Only you know which policy for which client expires in a month — if you're tracking it.
• Proof of advice given. In a dispute or a client question, documentation is your protection, not the insurer's.
• A complete view of the client. A client with three policies across two insurers is only ever partially visible in any one insurer's records.
• A foundation for growth. A record linking clients to policies is also a list of opportunities for cross-selling and renewals.

Keeping a record, then, isn't an administrative duty done for tidiness. It's the tool without which you're running the business from memory.

A record without a predefined structure eventually turns into a collection of random columns nobody understands anymore. Below is the set of fields an insurance policy record needs to serve its purpose.

Client information

• first and last name, or the legal entity's name,
• a unique client identifier (an internal code, to avoid duplicates),
• contact details (phone, email),
• the date and source of client acquisition.

Policy information

• policy number, written in a consistent format,
• insurer,
• product or type of coverage,
• inception date and start of coverage,
• policy term or expiry date,
• premium, payment method (monthly, annual), and currency,
• policy status (active, dormant, cancelled, expired, in renewal).

Commission calculation data

• commission rate and the base it's calculated on,
• expected commission amount,
• the date and amount actually paid,
• a separate entry for initial and any renewal commission.

Advice information

• the date of advice or contract signing,
• a short summary of the client's needs and the options offered,
• a reference to the suitability documentation (if kept as a separate file).

Administrative fields

• date of the last change to the record,
• who last edited the record (if several colleagues maintain it),
• notes.

The number of columns isn't the goal in itself. The goal is to have everything you need for three things: calculating commission, alerting before expiry, and proving advice was given. Everything else is a bonus, not a necessity.

The part of the record agents most often neglect is a note on what they actually advised a client and why they recommended a given product. This isn't just good practice — it's documentation that insurance distribution regulation expects from agents and brokers when assessing a client's demands, needs, and the suitability of an offer.

What belongs in the advice log

• a short note of the client's needs and goals, as stated by the client,
• an overview of the options you considered (including ones you didn't recommend, and why),
• the reasoning for why the chosen product suited the client's circumstances,
• the date and form of the advice (in person, remote, by email),
• a signed or otherwise confirmed client statement, where required.

Why this matters for you too

Suitability documentation isn't just a formality for your regulator. It's also your protection when a client claims, years later, that a product wasn't suitable or that they weren't informed of certain features. Without a record, it's your word against the client's. With one, it's clear what was agreed and on what basis.

The advice log isn't there to prove you "did everything right." It's there so that, two or five years later, you can still remember why you recommended something to a client.

Practical tip

The advice note doesn't need to read like a legal document. Three to five sentences per policy sold, written right after the meeting while the details are fresh, are enough. Reconstructing a conversation later is unreliable and time-consuming.

A policy record contains client personal data, so general data protection rules apply to it, whether you keep it in a spreadsheet or a dedicated tool.

Basic principles worth following

• Purpose limitation. Collect data for arranging and servicing insurance contracts and legally required obligations, not for arbitrary other uses.
• Proportionality. Keep only the data you actually need — not everything you could learn about a client.
• Storage limitation. Retain data only as long as necessary for the purpose it was collected for, or as required by rules on retaining business and insurance documentation.
• Security. The record should be accessible only to authorized people, with sensible protections (passwords, sharing restrictions, control over copies).

Retention periods — a general guideline

Concrete retention periods vary by type of documentation and applicable law, so check what applies to your situation. As a general guideline, keep documentation on a signed policy for at least as long as the contractual relationship lasts, and keep advice and suitability documentation typically longer still, since suitability questions can arise even after a policy ends.

Don't rely on "I'll keep everything forever." Retaining personal data beyond a defined purpose is just as problematic as deleting it too soon.

Practical consequences for your record

• Decide who has access to the spreadsheet or tool holding the record, and review that access regularly.
• Separate active clients from those with whom no contract was ever signed (e.g., a mere inquiry), since different timeframes apply to them.
• When exporting or sharing the record (for example with an insurer or accountant), share only the data needed for that specific purpose.

This section is general in nature and isn't legal advice — consult an appropriate professional or check the currently applicable rules for concrete timeframes and obligations.

One of the most overlooked benefits of an organized record is the ability to be alerted in time before a policy expires. Without it, renewals happen whenever an agent happens to remember — or not at all.

Why this matters for the business

• A renewed policy means continued commission without the cost of acquiring a new client.
• Timely contact before expiry is an opportunity to review the client's needs and potentially cross-sell.
• A missed renewal means a lost client who may be picked up by a competitor without ever knowing they were "in play."

How to organize alerts in a spreadsheet

1. Keep a column in your record for the policy expiry date.
2. Add a helper column that calculates the number of days until expiry (the difference between the expiry date and today).
3. Use conditional formatting to visually highlight policies expiring in the next 30 or 60 days.
4. Review the highlighted rows once a week and contact those clients.

An alert you have to go looking for in a spreadsheet every week is better than nothing, but it isn't reliable. A reliable alert is one that comes to you, not the other way around.

What renewals require from the record

For an alert to be useful, both the expiry date and the client's contact information must always be current. If a client's details change (a new phone number, a new address) but the record stays outdated, the alert arrives too late or misses its target. That's why maintaining the record is an ongoing task, not a one-time setup.

Policy records and commission oversight are closely linked — in fact, they're the same underlying data used for two purposes.

Why they shouldn't be kept separate

To check whether an insurer paid the correct commission, you need exactly the same data the policy record holds: policy number, premium, inception date, product, and commission rate. If you keep a separate policy record and a separate commission record, you risk the two drifting apart — for example, a premium gets corrected in one but not the other.

How the record should be designed

• Each policy has one entry, from which both the client-management data (client, contact, renewal) and the commission data (base, rate, expected commission) are derived.
• Add a column for the commission actually paid and a column for the difference between expected and paid amounts.
• The policy status (active, cancelled, in renewal) should be the same across both purposes — not two different labels in two different files.

When the policy record and the commission record are the same thing, every new policy automatically becomes a commission-tracking entry too. When they're separate, the extra work sooner or later becomes less important than day-to-day client work — and the oversight stalls.

The practical effect

An agent with a single record can put together a monthly review in a few minutes: how much commission is expected, how much was actually paid, and where the discrepancies are. An agent with separate records first has to reconcile the data, which is an extra step that's often skipped in practice.

Most problems with policy records don't come from a lack of discipline, but from structural mistakes that only get worse as the portfolio grows.

Duplicate clients

The same client appears multiple times in the record — once as "John Smith," again as "J. Smith," a third time with a differently written address. The result is a fragmented view of the client: you don't see they already have three policies with you, so you never offer them a fourth.

Fix: assign every client a unique identifier the first time they're entered, and always search by that identifier, not by name.

Inconsistent policy number formats

A policy number is often written differently in the record, on the statement, and in the contract — with spaces, hyphens, or leading zeros. The result is that entries fail to match against the insurer's statement, which makes commission oversight impossible.

Fix: define one consistent format (for example, no spaces or hyphens) and apply it consistently, ideally with an additional "cleaned" column for comparisons.

Missing expiry dates

For some policies, the expiry or renewal date is left blank because it "didn't matter" at the time of entry. The result is missed renewals that you only notice once the client has already gone elsewhere.

Fix: make the expiry date a mandatory field for every policy entered, with no exceptions.

A record with no owner

When several colleagues share a record but it's unclear who's responsible for keeping which part up to date, the data gradually goes stale. Nobody's sure whether an entry is still valid.

Fix: decide who enters new policies, who updates statuses, and who reviews the whole record once a month.

Mixing statuses and free text in the same column

A "status" column contains "active" in one row, "Active" in another, and a blank field with a note elsewhere. This kind of inconsistency breaks filtering and automatic alerts.

Fix: use a closed list of allowed values for status fields, not free text.

For a small to mid-sized portfolio, Google Sheets is a perfectly adequate tool to start with — it's free, accessible from anywhere, and easy to share.

How to set up the basic structure

1. One sheet for policies, where each row is one policy and the columns match the field set described earlier.
2. One sheet for clients, linked to policies through a unique client identifier.
3. A locked header row and protected formulas, so a careless entry doesn't overwrite your calculations.
4. Conditional formatting for expiry alerts and commission discrepancies.
5. A single source of truth — avoid keeping copies of the same record in multiple files, since they drift apart quickly.

Where a spreadsheet hits its limit

• When the portfolio passes a few hundred active policies and manually finding duplicate clients or mismatched numbers becomes too slow.
• When several colleagues work on the record and questions arise about who changed what and when.
• When you want automatic alerts (for example, an email a week before expiry) that a spreadsheet won't send on its own.
• When, besides the policy record, you're also regularly checking commission statements and manual reconciliation takes up more and more time.

A spreadsheet isn't the wrong choice. The wrong choice is sticking with it once it costs you hours every month that could go to clients instead.

What switching to a dedicated tool brings

Switching doesn't mean leaving Google Sheets — it means a tool reads data directly from them, checks it, links it to commission rules, and automatically flags policies nearing expiry or commissions that don't match expectations. Your record stays with you, and less of the work is manual.

Commission Clarity is built for exactly this step: it reads data from your Google Sheets, organizes it into a clear client list, alerts you to expiring policies, and checks commission statements — all for EUR 20 a month. Instead of maintaining the record by hand at the end of every month, you get a tool that keeps it current continuously and tells you when to act.

If your record already exists in a spreadsheet today, the first step is simple: check whether it includes all the fields from this article, and add whatever's missing. Once the structure is in order, connecting it to a tool like Commission Clarity is a matter of minutes, not a day of work.