VRBO-Power-BI-Project

VRBO using Power BI

Comment

🏨 Vrbo Property Yield & Cancellation Intelligence Suite

An end-to-end Power BI business intelligence and analytics project transforming raw, unstructured hospitality booking records into an executive diagnostic and yield optimization dashboard.

📌 Executive Summary

In short-term rental property management, top-line booking demand can be deceptive. Operational profitability depends directly on realized cash flow, active yield pricing, and mitigating cancellation risk.

This project analyzes 19,243 validated bookings across New York, Los Angeles, and San Francisco. By establishing a rigorous data hygiene pipeline and dynamic DAX modeling, this dashboard bridges the gap between gross booking demand ($1.026B) and net realized revenue ($509.7M), uncovering a critical 50.4% cancellation rate and isolating high-risk booking profiles.

🏗️ Architecture & Data Pipeline


Raw CSV (22,000 Rows with Intentional Flaws)
│
▼
Power Query ETL Pipeline (Deduplication, Format Standardization, Custom Duration Logic)
│
▼
Star Schema Data Model + Automated DAX Calendar Dimension
│
▼
Measures & Analytics Engine (Organized DAX Measure Table)
│
▼
3-Page Executive Reporting Suite (with Custom Micro-Tooltip Engine)

🛠️ Data Cleaning & ETL (Power Query)

The raw dataset contained severe data hygiene issues simulating real-world pipeline failures. The following sequential ETL pipeline was implemented:

  1. Deduplication: Identified and eliminated 2,000 duplicate transactions based on booking_id.
  2. Text Standardization & Trimming:
    • Stripped hidden leading/trailing whitespaces and applied proper case formatting on guest_name.
    • Standardized fragmented city strings ("nyc", "Nyc", "LA", "San Fran", "Sf") into normalized market names (New York, Los Angeles, San Francisco).
  3. Date Harmonization: Parsed mixed-locale and unstructured date strings (YYYY-MM-DD, DD/MM/YYYY, 18 February 2025) into standardized ISO Date types using explicit locale handling.
  4. Duration & Stay Validation:
    • Recomputed stay length dynamically: Duration.Days([checkout_date] - [checkin_date]).
    • Filtered out corrupted records with negative or zero-duration stays where check-out occurred before check-in.
  5. Financial Cleansing:
    • Converted textual values ("free") to 0.00 and enforced Fixed Decimal Currency data types.
    • Reconstructed 1,300+ corrupted total_price entries containing text errors by computing [nights] * [price_per_night].
  6. Boolean & Review Normalization:
    • Harmonized 10 distinct cancellation representations ('Yes', 'y', '1', 'True', 'TRUE', 'No', 'n', '0', 'False', 'FALSE') into a uniform boolean True/False logical column.
    • Filtered out-of-bounds review scores (-1.0 and 6.0) to constrain ratings strictly to a 1.0–5.0 scale.

📐 Data Modeling & DAX Engineering

Data Model Architecture

  • Fact Table: verbo_data (Cleaned transactional records, stay duration, financial metrics, cancellation status).
  • Dimension Table: Dim_Date (Generated dynamically via DAX CALENDAR with Year, Quarter, Month, and Day-of-Week attributes).
  • Relationship: $1 \rightarrow *$ active single-direction relationship between Dim_Date[Date] and verbo_data[checkin_date].

┌───────────────────────────┐                ┌───────────────────────────┐
│         Dim_Date          │                │        verbo_data         │
├───────────────────────────┤                ├───────────────────────────┤
│ Date (PK)                 │ 1            * │ booking_id (PK)           │
│ Year                      ├───────────────►│ checkin_date (FK)         │
│ Year-Month                │                │ checkout_date             │
│ Month                     │                │ listing_city              │
│ Quarter                   │                │ price_per_night           │
│ Day of Week               │                │ total_price               │
└───────────────────────────┘                │ is_cancelled              │
│ payment_status            │
└───────────────────────────┘

Core DAX Measures (_Key Measures)

// 1. Gross Potential Bookings Value
Gross Booked Value = SUM(verbo_data[total_price])

// 2. Net Realized Earnings (Excluding Cancellations)
Realized Revenue = 
CALCULATE(
    [Gross Booked Value],
    verbo_data[is_cancelled] = FALSE
)

// 3. Lost Revenue to Cancellations
Lost Revenue = 
CALCULATE(
    [Gross Booked Value],
    verbo_data[is_cancelled] = TRUE
)

// 4. Average Daily Rate (ADR)
Average Daily Rate = AVERAGE(verbo_data[price_per_night])

// 5. Total Completed Stays
Completed Bookings = 
CALCULATE(
    COUNTROWS(verbo_data),
    verbo_data[is_cancelled] = FALSE
)

// 6. Cancellation Rate %
Cancellation Rate % = 
DIVIDE(
    CALCULATE(COUNTROWS(verbo_data), verbo_data[is_cancelled] = TRUE),
    COUNTROWS(verbo_data),
    0
)

// 7. Average Length of Stay (ALOS)
Avg Length of Stay = AVERAGE(verbo_data[nights])

// 8. Guest Satisfaction Rating
Avg Review Score = AVERAGE(verbo_data[review_score])

📊 Dashboard Visual Suite

Page 1: Executive Overview & Market Velocity

  • Macro KPI Deck: Realized Revenue ($509.66M), ADR ($271.76), Completed Bookings (9.54K), and Overall Cancellation Rate (50.4%).
  • Revenue Trend: Dual-axis visualization contrasting Realized Revenue against Lost Revenue across monthly booking horizons.
  • City Market Share: Revenue breakdown comparing New York ($414.5M gross), Los Angeles ($305.5M gross), and San Francisco ($306.3M gross).
  • Capital Realization: Donut chart evaluating Paid vs Pending vs Unpaid cash status.

Page 2: Yield Management & Property Deep Dive

  • Top 10 Listings: Ranked by total realized revenue generation using Top-N visual filters.
  • Price Elasticity Analysis: Scatter plot correlating nightly pricing against length of stay across city tiers.
  • Property Performance Matrix: Multi-level hierarchy (listing_city $\rightarrow$ listing_id) equipped with conditional background color gradients on [Realized Revenue] and dynamic star-rating icons for [Avg Review Score].
  • Custom Tooltip Canvas: Dedicated hover tooltip (Tooltip_Listing) displaying micro-KPIs and monthly performance trends for individual listings.

Page 3: Cancellation & Risk Diagnostics

  • Gross-to-Net Waterfall Chart: Bridges total booked value down to realized revenue by isolating cancellation deductions.
  • AI Decomposition Tree: Root-cause analysis tracing cancellation revenue leakage through city tiers, payment statuses, and stay duration buckets.
  • High-Exposure Risk Watchlist: Filterable operational table highlighting high-value pending/unpaid bookings requiring immediate deposit follow-up.

💡 Key Business Insights

  1. Severe Revenue Leakage: Across all three core markets, 50.4% of total booked revenue ($516.85M) failed to convert to realized revenue due to cancellations.
  2. Payment Collection Gap: Over 57% of total booking value sat in Pending or Unpaid status at check-in horizon, which directly correlates with higher drop-off and cancellation rates.
  3. Market Pricing Inelasticity: Average Daily Rates remained nearly identical across NYC ($271.53), LA ($272.53), and SF ($271.31), indicating an opportunity to implement dynamic seasonal and weekend surge pricing models.

📂 Repository Contents

  • VRBO_dashboard.pbix — Complete interactive Power BI Desktop report and data model.

🔗 Credits & Data Source

Built With

  • powerbi
Share this project:

Updates