Compute browsing metrics in Python from logs

Q: Compute browsing metrics in Python from logs

This is a Data Manipulation (SQL/Python) interview question from Airbnb for Data Scientist roles. View the full question and solution on PracHub.

Q: How do I approach Data Manipulation (SQL/Python) interview questions?

Data Manipulation (SQL/Python) questions require understanding of core concepts and practice. PracHub provides solutions with explanations to help you master data manipulation (sql/python) interviews.

Question

Given event logs, write idiomatic Pandas to compute segment-level metrics and a funnel. Data schema: events(event_id, ts_utc, guest_id, device in {desktop, mobile}, traffic_source in {direct, seo, sem, email, partner}, event_type in {page_view, add_to_cart, checkout_start, order_completed}, page_url, user_agent), guests(guest_id, signup_dt_utc). Definitions: sessionize per guest with a 30-minute inactivity timeout; drop bot traffic where user_agent ILIKE '%bot%' OR per-guest valid events<2; treat a conversion as an order_completed event. Use the window last 7 days relative to “today” where today=2025-09-01, i.e., 2025-08-26 through 2025-09-01 inclusive. Tasks: 1) Create sessions with session_id and session_start/end, ensuring time zone consistency (UTC) and stable sessionization across devices. 2) For each segment (traffic_source, device), compute: unique guests, sessions, session-level conversion rate (sessions with ≥1 order_completed / sessions), guest-level conversion rate (guests with ≥1 order_completed / guests), average sessions-to-first-order per guest, and median time from first page_view to first order. 3) Build a de-duplicated guest funnel across the window: page_view → add_to_cart → checkout_start → order_completed; report step-through rates and absolute drop-offs, counting each guest once at their highest attained step. 4) Exclude guests with only bot traffic and segments with <100 sessions; output the top 5 segments by lift vs. sitewide guest-level conversion (report lift and 95% Wilson CI for the segment conversion). 5) Clearly state and implement how you handle guests who order without prior events in the window (carry their first event from the previous 24h if available; otherwise treat as direct single-step). Provide Pandas code (vectorized; no per-row Python loops) and explain how it scales if moved to PySpark.

Compute browsing metrics in Python from logs

Comments (0)