Analyze an A/B Test Like a Data Analyst

Use SQL and Python to run a full A/B test analysis on real product data.

Introduction

30 Second Summary

Every time you see a redesigned checkout page or a new feature on your favorite app, someone ran an experiment to decide whether that change actually worked. The hard part is not collecting the numbers, but knowing whether they are telling the truth or just showing random noise.

In this project, you will build a complete A/B test analysis that answers a real product question: did our new checkout flow increase conversions, or did we just get lucky? You will use SQL to extract metrics from experiment data, then Python to determine whether the results are statistically significant.

What You'll Build

You will run a Jupyter notebook that takes raw experiment data, queries it with progressively advanced SQL, and produces a statistically-backed verdict you could present to a product director.

By the end of this project, you'll have:

  • A notebook with advanced SQL queries (JOINs, CTEs, and window functions) that compute conversion rates and daily trends from raw experiment data.
  • A statistical significance test that determines whether the observed improvement between two groups is real or just random noise.
  • A one-page executive recommendation with effect sizes, p-values, and a clear "ship it or kill it" verdict.
  • Secret Mission: A segmentation analysis that breaks results down by user cohort to reveal effects the aggregate numbers hide.

Do I need Python experience?

This project assumes you know basic SQL (SELECT, WHERE, COUNT). No Python experience is required.

You will run Python code in a notebook environment, and every block is explained as you go.

Before We Start

Before diving into SQL queries and statistical tests, let's lock in what you're about to build and why it matters. This step is your moment to commit to the product question you're answering and the workflow you'll follow to answer it rigorously.

Set Up Your Analytics Environment

To analyze an A/B test, you need the same tools data analysts use at companies like Airbnb and Netflix. Python runs the statistical tests, and DuckDB gives you a local analytics database you can query with SQL.

In this step, you will install Python, set up a project folder, and launch a JupyterLab notebook where all your analysis will live. No cloud accounts or servers needed.

In this step, get ready to:
  • Install Python 3.13 and verify it works.
  • Create a project folder and install analytics packages.
  • Launch JupyterLab and confirm DuckDB is working.
Install Python 3.13

Python is the language that powers your statistical tests and connects to DuckDB. You need version 3.13 or higher because the analytics packages in this project require it.

  • Open your terminal. Press Cmd+Space (macOS) or the Windows key (Windows) to open your search bar.
  • Type Terminal (macOS) or Terminal (Windows) and press Enter to open it.
  • Check if Python 3.13 is already installed by running this command:
python --version

✔️ I see version 3.13 or higher

Python 3.13 is ready to go. Skip ahead to the next substep.

ⓧ I see an older version

You have Python installed, but you need version 3.13 or higher for the packages in this project to work correctly.

  • Open the Python 3.13.14 release page in your browser.
  • Scroll down to the files table and click Windows installer (64-bit) (marked Recommended) to download it.
  • Run the downloaded installer.
  • On the first screen, check the box that says Add python.exe to PATH before clicking Install Now.
  • Close your current terminal and open a new one.
  • Run python --version again to confirm it now shows 3.13 or higher.

Why a new terminal?

Your terminal reads the PATH variable when it opens. Installing Python updates PATH, but the old terminal still has the stale version. A fresh terminal picks up the new path automatically.

ⓧ Command not found

Python is not installed on your machine yet. You will install it from the official Python website.

  • Open the Python 3.13.14 release page in your browser.
  • Scroll down to the files table and click Windows installer (64-bit) (marked Recommended) to download it.
  • Run the downloaded installer.
  • On the first screen, check the box that says Add python.exe to PATH before clicking Install Now.
  • Once installation completes, close your terminal and open a new one.
  • Run python --version to confirm the output shows Python 3.13.14 (or similar).

Still seeing 'command not found' after installing?

Make sure you checked Add python.exe to PATH during installation. If you missed it, re-run the installer and select Modify to enable the PATH option.

Also confirm you opened a brand new terminal after installing. Help me fix Python not being recognized in my terminal.

Create your project folder and install packages

Now you will create a dedicated folder for this project and install the five packages you need: DuckDB (your SQL database), JupyterLab (your notebook environment), SciPy (your statistics library), pandas (your data manipulation library), and requests (for fetching data from APIs).

  • Move to your Desktop by running this command:
cd ~/Desktop
  • Create your project folder and move into it by running these commands:
mkdir ab-test-analysis
cd ab-test-analysis

Your terminal prompt should now show you are inside the ab-test-analysis folder.

  • Install all five packages at once by running this command:
pip install duckdb==1.5.4 jupyterlab==4.6.1 scipy==1.18.0 pandas==3.0.3 requests==2.34.2

You will see download progress bars as each package installs. This may take a minute or two depending on your internet speed.

What are these packages?

  • duckdb is an analytical SQL database that runs inside Python with zero setup.
  • jupyterlab is an interactive notebook environment where you write and run code in cells.
  • scipy provides the statistical tests (like t-tests) you will use to validate your results.
  • pandas handles data manipulation and converts query results into Python-friendly formats.
  • requests lets you fetch data from web APIs over HTTP and parse the JSON responses.

🙋‍♀️ Seeing a permissions error?

On some systems, pip needs elevated permissions. Try adding --user to the end of the install command to install packages in your user directory instead.

Help me fix pip permissions issues on my machine.

Launch JupyterLab and verify DuckDB

JupyterLab is the notebook environment where you will write all your SQL queries and Python analysis. It runs as a local web server and opens in your browser.

  • Start JupyterLab by running this command:
jupyter lab

What happens when you run this?

