Answers to all your Obamacare questions are in the data, if you know where and how to look

As we pass the two-year mark on the rollout of the Affordable Care Act, journalists are still asking a lot of questions about just how well health reform is expanding coverage.

These include familiar questions like, How much are consumers paying for plans on the exchanges? Are most people re-enrolling in Obamacare plans or finding coverage elsewhere? How do premiums for different tier plans vary based on geography? How well are last-ditch efforts at enrolling hard-to-reach groups in Obamacare plans working as open enrollment comes to a close?

AP journalist Meghan Hoyer, formerly of USA Today’s Database Team, has an answer to all those questions. Or at least she knows how and where to find them: in the data! At this week’s 2015 California Data Fellowship in Los Angeles, Hoyer opened up new government spreadsheets and led a small band of journalists through the intricacies of “pivot tables” and data manipulation.

These types of questions are particularly timely right now, as the third open enrollment season winds down (Dec. 15 is the last day to enroll or change plans for coverage beginning Jan. 1).

“This is a big breaking story now because this is the end of the enrollment period,” Hoyer said. “Maybe you want to go back to your newsrooms next week and say, ‘Hey, we should check out what the numbers look like and what the last-ditch enrollment efforts are to get people enrolled.’”

In California, premium increases for Obamacare exchange plans in 2016 are a relatively modest 4 percent on average, but big price differences persist between north and south, Hoyer noted. An average "silver" plan will cost $384 a month in Northern California, compared with $296 in Southern California (the difference can be explained by more hospital competition in SoCal).

Journalists can seek out far more detailed information on their coverage area — and find more story ideas —  by turning directly to the raw data. 

Hoyer gave California journalists a helping hand by prompting Covered California to post online data for the current year. (Hoyer called Covered California last week to ask where its health care data was for this year, and the agency then emailed it to her. “Then they put it up online afterwards, which is like a classic data trick,” she said.) You can find and download that valuable Excel spreadsheet, called “2016 Product Prices for all Health Insurance Companies,” by clicking here.

In addition, this 150-page annual overview put out by Covered California is essential for California reporters, Hoyer said. “This document should be your ACA bible. It basically goes to each region and says, Here’s how much the average plans went up, here’s who’s affected, here’s the breakdown of who controls the ACA market in that region.”

But getting ahold of the relevant data is just the first step. Next comes cleaning and manipulating the data in Excel. A blog post can’t approximate Hoyer’s ace Excel tutorial, but if you don’t use them already, make a note to get friendly with “pivot tables.” As Hoyer explained, Excel’s pivot tables feature allows you to group clusters of similar data together, and then do some basic mathematical calculations based on those data groupings. (For another useful primer on how to use Excel for journalists, see our archived webinar with The Wall Street Journal’s Paul Overberg, who is helping lead this week’s California Data Fellowship.)

If you already have the courage to start “interviewing” the data in greater depth, Hoyer offered a number of story ideas worth customizing to your coverage area:

• What kind of price changes are consumers seeing as they shop for health plans in your region? Are they encountering steep or negligible premium increases?

• Who is enrolling outside the regular open enrollment period? And why are they doing so? As Hoyer notes, such enrollees tend to result in higher health care costs for insurers.

• Does your coverage area include higher-than-average numbers of people who aren’t getting subsidies? What’s motivating them?

• Consider the demographics of those who still haven’t enrolled in coverage yet. “By the end of this year, pretty much the people who are left are kind of troubled populations,” Hoyer said. “They’re really isolated at this point. You want to get in there and find out what’s going on that these people aren’t getting the message or feeling comfortable enough to sign up.”

• Who are the biggest players in your region? “One of the most interesting things to me is, Who’s dominating this market?” Hoyer said. “California is giving much better data than the national data on how many people are enrolled with Anthem versus enrolled with Kaiser, etc. That’s a gold mine. Who are these companies who are dominating this market? Are the smaller companies getting enough to make it?”