1. Fix assignment.
I keep the earliest assignment, as required by this example. A later event cannot move someone into the other arm. Conflicting arms at the same earliest timestamp stop the analysis.
FLAGSHIP CASE STUDY / EXPERIMENT INTEGRITY
Before choosing a winner,
check the denominator.
The report suggested B was underperforming. My first question was whether the report still represented the people assigned to the test.
Assessment, not a client result. Adapted from my job-application assessment work. The public example is anonymised: aggregate counts describe the assessment scenario; miniature records are a synthetic reconstruction. The reporting corrections are reproducible. Business uplift has not been measured.
THE DECISION / A 60-SECOND READ
Keep A for now.
Reconcile before rollout.
What needed deciding
Should B replace A? Included users showed a lower purchase rate for B, with a conventional p-value near 0.036. That looks actionable until you check who was left out.
What changed my view
Filtering removed different shares of A and B. The SQL exercise also moved a user between arms, excluded non-buyers and treated equal-value orders as duplicates. Those are measurement problems that change the interpretation.
MY RECOMMENDATION
I would pause B’s rollout, retain A and investigate the missing outcomes.
This is a temporary decision under uncertainty. It is not evidence that A is the better experience. I would only reconsider rollout after reconciling the assigned population and running a valid analysis.
Excluded from the aggregate report767 users12.78% of 6,000 assigned users
A RPU correction in the SQL fixture−50%$1,000 reported → $500 corrected
Executable validation checks12 passedIncluded in the reproducible pack
01 / CHECK WHAT THE DATA REPRESENTS
The result changes
when the population changes.
I would start with assignment counts and work forwards. A tidy dashboard cannot compensate for an unreliable denominator.
| Measure | A | B |
|---|---|---|
| Assigned users | 3,000 | 3,000 |
| Included in report | 2,497 | 2,736 |
| Excluded users | 503 | 264 |
| Inclusion rate | 83.23% | 91.20% |
| Buyers among included users | 66 | 49 |
| Included purchase rate | 2.643% | 1.791% |
| Purchases among excluded users | Unknown | Unknown |
The descriptive result is clear. The causal conclusion is not.
B minus A is −0.852 percentage points among included users. A two-sided pooled proportion test gives p ≈ 0.0357, but a p-value does not repair selection bias. The assigned population is balanced; the included population is not. Its 50/50 sample-ratio check gives p ≈ 0.000954, alongside the known difference in inclusion rates.
The full assigned-population effect remains unknown. Replacing the denominator with 3,000 while treating missing purchases as zero would create a different error. The potential direction of bias cannot be inferred from the inclusion rates alone.
02 / REBUILD THE LOGIC AND CHECK THE OUTPUT
Three errors.
One unreliable comparison.
The miniature fixture makes each failure inspectable. Its eight users are not a sample for estimating a treatment effect.
2. Keep non-buyers.
I start from assigned users and left join their orders. The buyer count and the user denominator answer different questions; joining only purchasers makes the rate meaningless.
3. Deduplicate by identity.
Two orders can have the same value. I collapse exact duplicate records by order identity and flag conflicting versions, rather than using SUM(DISTINCT order_value).
4. Make the window explicit.
The reconstruction uses a seven-day post-assignment window, one currency and no refund model. A real study would define outcome timing and net revenue before launch.
| Arm / query | Users | Buyers | Revenue | Revenue per user |
|---|---|---|---|---|
| A · flawed | 1 | 1 | $1,000 | $1,000 |
| A · corrected | 4 | 2 | $2,000 | $500 |
| B · flawed | 2 | 2 | $2,500 | $1,250 |
| B · corrected | 4 | 1 | $1,500 | $375 |
A’s corrected revenue is higher while RPU is lower because the denominator is also corrected. This is reporting accuracy, not revenue growth. There is no valid inference about A versus B from four users per arm.
Inspect the corrected SQL
The full query is available in the reproducible pack.Download corrected.sql ↓
What the 12 checks cover
- All eight assigned users remain.
- The first assignment keeps u01 in A.
- A’s users, buyers, revenue and RPU reconcile.
- B’s users, buyers, revenue and RPU reconcile.
- The flawed query reproduces A’s $1,000 RPU.
- One exact duplicate export row is identified.
- A distinct same-value order adds $1,000 to A’s revenue.
- A purchase outside the seven-day window is excluded.
- Conflicting records for one order ID stop analysis.
- Conflicting earliest assignments stop analysis.
- The aggregate report excludes 767 people.
- A net difference of 18 recovered buyers in B’s favour produces the first strict point-estimate reversal.
03 / ASK WHAT WOULD CHANGE THE DECISION
How much could
the missing data matter?
I can quantify the sensitivity without pretending to know the missing outcomes.
Let a be the recovered buyer count for A and b the count for B. B’s full-population point estimate exceeds A’s only when b − a ≥ 18.
These are assumed scenarios, not recovered records. Each excluded user can be either a buyer or a non-buyer.
SENSITIVITY SCENARIO / NOT A TEST RESULT
A higher point estimate is not enough to roll out. Measurement, uncertainty, revenue and guardrails still need to hold.
With no assumptions about the missing users, worst-case B−A purchase-rate bounds run from −17.33 to +8.23 percentage points. These are identification bounds, not a confidence interval. Their width shows how little the incomplete report tells us.
04 / DEFINE WHAT “IMPACT” ACTUALLY MEANS
Measure the correction.
Then measure the business effect.
I would keep technical corrections, operational improvements and commercial outcomes separate.
| Impact measure | Method | Current evidence |
|---|---|---|
| Reporting accuracy | Reconcile query outputs against known user and order records. Compare before and after using the same fixture. | Verified here: A RPU $1,000 → $500; corrected revenue $2,000. |
| Population coverage | Represented assigned users ÷ all assigned users. Report missing-outcome rates separately by arm. | Verified here: 767 excluded from the aggregate report. Fixture coverage becomes 8/8. |
| Rework and review time | Measure the time required for comparable reconciliation tasks before and after; record volume, analyst experience and error rates. Report median and spread. | Proposed: no time saving measured. |
| Commercial effect | Run a valid pre-registered A/B study using net revenue per assigned user, with revenue-appropriate uncertainty and cost/margin guardrails. | Unknown: no realised revenue lift or avoided loss established. |
How I would implement the measurement
Agree the outcome and economics.
Define seven-day net revenue per assigned user as an example primary measure, including refunds, currency and attribution-independent order matching. Fix the horizon to the purchase cycle. Purchase rate is secondary; agree contribution margin and refund guardrails.
Restore a complete audit.
Engineering extracts assignment and backend outcomes; analytics reconciles IDs, counts and order value; the experiment owner signs off definitions. Keep missing outcomes explicit. Every assignment must be represented and conflicting order IDs resolved before interpreting effects.
Plan the retest.
Fix randomisation unit, eligible population, MDE, power, duration, conversion lag and stopping rule in advance. Plan sample size from historical user-level net-revenue variability, not the tiny fixture or a binary conversion-rate formula.
Set a decision rule.
Estimate the intention-to-treat net-revenue difference and a suitable interval. Consider rollout only if the pre-specified evidence and economic threshold are met, validity checks pass and guardrails remain acceptable. Otherwise hold or redesign.
A rollout scenario is not realised impact.
Projected contribution over a stated horizon = eligible users × valid incremental net revenue per user × assumed contribution margin − incremental delivery costs. State each assumption and carry uncertainty through. Without a valid effect estimate, I would not attach a savings figure to this case.
This case concerns an experience A/B comparison. It does not establish advertising incrementality; that requires an appropriate no-tested-advertising holdout. See the incrementality walkthrough.
MY CONTRIBUTION & THE LIMITS
Make the reasoning
easy to inspect.
My assessment work focused on SQL defects, the rollout recommendation and the missing-buyer sensitivity. This public extension makes that reasoning reproducible.
The reconstructed data, code, checks and site copy were developed with ChatGPT assistance. They are inspectable artifacts, not evidence of an independently delivered client project. There is no claim of production deployment or measured commercial lift.
What I would investigate next
- Why the report filter retained B users more often.
- Whether orders can be reconciled for excluded users.
- Whether assignment crossover reflects logging or real exposure.
- Whether the original stopping plan and outcome window were valid.
- Whether refunds or repeated orders change the revenue story.
What this demonstrates
SQL window functions and joins, metric definitions, experiment validity, regression checks, sensitivity analysis and a bounded business recommendation.
REPRODUCE THE WORK
The numbers should
stand up to a check.
Download the pack and run python analysis.py. It uses Python’s standard library and SQLite, executes the checks and regenerates the results.
Why I chose this assessment and how I developed it
I compared the experiment diagnosis with a campaign-recovery assessment and a client-response scenario. The experiment case has the strongest combination of inspectable logic, numerical verification and a decision under uncertainty. The recovery deck offers strategic breadth, but its projected gains are not measured outcomes. The response scenario shows judgement, but lacks a reproducible dataset.
I used two public portfolios as structural references: a question-to-decision narrative and a downloadable, validated evidence pack. Their self-published claims were not independently verified, and no results or code were borrowed.
Method references: Microsoft Research on diagnosing sample-ratio mismatch and post-experiment analysis practices.
How I turn this into a digital maturity plan →