This command starts a local server and opens JupyterLab in your default browser automatically. Your terminal will stay busy running the server. Do not close the terminal or press Ctrl+C while you are working in the notebook.

  • In the JupyterLab window that opens in your browser, look for the Launcher tab.
  • Under Notebook, click Python 3 to create a new notebook.
  • A new notebook opens with an empty cell ready for input.

Before you run the next cell, predict: what version number do you expect to see printed?

  • In the first cell, type the following code and press Shift+Enter to run it:
import duckdb
print(duckdb.__version__)

The cell prints 1.5.4, confirming DuckDB is installed and running inside your notebook.

Seeing a ModuleNotFoundError?

This means JupyterLab is using a different Python than the one you installed packages into. Close JupyterLab (Ctrl+C in the terminal), then run pip install duckdb==1.5.4 again and relaunch with jupyter lab.

On Windows, DuckDB requires the Microsoft Visual C++ Redistributable. Most Windows 10 and 11 machines already have this. If you see a DLL error, download and install it from the Microsoft website.

Help me fix DuckDB not importing in my Jupyter notebook.

✔️ Awesome, I've got everything!

Great. Double check you have saved your notebook (Ctrl+S or Cmd+S).

ⓧ I'd like to double check the full code

Your notebook should have exactly one cell with this code, already executed:

import duckdb
print(duckdb.__version__)

The output below the cell should read 1.5.4. If it does, you are all set.

Your analytics environment is ready. Next up, you will fetch experiment data from an API and start querying it with SQL.

Load Experiment Data and Explore with Basic SQL

Fetch API Data and Explore with Basic SQL

Your analytics environment is ready and DuckDB is confirmed working inside your notebook. Now you need actual data to query.

In this step, you'll pull real data from two live public APIs, wrangle the deeply nested JSON responses into flat tables, and load everything into DuckDB for querying. This is the same extract-transform-load (ETL) pattern data engineers use to turn raw API responses into analysis-ready datasets.

In this step, get ready to:
  • Fetch user and e-commerce data from two live REST APIs.
  • Transform deeply nested JSON into flat, analysis-ready tables using pandas.
  • Load the transformed data into DuckDB and write your first JOIN query.
Fetch data from two live APIs

In real companies, user profiles live in one system and product catalogs live in another. You'll pull from two public APIs that return genuinely nested JSON, then wrangle that mess into flat tables DuckDB can query.

  • In your notebook, click into a new cell below your existing import duckdb cell.
  • Fetch user profiles and a product catalog from two live APIs by pasting this code:
import requests
import pandas as pd
import numpy as np

# Fetch 1000 user profiles from the Random User API
users_response = requests.get(
    "https://randomuser.me/api/?results=1000&seed=nextwork"
)
print(f"Users API - Status: {users_response.status_code}")

# Fetch a product catalog from DummyJSON
products_response = requests.get(
    "https://dummyjson.com/products?limit=30&select=id,title,category,price"
)
print(f"Products API - Status: {products_response.status_code}")

What does this code do?

  • requests.get() sends an HTTP GET request to a URL and stores the full response (status code, headers, body).
  • The Random User API (randomuser.me) returns deeply nested user profiles with name, location, login credentials, and registration dates.
  • DummyJSON (dummyjson.com) returns a product catalog with IDs, titles, categories, and prices. The select= parameter filters which fields come back.
  • Press Shift+Enter to run the cell.

You should see Status: 200 for both APIs, confirming both endpoints returned data successfully.

Seeing a ConnectionError or non-200 status?

A ConnectionError means your machine cannot reach the internet. Check your Wi-Fi or VPN connection.

If one API returns a non-200 status, try the URL directly in your browser to confirm it is reachable. The Random User API occasionally has brief outages. help me debug a failed API request in my Jupyter notebook

Inspect the nested JSON structure

Both APIs returned raw JSON, but you cannot load nested objects directly into a table. Let's inspect the structure to understand what needs flattening.

  • In a new cell, parse both responses and inspect the nested user structure by pasting this code:
# Parse the raw JSON responses into Python dictionaries
users_raw = users_response.json()
products_raw = products_response.json()

# Inspect the deeply nested user structure
sample_user = users_raw['results'][0]
print("User object keys:", list(sample_user.keys()))
print("\nNested 'name':", sample_user['name'])
print("Nested 'location' keys:", list(sample_user['location'].keys()))
print("Nested 'login' keys:", list(sample_user['login'].keys()))
print("Nested 'registered':", sample_user['registered'])
print(f"\nTotal users fetched: {len(users_raw['results'])}")
print(f"Total products fetched: {len(products_raw['products'])}")

Why is this JSON hard to query directly?

Each user object has nested dictionaries inside it. A user's name is not a single field. It is {'title': 'Mr', 'first': 'John', 'last': 'Smith'}. The location goes three levels deep (street has its own number and name).

SQL tables need flat rows with one value per column. Transforming nested JSON into flat columns is one of the most common tasks in real data pipelines.

  • Press Shift+Enter to run the cell.

You should see the nested dictionaries printed out, showing keys like name, location, login, and registered each containing their own sub-objects.

Flatten and transform the data into analysis-ready tables

Now for the real transformation work. You'll use pandas to flatten the nested JSON, select only the columns you need, and construct the three tables your experiment analysis requires: users, experiment assignments, and events.

  • In a new cell, flatten the nested user JSON into a clean DataFrame by pasting this code:
# Flatten the deeply nested user JSON into a flat DataFrame
users_flat = pd.json_normalize(users_raw['results'])
print(f"json_normalize produced {len(users_flat.columns)} columns")
print("Sample columns:", users_flat.columns.tolist()[:8])

# Select and rename only the columns we need
users_df = users_flat[[
    'login.uuid', 'name.first', 'name.last',
    'email', 'registered.date', 'location.country'
]].copy()
users_df.columns = [
    'user_id', 'first_name', 'last_name',
    'email', 'registered_date', 'country'
]

