Monthly Revenue Estimation (MRE) Models

This document explains the Monthly Revenue Estimation (MRE) models used to calculate billable patient enrollments for each payer. The models determine eligibility for monthly billing based on enrollment periods, service requirements, and payer-specific business rules. BizOps maintains logic for these models here.

Monthly Billing Windows by Payer

Each payer has specific monthly billing windows that define when services must be provided and when payments are expected:

Payer	Submission Window	Payment Date
AAH	1st - end of month	Last day of month
Anthem	1st - end of month	Last day of month
CalOptima Health	1st - end of month	Last day of month
CCAH (Merced, Monterey, Santa Cruz)	26th (prev month) - 26th	Last day of month
CHG	1st - end of month	Last day of month
CalViva Health	1st - end of month	Last day of month
Gold Coast Health Plan	1st - end of month	Last day of month
Health Net	1st - end of month	Last day of month
IEHP	11th (prev month) - 10th	Last day of month
LA Care	1st - end of month	Last day of month
Molina	15th (prev month) - 14th	Last day of month
San Joaquin (HPSJ)	1st - end of month	Last day of month

Key Notes:

Submission windows determine when services must be provided to qualify for billing
Payment date is when reimbursement is expected from the payer
Some payers (CCAH, IEHP, Molina) have submission windows spanning across months

Enrollment and Submission Window Overlap Logic

A patient is considered enrolled for a given submission month if their enrollment period overlaps with any part of that month's submission window. The overlap condition is:

enrollment_start_date <= submission_end_date
AND enrollment_end_date >= submission_start_date

Examples:

Full month enrollment:
- Patient enrolled: Jan 1 - Mar 31
- February submission window: Feb 1 - Feb 28
- ✅ Patient is enrolled for February (fully overlaps)
Partial month enrollment:
- Patient enrolled: Feb 15 - Mar 31
- February submission window: Feb 1 - Feb 28
- ✅ Patient is enrolled for February (enrolled for 13 days)
Cross-month submission window:
- Patient enrolled: Jan 20 - Feb 5
- Molina submission window: Jan 15 - Feb 14
- ✅ Patient is enrolled for this billing period (overlaps Jan 20 - Feb 5)
No overlap:
- Patient enrolled: Jan 1 - Jan 15
- February submission window: Feb 1 - Feb 28
- ❌ Patient is NOT enrolled for February (no overlap)

Important: This overlap check determines initial eligibility. Service requirements (if applicable) are then evaluated for qualifying services that occur within the submission window during the enrollment period.

Implementation: int__mre_adjusted_monthly_enrollments.sql:36-38

Service Requirements by Payer

Service-Based Payers

These payers require specific services during the submission period to be billable:

Molina

Requirement: Any service (encounter OR outreach)
Details: Most flexible requirement - any communication counts

CHG

Requirement: Encounter AND contacted_entity = 'patient_or_guardian'
Details: Must be direct patient contact, not family/caregiver only

AAH

