Evaluating Data Reliability and Identifying Reporting Errors
A revenue cycle report may calculate perfectly and still be wrong. The source data might contain an incomplete payment batch, an incorrect account association, or duplicate amounts introduced when files were combined. A dashboard can hide those problems behind clean totals and polished charts.
Data quality and reporting accuracy are related, but they aren't interchangeable. Data quality addresses whether information is fit for its intended purpose. Reporting accuracy asks whether a particular calculation or presentation faithfully represents the records, definitions, and time period it claims to measure.
Before using a report to make a decision, its population, timing, joins, exclusions, and totals need to stand up to a second look. The examples connect apparently ordinary spreadsheet discrepancies with the records and reporting rules that created them.
Why Correct Arithmetic Can Produce a Wrong Report
Every revenue cycle activity generates data: registration creates identifiers, charge capture creates service transactions, billing creates claims, payers return adjudication records, and posting creates accounting entries. Reports combine those records to answer questions about work completed, money owed, money received, and financial performance.
AHIMA identifies characteristics such as accuracy, completeness, consistency, currency, definition, granularity, and timeliness in healthcare data quality. Those characteristics are useful because a dataset can satisfy one and fail another. A payment amount may be accurate but assigned to the wrong patient; a claim may have correct values but be missing from the reporting period. [1]
Not every repeated row is bad data. Multiple service lines can share one claim number; a reversal and repayment can share a service identifier; and an ERA may contain multiple adjustments for the same line. The first task is to establish what each row represents.
Who Owns Data Quality and Where Problems Begin
Data quality is shared across the workflow, but responsibilities are not interchangeable. A report analyst can identify and document a discrepancy; an authorized registration, coding, posting, systems, finance, or compliance team may be required to correct the underlying record.
Upstream errors can travel into multiple reports. A flawed analysis can also create a problem even when the upstream transactions are correct. The investigation should establish where the data first diverged, not assume the person using the final report introduced the error.
Define the Question Before Opening the Dataset
An accurate answer requires a precise reporting definition. “How many claims were denied last month?” is incomplete until the report specifies whether it counts distinct claims or service lines, initial denials or all denial events, adjudication date or posting date, and which payers and claim types are in scope.
- Business question: the decision the report is meant to support.
- Population: the claims, accounts, patients, batches, or transactions that belong in the report.
- Grain: one row per claim, service line, adjustment, payment, deposit, or event.
- Time basis: service, submission, adjudication, posting, deposit, or snapshot date.
- Metric definition: the numerator, denominator, inclusions, exclusions, and calculation rules.
- Reconciliation target: the trusted totals or source records that will independently check the result.
HFMA MAP Keys provide consistent revenue cycle KPI definitions, including inclusions, exclusions, and suggested data sources. If a report claims to use a MAP Key, its calculation should follow the applicable published definition rather than an informal local approximation. Locally defined measures are useful too, but should be labeled as such. [2]
A grain mismatch that changes the answer
Suppose a claim header contains one $300 payment and its detail table contains three service lines. Joining them on claim ID creates three rows with the same $300 claim-level amount. Summing that joined field produces $900 even though only $300 was received.
The correction is not to delete two legitimate service lines. Keep the claim-level payment at claim grain, allocate it only when a supported allocation method exists, or summarize the service lines before joining. Microsoft’s data-modeling guidance likewise emphasizes clear fact-table grain and carefully designed relationships. [3]
Preparing and Profiling Source Data
Before calculating anything, preserve the original export and record its source system, report name, parameters, extraction time, timezone if relevant, reporting cutoff, row count, and monetary control totals. Work on a separate copy or reproducible query. This provides an audit trail if the final report differs from the source.
Practical source checks
- Confirm that all expected files, batches, and reporting periods are present.
- Inspect data types. Identifiers such as member numbers and ZIP codes may need to remain text to preserve leading zeros.
- Check required fields, blank identifiers, invalid dates, unexpected signs, and invalid code values.
- Count rows and distinct keys at the appropriate grain; investigate duplicates without automatically deleting them.
- Compare record counts and amount totals by source, payer, date, and transaction status.
- Review late postings, reversals, replacements, and files arriving after the reporting cutoff.
For remittance work, preserve signs and distinguish claim payments from PLBs and actual EFTs. A positive service payment and a negative recovery can coexist in one reporting period; changing signs merely to force a favorable total destroys the information needed to reconcile.
Reconcile Financial Data Before Trusting a Report
A financial report should be traceable to an independent control. That may be a bank deposit, a posting batch report, a remittance control total, an AR trial balance, or a finance ledger. Use the control appropriate to the question; an EFT total should not be treated as the same measurement as gross claim payments.
Worked example: claim payments, PLB, and EFT
An ERA shows $1,250 in claim payments and a provider-level recovery that reduces the net payment by $150. The associated EFT is $1,100. The reconciliation is: $1,250 − $150 = $1,100. A report that shows only claim payments isn't necessarily incorrect, but it cannot be labeled “cash received” without accounting for the PLB.
The payment-posting report must also be checked at the correct account and transaction levels. A balanced deposit confirms the amount of cash accounted for, not that every dollar was associated with the correct patient or claim.
Worked example: offsetting account errors
A posting batch should credit Account A with $240 and Account B with $160. Instead, it credits A with $200 and B with $200. Both the expected and posted totals equal $400, but A is short $40 and B is overstated by $40. Account-level comparisons reveal what the batch-level check cannot.
The proper resolution requires researching source remittance details, validating the account associations, and following authorized payment correction procedures. Net-zero differences are not permission to skip transaction-level testing.
Joins, Lookups, and Data Transformations
Combining files introduces its own risks. A lookup returning the first match may be valid for a unique account master but unreliable for service lines, payments, or adjustment transactions that legitimately repeat an identifier. Similarly, an inner join can silently remove unmatched records, while a many-to-many join can multiply values.
When a model legitimately requires many-to-many relationships, document how filtering and aggregation behave. Microsoft recommends dimension and bridge-table approaches for many common cases rather than directly connecting two fact tables with an ambiguous many-to-many relationship. [4]
A practical Excel exception check
If one worksheet contains the authoritative remittance line amounts and another contains posting entries, use stable transaction identifiers and compare totals at the same grain. A simple validation might calculate =SUMIFS(PostedAmount,TransactionID,A2)-SUMIFS(ExpectedAmount,TransactionID,A2) in a structured extract. However, the meaning of that result depends on whether TransactionID truly identifies the same unit in both files.
Count unmatched keys in both directions, not just in the system receiving the merge. A record missing from the source-to-posted match may represent an unposted payment; a posted record missing from the source may represent a reversal, manual entry, wrong period, or incorrect mapping.
A Repeatable Data Quality Review Workflow
- Define: Record the business purpose, report owner, population, period, cutoff, and intended decision.
- Preserve: Retain original source extracts and metadata without overwriting transaction history.
- Profile: Validate row counts, data types, completeness, signs, keys, and status values.
- Reconcile: Compare independent financial totals and detailed records at the same grain.
- Isolate: Create an exception population with original IDs, evidence, and calculated differences.
- Trace: Locate the first system, rule, mapping, or transaction where the discrepancy arose.
- Assign: Route underlying corrections to the team with authority; distinguish report fixes from source corrections.
- Retest: Refresh or rebuild the report and verify counts, amounts, and corrected records.
- Monitor: Track repeat exceptions and control performance over subsequent reporting cycles.
Documentation should explain which result changed, why it changed, what source supported the correction, who approved or performed it, and whether previously published reports require restatement. Report repair and transaction correction are related activities but require separate validation.
Common Reporting Problems and How to Investigate Them
Measuring the Quality of the Data Itself
Revenue cycle teams may measure incomplete records, unmatched transactions, reporting exceptions, or correction cycle time. These are local data-quality indicators unless tied to a specifically published standard. Their definitions should be documented just as carefully as financial KPIs.
Example: an exception rate improves
A report begins with 75 unmatched records out of 1,500 eligible transactions, or 5%. After a mapping correction, 12 of 1,600 eligible transactions are unmatched, or 0.75%. That is a 4.25-percentage-point reduction. Before crediting the control, confirm that both periods used the same matching definition, required transactions, and status exclusions.
Improvement in a reported rate is not evidence by itself that no errors remain. Use a targeted sample of previously affected transactions, check for new failure types, and confirm that no unmatched records were merely hidden by a changed filter.
Privacy, Integrity, and Auditability
Financial datasets may include protected health information, personal identifiers, account information, and confidential payer contracts. Analyses should use authorized access, appropriate storage, minimum necessary handling where applicable, and organizational retention policies. Don't upload live PHI to unapproved external analysis tools or casually distribute exported data by email.
AHIMA emphasizes data accuracy, correct patient association, appropriate detail, and accountability as part of health data quality and integrity. For operational reporting, those principles translate into controlled source access, reproducible transformations, clear lineage, and corrections that preserve the original audit trail. [5]
A suspicious pattern does not by itself prove fraud, a privacy breach, or a broken automation. Escalation should reflect the evidence and follow the relevant data governance, security, finance, compliance, or operational process.
Practical Reporting Quality Checklist
- ☐ Is the business question and reporting population clearly defined?
- ☐ Are the grain, date basis, exclusions, and metric calculation documented?
- ☐ Are original source files preserved with timestamps and row counts?
- ☐ Are record IDs, statuses, payment signs, and missing fields validated?
- ☐ Have joins and lookups been tested for duplicate multiplication and omitted records?
- ☐ Do financial totals reconcile to the correct independent control?
- ☐ Have account-level discrepancies been tested even when aggregate totals balance?
- ☐ Have exceptions been traced to evidence rather than attributed by assumption?
- ☐ Are source corrections performed or approved by the proper owners?
- ☐ Has the report been rerun and checked after corrections?
- ☐ Can another reviewer reproduce the result and understand its limitations?
A trustworthy report is one that can be explained and reproduced, not merely one that produces the expected total. When analysts preserve source history and document their logic, unusual results become questions that can be investigated rather than numbers that must be made to fit.
References and Further Learning
The following sources support the data-quality definitions, financial reporting standards, and data-modeling principles discussed here. Product guidance and metric specifications should be checked for the version and reporting context in use.
References
Additional Learning
Educational Disclaimer
Revenue Cycle Uncovered is an independent educational resource. Content is provided for general informational purposes and does not represent the policies, practices, or positions of any employer or organization. Workflows, payer requirements, regulations, and operational practices may vary. Information should be independently verified when applied to specific situations.