Cohort Analysis - Need Help in creating Retention Matrix

[sudheerbolla]

Hi everyone,

Please help me in getting the cohert analysis. Thanks in advance

Trying to find retention count and percentage. the data has records of customers who bought the policy. A customer can have multiple records for the same policy if the policy has multiple events like create_policy, update_policy, cancel_policy, cancel_policy_non_payment. For the the records of a distinct policy there will be a crediting date which i consider as the date for different event types. There is no specific columns showing dates for events create_policy, update_policy, cancel_policy, cancel_policy_non_payment. all these dates to be arrived from the date mentioned crediting_date column against that particular event code. If a customer cancel the policy, the date in the credinting date against the cancelled record is taken and populated in PolicyCancelDateColumn. Through these date i was able to get the attrition of the customers for the policy taken in specific month and how many cancelled policy in that very month of the policy taken and the next month and there after.

For the active policy numbers i dont have any date as they have date as they are still alive. So , how can i get the count of the policies that are taken in specific month, and how many customers are retained in that verymonth and the following months... there after.

	POLICIES TAKEN	0	1	3
FEB-2023	100
MAR-2023	200
APR-2023	250
MAY-2023	300

Highlighted ones are the of the same policy number. In the data, a distinct policy no can have multiple records if that policy got update or cancelled in future date. The date we take as reference is the crediting date (first column).

Iam trying to find:

1. If in case 10 nos of customers taken policy in Dec-2022, how many customers got retained in 0 month, next month (1), following month (2)…. And there after. Another their percentage also
2. If in case 10 nos of customers taken policy in Dec-2022, how many customers cancelled in 0 month, next month (1), following month (2)…. And there after. And their percentage also