Monday 18 January 2016

Solving Data Puzzles with Jodi Bloom
Using Human Eyes To Find Data Trends

Posted by at 5:20 PM

Solving Data Puzzles with Jodi Bloom

I like to solve puzzles:  Sudoku, Jigsaw, Ken Ken, and even Algebra.

I approach data mining as if I am solving a puzzle – after all, data mining is just digging for data.  Sometimes, it seems I start with no picture or corners.  I sketch out a simple data table of column headers with some data in rows and maybe a grouping or two with totals. I go hunting in whatever database I’m working in for the fields I need to add to the report.  I then begin to formulate a basic starting point.

My father was a statistician and professor.  He taught research design and statistics in graduate schools. Yeah, heady stuff.  He explained to me that the human eye can interpret a column of data very quickly and with surprising accuracy when compared to some fancy statistical measurements.

Using his philosophy, once I have my columns and populate a report with some sample data, I begin to interpret with my eyes.  I start with high volume and high values such as percentages and outliers - - numbers that stick out.  Give it a try. You want to get a column and sort it first high to low on values, then calculate some %.  Scan the columns to look for logical grouping.  Group by a few different values until you see a trend that answers whatever question started you down this rabbit trail.

For example, let’s say you want to find out what procedures are paid the least in comparison to all procedures.  You will probably find a top 10 or 20.

  • Start by building a report with very basic information: Patient Account, Admit Date, Date of Service, Total Charges, Expected Payment, Actual Insurance Payment, Actual Patient Payment, Actual Write-off, ICD 9/10 Procedures #1, #2, #3, Surgeon, Attending provider, Patient Type and Department. Those columns will fit onto a landscape page so your eye can begin the interpretation.

Patient Account  Admit Date  Date of Service  Total Charges  Expected Payment  Actual Insurance Payment  Actual Patient Payment  Actual Write-off  ICD 9/10 Proc #1  ICD 9/10 Proc #2  ICD 9/10 Proc #3  Surgeon  Attending Provider  Patient Type Dept

Run the report for 1 to 3 months and sort by Total Payment and Total Charges. See a trend? Here are examples payment variance reports by detail and graphical summary.

  • Next, use a report writing tool like Crystal Reports and add some groupings like the Primary Procedure then to calculate % of total by volume and by charges. How about % by payment? Or subtract expected from actual payment and trend by the variance. Hide the detail rows and look at the trends.See anything?

  • Add a grouping by provider. Look at the same % with and without breakdown by procedure.Are providers similarly profitable? You might need costing info, but charges and payments will give you a good place to start.The question by provider is how they compare to each other; who has the highest volume and either highest revenue or lowest cost.

  • Try swapping out provider for insurance plan or contract. This is where it might get a little weird. You aren’t really looking for actual payments but to see the expected payment % by payor, if you have expected payment calculating in your system. What can you do with that one little field? A LOT! The question with payor data is first are they paying correctly?What payor has the highest volume? Other factors will include denial and underpayment rate, which affect cost to collect and AR days, and you might find more fields to trend or add to this dataset in another report.

By now, we have four corners to the puzzle and are beginning to get a picture.  We might see several reports emerging.  Look for graphs or charts that will help you to illustrate the trends.

And the human eye is ready to interpret!

What is your question on Data mining or Crystal reports?  Ask me by sending an email to or on the Contact page on the website. 

When you have a need, think of BRC.

Follow us on Linked In, Twitter, Facebook.