Airbnb·Data Scientist·Technical Phone Screen
- Given a raw events table and a guests table, write idiomatic vectorized Pandas to: sessionize events per guest using a 30-minute inactivity timeout, filter bot traffic, then compute segment-level metrics (unique guests, sessions, session-level and guest-level conversion rates, average sessions-to-first-order, and median time from first page_view to first order_completed) broken out by traffic_source and device. Restrict the analysis to a 7-day window ending 2025-09-01.
- Using the same event data, build a de-duplicated guest funnel for page_view, add_to_cart, checkout_start, and order_completed over the 7-day window. Each guest should be counted once at their highest attained step. Report step-through rates and absolute drop-offs.
- After filtering segments with fewer than 100 sessions, identify the top 5 segments by lift versus the sitewide guest-level conversion rate. Report both the lift and the 95% Wilson confidence interval for each segment's conversion rate.
- How do you handle guests who complete an order_completed event in the window but have no earlier events in the same window? Implement a specific rule: pull their first event from the prior 24 hours if available, otherwise treat them as a direct single-step conversion.
- How would you adapt this Pandas pipeline to run at scale in PySpark? What are the key translation points?
“The sessionization part is where I spent most of my mental energy.”