Requirement: Encounter AND contacted_entity = 'patient_or_guardian'
Special Logic: Contact classification based on service intensity:
- High Contact: 3+ encounters with at least 1 in-person visit
- Low Contact: 1+ encounters (but doesn't meet high contact criteria)

LA Care

Requirement: Encounter AND contacted_entity = 'patient_or_guardian' AND duration ≥ 45 minutes
Special Logic: Service count based on encounter duration groups:
- Each encounter is classified into duration groups (~1 hour increments)
- Groups are summed to determine total qualifying service count
- Encounters < 45 minutes do not contribute to the count (group value = 0)
- Duration Groups:
  - < 45 minutes: 0
  - 45-104 minutes: 1
  - 105-164 minutes: 2
  - 165-224 minutes: 3
  - 225-284 minutes: 4
  - ≥ 285 minutes: 5
- Example: A 45-minute encounter (group 1) + 110-minute encounter (group 2) = qualifying_service_count of 3
Details: Duration-based tiered service counting system

San Joaquin (HPSJ)

Requirement: Encounter AND contacted_entity = 'patient_or_guardian'
Details: Same as CHG - direct patient contact required

Anthem

Requirement: Encounter AND contacted_entity = 'patient_or_guardian'
Details: Same as CHG - direct patient contact required

CalOptima Health

Requirement: Minimum 2 hours (120 minutes) of ECM services (any type)
Details: Most inclusive - both encounters and outreach count, all contacted entities count
Special Logic: Duration-based requirement using duration_in_minutes field

Gold Coast Health Plan

Requirement: 2 encounters AND contacted_entity = 'patient_or_guardian'
Details: Similar to CHG/Anthem but requires at least 2 encounters instead of 1
Special Logic: Any contact method counts (in-person, telephonic, video, etc.)

Non-Service Payers

These payers do not require services and are billable based on enrollment alone:

CalViva Health
Health Net
CCAH (Merced, Monterey, Santa Cruz)
IEHP (enrollment-based after special adjustments)

Special Payer Logic

CCAH (Merced, Monterey, Santa Cruz)

warning

Enrollment Adjustment: Uses authorization valid_from_at date instead of original enrollment start date. Billing can only begin when authorization is valid, not when enrollment begins. This can significantly delay billable periods.

Implementation: int__mre_ccah_adjusted_enrollment_periods.sql:38

IEHP

warning

Service Gap Rule: If there's a 90+ day gap between patient encounters, enrollment effectively ends 90 days after the last service before the gap. IEHP considers patients inactive after extended service gaps. Proactive outreach prevents revenue loss.

Service Definition: Must be encounters with contacted_entity = 'patient_or_guardian'

Implementation: int__mre_iehp_adjusted_enrollment_periods.sql:57-67

Filtering Logic

CCAH & Molina: Exclude records with status_type = 'submitted' from final output
Rationale: These payers don't bill for submitted-only status
Implementation: billable_monthly_patient_enrollments.sql:95

Output Model:

billable_monthly_patient_enrollments

Key Fields

Field	Description
`billable_monthly_patient_enrollments_key`	Unique identifier combining person_id, enrolled_on_date, and submission_month
`person_id`	Patient identifier
`payer_name`	Insurance payer name
`enrolled_on_date`	Enrollment start date (adjusted for CCAH)
`unenrolled_on_date`	Enrollment end date (adjusted for IEHP)
`status_type`	Current enrollment status (submitted/enrolled)
`submission_start_date`	Start of monthly billing window
`submission_end_date`	End of monthly billing window
`submission_month`	Billing month identifier (YYYY-MM-DD)
`payment_date`	Expected payment date
`meets_service_requirements`	Whether patient met payer-specific service requirements
`qualifying_service_count`	Number/sum of qualifying services (NULL for non-service payers; for LA Care, sum of duration groups)
`contact_classification`	Service intensity (high_contact/low_contact for AAH only)

Demographics & Clinical Fields

Field	Description
`ecm_profile_id`	ECM profile identifier
`birth_date`	Patient birth date
`clinic_name`	Primary clinic name
`clinics_group_name`	Clinic group/network
`zip_code`	Patient zip code
`is_metro`	Whether patient is at Metropolitan clinic
`is_adult`	Whether patient is 18+ at submission start

Model Architecture

stage__mre_* (staging layer)
├── stage__mre_payer_billing_periods    # Monthly windows by payer
├── stage__mre_enrollment_periods       # Base enrollment data
├── stage__mre_patients                 # Patient demographics
├── stage__mre_services                 # ECM services/communications
└── stage__mre_authorization_encounters # Authorization data

int__mre_* (intermediate layer)
├── int__mre_ccah_adjusted_enrollment_periods    # CCAH start date adjustments
├── int__mre_iehp_adjusted_enrollment_periods    # IEHP end date adjustments
├── int__mre_adjusted_enrollment_periods         # Combined adjustments
├── int__mre_adjusted_monthly_enrollments        # Base monthly enrollments
├── int__mre_molina_monthly_services            # Molina service logic
├── int__mre_chg_monthly_services               # CHG service logic
├── int__mre_aah_monthly_services               # AAH service logic
├── int__mre_la_care_monthly_services           # LA Care service logic
├── int__mre_hpsj_monthly_services              # HPSJ service logic
├── int__mre_anthem_monthly_services            # Anthem service logic
├── int__mre_caloptima_monthly_services         # CalOptima service logic
└── int__mre_gold_coast_monthly_services        # Gold Coast service logic

billable_monthly_patient_enrollments (marts layer)
└── Final output combining all payers with service requirements

Usage Notes

Data Refresh: Models are refreshed nightly in production (DBT_PROD schema)
Development: Use DBT_DEV_{username} schema for testing changes
Testing: Run dbt build --select +billable_monthly_patient_enrollments+ to test the full pipeline
Monitoring: Key metrics to watch:
- Service requirement compliance rates by payer
- Month-over-month billable patient counts
- IEHP service gap adjustments
- CCAH authorization date impacts

Key Business Rules Summary

tip

Service requirements vary significantly by payer - some require any contact, others need specific encounter types with duration minimums
CCAH billing starts from authorization date, not enrollment date - can significantly delay billable periods
IEHP patients become unbillable after 90-day service gaps - proactive outreach prevents revenue loss
AAH has tiered contact classifications affecting reimbursement rates
Molina and CCAH exclude "submitted" status from billing

Monthly Billing Windows by Payer​

Enrollment and Submission Window Overlap Logic​

Service Requirements by Payer​

Service-Based Payers​

Molina​

CHG​

AAH​

LA Care​

San Joaquin (HPSJ)​

Anthem​

CalOptima Health​

Gold Coast Health Plan​

Non-Service Payers​

Special Payer Logic​

CCAH (Merced, Monterey, Santa Cruz)​

IEHP​

Filtering Logic​

Output Model:​

Key Fields​

Demographics & Clinical Fields​

Model Architecture​

Usage Notes​

Key Business Rules Summary​