Insurance policy renewals missed or found late

Keep one SharePoint list with one row per policy, with an expiry date, a producer (the person who owns the account) and a stage. A daily Power Automate flow emails the producer 90, 60 and 30 days before expiry, and a saved view shows what is coming up and what has already lapsed.

This guide is a record and a reminder tool. It does not tell you what rules apply to your agency, and it does not replace your agency management system if you have one. Exact names may differ on your screen.

One policy across its year

Picture Policy 12, bound last spring for Client 7. Its row carries a single Expiry date, and the Stage moves as the year goes on: 1 Booked, then 2 Approaching once the date is near, then 3 Quoting while terms are priced, then either 4 Renewed or 5 Lost. The row is never deleted. When Policy 12 renews, add a new row for the new term and set the old one to 4 Renewed, so last year's date stays on record. Our advice, not a Microsoft rule.

The build has three parts: two linked lists, a few views, and one daily flow with warnings at 90, 60 and 30 days. Starting from an Excel renewal sheet? "Import an Excel sheet into an existing SharePoint list" covers the import. Also read "Replace a spreadsheet tracker with a SharePoint list" first. A broader contract or supplier list is covered in "How to set up a vendor list with renewal dates in SharePoint".

Store as little client data as you can

A policy list can hold personal and business information. Decide what goes in before you build.

Ask your agency's owner, compliance contact or attorney what you may store in a SharePoint list, and where. This guide does not state any rule.
Our advice, not a Microsoft rule: keep only a client ID such as Client 7, a policy number, line, carrier, expiry date, producer and stage. Leave out birth dates, government ID numbers, bank details and claim details.
A view is not security. It only changes what is shown, and anyone who can open the list can switch views. To limit who sees the list, use permissions. Microsoft describes how in its help page on customizing permissions for a list or library. Ask your admin which setup fits your site.

Policies and clients linked by a lookup

Build two lists on your team's SharePoint site. Microsoft's help page on creating a list says to select "+ New", then "List", then "Blank list", enter a name, and select "Create". Exact names may differ.

Clients list. One row per client. Title holds the client ID, such as Client 7. Nothing else is needed.

Policies list, called "Policy renewals". One row per policy, so a client with three policies has three rows. Rename any column to match your office.

Title becomes the policy number. Text keeps leading zeros and letters that a Number column would drop.
Client, Lookup, pointing at the Clients list and its Title column. Pick the client from a menu instead of retyping the ID, so Client 7 is spelled one way everywhere.
Line, Choice: 1 Auto, 2 Home, 3 Business, 4 Life, 5 Other. Our advice, not a Microsoft rule: the number prefix keeps the order.
Carrier, Single line of text.
Expiry, Date and Time, date only. Use one word. Microsoft says a space in a column name is stored as "_x0020_" inside a flow query.
Producer, Person, set to required. It decides who gets each warning.
Stage, Choice: 1 Booked, 2 Approaching, 3 Quoting, 4 Renewed, 5 Lost. In this guide, 1 Booked means the term is in force, 2 Approaching means the expiry date is near, 3 Quoting means terms are being priced, 4 Renewed means a new term is bound, and 5 Lost means the client left.
Notes, Multiple lines of text, kept short.

Select "Add column" at the end of the heading row, pick the type and save. Then enter six sample policies, such as Policy 12 expiring on a Friday and Policy 13 the next Monday, and confirm that sorting by Expiry gives true date order.

Who renews in the next 60 days

Build these in the classic view editor, which the [Today] filters need. Our advice, not a Microsoft rule: use it for "is not equal to" filters too. Use "Save view as" to start a second view from an existing one.

Microsoft's help page on filtering a view says "It is not possible to use the [Today] or [Me] filters when filtering using a column header." It also says "You must use the classic view editor to filter columns using these keywords." Its examples include a Date column filter that is less than [Today].

View "Renews in 60 days"

Under Filters, pick "Show items only when the following is true".
Expiry is greater than or equal to [Today], And Expiry is less than or equal to [Today]+60. Our advice, not a Microsoft rule: the plus-days form follows the [Today] - 30 pattern in the Microsoft examples, so test it with a sample row.
Add And Stage is not equal to 4 Renewed, And Stage is not equal to 5 Lost.
Sort by Expiry, ascending. The nearest date leads.

View "Expired, still open"

