One thing is for sure. Patient churn rate is the hardest metric of patient lifetime value to accurately determine.
What is patient churn rate and patient lifespan?
Patient lifespan is the number of time periods (e.g. years) a patient sees you, on average.
Patient churn rate is the percentage of patients that leave your practice within this time period.
Patient lifespan is the inverse of churn rate.
Many retailers might use one year for determining churn. For many providers, this is not appropriate.
Opthamologists are seen every 2-5 years. Counselors are seen every few weeks. If we were to use one year as a basis for churn our estimates, and marketing budget, would be inaccurate.
That is why it is crucial to first find our patient recall time. We cannot know how long a patient will stay with us if we do not know when we expect them back.
We must determine churn as a function of our practice’s appointment frequency.
Count return patients
Now, that we have our max patient recall time Tmax, we need to use it.
We need to find total patients over twice this span. Then, we need to find those that returned within a single span.
It is easier to use Excel or Google Sheets in conjunction with EHR data for the following steps. That said, you can also count them manually.
While you do not need to calculate Tmax every year, you should count your total and returned patients every year.
Patients within twice your max Patient Recall Time
Using your EHR, pull a report of all patients seen within 2*Tmax years from December 31 of last year. For example, if your max patient recall time is 2.2 years, you want 4.4 years of appointments.
Count the total. We will represent it as p2Tmax.
This figure will be less than your total appointments. Some patients will have seen you two or more times.
Having trouble finding a report in your EHR that shows you unique patients? We can do it for you. Or, you can export the data yourself and open it with Excel.
First, open your extract of patient names and encounter dates. You exported this when you were finding your patient recall time.
Find an empty space in the file and enter the formula below. It assumes that your patient identifiers are in column “B”. Change the references if they are not.
You can also find this number by removing duplicates.
Select column “B”, or whatever column contains your patient identifiers. Select “Remove Duplicates” in Excel or Google Sheets.
Select the entire column that you just removed duplicates from. At the bottom right of the sheet, you will find “Count: ###”.
Just make sure to undo the “Remove Duplicates” once complete. We’ll be using this data later
Save this number. It is the patients you saw within your max patient recall time.
We represent it with:
Find the subset of patients who returned
Save your previous EHR report. Copy it and modify the criteria to show only patients seen within Tmax years.
Now, “circle” all of the patients on this report who also appear on the prior report. Count the circles and you have. The number of circles represents f(p2Tmax). We will use this number later.
We can automate this task for you, too. Just let us know if you need help.
Head back to your export of patient encounter data.
Filter this sheet to show only Tmax years from December 31 of last year.
For example, if your average appointment frequency is 2.2 years, you will now see 4.4 years of appointments. Use a filter on this sheet to hide all but the last 2.2 years of appointments.
You can use Excel or Google Sheets’ filter on the Date column to filter out the old dates. Enter your lookback date into a “Filter by condition”.
Confirm that you have filtered out the older dates. Now, copy this sheet to a new sheet in the same Excel or Google Sheets document.
Insert a new column into this sheet called “Repeat patient?”. Enter an IF formula into the first cell of this column to count
This formula looks for all patients on the first sheet. It returns a “1” if it finds a match and a “0” if it does not.
Once again, we are assuming that your patient identifiers are in column “B”. Change the references if they are not.
Finally, select the entire “Repeat patient?” column. Find the “Sum” in the bottom right of the screen.
This sum will be used later and is:
Calculate Patient Churn Rate and Patient Lifespan
There is a lot of misinformation online about patient churn rate and patient lifespan.
Explanations are either vague or full of faulty assumptions.
Patient Churn Rate and Patient Lifespan Formulas
Now, we will substitute the numbers we just found into these formulas.
To calculate patient churn rate and patient lifespan, you need to have done the prework. You should have found your patient recall time, the total number of unique patients seen within twice this timeframe, and the subset of those that returned.
Once you have confirmed these values, you can calculate your patient churn rate and patient lifespan.
The first formula represents the patient churn rate.
The second formula is your patient lifespan. It is the inverse of the first formula, times your appointment frequency.
These calculations can get tricky. Again, contact us and we will do it for you.
Once you have your patient churn rate and patient lifespan, save them. They will be useful for years to come.
Click here to calculate patient lifetime value.