Thursday, September 24, 2009

Large Claim Chart #2 - Pretty Pictures

Alright, I finally got around to posting this thing...

So when we left off I was explaining the idea behind a large claim chart and why they can be useful.  Now, a history lesson.

When I first started my job, we had one table that explained large claims that I've recreated below.  Like all images on this blog, please click for a usable size.

Here we have 5 years of large claims in 5 bins.  The size of the bins changes depending on the group, but suffice to say that they are almost always worthless.  The average year for a mid-sized group will have 25+ large claims between $25,000 and $250,000 each.  You can't split that up into 5 useful bins, it's too much ground for just five bins to cover.  Looking at the large claim table as filled in above, even if I capped all of the claims at $150,000 the table could total up anywhere from $3.1m to $4.7m.  That isn't precise by anyone's standards.

Also, they aren't really bins.  If you look at the header row it has a greater than sign, which means in 2008-09 there were 6 claims total over over $15,000, all 6 of those were over $25,000, 5 of those were over $50,000, etc.   So a claim in the largest bin is also counted in every other bin.  Apparently this isn't intuitive because I get asked about it all the time and I usually have to explain it for 30 minutes before it clicks.  I'd like to scrap this chart altogether, but people fear change.  Who can blame them?

This is my first attempt to show off large claim experience visually.  Note that I didn't crop or touch up these images.  Also, for this image pretend that there are five years shown on the x-axis that correspond with the five vertical columns of dots.

You may ask yourself, "Why are the dots arranged semi-randomly in each year?  And why don't I own Spaced: The Complete Series on dvd?"  This is still the largest problem with the chart, but I can't think of a better way to do it.  Large claims occur over several months so it doesn't make sense to arrange them by month, and since we're concerned primarily with stop loss we only care what the year end total was for each plan year.  I can't arrange the claims in a vertical line, they would overlap too much.  So instead, clusters.  They've grown on me, but it still seems like that x-axis could be put to better use.  If you can think of a better way to arrange them, please leave a comment.

I should also point out that the clusters aren't random.  They used to be, but now they are just scattered over 5, 7, or 9 repeating horizontal positions in each year.  This gives me a little room to play with the data so that it doesn't overlap and hopefully looks more appealing without drawing every single stupid dot by myself.

So that chart was a good start, we can easily see at a glance that year three was very good and year four was very, very bad.  New questions came up though.  What was wrong with all of these large claimants?  Is it contagious?  Would it have been preventable with early detection?  Do we need to address it in our wellness plan*?  Also, which of these claims exceeded the stop loss deductible?

This next version of the chart addresses those questions (I mean, it would've if I had included the diagnosis for each of those labels, but I'm saving that for the final reveal.  Skip down if you're impatient).  Looking at the numbers on each claim will give you the diagnosis, but when you see the same diagnosis in several years you wonder if it's the same person.  We have an answer for that too.  Oh, and I added a thick, grey line to indicate the specific deductible in each plan year.

The chart has now transformed into what I call a "Spaghetti and Meatballs" chart, which I think deserves scare quotes (in the future, all quotes are scare quotes).  I changed the color of claimants appearing in multiple years to a peaceful green and connected them all with lines.  I don't have a picture of what the chart looked like when I kept everything orange, but before you go trying to recreate it at home please believe me that it will make you gouge out your eyes.

That chart is pretty informative, but it can be improved.  After seeing someone with a horribly expensive, scary diagnosis appearing year after year, the person who pays those bills will wonder if they are still active on the plan.

Here is the current and possibly final version of my large claim chart.  In an attempt to put Edward Tufte's Escaping Flatland chapter into practice, each point shows which year a claimant appeared in, how much their claims were that year, if they appeared in multiple years, if they are still active, and what they were diagnosed with that year.  It's also sort of a Micro/Macro design, with the graph above giving a rough overview of the large claim history with more gritty details available below for those willing to invest some time into the chart.

And what stories it can tell!  Look at the $250,000 claimant in 2007-08.  That year she was Owned by a Lich, the following year she suffered Beholder-Related Agony, and in 2009-10 she was Reamed by a Dragon.  Despite all of that, she is still active on the plan!  What a daring tale of survival!  Less uplifting is the story of the $350,000 claimant in 2005-06.  With $350,000 in claims and a diagnosis of Finger of Death, we can only assume the worst when we observe that this member is no longer active.

Unfortunately the data table is still there, but I had to leave a familiar point of entry in the chart so people wouldn't completely freak out.

Better version available here.

A few boring notes on the chart that I feel obligated to throw in:  I really like it, but I want to make it even better so feedback would be great.  However, I made this at work and they'd probably be irritated if it started showing up other places, so please don't use this commercially.  There is a Creative Commons thingy below.  Also, we take HIPAA very seriously, so all of these claim numbers included are random and all of the diagnoses are D&D inspired because I've been playing an obscene amount of Baldur's Gate 2 lately.


* Don't get me started...
Creative Commons License
Large Claim Chart by David Montgomery is licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 United States License.

No comments:

Post a Comment

Creative Commons License
David @ Work by David Montgomery is licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 United States License.