CVS Health Data Analyst Case Interview: Claims & Pharmacy Analytics to Improve Adherence and PMPM

This case mirrors common CVS Health (Aetna/Pharmacy Services/Retail) data-analyst interviews, emphasizing real-world payer/PBM problems, HIPAA-conscious analysis, and clear business storytelling. You will act as a Data Analyst supporting the Pharmacy Services team to diagnose drivers of low medication adherence and rising drug PMPM for a Medicare Advantage cohort and recommend scalable, member-first interventions. What the case covers at CVS Health: - Business context: Evaluate adherence for diabetes and hypertension therapies, its impact on CMS Star measures and overall PMPM; propose actions that leverage PBM levers (formulary/design, retail vs. mail, 90‑day conversion) and Retail/MinuteClinic touchpoints. - Data landscape (de-identified mock tables): rx_claims(claim_id, member_id, ndc, fill_date, days_supply, channel, copay, paid_amt), medical_claims(member_id, svc_date, dx_code, er_flag, ip_flag), members(member_id, dob, gender, plan_id, county), formulary(ndc, tier, pa_flag, step_flag), store_visits(member_id, visit_date, clinic_type), pa_requests(member_id, ndc, status, decision_date). - Core metrics to define and compute: PDC/MPR by therapeutic class; non-adherence rate (PDC<0.80); PMPM (medical, pharmacy, total); Generic Dispense Rate; 30/90‑day fill mix; mail‑order penetration; gap-closure rate for relevant HEDIS/CMS measures. - SQL/pseudocode exercise: Write queries to (1) calculate 6‑month PDC by member and class, (2) produce PMPM trend by month and channel, (3) segment non-adherent members by geography/plan/channel and identify top N NDCs contributing to PMPM. - Analysis & insights: Identify drivers (benefit design/copay, prior auth denials, short days_supply, lack of 90‑day sync, provider patterns, retail vs. mail access). Quantify opportunity size from a 30‑ to 90‑day conversion + synchronization + targeted outreach. - Experiment design: Outline a test (eligibility, randomization, success metrics: PDC lift, PMPM delta, gap closures, equity/quality checks), required sample size assumptions, risks, and how to monitor. - Communication & visualization: Draft a 90‑second exec summary and an outline of a dashboard: adherence funnel, PMPM trend, heat map of non-adherence hotspots, cohort drilldowns. Emphasize member-first impact, operational feasibility, and compliance. - CVS-specific expectations: Tie recommendations to cross-business execution (PBM + Retail/MinuteClinic), acknowledge CMS Stars/HEDIS implications, call out PHI handling and minimum necessary access, and consider scalability within large, diverse member populations. Format & flow (used in many CVS Health case rounds): - 5 min: Prompt read/clarifying questions. - 30–35 min: Individual work (SQL/pseudocode + analysis plan). - 15 min: Walkthrough of logic, assumptions, and ROI math. - 10–15 min: Q&A and scenario variations (e.g., specialty meds, Medicaid, supply chain constraints). Evaluation rubric aligned to CVS culture: - Business rigor & healthcare literacy (clear metric definitions; alignment to Stars/HEDIS; realistic PMPM math). - Technical depth (clean, efficient SQL/pseudocode; sensible data model assumptions; quality checks). - Actionability at scale (feasible interventions using PBM and retail levers; measurable outcomes). - Member-first & compliance mindset (equity, privacy, HIPAA awareness; risk mitigation). - Communication (concise narrative, visuals, and stakeholder tailoring).