Finding Replacement Insurance Policies

Question

Good morning folks - I'm new to Alteryx and trying to learn the terminology/process of how the tool works as related to some other tools I've used.  This community is awesome so I thought I'd ask for some help.

I have a dataset of life insurance policies written in the last 2 years and I'm trying to detect when a customer lapses or surrenders a policy and immediately buys another policy on the date of the cancellation or a few days later.   I have not been able to find this topic when searching, but doubting I'm the only person who has tried this.

I'm not familiar with all the techniques to iterate through the data and can use some guidance (I think that's the right term).  Which tools would handle this the best?  Any help is appreciated!

Some Sample data is below, Customer AA and CC are the ones I'm interested in finding.

Thanks, George

PolicyNumCustomerIssueDatePolicyStatusPlanCodeTerminationDate1AA12/30/2019LapsedLIF 1012/30/20202BB11/14/2016ActiveAH 12 3BB12/1/2015ActiveAH 12 4AA1/3/2021ActiveLIF 20 5CC4/18/2016SurrenderedLIF 104/18/20166CC4/18/2016ActiveLIF 20

atcodedog05 · Answer

Hi @George_Fischetti

Here is my take on it which is slightly different.

Workflow:

1. Using Multi-field formula to convert date to alteryx date format.

2. Using Multi-row formula to check for lapsed and surrendered policy is there a new active policy for the same customer.

Hope this helps : )

policy.yxmd

George_Fischetti · Answer

Thank you Dawn - I will try this as soon as my new dataset arrives.  Appreciate your input.

DawnDuong · Answer

hi @George_Fischetti

I would suggest the following schematic:

1) Sort your data by customer name, issued date, termination date, and status.

2) use a Multi-row formula to calculate the number of days between lapsed/surrendered and the next immediate active policy. Note the use of the Group by option with the Multi-row tool.

I attached here a sample workflow. Hope it helps you.

Dawn.

ARComm_for_GeorgeFischetti.yxmd