Experimentation Platforms and Infrastructure Questions

Direct answer

Compute conversion rate per variant by counting distinct exposed users per variant as the denominator, and counting distinct users who generated a purchase event within 14 days AFTER their own exposure timestamp as the numerator, being careful that the window is bounded on BOTH sides: it must start at the user's own assigned_at (excluding any purchase that happened before they were ever exposed) and end 14 days later (excluding anything past the window), anchored per-user, not to a single fixed calendar cutoff for everyone.

Structured elaboration

SELECT
  e.variant,
  COUNT(DISTINCT e.user_id) AS exposed_users,
  COUNT(DISTINCT CASE
      WHEN ev.event_type = 'purchase'
       AND ev.occurred_at >= e.assigned_at
       AND ev.occurred_at <= e.assigned_at + INTERVAL '14 days'
      THEN e.user_id END) AS converters,
  CAST(COUNT(DISTINCT CASE
      WHEN ev.event_type = 'purchase'
       AND ev.occurred_at >= e.assigned_at
       AND ev.occurred_at <= e.assigned_at + INTERVAL '14 days'
      THEN e.user_id END) AS REAL)
    / COUNT(DISTINCT e.user_id) AS conversion_rate
FROM exposures e
LEFT JOIN events ev ON ev.user_id = e.user_id
GROUP BY e.variant
ORDER BY e.variant;

Key points, complexity, and edge cases

The window is bounded on BOTH sides from each row's own assigned_at: occurred_at >= e.assigned_at excludes any purchase that happened before the user was exposed at all (a real risk for a repeat customer who purchased weeks earlier, unrelated to this experiment), and occurred_at <= e.assigned_at + INTERVAL '14 days' excludes anything past the attribution window. A query missing the lower bound will silently inflate the converter count with pre-exposure purchases; this is exactly the kind of bug that looks fine on a clean worked example (no test data with a pre-exposure purchase) and only surfaces once real, messy production data (a customer with purchase history predating the experiment) hits it. COUNT(DISTINCT ...) inside the CASE, rather than a plain COUNT, correctly avoids double-counting a user who made two purchases within the window. Complexity is a single-pass join and group-by, O(n) in the size of the joined exposures-and-events dataset; a user with no purchase events correctly contributes to exposed_users but not to converters, since the LEFT JOIN preserves them with no matching purchase row.

I executed this query in SQLite against a constructed test: two users per variant, one purchase inside the window (control, converts), one purchase exactly 15 days after exposure for the other variant (treatment, one day past the 14-day window). The query returned control: 2 exposed / 1 converter / 0.5 rate, and treatment: 2 exposed / 0 converters / 0.0 rate, correctly excluding the late purchase. I then added an adversarial case: a user exposed on 2026-02-10 with a purchase logged on 2026-01-05 (over a month before exposure). With only an upper-bound date filter (no occurred_at >= assigned_at), that pre-exposure purchase was incorrectly counted as a conversion (rate 1.0 for a single-user group); adding the lower bound above correctly excludes it (in a 3-user control group with that case added, converters = 1, not 2, giving a 0.333 rate instead of the wrong 0.667).

Trade-offs and pitfalls

A common mistake is joining events to exposures without restricting to event_type = 'purchase' inside the conversion condition, which would count ANY event (a click, a page view) as a conversion. An equally common and easy-to-miss mistake, confirmed above, is forgetting the LOWER bound on the attribution window entirely: filtering only on occurred_at <= assigned_at + 14 days looks correct and passes any test case that doesn't include a pre-exposure purchase, but silently inflates the converter count in production the moment a user with prior purchase history is exposed. Another mistake is using a single WHERE clause date filter (occurred_at <= '2026-01-15') instead of anchoring the cutoff per row to each user's own assigned_at, which silently breaks the moment users are exposed on different days, which they almost always are in a real rolling experiment.

Direct answer

Design the metadata schema around lineage, versioning, ownership, compliance, and status, with each experiment record capable of pointing back to prior versions of itself (a re-launch, a configuration change) rather than treating every change as an entirely new, disconnected record; an analyst uses these fields to find what ran and when, and an auditor uses them to reconstruct exactly what changed, who approved it, and why.

Structured elaboration

