Domain
Managed care / healthcare pay analytics (simulated data)
Primary Question
How can enrollment, claims, and utilization data be structured to support cost monitoring, risk stratification, and care management prioritization in a managed care setting?
Status
In progress
What This Project Demonstrates
Member-month data modeling • Exposure-adjusted PMPM metrics • ED utilization analysis • Rule-based risk stratification for care management targeting
Tools
SQL Server (T-SQL) • Relational database design • Power BI
Transferable Insight
The member-month spine, cost and utilization logic, and risk stratification framework mirror analytical patterns used in healthcare payers to support transparent decision-making, operational reporting, and care management workflows across regulated, data-sensitive environments.
This project demonstrates how structured data analysis can support cost monitoring, utilization management, and care management prioritization in a managed care setting. Using fully simulated data and documented SQL workflows, the analysis links enrollment to medical claims, and utilization data to identify high-priority members for intervention and leadership review.
Managed care organizations must balance cost control, quality performance, and member access while operating within highly regulated environments. Data used for these purposes must be accurate, transparent, and interpretable by both technical and non-technical stakeholders.
This project was designed to reflect real-world payer analytics questions, including how exposure-adjusted metrics and clear business rules can support care management decision-making without relying on black-box models.
1
Which members account for a disproportionate share of medical spending?
2
How does cost and utilization vary across service areas and plan types?
3
Which members show high emergency department utilization?
4
Which members exhibit rising cost trends over time?
5
How can these signals be combined to prioritize care management outreach?
The analysis uses a relational data model with a member-month enrollment spine to ensure accurate exposure-adjusted metrics. Core tables include member enrollment, medical claims, pharmacy fills, quality measures, and a calendar dimension.
SQL queries are used in the following ways:
The structure emphasizes clarity, reproducibility, and governance, mirroring analytical practices commonly used in healthcare payer environments.
Using these measures, the analysis highlights
These insights demonstrate how structured analytics can move beyond descriptive reporting to support proactive, targeted decision-making.
All data used in this project is fully simulated. The analysis emphasizes
Ethical data use is essential when analytics influence healthcare access and resource allocation.
Effective program evaluation connects data to decisions and decisions to better outcomes for children and families.
Email: johnnyakenton@gmail.com
Thank you for reviewing my work.
All models and dashboards are based on simulated data.
Built in WordPress with Elementor