This is a completely fictitious study to illustrate how to determine catchment area (or the market share depending on how you choose to define it). Imagine an equally fictitious cardiac electrophysiologist who serves 5 communities from City Alpha population 200,000. Each of the other 4 cities (Bravo, Charlie and Delta) have populations of 100,000. There are 500 family doctors and 3 cardiologists. About ½ of the patients from City Delta are seen in another neighboring centre.
When the study is published the electrophysiologists does an education campaign with the family doctors and cardiologists in the area and now wants to determine if patients with AF area being treated according to the new specifications.
He first determines the rate of AF in the general population. After reviewing population studies he determines that the overall rate of people that will need the electrophysiology study is 2.5 per 1,000 people per year. (As a quick ‘aside’ we’ve found these rates to be predictable for a large number of procedures and disorders). He also knows that the physicians of City Alpha have already adopted the standards of the study and he receives 520 referrals per year. Since (520 * 1,000) / 200,000 = 2.6 the population study is in agreement with his experience. Based on these two checks he determines (at a rate of 2.5 per 1,000) that the number of patients he should be seeing from each of the cities is:
City Alpha: (200,000/1,000*2.5) = 500
City Bravo: (100,000/1,000*2.5) = 250
City Charlie: (100,000/1,000*2.5) = 250
City Delta: (100,000/1,000*2.5) * ½ = 125
After data mining his EMR he determines that the number of actual referred patients is:
City Alpha: 520 (104%)
City Bravo: 240 (96%)
City Charlie: 120 (48%)
City Delta: 180 (144%)
Based on these numbers he determines that City Alpha and Bravo are behaving as predicted. City Charlie is much lower than expected (he subsequently found out a cardiologist hadn’t read the study yet) and that he was seeing many more patients from City Delta than he expected (turns out the neighboring electrophysiologist likes his vacation time).
While these calculations are relatively easy to do on paper they are more complicated in reality because:
-city names are misspelled
-people name an area that is not a census subdivision (e.g. a small hamlet)
-the population counts are over multiple census subdivision areas
The simplest way I found to deal with the mess of data that comes from data mining is to assign a census subdivision to each patient.
In Excel:
Step 1: Download the data. You should have the DateTx, PatientID and PatientCity. Place this in a sheet and name it RawData
Step 2: Create a list of census subdivisions from government data and each population. Call this sheet CensusSub
Step 3: Using the Pivot Table function to create a unique list of PatientCity. Cut and paste that list into a new sheet called CitytoCensus. Next to each unique city/town assign it to one of the census subdivisions (spelled the same way as in the CensusSub sheet)
Step 4: In the RawData sheet add a column to each patient record called Census. Use the vlookup formula to assign a census subdivision to each patient record. The formula will look something like =VLOOKUP(B3, citytocensus!$A$2:$B$20,3,FALSE). See the video below on how to use the vlookup function. The FALSE statement forces an exact match rather than an approximate.
Step 5: Create a PivotTable of the raw data and place the DateTx in the row fields (group by year and quarter) then move it to the column field. Place Census in the row fields and Count of PatientID in the Data Field. This will create a count of patients by quarter, year and census subdivision.
Step 6: Convert to a rate per 1,000: Cut and Paste Special >>Values this summary. Use the census subdivision count (in censussub sheet) and actual patient count per census subdivision to determine the actual rate of referral per 1,000 for each census subdivision.
This last formula will be based on the following:
=Count of PatientID*1,000/censuspopulation
If the censussubdivision is in column A and the Count of PatientID in column B the formula will look something like:
=B2*1000/(VLOOKUP($A2,CENSUSSUB!$A$1:$B$20,2,FALSE)
As usual the devil is in the details. Doing a catchment area analysis is a powerful tool to determine the area and patient population that your office or clinic is covering. Varations in patient load from neighboring area can explain sudden changes in health care wait time. For emergency departments with suddenly long ER wait times it can also determine the cause of surges in patients. If needed I can post a video of doing this in an actual spreadsheet.
Posts
No comments:
Post a Comment