Core fields: experiment_id (stable across the experiment's life), version (increments on any material configuration change), parent_experiment_id (for a follow-up or re-launch that's conceptually the "same" test), owner, compliance_tags (which regulatory or policy category this touches, if any), dataset_links (which underlying tables/event streams feed its metrics), and status with a full transition history rather than just a current value. Rounding out the fields a reproducible re-analysis actually needs: assignment_seed (the exact value used to hash users into buckets, without which a past random assignment can never be recomputed), sample_allocation (the percentage split that was actually live, which the version field's change_history should preserve alongside every prior value, not just the current one), and code_version (which build of the experiment's code was running), so a backfill job recomputing historical metrics after a bug fix can reconstruct precisely what a user would have experienced at any past point in time.

Lookup patterns: an analyst investigating "what experiments touched this metric last quarter" queries by dataset_links and date range; an auditor reconstructing a specific decision queries the status transition history for exactly when an approval happened and who granted it; someone tracing a re-launched experiment's full history follows the parent_experiment_id chain back through every prior version rather than treating each relaunch as an unrelated, disconnected record.

Worked example

A pricing experiment launches, gets paused after two days due to a guardrail breach, is fixed, and relaunches as a new version two weeks later. Without a parent_experiment_id linking the relaunch to the original, an analyst querying "what happened with this pricing test" only finds the successful second attempt and has no record of the first attempt's guardrail breach, which is exactly the kind of institutional memory loss a lineage-aware schema is meant to prevent.

Trade-offs and pitfalls

Making every configuration change spawn an entirely new, disconnected experiment_id is simpler to implement but destroys the lineage an auditor or analyst actually needs; carrying full version history for every field on every change is more complete but adds real storage and query complexity. Most platforms land on versioning only the fields that materially affect interpretation (allocation, targeting, metrics) while treating cosmetic changes (a description edit) as non-versioned metadata updates.

Direct answer

Design the reconciliation around canonical UTC timestamps computed as close to the source as possible, a shared, explicit session-windowing definition both services agree to use (rather than each inferring sessions independently), deduplication keys based on a stable event id rather than a recomputed hash, idempotent processing so a retry doesn't double-count, and automated tests that assert the joined output on a known input produces a known, hand-verified result.

Structured elaboration

