careEpisode
Query to specify episode-based query parameters to return line-item level patient journey data for episodes of care.
We define an episode as sequential group of encounters that occur within the 30 days from one another, in continuation, until greater than 30 days has elapsed.
Get Started with careEpisode
If you have already setup CareQuery, run the below code to get started with your first careEpisode query.
Quick Example:
Heart failure episodes of care where with cardio surgery for patients from New England in the winter of 2022/2023.
# 1) import
from care_query.care_query import CareQuery
# 2) instantiate and connect
cq = CareQuery(email = "your-email",
token = "your-api-token",
sftp_key = "path/to/your/company.PEM")
# 3) build query
query_name = cq.careEpisode(proc_subcategory = "surgery - cardiovascular system",
diag_subcategory = "heart failure"
division = "new england"
min_date = "2022-11-01",
max_date = "2023-02-01",
alias = "chf-new-england_winter23", # optional argument to add sftp_path prefix
limit = 10000) # False returns all rows
# 4) submit query, data will return to SFTP endpoint
# could also run .sample() to return the first 1k rows or .execute() to get a size estimate
result_object = query_name.execute()
# 5) check query status
# user will be sent an email upon submission and completion, check query status along the way
# token and query_id can be left empty if within the same session as the query submission
# if you want to get the status of a query outside of the session that submitted the query, submit your user token, and the query_id that you can find in your email.
query_name.queryStatus(token = token, query_id = query_id)
# 6) upon successful completion of query, read data directly into pandas environment
result_data = query_name.sftpToPandas()
Query Parameters
The following a parameters are available within the careEpisode query, including details on the parameter values
| parameter | description | options |
|---|---|---|
alias | String value for users to give a descriptive name to the data object written to each customer's CareQuery SFTP location. | String |
min_date | Lower threshold date within the date range of interest, observational start date | Date String |
max_date | Upper threshold date within the date range of interest, observational end date | Date String |
gender | Patient gender within the population of interest | M,F or ['M','F'] |
age_min | Minimum patient age identified within the population of interest | 0 to 120 |
age_max | Maximum patient age identified within the population of interest | 0 to 120 |
visit_type | Description of the setting of care | values available |
payor_channel | Health Insurance Payor channel-of-business or line-of-business | values available |
revenue_center_code | 4-digit code used to identify a specific accommodation, ancillary service, or billing calculation on a hospital bill | values available |
place_of_service | Setting in which a healthcare service was provided by healthcare providers, insurers, and government agencies to track and bill for healthcare services | values available |
diag_code | ICD-10 diagnosis code given to the patient within the encounter | values available |
diag_category | Category of the ICD-10 diagnosis code given to the patient within the encounter | values available |
diag_subcategory | Sub-category of the ICD-10 diagnosis code given to patient within the encounter | values available |
short_diag_code | First three digits of the ICD-10 diagnosis code given to the patient within the encounter | values available |
diag_num | Total number of diagnoses code columns returned to the user in addition to the diagnoses list. Defaults to top 5 diagnosis codes. | Any number from 0 to 25 |
diag_greedy | Boolean indicator for 'AND' or 'OR' when multiple diagnosis parameters specified - helps users offer various levels of specificity without confusing and/or logic | True or False |
drg_code | Diagnosis Related Group code(s) categorizes case-mix complexity and diagnoses to calculate hospital costs and reimbursement rates | values available |
drg_category | Diagnosis Related Group category or categories in order to further group DRGs by service line or clinical arena | values available |
proc_code | CPT or HCPCS procedural code performed within the encounter | values available |
proc_category | Category of the CPT or HCPCS procedure performed within the encounter | values available |
proc_subcategory | Sub-category of the CPT or HCPCS procedure performed within the encounter | values available |
proc_greedy | Boolean indicator for 'AND' or 'OR' when multiple procedure parameters specified - helps users offer various levels of specificity without confusing and/or logic | True or False |
taxonomy_code | Taxonomy of the organization or individual provider, 10-digit code that used to identify the type, classification, and area of specialization of an individual or organizational healthcare provider | values available |
specialty | Specialty of the organization or individual provider(s), specific focus of a provider by patient population, disease, skill or philosophy | values available |
specialty_category | Specialty category of the organization or individual provider(s), broad focus of a provider by patient population, disease, skill or philosophy | values available |
subspecialty | Specialty subcategory of the organization or individual provider(s), hyper-specific focus of a provider by patient population, disease, skill or philosophy | values available |
taxonomy_greedy | Boolean indicator for 'AND' or 'OR' when multiple taxonomy or specialty parameters specified - helps users offer various levels of specificity without confusing and/or logic | True or False |
ref_npi | Referring provider(s) on the encounter as indicated by their National Provider Identifiers (NPIs) | Any Valid NPI |
npi | National Provider Identifier (NPI) of individual and/or organizational provider(s) that appear on the encounter. | Any Valid NPI |
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 |
metro | Metropolitan and micropolitan statistical areas according to the US Census Bureau | values available |
short_zip | First three digits the zip code of the query patient population specified | First 3 digits of zip code(s) |
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 careEpisode query returns the JOURNEY_TABLE for all episodes that meet the criterion specified within the query.
The JOURNEY_TABLE offers comprehensive detail of longitudinal patient journeys, with line-item encounter detail, insight into payor, provider, procedure, revenue code, charges and SmartAllowedAmount®.
| feature | description | example |
|---|---|---|
| journey_id | Randomly generated identifier unique to each longitudinal patient journey. | 355ed5199ebed017821c7faa62965a61 |
| episode_id | Randomly generated identifier unique to each sequence of encounters with at least 30 days between visits. | 9318831e2ce3f30a83b1317b4d02ebfd |
| visit_id | Randomly generated identifier unique to each set of encounters that occur within the same event of care. | 315cb4170e73bed429a86e3dbb4e95a d6c6ce162412dfc97fce0c7a660ea2350 |
| encounter_id | Randomly generated identifier, unique to an isolated event of care. | 5ee14a8a88b18ce16f748baa7742b28d 60a057208872b0f650ccfae7a40ffa9e |
| date | Primary date of each encounter. | 2022-05-02 |
| patient_state | US State residence of the patient on each encounter. | CO |
| patient_short_zip | First three digits of the patient's zip code | 801 |
| patient_age | Age (within 5 years) of the patient at the moment of each encounter. | 65 |
| patient_gender | Gender/Sex of the patient | M |
| place_of_service | Type of facility where a encounter occurred. | Office |
| visit_type | Description of the setting of care. | Outpatient Visit |
| payor | Name of the primary payor on each encounter. | Centers for Medicare & Medicaid Services (CMS) |
| payor_channel | Payor channel-of-business, line-of-business, or plan type. | Medicare |
| ref_npi | Referring individual provider of an encounter, identified via their National Provider Identifier (NPI). | 1003878000 |
| hcp_npi | Primary individual provider of an encounter, identified via their National Provider Identifier (NPI). | 1003878000 |
| hcp_taxonomy | Primary taxonomy code of the individual provider on an encounter. | 207Q00000X - Allopathic and Osteopathic Physicians/Family Medicine |
| hcp_specialty | Primary specialty of the individual provider provider on an encounter. | Family Practice |
| hco_npi | Organizational provider of an encounter, identified via their National Provider Identifier (NPI). | 1063001238 |
| hcp_npi_list | List of all individual providers on an encounter, including primary and supporting providers, all designated by their National Provider Identifier (NPI) | 1003878000, 01920493029 |
| hco_npi_list | List of all organizational providers on an encounter, including primary and auxiliary providers, all designated by their National Provider Identifier (NPI) | 1063001238, 29380050392 |
| diag_list | All diagnoses coded (problem list) within each claim encounter. The first diagnosis listed is the primary diagnosis. | I10, E7849, E7800, E7800, I639 |
| diag_1 - diag_25 (optional) | Depending on the diag_num listed within the query parameters, the user may receive diagnoses 1 through 25 as their own columns rather than an combined list. Defaulted to return the top 5 diagnoses. | I10....I639 |
| rev_center_code | Revenue code corresponding to encounter | '0510' |
| rev_center_units | Number of days a revenue code has been triggered, corresponding to each encounter | 1.0 |
| proc_code | Procedure code performed within encounter. | 99457 |
| proc_units | Total number of procedure units within encounter. | 1.0 |
| proc_modifier | Procedure code modifier for procedure performed within encounter. | GW |
| line_charge | Line-item charge submitted to insurers for the procedures rendered by the provider. The line charge is the pre-adjudicated value submitted to insurers. | 63.02 |
| claim_charge | Line-item charge submitted to insurers along with facility and revenue center charge considerations. The line charge is the pre-adjudicated value submitted to insurers. | 284.49 |
| smart_allowed | Line-item estimate of allowed amount, utilizing proprietary ML on CMS, Price Transparency and Actual Adjudicated amounts to provide estimates within an average of 5% variance from actuals. | $80.54 |
Advanced Query Logic & Recipes
As one thinks about what the healthcare episode's they'd like returned in a query...the diagnosis, procedure, location, time and specialty criterion...things can get quite complex.
careEpisode 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 10 months ago