Analyze a Promotion Experiment

Use SQL and Python to determine whether a promotion lifts conversion and profit.

Introduction

30 Second Summary

Discounts often produce a satisfying jump in purchases. The same offer can quietly shrink the money left from every visitor.

In this project, you will analyze a controlled experiment in Google Colab to decide whether the promotion should launch. Your notebook will use DuckDB SQL inside Python to expose the trade-off between conversion and profit.

What You'll Build

Your finished notebook runs from raw visitor data to a two-panel Matplotlib decision chart before printing a recommendation you can defend in a review.

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

  • A reproducible experiment notebook that regenerates the same 12,000-user dataset each time. Its SQL tables reveal which variant wins on conversion.
  • A defensible profitability analysis with 95% confidence intervals for conversion plus profit. A pre-defined customer breakdown locates the larger downside.
  • A two-panel decision chart that shows conversion rising while profit per visitor falls. A concise executive recommendation explains why the broad promotion should stay on hold.
  • Secret Mission: Calculate the maximum sustainable discount for new customers plus returning customers.

Are there any prerequisites?

You need basic SQL knowledge plus a Google account with confirmed Google Colab access. The whole analysis runs in the browser, which removes the need for a local Python or database installation.

Before We Start

Before you examine the experiment results, commit to the business decision you are making. This keeps an attractive conversion result from outweighing the promotion's effect on profit.

Set Up the Colab Analysis Environment

A promotion decision needs calculations that you can rerun from the same starting point. Google Colab gives you a browser-based notebook without requiring a local Python installation.

A pinned DuckDB release keeps the later SQL analysis consistent. A pinned Matplotlib release does the same for your charts.

In this step, get ready to:
  • Create a Colab notebook named promotion-experiment-analysis.
  • Install DuckDB 2.0.0 plus Matplotlib 3.11.2 in the current kernel.
  • Confirm that the notebook reports both pinned package versions.
Create your Colab notebook

Colab stores your notebook in Google Drive. This gives your analysis a persistent home even after the temporary runtime expires.

  • Visit Google Colab in your browser.
  • Sign in to your Google account if Colab asks for access.
  • Click File in the top menu.
  • Click New notebook.

Your blank notebook is now open. It contains a code cell where you can run the first setup command.

  • Click the current notebook title in the upper-left corner.
  • Replace the current title with promotion-experiment-analysis.
  • Press Enter to save the new name.

You should see promotion-experiment-analysis at the top of the notebook. Your analysis now has a named file in Google Drive.

Install the pinned packages

A Colab runtime is a temporary computer that executes your notebook cells. Installing packages in the current kernel makes the pinned releases available to the analysis you run during this session.

  • Paste this installation command into the first code cell:
%pip install duckdb==2.0.0 matplotlib==3.11.2

What does this command do?

  • The %pip magic runs the package installer within the current notebook kernel.
  • The duckdb==2.0.0 value installs the required DuckDB release.
  • The matplotlib==3.11.2 value installs the required Matplotlib release.
  • Run the installation cell by pressing Shift+Enter.
  • Wait for the installation output to finish before using the next cell.

Good progress: your current kernel now has both pinned packages available.

Package installation did not finish?

Check that the installation cell contains the complete command. Run the cell again if the output reports that a package could not be installed.

Help me fix package installation in Google Colab.

Verify the package versions

The installation output shows that packages were processed. A direct version check confirms which releases the active kernel imports.

  • Paste this version check into the empty code cell below the installation output:
import duckdb
import matplotlib

# Print the package versions used by this notebook
print(duckdb.version())
print(matplotlib.__version__)

What does this code do?

  • The two import statements load the installed packages into the active kernel.
  • The duckdb.version() call reports the DuckDB release used by the notebook.
  • The matplotlib.__version__ attribute reports the Matplotlib release used by the notebook.

Before you run the version check, which two pinned versions do you expect this kernel to report?

  • Run the version-check cell by pressing Shift+Enter.

The output should contain 2.0.0 for DuckDB. The next line should contain 3.11.2 for Matplotlib.

✔️ I see 2.0.0 and 3.11.2

That's the setup confirmed: your current kernel is using both pinned package releases.

ⓧ I see different versions

A different version means the active kernel has not loaded the pinned release yet.

  • Return to the package-installation cell above.
  • Run the installation cell again with Shift+Enter.
  • Return to the version-check cell.
  • Run the version-check cell again with Shift+Enter.
  • Confirm that the new output contains 2.0.0 plus 3.11.2.

ⓧ I see an import error

An import error means the package installation did not become available to the active kernel.

  • Return to the package-installation cell above.
  • Run the installation cell again with Shift+Enter.
  • Wait for the installation output to finish.
  • Return to the version-check cell.
  • Run the version-check cell again with Shift+Enter.

Versions still do not match?

Confirm that the installation cell includes both exact version pins. Check that the installation completed before rerunning the imports.

Help me troubleshoot my Colab package versions.

✔️ Awesome, I've got everything!

Your promotion-experiment-analysis notebook contains the installation cell plus the version-check cell. Both pinned versions appear in the output.

ⓧ I'd like to double check the full code

Compare your package-installation cell with this command:

%pip install duckdb==2.0.0 matplotlib==3.11.2

Compare your separate version-check cell with this code:

import duckdb
import matplotlib

# Print the package versions used by this notebook
print(duckdb.version())
print(matplotlib.__version__)

Your reproducible analysis environment is ready. Next, you'll generate the experiment dataset. You will then test the headline conversion result.

Find the Headline Conversion Winner

Your pinned Google Colab environment is ready. Your notebook now needs a reproducible experiment dataset that you can analyze with DuckDB.

A controlled experiment can produce an attractive headline metric while leaving the launch decision unresolved. This step deliberately creates that apparent winner so you can experience the limits of conversion alone.

In this step, get ready to:
  • Define the deterministic experiment design.
  • Generate a 12,000-user CSV dataset.
  • Run a headline conversion query with SQL.
Define the experiment design

A deterministic dataset returns the same result every time you regenerate it. Each design cell fixes the audience size plus the purchasing outcomes for one variant and customer segment.

  • Add a code cell beneath the version-check cell using Colab's code-cell control.
  • Set up the analysis imports plus output filenames by pasting this code into the new cell:
import csv
import math
from statistics import NormalDist, mean, stdev

import duckdb
import matplotlib.pyplot as plt

DATA_FILE = "promotion_experiment.csv"
CHART_FILE = "promotion_experiment_summary.png"

What Do These Imports Prepare?

  • The standard-library imports support CSV creation plus statistical analysis later in the notebook.
  • DuckDB runs the analytical queries directly against the generated data file.
  • Matplotlib prepares the notebook for the decision chart you build later.
  • The two filename constants give the generated data plus chart consistent locations.
  • Run the imports cell with Colab's play control.

You should see the cell complete without a traceback. Its execution indicator confirms the imports plus filenames are now available to later cells.

Seeing an Import Error?

Return to the package-installation cell from the previous step. Run that cell again before rerunning this imports cell.