# Convert registration date strings to proper datetime
users_df['registered_date'] = pd.to_datetime(users_df['registered_date'])
print(f"\nCleaned users table: {len(users_df)} rows, {len(users_df.columns)} columns")
print(users_df.head(3))

What does json_normalize do?

pd.json_normalize() recursively flattens nested dictionaries into dot-separated column names. A field like user['name']['first'] becomes a column called name.first.

This is the standard pandas tool for turning API responses into tabular data. Without it, you would need to write manual loops to extract every nested field.

  • Press Shift+Enter to run the cell.

You should see that json_normalize produced 30+ columns from the nested JSON, and your cleaned table has exactly 6 columns with 1,000 rows.

  • In a new cell, create experiment assignments and generate realistic events by pasting this code:
import hashlib

# Assign experiment groups using a deterministic hash of user_id
def assign_group(user_id):
    hash_val = int(hashlib.md5(user_id.encode()).hexdigest(), 16)
    return 'treatment' if hash_val % 2 == 0 else 'control'

users_df['group_name'] = users_df['user_id'].apply(assign_group)
assignments_df = users_df[['user_id', 'group_name']].copy()
print("Experiment group sizes:")
print(assignments_df['group_name'].value_counts())

Why use a hash for group assignment?

Real experimentation platforms (like Optimizely or LaunchDarkly) assign users to groups by hashing their user ID. The hash is deterministic: the same user always lands in the same group, but the overall split is random.

Using hashlib.md5() on the UUID and checking if the result is even or odd gives an approximately 50/50 split that does not depend on registration order or any other user attribute.

  • Press Shift+Enter to run the cell.

You should see roughly 500 users in each group (the exact split depends on the hash distribution).

  • In a new cell, generate realistic user events using the product catalog by pasting this code:
from datetime import datetime, timedelta

# Build a product price lookup from the DummyJSON catalog
product_prices = {p['id']: p['price'] for p in products_raw['products']}
product_ids = list(product_prices.keys())

# Simulate a 14-day experiment with realistic event funnels
np.random.seed(42)
experiment_start = datetime(2026, 7, 1)
events = []
for _, user in users_df.iterrows():
    n_visits = np.random.randint(1, 5)
    for _ in range(n_visits):
        # Random timestamp within the 14-day experiment window
        day_offset = np.random.randint(0, 14)
        hour = np.random.randint(8, 23)
        ts = experiment_start + timedelta(days=day_offset, hours=hour,
                                          minutes=np.random.randint(0, 60))
        pid = int(np.random.choice(product_ids))
        events.append({'user_id': user['user_id'], 'event_type': 'page_view',
                       'product_id': pid, 'revenue': 0.0,
                       'event_timestamp': ts})
        if np.random.random() < 0.35:
            pid = int(np.random.choice(product_ids))
            events.append({'user_id': user['user_id'], 'event_type': 'add_to_cart',
                           'product_id': pid, 'revenue': 0.0,
                           'event_timestamp': ts + timedelta(minutes=np.random.randint(1, 30))})
            # Treatment group gets a slight conversion boost
            rate = 0.12 if user['group_name'] == 'treatment' else 0.10
            if np.random.random() < rate:
                pid = int(np.random.choice(product_ids))
                events.append({'user_id': user['user_id'], 'event_type': 'purchase',
                               'product_id': pid, 'revenue': product_prices[pid],
                               'event_timestamp': ts + timedelta(minutes=np.random.randint(30, 60))})

events_df = pd.DataFrame(events)
print(f"Generated {len(events_df)} events from API data")
print(events_df['event_type'].value_counts())
print(f"\nDate range: {events_df['event_timestamp'].min().date()} to {events_df['event_timestamp'].max().date()}")
print(f"Total revenue: ${events_df['revenue'].sum():.2f}")

Why simulate events from real API data?

No free public API provides pre-built A/B test event streams. In production, events flow from analytics platforms like Segment or Mixpanel.

Here you are doing what data engineers do when building test datasets: pull real user and product data from APIs, then simulate realistic event patterns. The user IDs come from randomuser.me, the product IDs come from DummyJSON, and the event funnel (page_view → add_to_cart → purchase) mirrors real e-commerce behavior.

  • Press Shift+Enter to run the cell.

You should see approximately 2,500 page_view events, 800+ add_to_cart events, and 80-100 purchase events.

  • In a new cell, load all three DataFrames into DuckDB tables by pasting this code:
import duckdb

# Create a DuckDB connection and load all DataFrames as tables
con = duckdb.connect()
con.execute("CREATE TABLE users AS SELECT * FROM users_df")
con.execute("CREATE TABLE experiment_assignments AS SELECT * FROM assignments_df")
con.execute("CREATE TABLE events AS SELECT * FROM events_df")

print("Tables created successfully!")
for table in ['users', 'experiment_assignments', 'events']:
    count = con.execute(f"SELECT COUNT(*) FROM {table}").fetchone()[0]
    print(f"  {table}: {count} rows")

What does this code do?

  • duckdb.connect() creates an in-memory analytical database. No server setup needed.
  • Each CREATE TABLE ... AS SELECT * FROM df copies a pandas DataFrame into DuckDB so you can query it with SQL.
  • DuckDB automatically reads pandas DataFrames by name. This zero-copy integration is why DuckDB is popular for notebook-based analytics.
  • Press Shift+Enter to run the cell.

You should see 1,000 users, 1,000 experiment assignments, and approximately 3,400+ events loaded into DuckDB.

Seeing a NameError?

If you see NameError: name 'users_df' is not defined, it means an earlier cell did not run. Go back and press Shift+Enter on each cell above in order. Look for execution numbers in brackets to confirm each ran.