Expiry is less than [Today], And Stage is not equal to 4 Renewed, And Stage is not equal to 5 Lost.
Every row here has a date that has gone by with no closing stage. Treat each as same-day work.

View "My renewals"

Producer is equal to [Me], sorted by Expiry, ascending. Each producer sees their own policies. It is a convenience, not a lock.

The reminder flow

A view only helps when someone opens it. A flow does not wait to be asked. Open https://make.powerautomate.com, create a scheduled cloud flow and name it "Policy renewal reminders". Exact names may differ.

Trigger: Recurrence, every 1 day, set before your team starts work.
Add a SharePoint "Get items" action for the "Policy renewals" list.
In Advanced options, Filter Query. Microsoft's page on Get items says filter queries use OData and lists lt, le, gt, ge, eq and ne. For policies expiring 90 days out, use a one-day range:

Expiry ge 'START' and Expiry lt 'END' and Stage ne '4 Renewed' and Stage ne '5 Lost'

Replace START with: formatDateTime(addDays(utcNow(), 90), 'yyyy-MM-dd')

Replace END with: formatDateTime(addDays(utcNow(), 91), 'yyyy-MM-dd')

The range matches a date column that has a time part. Text values go in single quotes, and the Stage text must match exactly, number prefix included.

Top Count: Microsoft says the default item limit is 100, and: "If you are working with large lists, you can increase this limit up to the list view threshold limit of 5,000." Set Top Count above the most renewals you expect on one day, never past 5,000. If the list holds more than 5,000 rows, also turn on Pagination in the action settings.
Add "Apply to each" over the result, then "Send an email (V2)" to the Producer's email. Body: client, policy number, expiry date. Subject: "90 days".
Copy the Get items and the loop twice. Change 90 and 91 to 60 and 61, then 30 and 31, with subjects "60 days" and "30 days".
Save, then test with a policy expiring exactly 90 days out and yourself as Producer.

Where renewal trackers go wrong

A date typed as plain text cannot sort or filter as a date, so Expiry must be a Date and Time column. A row with no Producer has nobody to email, which is why Producer is required.

Free text in Stage breaks the filters above, because the flow compares against the exact text of 4 Renewed and 5 Lost. Use the numbered Choice values.

Health, ID number, bank or claim details do not belong in a row, and leaving the site open to the whole organization exposes whatever is there. Ask your admin who has access today.

Last, do not trust the flow alone. Open "Expired, still open" each week; it should be empty, and each producer should say which stage their upcoming policies are in.

Limits

The list holds what a person typed, so a stale Stage means a stale list, and nothing here checks Expiry against a carrier. The flow only sends email and cannot tell whether it was read; this guide does not say which Power Automate licenses you need, so ask your admin.

Related guides: "Send a reminder before a due date in a SharePoint list" covers the general reminder build, and "Late tasks nobody notices" covers items already past due. Lists that outgrow one page of results need "Power Automate Get items stops at 100 rows: how to get them all".

When to get help

You can build all of this yourself in Microsoft 365. If you would rather have it built and tested for your team, I do that. A tracker like this is a fixed price: $5,000 for one, built in 2 weeks. See "What a SharePoint tracker costs to build". Bring one example of the problem to a free call.

Tell me what's still manual

I am Al Davies. I run 250 High Street LLC and build SharePoint trackers and Power Automate flows for operations teams. I work remotely. The first call is free, and I will not try to sell you anything.

See trackers I built: Tools I built

FAQ

What is an insurance renewal timeline?

In this guide the flow emails the producer 90, 60 and 30 days before each expiry date. Our advice, not a Microsoft rule: pick the days your agency works to, and change the numbers in the flow.

What do the renewal stages mean?

In this guide, 1 Booked means the term is in force, 2 Approaching means the expiry date is near, 3 Quoting means terms are being priced, 4 Renewed means a new term is bound, and 5 Lost means the client left.

Can I move my Excel renewal tracker into SharePoint?

Yes. Make the Policy renewals list, then follow the import steps in the guide on importing an Excel sheet into an existing list.

Does this replace my agency management system?

No. It is a simple list that records dates, owners and stages and sends reminders. If you already keep policies in another system, use this only if you want a shared reminder list. Ask your own team what records you must keep.

Why does the Get items action return only some rows?

Microsoft says the default item limit is 100. Set Top Count to a number above the rows you expect. Microsoft says going past 5,000 causes an error.