Though the implementation
of a proper Electronic Medical Records
system may be unaffordable for a small
clinic most clinics use some form
of computerised patient records. Detailed
analysis of the data from these systems
can provide information on key performance
indices and are invaluable for strategic
Computers have become an indispensable
part of every clinic. Though it may not
be economically viable to install an 'Electronic
Medical Records' or EMR system in
a small clinic, computers are used in some
form or other for patient data management.
An EMR is software that allows
you to create, store, organize, edit and
retrieve patient records on a computer.
But it is more than just the electronic
equivalent of paper. Advanced EMRs also
allow you to automate many time-consuming,
paper-driven office tasks. Some are even
equipped with 'Computerized Physician Order
Entry'2 tool. EMR software has many advantages
which are beyond the scope of this article.
Most of the clinics use custom
made software to record patient data, but
this does not include all the features of
a typical EMR. This is generally used for
storage of demographic and personal data,
storage and retrieval of patient ID and
appointment scheduling. Even these simple
software packages require a database backend
which in the majority of cases is MS Access
though some use more advanced RDBMS (Relative
Data Base Management Systems)3 like Oracle
or MS-SQL server.
The data stored in these database
systems is usually used only for income
auditing, billing and other similar tasks.
However this data could provide a wealth
of information which is important from the
practice monitoring and marketing perspective,
though this is often overlooked in small
practises and clinics.
Big hospitals use various
kinds of data to monitor performance. However
small clinics use only traditional financial
statements. Going beyond figures like net
revenues and costs and using meaningful
financial reporting can offer a deeper and
ultimately more effective analysis.
Medical practice is traditionally considered
as a venerable profession outside the realm
of a business environment. However the trend
is slowly changing and more and more practitioners
are considering their practice as a business
and their patients as customers. This is
especially true for certain specialties
like cosmetology and dentistry and they
have started realising that the principles
of management applies to practice management
as well. Hence it is important to think
what else needs to be monitored apart from
total income in a small medical establishment.
A few of the indices which
may be important in practice management
are mentioned below.
1. New Patient flow: This
can be expressed as a percentage of previous
month or a certain time period as it is
important only when considered with respect
to the previous month. It is not relevant
for a new clinic. However for an old clinic
it indicates the efficiency of the marketing
2. Old patient rate (OPR):
This can be expressed as a percentage
of total number of patients. OPR indicates
the efficiency of the practitioner to retain
the patients. However for a more sensitive
measure of patient retention, the number
of old patients returning after the standard
follow-up period needs to be considered.
3. Average collection per
new patient: This index may increase
the income for a short period. However it
is important to retain it within acceptable
limits for each specialty as the longterm
effects are not favourable.
4. Average collection per
patient: This is a very sensitive measure
of patient loyalty.
5. Revenue growth as a
percentage of previous year.
6. Descriptive statistics
of geographic location, socio-economic status,
demographic profile and referral from other
practitioners and clinics: This is very
important for strategic planning.
It is important to understand
that most, if not all, the information needed
to calculate the above indices is essentially
data that the hospital already has available.
It is a matter of taking the various datasets
and merging them into useful information
upon which decisions can be formed.
Databases are fundamentally
tools to allow people to organize and manipulate
large amounts of data using the power of
the computer to quickly translate and deliver
that information in a humanly readable format.
Databases fundamentally organize
the data into hierarchies. The building
blocks for creating databases starting from
the top level are the data structure or
schema. Each data structure is made up of
a series of records, and each record has
a set of predefined fields.
Databases use a series of
Tables to store the data. A table
simply refers to a two dimensional representation
of your data using columns and rows. Each
column in the table is given a unique name
which would be something like first_name,
To reduce the redundancy of
information, some tables will have a primary
key which can be used to link to other tables.
It will become a foreign key for the linked
table. For example if a patient comes for
multiple visits we may have to enter the
patient data each time if there is only
one table. However if the patient data is
stored in a separate table with a unique
number, only that number needs to be entered
to the 'visits' table so that data redundancy
is minimised. This unique number is called
as the primary key for the first table and
foreign key for the 'visits' table.
If you give data from your
patient management system to database professionals
for analysis then you have to deal with
certain crucial ethical issues like data
privacy. The data often includes the personal
details of the patient, diagnosis and investigation
reports which have to be treated as confidential.
Hence it is imperative for healthcare practitioners
to learn basic data warehousing techniques
to remove sensitive data before outsourcing
it to professionals for more detailed analysis.
Data warehousing is the process
of consolidating data to a central store
so as to make analysis of data easier. The
practitioner who owns the data has to remove
the personal information in the database
before giving it for analysis. Usually all
the personal information will be grouped
together in a single table. Though in some
cases this database table can be safely
dropped or removed. This may make the database
unstable because the primary key is often
stored in this table. This table can also
contain some useful information like age,
sex, region, occupation and socio-economic
status. Hence it is better to remove individual
fields like first_name, last_name, phone
number, address, email etc.
Data warehouse also implies
that the data is manipulated and consolidated
in a separate location, different from operational
data used for day-to-day activities. For
a small clinic, it essentially means that
you have to take a copy of the database
and work on that so that original database
is not damaged. Though I have used the term
'Data warehousing' here, it is a much broader
concept than just backup and deleting unnecessary
Microsoft® Access is a
powerful program to create and manage your
databases. It has many built in features
to assist you in constructing and viewing
your information. A detailed description
of MS Access is beyond the scope of this
article. Deleting a field is usually done
in the table design view as depicted in
Fig 1. It involves the following steps.
1. Click on the Tables tab
on the Access main screen
2. Highlight the name of the table to be
modified and click on the Design button.
3. Make the necessary changes.
4. Save the table by pulling down the File
menu and choosing Save.
5. Close the table by pulling down the File
menu and choosing Close.
A database query is usually
expressed in SQL or structured query language.
The indices I mentioned earlier can be easily
calculated from the database by using simple
SQL. In MS Access you can easily create
a query in Design View as depicted in Fig
2 to filter the information in your table.
You establish a set of criteria when you
create a query.
However there is other information which
can be derived from the database apart from
the calculation of above indices and descriptive
statistics of demographic data like age,
sex and location.
Data mining has been defined
as "The nontrivial extraction of implicit,
previously unknown, and potentially useful
information from data" and "The
science of extracting useful information
from large data sets or databases".
Although it is usually used in relation
to analysis of data, data mining, like artificial
intelligence, is an umbrella term and is
used with varied meaning in a wide range
of contexts. It is usually associated with
a business or other organization's need
to identify trends. Data mining techniques
can be used for patient data also. It will
help us find patterns which may not be evident
at the first look like the referral pattern
between specialists in a clinic. Data mining
converts data into knowledge.
THE RIGHT STRATEGY
As with any complex business,
clinics can create all kinds of statistics.
But if not used wisely, the only thing all
these data will add up to is a bunch of
numbers. Organizations that get the best
results from this data are those that get
all levels of leadership involved in analyzing
Strategy formulation frequently
includes a SWOT analysis, or an assessment
of internal strengths and weaknesses (SW)
combined with environmental opportunities
and threats (OT).
However, SWOT analysis may
sometimes fail to reflect the true picture.
Senior managers act only on perceived strengths,
weaknesses, opportunities, and threats as
filtered through their own lenses and those
of middle managers, physicians, nurses,
and others in the organization. These filtered
perspectives prevent senior management from
seeing the real SWOT, thereby creating a
potential for suboptimal strategic decisions.
Patient data will help in having a more
realistic SWOT analysis.
Using the patient data we
can monitor the volumes and the practice
indices for all departments to help management
understand where the hospital is growing
or declining during the year. The analysis
will also help in understanding the impact
of various factors like changes in payer
contracts, coding and reimbursement regulations.
It can even unearth a few not so obvious
factors like practice patterns of physicians.
This includes the pattern of sending investigations
and other departmental referrals. Patients
may initially succumb to physician demands
for cutting-edge technology when less expensive
technology would do an acceptable job without
compromising quality. This might increase
the 'average collection per new patients'
but makes patient retention difficult. Clinics
need to think carefully about the mix of
supportive services they provide to patients,
their cost, and whether a less expensive
approach could achieve the same results.
Another important aspect which
needs to be considered is whether a clinic
can provide every service or whether they
should specialise in certain key services.
Modern healthcare organizations should consider
which services and programs they wish to
emphasize, for which kinds of patients,
and in which localities and then eliminate
programs and other activities that do not
fit that focus. Analysis of existing patient
data will provide key insight into this.
Strategic trade-offs should
also be evaluated in terms of competitive
scope and pricing policy both of which can
be defined from the existing patient data.
The goal of organizations with a low-price
strategy is to deliver a product of acceptable
quality at a price below that of its competitors.
By contrast, organizations with a high-price
strategy select small market segments and
offer services that are widely acknowledged
as superior. It is important to assess based
on previous data, which segment of the population
the clinic is catering to before introducing
Detailed analysis of patient
data will also provide useful information
for other managerial responsibilities like
controlling and staffing. For example it
will help in procuring the right supplies
at the right time and in the right quantities
so that there will not be any wastage or
shortage. It will also help in identifying
the bottle necks in patient flow and adequately
staff such segments.
Most of the clinics have
a variety of data waiting to be explored in
their computerised patient records. A detailed
analysis of this data with respect to the
patient type, departments, doctors and procedures
can provide key insights, which may be invaluable
in strategic planning.