Financial PMPM

Overview

Code

The Financial PMPM data mart computes member months and stratifies population paid and allowed amounts by member months and service categories across various payers and plans.

Instructions

This mart runs on claims data. You just need to map your data to the input layer and run the project.

dbt Examples

# Runs all marts
dbt build

# Runs only the PMPM mart
dbt build --select tag:financial_pmpm

Data Dictionary

pmpm_prep

A table that computes all the paid and allowed statistics for every patient_id and year_month combination.

Primary Keys:

• patient_id
• year_month
• plan
• data_source

Column	Data Type	Description	Terminology

pmpm_payer_plan

A table that computes per member per month statistics for every service category by aggregating across patients from pmpm_prep. This table is at the payer, plan grain.

Primary Keys:

• year_month
• payer
• plan
• data_source

Column	Data Type	Description	Terminology

pmpm_payer

A table that computes per member per month statistics for every service category by aggregating across patients from pmpm_prep. This table is at the payer grain.

Primary Keys:

• year_month
• payer
• data_source

Column	Data Type	Description	Terminology

Analytics

Calculate Member Months and Total Medical Spend

select
      data_source
    , year_month
    , cast(sum(medical_paid) as decimal(18,2)) as medical_paid
    , count(*) as member_months
    , cast(sum(medical_paid)/count(*) as decimal(18,2)) as pmpm
from financial_pmpm.pmpm_prep
group by
      data_source
    , year_month
order by
      data_source
    , year_month;

Trending PMPM by Service Category

The pmpm table already breaks out pmpm by service category and groups it at the member month level.

select *
from financial_pmpm.pmpm_payer
order by year_month;

Trending PMPM by Claim Type

Here we calculate PMPM manually by counting member months and joining payments by claim type to them.

with member_month as (
    select
          data_source
        , year_month
        , count(*) as member_months
    from financial_pmpm.member_months
    group by
          data_source
        , year_month
)

, medical_claims as (
    select
          mc.data_source
        , to_char(claim_start_date, 'YYYYMM') AS year_month
        , claim_type
        , cast(sum(paid_amount) as decimal(18,2)) AS paid_amount
    from core.medical_claim mc
        inner join financial_pmpm.member_months mm
            on mc.patient_id = mm.patient_id
            and mc.data_source = mm.data_source
            and to_char(mc.claim_start_date, 'YYYYMM') = mm.year_month
    group by
          mc.data_source
        , to_char(claim_start_date, 'YYYYMM')
        , claim_type
)

select
      member_month.data_source
    , member_month.year_month
    , medical_claims.claim_type
    , medical_claims.paid_amount
    , member_month.member_months
    , cast(medical_claims.paid_amount / member_month.member_months as decimal(18,2)) as pmpm_claim_type
from member_month
    left join medical_claims
        on member_month.data_source = medical_claims.data_source
        and member_month.year_month = medical_claims.year_month
order by
      member_month.data_source
    , member_month.year_month
    , medical_claims.claim_type;

PMPM by Chronic Condition

Here we calculate PMPM by chronic condition. Since members can and do have more than one chronic condition, payments and members months are duplicated. This is useful for comparing spend across chronic conditions, but should be used with caution given the duplication across conditions.

with chronic_condition_members as (
    select distinct patient_id
    from chronic_conditions.tuva_chronic_conditions_long
)

, chronic_conditions as (
    select
          patient_id
        , condition
    from chronic_conditions.tuva_chronic_conditions_long

    union

    select
          p.patient_id
        , 'No Chronic Conditions' as Condition
    from core.patient p
        left join chronic_condition_members ccm
            on p.patient_id=ccm.patient_id
    where ccm.patient_id is null
)

, medical_claims as (
    select
          mc.data_source
        , mc.patient_id
        , to_char(claim_start_date, 'YYYYMM') AS year_month
        , cast(sum(paid_amount) as decimal(18,2)) AS paid_amount
    from core.medical_claim mc
        inner join financial_pmpm.member_months mm
            on mc.patient_id = mm.patient_id
            and mc.data_source = mm.data_source
            and to_char(mc.claim_start_date, 'YYYYMM') = mm.year_month
    group by
          mc.data_source
        , mc.patient_id
        , to_char(claim_start_date, 'YYYYMM')
)

select
      mm.data_source
    , mm.year_month
    , cc.condition
    , count(*) as member_months
    , sum(mc.paid_amount) as paid_amount
    , cast(sum(mc.paid_amount) / count(*) as decimal(18,2)) as medical_pmpm
from financial_pmpm.member_months mm
    left join chronic_conditions cc
        on mm.patient_id = cc.patient_id
    left join medical_claims mc
        on mm.patient_id = mc.patient_id
        and mm.year_month = mc.year_month
        and mm.data_source = mc.data_source
group by
      mm.data_source
    , mm.year_month
    , cc.condition
order by member_months desc;