Channel Persistency: Dues vs Collection

Channel Persistency: Dues vs Collection

Channel Persistency: Dues vs Collection

Persistency refers to the measure of how many customers renew their policies. Insurance policies are sold through different sales channels like banks, agents, brokers. Channel persistency shows the number of consumers that choose to renew their policies at the end of the year via the existing sales channels.

  • A prominent Life Insurance client collects data from its different sales channels/sub-channels and consolidates the data. This data contains details of policies up for renewal in the given month.  This file is used to create daily dashboards containing indicators like daily and monthly collection%, which help in assessing the performance and efficiency of the sales channels. 
  • These daily reports are termed as Dues vs Collection as it reflects the expected collection of premium amount v/s the actual premium amount. These reports serve as a base to reflect the majorly popular channels and products and help in tracking and comparing the business output within the channels themselves.


  • Timely issuance of daily detailed reports derived from largescale raw data (greater than 20 lakhs) is a strenuous task. The manual preparation and collation of policy data for a given month takes around 2 man-days and thus utilizes a lot of resources. 
  • The reports being complex in nature are heavily prone to manual errors such as :
    • Incorrect mapping of hierarchy structure (once identified the monthly collated file has to be prepared again)
    • Tracking of performance indicators (incorrect tracking leads to a mismatch in monthly reconciliation of Dues vs Collection)

SheetKraft Methodology

  • The multiple raw datasets are filtered to retain policy data for policies up for renewal in a specified month. The data is now segregated on the basis of sales channels and certain calculations are performed.
  • Segregated data is sorted and crucial indicators such as Total Amount, Billing Frequency, and Ticket size are created. The data is further mapped for hierarchy from different sets of master data to reflect the business performance for each node present in the multiple channels and then collated. This acts as a consolidated report from which multiple dashboards are generated on the basis of certain criteria to create multiple daily performance reports. A few of the major criteria for dashboards include the likes of:
  • Product Name
  • Hierarchy Heads
  • State, Cluster And Zonal Heads

Before Sheetkraft Automation

After Sheetkraft Automation

Impact of SheetKraft

Sheetkraft has been able to successfully create the application to generate the monthly and daily reports in a scheduled, hassle-free, swift manner. 

  • Automatic collation of policy data for a given month.
  • A single centralized database to store daily data for tracking performance indicators.
  • System driven and error free reconciliation of Dues vs Collections and daily and monthly collection % calculations.
  • Facility to mail the generated reports to the stakeholders. 


Savings of ~ 40 resource hours/month

Streamlining report generation (20+ reports) while eliminating all possible errors.


Quantum Phinance
Enable registration in settings - general