• Canonical timestamps: convert every timestamp to UTC at the earliest possible point (ideally at the source service, not downstream), and store the original timezone/offset alongside it for debugging, since a downstream conversion error is much harder to spot after the original context is lost.
• Session windowing: if Service A and Service B use different session-timeout definitions (say, 30 minutes of inactivity versus a fixed calendar day), events that should logically belong to the same session can get split differently by each service; the reconciliation process needs a single, explicitly agreed session definition applied consistently across both, computed at reconciliation time rather than trusting each service's own internal session boundary.
• Deduplication keys: use a stable, source-assigned event id (not a hash of mutable fields, which can change if any field is corrected or reprocessed) so a legitimate retry is recognized as the same event rather than treated as new.
• Late-arriving events: define an explicit lateness tolerance (how long after a session's nominal end an event can still arrive and be included) consistent with the storage-tiering and watermarking approach used elsewhere in the pipeline.
• Idempotency: the join and aggregation logic should produce the same result whether run once or replayed multiple times over the same input, which usually means writing to an idempotent sink (an upsert keyed by the same deduplication key) rather than a naive append.
• Tests for correctness: a golden-file style test with hand-constructed events from both services, including a case that straddles a timezone boundary and a case with a legitimate retry, verifying the joined output matches a manually-computed expected result.

Worked example

Service A logs an exposure at 23:50 local time in a timezone that's UTC+9, while Service B logs the matching conversion event using a server timestamp already in UTC. Without converting Service A's timestamp to UTC before comparison, a naive join could conclude the conversion happened BEFORE the exposure (since 23:50 local time is actually 14:50 UTC the same day, a nine-hour difference that a naive string or local-time comparison would miss entirely), silently excluding a legitimate conversion from the metric.

Trade-offs and pitfalls

Trusting each service's own internal session or timestamp handling instead of establishing one shared, explicit definition at reconciliation time is the root cause of most cross-service join bugs like this; it's tempting because it requires no coordination between the two teams, but it's exactly the coordination that prevents silent, hard-to-detect metric corruption. The cost of building the shared reconciliation layer (agreeing on and enforcing one canonical timestamp and session definition) is real coordination overhead between two teams, but it's cheap relative to the cost of a metric quietly being wrong for months before anyone traces it back to a timezone mismatch.

Direct answer

The core instrumentation practices are: consistent naming conventions across teams, explicit schema versioning, idempotent event delivery, event enrichment at ingestion rather than at query time, and backward compatibility whenever a schema changes, because a violation of any one of these quietly corrupts every metric computed downstream without producing an obvious error.

Structured elaboration

• Naming conventions: a shared taxonomy (verb_object style, like purchase_completed rather than one team's buy_done and another's checkout_success for the same underlying action) so a metric defined once can be reused across teams instead of every team redefining "purchase" slightly differently.
• Schema versioning: every event schema carries a version field, and a schema change is additive (new optional fields) rather than repurposing an existing field's meaning, so historical events remain readable by current code.
• Idempotency: events carry a unique event id so a retry (from a flaky network on the client, or a replay after a pipeline failure) can be deduplicated rather than double-counted, which matters enormously for count and sum metrics specifically.
• Event enrichment: joining an event to its experiment exposure, user segment, or session context should happen once, at ingestion, and be stored on the enriched record, rather than re-joined by every downstream query, both for performance and so every team computing a metric from the same enriched table agrees on what "exposed" meant at that moment.
• Backward compatibility: a schema change should never break a metric-computation job that hasn't yet been updated to read the new field, which in practice means never removing or repurposing a field, only adding.

Worked example

A team renames a field from user (an integer id) to user_id (a string id) in place, without a version bump, to "clean things up." Every metric job still filtering on the old field silently sees no matches for new events and reports a metric that quietly stops updating, which looks exactly like "the experiment has no effect" rather than "the pipeline broke," and can go unnoticed for days because nothing errors.

Trade-offs and pitfalls

Enforcing all of this centrally (a single shared schema registry every team must go through) adds process overhead that slows down individual teams shipping new events. The pragmatic middle ground most platforms land on is: a small number of core, centrally-governed event types (exposure, generic conversion) that everyone must use as-is, plus a flexible custom-properties field for team-specific context that doesn't require central review, so the fields that feed universal validity checks (like SRM) stay standardized while teams retain flexibility for their own custom analysis.

Direct answer

Automated safeguards need three distinct pieces working together: a continuous Sample Ratio Mismatch and instrumentation-drift check, an enforced pre-registration workflow that captures the hypothesis and stopping rule before launch, and a sequential-testing framework (alpha-spending or a Bayesian stopping rule) that lets teams look at results early without inflating the false-positive rate the way naive repeated peeking would.

Structured elaboration

• SRM and drift detection: the same continuous chi-square check described elsewhere, run on a cadence tight enough to catch a problem within hours, not days.
• Discouraging optional stopping: the platform shouldn't just trust teams not to peek; it should make peeking safe by design. That means either disabling an unadjusted significance view before a pre-committed analysis date, or, better, replacing it entirely with a sequential-testing view that's valid to check at any time because the stopping boundary itself accounts for repeated looks.
• Pre-registration: require hypothesis, primary metric, and either a planned duration or (for a sequential design) the alpha-spending function and maximum sample size, captured in the metadata registry before the experiment is allowed to launch, so the "was this decided in advance or after seeing the data" question always has a documented answer.
• Stopping rules: alpha-spending (O'Brien-Fleming style, conservative early, more permissive as more data accumulates) is a natural fit for teams that want a familiar frequentist framework; a Bayesian stopping rule (stop when the posterior probability of a meaningful effect crosses a threshold) is often more intuitive to communicate to a non-technical stakeholder ("there's a 97% chance this is a real improvement") but requires the team to agree on a prior in advance, which itself needs governance so priors aren't chosen post-hoc to get a convenient answer.
• Education, not just enforcement: pairing the guardrails with a short, mandatory explanation of WHY peeking is dangerous (framed around the platform's own historical false-positive rate before these controls existed, if that data is available) tends to get more genuine buy-in than a purely restrictive UI that teams try to route around.

Worked example

A team wants to check results on day 3 of a planned 14-day experiment because early numbers look extremely promising. Under a naive fixed-horizon test, checking early and stopping on a favorable result inflates the true false-positive rate well past the nominal 5%, sometimes several times over depending on how many times a team is tempted to look. Under an O'Brien-Fleming sequential design, the day-3 boundary is deliberately very conservative (requiring a much larger effect to declare significance that early), so the platform can honestly tell the team "you're welcome to look, but this early a result would need to be unusually large to be trustworthy," which is both true and actionable.

Trade-offs and pitfalls

Sequential methods trade simplicity for validity: teams used to a single end-of-experiment p-value have to learn a slightly different mental model (a boundary that moves over time rather than a single fixed threshold). The platform-design risk is building the sequential machinery correctly but leaving an unadjusted "quick view" dashboard available alongside it, which defeats the entire purpose, since teams will gravitate to whichever view gives them the answer they want to see.