The Problem

New residents submit work orders within days of moving in. A resident who moves in on Monday and submits a maintenance request on Wednesday is telling you the unit wasn't actually ready.

This creates three immediate problems:

Operational cost: You pay for make-ready once during turn, then pay again to fix what was missed. Labor and materials spent twice on the same unit.

Resident satisfaction: First impression matters. A resident who submits 2-3 work orders in their first week starts the lease frustrated. This shows up in online reviews and renewal decisions 11 months later.

Portfolio blindness: Without measurement, you can't distinguish between properties with tight make-ready processes and those rushing turns to hit occupancy targets.

Most companies track work orders but don't segment by move-in date. The signal gets buried in aggregate maintenance data.

The Measurement Framework

We track three metrics:

1. Work Order Incidence Rate: Percentage of new move-ins who submit at least one work order within 10 days. High percentage indicates make-ready quality problems.

2. Early Intensity: For residents who submit work orders, how many in the first 10 days? One minor issue is fixable. Three or four means the unit had multiple failures.

3. Total Intensity: Same residents, tracked over 90 days. Did the early problems stop, or do these units continue generating work orders?

The 10-day window captures make-ready failures specifically. Items discovered through actual use (running the dishwasher, using the shower, operating the HVAC under load).

We use trailing 3-month periods (T3) for analysis. This provides adequate sample size and smooths weekly variance.

Building the Dataset

Two PMS reports don't natively connect. The move-in report shows who moved in and when. The work order report shows maintenance requests by unit. Neither tells you if a work order came from a new move-in.

Load source data and handle dates:

import pandas as pd
from datetime import datetime
from pathlib import Path

# Read source files
lease_data = pd.read_excel(lease_data_file)
work_orders = pd.read_excel(work_order_file)

# Convert date columns - handle mixed timezones
lease_data['Move-In'] = pd.to_datetime(
    lease_data['Move-In'],
    errors='coerce',
    utc=True
).dt.tz_localize(None)

work_orders['Created'] = pd.to_datetime(
    work_orders['Created'],
    errors='coerce',
    utc=True
).dt.tz_localize(None)

work_orders['Closed'] = pd.to_datetime(
    work_orders['Closed'],
    errors='coerce',
    utc=True
).dt.tz_localize(None)

Create merge keys and identify matches:

# Create merge key for joining property + unit
lease_data['merge_key'] = (
    lease_data['Property Name'] + '|' + lease_data['Bldg-Unit'].astype(str)
)
work_orders['merge_key'] = (
    work_orders['Property'] + '|' + work_orders['BLDG-Unit'].astype(str)
)

# For each lease, check if any WO exists within 10 days
matches = []
matched_residents = []

for idx, lease_row in lease_data.iterrows():
    if pd.isna(lease_row['Move-In']):
        matches.append("No Match")
        continue

    # Filter WOs for this unit
    unit_wos = work_orders[work_orders['merge_key'] == lease_row['merge_key']]

    # Check if any are within 10 days
    match_found = any(
        (unit_wos['Created'] >= lease_row['Move-In']) &
        (unit_wos['Created'] <= lease_row['Move-In'] + pd.Timedelta(days=10))
    )

    matches.append("Match Found" if match_found else "No Match")
    if match_found:
        matched_residents.append(idx)

lease_data['Match Status'] = matches

Calculating Metrics

Property-level summary:

# Create Summary data by property
summary_by_property = []

for property_name in lease_data['Property Name'].unique():
    property_leases = lease_data[lease_data['Property Name'] == property_name]
    total_move_ins = len(property_leases)
    move_ins_with_wo = len(
        property_leases[property_leases['Match Status'] == 'Match Found']
    )
    percentage = (move_ins_with_wo / total_move_ins) if total_move_ins > 0 else 0

    summary_by_property.append({
        'Property': property_name,
        'Move Ins': total_move_ins,
        'MIs Submitting W/O within 10 Days': move_ins_with_wo,
        '%': percentage
    })

