Mastering Applied Epic’s Retention Report and Why It’s Essential for Insurance Agencies
- March 18, 2024
Studies show that it costs insurance agencies 7-9 times more to attract new clients than it does to just keep existing clients. Retaining a steady collection of clients is vital to agencies. In addition, when paired with rounding out current accounts, retaining clients is a great way to reduce acquisition costs and increase revenue.
To help you get a firm grasp on your insurance agency’s retention rate, let’s talk about how to measure retention and how you can effectively run the Retention Report in Applied Epic.
The Formula for Measuring Retention
Let’s begin our deep dive by talking about what retention means. Retention is the percentage of clients or policies at the beginning of a time period—typically a year—that are kept at the end of that time period. It’s traditionally measured by taking the number at the end, subtracting the number of new clients, and dividing by the number at the beginning. Here’s what that formula looks like.
Traditional Formula:
[(Ce – Cn) ÷ Cb] x 100
Where
Ce is the # of clients at the end of the time period
Cn is the # of new clients during the time period
Cb is the # of clients at the beginning of the time period
Retention at the Client Level
Measuring retention at the client level can come with its set of challenges. First of all, tracking at the account level and not the policy level only shows a loss when the final policy cancels or nonrenews. There is no method for measuring premium loss as it happens in order to “stop the bleeding.”
Also, measuring client retention requires excellent maintenance of Client Accounts in Applied Epic. While you could bypass the Active/Inactive status and the Insured/Prospect types by using the Condition of Policies criteria on the Client List Report, an accurate Date Entered is essential. Unfortunately, most agencies find it difficult to achieve this, especially if they are entering Prospects long before they actually become Insured.
Finally, measuring retention at the client level cannot be done through a single report. Here’s what you will need:
- A Client list of all accounts entered before the start of the timeframe
- A Client list of all new accounts entered during the timeframe
- A Client list of all accounts entered on or before the last date of the timeframe.
If using the Client List is not an option, there is another way to measure your client retention from the policy level with some Excel skills.
First, run a policy list/Book of Business for policies in force at the start of the time frame (effective Open-start day, expiration day after start-Open). Generate the report in Excel and remove duplicates for Account Lookup Code. Do the same for the last day of the time frame and new business during the time frame. If you don’t trust your data, do a VLOOKUP or pivot table to compare the start list with the end list to make sure that there are no missed expirations or starting policies that just haven’t been entered yet.
Overall, to harness valuable insights about retention, you need to conduct a lot of manual work. But luckily, Applied Epic users have a built-in retention report that makes the process a whole lot easier!
Applied Epic's Built-In Retention Report
Applied features a handy Retention Report – Multi-Layouts* located in the main Policy section of Reports/Marketing. This report is part of the newer Report Pack. If you do not see it in your database, contact Applied and ask for it. They typically load it the same day as your request.
This report tracks retention by policy and is useful for seeing the losses as they occur, rather than waiting for a client to fully leave. It uses a similar formula as above, but instead of taking into account new policies, it measures those that are lost.
Epic’s Formula:
[(Total Policies – Lost Policies) ÷ Total Policies] x 100
Epic calculates this with both # of policies and estimated premium.
This report takes into consideration all policies that expired during the specified time frame and subtracts anything that is counted as Lost Business. The criteria for running this report are:
- Expiration Date: -365 days through Today (or use a Fixed Date Range)
- Policy Status: Exclude any quoting or error statuses, like NWQ, MKT, ECU. You are only looking for actual written policies that were either renewed or cancelled.
Out of the box, Epic only considers CAN – Cancelled – General, CIR – Cancelled – Insured’s Request, and CNP – Cancelled – Nonpay. If your agency uses other cancellation codes like NON – Nonrenewed by Carrier or CSD – Cancelled – Sold, then the report layouts must be updated (see below). Note: CRW – Cancelled – Rewritten to a New Carrier is not Lost Business and should not be counted as such.
The report comes with several versions of a Summary and Detailed layout, much like the Book of Business – Billed/Est – Multi-Layouts* report. Major sorts include Detail and Summary by Producer, PPE, and Branch. Like all reports in Epic, copy the layouts and create Major Sorts for Account Managers or any other options (Policy Type, etc).
A Word About Configuration
If you choose to use other Cancelled statuses, consider acknowledging controllable vs. uncontrollable losses. For example, clients who are deceased or closed their business are uncontrollable losses. Knowing these numbers and reasons helps inform your decisions.
Also, ensure your cancellation statuses are clear enough that your staff is not confused about when to use each one. Cancelled – General gives no information about why the policy was lost. Avoid adding too many statuses to prevent decision fatigue. Create a cheat sheet of when to use each cancelled status.
Cancellation Workflow Considerations for a Retention Report
When cancelling a policy mid-term, make sure to cancel only the current term and error out any “renewal” (that will never be an in-force policy) that may have downloaded or renewed in Epic prior to the cancellation request/notice.
When cancelling a policy at renewal, make sure to renew the policy first and Cancel or Not Issue on the renewal term (cancel flat). Keep the expiring term as in-force with a valid status, since that policy was in effect for the whole term.
Never have a cancellation status on both the expiring term and the renewal. This will duplicate your lost business numbers and affect your calculated retention rate.
Keep Your Data Clean!
It’s always important to have good workflows and to monitor your data regularly. Review the Lost Business Report monthly and consider, was the policy really lost or was it rewritten to a new carrier? Review the Missed Expirations Report monthly: Are there policies that cancelled at renewal but were allowed to just expire? Create a “Cancelled but Status is not Updated” Report, then run a list of policies where the SSR Action = Cancel but status is not CAN, CIR, CNP, CRW, NON, etc.
Understanding and Updating Your Retention Report
IMPORTANT NOTE: ALWAYS copy to your My Reports before adjusting the layout!
Totals and formulas are calculated using Underlying Math section at the bottom of each Major Sort and at the Report Totals and, as mentioned above, only contains subtotals of policies with CAN, CIR, and CNP statuses. You will need to add NON and any other lost business statuses used by your agency.
The Retention calculation section contains the following totals/formulas:
- Total Premium/Policies are counted from the Detail section. This is a “normal” subtotal that is typical of Epic Reports.
- Total Lost Premium/Policies – a Formula that is calculated from the Underlying Math section – adds up each Total field.
- Total Less Lost Premium/Policies – a Formula that subtracts the Lost number from the Total number (this is the number that is retained).
- Premium Retention % – a Formula that divides the Total Less Lost Premium/Policies by the original Total and multiplies by 100.
Don’t reinvent the wheel. Just copy and paste fields, and then update formulas as needed. Use the Field Properties to adjust font, formulas, and so forth.
Copy/Paste the fields in the Underlying Math section (use CAN) and adjust the criteria to other status codes.
Use the dropdown next to Fields to quickly locate Total Fields and Formula Fields (clicking on the field in the dropdown will highlight that field in the canvas).
Use the Formula Builder to add Total and Formula fields to each formula.
Make the changes on one Detail layout first, then copy the layout and update the Major Sort. This is much easier than trying to update the formulas in every layout.
Summary layouts have the same concepts/formulas, they are just arranged differently. Update the formulas for one Summary and one Detail, and then copy from those.
Summary
Tracking your agency’s retention is a vital tool in your arsenal. It helps you keep tabs on trends and identify any issues with your clients or carriers. Measuring your retention rate is a complicated process no matter how you decide to measure it, but it’s worth it.
If you need assistance with the Retention Report or any other reports, be sure to check out our Applied Epic resources on our website and our YouTube channel. We have blogs, prerecorded webinars, and Consulting Corner webisodes available.
Kite Technology also offers a variety of Consulting Services, from migrations and training, to optimization, bookkeeping, and much more. Interested in expert Applied Epic support? Schedule a time to talk with us!
Jenny Honican
Agency Consultant
Kite Technology Group