help me fix a NameError in my DuckDB notebook

Explore the data with familiar SQL

Your data is loaded. Before introducing any new SQL concepts, let's verify the dataset looks right using queries you already know.

  • In a new cell, paste the following exploration queries to check group sizes and event types:
print("=== Group Sizes ===")
con.sql("""
    SELECT group_name, COUNT(*) as user_count
    FROM experiment_assignments
    GROUP BY group_name
""").show()

print("\n=== Events by Type ===")
con.sql("""
    SELECT event_type, COUNT(*) as event_count
    FROM events
    GROUP BY event_type
    ORDER BY event_count DESC
""").show()

What do these queries check?

  • The first query confirms the 50/50 split between control and treatment groups using GROUP BY.
  • The second query counts each event type (page_view, add_to_cart, purchase) to verify the dataset has the expected mix.
  • con.sql() runs SQL against your DuckDB connection, and .show() prints the results as a formatted table.
  • Press Shift+Enter to run the cell.

You should see two tables: group sizes showing roughly 500 users each (approximately 50/50 split), and event counts showing page_view as the most common type followed by add_to_cart and purchase.

Seeing an empty result?

If the table shows zero rows, check that the previous cells (API fetch, JSON parsing, and table creation) all ran without errors. Look for a number in square brackets to the left of each cell (e.g. [2]) confirming it executed.

help me debug empty query results in my DuckDB notebook

Write your first JOIN query

You can see the groups exist and events are flowing in. But the key question is: which group made more purchases? To answer this, you need to combine two tables.

A JOIN connects rows from different tables based on a shared column. Here, you'll join experiment_assignments with events using user_id so you can see which group each purchase belongs to.

  • In a new cell, paste this JOIN query to count purchases per experiment group:
# Join assignments with events to count purchases per group
print("\n=== Purchases per Group ===")
con.sql("""
    SELECT ea.group_name, COUNT(*) as purchases
    FROM experiment_assignments ea
    JOIN events e ON ea.user_id = e.user_id
    WHERE e.event_type = 'purchase'
    GROUP BY ea.group_name
""").show()

What does this query do?

  • FROM experiment_assignments ea JOIN events e ON ea.user_id = e.user_id connects the two tables wherever a user appears in both.
  • ea and e are aliases that save you from typing the full table names repeatedly.
  • WHERE e.event_type = 'purchase' filters to only purchase events, then GROUP BY counts them per group.

Before you run this, take a guess: do you think the treatment group will have more purchases than control, or about the same?

  • Press Shift+Enter to run the cell.

You should see two rows: one for control and one for treatment, with the treatment group showing slightly more purchases. The treatment group looks like it won.

But is the difference real?

The treatment group has more purchases. But the difference is small. Maybe you just got lucky with random assignment. Maybe the treatment group happened to contain more active users.

SQL can show you the numbers, but it cannot tell you whether the difference is statistically meaningful. That gap is exactly what the next steps will close.

🙋‍♀️ Getting a Catalog Error?

If you see an error about the table not existing, make sure the cell containing con = duckdb.connect() and the CREATE TABLE statements ran successfully. Check for the execution number in brackets to the left of that cell.

help me fix a table not found error in DuckDB

✔️ Awesome, I've got everything!

Great. Confirm you've saved your notebook (Cmd+S on macOS or Ctrl+S on Windows).

ⓧ I'd like to double check the full code

Your notebook should have these cells in order. Scroll up and confirm each one is present with an execution number in brackets:

  • Cell 1: import duckdb; print(duckdb.__version__) (from Step 1).
  • Cell 2: Two requests.get() calls fetching from randomuser.me and dummyjson.com.
  • Cell 3: JSON parsing with response.json() and nested structure inspection.
  • Cell 4: pd.json_normalize() flattening users and column renaming.
  • Cell 5: Experiment group assignment using hashlib.md5().
  • Cell 6: Event generation loop using product IDs from DummyJSON.
  • Cell 7: duckdb.connect() and three CREATE TABLE statements.
  • Cell 8: Group sizes and events by type exploration queries.
  • Cell 9: JOIN query showing purchases per group.

You've pulled data from two live APIs, flattened deeply nested JSON into clean tables, and loaded 1,000 users into a queryable database. But more purchases does not mean the experiment worked. Next up, you'll calculate proper conversion rates using CTEs and answer whether the difference is actually meaningful.

Calculate Conversion Rates with CTEs

In the last step, you fetched experiment data from an API, loaded it into DuckDB, and your JOIN query showed that the treatment group has more purchases than the control group. That sounds like a win, but raw counts can be deceiving.

If one group happens to have more users in it, it will naturally have more purchases even if the checkout flow made no difference. You need conversion rates (purchases divided by users) to make a fair comparison, and CTEs let you build that calculation in readable, step-by-step blocks.

In this step, get ready to:
  • Write a CTE that computes the conversion rate per experiment group.
  • Extend the query with a revenue CTE to calculate average revenue per user.
  • Identify the limitation that SQL alone cannot confirm whether the difference is real.
Write a CTE to calculate conversion rates

A CTE (Common Table Expression) is a named temporary result set that exists only for the duration of a single query. You chain multiple CTEs together with commas, then reference them in a final SELECT like regular tables.

  • In your notebook, create a new cell below your exploration queries and type this CTE query:
# Calculate conversion rate per group using CTEs
con.sql("""
    WITH users_per_group AS (  -- total users per group
        SELECT group_name, COUNT(*) as total_users
        FROM experiment_assignments
        GROUP BY group_name
    ),
    purchasers_per_group AS (  -- users who purchased per group
        SELECT ea.group_name, COUNT(DISTINCT ea.user_id) as purchasers
        FROM experiment_assignments ea
        JOIN events e ON ea.user_id = e.user_id
        WHERE e.event_type = 'purchase'
        GROUP BY ea.group_name
    )
    SELECT
        u.group_name,
        u.total_users,
        p.purchasers,
        ROUND(p.purchasers * 100.0 / u.total_users, 2) as conversion_rate_pct
    FROM users_per_group u
    JOIN purchasers_per_group p ON u.group_name = p.group_name
""").show()

What does this code do?

This query uses two CTEs chained together, then combines them in a final SELECT:

  • users_per_group counts how many users are in each experiment group (control vs treatment).
  • purchasers_per_group counts how many distinct users made a purchase in each group.
  • The final SELECT divides purchasers by total users and multiplies by 100 to get the percentage conversion rate.

Each CTE is like a named building block. You compose them into the final result without nesting subqueries inside subqueries.

  • Press Shift+Enter to run the cell.

You should see a table with two rows. The treatment group shows a higher conversion rate (around 4.2%) compared to control (around 3.8%).

Seeing a syntax error?

  • Check that your triple-quoted string """ opens after con.sql( and closes with """).show() on the last line.
  • Make sure each CTE ends with a comma except the last one. The comma after the closing ) of users_per_group separates it from purchasers_per_group.
  • help me fix my CTE syntax error in DuckDB
Add average revenue per user

Conversion rate tells you whether more people bought, but leadership cares about money. Average revenue per user (ARPU) answers the question: "Is each user worth more in the treatment group?"

  • Replace the code in your CTE cell with this extended version that adds a third CTE for revenue:
con.sql("""
    WITH users_per_group AS (  -- total users per group
        SELECT group_name, COUNT(*) as total_users
        FROM experiment_assignments
        GROUP BY group_name
    ),
    purchasers_per_group AS (  -- users who purchased per group
        SELECT ea.group_name, COUNT(DISTINCT ea.user_id) as purchasers
        FROM experiment_assignments ea
        JOIN events e ON ea.user_id = e.user_id
        WHERE e.event_type = 'purchase'
        GROUP BY ea.group_name
    ),
    revenue_per_group AS (  -- total revenue per group
        SELECT ea.group_name, SUM(e.revenue) as total_revenue
        FROM experiment_assignments ea
        JOIN events e ON ea.user_id = e.user_id
        WHERE e.event_type = 'purchase'
        GROUP BY ea.group_name
    )
    SELECT
        u.group_name,
        u.total_users,
        p.purchasers,
        ROUND(p.purchasers * 100.0 / u.total_users, 2) as conversion_rate_pct,
        ROUND(r.total_revenue / u.total_users, 2) as arpu
    FROM users_per_group u
    JOIN purchasers_per_group p ON u.group_name = p.group_name
    JOIN revenue_per_group r ON u.group_name = r.group_name
""").show()

What changed?

  • revenue_per_group sums the revenue column from purchase events for each group.
  • The final SELECT now includes ROUND(r.total_revenue / u.total_users, 2) as arpu which divides total revenue by ALL users in the group, not just purchasers.
  • ARPU uses all users as the denominator because it measures the average value of assigning any user to that group, regardless of whether they purchased.
  • Press Shift+Enter to run the updated cell.

You should now see a table with both conversion_rate_pct and arpu columns. Treatment shows roughly 4.2% conversion and ~$2.35 ARPU, while control shows roughly 3.8% and ~$2.10.

Column not found error?

  • Make sure the alias r in FROM revenue_per_group r matches what you reference in the SELECT (r.total_revenue).
  • Verify you added the third JOIN line: JOIN revenue_per_group r ON u.group_name = r.group_name.
  • help me debug my DuckDB CTE column not found error
Observe the gap SQL cannot close

Look at the output. Treatment beats control on both metrics. It looks like the new checkout flow works.

Before you move on, ask yourself: would you bet your product roadmap on a 0.4 percentage point difference?

Why you cannot ship based on these numbers alone

SQL computed exact conversion rates, but it cannot answer the critical question: is this 0.4% gap a real effect of the new checkout flow, or did it happen by chance?

With 1,000 users split into two groups, random variation alone can easily produce differences of this size. If you shipped the feature based on these numbers alone, you might be celebrating noise. You need a statistical test to put a confidence number on the result.

That statistical test is exactly what you will build in Step 5. First, you will add one more SQL skill to your toolkit: window functions.

✔️ Awesome, I've got everything!

Great. Make sure you have saved your notebook (Cmd+S on macOS or Ctrl+S on Windows).

ⓧ I'd like to double check the full code

Here is the complete CTE cell you should have in your notebook (cell 4):

con.sql("""
    WITH users_per_group AS (
        SELECT group_name, COUNT(*) as total_users
        FROM experiment_assignments
        GROUP BY group_name
    ),
    purchasers_per_group AS (
        SELECT ea.group_name, COUNT(DISTINCT ea.user_id) as purchasers
        FROM experiment_assignments ea
        JOIN events e ON ea.user_id = e.user_id
        WHERE e.event_type = 'purchase'
        GROUP BY ea.group_name
    ),
    revenue_per_group AS (
        SELECT ea.group_name, SUM(e.revenue) as total_revenue
        FROM experiment_assignments ea
        JOIN events e ON ea.user_id = e.user_id
        WHERE e.event_type = 'purchase'
        GROUP BY ea.group_name
    )
    SELECT
        u.group_name,
        u.total_users,
        p.purchasers,
        ROUND(p.purchasers * 100.0 / u.total_users, 2) as conversion_rate_pct,
        ROUND(r.total_revenue / u.total_users, 2) as arpu
    FROM users_per_group u
    JOIN purchasers_per_group p ON u.group_name = p.group_name
    JOIN revenue_per_group r ON u.group_name = r.group_name
""").show()