# Add Portfolio total
total_move_ins = len(lease_data)
total_with_wo = len(lease_data[lease_data['Match Status'] == 'Match Found'])
portfolio_pct = (total_with_wo / total_move_ins) if total_move_ins > 0 else 0

summary_by_property.append({
    'Property': 'Portfolio',
    'Move Ins': total_move_ins,
    'MIs Submitting W/O within 10 Days': total_with_wo,
    '%': portfolio_pct
})

summary_df = pd.DataFrame(summary_by_property)

Calculate intensity metrics:

# Calculate intensity for each property
stats_data = []

for property_name in lease_data['Property Name'].unique():
    property_leases = lease_data[lease_data['Property Name'] == property_name]

    wo_count_10_days = 0
    wo_count_3_months = 0
    move_ins_with_wo_count = 0

    for idx, lease_row in property_leases.iterrows():
        if pd.isna(lease_row['Move-In']):
            continue

        merge_key = lease_row['merge_key']
        unit_wos = work_orders[work_orders['merge_key'] == merge_key]

        # Count WOs within 10 days
        wos_10 = unit_wos[
            (unit_wos['Created'] >= lease_row['Move-In']) &
            (unit_wos['Created'] <= lease_row['Move-In'] + pd.Timedelta(days=10))
        ]

        # ONLY proceed if this resident had WOs within 10 days
        if len(wos_10) > 0:
            move_ins_with_wo_count += 1
            wo_count_10_days += len(wos_10)

            # Now count ALL their WOs within 3 months (90 days)
            wos_3m = unit_wos[
                (unit_wos['Created'] >= lease_row['Move-In']) &
                (unit_wos['Created'] <= lease_row['Move-In'] + pd.Timedelta(days=90))
            ]
            wo_count_3_months += len(wos_3m)

    early_intensity = (
        wo_count_10_days / move_ins_with_wo_count
        if move_ins_with_wo_count > 0 else 0
    )
    total_intensity = (
        wo_count_3_months / move_ins_with_wo_count
        if move_ins_with_wo_count > 0 else 0
    )

    stats_data.append({
        'Property': property_name,
        'Sum (First 10 days)': wo_count_10_days,
        'Sum (Full 3 months)': wo_count_3_months,
        'Move Ins with Work Order within 10 Days': move_ins_with_wo_count,
        'Early Intensity': round(early_intensity, 1),
        'Total Intensity': round(total_intensity, 1)
    })

stats_df = pd.DataFrame(stats_data)

The Dashboard Architecture

Create executive dashboard with styling:

import openpyxl
from openpyxl.styles import PatternFill, Font, Alignment, Border, Side

# Styles
header_fill = PatternFill(start_color='002060', end_color='002060', fill_type='solid')
header_font = Font(color='FFFFFF', bold=True, size=12)
kpi_font = Font(size=32, bold=True, color='002060')
center_align = Alignment(horizontal='center', vertical='center')

# Red/Yellow/Green fills for thresholds
red_fill = PatternFill(start_color='FF6B6B', end_color='FF6B6B', fill_type='solid')
yellow_fill = PatternFill(start_color='FFFF00', end_color='FFFF00', fill_type='solid')
green_fill = PatternFill(start_color='92D050', end_color='92D050', fill_type='solid')

# Create workbook
with pd.ExcelWriter(output_file, engine='openpyxl') as writer:
    # Write data sheets
    work_orders.to_excel(writer, sheet_name='WO_T3', index=False)
    lease_data.to_excel(writer, sheet_name='LeaseData_T3', index=False)
    summary_df.to_excel(writer, sheet_name='Summary', index=False)
    stats_df.to_excel(writer, sheet_name='Stats', index=False)

Build dashboard headers and KPIs:

wb = openpyxl.load_workbook(output_file)
ws_dash = wb['Executive Dashboard']

