Experiment Event Schemas, Minimal Instrumentation and Example Analytics Queries

Most experiment programs don't fail because the ideas were bad. They fail at the handoff — the moment a product team ships an experiment and passes the "results" to ops or an innovation lead who then can't answer a basic question like how many people actually reached the point where the experiment could have changed their behavior?

The gap is almost always the event schema. Someone instrumented the experiment in whatever way was convenient that sprint, naming was invented on the fly, and three weeks later nobody can reconcile the product analytics dashboard with the spreadsheet the innovation team is using to decide whether to fund the next phase.

This post is narrow on purpose. It's about the experiment event schema telemetry you need for clean product/ops handoffs — the actual field-level contracts in CSV and JSON, the smallest instrumentation checklist that still gives you defensible numbers, and SQL queries you can paste into most warehouses to compute learning KPIs. No theory about experimentation culture. Just the plumbing that keeps two teams reading the same number.

The specific failure this solves

Here's a pattern that shows up constantly in cross-functional experiment work. A product squad runs a two-week test on a new onboarding flow. They fire events named onboardingstarted, step2done, finished_v2. The names made sense to the engineer writing them. The experiment "ends" and gets handed to an ops or innovation person who needs to report: did this experiment produce a learning worth scaling? That person opens the data and finds:

1. 1
   No consistent experiment identifier, so they can't isolate this test from the three others running concurrently.
2. 2
   No variant field, so control and treatment are mixed together.
3. 3
   Timestamps in three different formats because two events came from the web app and one came from a backend job.
4. 4
   No way to consistently tie an event back to a unit — user, account, or session.

So they do what everyone does: manually reconstruct the analysis, make three assumptions, and present a number they don't fully trust. That distrust is what kills the funding decision, not the experiment result itself. The fix is boring and it works: agree on an event contract before instrumentation, keep it small, and make it platform-agnostic so it survives whether events come from a web app, a mobile SDK, a CSV export, or a backend service.

The minimal event contract (the part everyone skips)

An event contract is just the agreed shape of every experiment event. The mistake teams make is trying to design something "complete" with 30 fields. In practice, the schemas that survive handoffs are the ones with around 8–10 required fields and everything else pushed into a flexible properties bag. Here's the core contract. Every experiment event, regardless of source, should carry these:

The combination of unitid + variant + experimentid is what lets anyone — product, ops, an analyst who joined last week — slice the experiment correctly without asking three questions in Slack.

One thing worth internalizing: the properties bag is where discipline goes to die. It's tempting to let every team dump whatever they want in there. Keep a lightweight registry of what properties each experiment uses, or six months from now your properties field will contain amount, amt, total_amount, and value all meaning the same thing.

“

Track a one-line registry per experiment of the properties keys you accept so downstream analysts can rely on consistent naming.

JSON version

{ "eventid": "b3f1c2a4-7e2d-4c9a-9f11-8a2b0c4d5e6f", "eventname": "checkoutcompleted", "eventtimestamp": "2026-03-14T09:22:41Z", "experimentid": "exp2026checkoutsimplify", "variant": "treatmenta", "unitid": "user84213", "unittype": "user", "source": "web", "schemaversion": "1.1", "properties": { "ordervalue": 47.90, "stepcount": 3, "couponused": false } }

CSV version

eventid,eventname,eventtimestamp,experimentid,variant,unitid,unittype,source,schemaversion,properties b3f1c2a4-7e2d-4c9a-9f11-8a2b0c4d5e6f,checkoutcompleted,2026-03-14T09:22:41Z,exp2026checkoutsimplify,treatmenta,user84213,user,web,1.1,"{""ordervalue"":47.90,""stepcount"":3,""couponused"":false}" 7d9e0a11-3b4c-4d5e-8f6a-1b2c3d4e5f60,checkoutcompleted,2026-03-14T09:24:03Z,exp2026checkoutsimplify,control,user84990,user,web,1.1,"{""ordervalue"":39.00,""stepcount"":5,""couponused"":true}"