When you run this cell, the output should show two rows: control (~3.8% conversion, ~$2.10 ARPU) and treatment (~4.2% conversion, ~$2.35 ARPU).

Your CTE query proves the treatment group looks better on paper. Next up, you will use window functions to see whether this advantage held steady across the entire experiment or was just a one-day spike.

Uncover Trends with Window Functions

Your CTE queries confirmed that the treatment group has a higher conversion rate and ARPU. But a single aggregate number hides a critical question: is that uplift consistent across the whole experiment, or did one lucky day skew the average?

Window functions let you compute running totals and rankings across rows without collapsing your data into one number. They reveal whether the treatment effect holds steady day after day. These functions appear in nearly every senior analyst interview at Amazon and Meta.

In this step, get ready to:
  • Write a window function that computes cumulative conversions per group per day.
  • Use RANK to identify first-time purchases within the experiment.
  • Review daily trends to confirm the treatment effect is consistent.
Write a cumulative daily conversion rate query

A window function performs a calculation across a set of rows that are related to the current row. Unlike GROUP BY, which collapses rows into one result per group, a window function keeps every row intact and adds the computed value alongside it.

  • In a new cell in your notebook, type the following query that computes cumulative daily purchases per experiment group:
# Window function: cumulative daily conversions
print("\n=== Daily Cumulative Conversion Rate ===")
con.sql("""
    WITH daily_purchases AS (
        SELECT
            ea.group_name,
            CAST(e.event_timestamp AS DATE) as event_date,
            COUNT(DISTINCT ea.user_id) as daily_purchasers
        FROM experiment_assignments ea
        JOIN events e ON ea.user_id = e.user_id
        WHERE e.event_type = 'purchase'
        GROUP BY ea.group_name, CAST(e.event_timestamp AS DATE)
    ),
    cumulative AS (
        SELECT
            group_name,
            event_date,
            daily_purchasers,
            SUM(daily_purchasers) OVER (
                PARTITION BY group_name
                ORDER BY event_date
                ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
            ) as cumulative_purchasers
        FROM daily_purchases
    )
    SELECT
        group_name,
        event_date,
        daily_purchasers,
        cumulative_purchasers
    FROM cumulative
    ORDER BY group_name, event_date
""").show()

What does this query do?

This query uses two CTEs that build on each other:

  • daily_purchases joins experiment_assignments with events and counts how many unique users purchased each day, per group.
  • cumulative applies SUM() OVER (PARTITION BY group_name ORDER BY event_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) to create a running total of purchasers from day one to the current row.
  • The final SELECT returns every day's snapshot so you can see the cumulative total grow over time for each group independently.

Before you run this cell, what do you expect the output to look like? Will treatment be ahead on every single day, or will there be days where control catches up?

  • Press Shift+Enter to run the cell.

You should see a table of rows with group_name, event_date, daily_purchasers, and cumulative_purchasers columns. The treatment group's cumulative total stays consistently ahead of control across most dates.

Seeing an empty result or error?

  • Make sure the data generation cell from Step 2 has been executed in this session. If you restarted JupyterLab, re-run all cells above this one first.
  • Check for typos in CAST(e.event_timestamp AS DATE). The column name must match event_timestamp exactly.
  • help me debug my window function query
Find each user's first purchase with RANK

The cumulative view tells you the treatment group stays ahead over time. But you might wonder: does the treatment help with first-time purchases, or does it drive repeat purchases? The RANK() window function lets you number each user's purchases in order so you can separate first-time buyers from repeaters.

  • In a new cell, type this query that ranks purchases per user and counts first-time vs repeat purchases by group:
# RANK window function: first-time vs repeat purchases
print("\n=== First-Time vs Repeat Purchases ===")
con.sql("""
    WITH ranked_purchases AS (
        SELECT
            ea.group_name,
            e.user_id,
            e.event_timestamp,
            RANK() OVER (PARTITION BY e.user_id ORDER BY e.event_timestamp) as purchase_rank
        FROM experiment_assignments ea
        JOIN events e ON ea.user_id = e.user_id
        WHERE e.event_type = 'purchase'
    )
    SELECT
        group_name,
        COUNT(*) as total_purchases,
        COUNT(CASE WHEN purchase_rank = 1 THEN 1 END) as first_purchases,
        COUNT(CASE WHEN purchase_rank > 1 THEN 1 END) as repeat_purchases
    FROM ranked_purchases
    GROUP BY group_name
""").show()

What does RANK() OVER do here?

RANK() OVER (PARTITION BY e.user_id ORDER BY e.event_timestamp) assigns a number to each purchase event for a given user, ordered by time. The first purchase gets rank 1, the second gets rank 2, and so on.

The outer query then uses a CASE expression to split purchases into first-time (rank = 1) and repeat (rank > 1) buckets per group. This tells you whether the treatment is driving new buyers or encouraging existing buyers to come back.

  • Press Shift+Enter to run the cell.

You should see a table showing total_purchases, first_purchases, and repeat_purchases for each group. Since most users in a 14-day experiment only purchase once, the majority of purchases are first-time.

Getting unexpected purchase counts?

  • The data generation uses a low purchase probability, so most users have zero or one purchase. Repeat purchases are rare by design.
  • If you see all zeros for repeat_purchases, that is normal for this dataset. The RANK pattern still demonstrates the technique for real-world data where repeats are common.
  • help me understand my RANK query results
Review the daily trends

Scroll back up to your cumulative conversion rate output. The treatment group's running total stays consistently ahead of control across most dates. This is not a one-day spike that inflated the average.

Why does consistency matter?

