Healthcare pricing transparency has taken a major step forward with the Transparency in Coverage (TiC) rule, which requires health insurers to publish their negotiated rates in machine-readable files (MRFs). However, analysts working with this data have encountered an interesting phenomenon known as "zombie rates" - rates that appear in the data but shouldn't logically exist. Let's explore what these are and how to analyze them using spreadsheet tools.
Understanding Transparency in Coverage MRFs
The Transparency in Coverage rule, which went into effect in July 2022, requires health plans and issuers to publish machine-readable files containing their in-network negotiated rates, out-of-network allowed amounts, and prescription drug pricing information. These files follow a standardized JSON schema and are updated monthly, creating a unprecedented window into healthcare pricing.
The in-network files typically contain:
Negotiated rates between providers and payers
Billing codes (CPT, HCPCS, etc.)
Provider information (TIN, NPI)
Plan information
Various pricing arrangements (fee schedules, percentage of charges, etc.)
What Are Zombie Rates? 🧟
In the context of healthcare pricing transparency, "zombie rates" refer to negotiated rates that appear in the data but shouldn't exist based on other information we have about the provider-payer relationship. Common examples include:
Rates appearing for terminated provider contracts
Rates showing up for services a provider isn't licensed to perform
Duplicate rates with different effective dates
Rates appearing under multiple different payment arrangements
These zombie rates can significantly impact analysis and decision-making if not properly identified and handled.
Technical Approaches for Processing MRF Data
The massive size of MRF files presents a significant challenge for analysis. A single payer's monthly in-network file can easily exceed several gigabytes. Let's explore different approaches for getting this data into a workable format.
Excel-Based Approaches
Microsoft Excel offers several ways to work with JSON data, though you'll need to work with extracted subsets of the full MRF files due to size limitations:
Power Query Power Query can directly import JSON data and automatically flatten nested structures. Here's how to approach it:
Use Get Data > From File > From JSON
Select the nested tables you want to expand
Use the expand column button (two arrows) to flatten nested arrays
Filter for specific providers or services before loading to manage data size
Excel's Built-in JSON Support Newer versions of Excel support JSON imports through formulas:
=JAONFORMAT(A1, "$.in_network[*].negotiated_rates[*].negotiated_prices[*].negotiated_rate")This works well for smaller extracts and specific lookups.
Python Solutions
Python offers robust tools for handling large JSON files efficiently:
import pandas as pd import json import ijson # For streaming large JSON files # For smaller files that fit in memory def process_mrf_simple(file_path): with open(file_path, 'r') as f: data = json.load(f) # Extract and flatten negotiated rates rates = [] for item in data['in_network']: for rate in item['negotiated_rates']: for price in rate['negotiated_prices']: rates.append({ 'billing_code': item['billing_code'], 'billing_code_type': item['billing_code_type'], 'negotiated_rate': price['negotiated_rate'], 'service_code': item.get('service_code', ''), 'provider_references': rate['provider_references'] }) return pd.DataFrame(rates) # For large files using streaming def process_mrf_streaming(file_path): rates = [] with open(file_path, 'rb') as f: parser = ijson.parse(f) current_item = {} for prefix, event, value in parser: # Build your data structure incrementally # Add logic to handle nested structures pass return pd.DataFrame(rates)Gigasheet: Purpose-Built for MRF Analysis
Gigasheet offers a particularly elegant solution for working with MRF data. Their platform automatically handles many of the common challenges:
Automatic Flattening Gigasheet automatically flattens the nested JSON structure of MRF files into a tabular format, eliminating the need for complex JSON parsing or data transformation code. This makes the data immediately analyzable using familiar spreadsheet operations.
Healthcare-Specific Features Their price transparency offering includes built-in support for healthcare data standards:
Automatic taxonomy code lookups to validate provider specialties
Built-in reference data for procedure codes
Smart detection of common data quality issues including zombie rates
Support for comparing rates across multiple payers
Scale Without Compromise Perhaps most importantly, Gigasheet can handle the true scale of MRF data:
Process billions of rows without sampling
Perform interactive analysis on complete datasets
Generate aggregations and statistics across full payer files
Export manageable subsets for detailed analysis
This combination of healthcare-specific features and big data capabilities makes Gigasheet particularly well-suited for comprehensive MRF analysis.
Choosing the Right Approach
Your choice of tools should depend on your specific needs.
For quick analysis of specific providers or services:
Excel with Power Query works well for targeted extracts
Focus on specific billing codes or provider groups to manage size
For programmatic analysis:
Python offers flexibility and can handle larger datasets
Consider using streaming approaches for full file processing
Build validation rules into your processing pipeline
For enterprise-scale analysis:
Gigasheet provides a complete solution with healthcare-specific features
Particularly valuable when working with multiple payer files
Eliminates need for complex data engineering
Using Spreadsheets to Detect Zombie Rates
While the full MRF files are typically too large for spreadsheet analysis, you can work with targeted extracts for specific providers or services. Here's how to approach the analysis:
Step 1: Data Extraction and Organization
First, create a structured dataset with these key fields:
Provider | NPI | Service Code | Negotiated Rate | Rate Type | Effective Date | Expiration DateFor initial analysis, focus on a specific market or provider group to keep the data manageable.
Step 2: Basic Zombie Detection
Create validation checks to identify common zombie rate patterns:
=IF(AND(TODAY()>Expiration_Date, NOT(ISBLANK(Negotiated_Rate))), "Potential Zombie Rate", "Valid Rate")Step 3: Cross-Reference Analysis
Compare your rates against known provider specialties and capabilities. Create a lookup table of valid service codes by provider type:
=VLOOKUP(Service_Code, Valid_Services_Table, 2, FALSE)Step 4: Time-Based Analysis
Examine rate patterns over time to identify temporal anomalies:
Create a timeline of all rates for each provider-service combination
Look for overlapping effective dates
Compare against known contract periods
Common Zombie Rate Patterns
Through analysis of TiC MRF data, several patterns have emerged:
The Contract Zombie
Rates that continue to appear after known contract termination dates. These often occur due to delayed updates in payer systems or incomplete provider termination processing.
The Specialty Mismatch
Rates appearing for services outside a provider's specialty or scope of practice. For example, surgical rates appearing for providers who only offer consultative services.
The Duplicate Zombie
Multiple active rates for the same service with different payment arrangements. This often happens when new contracts are loaded without properly ending old ones.
Spreadsheet Analysis Best Practices
When working with MRF extracts:
Document your data extraction criteria and any filtering applied
Maintain reference tables for valid provider-service combinations
Create automated checks for rate reasonableness
Track historical changes in rates over time
Document all assumptions about contract dates and provider capabilities
Practical Tips for Analysis
Consider these approaches when analyzing MRF data:
Rate Reasonableness Checks
Create bounds for reasonable rates based on:
Medicare fee schedule percentages
Historical contracted rates
Geographic averages
Provider Validation
Cross-reference against:
Provider directories
State license databases
Medicare specialty codes
Contract Timeline Analysis
Track rate changes over time to identify:
Unexpected rate persistence
Missing contract updates
Overlapping rate periods
Taking Action on Zombie Rates
Once you've identified zombie rates in your MRF analysis:
Document all instances with supporting evidence
Create a systematic classification of zombie rate types
Develop regular monitoring processes
Establish feedback loops with data suppliers
Consider implications for network adequacy and provider directories
Conclusion
Zombie rates in healthcare pricing transparency data present both a challenge and an opportunity. While they can complicate analysis, they also help identify areas where data quality can be improved. By using systematic spreadsheet analysis techniques, analysts can identify these anomalies and work toward more accurate healthcare pricing data.
As the healthcare industry continues to adapt to transparency requirements, the ability to identify and address zombie rates will become increasingly important. Regular analysis of these patterns can help improve data quality and support better decision-making in healthcare pricing and contracting.
In future posts, we'll explore more advanced techniques for analyzing TiC MRF data, including methods for handling larger datasets and automated anomaly detection approaches.
Commentaires