# Title
ws_dash['A1'] = 'PERFECT MOVE-IN DASHBOARD'
ws_dash['A1'].font = Font(bold=True, size=18, color='002060')
ws_dash.merge_cells('A1:L1')

# Portfolio KPI
kpi_row = 4
ws_dash[f'A{kpi_row}'] = 'Portfolio Performance'
ws_dash[f'B{kpi_row}'] = portfolio_pct
ws_dash[f'B{kpi_row}'].number_format = '0%'
ws_dash[f'B{kpi_row}'].font = kpi_font
ws_dash[f'B{kpi_row}'].alignment = center_align

# Color code the KPI
if portfolio_pct > 0.50:
    ws_dash[f'B{kpi_row}'].fill = red_fill
elif portfolio_pct >= 0.30:
    ws_dash[f'B{kpi_row}'].fill = yellow_fill
else:
    ws_dash[f'B{kpi_row}'].fill = green_fill

Property table with intensity highlighting:

# Calculate 90th percentile for intensity highlighting
property_early_intensities = [
    v['early'] for k, v in intensity_by_property.items()
    if k != 'Portfolio'
]
property_total_intensities = [
    v['total'] for k, v in intensity_by_property.items()
    if k != 'Portfolio'
]

early_threshold = pd.Series(property_early_intensities).quantile(0.90)
total_threshold = pd.Series(property_total_intensities).quantile(0.90)

# Build property rows
for _, row in summary_df.iterrows():
    property_name = row['Property']
    current_pct = row['%']
    early_int = intensity_by_property.get(property_name, {}).get('early', 0)
    total_int = intensity_by_property.get(property_name, {}).get('total', 0)

    # Color code percentage
    if current_pct > 0.50:
        cell.fill = red_fill
    elif current_pct >= 0.30:
        cell.fill = yellow_fill
    else:
        cell.fill = green_fill

    # Highlight high intensity properties
    if early_int >= early_threshold:
        early_cell.fill = light_red_fill
    if total_int >= total_threshold:
        total_cell.fill = light_red_fill

Historical Tracking

Append current month to historical file:

# Auto-detect current month from latest move-in date
max_move_in = lease_data['Move-In'].max()
current_month_date = pd.Timestamp(year=max_move_in.year, month=max_move_in.month, day=1)
current_month_label = current_month_date.strftime('%b%Y')  # e.g., "Dec2025"

# Prepare data for historical append
historical_append_df = summary_df[[
    'Property',
    'Move Ins',
    'MIs Submitting W/O within 10 Days',
    '%'
]].copy()

# Append to historical file
if historical_file.exists():
    with pd.ExcelWriter(
        historical_file,
        engine='openpyxl',
        mode='a',
        if_sheet_exists='replace'
    ) as writer:
        historical_append_df.to_excel(
            writer,
            sheet_name=current_month_label,
            index=False
        )
else:
    with pd.ExcelWriter(historical_file, engine='openpyxl') as writer:
        historical_append_df.to_excel(
            writer,
            sheet_name=current_month_label,
            index=False
        )

Trend Visualization

Create sparklines showing 6-month trends:

import matplotlib.pyplot as plt
from openpyxl.drawing.image import Image as XLImage
from io import BytesIO
import numpy as np

def create_sparkline(values, width=2.5, height=0.35):
    """Create a mini sparkline chart"""
    fig, ax = plt.subplots(figsize=(width, height))

    # Filter out None values
    valid_indices = [i for i, v in enumerate(values) if v is not None]
    valid_values = [v * 100 for v in values if v is not None]

    if len(valid_values) < 2:
        plt.close()
        return None

    # Plot line with markers
    ax.plot(
        valid_indices,
        valid_values,
        color='#4472C4',
        linewidth=2.5,
        marker='o',
        markersize=4
    )
    ax.fill_between(valid_indices, valid_values, alpha=0.15, color='#4472C4')

    # Add horizontal reference line at mean
    mean_val = np.mean(valid_values)
    ax.axhline(y=mean_val, color='gray', linestyle='--', linewidth=0.8, alpha=0.5)

    ax.axis('off')
    plt.tight_layout(pad=0)

    # Save to BytesIO
    img_buffer = BytesIO()
    plt.savefig(
        img_buffer,
        format='png',
        dpi=120,
        bbox_inches='tight',
        transparent=True
    )
    img_buffer.seek(0)
    plt.close()

    return img_buffer