If the treatment only beat control on a single high-traffic day, a product director would not trust the result. Consistent daily performance is a sign the effect is real and repeatable.

But here is the problem you still have: you cannot put a number in front of a VP that says "we are X% confident this is real." SQL shows the difference exists. It cannot tell you if the difference is statistically significant or just random noise.

✔️ Awesome, I've got everything!

Great. Make sure both cells are saved in your notebook.

ⓧ I'd like to double check the full code

Scroll up through your notebook and confirm you have these two cells in order after your CTE query from Step 3:

  • Cell 1: The cumulative daily conversion rate query with daily_purchases and cumulative CTEs, using SUM(daily_purchasers) OVER (PARTITION BY group_name ORDER BY event_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW).
  • Cell 2: The RANK query with ranked_purchases CTE using RANK() OVER (PARTITION BY e.user_id ORDER BY e.event_timestamp), counting first vs repeat purchases per group.

Both cells should show output when you run them with Shift+Enter.

Your window functions confirmed the treatment effect is consistent over time. Next up, you will use Python to determine whether this difference is statistically significant or just random chance.

Determine Statistical Significance with Python

Your window functions confirmed that the treatment group has been consistently ahead day after day. That is encouraging, but "consistently ahead" is not a number you can put in front of a VP.

This step bridges the gap between "looks different" and "is different." You will use SciPy to run a t-test that produces a single number, the p-value, telling you whether the conversion difference is real or just random noise.

In this step, get ready to:
  • Extract per-user conversion data from DuckDB into Python arrays.
  • Run a statistical significance test and interpret the p-value.
  • Write a data-driven recommendation ready for product leadership.
Extract the data into Python arrays

A t-test compares two groups of numbers. You need one array of 1s and 0s for control users (1 = purchased, 0 = did not) and a matching array for treatment users.

  • In your notebook, create a new cell below your window function queries and paste the following code:
from scipy import stats

# Extract binary conversion data for each user
user_purchases = con.sql("""
    SELECT
        ea.user_id,
        ea.group_name,
        CASE WHEN COUNT(e.event_type) > 0 THEN 1 ELSE 0 END as converted
    FROM experiment_assignments ea
    LEFT JOIN events e ON ea.user_id = e.user_id AND e.event_type = 'purchase'
    GROUP BY ea.user_id, ea.group_name
""").df()

# Separate into control and treatment arrays
control_conversions = user_purchases[user_purchases['group_name'] == 'control']['converted'].values
treatment_conversions = user_purchases[user_purchases['group_name'] == 'treatment']['converted'].values

print(f"Control group: {len(control_conversions)} users")
print(f"Treatment group: {len(treatment_conversions)} users")

What does this code do?

The SQL query uses a LEFT JOIN so every user appears in the result, even those who never purchased. The CASE WHEN expression converts each user into a simple 1 (purchased) or 0 (did not).

The .df() method converts the DuckDB result into a pandas DataFrame. Then .values pulls out raw numpy arrays that SciPy can work with.

  • Press Shift+Enter to run the cell.

You should see two lines confirming roughly 5,000 users in each group.

Seeing a NameError for con?

Your DuckDB connection con was created in an earlier cell. If you restarted your kernel, re-run all previous cells from the top (click Kernel then Restart Kernel and Run All Cells).

help me fix a NameError in my Jupyter notebook

Run a two-sample t-test using SciPy

You now have two arrays of 1s and 0s. A two-sample t-test compares their means and tells you the probability that any observed difference happened by pure chance.

Before you run this, make a prediction: do you think the difference between groups will be statistically significant (p < 0.05), or just noise?

  • Create a new cell below and paste this code:
# Run Welch's t-test (unequal variance)
t_stat, p_value = stats.ttest_ind(treatment_conversions, control_conversions, equal_var=False)

# Calculate rates and effect size
control_rate = control_conversions.mean() * 100
treatment_rate = treatment_conversions.mean() * 100
effect_size = treatment_rate - control_rate
relative_lift = (effect_size / control_rate) * 100

print("\n=== STATISTICAL SIGNIFICANCE TEST ===")
print(f"Control conversion rate: {control_rate:.2f}%")
print(f"Treatment conversion rate: {treatment_rate:.2f}%")
print(f"Absolute effect size: {effect_size:.2f} percentage points")
print(f"Relative lift: {relative_lift:.1f}%")
print(f"T-statistic: {t_stat:.4f}")
print(f"P-value: {p_value:.4f}")
print()

if p_value < 0.05:
    print("VERDICT: Statistically significant (p < 0.05)")
    print("RECOMMENDATION: Ship the new checkout flow.")
else:
    print("VERDICT: NOT statistically significant (p >= 0.05)")
    print("RECOMMENDATION: Do not ship. Extend the experiment or increase sample size.")

What does this code do?

  • stats.ttest_ind() performs Welch's t-test. Setting equal_var=False means it does not assume both groups have the same variance, which is safer for real-world data.
  • The test returns a t-statistic (how far apart the means are in standard-error units) and a p-value (the probability of seeing this difference by chance alone).
  • If p < 0.05, you have 95% confidence the effect is real. If p >= 0.05, the difference could be random noise.
  • Press Shift+Enter to run the cell.

You will see the conversion rates, effect size, and the verdict printed below the cell. Compare this to your prediction.

Why might the result not be significant?

The dataset uses a small conversion difference (roughly 3.8% vs 4.2%). With 10,000 users split across two groups, this borderline gap may or may not reach significance depending on random variation in the generated data. Either outcome is valid for your recommendation.

🙋‍♀️ Seeing an ImportError for scipy?

Make sure you installed SciPy in the same environment running JupyterLab. Open a new terminal and run pip install scipy==1.18.0 again, then restart your kernel.