Check that every import matches the snippet exactly. Help me troubleshoot the analysis imports in my Google Colab notebook.

The experiment contains four design cells. The next two snippets form one complete Python assignment in a single notebook cell.

  • Add another code cell beneath the imports cell.
  • Paste the first half of EXPERIMENT_DESIGN into the new cell:
EXPERIMENT_DESIGN = [
    {
        "variant": "control",
        "segment": "new",
        "users": 3000,
        "conversions": 240,
        "order_value": 80.0,
        "cost_of_goods": 45.0,
    },
    {
        "variant": "treatment",
        "segment": "new",
        "users": 3000,
        "conversions": 300,
        "order_value": 68.0,
        "cost_of_goods": 45.0,
    },

What Does the First Half Represent?

  • The first dictionary represents 3,000 new customers assigned to control.
  • The second dictionary represents 3,000 new customers assigned to treatment.
  • The conversion totals plus order economics determine each cell's user-level outcomes.
  • Place your cursor directly beneath the second dictionary in the same cell.
  • Complete EXPERIMENT_DESIGN by pasting the remaining design cells:
    {
        "variant": "control",
        "segment": "returning",
        "users": 3000,
        "conversions": 480,
        "order_value": 100.0,
        "cost_of_goods": 55.0,
    },
    {
        "variant": "treatment",
        "segment": "returning",
        "users": 3000,
        "conversions": 510,
        "order_value": 85.0,
        "cost_of_goods": 55.0,
    },
]

How Is the Design Completed?

  • The remaining dictionaries define the control plus treatment outcomes for returning customers.
  • Each segment contains 3,000 users per variant.
  • All four cells together produce 12,000 randomized-user records.
  • Run the completed experiment-design cell with Colab's play control.

You should see the cell finish without a traceback. The completed list now holds four design dictionaries.

Seeing a Syntax Error in the Design?

Check that both snippets are inside the same cell. Confirm that the final closing bracket appears beneath the fourth dictionary.

Compare the commas after each dictionary with the snippets above. Help me find the syntax error in my EXPERIMENT_DESIGN list.

Generate the user-level dataset

The design stores aggregate targets. The create_dataset() function expands those targets into one row per randomized user.

  • Add a code cell beneath the experiment-design cell.
  • Define the dataset generator by pasting this function into the new cell:
def create_dataset():
    rows = []
    user_number = 1

    for cell in EXPERIMENT_DESIGN:
        for position in range(cell["users"]):
            converted = int(position < cell["conversions"])
            revenue = cell["order_value"] if converted else 0.0
            cost_of_goods = cell["cost_of_goods"] if converted else 0.0
            profit = revenue - cost_of_goods

            rows.append(
                {
                    "user_id": f"U{user_number:05d}",
                    "variant": cell["variant"],
                    "segment": cell["segment"],
                    "converted": converted,
                    "revenue": revenue,
                    "cost_of_goods": cost_of_goods,
                    "profit": profit,
                }
            )
            user_number += 1

    with open(DATA_FILE, "w", newline="", encoding="utf-8") as csv_file:
        writer = csv.DictWriter(csv_file, fieldnames=rows[0].keys())
        writer.writeheader()
        writer.writerows(rows)

    return len(rows)

What Does This Function Do?

  • The nested loops create the required number of users for every design cell.
  • Converted users receive revenue plus cost values from their assigned cell.
  • Profit records the difference between revenue plus cost of goods for each user.
  • The CSV writer saves the resulting user-level rows to promotion_experiment.csv.
  • Run the dataset-generator cell with Colab's play control.

You should see the function definition complete without a traceback. The notebook can now turn the experiment design into user-level rows.

Seeing an Error in the Generator?

Check that the experiment-design cell ran before this cell. Confirm that each nested block uses consistent indentation.

Compare every dictionary key with EXPERIMENT_DESIGN. Help me debug my create_dataset function.

Two small helpers keep the upcoming SQL cells readable. One returns query results as dictionaries. The other prints those dictionaries as aligned tables.

  • Add a code cell beneath the dataset-generator cell.
  • Define the query helper by pasting this function into the new cell:
def query_rows(connection, sql):
    connection.execute(sql)
    columns = [column[0] for column in connection.description]
    return [dict(zip(columns, row)) for row in connection.fetchall()]

What Does the Query Helper Return?

  • The connection executes the supplied SQL statement.
  • The helper reads the result column names from the connection description.
  • Each returned database row becomes a dictionary keyed by those column names.
  • Run the query-helper cell with Colab's play control.

You should see the function definition complete without a traceback. Later cells can now retrieve named query results.

Query Helper Cell Failing?

Check the indentation inside query_rows(). Confirm that connection uses the same spelling on every line.

Help me compare my query_rows helper with the project code.

  • Add a code cell beneath the query-helper cell.
  • Define the table printer by pasting this function into the new cell:
def print_table(rows):
    if not rows:
        print("No rows returned")
        return

    headers = list(rows[0].keys())
    widths = {
        header: max(len(header), *(len(str(row[header])) for row in rows))
        for header in headers
    }

    print(" | ".join(header.ljust(widths[header]) for header in headers))
    print("-+-".join("-" * widths[header] for header in headers))
    for row in rows:
        print(" | ".join(str(row[header]).ljust(widths[header]) for header in headers))

How Does the Table Printer Help?

  • The empty-result check gives you a clear message when a query returns no rows.
  • The width calculation finds the longest value in each result column.
  • The final loop aligns every value beneath its matching header.
  • Run the table-printer cell with Colab's play control.

You should see the function definition complete without a traceback. Your query results can now be printed as readable notebook tables.

Table Printer Cell Failing?

Check the indentation beneath the empty-result condition plus the final loop. Confirm that each reference to widths uses the same spelling.

Help me debug my print_table helper.

  • Add a code cell beneath the table-printer cell.
  • Generate the CSV plus open the in-memory connection by adding this code to the new cell:
row_count = create_dataset()
print(f"Created {row_count} rows in {DATA_FILE}\n")

con = duckdb.connect()

What Happens When This Cell Runs?

  • The function writes the deterministic user-level dataset to promotion_experiment.csv.
  • The returned row count provides an immediate completeness check.
  • The con variable holds an in-memory DuckDB connection for the analysis queries.
  • Run the dataset-creation cell with Colab's play control.

You should see Created 12000 rows in promotion_experiment.csv. That confirms all four 3,000-user design cells were expanded into the dataset.

Row Count Missing or Incorrect?

Rerun the imports plus experiment-design cells before rerunning the helper cells. Check that every design dictionary contains 3000 users.

Confirm that the dataset-creation cell calls create_dataset() exactly once. Help me find why my generated row count is not 12000.

Run the headline conversion query

The headline query groups every user by experiment variant. It measures the percentage who converted without considering order value or profit.

  • Add a code cell beneath the dataset-creation cell.
  • Prepare the headline conversion result by pasting this query into the new cell:
headline = query_rows(
    con,
    f"""
    SELECT
        variant,
        COUNT(*) AS users,
        ROUND(100.0 * AVG(converted), 2) AS conversion_rate_pct
    FROM '{DATA_FILE}'
    GROUP BY variant
    ORDER BY variant
    """,
)
print("Headline conversion result")
print_table(headline)

What Does the Headline Query Measure?

  • The grouping produces one result row for control plus one for treatment.
  • The user count checks how many randomized visitors belong to each variant.
  • The average of the binary conversion field becomes the conversion rate.
  • The multiplication converts each rate into a percentage.

Before you run this cell, which variant do you think will appear to win when conversion is the only decision metric? Make your prediction now.

  • Run the headline-query cell with Colab's play control.

You should see 6,000 users in each variant. Control shows 12.0% conversion. Treatment shows 13.5% conversion.

You have your first visible experiment result. Based on conversion alone, treatment appears to win.

Why Is This Conclusion Incomplete?

Conversion tells you how often visitors buy. It does not reveal how much value each purchase creates.

The treatment's higher rate creates a tempting launch conclusion. The experiment still needs a business-value check before that conclusion becomes defensible.

Headline Results Look Different?

Confirm that the row-count cell printed Created 12000 rows in promotion_experiment.csv. Rerun the headline cell after recreating the dataset.

Check that the query averages converted after grouping by variant. Help me debug my headline conversion query.

✔️ Awesome, I've got everything!

Great. Confirm that your notebook reports 12,000 generated rows plus the 12.0% control rate plus the 13.5% treatment rate.

ⓧ I'd like to double check the full code

Compare your analysis cells with the complete cumulative code for this step:

import csv
import math
from statistics import NormalDist, mean, stdev

import duckdb
import matplotlib.pyplot as plt

DATA_FILE = "promotion_experiment.csv"
CHART_FILE = "promotion_experiment_summary.png"

EXPERIMENT_DESIGN = [
    {
        "variant": "control",
        "segment": "new",
        "users": 3000,
        "conversions": 240,
        "order_value": 80.0,
        "cost_of_goods": 45.0,
    },
    {
        "variant": "treatment",
        "segment": "new",
        "users": 3000,
        "conversions": 300,
        "order_value": 68.0,
        "cost_of_goods": 45.0,
    },
    {
        "variant": "control",
        "segment": "returning",
        "users": 3000,
        "conversions": 480,
        "order_value": 100.0,
        "cost_of_goods": 55.0,
    },
    {
        "variant": "treatment",
        "segment": "returning",
        "users": 3000,
        "conversions": 510,
        "order_value": 85.0,
        "cost_of_goods": 55.0,
    },
]


def create_dataset():
    rows = []
    user_number = 1

    for cell in EXPERIMENT_DESIGN:
        for position in range(cell["users"]):
            converted = int(position < cell["conversions"])
            revenue = cell["order_value"] if converted else 0.0
            cost_of_goods = cell["cost_of_goods"] if converted else 0.0
            profit = revenue - cost_of_goods

            rows.append(
                {
                    "user_id": f"U{user_number:05d}",
                    "variant": cell["variant"],
                    "segment": cell["segment"],
                    "converted": converted,
                    "revenue": revenue,
                    "cost_of_goods": cost_of_goods,
                    "profit": profit,
                }
            )
            user_number += 1

    with open(DATA_FILE, "w", newline="", encoding="utf-8") as csv_file:
        writer = csv.DictWriter(csv_file, fieldnames=rows[0].keys())
        writer.writeheader()
        writer.writerows(rows)

    return len(rows)


def query_rows(connection, sql):
    connection.execute(sql)
    columns = [column[0] for column in connection.description]
    return [dict(zip(columns, row)) for row in connection.fetchall()]


def print_table(rows):
    if not rows:
        print("No rows returned")
        return

    headers = list(rows[0].keys())
    widths = {
        header: max(len(header), *(len(str(row[header])) for row in rows))
        for header in headers
    }

    print(" | ".join(header.ljust(widths[header]) for header in headers))
    print("-+-".join("-" * widths[header] for header in headers))
    for row in rows:
        print(" | ".join(str(row[header]).ljust(widths[header]) for header in headers))


row_count = create_dataset()
print(f"Created {row_count} rows in {DATA_FILE}\n")

con = duckdb.connect()

headline = query_rows(
    con,
    f"""
    SELECT
        variant,
        COUNT(*) AS users,
        ROUND(100.0 * AVG(converted), 2) AS conversion_rate_pct
    FROM '{DATA_FILE}'
    GROUP BY variant
    ORDER BY variant
    """,
)
print("Headline conversion result")
print_table(headline)

Your treatment now looks like the conversion winner. Next, you will test whether that apparent win creates business value.

Expose the Profitability Trade-off

Your Google Colab notebook now shows treatment conversion at 13.5%. Control conversion sits at 12.0%.

That headline result makes the promotion look successful. However, a conversion-only conclusion ignores the discount's effect on order value and profit.

This step checks whether the experiment has balanced traffic. You will then measure whether the extra conversions create enough business value.

In this step, get ready to:
  • Validate that control and treatment received equal traffic.
  • Calculate conversion and commercial performance in one KPI table.
  • Replace the conversion-only conclusion with a profitability question.
Validate the traffic split

Traffic balance is a data-quality check for a controlled experiment. A balanced split gives each variant an equal share of the randomized users.

You will use SQL in DuckDB to count each variant before trusting the outcome metrics.

  • In the promotion-experiment-analysis notebook from earlier, add a code cell beneath the headline conversion output.
  • Paste this traffic-share query into the new cell:
split_check = query_rows(
    con,
    f"""
    SELECT
        variant,
        COUNT(*) AS users,
        ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 2) AS traffic_share_pct
    FROM '{DATA_FILE}'
    GROUP BY variant
    ORDER BY variant
    """,
)
print("\nTraffic split check")
print_table(split_check)

What does this query check?

  • The COUNT(*) calculation counts the users assigned to each variant.
  • The window calculation divides each count by the total experiment population.
  • The GROUP BY variant clause produces one row for control and one row for treatment.
  • The print_table() helper turns the query result into a readable comparison.
  • Before you run the cell, predict whether the two variants received equal traffic.
  • Run the traffic-share cell.

You should see 6000 users in each variant. Each row should show a traffic share of 50.00.

That balance check is complete. Both variants had the same opportunity to produce the outcomes you compare next.

Traffic check not matching?

  • Rerun the earlier connection cell if the notebook cannot find con.
  • Rerun the earlier dataset-generation cell if the query cannot read promotion_experiment.csv.
  • Compare the query with the reference tab below if the user counts or traffic shares differ.

Help me diagnose my traffic split query.

Measure conversion and business value

A conversion rate measures how often visitors buy. Average order value measures how much each converted order earns before costs.

Profit per visitor spreads the resulting profit across every randomized user. This metric reveals whether the promotion creates value at the experiment level.

  • Add another code cell beneath the traffic split output.
  • Paste this grouped KPI query into the new cell:
