Fast Host Listings Metrics Page (Airbnb)

0Problem RestatementProblem

Airbnb asked: design the backend for a host's listings page. A host opens a page showing their listings, selects a date range, and for each listing sees aggregated metrics for that range, like views, booking requests, bookings, nights booked, occupancy rate and revenue. The page must load fast, even for professional hosts with hundreds or thousands of listings.

1RequirementsRequirements

• Metrics per listing for any date range (up to ~1–2 years back), plus totals across all listings.
• Page load under ~500 ms, with pagination and sorting by metric.
• Data freshness: views can lag a little (minutes to an hour), and bookings and revenue should be accurate.

1.1 Scale

• 7M listings, 4M hosts. Raw events: billions of views per day.

2Deep Dive — Metrics for an arbitrary date rangeDeep dive

A host picks "last 90 days" and expects views, bookings, nights and occupancy for every one of their listings, immediately.

%%{init: {"look":"handDrawn","handDrawnSeed":7,"theme":"base","fontFamily":"Virgil, \"Segoe Print\", \"Comic Sans MS\", cursive","themeVariables":{"fontFamily":"Virgil, \"Segoe Print\", \"Comic Sans MS\", cursive","fontSize":"16px","primaryColor":"#fff4e6","primaryBorderColor":"#1e1e1e","primaryTextColor":"#1e1e1e","secondaryColor":"#e7f5ff","tertiaryColor":"#ebfbee","lineColor":"#1e1e1e","textColor":"#1e1e1e","mainBkg":"#fff4e6","nodeBorder":"#1e1e1e","clusterBkg":"#f8f9fa","edgeLabelBackground":"#ffffff","classText":"#1e1e1e"}}}%%
flowchart LR
  PAGE["Host opens the page"] --> SCAN["Scan billions of view and booking events"]
  SCAN --> FILTER["Filter by listing_id and date range"]
  FILTER --> AGG["Group and aggregate"]
  AGG --> SLOW["Seconds to minutes, for every host, on every page load"]

listing_daily_metrics(listing_id, date, views, booking_requests, bookings,
                      nights_booked, available_nights, revenue_cents)
PRIMARY KEY (listing_id, date)

%%{init: {"look":"handDrawn","handDrawnSeed":7,"theme":"base","fontFamily":"Virgil, \"Segoe Print\", \"Comic Sans MS\", cursive","themeVariables":{"fontFamily":"Virgil, \"Segoe Print\", \"Comic Sans MS\", cursive","fontSize":"16px","primaryColor":"#fff4e6","primaryBorderColor":"#1e1e1e","primaryTextColor":"#1e1e1e","secondaryColor":"#e7f5ff","tertiaryColor":"#ebfbee","lineColor":"#1e1e1e","textColor":"#1e1e1e","mainBkg":"#fff4e6","nodeBorder":"#1e1e1e","clusterBkg":"#f8f9fa","edgeLabelBackground":"#ffffff","classText":"#1e1e1e"}}}%%
flowchart LR
  EV["Raw events"] --> D["listing_daily_metrics - one row per listing per day"]
  D --> M["listing_monthly_metrics - one row per listing per month"]
  Q["Range query"] --> SPLIT["Whole months from monthly, edges from daily"]
  M --> SPLIT
  D --> SPLIT
  SPLIT --> SUM["Sum numerators and denominators separately"]
  SUM --> RATIO["occupancy = sum(nights_booked) / sum(available_nights)"]
  RATIO --> WRONG["NOT the average of daily occupancy rates"]

3ArchitectureArchitecture

%%{init: {"look":"handDrawn","handDrawnSeed":7,"theme":"base","fontFamily":"Virgil, \"Segoe Print\", \"Comic Sans MS\", cursive","themeVariables":{"fontFamily":"Virgil, \"Segoe Print\", \"Comic Sans MS\", cursive","fontSize":"16px","primaryColor":"#fff4e6","primaryBorderColor":"#1e1e1e","primaryTextColor":"#1e1e1e","secondaryColor":"#e7f5ff","tertiaryColor":"#ebfbee","lineColor":"#1e1e1e","textColor":"#1e1e1e","mainBkg":"#fff4e6","nodeBorder":"#1e1e1e","clusterBkg":"#f8f9fa","edgeLabelBackground":"#ffffff","classText":"#1e1e1e"}}}%%
flowchart LR
    EV["View events"] --> K[("Kafka")]
    BK["Bookings DB"] -->|"CDC"| K
    K --> STR["Stream job - today's counters"]
    K --> LAKE[("Data lake")]
    LAKE --> BATCH["Daily batch - exact daily rows + monthly rollups"]
    BATCH --> MS[("Metrics store - by (listing, date)")]
    STR --> MS
    UI["Host page"] --> API["Metrics API - batch per page"]
    API --> HL[("Host → listings index")]
    API --> MS
    API --> C[("Cache")]

• Batch job (daily): computes exact daily metrics from bookings (source of truth) and deduplicated views, and writes daily and monthly rows.
• Streaming job: keeps today's numbers fresh (views, new bookings) until the batch finalizes them.
• Metrics store: a DB good at range scans by key (Cassandra/HBase, or Postgres partitioned by listing, or an OLAP store like Druid/ClickHouse).

4Serving the Page

1. Get the host's listing IDs (a host → listings index), paginated (e.g., 50 per page).
2. One batched query for all 50 listings and the date range (not 50 separate calls). Each listing's rows are contiguous, so this is 50 short range scans done in parallel.
3. Sum per listing, compute ratios, and return rows plus the page total.
4. Sorting by a metric across 1,000 listings (e.g., "sort by revenue this month"): compute summaries for all the host's listings for that range (1,000 × ~30 rows is fine), sort, and cache the result for the session.
5. Cache results keyed by (host, range, page) for a few minutes. Common ranges (last 30 days, this month) can be precomputed per host overnight.

5Correctness

• Bookings, cancellations and revenue come from the bookings DB via CDC, so they match what the host sees elsewhere. Cancellations subtract from the day they affect.
• Time zones: aggregate by the listing's local date.
• Backfills: if logic changes, recompute daily rows from the data lake and overwrite them (idempotent by primary key).

6Wrap-UpWrap-up

Pre-aggregate metrics into daily (and monthly) rows keyed by (listing_id, date), built exactly by a daily batch job from bookings and deduplicated views, with a streaming job keeping today fresh. Serve the page by fetching the host's listings page by page and running one batched range query, summing rows and computing ratios after summing, and cache common ranges and sorted results so even hosts with thousands of listings get a fast page.