Reporting on former customer accounts and lost policies

Overview

Use this article to identify and report on accounts that have become former customer where all in-force policies have dropped to zero. You'll learn how to build an Account History report to track when an account's type changed to "Former Customer", how to count unique accounts to avoid duplicates, and how to filter by policy type (CL, PL, or Benefits) using separate reports.

Create an Account History report for lost accounts

An Account History report identifies the exact date an account's Type field changed from Customer to Former Customer, which is more reliable than the Date Last Modified field that can be updated by unrelated system processes. Use the steps below to generate the report.

1. Click the Reports tab and select New Report.

2. In the Create Report modal, select All under Category, then select Account History as the Report Type.

3. Click Start Report.

4. Once the report opens, add the following columns:

   1. Type

   2. Total In-Force CL Policies

5. Click Filters, then add the following filters:

   1. Field/Event equals Type

   2. Old Value equals Customer

   3. Type equals Former Customer

   4. Total In Force CL Policies equals 0.

6. Click Refresh to run the report.

Count unique accounts

An account may appear multiple times in an Account History report, once for each field change event. Use one of the two options below to count unique accounts and get an accurate total.

Show unique count in columns

1. Select Edit on the report.

2. Drag and drop the Account Name field to the top of the Columns section.

3. Click the drop-down arrow on the Account Name column header. and select Show Unique Count.

Add a summary formula

1. Select Edit on the report.

2. Add the Account Name field in the Groups section.

3. Click the drop-down arrow in the Columns area and select Add Summary Formula.

3. Enter the following:

   1. Column Name: Enter a name (e.g., "Unique Accounts")

   2. Formula Output Type: Number

   3. Decimal Points: 0

   4. Formula: ACCOUNT.NAME:UNIQUE

   5. Click Apply.

5. Select Refresh

Once the report is run, the unique account count will appear at the top of the report summary.

Differentiating lost accounts by policy type (CL, PL, Benefits)

Account History reports don't support custom objects, so differentiating lost accounts by policy type requires separate reports for each type. Follow the steps below to build a report for a specific policy type.

1. On the reports tab, select New Report.

2. Switch the category to All and select Accounts with Policies as the report type.

3. Select Start Report.

4. Select filters and enter the following information:

   1. Show Me: All accounts

   2. Created Date: All Time

   3. Record Type: equals Written PL

   4. Type: equals Former Customer

5. Then, select the down arrow to Add Cross Filter.

6. Enter the following: Accounts without Policies

7. Add the following Status to Add Policies Filter: New, Written, or Renewal and select Apply.

8. Repeat this process for each policy type (CL, PL, Benefits) as separate reports.