kpis = query_rows(
    con,
    f"""
    SELECT
        variant,
        COUNT(*) AS users,
        SUM(converted) AS conversions,
        ROUND(100.0 * AVG(converted), 2) AS conversion_rate_pct,
        ROUND(SUM(revenue) / NULLIF(SUM(converted), 0), 2) AS average_order_value,
        ROUND(SUM(profit) / COUNT(*), 2) AS profit_per_visitor,
        ROUND(SUM(profit), 2) AS total_profit
    FROM '{DATA_FILE}'
    GROUP BY variant
    ORDER BY variant
    """,
)
print("\nExperiment KPI table")
print_table(kpis)

What does this query measure?

  • The users and conversions columns show the population behind each result.
  • The conversion_rate_pct calculation averages the binary conversion outcome.
  • The average_order_value calculation divides revenue by converted orders.
  • The profit_per_visitor calculation divides profit by every randomized user.
  • The total_profit calculation shows the total commercial result for each variant.
  • Before you run the cell, predict whether the conversion winner also produces more profit.
  • Run the KPI cell.

Control shows 12.0% conversion. Its profit per visitor is $5.00.

Treatment shows 13.5% conversion. Its profit per visitor falls to $3.70.

Treatment also has a lower average order value. Total profit falls from $30,000 to $22,200.

KPI values not matching?

  • Check that the query reads from DATA_FILE through the existing con connection.
  • Check that average_order_value divides revenue by conversions.
  • Check that profit_per_visitor divides profit by all users.

Help me troubleshoot my KPI query.

✔️ Awesome, I've got everything!

Great. Your notebook now verifies the balanced traffic split and exposes the profitability trade-off.

ⓧ I'd like to double check the full code

Keep the earlier package installation cell above this analysis. Your cumulative Python code through this step should match:

import csv
import math
from statistics import NormalDist, mean, stdev

import duckdb
import matplotlib.pyplot as plt

DATA_FILE = "promotion_experiment.csv"
CHART_FILE = "promotion_experiment_summary.png"

EXPERIMENT_DESIGN = [
    {
        "variant": "control",
        "segment": "new",
        "users": 3000,
        "conversions": 240,
        "order_value": 80.0,
        "cost_of_goods": 45.0,
    },
    {
        "variant": "treatment",
        "segment": "new",
        "users": 3000,
        "conversions": 300,
        "order_value": 68.0,
        "cost_of_goods": 45.0,
    },
    {
        "variant": "control",
        "segment": "returning",
        "users": 3000,
        "conversions": 480,
        "order_value": 100.0,
        "cost_of_goods": 55.0,
    },
    {
        "variant": "treatment",
        "segment": "returning",
        "users": 3000,
        "conversions": 510,
        "order_value": 85.0,
        "cost_of_goods": 55.0,
    },
]


def create_dataset():
    rows = []
    user_number = 1

    for cell in EXPERIMENT_DESIGN:
        for position in range(cell["users"]):
            converted = int(position < cell["conversions"])
            revenue = cell["order_value"] if converted else 0.0
            cost_of_goods = cell["cost_of_goods"] if converted else 0.0
            profit = revenue - cost_of_goods

            rows.append(
                {
                    "user_id": f"U{user_number:05d}",
                    "variant": cell["variant"],
                    "segment": cell["segment"],
                    "converted": converted,
                    "revenue": revenue,
                    "cost_of_goods": cost_of_goods,
                    "profit": profit,
                }
            )
            user_number += 1

    with open(DATA_FILE, "w", newline="", encoding="utf-8") as csv_file:
        writer = csv.DictWriter(csv_file, fieldnames=rows[0].keys())
        writer.writeheader()
        writer.writerows(rows)

    return len(rows)


def query_rows(connection, sql):
    connection.execute(sql)
    columns = [column[0] for column in connection.description]
    return [dict(zip(columns, row)) for row in connection.fetchall()]


def print_table(rows):
    if not rows:
        print("No rows returned")
        return

    headers = list(rows[0].keys())
    widths = {
        header: max(len(header), *(len(str(row[header])) for row in rows))
        for header in headers
    }

    print(" | ".join(header.ljust(widths[header]) for header in headers))
    print("-+-".join("-" * widths[header] for header in headers))
    for row in rows:
        print(" | ".join(str(row[header]).ljust(widths[header]) for header in headers))


row_count = create_dataset()
print(f"Created {row_count} rows in {DATA_FILE}\n")

con = duckdb.connect()

headline = query_rows(
    con,
    f"""
    SELECT
        variant,
        COUNT(*) AS users,
        ROUND(100.0 * AVG(converted), 2) AS conversion_rate_pct
    FROM '{DATA_FILE}'
    GROUP BY variant
    ORDER BY variant
    """,
)
print("Headline conversion result")
print_table(headline)

split_check = query_rows(
    con,
    f"""
    SELECT
        variant,
        COUNT(*) AS users,
        ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 2) AS traffic_share_pct
    FROM '{DATA_FILE}'
    GROUP BY variant
    ORDER BY variant
    """,
)
print("\nTraffic split check")
print_table(split_check)

kpis = query_rows(
    con,
    f"""
    SELECT
        variant,
        COUNT(*) AS users,
        SUM(converted) AS conversions,
        ROUND(100.0 * AVG(converted), 2) AS conversion_rate_pct,
        ROUND(SUM(revenue) / NULLIF(SUM(converted), 0), 2) AS average_order_value,
        ROUND(SUM(profit) / COUNT(*), 2) AS profit_per_visitor,
        ROUND(SUM(profit), 2) AS total_profit
    FROM '{DATA_FILE}'
    GROUP BY variant
    ORDER BY variant
    """,
)
print("\nExperiment KPI table")
print_table(kpis)
Replace the headline conclusion

The treatment wins on conversion. The same treatment loses on profit per visitor.

The lower average order value explains the trade-off. The promotion generates extra orders with less profit behind each randomized visitor.

  • Compare conversion_rate_pct across the control and treatment rows.
  • Compare average_order_value across the same rows.
  • Compare profit_per_visitor across the same rows.
  • State the new profitability question in the checkpoint below.

What changed in the decision?

The launch decision now asks whether the conversion gain compensates for the lower order value and profit. The KPI table shows that it does not.

A broad launch would trade higher conversion for lower profit per visitor. That is the commercial risk hidden by the headline metric.

You have exposed the real trade-off behind the promotion. Next, you will test whether the conversion gain and profit loss are reliable enough to guide a launch decision.

Quantify Uncertainty and Segment Effects

Your balanced KPI table in Google Colab exposed the promotion's trade-off. Treatment converts more visitors but earns less profit per visitor.

Point estimates alone cannot show whether those differences are reliable. A 95% confidence interval gives a plausible range for each treatment-minus-control effect.

A pre-defined customer split then shows where the profit loss is concentrated. Together, these checks turn the profitability question into defensible evidence.

In this step, get ready to:
  • Estimate treatment-minus-control effects for conversion and profit.
  • Calculate 95% confidence intervals from user-level outcomes.
  • Compare the promotion's effect across new and returning customers.
