Dashboard

Each quarter your Airbnb market manager sends a workbook. To populate this view, add four tabs to the same Google Sheet the dashboard already reads and paste the relevant columns. Once those tabs exist, the dashboard picks them up automatically.

1. Airbnb Quality — per-listing rating breakdowns + issue tags. Columns: Listing | Overall | Checkin | Accuracy | Cleanliness | Communication | Location | Value | Issue Rate | Trip Issues | Negative Tags
2. Airbnb Sandwich Nights — actionable gap dates. Columns: Listing | # Sandwich Nights | Sandwich Dates | # Blocked Nights | Blocked Dates
3. Airbnb Amenities — recommended adds per listing. Columns: Listing | Recommended Amenities (comma-separated)
4. Airbnb Price — comp benchmarks. Columns: Listing | Cleaning Fee | Comp Cleaning Fee | Weekly Discount | Comp Weekly Discount | Monthly Discount | Comp Monthly Discount | Median Nightly Price | Comp Nightly Comparison

Sections below render whatever tabs exist; missing tabs show a "not yet set up" placeholder.

For each listing and each forward window (Next 30 / 60 / 90 days):

1. OTB (on the books): sum of host revenue for confirmed Hospitable reservations checking in within the window. Stays straddling the window edge contribute only their in-window nights.
2. Expected occupancy: the listing's historical average occupancy over the past 12 months (booked nights ÷ available days).
3. Headroom: max(0, expected_occupancy − current_booked_occupancy). If we're already above our historical average, headroom = 0 (no pickup projected).
4. Pickup revenue: headroom × window_nights × historical ADR.
5. Projected total: OTB + pickup.

Caveats: historical average is unweighted across the past 12 months — it doesn't yet adjust for seasonality (Phase 2). Listings with fewer than 3 months of bookings show "—" for historical fields and project only OTB.

Each established listing is scored on five dimensions; each dimension has a green zone that scores 100 and a soft penalty below. The composite is the weighted average over dimensions where data is available — missing inputs (e.g. no reviews yet) are skipped so a listing isn't penalised for what we don't know about it.

1. Pricing Fit · MPI (weight 25%) — green zone: MPI 0.95–1.20 → 100. Outside: exponential decay (floor 20).
2. Exposure Health · Search→Listing rate (weight 20%) — converted to an approximate market percentile against the Key-tab target band 12–18%. Green zone: percentile ≥ 40 → 100. Below: linear penalty.
3. Conversion Quality · Listing→Booking rate (weight 20%) — green zone: rate ≥ 1.75% (midpoint of the Key-tab 1–2.5% target) → 100. Below: linear penalty to 0.
4. Forward Pacing vs Market (weight 25%) — equal-weighted blend of three subcomponents:
   • S30: Listing forward-30 occupancy ≥ market − 5pp → 100. Below: k = 3.0 per pp.
   • S90: Listing forward-90 occupancy ÷ market forward-90 ≥ 0.90 → 100. Below: k = 1.5 per %.
   • SBW: Recent avg booking lead time within ±20% of 12-month historical avg → 100. Beyond: k = 1.0 per % deviation.
5. Guest Experience (weight 10%) — green zone: lifetime avg ≥ 4.8 AND last-10 avg ≥ 4.7 AND no 1–3★ reviews in last 5 → 100. Otherwise: −50 per 0.1 below threshold, −15 per recent low review.

Letter cutoffs: A ≥ 85 · B ≥ 75 · C ≥ 65 · D ≥ 55 · F < 55.

Methodology authored by Jeff (RM). Caveats: Exposure + Conversion compare against Key-tab static targets (no live market-median feed); SBW uses last-30-day forward bookings as "actual" vs 12-month avg as "expected"; Guest Experience uses Hospitable's review feed.

Reads the VRBO weekly tracking tab from a Google Sheet (1WFy7pMgL7C9izs100BIN2-bN4A3t9xOfMHagPqNXc_Q). Each row is one listing × one weekly snapshot; each snapshot is a trailing 30-day window, so we never sum across snapshots — the latest snapshot per listing is the "current" reading.

• Portfolio summary uses the latest snapshot per listing only. Totals for impressions, views, and bookings. Flag distribution across listings.
• Per-listing cards show the latest VRBO snapshot side-by-side with the latest Airbnb week for the same listing (joined by nickname). Same funnel structure — search-to-view, view-to-booking, overall conversion — with target bands.
• Sparklines show bookings across the last snapshots so you can see whether the listing is trending up or down on VRBO.

Target bands are industry defaults; can be overridden with rows on the Key tab of the metrics sheet.

Rachel (your Airbnb market manager) sends a quarterly report with specific per-listing opportunities. This tab surfaces the two categories the rest of the dashboard doesn't already cover:

• Promotions: Weekly Discount, Monthly Discount, Early Bird, Cleaning Fee, Last Minute Discount. For each, we compare your current setting to the median of your comp set — a listing is flagged when yours is meaningfully below (or, for cleaning fee, above) comps.
• Cancellation Policies: your current policy vs. Rachel's recommended more-flexible alternative. Flexible policies typically lift booking conversion.

Data lives in the Google Sheet in two tabs: Airbnb Promotions and Airbnb Cancellation — copy Rachel's quarterly xlsx tabs into them and this view refreshes.

Source of truth is your Google Sheet — Buildings, Units, Assumptions, Scenarios. This tab reads it live, computes today's amortized mortgage balance for each loan, and derives the numbers below.

• Equity = Zestimate − current mortgage balance
• Cap rate = annual NOI ÷ Zestimate
• Cash-on-cash = annual cash flow ÷ equity (the "return on trapped money")
• NOI uses gross rent minus opex (tax + insurance + HOA + other) minus vacancy allowance, maintenance reserve, and property-management fee from the Assumptions tab
• Trapped equity flag — properties with equity > $50k earning less than 5% cash-on-cash — strong 1031 candidates

Phase 1 shows the read-only analytical layer. Zillow scrape, market-research auto-fill, 1031 modeler, and AI chat pane come in later phases.