A/B test analysis of a new onboarding flow for SkillFlow, a fictional EdTech subscription product. Full pipeline: synthetic data generation → Excel → PostgreSQL/SQL → Python/Jupyter → statistics → visualization → business recommendation.
Result: the new onboarding significantly increases trial activation (+9.76% relative, p = 0.0005) without hurting downstream engagement. Decision: rollout.
SkillFlow lets users learn practical skills by subscription: Signup → Onboarding → 7-day free trial → Learning activity. This project simulates and analyzes an A/B test on the onboarding
step, end-to-end — synthetic data generation, validation, and independent analysis in three tools
(Excel, SQL, Python) to confirm the same conclusion every time.
SkillFlow wants more new users to activate the 7-day free trial after signing up.
Business question: will an interactive, personalized learning plan at the first onboarding step increase conversion to trial activation?
| Control (A) | Standard onboarding: pick 1–2 topics → general course catalog → "Try 7 days free" |
| Treatment (B) | 3 short questions (goal, time available, interest) → personalized weekly plan → "Activate my plan" |
| Unit of randomization | user_id — each user sees only one variant |
| Sample size | A: 20,061 users · B: 19,939 users · Total: 40,000 |
| Signup window | 2026-06-01 → 2026-06-23 |
| Observation window | through 2026-06-30 (up to 7 days per user for the D7 metric) |
40,000 users is a demonstration scale chosen for reproducibility and easy manual verification, not a claim about the sample size a real experiment would need.
| Metric | Formula | Purpose |
|---|---|---|
| Trial Conversion Rate (primary) | trials activated / signups | Does the new onboarding drive more trials? |
| Onboarding Drop-off Rate (guardrail) | (started − completed) / started | Is the new quiz too long or burdensome? |
| D7 Lesson Completion Rate (guardrail) | trial users with ≥1 lesson completed within 7 days / trial users | Are the extra trials made of engaged users? |
H0: p_A = p_B (no true difference in Trial Conversion Rate) vs. two-sided H1: p_A ≠ p_B, tested
at α = 0.05 set before looking at results.
Primary test: two-proportion z-test. Secondary check: chi-square test of independence.
- Generate a realistic synthetic dataset (
scripts/generate_data.py, seed 42): users, experiment assignment, onboarding events, subscriptions, learning activity. - Validate the data (
scripts/validate_data.py) — 51/51 integrity and business-rule checks pass (uniqueness, foreign keys, event ordering, allowed values, funnel logic). Seedocs/generation_report.md. - Analyze independently in three tools, each computing the funnel and the statistical test
from the same raw data:
-
Excel (
excel/skillflow_ab_test.xlsx) — COUNTIFS-based metrics, visible formula steps, dashboard.
-
SQL / PostgreSQL (
sql/) — table creation, validation, funnel, and segmentation queries. -
Python / Jupyter (
notebooks/ab_test_analysis.ipynb) — pandas for metrics;scipy/statsmodelsfor the z-test, chi-square test, and CI;matplotlibfor charts.
-
| Metric | Excel | SQL | Python |
|---|---|---|---|
| Users A / B | ✓ | ✓ | ✓ |
| CR A / CR B | ✓ | ✓ | ✓ |
| Effect (p.p.) | ✓ | optional | ✓ |
| Pooled proportion | ✓ | optional | ✓ |
| Standard error | ✓ | — | ✓ |
| Z-statistic | ✓ | — | ✓ |
| p-value | ✓ | — | ✓ |
| Confidence interval | ✓ | — | ✓ |
All three tools produced identical user counts, conversion rates, and effect sizes from the same raw data — SQL covers the funnel and pooled effect, while Excel and Python carry the full statistical test through to the p-value and confidence interval.
| Group A (control) | Group B (treatment) | |
|---|---|---|
| Signups | 20,061 | 19,939 |
| Trials activated | 2,342 | 2,555 |
| Conversion rate | 11.67% | 12.81% |
- Absolute effect: +1.14 percentage points
- Relative uplift: +9.76%
- Pooled proportion: 0.1224 · SE(H0): 0.00328
- Z = 3.477, p = 0.0005 (two-sided) — chi-square = 12.09, p = 0.0005 (confirms the z-test)
- 95% CI for the effect: [+0.50, +1.78] p.p.
At α = 0.05, p = 0.0005 < α, so H0 is rejected: the data are inconsistent with equal conversion rates in A and B. The 95% CI excludes 0, which agrees with that conclusion.
| Guardrail | Group A | Group B | Effect | p-value | Verdict |
|---|---|---|---|---|---|
| Onboarding Drop-off Rate | 8.17% | 8.74% | +0.58 p.p. | 0.04 | Small, statistically significant increase |
| D7 Lesson Completion Rate | 42.27% | 44.81% | +2.54 p.p. | 0.07 | Not statistically significant — did not deteriorate |
The onboarding drop-off is measured against everyone who started onboarding, but the Trial Conversion Rate is measured against everyone who signed up — so the extra drop-off is already "priced into" the conversion-rate result. The net effect on trial activation is still positive.
What it does:
- KPI tiles for the primary metric and both guardrails turn green / red depending on whether group B beats group A on the current selection (lower is better for drop-off).
- Filters (device, country, traffic source) apply to every chart, so the effect can be checked inside any segment — e.g. on tablets B is worse, a reminder that small segments are noisy.
- Segment parameter switches the breakdown between device, traffic source, country and learning goal.
- Funnel, guardrails and daily signups by group complete the picture.
The workbook is saved as tableau/skillflow_ab_test.twbx (data extract included).
Statistical conclusion: the treatment group showed a statistically significant increase in Trial Conversion Rate from 11.67% to 12.81% (effect = +1.14 p.p., p = 0.0005, α = 0.05, 95% CI [+0.50, +1.78] p.p.). Onboarding Drop-off increased from 8.17% to 8.74% (+0.58 p.p., p = 0.04), while D7 Lesson Completion moved from 42.27% to 44.81% (+2.54 p.p., p = 0.07, not significant).
Business conclusion: the new onboarding brings more users to the free trial — about +228 extra trials per 20,000 signups. The longer quiz makes slightly more people drop out during onboarding, but that loss is already included in the conversion-rate denominator, so the net effect is positive. Users who activate the trial under the new flow stay at least as active in their first 7 days as before.
Decision: Rollout. Ship the personalized onboarding to all new users, keep monitoring onboarding drop-off, and test a shorter quiz as the next iteration.
- Python / Jupyter — pandas, numpy, scipy.stats, statsmodels, matplotlib (
notebooks/ab_test_analysis.ipynb) - PostgreSQL / SQL — table design, validation, funnel and segmentation queries (
sql/) - Excel — COUNTIFS-based metrics, formula-driven statistical test, dashboard (
excel/skillflow_ab_test.xlsx) - Tableau Public — interactive dashboard with dynamic KPI tiles, global filters and a segment parameter (
tableau/)
skillflow-ab-testing/
├── README.md
├── data/raw/ # users, experiment_assignments, onboarding_events,
│ # subscriptions, learning_activity, + flat skillflow_ab_test.csv
├── docs/ # data_dictionary.md, data_generation_logic.md,
│ # generation_report.md (actual numbers, 51/51 checks passed)
├── scripts/
│ ├── generate_data.py # synthetic data generator, seed 42
│ └── validate_data.py # integrity + business-rule checks
├── excel/
│ └── skillflow_ab_test.xlsx
├── sql/
│ ├── 00_load_data.sql · 01_create_tables.sql · 02_data_validation.sql
│ └── 03_metrics.sql · 04_funnel.sql · 05_ab_test.sql · 06_segmentation.sql
├── notebooks/
│ └── ab_test_analysis.ipynb
├── tableau/
│ └── skillflow_ab_test.twbx # Tableau Public workbook (interactive dashboard)
└── images/
├── dashboard.png # Excel dashboard screenshot
├── tableau_dashboard.png # Tableau dashboard screenshot
├── conversion_rate.png · funnel.png · guardrails.png · confidence_interval.png
Reproducing the dataset (requires numpy, pandas; deterministic, SEED = 42):
python scripts/generate_data.py # writes the 6 CSV files into data/raw/
python scripts/validate_data.py # prints the validation report, saves docs/generation_report.md- All data is synthetic and internally consistent by construction — results demonstrate the method, not a real product effect. 40,000 users is a demonstration scale, not a claim about the sample size a real experiment would need.
- The signup window is 23 days in a single month; seasonality and novelty effects are not covered, and late signups have a truncated 7-day observation window (right-censoring).
- D7 Lesson Completion is the only downstream quality metric measured; long-term retention and revenue were not tracked.
- Segment-level results (e.g. by device or traffic source) are descriptive only — not individually significance-tested, and based on smaller samples.
- A p-value indicates how unusual the observed data would be if H0 were true — it is not the probability that H0 is true, and statistical significance is not automatically business significance.