Calculate 95% confidence intervals

The experiment randomized individual users, so the analysis measures conversion and profit at the user level. The helper below calculates the mean difference plus its uncertainty range.

  • Insert a code cell directly beneath the KPI output using the notebook toolbar.
  • Define the reusable interval calculation by pasting this code into the new cell:
def mean_difference_ci(control_values, treatment_values, confidence=0.95):
    alpha = 1.0 - confidence
    critical_value = NormalDist().inv_cdf(1.0 - alpha / 2.0)
    difference = mean(treatment_values) - mean(control_values)
    standard_error = math.sqrt(
        stdev(control_values) ** 2 / len(control_values)
        + stdev(treatment_values) ** 2 / len(treatment_values)
    )
    margin = critical_value * standard_error
    return difference, difference - margin, difference + margin

How Does the Interval Work?

  • The difference value subtracts the control mean from the treatment mean.
  • The standard_error value combines the user-level variability from both variants.
  • The normal critical value scales that uncertainty to the requested confidence level.
  • The function returns the estimated effect plus its lower and upper bounds.

Why User-Level Differences in Means?

Each CSV row represents one randomized user. Comparing variant means preserves that experimental unit.

A regression model could estimate additional interactions. It adds statistical complexity that this launch decision does not need.

  • Run the function cell by clicking the play icon at its left edge.

The cell finishes without an error. Google Colab saves the function with your notebook.

Does the Function Cell Stop?

  • Check that every line beneath def mean_difference_ci remains indented by four spaces.
  • Check that the closing parentheses around math.sqrt() match the reference code.

Help me find the syntax or indentation issue in my mean_difference_ci function.

The function needs one conversion value and one profit value for every randomized user. Your existing con connection can read those outcomes directly from promotion_experiment.csv.

  • Insert another code cell beneath the function cell using the notebook toolbar.
  • Prepare the user-level outcome lists by pasting this code into the new cell:
user_metrics = query_rows(
    con,
    f"""
    SELECT
        variant,
        CAST(converted AS DOUBLE) AS converted,
        CAST(profit AS DOUBLE) AS profit
    FROM '{DATA_FILE}'
    """,
)

What Does This Query Prepare?

The query keeps one row per experiment user. It converts the outcome columns to numeric values that Python can pass into the interval function.

Conversion stays binary at 0.0 or 1.0. Profit retains each visitor's realized value.

  • Run the user-metrics cell by clicking its play icon.

The cell finishes successfully. The user_metrics rows are now available for the interval calculation.

Does the User Query Fail?

  • Confirm the earlier cell that creates con has already run.
  • Confirm promotion_experiment.csv still appears in the current Colab runtime.

Help me troubleshoot why the user_metrics query cannot read my experiment CSV.

The final calculation separates each variant's user outcomes. It then reports treatment minus control for both business metrics.

  • Insert a code cell beneath the user-metrics cell using the notebook toolbar.
  • Calculate both effects by pasting this code into the new cell:
control_conversion = [
    row["converted"] for row in user_metrics if row["variant"] == "control"
]
treatment_conversion = [
    row["converted"] for row in user_metrics if row["variant"] == "treatment"
]
control_profit = [
    row["profit"] for row in user_metrics if row["variant"] == "control"
]
treatment_profit = [
    row["profit"] for row in user_metrics if row["variant"] == "treatment"
]

conversion_effect = mean_difference_ci(control_conversion, treatment_conversion)
profit_effect = mean_difference_ci(control_profit, treatment_profit)

print("\nTreatment minus control effects with 95% confidence intervals")
print(
    "Conversion-rate difference: "
    f"{conversion_effect[0]:.4f} "
    f"({conversion_effect[1]:.4f}, {conversion_effect[2]:.4f})"
)
print(
    "Profit-per-visitor difference: "
    f"${profit_effect[0]:.2f} "
    f"(${profit_effect[1]:.2f}, ${profit_effect[2]:.2f})"
)

What Does This Calculation Show?

The four lists separate control and treatment observations for each outcome. The same interval function can therefore measure conversion and profit on their original scales.

A positive effect favors treatment. A negative effect means treatment produced a lower average outcome.

Before you run this cell, do you expect either 95% interval to cross zero?

  • Run the effect-calculation cell by clicking its play icon.

You'll see the conversion effect as 0.0150, with a 95% interval from about 0.0031 to 0.0269. You'll see the profit effect as $-1.30, with a 95% interval from about $-1.72 to $-0.88.

Are the Interval Values Different?

  • Run the earlier dataset-generation cell again to restore the deterministic experiment rows.
  • Run the user_metrics cell again after regenerating the CSV.
  • Confirm the effect calls pass control values first and treatment values second.

Help me trace why my confidence interval values differ from the expected experiment results.

Compare pre-defined customer segments

The aggregate effect identifies a reliable profit loss. A pre-defined split using the existing DuckDB connection shows whether that loss is similar for new and returning customers.

Why Use Pre-defined Segments?

Pre-defined segments answer a question chosen before inspecting many possible subgroups. This limits the risk of treating a chance pattern as a meaningful business result.

The comparison is a focused check for heterogeneous treatment effects. It shows whether the same promotion produces different outcomes across customer groups.

  • Insert a code cell beneath the confidence-interval output using the notebook toolbar.
  • Build the customer-segment table by pasting this code into the new cell:
segments = query_rows(
    con,
    f"""
    SELECT
        segment,
        variant,
        COUNT(*) AS users,
        ROUND(100.0 * AVG(converted), 2) AS conversion_rate_pct,
        ROUND(SUM(profit) / COUNT(*), 2) AS profit_per_visitor
    FROM '{DATA_FILE}'
    GROUP BY segment, variant
    ORDER BY segment, variant
    """,
)
print("\nPre-defined customer segment results")
print_table(segments)

What Does the Segment Query Measure?

The query groups users by segment plus variant. Each row reports group size, conversion rate, and profit per visitor.

Keeping both outcomes together reveals whether a conversion gain offsets the commercial impact within each customer group.

Before you run this cell, which customer segment do you expect to show the larger profit decline?

  • Run the segment-query cell by clicking its play icon.

For new customers, you'll see control at 8.0% conversion with $2.80 profit per visitor. Treatment reaches 10.0% conversion with $2.30 profit per visitor.

For returning customers, you'll see control at 16.0% conversion with $7.20 profit per visitor. Treatment reaches 17.0% conversion with $5.10 profit per visitor.

Is a Segment Row Missing?

  • Confirm the query groups by both segment and variant.
  • Confirm the query orders by both segment and variant.
  • Run the dataset-generation cell again if the CSV has been replaced.

Help me diagnose why my segments table is missing a customer group or variant.

✔️ Awesome, I've got everything!

Great. Double-check that the interval output and all four segment rows appear in promotion-experiment-analysis.

ⓧ I'd like to double check the full code

Compare every code cell added in this step with this combined reference:

