Marketplace Funnel Analysis

What's being tested

These prompts test event-level product analytics: building sessionized funnels from raw events, correct deduplication, time-windowed attribution, and computation of conversion/checkout metrics. Interviewers probe a candidate's ability to translate event logs into reliable aggregated metrics in Python/SQL, and reason about experiment-level choices like randomization unit and guardrail metrics.

Patterns & templates

• Sessionization by fixed inactivity gap (commonly 30 minutes): sort by user+ts, assign session ids with cumulative gap Boolean, O(n) streaming in pandas or SQL window.

• Last-event per visit using ROW_NUMBER() OVER (PARTITION BY user, session ORDER BY ts DESC) to dedupe and attribute conversions reliably.

• Time normalization: convert all timestamps to UTC, then bucket by user-local date using stored timezone or event geo before aggregating.

• Visit-to-conversion join: collapse visits to one row per session, then left-join bookings within attribution window (e.g., 7 days) using event-time ranges.

• Conversion rate intervals: compute proportions with Wilson score CI or Agresti–Coull for low counts; bootstrap for complex derived metrics.

• Experiment aggregation: aggregate per-randomization-unit (e.g., user or city), compute per-unit metrics, then use paired t-test/Poisson regression or nonparametric bootstrap for significance.

• Data sanity checks: COUNT(DISTINCT user) drift, event-type balances, and duplicate event_id detection before metric computation.

Common pitfalls

Pitfall: Using raw event rows for conversion rate leads to overcounting when users generate many events—always collapse to the attribution unit (session/user).

Pitfall: Ignoring timezone/user-local date causes weekday/cohort leakage in funnel metrics and misaligned A/B comparisons.

Pitfall: Reporting a p-value without predefining the primary metric or accounting for multiple metrics (no Bonferroni/false-discovery control).

Practice these

The practice cards below cover the canonical variants — solve all of them and time yourself.