Guided Notes

Analyzing Claims Data

Claim headers and lines, billed vs allowed vs paid, claim status and adjustments, de-duplicating versions, and denial and payment analysis with SQL.

Claims data structure

Slide 1 / 5
TableGrainKey fields
Claim headerOne claimclaim_id, member_id, provider, claim type (professional/institutional), dates, diagnoses, status
Claim lineOne service lineline number, CPT/HCPCS or revenue code, units, billed, allowed, paid
Member/eligibilityMember × month (or span)plan, product, enrollment dates, demographics
ProviderOne providerNPI, specialty, network status
Pharmacy claimsOne fillNDC, days' supply, quantity, paid