Fast Host Listings Metrics Page (Airbnb)

0) Problem Restatement

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.

1) Requirements

• 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.

2) Key Idea: Pre-Aggregate by Day

Computing from raw events on every page load (billions of rows) is far too slow. Instead, keep a daily summary row per listing:

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

• A date-range query for one listing = sum of at most ~365–730 small rows, read contiguously thanks to the primary key order (listing_id, date).
• Occupancy = sum(nights_booked) / sum(available_nights). Compute ratios after summing, never average daily ratios.
• For long ranges, also keep monthly rollups: a 2-year range = 24 monthly rows + a few daily rows at the edges.

3) Architecture

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).

4) Serving 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.

5) Correctness

• 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).

6) Wrap-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.