Airbnb-Specific Data Patterns Questions

Domain-specific data modeling and analytics patterns used in Airbnb-scale product analytics. Covers data schema design, event and transaction patterns, feature engineering templates for predictive models, cohort and lifecycle analytics, geospatial and temporal data patterns, price and demand forecasting signals, AB testing data patterns, and data quality, governance, and lineage considerations relevant to Airbnb data.

MediumTechnical

135 practiced

You're designing an A/B test to evaluate a change to the search ranking algorithm intended to increase bookings. What primary and guardrail metrics would you choose, how would you assign users to variants to avoid peeking and contamination, and how would you calculate required sample size for detecting a 2% lift in booking rate?

Sample Answer

Primary metric (goal): booking rate (bookings / unique search sessions). Define precisely (e.g., first booking within 7 days of the session) and compute per user-session.

Secondary/engagement metrics: CTR on results, clicks per session, time-to-book, conversion funnel drop-offs.

Guardrail metrics:- Revenue per session and average booking value (ensure lift isn't from lower-value bookings)- Cancelation/refund rate and booking quality (fraud/short-stay)- Search latency and result relevance (technical/regression risks)- Downstream engagement (repeat bookings within 30/90 days)

Randomization & avoiding contamination/peeking:- Unit: randomize at the user-id or cookie level (not session) so users see one variant.- Assignment: deterministic hash(user_id) mod 100 -> bucket; allocate buckets to control/treatment. This prevents re-randomization and cross-contamination across devices if using stable IDs.- Avoid peeking: pre-register primary metric, significance threshold, and analysis plan. Use either (a) fixed-horizon testing (collect full sample before analyzing) or (b) sequential testing with an alpha spending method (e.g., O'Brien-Fleming or Pocock) or use Bayesian stopping rules. Log interim looks and use corrected thresholds to control Type I error.

Sample size calculation for 2% lift:First clarify lift type. Two options:- Relative 2% lift (from p0 to p1 = p0 * 1.02)- Absolute 2 percentage point lift (p1 = p0 + 0.02)

Use two-proportion z-test approximation. Formula per group:n = [ (z_{1-α/2} * sqrt(2*p̄*(1-p̄)) + z_{power} * sqrt(p0*(1-p0)+p1*(1-p1)) )^2 ] / (p1 - p0)^2where p̄ = (p0+p1)/2, z_{1-α/2}=1.96 for α=0.05, z_{power}=0.84 for 80% power.

Example (typical baseline p0=5% = 0.05):- Relative 2% lift -> p1 = 0.051. Plugging values gives enormous N (~ millions per arm). This is expected because absolute change is tiny (0.001).- Absolute 2 percentage points -> p1 = 0.07.Compute:p̄=0.06. Numerator term ≈ (1.96*sqrt(2*0.06*0.94) + 0.84*sqrt(0.05*0.95+0.07*0.93))^2≈ (1.96*0.335 + 0.84*0.335)^2 ≈ (0.657+0.281)^2 ≈ 0.88^2 ≈0.774Denominator (0.02)^2 = 0.0004n ≈ 0.774 / 0.0004 ≈ 1935 per group (≈3.9k total).

Actionable notes:- Pick baseline p0 from recent data and clarify relative vs absolute lift.- If required N is huge for small relative lifts, consider alternative levers: target high-traffic segments, use metric with higher incidence (clicks), or run longer.- Monitor guardrails and use pre-specified stopping/analysis plan to avoid false positives.

MediumTechnical

85 practiced

Design feature engineering approaches to model price elasticity for listings. Describe candidate independent variables (price, competitor prices, seasonality indicators, occupancy, lead time), how to create price change experiments or quasi-experimental variation, and how you would define the target variable for elasticity estimation.

Sample Answer

Approach summary:Estimate how quantity demanded (bookings or nights booked) responds to price using feature engineering to capture listing, market, temporal, and demand-side drivers; use experimental or quasi-experimental variation to identify causal effects; define the target as a continuous demand metric (often in log form) so elasticity is the coefficient on log price.

Candidate independent variables (features & transforms)- Price features - log_price = ln(listing_price) — for log-log elasticity interpretation - relative_price = listing_price / market_median_price (or z-score) — captures competitiveness - price_change_pct_t = (price_t - price_{t-1})/price_{t-1} — dynamic effect - promo_flag, discount_pct- Competitor prices / market context - avg_competitor_log_price within same neighborhood/property type - min_competitor_price, share_of_competitors_lower_price - change in competitor prices (lags)- Seasonality & temporal controls - day_of_week, month, holiday_flag, event_flag - seasonal_spline or Fourier terms to capture smooth seasonality - lead_time (days between booking date and stay date) and lead_time buckets- Availability & demand signals - occupancy_rate for listing or neighborhood (past 7/30 days) - views_impressions, clicks if available - number_of_listings_available for date (supply)- Listing & host controls (fixed or engineered) - listing_fixed_effects (ID), host_fixed_effects — absorb time-invariant heterogeneity - review_score, number_of_reviews, amenities_count- Interaction terms - log_price × seasonality (e.g., peak vs off-peak) - log_price × lead_time_bucket (elasticity may differ by lead time) - log_price × occupancy_bin

Creating experimental or quasi-experimental variation- Randomized price experiments (A/B) - Randomly assign a subset of listings or dates to small controlled price changes; measure demand differences. Best causal evidence.- Staggered rollout / encouragement design - Encourage hosts to adopt new pricing algorithm in randomized waves; use intent-to-treat or compliance-adjusted IV.- Natural experiments / quasi-experiments - Use exogenous shocks such as platform-wide coupon campaigns, competitor site outage, or sudden policy change affecting a subset of listings. - Exploit algorithmic or policy changes rolled out by geography/time (difference-in-differences).- Instrumental variables - Use instruments correlated with listing price but not directly with local demand shocks: e.g., cost shocks to hosts (local tax change), competitor-supply shocks, randomized exposure to a new pricing tool.- Regression discontinuity - If pricing thresholds trigger visibility changes (e.g., free listing vs paid), estimate around threshold.

Defining the target variable for elasticity estimation- Primary: log_quantity = ln(quantity_t) where quantity_t is bookings, nights booked, or revenue per availability window (e.g., per day or per week). Estimate: log_quantity_it = α + β * log_price_it + X_it'γ + η_i + δ_t + ε_it - β is the point elasticity: %Δquantity / %Δprice.- Alternatives: - Δlog_quantity (first-difference) to remove listing fixed effects for panel with trends. - Conversion_rate per listing-day (bookings/impressions) — use logit/odds or log-odds for elasticity-like interpretation. - Revenue elasticity: log_revenue as target (captures intensive margin).- Windowing: - Aggregate over relevant decision windows (e.g., bookings per availability-day, weekly aggregates) to reduce noise.- Outcome timing: - Use forward-looking demand (bookings for target stay date) and lag price appropriately (price set at booking vs price at stay).

Identification & modeling notes- Always include listing and time fixed effects to control for unobserved heterogeneity and temporal shocks.- Cluster standard errors by market/listing as appropriate.- Check parallel trends for diff-in-diff; test instrument relevance and exclusion restriction for IV.- Model heterogeneity: estimate elasticities by segment (location, property type, lead time, occupancy) using interactions or separate models.- Validate: run placebo tests, check pre-trends, and compare experimental vs observational estimates.

Edge cases & practical considerations- Sparse demand listings: use hierarchical/Bayesian pooling to stabilize estimates.- Large outliers and promotions: winsorize or model separately.- Multi-night bookings: use nights booked or revenue-per-night normalization.

This approach produces interpretable elasticity estimates (β in log-log), leverages experimental/quasi-experimental variation for causal identification, and uses thoughtfully engineered features to control confounding and capture heterogeneity relevant for pricing decisions.

EasyTechnical

71 practiced

Explain cohort analysis and describe a specific cohort-based metric you would build to measure host retention. Include a brief SQL outline for how you would construct weekly cohorts based on host_signup_date and compute retention over 12 weeks using an events table containing host_id and event_date.

Sample Answer

Cohort analysis groups users by a shared start date (e.g., signup week) and tracks their behavior over time to isolate retention/engagement trends independent of growth. For host retention, I’d build a “weekly signup cohort retention” metric: for each signup-week cohort, compute the percent of hosts who performed at least one meaningful event (e.g., listing created, booking, login) in each subsequent week 0–12.

SQL outline (weekly cohorts, 12-week retention):

sql

WITH hosts AS (
  SELECT
    host_id,
    DATE_TRUNC('week', host_signup_date) AS cohort_week
  FROM host_table
),
events_weekly AS (
  SELECT
    e.host_id,
    DATE_TRUNC('week', e.event_date) AS event_week
  FROM events_table e
  WHERE e.event_date >= (SELECT MIN(host_signup_date) FROM host_table)
),
host_activity AS (
  SELECT
    h.host_id,
    h.cohort_week,
    DATE_PART('week', AGE(event_week, cohort_week)) AS weeks_since_signup
  FROM hosts h
  JOIN events_weekly ew ON h.host_id = ew.host_id
  WHERE DATE_PART('week', AGE(event_week, cohort_week)) BETWEEN 0 AND 12
  GROUP BY h.host_id, h.cohort_week, weeks_since_signup
),
cohort_sizes AS (
  SELECT cohort_week, COUNT(DISTINCT host_id) AS cohort_size
  FROM hosts
  GROUP BY cohort_week
),
cohort_retention AS (
  SELECT
    ha.cohort_week,
    ha.weeks_since_signup,
    COUNT(DISTINCT ha.host_id) AS active_hosts
  FROM host_activity ha
  GROUP BY ha.cohort_week, ha.weeks_since_signup
)
SELECT
  cr.cohort_week,
  cr.weeks_since_signup,
  cr.active_hosts,
  cs.cohort_size,
  ROUND(cr.active_hosts::numeric / cs.cohort_size, 4) AS retention_rate
FROM cohort_retention cr
JOIN cohort_sizes cs USING (cohort_week)
ORDER BY cohort_week, weeks_since_signup;

Key points:- Metric = % of cohort hosts with ≥1 event in week N.- Use week truncation so calendar alignment is consistent.- Watch for hosts with events before signup (exclude) and for small cohort sizes—suppress or annotate low-confidence rates. Alternative: compute median or cohort-level activity to complement percentages.

MediumTechnical

64 practiced

A stakeholder asks for a reproducible, automated report showing the 30-day trend in new hosts by city, excluding test accounts and support staff. Describe the end-to-end implementation: data sources, filters to exclude internal/test accounts, scheduled jobs, data validation, and how to make the report auditable.

Sample Answer

Approach: Build an automated, auditable 30-day “new hosts by city” pipeline that (1) ingests signup and user-role data, (2) applies deterministic filters to remove test/internal/support accounts, (3) aggregates daily counts by city for last 30 days, (4) validates data quality, and (5) publishes an auditable report/dashboard.

Data sources:- Primary user table (user_id, created_at, city, email, status)- Authentication/roles table (roles, team, is_support_flag)- Test account registry (explicit test_account boolean or list)- Optional: HR directory / SSO feed to identify internal employees

Filters to exclude internal/test accounts:- Exclude if is_test_account = true OR email domain in maintained blacklist (e.g., @example-test.com)- Exclude if user_id in internal_employee_list joined from HR/SSO- Exclude users with role flags is_support = true or roles LIKE '%support%'- Apply filters in SQL as a single deterministic WHERE clause and store the mask versioned (filter_version, rationale)

Implementation and scheduling:- Implement as a parameterized SQL transformation (dbt or scheduled stored proc) that: - Selects users where created_at BETWEEN current_date - 30 AND current_date - 1 - Applies exclusion filters - Groups by date(created_at) and city, producing daily new_host_count- Schedule via Airflow / cron / cloud scheduler to run daily at 02:00 (after ETL loads)- Persist results into a results table partitioned by run_date, with metadata columns (run_id, filter_version, source_snapshot_time)

Data validation:- Row-level checks: no null city for included rows (or bucket as 'Unknown'), created_at in expected window- Aggregation checks: compare sum(new_host_count) vs. count(distinct user_id) and vs. incremental delta from previous snapshot- Threshold alerts: send email/slack if daily counts deviate > 50% from 7-day moving average or if rows dropped > X%- Implement these as lightweight tests in dbt or Great Expectations; fail the job and attach logs if tests fail

Auditing and reproducibility:- Version-control SQL transformations and filter lists in Git; tag releases used in production- Store raw input snapshots (or snapshot references) and transformation outputs with run_id and timestamps- Persist filter_version and commit_hash in the results table and dashboard footer- Log lineage: which source tables/partitions and which run produced each output row- Provide an “audit view” that shows: run_id, filter_version, source_snapshot_times, rows_in, rows_out, and validation test results- Retain data and logs for the retention policy (e.g., 1 year) for compliance

Delivery:- Publish a Power BI/Tableau dashboard showing 30-day trend by city with interactive filters and a visible audit panel (filter_version, last run time, validation status). Include exportable CSV and a scheduled email with attached CSV and validation summary.

This design ensures deterministic, reproducible counts, automated delivery, and clear auditability for stakeholders.

MediumTechnical

85 practiced

Provide an approach to detect and correct for geographic misallocation of listings (wrong neighborhood or city assigned) in analytics tables. What heuristics, validation datasets, and correction strategies would you use, and how would you measure residual error after corrections?

Sample Answer

Approach (overview)- Use geospatial validation: compare listing lat/lon to canonical polygons for city/neighborhoods; flag mismatches where assigned area != polygon-lookup result.Heuristics to detect misallocation- Polygon mismatch: assigned_neighborhood != ST_Within(point, neighborhood_polygon).- Distance threshold: if centroid distance from assigned neighborhood > X km (e.g., 2 km), flag.- Outlier clustering: listing’s coordinates far from other listings of same host/property cluster (DBSCAN).- Postal code / administrative mismatch: assigned city != reverse-geocode(city_from_postcode).Validation datasets- Authoritative shapefiles: municipal/neighborhood polygons from city GIS or OpenStreetMap.- Reverse-geocoding services (Google, Nominatim) for cross-check.- Ground-truth sample: manual-labeled 1–2k listings across neighborhoods.Correction strategies1. Deterministic correction: if ST_Within returns a different polygon, update assigned_neighborhood = polygon_id and record source="polygon-override".2. Confidence scoring: combine signals (polygon match, distance, postcode) into score; only auto-correct when score > 0.9.3. Probabilistic correction: for ambiguous cases, assign top-N candidate neighborhoods with probabilities; surface to manual review for medium confidence.4. Propagate fixes: update analytics tables and keep audit log + versioned snapshot for reproducibility.Example SQL (PostGIS)

sql

SELECT l.id,
       n.id AS polygon_id,
       ST_Distance(l.geom, ST_Centroid(n.geom)) AS dist_m
FROM listings l
JOIN neighborhoods n
  ON ST_Contains(n.geom, l.geom)
WHERE l.assigned_neighborhood != n.id;

Measuring residual error- Holdout validation: use manual ground-truth sample to compute precision, recall, and accuracy before/after corrections.- Confusion matrix by neighborhood; per-neighborhood error rates.- Geographic drift metrics: median distance between listing point and assigned polygon centroid after correction.- Monitor changes over time: percentage of listings flagged per week and false-correction rate from a random audit (sample 1% monthly).Operational notes- Conservative auto-correct threshold to avoid introducing errors; log every change and enable rollback.- Periodically refresh shapefiles and postcode mappings; retrain confidence scoring if using ML features (density, host history, text address).

Unlock Full Question Bank

Get access to hundreds of Airbnb-Specific Data Patterns interview questions and detailed answers.

Join thousands of developers preparing for their dream job.