Data Pipeline: Download, Clean & Enrich

This notebook walks through the three-step pipeline that transforms raw Kaggle data into the enriched parquet file consumed by all downstream analysis.

Steps:

  1. Understand the raw dataset schema and its limitations
  2. Clean by removing mismatched assignments and deduplicating users
  3. Enrich with synthetic behavioral columns (device, country, revenue, etc.) for portfolio depth

Every decision is documented with its business rationale. Reproducibility is guaranteed via seed=42 throughout.

In [1]:
import matplotlib
matplotlib.use('Agg')
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import matplotlib.ticker as mtick
import warnings
warnings.filterwarnings('ignore')
plt.style.use('seaborn-v0_8-whitegrid')
plt.rcParams['figure.figsize'] = (10, 5)

Matplotlib is building the font cache; this may take a moment.

Step 1: Understanding the Raw Data

The original Kaggle dataset (ab_data.csv) contains roughly 294,000 rows, one per user-experiment event. The experiment ran for 22 days in January 2017 and tests a new e-commerce landing page design against the existing one.

The five raw columns are:

Column Type Description
user_id int Unique user identifier (may repeat — duplicates exist)
timestamp datetime Event time
group str Experiment assignment: control or treatment
landing_page str Page actually served: old_page or new_page
converted int Binary outcome: 1 = user converted, 0 = did not

Critical design contract: A user assigned to control must see old_page; treatment must see new_page. Violations of this contract indicate logging errors or experiment leakage and must be removed before any inference.

In [2]:
df_enriched = pd.read_parquet('../data/processed/ab_data_enriched.parquet')
# Show a sample of core columns — mirrors the raw schema before enrichment
raw_cols = ['user_id', 'timestamp', 'group', 'landing_page', 'converted']
df_enriched[raw_cols].head(10)
Out[2]:
user_id timestamp group landing_page converted
0 851104 2017-01-21 22:11:48.556739 control old_page 0
1 804228 2017-01-12 08:01:45.159739 control old_page 0
2 661590 2017-01-11 16:55:06.154213 treatment new_page 0
3 853541 2017-01-08 18:28:03.143765 treatment new_page 0
4 864975 2017-01-21 01:52:26.210827 control old_page 1
5 936923 2017-01-10 15:20:49.083499 control old_page 0
6 679687 2017-01-19 03:26:46.940749 treatment new_page 1
7 719014 2017-01-17 01:48:29.539573 control old_page 0
8 817355 2017-01-04 17:58:08.979471 treatment new_page 1
9 839785 2017-01-15 18:11:06.610965 treatment new_page 1
In [3]:
print(f"Total rows: {len(df_enriched):,}")
print(f"Columns: {list(df_enriched.columns)}")
print(f"\nData types:\n{df_enriched.dtypes}")

Total rows: 290,584
Columns: ['user_id', 'timestamp', 'group', 'landing_page', 'converted', 'device_type', 'browser', 'country', 'user_segment', 'traffic_source', 'revenue', 'session_duration_sec', 'pages_viewed']

Data types:
user_id                          int64
timestamp               datetime64[us]
group                              str
landing_page                       str
converted                        int64
device_type                        str
browser                            str
country                            str
user_segment                       str
traffic_source                     str
revenue                        float64
session_duration_sec           float64
pages_viewed                     int64
dtype: object

Step 2: Cleaning Decision — Removing Mismatched Rows

In the raw data, ~3,893 rows violate the assignment contract:

  • control users who were served new_page (~1,965 rows)
  • treatment users who were served old_page (~1,928 rows)

Why we remove them, not recode them:

  1. Causal contamination: A control user who saw the new page received the treatment. Their outcome cannot be attributed to either variant cleanly.
  2. Bias in both directions: Leaving them inflates variance and distorts both conversion rate estimates — the direction of the bias is not predictable without knowing why the mismatch occurred.
  3. Minimal information loss: ~3,893 rows = 1.3% of the dataset. Removing them has negligible impact on statistical power.

Alternative considered: Reassigning mismatched rows to the variant they actually saw. This was rejected because it changes the intent-to-treat (ITT) estimand to an as-treated estimand — a valid choice, but not what the experiment was designed to measure.

In [4]:
# Simulate mismatch detection on the enriched data
# (The pipeline already removed them; we show what the detection rule looks like)
mismatches_control = df_enriched[
    (df_enriched['group'] == 'control') & (df_enriched['landing_page'] == 'new_page')
]
mismatches_treatment = df_enriched[
    (df_enriched['group'] == 'treatment') & (df_enriched['landing_page'] == 'old_page')
]
print(f"Remaining mismatches in clean data: {len(mismatches_control) + len(mismatches_treatment)}")
print("(All mismatches were removed in the cleaning step)")
print(f"\nFinal dataset shape: {df_enriched.shape}")
print(f"Control group:   {(df_enriched['group']=='control').sum():,} users")
print(f"Treatment group: {(df_enriched['group']=='treatment').sum():,} users")

Remaining mismatches in clean data: 0
(All mismatches were removed in the cleaning step)

Final dataset shape: (290584, 13)
Control group:   145,274 users
Treatment group: 145,310 users

Step 3: Cleaning Decision — Deduplication

Some user_id values appear more than once in the raw data (e.g., a user who visited on multiple days). When the same user has conflicting converted values across records, we must choose a deduplication strategy.

Decision: keep the first record per user_id (sorted by timestamp).

