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
person_id	varchar	Unique identifier for each patient in the dataset.	no
year_month	varchar	Unique year-month of in the dataset computed from eligibility.	no
payer	varchar	Name of the payer (i.e. health insurer) providing coverage.	no
plan	varchar	Name of the plan (i.e. sub contract) providing coverage.	no
data_source	varchar	User-configured field that indicates the data source (e.g. typically named after the payer and state "BCBS Tennessee").	no
payer_attributed_provider	varchar	Unique identifier for the provider assigned to this patient-year_month by the payer.	no
payer_attributed_provider_practice	varchar	Name of the practice for the payer attributed provider.	no
payer_attributed_provider_organization	varchar	Name of the organization for the payer attributed provider.	no
payer_attributed_provider_lob	varchar	Name of the line of business for the payer attributed provider (e.g. medicare, medicaid, commercial).	no
custom_attributed_provider	varchar	Unique identifier for the provider assigned to this patient-year_month by the user.	no
custom_attributed_provider_practice	varchar	Name of the practice for the attributed provider assigned by the user.	no
custom_attributed_provider_organization	varchar	Name of the organization for the attributed provider assigned by the user.	no
custom_attributed_provider_lob	varchar	Name of the line of business for the attributed provider assigned by the user (e.g. medicare, medicaid, commercial).	no
inpatient_paid	number	Total inpatient paid amount per member per month (PMPM).	no
outpatient_paid	number	Total outpatient paid amount per member per month (PMPM).	no
OFFICE_BASED_PAID	number		no
ancillary_paid	number	Total ancillary paid amount per member per month (PMPM).	no
other_paid	number	Total other paid amount per member per month (PMPM).	no
pharmacy_paid	number	Total pharmacy paid amount per member per month (PMPM).	no
acute_inpatient_paid	number	Total acute inpatient paid amount per member per month (PMPM).	no
ambulance_paid	number	Total ambulance paid amount per member per month (PMPM).	no
AMBULATORY_SURGERY_CENTER_PAID	number		no
dialysis_paid	number	Total dialysis paid amount per member per month (PMPM).	no
durable_medical_equipment_paid	number	Total durable medical equipment paid amount per member per month (PMPM).	no
emergency_department_paid	number	Total emergency department paid amount per member per month (PMPM).	no
home_health_paid	number	Total home health paid amount per member per month (PMPM).	no
INPATIENT_HOSPICE_PAID	number		no
inpatient_psychiatric_paid	number	Total inpatient psychiatric paid amount per member per month (PMPM).	no
inpatient_rehabilitation_paid	number	Total inpatient rehabilitation paid amount per member per month (PMPM).	no
lab_paid	number	Total lab paid amount per member per month (PMPM).	no
OBSERVATION_PAID	number		no
OFFICE_BASED_OTHER_PAID	number		no
OFFICE_BASED_PT_OT_ST_PAID	number		no
OFFICE_BASED_RADIOLOGY_PAID	number		no
OFFICE_BASED_SURGERY_PAID	number		no
OFFICE_BASED_VISIT_PAID	number		no
OTHER_PAID_2	number		no
OUTPATIENT_HOSPICE_PAID	number		no
outpatient_hospital_or_clinic_paid	number	Total outpatient hospital or clinic paid amount per member per month (PMPM).	no
OUTPATIENT_PT_OT_ST_PAID	number		no
outpatient_psychiatric_paid	number	Total outpatient psychiatric paid amount per member per month (PMPM).	no
OUTPATIENT_RADIOLOGY_PAID	number		no
outpatient_rehabilitation_paid	number	Total outpatient rehabilitation paid amount per member per month (PMPM).	no
OUTPATIENT_SURGERY_PAID	number		no
PHARMACY_PAID_2	number		no
skilled_nursing_paid	number	Total skilled nursing paid amount per member per month (PMPM).	no
TELEHEALTH_VISIT_PAID	number		no
urgent_care_paid	number	Total urgent care paid amount per member per month (PMPM).	no
inpatient_allowed	number	Total inpatient allowed amount per member per month (PMPM).	no
outpatient_allowed	number	Total outpatient allowed amount per member per month (PMPM).	no
OFFICE_BASED_ALLOWED	number		no
ancillary_allowed	number	Total ancillary allowed amount per member per month (PMPM).	no
other_allowed	number	Total other allowed amount per member per month (PMPM).	no
pharmacy_allowed	number	Total pharmacy allowed amount per member per month (PMPM).	no
acute_inpatient_allowed	number	Total acute inpatient allowed amount per member per month (PMPM).	no
ambulance_allowed	number	Total ambulance allowed amount per member per month (PMPM).	no
AMBULATORY_SURGERY_CENTER_ALLOWED	number		no
dialysis_allowed	number	Total dialysis allowed amount per member per month (PMPM).	no
durable_medical_equipment_allowed	number	Total durable medical equipment allowed amount per member per month (PMPM).	no
emergency_department_allowed	number	Total emergency department allowed amount per member per month (PMPM).	no
home_health_allowed	number	Total home health allowed amount per member per month (PMPM).	no
INPATIENT_HOSPICE_ALLOWED	number		no
inpatient_psychiatric_allowed	number	Total inpatient psychiatric allowed amount per member per month (PMPM).	no
inpatient_rehabilitation_allowed	number	Total inpatient rehabilitation allowed amount per member per month (PMPM).	no
lab_allowed	number	Total lab allowed amount per member per month (PMPM).	no
OBSERVATION_ALLOWED	number		no
OFFICE_BASED_OTHER_ALLOWED	number		no
OFFICE_BASED_PT_OT_ST_ALLOWED	number		no
OFFICE_BASED_RADIOLOGY_ALLOWED	number		no
OFFICE_BASED_SURGERY_ALLOWED	number		no
OFFICE_BASED_VISIT_ALLOWED	number		no
OTHER_ALLOWED_2	number		no
OUTPATIENT_HOSPICE_ALLOWED	number		no
outpatient_hospital_or_clinic_allowed	number	Total outpatient hospital or clinic allowed amount per member per month (PMPM).	no
OUTPATIENT_PT_OT_ST_ALLOWED	number		no
outpatient_psychiatric_allowed	number	Total outpatient psychiatric allowed amount per member per month (PMPM).	no
OUTPATIENT_RADIOLOGY_ALLOWED	number		no
outpatient_rehabilitation_allowed	number	Total outpatient rehabilitation allowed amount per member per month (PMPM).	no
OUTPATIENT_SURGERY_ALLOWED	number		no
PHARMACY_ALLOWED_2	number		no
skilled_nursing_allowed	number	Total skilled nursing allowed amount per member per month (PMPM).	no
TELEHEALTH_VISIT_ALLOWED	number		no
urgent_care_allowed	number	Total urgent care allowed amount per member per month (PMPM).	no
total_paid	number	Total paid amount per member per month (PMPM).	no
medical_paid	number	Total medical paid amount per member per month (PMPM).	no
total_allowed	number	Total allowed amount per member per month (PMPM).	no
medical_allowed	number	Total medical allowed amount per member per month (PMPM).	no
tuva_last_run	varchar	The last time the data was refreshed. Generated by `dbt_utils.pretty_time` as the local time of the `dbt run` environment. Timezone is configurable via the `tuva_last_run` var.	no

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
year_month	varchar	Unique year-month of in the dataset computed from eligibility.	no
payer	varchar	Name of the payer (i.e. health insurer) providing coverage.	no
plan	varchar	Name of the plan (i.e. sub contract) providing coverage.	no
data_source	varchar	User-configured field that indicates the data source (e.g. typically named after the payer and state "BCBS Tennessee").	no
member_months	number	The sum of member months.	no
total_paid	number	Total paid amount per member per month (PMPM).	no
medical_paid	number	Total medical paid amount per member per month (PMPM).	no
inpatient_paid	number	Total inpatient paid amount per member per month (PMPM).	no
outpatient_paid	number	Total outpatient paid amount per member per month (PMPM).	no
OFFICE_BASED_PAID	number		no
ancillary_paid	number	Total ancillary paid amount per member per month (PMPM).	no
other_paid	number	Total other paid amount per member per month (PMPM).	no
pharmacy_paid	number	Total pharmacy paid amount per member per month (PMPM).	no
acute_inpatient_paid	number	Total acute inpatient paid amount per member per month (PMPM).	no
ambulance_paid	number	Total ambulance paid amount per member per month (PMPM).	no
AMBULATORY_SURGERY_CENTER_PAID	number		no
dialysis_paid	number	Total dialysis paid amount per member per month (PMPM).	no
durable_medical_equipment_paid	number	Total durable medical equipment paid amount per member per month (PMPM).	no
emergency_department_paid	number	Total emergency department paid amount per member per month (PMPM).	no
home_health_paid	number	Total home health paid amount per member per month (PMPM).	no
INPATIENT_HOSPICE_PAID	number		no
inpatient_psychiatric_paid	number	Total inpatient psychiatric paid amount per member per month (PMPM).	no
inpatient_rehabilitation_paid	number	Total inpatient rehabilitation paid amount per member per month (PMPM).	no
lab_paid	number	Total lab paid amount per member per month (PMPM).	no
OBSERVATION_PAID	number		no
OFFICE_BASED_OTHER_PAID	number		no
OFFICE_BASED_PT_OT_ST_PAID	number		no
OFFICE_BASED_RADIOLOGY_PAID	number		no
OFFICE_BASED_SURGERY_PAID	number		no
OFFICE_BASED_VISIT_PAID	number		no
outpatient_hospital_or_clinic_paid	number	Total outpatient hospital or clinic paid amount per member per month (PMPM).	no
OUTPATIENT_PT_OT_ST_PAID	number		no
outpatient_psychiatric_paid	number	Total outpatient psychiatric paid amount per member per month (PMPM).	no
OUTPATIENT_RADIOLOGY_PAID	number		no
outpatient_rehabilitation_paid	number	Total outpatient rehabilitation paid amount per member per month (PMPM).	no
OUTPATIENT_SURGERY_PAID	number		no
skilled_nursing_paid	number	Total skilled nursing paid amount per member per month (PMPM).	no
TELEHEALTH_VISIT_PAID	number		no
urgent_care_paid	number	Total urgent care paid amount per member per month (PMPM).	no
total_allowed	number	Total allowed amount per member per month (PMPM).	no
medical_allowed	number	Total medical allowed amount per member per month (PMPM).	no
inpatient_allowed	number	Total inpatient allowed amount per member per month (PMPM).	no
outpatient_allowed	number	Total outpatient allowed amount per member per month (PMPM).	no
OFFICE_BASED_ALLOWED	number		no
ancillary_allowed	number	Total ancillary allowed amount per member per month (PMPM).	no
other_allowed	number	Total other allowed amount per member per month (PMPM).	no
pharmacy_allowed	number	Total pharmacy allowed amount per member per month (PMPM).	no
acute_inpatient_allowed	number	Total acute inpatient allowed amount per member per month (PMPM).	no
ambulance_allowed	number	Total ambulance allowed amount per member per month (PMPM).	no
AMBULATORY_SURGERY_CENTER_ALLOWED	number		no
dialysis_allowed	number	Total dialysis allowed amount per member per month (PMPM).	no
durable_medical_equipment_allowed	number	Total durable medical equipment allowed amount per member per month (PMPM).	no
emergency_department_allowed	number	Total emergency department allowed amount per member per month (PMPM).	no
home_health_allowed	number	Total home health allowed amount per member per month (PMPM).	no
INPATIENT_HOSPICE_ALLOWED	number		no
inpatient_psychiatric_allowed	number	Total inpatient psychiatric allowed amount per member per month (PMPM).	no
inpatient_rehabilitation_allowed	number	Total inpatient rehabilitation allowed amount per member per month (PMPM).	no
lab_allowed	number	Total lab allowed amount per member per month (PMPM).	no
OBSERVATION_ALLOWED	number		no
OFFICE_BASED_OTHER_ALLOWED	number		no
OFFICE_BASED_PT_OT_ST_ALLOWED	number		no
OFFICE_BASED_RADIOLOGY_ALLOWED	number		no
OFFICE_BASED_SURGERY_ALLOWED	number		no
OFFICE_BASED_VISIT_ALLOWED	number		no
outpatient_hospital_or_clinic_allowed	number	Total outpatient hospital or clinic allowed amount per member per month (PMPM).	no
OUTPATIENT_PT_OT_ST_ALLOWED	number		no
outpatient_psychiatric_allowed	number	Total outpatient psychiatric allowed amount per member per month (PMPM).	no
OUTPATIENT_RADIOLOGY_ALLOWED	number		no
outpatient_rehabilitation_allowed	number	Total outpatient rehabilitation allowed amount per member per month (PMPM).	no
OUTPATIENT_SURGERY_ALLOWED	number		no
skilled_nursing_allowed	number	Total skilled nursing allowed amount per member per month (PMPM).	no
TELEHEALTH_VISIT_ALLOWED	number		no
urgent_care_allowed	number	Total urgent care allowed amount per member per month (PMPM).	no
tuva_last_run	varchar	The last time the data was refreshed. Generated by `dbt_utils.pretty_time` as the local time of the `dbt run` environment. Timezone is configurable via the `tuva_last_run` var.	no

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
year_month	varchar	Unique year-month of in the dataset computed from eligibility.	no
payer	varchar	Name of the payer (i.e. health insurer) providing coverage.	no
data_source	varchar	User-configured field that indicates the data source (e.g. typically named after the payer and state "BCBS Tennessee").	no
member_months	number	The sum of member months.	no
total_paid	number	Total paid amount per member per month (PMPM).	no
medical_paid	number	Total medical paid amount per member per month (PMPM).	no
inpatient_paid	number	Total inpatient paid amount per member per month (PMPM).	no
outpatient_paid	number	Total outpatient paid amount per member per month (PMPM).	no
OFFICE_BASED_PAID	number		no
ancillary_paid	number	Total ancillary paid amount per member per month (PMPM).	no
other_paid	number	Total other paid amount per member per month (PMPM).	no
pharmacy_paid	number	Total pharmacy paid amount per member per month (PMPM).	no
acute_inpatient_paid	number	Total acute inpatient paid amount per member per month (PMPM).	no
ambulance_paid	number	Total ambulance paid amount per member per month (PMPM).	no
AMBULATORY_SURGERY_CENTER_PAID	number		no
dialysis_paid	number	Total dialysis paid amount per member per month (PMPM).	no
durable_medical_equipment_paid	number	Total durable medical equipment paid amount per member per month (PMPM).	no
emergency_department_paid	number	Total emergency department paid amount per member per month (PMPM).	no
home_health_paid	number	Total home health paid amount per member per month (PMPM).	no
INPATIENT_HOSPICE_PAID	number		no
inpatient_psychiatric_paid	number	Total inpatient psychiatric paid amount per member per month (PMPM).	no
inpatient_rehabilitation_paid	number	Total inpatient rehabilitation paid amount per member per month (PMPM).	no
lab_paid	number	Total lab paid amount per member per month (PMPM).	no
OBSERVATION_PAID	number		no
OFFICE_BASED_OTHER_PAID	number		no
OFFICE_BASED_PT_OT_ST_PAID	number		no
OFFICE_BASED_RADIOLOGY_PAID	number		no
OFFICE_BASED_SURGERY_PAID	number		no
OFFICE_BASED_VISIT_PAID	number		no
outpatient_hospital_or_clinic_paid	number	Total outpatient hospital or clinic paid amount per member per month (PMPM).	no
OUTPATIENT_PT_OT_ST_PAID	number		no
outpatient_psychiatric_paid	number	Total outpatient psychiatric paid amount per member per month (PMPM).	no
OUTPATIENT_RADIOLOGY_PAID	number		no
outpatient_rehabilitation_paid	number	Total outpatient rehabilitation paid amount per member per month (PMPM).	no
OUTPATIENT_SURGERY_PAID	number		no
skilled_nursing_paid	number	Total skilled nursing paid amount per member per month (PMPM).	no
TELEHEALTH_VISIT_PAID	number		no
urgent_care_paid	number	Total urgent care paid amount per member per month (PMPM).	no
total_allowed	number	Total allowed amount per member per month (PMPM).	no
medical_allowed	number	Total medical allowed amount per member per month (PMPM).	no
inpatient_allowed	number	Total inpatient allowed amount per member per month (PMPM).	no
outpatient_allowed	number	Total outpatient allowed amount per member per month (PMPM).	no
OFFICE_BASED_ALLOWED	number		no
ancillary_allowed	number	Total ancillary allowed amount per member per month (PMPM).	no
other_allowed	number	Total other allowed amount per member per month (PMPM).	no
pharmacy_allowed	number	Total pharmacy allowed amount per member per month (PMPM).	no
acute_inpatient_allowed	number	Total acute inpatient allowed amount per member per month (PMPM).	no
ambulance_allowed	number	Total ambulance allowed amount per member per month (PMPM).	no
AMBULATORY_SURGERY_CENTER_ALLOWED	number		no
dialysis_allowed	number	Total dialysis allowed amount per member per month (PMPM).	no
durable_medical_equipment_allowed	number	Total durable medical equipment allowed amount per member per month (PMPM).	no
emergency_department_allowed	number	Total emergency department allowed amount per member per month (PMPM).	no
home_health_allowed	number	Total home health allowed amount per member per month (PMPM).	no
INPATIENT_HOSPICE_ALLOWED	number		no
inpatient_psychiatric_allowed	number	Total inpatient psychiatric allowed amount per member per month (PMPM).	no
inpatient_rehabilitation_allowed	number	Total inpatient rehabilitation allowed amount per member per month (PMPM).	no
lab_allowed	number	Total lab allowed amount per member per month (PMPM).	no
OBSERVATION_ALLOWED	number		no
OFFICE_BASED_OTHER_ALLOWED	number		no
OFFICE_BASED_PT_OT_ST_ALLOWED	number		no
OFFICE_BASED_RADIOLOGY_ALLOWED	number		no
OFFICE_BASED_SURGERY_ALLOWED	number		no
OFFICE_BASED_VISIT_ALLOWED	number		no
outpatient_hospital_or_clinic_allowed	number	Total outpatient hospital or clinic allowed amount per member per month (PMPM).	no
OUTPATIENT_PT_OT_ST_ALLOWED	number		no
outpatient_psychiatric_allowed	number	Total outpatient psychiatric allowed amount per member per month (PMPM).	no
OUTPATIENT_RADIOLOGY_ALLOWED	number		no
outpatient_rehabilitation_allowed	number	Total outpatient rehabilitation allowed amount per member per month (PMPM).	no
OUTPATIENT_SURGERY_ALLOWED	number		no
skilled_nursing_allowed	number	Total skilled nursing allowed amount per member per month (PMPM).	no
TELEHEALTH_VISIT_ALLOWED	number		no
urgent_care_allowed	number	Total urgent care allowed amount per member per month (PMPM).	no
tuva_last_run	varchar	The last time the data was refreshed. Generated by `dbt_utils.pretty_time` as the local time of the `dbt run` environment. Timezone is configurable via the `tuva_last_run` var.	no

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;