Data Science for Healthcare Claims Data

Explore the four core functions of health care systems—provision, financing, resource generation, and stewardship—and learn how health care claims data connect personal provisions with purchasing.

Identify the three key actors in claims data: the patient, the provider, and the payer—and explore how data exchanges in the triangular workflow support payments, reimbursements, and care.

Explore vertical integration of payer and provider functions and its impact on claims data, data cleaning, data access scope, and accountability within health care systems.

Explore the main payment methods for health care providers, assess their pros and cons, and examine how incentives and financial risk affect payer versus provider, and impact claims data quality.

Explain how fee-for-service pays providers for each service with fixed advance fees, promoting overproduction and limited cooperation, while yielding highly detailed, timely, consistent, and accurate billing data.

Examine capitation funding, where providers receive a monthly per patient fee and bear risk. Compare data sources: encounter data may be limited versus claims data, with incentives for preventive care.

Explain how bundled payments, or case-based payments, reimburse care providers for an episode of care using DRGs as a broad description of hospital services from start to finish.

Global budgets fix a period’s total expenditure for negotiated services, allow flexible spending not tied to specific goods, and use claims data to support performance measurement and accountability.

Identify payment method in your context and recognize models like capitation, fee for service, and bundled payments. See how these methods affect quality and detail of health care claims data.

Third party payers, such as health insurers, face no cost conscious demand and information asymmetry in claims data, driving them to develop authorization signals as control systems.

Explain how billing data and authorization signals are organized in a data warehousing framework, using fact and dimension tables linked by keys like provider ID and patient ID.

Analyze how billing data and authorization signals stored in fact tables, referrals, prescriptions, diagnostic requests, and prior authorizations, mitigate cost-conscious demand and information asymmetry, enabling insights from healthcare claims data.

Learn how to merge healthcare claims data to prepare an analysis-ready dataset, including extracting from the data warehouse using fact and dimension tables and capturing full episodes of care.

Merge fact and dimension tables in a data warehouse to build a custom dataset with SQL, calculating patient age at knee surgery and linking billing with authorization signals.

Merge data from multiple billing fact tables across providers to form a complete episode of care for diabetes, then prepare for downstream analysis.

Assign health care claims data to higher level categories to reduce complexity, tailor categorization to specific analyses, and apply domain knowledge for relevant subspecialties like ob gyn.

Consult the data dictionary to map codes to higher level claim categories using a centralized repository of meanings, relationships, origins, formats, including concept class and domain dimensions.

Learn to assign codes to higher level categories using hierarchical structures in claims data when a data dictionary is unavailable, by reviewing the services dimension table and department hierarchies.

Identify patterns in codes to assign higher level categories when data dictionaries miss meaning. Show how prefixes link to burn wounds, dialysis, and prosthetics, guiding official documentation.

Apply existing hierarchies from national or international coding systems to reduce code variety in healthcare claims, using loinc for labs, atc for pharmaceuticals, and icd-10 or snomed-ct hierarchies.

Consult domain experts to categorize medical codes by having an ob-gyn doctor assign each code to obstetrics or gynecology care, and consult medical coding specialists who translate procedures into codes.

Access all course resources in a centralized google drive folder, including code, data sets, and open-access literature references, and revisit anytime to download or share materials.

Work with the ObGyn csv to manipulate Medicare claims data using Excel (or Google Sheets), exploring person IDs, dates, procedure names, amounts, and provider details.

Explore basic data filtering in Excel to examine healthcare claims, filtering by health care provider, concept name, procedure dates, or person ID, and clear filters to reset.

Learn to transform raw healthcare claims data with pivot tables in Excel, summarizing data with sums and averages, drag and drop fields to create meaningful graphs.

Create a pivot table in Excel to calculate total revenue per provider from healthcare claims, add concept name for procedure-level detail, and sort procedures by cost.

Choose aggregations in a pivot table to summarize data, switch between sum, count, and average, and format numbers with thousands separators to show total payments per healthcare provider.

Group procedure date by year and quarter in a pivot table to measure revenue per provider per quarter, then rotate provider names from rows to columns for clearer comparisons.

Use pivot table outputs as inputs for charts in Excel to visualize quarterly revenue by healthcare provider, then switch metrics and chart types with the show field list.

Explore how to merge two datasets using a table join and the vertical lookup function in Excel, noting sorting in ascending order and performance issues with large tables.

Apply a domain expert–defined category column to a 52,000-row healthcare claims dataset, grouping 59 codes into four high level categories: consultation, delivery, diagnostic, and procedure.

Apply vertical lookup to add a category column to the 52,000-row dataset by joining on the procedure concept id, sorting keys, and using the second column of the lookup table.

Discover vertical look-up part 3 and how to fill a formula down across 52,000 rows to assign categories. Compare two fill-down methods—Ctrl+Shift+Down with Ctrl+Shift+Up, and double-clicking the fill handle.

Finalize the dataset by pasting only values from a vertical lookup to avoid formula errors in Excel, improving performance and correctness of the healthcare claims data.

Demonstrate how introducing a high level categorization improves readability and analytics in healthcare claims data, using a pivot table to compare providers and codes.

Explore how Medicare claims synthetic public use files enable research and how Google BigQuery provides a cloud data warehouse with a Gmail account, SQL knowledge, and dimension and fact tables.

Explore a relational schema for healthcare claims data, showing how fact and dimension tables like procedure occurrence, provider, person, concept, drug era, and cost interrelate via SQL joins in BigQuery.

Learn how the new big query interface changes access to the public claims data set, while the SQL script remains unchanged for healthcare claims data.

Explore Google BigQuery sandbox for free, with no credit card required, and access public CMS healthcare datasets. Join procedure occurrences and concepts using fact and dimension tables with SQL.

Access the Medicare claims dataset in the updated BigQuery interface by using the search function and broadening results to reveal the public dataset.

Learn to extract, transform, and load data with SQL in Google BigQuery, exploring the CMS synthetic patient data, fact and dimension tables, and mapping procedure codes to names.

Merge the procedure occurrence fact table with the concept dimension table on concept ID to add concept name and domain ID, enabling readable, categorized query results.

Visualize healthcare claims data by using BigQuery's Explore Data to launch Google Data Studio and create charts in a business intelligence dashboard.

Confirm knee surgery codes by querying the concept dimension table with a where clause and using a regular expression to identify knee replacement in concept names, then select exact code.

Inspect the compatibility between the person dimension and the procedure occurrence fact. Align birth date fields with the procedure date to calculate patient age at operation across data types.

Concatenate the birth year, month, and day columns to form a birth date, then cast the result as a date in SQL to enable age calculations at procedure time.

Create a subquery to bring birth date into the query, join it to the procedure occurrence data, and compute the patient's age at the time of the procedure.

Compute age at procedure with the date difference function in SQL. Filter by a where clause for the procedure and review age at procedure in the healthcare claims data warehouse.

Compare absolute health care costs with relative metrics by converting to a percentage of GDP and per capita, using Curacao and Japan in 2017, and visualize with dual axes.

Compare healthcare providers using relative numbers by converting values to percentage of row total in Excel, ensuring each row sums to 100% for clear percent-based comparison.

Learn how to compute per capita metrics in healthcare claims data using distinct count to measure unique patients and derive average revenue per capita across providers.

Create a pivot table in Excel, enable distinct count by adding data to the data model (avoiding csv limitations), and compute average revenue per provider per patient.