Apartment Quant
Back to projects

Renewal Likelihood Scoring: Combining Behavioral Data Into Risk-Adjusted Term Optimization

The Problem

Renewal management runs manually. Properties track expirations in spreadsheets and offer terms based on tenure or property manager judgment. There's no systematic way to identify which residents are likely to renew versus which require retention intervention.

Standard renewal tracking shows acceptance rates by term length and property. It doesn't connect resident behavior to renewal decisions. Properties can't segment residents by likelihood to accept before making offers.

The Measurement Framework

Renewal likelihood scoring combines behavioral data across resident touchpoints: payment history, maintenance requests, lease violations, review sentiment, and tenure. The model identifies which residents may benefit from proactive retention support.

Behavioral inputs:

The model aggregates these signals into three likelihood bands: "Likely to Renew" (high stability, low risk), "Possibly Will Renew" (moderate indicators), "Unlikely / High Risk" (multiple negative signals). These bands guide internal resource allocation and retention timing.

Renewal Term Optimization

The term optimization model uses historical data on renewal expirations, term offers, and outcomes. The primary focus is managing lease expiration timing across the portfolio to avoid concentration in peak leasing months and maintain stable occupancy.

Properties track how different term lengths affect expiration distribution. Offering varied terms (3, 6, 9, 12, 15 months) spreads expirations throughout the year rather than clustering renewals in a few months. This smooths leasing workload and reduces vacancy risk from seasonal turnover spikes.

The model analyzes which months show expiration concentration and identifies opportunities to shift renewals. If 40% of leases expire in June-August, the model highlights which residents renewing in those months could be offered terms that push expirations to lower-volume months. This prevents operational bottlenecks during peak leasing season.

The Code: Behavioral Aggregation

The likelihood model starts with rent roll as the base dataset. Every row in the rent roll remains in the output with behavioral metrics appended.

Standardizing Resident Names

Resident names appear inconsistently across systems: "Smith, John" in rent roll, "John Smith" in work orders, "J. Smith" in late notices. The model normalizes all names to "Last, First" format for matching:

def std_key(name: str) -> str:
    s = str(name).split("&")[0].split("/")[0].strip().lower()
    if "," in s:
        parts = [p.strip() for p in s.split(",") if p.strip()]
        return f"{parts[0]}, {parts[1].split()[0]}" if len(parts) >= 2 else parts[0]
    tokens = re.findall(r"\w+", s)
    return f"{tokens[-1]}, {tokens[0]}" if len(tokens) >= 2 else s

This handles roommates (splits on "&"), co-signers (splits on "/"), and reverses first-last to last-first format. Property names also get normalized to lowercase for consistent matching across sheets.

Aggregating Behavioral Metrics

Each data source (work orders, late notices, violations, reviews) gets grouped by standardized resident name and property, then counted or averaged:

wo_agg = (work_orders_df.groupby(["__std_name", "__std_prop"], as_index=False)
                      .size().rename(columns={"size": "Work Orders"}))

late_agg = (late_notices_df.groupby(["__std_name", "__std_prop"], as_index=False)[late_cnt_hdr]
                        .sum().rename(columns={late_cnt_hdr: "Late Payments"}))

viol_agg = (violations_df.groupby(["__std_name", "__std_prop"], as_index=False)
                         .size().rename(columns={"size": "Violations"}))

rev_agg = (reviews_df.groupby(["__std_name", "__std_prop"], as_index=False)["STAR RATING"]
                      .mean().rename(columns={"STAR RATING": "Average STAR RATING"}))

These aggregates merge back onto the rent roll via left join. Residents without behavioral records (no work orders, no violations) get zero counts. Star ratings remain NaN if no reviews exist.

Likelihood Scoring

The model assigns weights to each behavioral dimension. Lower late payments, fewer work orders, longer tenure, and higher star ratings increase likelihood scores:

def w_late(x):  return 3 if x < 1 else 2 if x <= 2 else 1
def w_work(x):  return 3 if x < 3 else 2 if x <= 5 else 1
def w_years(x): return 3 if x > 2 else 2
def w_viol(x):  return 3 if x == 0 else 2 if x <= 2 else 1
def w_rev(x):
    if x == "N/A": return 2
    r = float(x)
    return 3 if 4 <= r <= 5 else 2 if r == 3 else 1

def likelihood(r):
    s = (w_rev(r["Average STAR RATING"]) + w_late(r["Late Payments"])
         + w_work(r["Work Orders"])      + w_years(r["Years as Resident"])
         + w_viol(r["Violations"]))
    return ("Likely to Renew" if s >= 13 else
            "Possibly Will Renew" if s >= 11 else
            "Unlikely / High Risk")

Each behavioral factor receives a weight. The weights sum to a total score, which maps to likelihood bands. The thresholds calibrate based on portfolio-specific renewal patterns.

The Term Optimization Model

The term model runs in Excel using pivot tables as the calculation engine. Source data contains renewal expirations, term offers, and outcomes (accepted, declined, rent changes). A pivot table aggregates by Property × Month × Term, calculating acceptance rates and average rent trade-out per term length.

GETPIVOTDATA as Stable Interface

The model never reads the pivot table visually. Instead, it uses GETPIVOTDATA formulas to pull specific metrics. This creates a stable interface: as new months get added to source data, the pivot refreshes, and GETPIVOTDATA formulas automatically reference the updated aggregates.

Helper sheet resolves dynamic month/year headers. Model sheet pulls metrics via GETPIVOTDATA without manual column updates. The pivot behaves like a structured dataset that updates automatically.

Constraints and Segmentation

The model respects operational constraints:

Likelihood bands from the scoring model help segment residents for operational planning. The model identifies where properties should invest retention resources: residents showing risk indicators receive proactive maintenance follow-ups, early renewal conversations, or community engagement.

Interpreting the Output

Likelihood distribution: Properties with high concentration of "Likely to Renew" residents show portfolio stability. Properties with high "Unlikely / High Risk" concentration face retention challenges requiring intervention.

Behavioral correlations: The model tracks five behavioral factors and their correlation to renewal outcomes:

Expiration management: The model tracks lease expiration timing across the portfolio. Properties identify expiration concentration (multiple leases ending in the same month) and use varied term lengths to smooth renewal timing. This prevents operational bottlenecks during peak leasing season and reduces vacancy risk from seasonal turnover spikes.

Operational Impact

Proactive retention timing: The model identifies residents who may benefit from early engagement. Properties reach out earlier to residents showing risk indicators, offering maintenance follow-ups, community events, or early renewal discussions.

Model validation: Properties track actual renewals against predicted likelihood bands. This measures scoring accuracy and enables threshold calibration over time.

Transition path: The likelihood scoring framework provides a baseline for later predictive models. Properties can refine weighting algorithms over time as more historical renewal outcomes become available.

Get in Touch