Keeping the CSV and JSON field-identical is the whole point. A pilot that starts as a manual spreadsheet and later gets proper instrumentation should produce rows that line up in the same table without a rewrite.

Minimal instrumentation checklist

You don't need to instrument everything. You need to instrument the handful of moments that let you compute the learning KPI you actually care about. Before a single event fires, run through this:

1. 1
   [ ] One exposure event. The moment a unit is assigned to a variant. Without this, you can't compute a real denominator — you'll accidentally measure "people who converted" against "everyone" instead of "people who actually saw the experiment."
2. 2
   [ ] One or two outcome events. The behavior the experiment is trying to move. Resist adding five. Two is usually plenty.
3. 3
   [ ] A guardrail event. Something that catches unintended harm — errors, refunds, unsubscribes. Experiments that only track the upside are how programs quietly ship regressions.
4. 4
   [ ] Consistent unitid across all events. If exposure uses userid and conversion uses account_id, your join silently drops rows.
5. 5
   [ ] UTC timestamps, one format. Decide this once. Re-litigating timezones during analysis wastes days.
6. 6
   [ ] Idempotent event_id. Generate it at the source so retries don't double-count.
7. 7
   [ ] Variant recorded on the event itself. Don't rely on looking up assignment later from a separate table that may have changed.
8. 8
   [ ] A dry run with 20–30 fake units. Fire events end to end and confirm they land in the warehouse before you trust real traffic.

The dry run is the step people skip and regret. A team launches, waits two weeks, then discovers their exposure event never fired on Safari. Two weeks of treatment data, no valid denominator. Thirty minutes of testing upfront would have caught it.

If you're layering any automated scoring or ML signals on top of these events later, the schema discipline here matters even more — messy inputs produce untrustworthy models. That trade-off is worth reading about separately in the write-up on practical ML signals and human-in-the-loop patterns for idea triage.

The queries that turn events into learning KPIs

Assume everything lands in a table called experiment_events with the columns above. These are the queries you'll reach for on almost every experiment. They're written in fairly standard SQL and should port to Postgres, BigQuery, or Snowflake with minor tweaks.

1. Sanity check — are your variants actually balanced?

SELECT experimentid, variant, COUNT(DISTINCT unitid) AS unitsexposed FROM experimentevents WHERE eventname = 'experimentexposed' AND experimentid = 'exp2026checkoutsimplify' GROUP BY experiment_id, variant ORDER BY variant;

If control shows roughly 9,800 units and treatment_a shows around 4,100, stop. Something is wrong with assignment, not with the treatment.

2. Conversion rate by variant (the core learning KPI).

WITH exposed AS ( SELECT DISTINCT unitid, variant FROM experimentevents WHERE eventname = 'experimentexposed' AND experimentid = 'exp2026checkoutsimplify' ), converted AS ( SELECT DISTINCT unitid FROM experimentevents WHERE eventname = 'checkoutcompleted' AND experimentid = 'exp2026checkoutsimplify' ) SELECT e.variant, COUNT(DISTINCT e.unitid) AS exposedunits, COUNT(DISTINCT c.unitid) AS convertedunits, ROUND( COUNT(DISTINCT c.unitid) * 1.0 / NULLIF(COUNT(DISTINCT e.unitid), 0), 4 ) AS conversionrate FROM exposed e LEFT JOIN converted c ON e.unitid = c.unit_id GROUP BY e.variant ORDER BY e.variant;

The DISTINCT on unit_id is doing quiet, important work — it prevents a user who triggered the outcome twice from inflating the numerator.

3. Guardrail check.

WITH exposed AS ( SELECT DISTINCT unitid, variant FROM experimentevents WHERE eventname = 'experimentexposed' AND experimentid = 'exp2026checkoutsimplify' ), guardrail AS ( SELECT DISTINCT unitid FROM experimentevents WHERE eventname = 'refundrequested' AND experimentid = 'exp2026checkoutsimplify' ) SELECT e.variant, COUNT(DISTINCT g.unitid) * 1.0 / NULLIF(COUNT(DISTINCT e.unitid), 0) AS refundrate FROM exposed e LEFT JOIN guardrail g ON e.unitid = g.unit_id GROUP BY e.variant;

