Setting the file. One moment.
Chapter 22 · Diagnosing Experiment Results
Subchapter 22.6
references/numbers-vs-sql.mdMarkdown17 KBView on GitHub
The experiment page applies a specific scope that ad-hoc SQL almost never replicates. A common pattern: SQL is written “to verify” experiment numbers and the results don’t match — most of the time, the experiment numbers are correct and the SQL is missing one or more scope filters.
If the gap between exposures and downstream metric counts is very large (the metric is one or
two orders of magnitude smaller than exposures), don’t anchor on SQL reconciliation. That shape of
divergence is most often a bucketing or identity-resolution problem, not a query-scope problem —
walk bias-and-skew.md first (especially A3 / A4) and only come back here once identity is ruled
out. The symptom often surfaces as “the numbers don’t match”, but the agent should route it to A
before D.
When the user reports “PostHog says X, my SQL says Y”, walk this checklist:
Exposure scope. The experiment counts only events that occur after the user’s first exposure. Raw counts don’t filter this way.
$multiple exclusion. With default handling (exclude), multi-variant users are dropped from
metrics. Raw counts include them.
Test-account filter. Defaults to true — internal/test users excluded. Raw counts don’t
typically apply it.
Date range. The experiment is bounded by start_date / end_date; raw counts often span more.
Variant attribution. The experiment uses the exposure event’s variant property; raw counts may pull variant from a different event.
Conversion window (funnel metrics only). Events outside the per-user conversion window are not counted. See D7.
Per-user aggregation. Mean / ratio metrics aggregate per-user before averaging, so the result is not a raw event-level total. See D4.
Winsorization (outlier clamping) on mean metrics. Mean metrics support a percentile-clamp
configuration that replaces values below the lower percentile and above the upper percentile with
the percentile values themselves before averaging. When enabled, no raw SQL AVG/SUM over the
underlying events will reconcile — values are post-clamp.
Recommend: reproduce the experiment’s scope in SQL exactly (start with experiment-get‘s
exposure_criteria, parameters, and stats_config), or accept that ad-hoc SQL will not match by
design.
Use this as the starting point when the user wants to reconcile. Fill the placeholders from
experiment-get. This reproduces sources 1, 2, 4, and 5 from the checklist directly; sources 3, 6,
and 7 are noted inline. Source 8 (winsorization) is not reproducible in a one-shot skeleton — if a
mean metric uses the percentile-clamp config, no raw AVG/SUM reconciles by design.
WITH exposures AS (
SELECT
person_id,
argMin(properties.$feature_flag_response, timestamp) AS variant,
min(timestamp) AS first_exposure
FROM events
WHERE event = '$feature_flag_called' -- or exposure_criteria.exposure_event when set
AND properties.$feature_flag = '<flag-key>'
AND properties.$feature_flag_response != '$multiple' -- source 2 (drop if multiple_variant_handling='first_seen')
AND timestamp >= '<start_date>' -- source 4
AND timestamp <= coalesce('<end_date>', now()) -- source 4
-- source 3: append the project's test-account filter here when filterTestAccounts=true
GROUP BY person_id
HAVING variant != ''
)
SELECT
u.variant,
count(DISTINCT u.person_id) AS exposed_users,
count(e.uuid) AS metric_events,
-- For "mean of per-user totals" (D4), wrap a per-user sum first then average:
-- avg(per_user_total) FROM (SELECT person_id, sum(toFloat(properties.<value-prop>)) AS per_user_total ...)
count(e.uuid) / nullIf(count(DISTINCT u.person_id), 0) AS events_per_user
FROM exposures u
LEFT JOIN events e
ON e.person_id = u.person_id
AND e.event = '<metric-event>' -- keep this in the JOIN, not WHERE,
-- so users with 0 metric events still count
AND e.timestamp >= u.first_exposure -- source 1
AND e.timestamp <= coalesce('<end_date>', now()) -- source 4
-- source 6: for funnel metrics, also gate e.timestamp <= u.first_exposure + INTERVAL '<conversion_window>'
GROUP BY u.variant
ORDER BY u.variantNote: keep the metric-event filter in the JOIN’s ON clause, not in a top-level WHERE — moving
it to WHERE would silently drop exposed users who never produced the metric event (e.event is
NULL for them), breaking the denominator.
Notes:
multiple_variant_handling = 'first_seen': drop the != '$multiple' filter and keep
argMin(...) — it already picks the first variant the user saw.e.event on the last step and joining the
exposure as step_0 implicitly.sum(...) subquery, then avg(...) across users in the
variant — not sum(...) event-level.exposures, not from e.filterTestAccounts=false and re-read the
experiment to confirm that’s the gap.For multi-step funnel metrics, statistical significance is always calculated between the first step (exposure) and the final step. Intermediate steps are shown for analysis and visualization but do not affect the significance calculation nor win probability — a user can read a significant intermediate step and incorrectly conclude the whole funnel is significant.
Implication: comparing PostHog’s funnel conversion rate to a SQL query that counts intermediate conversions will not match — and that’s expected.
The exposure event is automatically prepended as step_0 for funnel metrics, so a 1-step funnel is
really a 2-step funnel: exposure → action. Conversion = % of exposed users who reached the action.
When a user adds a breakdown (e.g. “by country” or “by device type”) to an experiment metric, the property is read from the exposure event, not the metric event. This is for statistical reasons — the metric event happens after exposure, but the breakdown needs to partition users at the time of exposure.
Implication: if the property only exists on the metric/conversion event (e.g. a checkout event with
payment_method), breaking down the experiment by it won’t work — every user will appear under “none”
because the property isn’t on the exposure event.
Recommend: if the user needs to break down by a property only set at conversion, they need to either:
Common confusion: adding “sum of revenue” expecting the raw total of all revenue events across exposed users. PostHog instead returns the mean of per-user totals — for each exposed user, sum their revenue events, then average across users in the variant.
Worked example: user A spent $50, user B spent $10. PostHog reports ($50 + $10) / 2 = $30, not
$60. The number looks much smaller than a raw SQL SUM(revenue) over the same time window
because it isn’t a sum at all — it’s the unit on which the statistical comparison runs.
This is the correct way to do statistical comparison (per-user values are the unit of randomization), but it’s a frequent source of “why is the number so much smaller than my SQL?” questions.
Recommend: explain the per-user aggregation. For a raw total for reporting, multiply the mean by the user count, or use product analytics for the descriptive total.
If a user breaks down by a property that doesn’t exist on the event being broken down, every value shows as “none” rather than an error. This is silent and confusing.
Verify: check that the breakdown property is actually being captured on the relevant event.
Recommend: if it’s the exposure event missing the property, see D3 — set the property earlier in
the journey, or capture it on $feature_flag_called directly.
The “View recordings” panel on the experiment page applies metric events as filters for finding relevant replays — but those filters don’t map exactly to the statistical calculations (e.g. funnel attribution type isn’t applied, conversion windows may not be).
Implication: the “story” in recordings can’t be reconciled 1:1 with the computed result. Don’t debug stats discrepancies via the recordings panel.
Recommend: use recordings to qualitatively understand variant differences (what users actually experienced), not to audit the numbers.
The conversion window isn’t a single rule — the new query runner applies it differently per metric type:
Mean / ratio metrics. Events count when
timestamp >= first_exposure_time AND timestamp < last_exposure_time + conversion_window. The
lower bound is anchored to the user’s first exposure; the upper bound is anchored to their
last exposure plus the window. Re-exposure extends the observation period; earlier conversions
still count.
Funnel metrics. The conversion window is enforced between consecutive funnel steps by the
aggregate_funnel_array ClickHouse UDF — not as a single window from first exposure. Each new
exposure event resets the funnel’s step-0 anchor, so re-exposure restarts the funnel rather than
extending an existing attempt. Ordered funnels skip the SQL-level temporal filter entirely; the
per-step gap check in the UDF is the only window enforcement.
Implication for SQL reconciliation:
e.timestamp >= u.first_exposure_time AND e.timestamp < u.last_exposure_time + INTERVAL '<window>',
not a single window from first exposure.If the numbers shifted unexpectedly across a query-runner migration, this is the most likely cause: historical pre-migration funnel attribution did not have the per-step gap semantics.
Experiment results are cached for up to 24 hours. Force-refresh (the manual button on the page) bypasses the cache. If pre-aggregation is enabled and a precomputation insert fails, PostHog falls back to a real-time query — which can produce a small inconsistency between two consecutive views, especially on fresh data.
Recommend: if numbers look stale, force-refresh the experiment first before debugging.
Symptom: a filter is added to a metric (e.g. “by device = mobile”) and the exposure / user count stays the same — only the conversion side moves. The conclusion looks like “the filter isn’t working.”
The experiment’s denominator is the set of exposed users, fixed at exposure time. A filter on a property of the metric event acts as a gate within that fixed population — it changes who counts as converted, not who counts as in the experiment. The denominator correctly does not shrink.
To shrink the denominator (i.e. only count users who match the filter as part of the experiment at all), encode the eligibility upstream — either in release conditions, or by setting the property on the exposure event itself, or by using a custom exposure event that already filters.
Recommend: explain the scope difference. If the mental model comes from another A/B tool that subset-filters the population on metric properties, name the tool and explain the design choice explicitly.
Insights have a “Use current person properties” toggle (versus as-of-event). Experiment metrics do not expose this toggle — person properties are always evaluated as of the time the event was captured.
This is intentional: the experiment’s population needs to be stable across the run. If person properties were re-resolved at query time, the population a user falls into could change over the course of the experiment as their attributes change (plan upgrades, geo moves, etc.), which would invalidate the analysis.
Recommend: for slices by “current state” attributes (e.g. “free vs paid as of today”), use one of:
Two EventsNode-shaped metric mis-configurations recur. Both produce numbers that look like
the data is broken but are actually the metric definition doing precisely what it was asked.
event: "" is not “all events”. In an EventsNode, the event field is an equality filter
against the event name. An empty string matches events literally named "" — i.e. none. The
metric’s metric_events CTE returns no rows, the LEFT JOIN from exposures produces NULLs on the
metric side, and the resulting metric collapses to a constant per user (commonly 1.0 for a
mean-shaped boolean count, or 0 for a total math). “All events” as a user-facing concept
requires either no event filter at the metric source or a different metric kind — not event: "".
count(boolean_expression) counts non-null, not true. A HogQL math_hogql of the form
count(properties.X = 'value') counts every event where the expression evaluates (i.e. every event
where the property is set, true or false), not events where the expression is true. Use
countIf(properties.X = 'value') for the “true” semantics, or sum(toInt(properties.X = 'value'))
for an additive form.
Verify directly. Inspect the rendered clickhouse_sql field from experiment-results-get —
the metric_events CTE shows the actual WHERE clause and the per-event value expression. If
the WHERE contains equals(events.event, ''), the metric is filtering to no events. If the
per-event value is a boolean expression wrapped in count(...), the math is counting evaluations
not truths. Either signature is dispositive.
Validation signals from PostHog. A validation_failures entry of "baseline-mean-is-zero" on
a mean metric is the system’s tell that every exposed user contributed 0 to the metric — almost
always a total math on a never-matching event filter.
Recommend:
event: "" with the actual event to measure (or use a metric kind that genuinely means
“all events” — confirm in the metric editor, not by typing "").countIf(...) or sum(toInt(...)) over count(...) of a boolean.