Funnel Analysis and Conversion Tracking Questions

Product analytics practice focused on analyzing user journeys and measuring how well a product or website converts visitors into desired outcomes. Core skills include defining macro and micro conversions, mapping multi step user journeys, designing and instrumenting event level tracking, building and interpreting conversion funnels, calculating step by step conversion rates and drop off, and quantifying funnel leakage. Candidates should be able to segment funnels by cohort, acquisition source, channel, device, geography, or user persona; perform retention and cohort analysis; reason about time based attribution and multi path journeys; and estimate the impact of optimization levers. Practical competencies include implementing tracking, validating data quality, identifying common pitfalls such as missing events or incorrect attribution windows, and using split testing and iterative analysis to validate hypotheses. Candidates should also be able to diagnose root causes of drop off, create mental models of user behavior, run diagnostic analyses and experiments, and recommend prioritized interventions and product or experience changes with expected outcomes and measurement plans.

MediumTechnical

56 practiced

Design strategies to detect and deduplicate duplicate events in an event stream. Describe both prevention (client/server-side idempotency) and post-ingest deduplication (SQL/ETL) approaches, and discuss trade-offs when using event_id, fingerprinting, or time-window based deduplication.

Sample Answer

Prevention (idempotency)- Client-side: Assign a stable event_id (UUIDv4 or deterministic UUIDv5 based on payload+user_id+timestamp) before send; retry logic should reuse the same id. Pros: prevents duplicates upstream, simple to trace. Cons: requires client changes; risk of accidental reuse if generation not deterministic.- Server-side: Accept client event_id and enforce idempotency table (event_id → processed_at). On ingest, check and drop duplicates before writing to primary store; return idempotent responses. Use TTL for table if space is a concern.

Post-ingest deduplication (SQL/ETL)- Event_id-based (preferred when available): Simple DISTINCT or LEFT JOIN against processed keys. Example (SQL):

sql

WITH ranked AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY event_id ORDER BY received_at) AS rn
    FROM raw_events
  )
  INSERT INTO events
  SELECT * FROM ranked WHERE rn = 1;

- Fingerprinting (when no event_id): compute hash over canonicalized payload (order keys, normalize whitespace, round floats) and dedupe on fingerprint.- Time-window / heuristic dedupe: group by user/session and event_type within N seconds/minutes to collapse obvious retries. Useful for high-volume noisy sources.

Trade-offs- event_id: most accurate, low false positives; requires producer discipline.- fingerprinting: flexible, handles lost event_ids, but risks false positives if two distinct events normalize to same fingerprint or payload mutates slightly.- time-window: easy and no exact id needed; higher false positive risk for legitimately repeated actions and depends on chosen window size.

Recommendations for BI context- Prioritize producer-generated event_id; enforce via SLA with engineering/partners.- For legacy data, implement fingerprint + SQL window dedupe in ETL and log decisions for auditability.- Monitor dedupe metrics (duplicate rate, discarded volume) and keep raw immutable archive so analysts can re-run rules if needed.

MediumTechnical

50 practiced

How would you model and measure conversion when users follow many different non-linear paths to the same outcome (multi-path journeys)? Describe at least three analytical methods or visualizations you would use and explain when each is appropriate.

EasyTechnical

109 practiced

List the top data-quality red flags you would monitor for a funnel reporting system (e.g., sudden drops, event count mismatches). For each red flag, give a first immediate check an analyst should run to diagnose whether the issue is instrumentation, ETL, or analysis-level.

Sample Answer

Here are the top data-quality red flags for a funnel reporting system and one immediate diagnostic check an analyst should run for each to help narrow whether it's an instrumentation, ETL, or analysis-level problem.

1) Sudden drop (or spike) in conversion rate for a step- Immediate check: Query raw event counts by ingestion timestamp for that event over the past 48–72 hours (GROUP BY date_hour). If raw event counts drop suddenly at source → instrumentation. If raw counts are present but not in warehouse table → ETL. If warehouse counts match but dashboard shows different numbers → analysis/filtering problem.

2) Event count mismatches between source and warehouse/dashboard- Immediate check: Compare a single-day event count from the streaming/source logs (or mobile analytics export) vs the warehouse table for the same time window. Mismatch before warehouse write → ETL; mismatch between warehouse and dashboard query → analysis (joins/filters/LookML); zero events in source → instrumentation.

3) High percentage of users stuck at one funnel step (unexpected bottleneck)- Immediate check: Inspect individual user/session event sequences for a sample of affected user IDs (LIMIT 10) in raw events. If missing downstream events for many users → instrumentation. If events present but excluded by transformation logic (e.g., dedup/time-window) → ETL. If segmentation/filters in dashboard isolate wrong cohort → analysis.

4) Temporal shifts (events appearing with delayed timestamps)- Immediate check: Check event ingestion_timestamp vs event_client_timestamp distributions. Client timestamps much earlier than ingestion → network/device instrumentation delay or SDK buffering. Ingestion timestamps present but ETL shows different partitioning → ETL timezone/partitioning bug. Dashboard time binning mismatch → analysis.

5) Unusually high duplicate events- Immediate check: Query raw events for duplicate (user_id, event_type, event_id) occurrences and check event_id uniqueness. If duplicates exist at source → instrumentation (idempotency), if duplicates introduced during ingestion/stream retries → ETL, if dedup logic missing in report query → analysis.

6) Negative or impossible metric values (e.g., negative session length)- Immediate check: Inspect raw payload fields used to compute metric (start_time/end_time). If payloads contain bad values → instrumentation. If raw values are fine but warehouse calculation produces negative → ETL transformation bug. If dashboard calculation (e.g., subtraction with wrong units) causes it → analysis.

7) Segment or cohort discrepancies (same cohort shows different numbers across reports)- Immediate check: Export the cohort definition and run the cohort-selection SQL against the warehouse independently. If independent SQL matches one report but not the other → analysis (definition mismatch). If both differ from raw events → ETL/instrumentation.

8) Schema changes causing missing fields- Immediate check: Check recent schema-change logs (SDK updates, API versions) and inspect example raw events for presence/absence of expected fields. Missing at source → instrumentation; present in source but missing in warehouse table columns → ETL; present in warehouse but dashboards use old field name → analysis.

General tip: always start by comparing raw/source logs → warehouse tables → dashboard query results. That three-layer comparison quickly isolates whether the fault originates in instrumentation, ETL, or analysis.

MediumTechnical

73 practiced

Back-of-envelope estimation: a product change improves a funnel step conversion from 25% to 30% on a page with 100,000 monthly visitors. Downstream conversion (to paid) is currently 20% from the next step, and average revenue per new paid user is $120. Estimate additional monthly paid conversions and incremental monthly revenue. Show your calculations and assumptions.

EasyTechnical

68 practiced

You observe three funnel steps with these counts: Discover=50,000, Activate=2,000, Retain=400. As a BI analyst, which single step would you prioritize for improvement first? Explain your reasoning using impact and effort considerations and describe one concrete experiment you might propose for that step.

Unlock Full Question Bank

Get access to hundreds of Funnel Analysis and Conversion Tracking interview questions and detailed answers.

Join thousands of developers preparing for their dream job.