def mean_difference_ci(control_values, treatment_values, confidence=0.95):
    alpha = 1.0 - confidence
    critical_value = NormalDist().inv_cdf(1.0 - alpha / 2.0)
    difference = mean(treatment_values) - mean(control_values)
    standard_error = math.sqrt(
        stdev(control_values) ** 2 / len(control_values)
        + stdev(treatment_values) ** 2 / len(treatment_values)
    )
    margin = critical_value * standard_error
    return difference, difference - margin, difference + margin

user_metrics = query_rows(
    con,
    f"""
    SELECT
        variant,
        CAST(converted AS DOUBLE) AS converted,
        CAST(profit AS DOUBLE) AS profit
    FROM '{DATA_FILE}'
    """,
)

control_conversion = [
    row["converted"] for row in user_metrics if row["variant"] == "control"
]
treatment_conversion = [
    row["converted"] for row in user_metrics if row["variant"] == "treatment"
]
control_profit = [
    row["profit"] for row in user_metrics if row["variant"] == "control"
]
treatment_profit = [
    row["profit"] for row in user_metrics if row["variant"] == "treatment"
]

conversion_effect = mean_difference_ci(control_conversion, treatment_conversion)
profit_effect = mean_difference_ci(control_profit, treatment_profit)

print("\nTreatment minus control effects with 95% confidence intervals")
print(
    "Conversion-rate difference: "
    f"{conversion_effect[0]:.4f} "
    f"({conversion_effect[1]:.4f}, {conversion_effect[2]:.4f})"
)
print(
    "Profit-per-visitor difference: "
    f"${profit_effect[0]:.2f} "
    f"(${profit_effect[1]:.2f}, ${profit_effect[2]:.2f})"
)

segments = query_rows(
    con,
    f"""
    SELECT
        segment,
        variant,
        COUNT(*) AS users,
        ROUND(100.0 * AVG(converted), 2) AS conversion_rate_pct,
        ROUND(SUM(profit) / COUNT(*), 2) AS profit_per_visitor
    FROM '{DATA_FILE}'
    GROUP BY segment, variant
    ORDER BY segment, variant
    """,
)
print("\nPre-defined customer segment results")
print_table(segments)

What Does the Evidence Prove?

The conversion interval stays above zero. The promotion reliably increases conversion in this experiment.

The profit interval stays below zero. The promotion reliably reduces profit per visitor.

Returning-customer profit falls by $2.10 per visitor. New-customer profit falls by $0.50 per visitor.

The larger returning-customer decline identifies where the promotion causes the most commercial damage.

That closes the uncertainty gap. You now have statistical evidence for the trade-off plus a clear view of where the downside is concentrated.

Your analysis now shows a reliable conversion lift alongside a reliable profit loss. Next, you'll turn that evidence into a decision chart and an executive recommendation.

Deliver the Business Recommendation

Your analysis now shows a reliable conversion lift. The same evidence shows a reliable profit loss.

Stakeholders need a clear decision supported by concise evidence. You'll use Matplotlib to turn the results in your Google Colab notebook into a chart and a launch recommendation.

In this step, get ready to:
  • Build a two-panel chart that compares conversion rate with profit per visitor.
  • Print a recommendation supported by the confidence intervals and customer segments.
  • Add a markdown executive summary above the chart.
Build the decision chart

A decision chart places the attractive result beside its commercial cost. This makes the trade-off visible before anyone focuses on conversion alone.

  • Insert a new code cell beneath the customer segment results in your notebook.
  • Paste the chart code below into the new cell:
kpi_by_variant = {row["variant"]: row for row in kpis}
variants = ["control", "treatment"]
colors = ["#4C78A8", "#F58518"]
conversion_values = [
    float(kpi_by_variant[variant]["conversion_rate_pct"]) for variant in variants
]
profit_values = [
    float(kpi_by_variant[variant]["profit_per_visitor"]) for variant in variants
]

fig, axes = plt.subplots(1, 2, figsize=(10, 4))
conversion_bars = axes[0].bar(variants, conversion_values, color=colors)
axes[0].bar_label(conversion_bars, fmt=lambda value: f"{value:.1f}%")
axes[0].set_title("Conversion rate")
axes[0].set_ylabel("Percent of visitors")

profit_bars = axes[1].bar(variants, profit_values, color=colors)
axes[1].bar_label(profit_bars, fmt=lambda value: f"${value:.2f}")
axes[1].axhline(y=0, color="black", linewidth=0.8)
axes[1].set_title("Profit per visitor")
axes[1].set_ylabel("Dollars")

fig.tight_layout()
fig.savefig(CHART_FILE, dpi=160, bbox_inches="tight")
plt.show()

What does this code do?

  • The kpi_by_variant dictionary makes each variant's KPI row available by name.
  • The conversion_values list supplies the heights for the conversion bars.
  • The profit_values list supplies the heights for the profit bars.
  • The two axes place conversion rate beside profit per visitor.
  • The zero-profit reference line gives the profit panel a clear financial baseline.
  • The fig.savefig() call saves the finished chart as promotion_experiment_summary.png.
  • The plt.show() call displays the chart inside the notebook.

Before you run the cell, which panel do you expect to make the promotion look successful?

  • Run the chart cell.

You'll see treatment ahead on conversion at 13.5% compared with control at 12.0%. The profit panel reverses the story with treatment at $3.70 per visitor compared with control at $5.00.

The runtime now contains promotion_experiment_summary.png.

Chart missing or empty?

Run the earlier KPI cell again if the chart cell reports that kpis is missing. Confirm that the KPI table contains one control row and one treatment row.

Check that the chart cell uses conversion_rate_pct and profit_per_visitor exactly as they appear in the KPI table.

Help me troubleshoot my two-panel Matplotlib chart in Google Colab.

Print the launch recommendation

The recommendation connects the headline metric to business value. It also uses the confidence intervals and segment result to show that the decision is supported by more than a point estimate.

  • Insert a new code cell beneath the chart cell.
  • Paste the recommendation code below into the new cell:
control_kpi = kpi_by_variant["control"]
treatment_kpi = kpi_by_variant["treatment"]
relative_conversion_lift = (
    float(treatment_kpi["conversion_rate_pct"])
    / float(control_kpi["conversion_rate_pct"])
    - 1.0
) * 100.0
profit_change = (
    float(treatment_kpi["profit_per_visitor"])
    - float(control_kpi["profit_per_visitor"])
)

recommendation = f"""
Recommendation: Do not launch the broad promotion.

Evidence:
- Conversion increased by {relative_conversion_lift:.1f}% relative, from {control_kpi['conversion_rate_pct']}% to {treatment_kpi['conversion_rate_pct']}%.
- Profit per visitor changed by ${profit_change:.2f}, from ${float(control_kpi['profit_per_visitor']):.2f} to ${float(treatment_kpi['profit_per_visitor']):.2f}.
- The 95% interval for the conversion effect stays above zero, while the interval for the profit effect stays below zero.
- Returning customers show the larger profit-per-visitor decline.

Next action: Test a smaller discount targeted to new customers instead of shipping the 15% promotion to everyone.
"""
print(recommendation)
print(f"Saved chart to {CHART_FILE}")

