Forum Discussion
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 |
1 Reply
itsmeanuj I believe the answer to your question lies in the quantity of medication consumed each month. With a 30-day month and a 14 day allowed lag, any given month that has a consumption of at least 16, qualifies as Persistency. By introducing a few helper columns, I calculated the monthly consumption (opening balance + purchased - consumed = closing balance), where the closing balance can not be less than zero.
The attached workbook demonstrates this is reproduces a Persistency table (in the green area) equal to the on in your example. But, perhaps I completely misunderstood. If so, please ignore.
