skip to content

Why would an Amplitude funnel report fewer conversions than the same funnel computed in the warehouse?

level: seniorimportance: should knowfreq 50%

answer

  1. Two systems, two definitions, two entities
  2. Client-side collection is best-effort
  3. Funnels are ordered and time-boxed
  4. Users are resolved, not just joined on id
  5. Day boundaries follow a configured timezone

basics

~20 s

Usually three causes stack up: client-side events never reached Amplitude, the funnel enforces a conversion window and a step order the SQL ignores, and the two systems count different entities — Amplitude counts resolved users, the query counts rows or raw user ids.

solid answer

~40 s

Treat it as a reconciliation, not a bug hunt, and check the causes in order of size. **Collection loss** comes first: browser events are blocked by extensions, lost on page unload or dropped offline, while a warehouse table fed server-side sees everything — this alone explains most single-digit-to-teens percentage gaps. **Definition differences** come second: an Amplitude funnel requires the steps in order within a configured conversion window and counts unique users, whereas naive SQL often counts every row with no time limit. **Identity and time** come third: Amplitude resolves `device_id` and `user_id` onto one internal user and merges anonymous history, while the query joins on `user_id` alone; and Amplitude buckets days in the project's configured timezone, not UTC. Quantify each before concluding either side is wrong.

code

sql · 19 lines
sql
-- naive: counts anyone who ever did both, in any order, forever
SELECT count(DISTINCT a.user_id)
FROM events a JOIN events b ON a.user_id = b.user_id
WHERE a.event_type = 'Viewed Product'
  AND b.event_type = 'Order Completed';

-- funnel-faithful: ordered, windowed, one row per user
WITH step1 AS (
  SELECT user_id, min(event_time) AS t1
  FROM events WHERE event_type = 'Viewed Product'
  GROUP BY user_id
)
SELECT count(DISTINCT s.user_id) AS converted
FROM step1 s
JOIN events b
  ON b.user_id = s.user_id
 AND b.event_type = 'Order Completed'
 AND b.event_time > s.t1
 AND b.event_time <= s.t1 + INTERVAL '7 days';

go deeper

for a junior

Be ready to name at least two reasons the numbers differ — events lost in the browser, and a funnel definition with a time window that the SQL did not apply.

for a middle

Explain the funnel's actual semantics — ordered steps, conversion window, unique users — and reproduce them faithfully in SQL rather than counting each step independently.

for a senior

Decompose the gap into named, quantified terms and reconcile the first step before anything else. Show that you know identity resolution and project timezone change what is being counted, not just how much.

for a principal

Own the resolution rather than the investigation: declare a system of record per metric, publish the known collection-loss factor, and stop the organisation from re-running this comparison every quarter.