How does the recommendation use the evidence?

  • The relative_conversion_lift value expresses the treatment increase relative to control.
  • The profit_change value stores the absolute change in profit per visitor.
  • The recommendation connects both effect directions to their confidence intervals.
  • The returning-customer result identifies where the larger downside occurs.
  • The final line turns the rejection into a smaller follow-up experiment.

Before you run the cell, which result should control the launch decision when conversion and profit point in opposite directions?

  • Run the recommendation cell.

You'll see a recommendation not to launch the broad promotion. The evidence reports a 12.5% relative conversion lift alongside a $1.30 decline in profit per visitor.

The printed text also explains that the conversion interval stays above zero. It states that the profit interval stays below zero.

Recommendation cell reporting a missing name?

Run the chart cell again if kpi_by_variant is missing. Run the confidence-interval cell again if conversion_effect or profit_effect is missing from the notebook state.

Help me restore the notebook variables needed by the recommendation cell.

Add the executive summary

An executive summary gives a reader the decision before the supporting analysis. The chart directly below it then makes the trade-off easy to verify.

What should the summary cover?

  • Decision: Do not launch the broad 15% promotion.
  • Conversion evidence: Treatment rises from 12.0% to 13.5%.
  • Profit evidence: Profit per visitor falls from $5.00 to $3.70.
  • Statistical evidence: The conversion interval remains above zero.
  • Statistical evidence: The profit interval remains below zero.
  • Segment implication: Returning customers show the larger downside.
  • Insert a markdown cell directly above the chart code cell.

You'll see an empty markdown cell in the decision section of your notebook.

  • Write an Executive Summary heading followed by a short paragraph using the six findings above.
  • Render the markdown cell.

You'll see a concise decision statement immediately above the two-panel chart.

  • Check that the notebook title still reads promotion-experiment-analysis.

Your notebook now opens with a reproducible analysis path and ends with a stakeholder-ready decision.

Before the final check, what should appear first when a stakeholder reaches the decision section?

  • Run the chart cell again.
  • Run the recommendation cell again.

You'll see the executive summary above a two-panel chart. You'll also see the recommendation not to launch the broad promotion printed below it.

The chart cell saves promotion_experiment_summary.png in the runtime. The recommendation points to a smaller test focused on new customers.

✔️ Awesome, I've got everything!

Great work. Your notebook now presents the experiment evidence as a chart and a defensible business recommendation.

ⓧ I'd like to double check the full code

Compare the Python cells in your notebook with the complete main analysis below:

import csv
import math
from statistics import NormalDist, mean, stdev

import duckdb
import matplotlib.pyplot as plt

DATA_FILE = "promotion_experiment.csv"
CHART_FILE = "promotion_experiment_summary.png"

EXPERIMENT_DESIGN = [
    {
        "variant": "control",
        "segment": "new",
        "users": 3000,
        "conversions": 240,
        "order_value": 80.0,
        "cost_of_goods": 45.0,
    },
    {
        "variant": "treatment",
        "segment": "new",
        "users": 3000,
        "conversions": 300,
        "order_value": 68.0,
        "cost_of_goods": 45.0,
    },
    {
        "variant": "control",
        "segment": "returning",
        "users": 3000,
        "conversions": 480,
        "order_value": 100.0,
        "cost_of_goods": 55.0,
    },
    {
        "variant": "treatment",
        "segment": "returning",
        "users": 3000,
        "conversions": 510,
        "order_value": 85.0,
        "cost_of_goods": 55.0,
    },
]


def create_dataset():
    rows = []
    user_number = 1

    for cell in EXPERIMENT_DESIGN:
        for position in range(cell["users"]):
            converted = int(position < cell["conversions"])
            revenue = cell["order_value"] if converted else 0.0
            cost_of_goods = cell["cost_of_goods"] if converted else 0.0
            profit = revenue - cost_of_goods

            rows.append(
                {
                    "user_id": f"U{user_number:05d}",
                    "variant": cell["variant"],
                    "segment": cell["segment"],
                    "converted": converted,
                    "revenue": revenue,
                    "cost_of_goods": cost_of_goods,
                    "profit": profit,
                }
            )
            user_number += 1

    with open(DATA_FILE, "w", newline="", encoding="utf-8") as csv_file:
        writer = csv.DictWriter(csv_file, fieldnames=rows[0].keys())
        writer.writeheader()
        writer.writerows(rows)

    return len(rows)


def query_rows(connection, sql):
    connection.execute(sql)
    columns = [column[0] for column in connection.description]
    return [dict(zip(columns, row)) for row in connection.fetchall()]


def print_table(rows):
    if not rows:
        print("No rows returned")
        return

    headers = list(rows[0].keys())
    widths = {
        header: max(len(header), *(len(str(row[header])) for row in rows))
        for header in headers
    }

    print(" | ".join(header.ljust(widths[header]) for header in headers))
    print("-+-".join("-" * widths[header] for header in headers))
    for row in rows:
        print(" | ".join(str(row[header]).ljust(widths[header]) for header in headers))


def mean_difference_ci(control_values, treatment_values, confidence=0.95):
    alpha = 1.0 - confidence
    critical_value = NormalDist().inv_cdf(1.0 - alpha / 2.0)
    difference = mean(treatment_values) - mean(control_values)
    standard_error = math.sqrt(
        stdev(control_values) ** 2 / len(control_values)
        + stdev(treatment_values) ** 2 / len(treatment_values)
    )
    margin = critical_value * standard_error
    return difference, difference - margin, difference + margin


row_count = create_dataset()
print(f"Created {row_count} rows in {DATA_FILE}\n")

con = duckdb.connect()

headline = query_rows(
    con,
    f"""
    SELECT
        variant,
        COUNT(*) AS users,
        ROUND(100.0 * AVG(converted), 2) AS conversion_rate_pct
    FROM '{DATA_FILE}'
    GROUP BY variant
    ORDER BY variant
    """,
)
print("Headline conversion result")
print_table(headline)

split_check = query_rows(
    con,
    f"""
    SELECT
        variant,
        COUNT(*) AS users,
        ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 2) AS traffic_share_pct
    FROM '{DATA_FILE}'
    GROUP BY variant
    ORDER BY variant
    """,
)
print("\nTraffic split check")
print_table(split_check)

kpis = query_rows(
    con,
    f"""
    SELECT
        variant,
        COUNT(*) AS users,
        SUM(converted) AS conversions,
        ROUND(100.0 * AVG(converted), 2) AS conversion_rate_pct,
        ROUND(SUM(revenue) / NULLIF(SUM(converted), 0), 2) AS average_order_value,
        ROUND(SUM(profit) / COUNT(*), 2) AS profit_per_visitor,
        ROUND(SUM(profit), 2) AS total_profit
    FROM '{DATA_FILE}'
    GROUP BY variant
    ORDER BY variant
    """,
)
print("\nExperiment KPI table")
print_table(kpis)