Rationale:

  • We are measuring first-exposure conversion — did the user convert the first time they saw the page? This matches the business question: "Does the new design convert visitors?"
  • Alternative (keep last record) would conflate returning behavior with first-impression response, biasing results toward users with more sessions.
  • Alternative (keep converted=1 if any record is 1) would overcount conversions, inflating both groups' rates and potentially masking the differential effect.

The conservative first-record approach is the most defensible for an intent-to-treat analysis.

Step 4: Synthetic Enrichment (seed=42)

The original 5-column dataset is sufficient for a basic statistical test, but a portfolio project needs to demonstrate segmentation analysis, Simpson's Paradox detection, and multi-dimensional breakdowns. We synthetically added:

Column Type Description
device_type str mobile, desktop, tablet
browser str chrome, firefox, safari, edge
country str US, UK, DE, FR, BR
revenue float Revenue per user (0 if not converted, positive otherwise)
session_duration_sec float Time on page (seconds)
pages_viewed int Pages visited in session
user_segment str new_user, returning_user
traffic_source str organic, paid, social, email

Why seed=42? Reproducibility. Every pipeline run generates identical synthetic values, so analysis results are deterministic across environments and reviewers.

Key design choice — heterogeneous treatment effects were deliberately engineered:

  • Mobile users have a stronger positive treatment effect than desktop users
  • New users respond better to the redesigned page than returning users
  • This creates a realistic Simpson's Paradox scenario: the aggregate lift (+2.4%) is primarily driven by new mobile users, while returning desktop users see minimal benefit

This design choice adds analytical depth without distorting the core frequentist result, which is computed solely from the original converted column.

In [5]:
synthetic_cols = [
    'device_type', 'browser', 'country', 'revenue',
    'session_duration_sec', 'user_segment', 'traffic_source'
]
print("Synthetic column distributions:\n")
for col in ['device_type', 'browser', 'country', 'user_segment', 'traffic_source']:
    print(f"{col}:")
    print(df_enriched[col].value_counts().to_string())
    print()

Synthetic column distributions:

device_type:
device_type
mobile     159506
desktop    102052
tablet      29026

browser:
browser
Chrome     174412
Safari      58207
Firefox     34612
Edge        23353

country:
country
US           116116
UK            43420
Canada        29052
Germany       23210
France        20434
Australia     14613
India         14593
Brazil        11556
Spain          8902
Japan          8688

user_segment:
user_segment
new          203628
returning     86956

traffic_source:
traffic_source
organic     116690
paid         87194
social       57876
referral     28824
In [6]:
fig, axes = plt.subplots(1, 2, figsize=(12, 4))
for ax, group in zip(axes, ['control', 'treatment']):
    data = df_enriched[df_enriched['group'] == group]['revenue']
    ax.hist(data[data > 0], bins=50, alpha=0.7,
            color='steelblue' if group == 'control' else 'coral')
    ax.set_title(f'Revenue Distribution — {group.capitalize()}')
    ax.set_xlabel('Revenue ($)')
    ax.set_ylabel('Count')
    ax.axvline(data.mean(), color='red', linestyle='--', label=f'Mean: ${data.mean():.2f}')
    ax.legend()
plt.tight_layout()
plt.savefig('/tmp/revenue_dist.png', dpi=100, bbox_inches='tight')
plt.show()
print(f"Control mean revenue:   ${df_enriched[df_enriched['group']=='control']['revenue'].mean():.4f}")
print(f"Treatment mean revenue: ${df_enriched[df_enriched['group']=='treatment']['revenue'].mean():.4f}")

Control mean revenue:   $7.3765
Treatment mean revenue: $8.2765

Final Dataset Summary

The pipeline produces a single enriched parquet file with the following characteristics:

  • 290,584 users after mismatch removal and deduplication (~1.3% removed)
  • 13 columns: 5 original + 8 synthetic enrichment columns
  • Balanced groups: ~145K control, ~145K treatment (SRM test will verify this in notebook 02)
  • Date range: 22-day window in January 2017
  • Conversion rates: ~12.0% control, ~12.3% treatment (a +2.4% relative lift)

The enriched file is the single source of truth for all downstream analysis notebooks and the FastAPI backend that powers the interactive dashboard.

In [7]:
summary = {
    'Total users': len(df_enriched),
    'Control users': int((df_enriched['group'] == 'control').sum()),
    'Treatment users': int((df_enriched['group'] == 'treatment').sum()),
    'Overall conversion rate': df_enriched['converted'].mean(),
    'Control conversion rate': df_enriched[df_enriched['group'] == 'control']['converted'].mean(),
    'Treatment conversion rate': df_enriched[df_enriched['group'] == 'treatment']['converted'].mean(),
    'Date range': f"{pd.to_datetime(df_enriched['timestamp']).min().date()} to {pd.to_datetime(df_enriched['timestamp']).max().date()}",
}
for k, v in summary.items():
    if isinstance(v, float):
        if 'rate' in k.lower():
            print(f"{k}: {v:.4%}")
        else:
            print(f"{k}: {v:.4f}")
    elif isinstance(v, (int, np.integer)):
        print(f"{k}: {v:,}")
    else:
        print(f"{k}: {v}")

Total users: 290,584
Control users: 145,274
Treatment users: 145,310
Overall conversion rate: 12.1831%
Control conversion rate: 12.0386%
Treatment conversion rate: 12.3274%
Date range: 2017-01-02 to 2017-01-24