Calculate Monthly persistency of customers

Highlighted
Occasional Visitor

Calculate Monthly persistency of customers

Hi,

I am working on a client project and need to calculate the monthly persistence of each customer based on their purchase in those months.

Problem statement: Medicine is subscribed to a patient (1 pill each day). Now based on his purchase of medicine, we need to calculate how persistent he is for the next 6 months on medicine.

In an ideal scenario, the patient should take medicine continuously, but he will still be called persistent unless there is a lag of more than 14 days (see row 4 in the example below - he just purchased medicine for 25 days but his persistence is 1 in M1 since lag is less than 14 days).

He can either purchase medicine every month (see row 4 below) or in bulk for a few months together (see row 1 below).

So we need to calculate Persistency for each month as 1 or 0.

 Allowed Lag 14 Treatment Days M1 M2 M3 M4 M5 M6 P1 P2 P3 P4 P5 P6 90 30 12 0 0 0 1 1 1 1 0 0 30 0 30 0 30 0 1 0 1 0 1 0