user_metrics = query_rows(
    con,
    f"""
    SELECT
        variant,
        CAST(converted AS DOUBLE) AS converted,
        CAST(profit AS DOUBLE) AS profit
    FROM '{DATA_FILE}'
    """,
)

control_conversion = [
    row["converted"] for row in user_metrics if row["variant"] == "control"
]
treatment_conversion = [
    row["converted"] for row in user_metrics if row["variant"] == "treatment"
]
control_profit = [
    row["profit"] for row in user_metrics if row["variant"] == "control"
]
treatment_profit = [
    row["profit"] for row in user_metrics if row["variant"] == "treatment"
]

conversion_effect = mean_difference_ci(control_conversion, treatment_conversion)
profit_effect = mean_difference_ci(control_profit, treatment_profit)

print("\nTreatment minus control effects with 95% confidence intervals")
print(
    "Conversion-rate difference: "
    f"{conversion_effect[0]:.4f} "
    f"({conversion_effect[1]:.4f}, {conversion_effect[2]:.4f})"
)
print(
    "Profit-per-visitor difference: "
    f"${profit_effect[0]:.2f} "
    f"(${profit_effect[1]:.2f}, ${profit_effect[2]:.2f})"
)

segments = query_rows(
    con,
    f"""
    SELECT
        segment,
        variant,
        COUNT(*) AS users,
        ROUND(100.0 * AVG(converted), 2) AS conversion_rate_pct,
        ROUND(SUM(profit) / COUNT(*), 2) AS profit_per_visitor
    FROM '{DATA_FILE}'
    GROUP BY segment, variant
    ORDER BY segment, variant
    """,
)
print("\nPre-defined customer segment results")
print_table(segments)

kpi_by_variant = {row["variant"]: row for row in kpis}
variants = ["control", "treatment"]
colors = ["#4C78A8", "#F58518"]
conversion_values = [
    float(kpi_by_variant[variant]["conversion_rate_pct"]) for variant in variants
]
profit_values = [
    float(kpi_by_variant[variant]["profit_per_visitor"]) for variant in variants
]

fig, axes = plt.subplots(1, 2, figsize=(10, 4))
conversion_bars = axes[0].bar(variants, conversion_values, color=colors)
axes[0].bar_label(conversion_bars, fmt=lambda value: f"{value:.1f}%")
axes[0].set_title("Conversion rate")
axes[0].set_ylabel("Percent of visitors")

profit_bars = axes[1].bar(variants, profit_values, color=colors)
axes[1].bar_label(profit_bars, fmt=lambda value: f"${value:.2f}")
axes[1].axhline(y=0, color="black", linewidth=0.8)
axes[1].set_title("Profit per visitor")
axes[1].set_ylabel("Dollars")

fig.tight_layout()
fig.savefig(CHART_FILE, dpi=160, bbox_inches="tight")
plt.show()

control_kpi = kpi_by_variant["control"]
treatment_kpi = kpi_by_variant["treatment"]
relative_conversion_lift = (
    float(treatment_kpi["conversion_rate_pct"])
    / float(control_kpi["conversion_rate_pct"])
    - 1.0
) * 100.0
profit_change = (
    float(treatment_kpi["profit_per_visitor"])
    - float(control_kpi["profit_per_visitor"])
)

recommendation = f"""
Recommendation: Do not launch the broad promotion.

Evidence:
- Conversion increased by {relative_conversion_lift:.1f}% relative, from {control_kpi['conversion_rate_pct']}% to {treatment_kpi['conversion_rate_pct']}%.
- Profit per visitor changed by ${profit_change:.2f}, from ${float(control_kpi['profit_per_visitor']):.2f} to ${float(treatment_kpi['profit_per_visitor']):.2f}.
- The 95% interval for the conversion effect stays above zero, while the interval for the profit effect stays below zero.
- Returning customers show the larger profit-per-visitor decline.

Next action: Test a smaller discount targeted to new customers instead of shipping the 15% promotion to everyone.
"""
print(recommendation)
print(f"Saved chart to {CHART_FILE}")

That's the analysis translated into a decision. Your case study now shows why a conversion win can still be the wrong launch.

Secret mission

Calculate a Break-even Discount

The rejected 15% promotion still contains a useful pricing signal. Calculate the highest discount each customer segment can sustain without dropping below control profit per visitor. Use the thresholds to shape a smaller new-customer retest.

Clean Up Your Resources

Clean Up Your Resources

Choose whether to keep the analysis available, pause your work, or delete the project. Google Colab is free of charge for this project.

Resources you used:

  • The promotion-experiment-analysis Google Colab notebook stored in Google Drive.
  • The promotion_experiment.csv dataset in the temporary Colab runtime.
  • The promotion_experiment_summary.png chart in the temporary Colab runtime.

Keep everything running

No action is needed for the notebook. Choose this if you want the analysis available for review or sharing.

  • Leave promotion-experiment-analysis in Google Drive.
  • Keep every notebook cell so another viewer can reproduce the full analysis.
  • Download promotion_experiment.csv from the Colab file browser if you want a persistent copy.
  • Download promotion_experiment_summary.png from the Colab file browser if you want a persistent copy.

The runtime copies can disappear when the temporary session expires.

Pause - I'll come back to this later

This option lets the temporary runtime expire while the saved notebook remains in Google Drive.

  • Confirm the latest analysis appears in promotion-experiment-analysis.
  • Close the Google Colab browser tab.
  • Reopen promotion-experiment-analysis from Google Drive when you are ready to continue.
  • Rerun the package installation cell when Colab connects a new runtime.
  • Rerun every remaining cell from top to bottom to recreate the analysis artifacts.

Delete - I don't want to use this again

This option removes the saved notebook. It also clears its temporary runtime artifacts.

  • Delete the current temporary runtime using Colab's runtime session controls.

Your promotion_experiment.csv file and promotion_experiment_summary.png file disappear with the runtime.

  • Return to Google Drive.
  • Locate promotion-experiment-analysis in your Drive files.
  • Use the notebook's context menu to move it to the trash.
  • Open the deleted-items area in Google Drive.
  • Permanently delete promotion-experiment-analysis.

Nice Work!

Nice Work!

You did it! Your Google Colab case study turns a tempting conversion win into a defensible decision against the broad promotion.

You've learned how to:

  • Built a reproducible promotion experiment analysis from a deterministic 12,000-user dataset using DuckDB SQL. Confirmed the 50/50 traffic split before trusting the outcome metrics.
  • Exposed the conversion-profit trade-off behind the headline conversion rate. Used 95% confidence intervals to show a reliable conversion increase. Confirmed a reliable profit-per-visitor decline. Located the larger downside among returning customers.
  • Created a two-panel decision chart with Matplotlib. Saved the shareable evidence as promotion_experiment_summary.png. Delivered an executive recommendation against launching the broad promotion.
  • Calculated break-even order values in the Secret Mission. Found $73.00 for new customers. Found $97.35 for returning customers. Converted those values into maximum discount thresholds of 8.75% for new customers plus 2.65% for returning customers. Proposed a smaller new-customer-focused follow-up test.

Ready to quiz yourself?