Data analysis doesn’t have to be difficult.  When you have a massive spreadsheet of medical claims and are trying to efficiently make sense of the content, using pivot tables can be a game changer. 

If you don’t already love pivot tables, you may be shocked to find out just how easy they are to use.  When navigating a large data set as part of a medical review, pivot tables take the painstaking process of sorting, summarizing, and processing data and make it easy to consume information with speed and ease.

Integrity Advantage wants to help you! Check out our latest article for our 6 go-to pivot tables!

Want to Get to Know Your Data?

Our 6 ‘go to’ pivots to get you started!

How Can I Efficiently Navigate a Large Dataset?

Even if you have the best technology out there, investigators will continue to use Excel to analyze data. Let’s say you received complaint that is unclear or an exposure request from law enforcement… essentially, any lead without a defined allegation. You have a spreadsheet of the providers’ data. As you scroll through tens of thousands of rows, you think ‘there must be a better way!’

And there is: PIVOT TABLES! If you immediately think, ‘oh no, they’re too complicated’ you’re wrong! To get started familiarizing ourselves with a dataset, we tend to set up the same handful of pivot tables each time. Start simple and use these tips and visuals to help.

Getting Started

If you don’t already love pivot tables, it might be because you just don’t know how easy and effective they can be to use. 😊 Time and time again we hear that folks just didn’t have time to learn them, but once they do, they wish they would have sooner! Think about pivot tables as a way to ask questions of your data. When you are looking at thousands of rows of data it can be overwhelming. Pivot tables summarize your data in an easy to consume and understand format.

In data analysis, like most investigative techniques, each step is often driven by the findings of the prior action. It’s easy to get carried away pulling in a bunch of fields, resulting in a hard-to-understand pivot table! By focusing on this handful of initial pivots, you can first get to know the data, then expand from there. This approach keeps it simple!

Of note: We use paid amount (or whatever you call the field indicating what the claim line paid) for this initial analysis. It’s important to consider allowed amount, claim counts and member counts, but when we boil it all down, if you don’t have paid dollars the rest diminishes.

The Basics

Before we jump into the nitty gritty, let’s take a moment to define some basics that are critical when using pivot tables.

Before we jump into the nitty gritty, let’s take a moment to define some basics that are critical when using pivot tables.

• Field List: The menu that appears on your screen which allows you to navigate which ways you’d like to sort your data (see image at the right)

  
  

• Filters: This area is used for applying filters in the pivot table to focus the data on specific criteria

  
  

• Columns: This area is used for organizing the data along the horizontal axis (we use this quadrant the least)

  
  

• Rows: This area is used for grouping and organizing the data along the vertical axis. The first field dropped here will be a unique listing.

  
  

• Values: This area is used for performing calculations on the data, such as sum, count, average, etc.

We like these 6 pivot tables to get you started...

1.Provider ID (Plan ID, NPI or TIN) by paid amount:

To get a handle on your potential exposure, pivot your data by one of the provider identifiers (drop the ID into the Rows quadrant) and summarize by the paid amount (drop the paid line field into the Values quadrant). This simple pivot will give you a unique list of provider IDs/NPIs/TINs - depending on which you choose - and the total paid dollars associated with each. I would highly recommend looking at the data by all three identifiers. Depending on your data set, you may have a single TIN and 3 associated NPIs, for example. You would be able to see this if you pulled TIN into the rows first, then below it in the rows pull in NPI. You can sort the paid column descending order if you want to observe your provider(s) in order of who was paid the most. Knowing these totals gives you helpful information on the impact the provider or billing entity has on the health plan, help you decide if there is enough exposure to warrant further review and inform you of the subject(s) of the investigation. From here you can pull in more aggregated fields (drop into the Values quadrant) like a member or claim count. This initial review may have you rethink your subject and where you might want to go with your potential investigation.

2.Top Procedure Codes by Paid Amount/Claim Count:

Running this pivot will give you a glance at what types of services the provider is rendering. To create it, pull your procedure codes into the Rows area of the field list and your aggregation of choice into the Values, such as paid amount or claim count. Be aware of how Excel calculates, you don’t want a paid count or claim sum. Your calculation can be changed with a right click ‘Summarize Values By’ selection. This pivot table will inform you if there is a nice spread of services or just a few codes billed, as well understand if the services are in line with the provider specialty. If there is a pattern, it may stick out very quickly. For example - are there a lot of evaluation and management codes? Do all of them happen to be level 5s?

3.Top Diagnosis Codes by Paid Amount/Claim Count:

Much like the procedure code pivot in number 2, this pivot provides an at-a-glance of the providers diagnosis codes. Pull the diagnosis code and/or description into the Rows and field of choice into the Values. Then you can ask yourself: are the diagnoses appropriate with what you just looked at for procedures? Do the diagnoses make sense for the provider specialty? Is there a good spread or does everyone have the same diagnosis?

4.Top Members by Paid Amount:

Continuing on with our pattern recognition, looking at top members by spend could identify outlier billing behavior. Bringing member ID or member name into Rows and paid amount into Values will enable you to quickly identify outliers. Ask yourself: is all the spend on a few members? Is there an out-of-the-ordinary number of unique members for the provider? Are there many members in the same family?

5.Top Modifiers by Paid Amount/Claim Count:

Looking at your dataset by modifier will identify whether or not there a significant number of modifiers paying. Following our pattern, bring modifier into Rows and either paid amount or claim ID into Values. For the modifiers observed, do they make sense with what you’ve learned about your data so far? Are there a lot of modifiers that affect payment being used? Is the large majority of payment or claims have a 59 or 25 modifier?

6.Member / Date of Service Review:

This one has bit more fields than the others, but it gives a more detailed perspective of what is going on in the data. The gist of the review is to see what is being billed per member on each date of service. Into Rows, pull first the member, then date of service, THEN claim ID (are they claim splitting?), diagnosis code, procedure code, modifier, units, and then in the Values add paid amount and/or allowed amount. Look for answers to questions such as: are multiple providers seeing the member? Are there weird pairings of diagnosis codes with procedure codes? Excessive units per encounter?

In addition to these tables, we will often use a quick pivot to find the date of service and paid date range, total paid amount, member count, claim count and any other count you can think of!

Conclusion

Pivot tables will make it easier for you to quickly navigate and assess the data in your spreadsheet. By using the six pivots we recommend, you can develop a workflow process that maximizes efficiency and reduces painstaking and time-consuming methods of searching through your data. With these basics mastered there are so many ways to slice and dice the data, you can leave no stone unturned!

Remember, if you’re new to pivot tables, start small with the data elements you pull in. Once you understand what the pivot table is doing, you can expand from there. With a little practice, you’ll quickly see the value and importance of this critical tool in the work we do in fraud, waste and abuse. And there’s a chance you’ll really start to love using them!

If you need help, remember Integrity Advantage is here for you!

Not only are we available for customized training and mentoring for FWA teams in need of support, but we have also developed a complete pivot table training for medical review (with step-by-step processes, images, and instructions) as part of our Medical Review Pro Toolkit.

https://www.integrityadvantage.com/toolkits

Do you need help with Excel for investigations? We offer customized training for investigators and medical review!  

With more than 30 years of experience supporting payers, Integrity Advantage provides healthcare fraud, waste and abuse consulting, outsourced investigations and medical record reviews for Special Investigations Units and other organizations fighting healthcare fraud.

We are a certified Women’s Business Enterprise (WBE) and an Economically Disadvantaged Woman Owned Small Business (EDWOSB).

For more information click below, call us at 866-644-7799 or email info@integrityadvantage.com.