## Frame it as reconciliation The interviewer is not asking you to declare a winner. Two systems computing "the same" funnel over "the same" events almost never agree exactly, and the useful skill is decomposing the gap into named, quantified causes. Work down this list, measuring each contribution, and stop when the residual is small enough to explain. ## 1. Collection loss (usually the biggest term) Browser-collected events are best-effort. Content blockers and privacy extensions block analytics requests outright; a user who navigates or closes the tab before the request flushes loses the event; an offline mobile session may never flush at all; consent banners suppress collection until acceptance. If the warehouse table is populated server-side — from your own application database or a backend event log — it sees traffic Amplitude never received. This is directional and one-way: it makes Amplitude lower, not higher, which matches the symptom in the question. Quantify it by comparing a step you also record server-side (an order row, a signup row) against the same event in Amplitude for the same day. That ratio is your collection-loss baseline and it applies to every client-side step. ## 2. Funnel semantics the SQL did not reproduce An Amplitude funnel is a specific query, not a set of independent counts: - **Conversion window.** Steps must complete within a configured span. A user who browses today and buys in three weeks converts in unconstrained SQL and does not convert in a funnel with a shorter window. - **Step order.** The funnel evaluates the steps as a sequence; ad-hoc SQL that counts "users who did A" and "users who did B" separately counts people who did B before A. - **Unique users, not events.** Funnels count distinct users per step. A `COUNT(*)` over event rows counts repeats and will be higher for reasons that have nothing to do with conversion. - **First occurrence versus any occurrence.** Which occurrence of step one anchors the window changes who is in the denominator. These are definition differences, and each can move the number by more than the collection loss. Reproducing the funnel faithfully in SQL means an ordered, windowed, per-user query — not three separate counts. ## 3. Identity resolution Amplitude resolves `device_id` and `user_id` onto one internal Amplitude ID and merges a device's anonymous history into the user it first sees on that device. A warehouse query joining purely on `user_id` cannot see the anonymous prefix of a journey, so it will drop pre-login steps entirely — or, if the raw export is joined on device id instead, split one human into several. Conversely, an unreset shared device merges two humans into one on the Amplitude side. Whichever direction it runs, the two systems are counting different entities, and no amount of tuning the SQL will reconcile them until you decide which entity the metric is about. ## 4. Time Three distinct time problems live here. Amplitude buckets by the **project's configured timezone**, so a warehouse query grouping by UTC date will disagree on the boundary rows — small in aggregate, glaring on a daily chart. The event's `time` field comes from the client, and Amplitude reconciles client and server upload times to correct clock skew, so a device with a badly wrong clock can land events on the wrong day. And late-flushing mobile clients deliver yesterday's events today, so a chart read too early undercounts a day that will later fill in. ## 5. Ingest-side filtering and deduplication Events can be rejected or dropped for reasons that never surface as an error in your application: identifiers shorter than the minimum length Amplitude enforces are rejected unless the request relaxes it, events blocked in the project's tracking plan never appear, and `insert_id` deduplication removes retried duplicates that a naive warehouse loader happily kept. That last one is worth noting because it runs the other way — it makes the *warehouse* higher rather than Amplitude lower, and it is a genuine correctness win for Amplitude. ## Working the problem Start at the single first step of the funnel and reconcile that one number before touching the rest; a discrepancy at step one propagates everywhere and there is no point debugging step three underneath it. Pick one day, one platform and one event, and compare raw counts. Then reproduce the funnel semantics in SQL explicitly, with the window and the ordering, before comparing conversion rates. If you have Amplitude's raw event export into the warehouse, compare against *that* rather than against your own server-side table — it isolates definition differences from collection differences, because the export contains exactly what Amplitude ingested. ## What good judgment sounds like The strongest answer ends by picking a system of record per metric rather than chasing perfect agreement. Revenue and billing come from the transactional system, full stop. Behavioural rates — funnel conversion, retention, feature adoption — come from the analytics tool, whose numbers are self-consistent even when they are not complete. Publish which source owns which metric, publish the known collection-loss factor, and stop re-litigating a gap that is structural. Chasing an exact match between a best-effort client stream and a transactional database is a project with no end.

  • How would you isolate collection loss from definition differences?
    Compare against Amplitude's own raw event export in the warehouse rather than against your server-side table. The export contains exactly what Amplitude ingested, so any remaining gap against the chart is definition — window, ordering, uniqueness, timezone — while the gap between the export and your server-side rows is pure collection loss. Two comparisons, two causes, no guessing.
  • Which direction does insert_id deduplication push the discrepancy?
    It makes Amplitude lower than a naive warehouse loader, but for a good reason: retried uploads that Amplitude collapsed into one event may have been stored twice by a loader without its own idempotency key. Here the analytics tool is the more correct side, and the fix belongs in the warehouse pipeline rather than in Amplitude.
  • The gap is stable at roughly a tenth across every step. What does that tell you?
    A uniform multiplicative gap points at collection loss rather than at funnel definitions, because window and ordering differences hit specific steps rather than scaling everything evenly. Confirm by checking whether the ratio differs by browser or platform — blocker-heavy segments will show a worse ratio — and then treat the factor as a documented constant rather than a bug.
  • How do you stop this argument recurring every quarter?
    Assign a system of record per metric and publish it. Revenue and anything billed comes from the transactional database; behavioural rates such as conversion and retention come from the analytics tool, which is internally consistent even when incomplete. Record the known collection-loss factor alongside it so nobody rediscovers the gap and reopens the investigation.

saying these in an interview costs you the question

  • Assumes the warehouse is right and the analytics tool is broken
  • Compares a windowed ordered funnel against an unconstrained SQL join
  • Counts event rows on one side and unique users on the other
  • Ignores ad blockers and page-unload loss on client events
  • Groups by UTC while the project buckets days in another timezone

context

resolving links…