procAllowedAvgs
Query to specify procedure-encounter-based criterion and return statistical data around healthcare claim allowed amounts.
Introduction
The adjudicated allowed amount (the money that ends up coming back to healthcare providers from the insurance companies for services rendered) is the most competitively protected value in all of US healthcare.
As a result, the open availability of line-item allowed amounts are legally and categorically unavailable in the current US healthcare economy. That being said, we can utilize the statistical shape of allowed amounts coming through our data sources to get as close as allowed to a reliable adjudicated amount being paid to health systems for services rendered from payors.
procAllowedAvgs offers a well informed, deeply nuanced, estimations of allowed amounts via annual statistical aggregates on a quarterly basis that articulate the statistical shape, the distribution of allowed amounts on a regional, payor, visit type and procedural basis.
Get Started with procAllowedAvgs
If you have already setup CareQuery, run the below code to get started with your first procAllowedAvgs query.
Example:
Allowed amounts for neurology/neuromuscular procedures in New York and South Dakota for Medicare and commercial payors
# import package
from care_query.care_query import CareQuery
# instantiate and connect
cq = CareQuery(email = "your-email",
token = "your-api-token")
# construct query
query = cq.procAllowedAvgs(proc_subcategory = "medicine - neurology and neuromuscular procedures",
state = ["NY","SD"],
payor_channel = ["medicare", "commercial"])
# execute query to return results
data = query.execute()
Query Parameters
The following a parameters are available within the procAllowedAvgs query, including details on the parameter values.
| parameter | description | options |
|---|---|---|
proc_code | CPT or HCPCS procedural code performed within the encounter | values available |
proc_subcategory | Sub-category of the CPT or HCPCS procedure performed within the encounter | values available |
proc_category | Category of the CPT or HCPCS procedure performed within the encounter | values available |
visit_type | Description of the setting of care | values available |
payor_channel | Health Insurance Payor channel-of-business or line-of-business | values available |
state | US State residence of the query patient population | values available |
division | Nine distinct US State groupings as defined by the US Census Bureau | values available |
region | Four major US State groupings as defined by the US Census Bureau | values available |
limit | Number of rows returned in the data request | Integer or False |
show | Binary indicator as to whether you'd like query to be printed for review upon it's creation | True or False |
Query Return Data
The procAllowedAvgs query returns the ALLOWED_AVGS_TABLE for all encounters that meet the criterion specified within the query.
The ALLOWED_AVGS_TABLE offers comprehensive statistics around the actual allowed amount values seen within submitted healthcare claims nationwide.
| feature | description | example |
|---|---|---|
| proc_code | CPT/HCPCS procedural code - used as a grouper for all corresponding statistics. | 27625 |
| proc_subcategory | Sub-categorical description of the procedure code - used as a grouper for all corresponding statistics. | 'Surgery - Musculoskeletal System' |
| state | US State where the procedure was performed - used as a grouper for all corresponding statistics. | 'KY' |
| payor_channel | Payor channel-of-business, line-of-business, or plan type - used as a grouper for all corresponding statistics. | 'Managed Medicaid' |
| visit_type | Description of the setting of care - used as a grouper for all corresponding statistics. | 'Outpatient Visit' |
| total_encounters | Total number of encounters corresponding to the groupers within the period and utilized in the allowed amount statistics. | 28 |
| trimmed_mean_allowed_amount | The mean allowed amount with outliers trimmed from consideration. | $ 366.46 |
| mean_allowed_amount | The mean allowed amount with all corresponding encounters considered. | $ 1,128.77 |
| min_allowed_amount | The minimum encounter allowed amount seen within the groupers/period. | $ 156.15 |
| percentile_10 | The 10th percentile of all allowed amount values seen within the corresponding groupers/period. | $ 247.86 |
| percentile_20 | The 20th percentile of all allowed amount values seen within the corresponding groupers/period. | $ 452.79 |
| percentile_50 | The 50th percentile of all allowed amount values seen within the corresponding groupers/period. | $ 632.16 |
| percentile_80 | The 80th percentile of all allowed amount values seen within the corresponding groupers/period. | $ 2,213.00 |
| percentile_90 | The 90th percentile of all allowed amount values seen within the corresponding groupers/period. | $ 2,788.31 |
| max_allowed_amount | The maximum encounter allowed amount seen within the groupers/period. | $ 3,111.53 |
| lookback_period | All values are computed within an year-long lookback period on a quarterly basis. This value represents the most recent quarter-year in which a lookback has occurred. | 'Q3-2023' |
| current | Binary indicator as to whether the listed period is the most current. | True |
Advanced Query Logic & Recipes
As one thinks about what the healthcare encounter's they'd like returned in a query...the diagnosis, procedure, location, time and specialty criterion...things can get quite complex.
procAllowedAvgs gives users categorical groupings for ease of use, as well as SQL-like capabilities to tactfully specify AND and OR logic, right within the CareQuery API!
Please reference the following resources to better understand how to create rockstar queries!
Updated about 1 year ago