Retention rate report

Overview

Retention is a key indicator of customer satisfaction, loyalty, and agency stability. High retention means stronger recurring revenue, lower acquisition costs, and better carrier relationships. This approach is specifically designed for organizations who define retention at the policy level, and a policy is considered retained if it is successfully renewed. If you have different organizational definitions, this information may not be applicable to your use case.

Requirements

To measure policy retention, you’ll need a reliable data point that indicates whether a policy has renewed, such as a Policy Renewed checkbox. If such a field does not exist, you can create a formula field to capture renewal status.

Create a renewal formula field

1. Click the gear icon in the top navigation, and click Setup.

   

2. In the Quick Find search box, type and select Object Manager.

3. Search and select Policy.

   

4. Click Fields & Relationships.

   

5. Click New.

   

6. Select Formula as the data type.

   

7. Click Next.

   

8. Enter:

   1. Field Label: Policy Renewed?

   2. Field Name: will auto-populate.

9. In the Formula Return Type section, select Checkbox.

10. Click Next.

    

11. Click the Advanced Formula tab.

    

12. Enter the following formula:

AND (

NOT( ISNULL(VRNA__Superseded_By__c ) )

, VRNA__Superseded_By__r.VRNA__Record_Type_Code__c = 'W'

)

13. Click Next.

    

14. Adjust the field-level security settings, then click Next.

    

15. Choose what page layout(s) the field should be added to, then click Save.

    

Create the report

1. Follow the instructions in Create a new report.

2. On the Create Report modal, select Policies for the Report Type Name.

3. Group the report by the field that represents a renewed policy.

   

4. Add useful columns such as:

   1. Policy Number

   2. Customer Name

   3. Effective Date

   4. Expiration Date, and so on.

Add a summary formula

1. Click the dropdown and select Add Summary Formula.

   

2. In the Edit Summary-Level Formula Column modal, set the following:

   1. Column Name: Renewal %

   2. Output Type: Percentage

   3. Formula: RowCount/PARENTGROUPVAL(RowCount,GRAND_SUMMARY)

   4. Display: Select Selected Groups

   5. Row Group: Select Policy Renewed?

      

3. Click Apply. You'll see the Renewed % field in the Summary Formula column area.

   

4. Use filters to define the reporting period and policy type, such as:

   1. Expiration Date in the last 12 months

   2. Record Type Code = W

   3. Monoline/Package = Package Parent or Monoline

5. Click Save & Run.