# Add sparklines to dashboard
for property_name in historical_data:
    if display_months:
        trend_values = []
        for month_date, month_label in reversed(display_months):
            if month_label in historical_data[property_name]:
                trend_values.append(historical_data[property_name][month_label])
            else:
                trend_values.append(None)

        sparkline_img = create_sparkline(trend_values)
        if sparkline_img:
            img = XLImage(sparkline_img)
            img.width = 140
            img.height = 25
            ws_dash.add_image(img, f'K{data_row}')

Interpreting the Metrics

The two metrics interact to reveal different operational problems:

High incidence rate + low early intensity:

Many residents submit work orders, but each resident submits only one. Issues are isolated and distributed across the resident base. Checklist items are being missed consistently but not catastrophically. The make-ready process is functioning but quality control has gaps. Training compliance or final inspection rigor are the likely causes.

Low incidence rate + high early intensity:

Most move-ins are clean. The minority that generate work orders generate several each. Failures are concentrated rather than distributed. Specific unit types, floor plans, or turn vendors may be the source. Age of equipment in certain units or contractor performance for specific scopes of work merit investigation. The process works for most units but fails badly for a subset.

High incidence rate + high early intensity:

Widespread quality failures. Many residents submit multiple work orders. Turn timelines may be compressed to meet occupancy targets, or make-ready standards have degraded systematically. A baseline incidence rate of 5-10% is normal for any property. When incidence rates exceed 25% and affected residents average 2+ work orders each, the entire make-ready process requires audit. Vendor changes, timeline extensions, or staffing adjustments become necessary.

Low incidence rate + low early intensity:

Operationally excellent. Few residents submit work orders and those who do submit one. This represents effective quality control and adequate turn timelines.

Strategic Implications

Category concentration: When a single category represents over 40% of early work orders at a property, equipment replacement becomes the priority rather than process refinement. HVAC-heavy properties need equipment replacement. Appliance-heavy properties need proactive replacement during turns rather than reactive fixes post-occupancy. Cosmetic-heavy properties have inspection failures.

Intensity correlation: Early intensity and total intensity should correlate. If residents submit multiple work orders in the first 10 days and continue through 90 days, the unit had serious quality issues at move-in. If early intensity is high but total intensity drops, the initial problems were fixed and the resident stabilized. Divergence between the two metrics reveals whether early problems are symptomatic of deeper issues or isolated failures.

Vendor performance: Work order incidence rates that spike after vendor changes or remain elevated with specific contractors provide quantifiable performance data for contract decisions.

Operational Applications

Triage and resource allocation: Properties with incidence rates above 25% receive immediate attention. Below 10% indicates operational excellence. The 10-25% range requires evaluation of intensity metrics to determine urgency.

Capital planning: Category concentration data drives capex budgeting. When equipment-related work orders dominate, replacement schedules can be accelerated for specific properties or unit types before turns rather than waiting for resident-reported failures.

Vendor management: Performance degradation becomes measurable. Incidence rate changes after vendor transitions provide quantifiable justification for contract review or replacement.

Retention strategy: Properties consistently above 20% work order incidence show measurably lower renewal rates. This allows targeting of retention efforts and pricing strategies months before lease expiration.

The system quantifies variance across properties, exposes magnitude of quality issues, and provides drill-down capability to investigate root causes. Operational decisions still require judgment, now informed by systematic measurement rather than reactive responses to complaints.