4. Data-quality watchdog — duplicate events.

SELECT eventid, COUNT() AS occurrences FROM experimentevents WHERE experimentid = 'exp2026checkoutsimplify' GROUP BY event_id HAVING COUNT() > 1 ORDER BY occurrences DESC;

If this returns rows, your event_id isn't doing its job upstream.

A minimal dashboard layout for the handoff

The dashboard product hands to ops doesn't need to be elaborate. It needs four things, in this order, so a non-analyst can read it top to bottom:

1. 1
   Exposure balance — units per variant, updated daily. This is the trust anchor. If it looks wrong, everything below is suspect.
2. 2
   Primary KPI by variant — conversion rate with raw counts visible, not just the percentage.
3. 3
   Guardrail metric — refund/error/unsubscribe rate by variant.
4. 4
   Data-quality strip — duplicate count, null unitid count, and unexpected eventname values.

That fourth tile is the one people leave off, and it's the one that saves the handoff. When ops can see for themselves that there are zero duplicates and zero null units, they stop second-guessing the numbers. When the strip lights up red, everyone knows to pause before making a call. For teams that also report experiment outcomes up to leadership, this pairs naturally with the broader dashboard thinking in innovation program metrics that actually prove ROI.

A short real scenario

A mid-market subscription business — roughly 40 people, a small product team and a two-person innovation function — was running somewhere between six and eight experiments a quarter. Their handoff process was a shared spreadsheet. Every experiment result took the ops lead two to three days to reconcile, mostly because each product engineer named events differently and mixed timezones.

They didn't buy anything fancy. They wrote a one-page event contract almost identical to the table above, enforced the eight required fields, and required the 30-unit dry run before any launch.

Within a couple of quarters, reconciliation dropped from days to well under an hour, because the queries just ran against a consistent table. The more interesting outcome wasn't speed — it was that the innovation lead stopped hedging in funding meetings. When the numbers came from a schema everyone trusted, roughly three experiments that had previously been shelved as "results unclear" got re-run cleanly and two of them turned into funded rollouts. The contract was the missing piece, not the tooling.

When this level of rigor makes sense — and when it doesn't

When it's worth it:

1. 1
   You're running multiple experiments at once and results get mixed up.
2. 2
   Product and ops are different people, and handoffs are where trust breaks down.
3. 3
   Someone downstream makes a funding or scaling decision from the numbers.
4. 4
   You're accumulating experiments over time and want them comparable later.

When it's overkill:

1. 1
   A single team runs one experiment, analyzes it themselves, and moves on. If the same person instruments and interprets, a heavy contract adds friction with no payoff.
2. 2
   Throwaway tests where you already know you won't act on the result.

Who should not do this: teams that haven't yet agreed on what the experiment is trying to learn. Instrumentation can't fix a fuzzy hypothesis. If nobody can name the one outcome event before you start, don't build the schema — go back and define the learning question first. A perfectly instrumented experiment measuring the wrong thing is more dangerous than a messy one, because it looks credible.

Closing thought

The value of a shared event contract isn't cleaner data for its own sake.

It's that two teams stop arguing about whether the number is real and start arguing about what to do with it — which is the argument you actually want to be having. Keep the schema small. Enforce the eight fields. Do the dry run. Run the sanity query before the outcome query, every time. Do that consistently, and the product-to-ops handoff stops being the place where good experiments go to die a slow, spreadsheet-shaped death.

Keep the schema small. Enforce the eight fields. Do the dry run. Run the sanity query before the outcome query, every time. Do that consistently, and the product-to-ops handoff stops being the place where good experiments go to die a slow, spreadsheet-shaped death.