help me fix an ImportError for scipy in Jupyter

Write your recommendation

The numbers are done. Now you need to translate them into the deliverable a product director actually reads: a short recommendation that states the question, the evidence, and the verdict.

  • In your notebook, click the + button in the toolbar to create a new cell.
  • Change the cell type from Code to Markdown using the dropdown in the toolbar.
  • Paste the following template and fill in the values from your test output:
# A/B Test Recommendation

## Business Question
Did the new checkout flow increase conversions compared to the existing flow?

## Metric Results
- Control conversion rate: [your control_rate value]%
- Treatment conversion rate: [your treatment_rate value]%
- Absolute effect size: [your effect_size value] percentage points
- Relative lift: [your relative_lift value]%

## Statistical Verdict
- T-statistic: [your t_stat value]
- P-value: [your p_value value]
- Confidence level: 95% (alpha = 0.05)

## Recommendation
[If p < 0.05]: **Ship the new checkout flow.** The improvement is statistically
significant. We can be 95% confident the treatment outperforms control.

[If p >= 0.05]: **Do not ship. Extend the experiment.** The observed difference
is not statistically significant. We need more data or a larger sample size.

Why write a recommendation, not just show the code?

Senior analysts at companies like Airbnb and Meta do not send Jupyter notebooks to product directors. They send a one-page summary with a clear verdict. This markdown cell is the deliverable that proves you can turn data into decisions.

  • Replace the bracketed placeholders with the actual values printed by your t-test cell.
  • Keep only the recommendation paragraph that matches your p-value result (delete the other one).
  • Press Shift+Enter to render the Markdown cell.

Your rendered cell now reads like a professional recommendation memo.

Markdown not rendering?

Make sure the cell type dropdown says Markdown, not Code. If you accidentally ran it as code, change the dropdown and press Shift+Enter again.

help me render a Markdown cell in JupyterLab

✔️ Awesome, I've got everything!

Great. Double check you have saved your notebook (Cmd+S on macOS or Ctrl+S on Windows).

ⓧ I'd like to double check the full code

Your notebook should now have these cells in order after the window function queries:

  • Cell 6: The extraction cell (from scipy import stats, the SQL query with LEFT JOIN and CASE WHEN, control_conversions and treatment_conversions arrays).
  • Cell 7: The t-test cell (stats.ttest_ind(), rate calculations, print statements, and the if/else verdict).
  • Cell 8: The Markdown recommendation cell with your business question, metrics, statistical verdict, and recommendation.

Scroll up through your notebook and confirm each cell is present and executed (no [*] indicators).

You now have a complete A/B test analysis: from raw data, through advanced SQL, to a statistically rigorous recommendation. This is exactly the workflow tested in data analyst interviews at companies like Amazon, Meta, and Airbnb.

Secret mission

Segment the Results by User Cohort

Your aggregate A/B test result tells one story. But what if the treatment works brilliantly for new users and does nothing for returning users? In this secret mission, you will break the results down by user cohort to uncover hidden effects that a single p-value would miss.

Clean Up Your Resources

Clean Up Your Resources

This project runs entirely locally, so there are no ongoing costs or cloud resources to worry about. Decide whether to keep your files and packages for future reference, or remove them to free up disk space.

Resources you used:

  • JupyterLab process (running in your terminal).
  • The ab-test-analysis project folder on your Desktop.
  • Python packages installed via pip (duckdb, jupyterlab, scipy, pandas).
  • Python 3.13 (installed during this project).

Keep everything running

No action needed. Choose this if you plan to keep exploring your notebook, adding new analyses, or revisiting SQL queries. Everything runs locally with no ongoing costs.

  • Your JupyterLab server stays running in the terminal for quick access.
  • Your notebook and all cells remain saved in the ab-test-analysis folder.
  • Python 3.13 and all pip packages remain installed and available for other projects.

Pause - I'll come back to this later

Stop the JupyterLab server to free up your terminal. Your files and packages stay on disk so you can resume later.

  • Switch to the terminal where JupyterLab is running.
  • Press Ctrl+C to stop the server. Press y if prompted to confirm.
  • To resume later, open a terminal, navigate to your ab-test-analysis folder, and run jupyter lab again.

Delete - I don't want to use this again

Remove all project files and uninstall the packages. This frees up disk space and cleans your Python environment.

  • Stop JupyterLab by pressing Ctrl+C in the terminal where it is running.
  • Delete the project folder by running:

macOS

cd ~/Desktop
rm -rf ab-test-analysis

Windows

cd ~\Desktop
rmdir /s /q ab-test-analysis
  • Uninstall the Python packages by running:
pip uninstall duckdb jupyterlab scipy pandas -y
  • If you no longer need Python 3.13 on your machine, you can uninstall it. On Windows, open Settings, select Apps, search for Python 3.13, and click Uninstall. On macOS, remove it from /Library/Frameworks/Python.framework.

Nice Work!

Nice Work!

You just built a complete A/B test analysis notebook, taking raw experiment data all the way to a statistically rigorous business recommendation. This is the exact analytical workflow that data analyst teams at companies like Airbnb, Meta, and Amazon use every day.

You've learned how to:

  • Build a complete A/B test analysis from raw data to an executive-ready recommendation using DuckDB and Python.
  • Write advanced SQL queries using JOINs, CTEs, and window functions to extract conversion rates, ARPU, and daily metric trends from experiment data.
  • Determine statistical significance using a two-sample t-test in SciPy, turning a "looks better" observation into a defensible "ship it or kill it" verdict with a p-value and confidence level.
  • Secret Mission: Added user cohort segmentation to uncover hidden effects in the data, running independent statistical tests for new vs returning users.

Ready to quiz yourself?