The Funnel That Leaks

In the Real World · Brief · Data science · Core · 1 to 2 days

Checkout conversion is down and two teams disagree about why. One says the site broke, the other says the tracking did. You have a month of GA4 events and have to settle it. Work it yourself before reading the walkthrough.

The situation

Wrenmoor sells home and garden online. Checkout completion has fallen over the past few weeks. The front end team shipped a checkout release during the period. The analytics team changed tags in the same window. Each points at the other, and the argument has been running for a week without evidence.

You have been given the raw event export and asked to say which it is, and if the loss is real, what it costs.

The data

One file, wrenmoor-ga4-events.csv, 35,994 events across 30 days, in the shape a GA4 BigQuery export produces. One row per event, uncleaned.

What the room believes

1. Checkout completion has genuinely fallen.
2. It is a tracking artefact and no orders were lost.
3. If it is real it affects all traffic, because it followed a release.
4. The reported revenue figure is trustworthy.

They are offered as alternatives. You do not have to accept that framing.

Definition of done

1. A verdict on each of the four beliefs, with evidence.
2. If something broke: the exact step, the segment, and the date, found from the data rather than taken from the deployment log.
3. An argument that distinguishes a site failure from a measurement failure, which is not a statistical test.
4. A cost, annualised, with the counterfactual stated and varied.
5. A separate list of any measurement faults you find that are real but are not the cause, with an owner for each.

If you want to go further

• Find the break date by scanning candidates rather than testing the one you suspect, and check whether the two agree.
• Put a confidence interval next to every segment rate before you decide which segments are affected. Count how many look alarming and how many survive.
• Take your counterfactual baseline from more than one place and see whether the cost estimate holds.
• Write the monitoring rule that would have caught this in a day instead of 18.

In the Real World · Data science · Core · 1 to 2 days

Checkout conversion fell and nobody could say whether it was real. Two explanations were on the table, a broken checkout or broken tracking, and the argument had been running for a week. Both turned out to be true. Only one of them was costing 936 pounds a day.

Section 1Problem Definition

No code yet. The question here is not where the funnel leaks, it is whether the leak is in the site or in the measurement, and those need different people to fix them.

Business objective

Checkout completion has dropped. Decide whether the drop is a real loss of orders or an artefact of how the events are recorded, and if it is real, say where and what it costs. The engineering team will not act without a specific step and a specific segment.

Success criteria

Hypotheses

1. H1. Checkout completion has genuinely fallen.
2. H2. The fall is a tracking artefact and no orders were lost.
3. H3. If real, it affects all traffic, because it followed a release.
4. H4. The reported revenue figure is trustworthy.

H1 and H2 are stated as alternatives on purpose. They are not, and discovering that both are true at once is the substance of this project.

Section 2Data Collection

A GA4 event export, one row per event, in the shape the BigQuery export produces.

import numpy as np
import pandas as pd
from scipy import stats

d = pd.read_csv('data/wrenmoor-ga4-events.csv', parse_dates=['event_timestamp'])
d['day'] = d['event_timestamp'].dt.day

print('events :', len(d))
print('users  :', d['user_pseudo_id'].nunique())
print('range  :', d['event_timestamp'].min().date(), 'to', d['event_timestamp'].max().date())
print()
print(d['event_name'].value_counts().to_string())

events : 35,994
users  : 6,171
range  : 2026-06-01 to 2026-06-30

purchase            1,031
add_payment_info    1,213
begin_checkout      1,752
add_to_cart         2,814
view_item           6,821
page_view          10,805
session_start      11,501

Section 3Data Preprocessing

Event data needs a different kind of preparation from tabular data. Nothing here is missing or mistyped. The work is turning a stream of events into sessions, and the very first decision is the one that most analyses get wrong.

3aDuplicates and schema checks

print('exact duplicate rows :', d.duplicated().sum())
print('duplicate events     :', d.duplicated(
      subset=['event_timestamp', 'event_name', 'user_pseudo_id', 'ga_session_id']).sum())

# THE KEY. ga_session_id is a session-start timestamp. It is unique within a
# user and means nothing across users, so two people can share one value.
shared = d.groupby('ga_session_id')['user_pseudo_id'].nunique()
print('ga_session_id values used by more than one user :', (shared > 1).sum())

d['session_key'] = d['user_pseudo_id'] + '|' + d['ga_session_id'].astype(str)
print('sessions on the composite key :', d['session_key'].nunique())
print('sessions on ga_session_id     :', d['ga_session_id'].nunique())

exact duplicate rows : 0
duplicate events     : 0
ga_session_id values used by more than one user : 26
sessions on the composite key : 11,501
sessions on ga_session_id     : 11,475

3bHandling categorical mess

for col in ['event_name', 'device_category', 'browser']:
    print(col, ':', sorted(d[col].unique()))

event_name : ['add_payment_info', 'add_to_cart', 'begin_checkout', 'page_view', 'purchase', 'session_start', 'view_item']
device_category : ['desktop', 'mobile', 'tablet']
browser : ['Chrome', 'Edge', 'Firefox', 'Safari', 'Samsung Internet']

Clean. Seven event names and no variants, which is what a properly governed measurement plan looks like. Worth confirming rather than assuming, because a renamed event halfway through a month is one of the classic causes of a funnel that appears to collapse.

3cDealing with outliers

The outlier in event data is not a large number. It is an event that should not exist.

pur = d[d['event_name'] == 'purchase']
print('purchase events      :', len(pur))
print('unique transactions  :', pur['transaction_id'].nunique())
print('surplus events       :', len(pur) - pur['transaction_id'].nunique())

# what are the surplus ones
dups = pur[pur['transaction_id'].duplicated(keep=False)]
g = dups.groupby('transaction_id')
print('groups               :', g.ngroups)
print('same user            :', (g['user_pseudo_id'].nunique() == 1).sum())
print('same value           :', (g['value'].nunique() == 1).sum())
print('median seconds apart :',
      g['event_timestamp'].apply(lambda s: (s.max() - s.min()).total_seconds()).median())

purchase events      : 1,088
unique transactions  : 1,031
surplus events       : 57
groups               : 57
same user            : 57
same value           : 57
median seconds apart : 7.0

57 surplus purchase events. Every one is the same user, the same transaction id and the same value, a median of 7.0 seconds after the first, never more than 10.0. A customer cannot place the same order twice in seven seconds. This is the purchase tag firing again on the confirmation page.

3dHandling missing values

ses = d.drop_duplicates('session_key')[['session_key', 'device_category',
                                        'browser', 'day']].copy()
for s in ['session_start', 'page_view', 'view_item', 'add_to_cart',
          'begin_checkout', 'add_payment_info', 'purchase']:
    ses[s] = ses['session_key'].isin(set(d.loc[d['event_name'] == s, 'session_key']))

print('sessions              :', len(ses))
print('sessions with no page_view :', (~ses['page_view']).sum(),
      f"({(~ses['page_view']).mean():.2%})")

sessions              : 11,501
sessions with no page_view : 696 (6.05%)

6.0% of sessions record a session start and no page view. That is not missing data to impute, it is a consent banner: the page view fires only after consent, and some visitors leave first or decline. Treat those sessions as real and count them in the denominator. Dropping them would quietly inflate every rate below.

3eHandling skewed data

Order values are right-skewed, as they are everywhere, with a mean order of 150.48 pounds. Nothing in this analysis takes the mean of a skewed column: the funnel is built on counts of sessions, and revenue only appears at the end to convert lost orders into money. Where a mean does get used, section 7c varies it.

3fData types and normalisation

The one transformation that matters is the one above: a stream of 35,994 events becomes 11,501 sessions with seven boolean columns, one per funnel step. Everything after this is a cut of that frame.

# does a later step ever appear without the earlier one it depends on
for a, b in [('view_item', 'page_view'), ('add_to_cart', 'view_item'),
             ('begin_checkout', 'add_to_cart'), ('add_payment_info', 'begin_checkout'),
             ('purchase', 'add_payment_info')]:
    print(f'{a:18s} without {b:18s} {(ses[a] & ~ses[b]).sum():5d} of {ses[a].sum():5d}')

view_item          without page_view            417 of  6821
add_to_cart        without view_item              0 of  2814
begin_checkout     without add_to_cart            0 of  1752
add_payment_info   without begin_checkout         0 of  1213
purchase           without add_payment_info       0 of  1031

Every dependency holds except one: 417 sessions view an item without a page view, which is the same consent effect from 3d arriving from a different direction. No session buys without a checkout, and none checks out without a basket. The export is well formed, so any drop we find below is about the site, not about the pipeline.

Section 4Exploratory Data Analysis

4aThe primary metric

steps = ['session_start', 'page_view', 'view_item', 'add_to_cart',
         'begin_checkout', 'add_payment_info', 'purchase']

prev = None
for s in steps:
    k = ses[s].sum()
    rate = '' if prev is None else f'   step {k/prev:6.1%}'
    print(f'{s:18s} {k:6,d}  {k/len(ses):6.1%} of sessions{rate}')
    prev = k

session_start      11,501  100.0% of sessions
page_view          10,805  94.0% of sessions   step 94.0%
view_item           6,821  59.3% of sessions   step 63.1%
add_to_cart         2,814  24.5% of sessions   step 41.2%
begin_checkout      1,752  15.2% of sessions   step 62.3%
add_payment_info    1,213  10.5% of sessions   step 69.2%
purchase            1,031   9.0% of sessions   step 85.0%

The funnel across the whole month. The biggest absolute losses are early, at view item and add to cart, which is normal. The step to watch is add payment info.

Read it the way a growth team would. The worst step rates are 63.1% into view item and 41.2% into add to cart, and those are where the volume is lost. They are also completely normal for a home and garden retailer, and they are not what changed.

The one that changed is 69.2% from begin checkout into add payment info. A month average hides when it moved, which is why 4b is a time series rather than another table.

4bNumerical variables

One series matters: the share of checkouts that reach the payment step, by day.

Checkouts reaching the payment step, by day, split by whether the session was Safari on a phone. One line falls off a cliff on 13 June. The other does not move.

That chart is the finding, and everything after it is confirmation. But it was drawn after the segment was known. Section 7b does it in the honest order: find the date first, without assuming one, then find the segment.

4cCategorical variables

Safari on a phone is 27.6% of all sessions. Large enough that a failure there moves the company-wide number, small enough that the company-wide number never looked catastrophic. That combination is why this ran for 18 days.

4dRelationships between variables

A funnel drop has to be attributed to a step before it can be attributed to a cause. Compare each step transition, before and after the break, for the affected segment and for everything else.

That single row is what makes the finding actionable. Telling engineering that checkout conversion is down starts a week of guessing. Telling them that begin_checkout fires and add_payment_info does not, on one browser, on one device, from one date, is a bug report.

4eTesting our hypotheses

H1 and H2 were written as alternatives and both are true. That is the most useful thing in the project: the room had spent a week arguing about which one it was, when the answer was that they were describing two different problems that happened to arrive together.

4fSubgroups

Change at the payment step by device and browser. Only one bar is drawn in red, because only one has confidence intervals that separate.

Section 5Metric Construction

def wilson(k, n, z=1.96):
    """Wilson score interval. Correct at the small counts a device and browser
    split produces, where the normal approximation gives intervals that are too
    narrow exactly where the argument is being made."""
    if n == 0:
        return 0.0, 0.0
    p = k / n
    denom = 1 + z * z / n
    centre = p + z * z / (2 * n)
    half = z * np.sqrt(p * (1 - p) / n + z * z / (4 * n * n))
    return (centre - half) / denom, (centre + half) / denom


# transactions, not purchase events
pur = d[d['event_name'] == 'purchase']
print('purchase events :', len(pur))
print('transactions    :', pur['transaction_id'].nunique())

purchase events : 1,088
transactions    : 1,031

Section 6Method Selection

Section 7Analysis and Validation

7aThe baseline the business believes

Two beliefs, held by two teams, and they cannot both be right. Marketing says checkout broke after the release. Engineering says the tags were changed in the same window and the drop is measurement. Neither has produced evidence beyond a dashboard line going down.

Both are partly right, which is the least satisfying outcome for the room and the most useful for the business. Beating this baseline means separating the two claims rather than picking a winner.

7bThe analysis

Find the date without assuming it

bc = ses[ses['begin_checkout']]

best = None
for cut in range(4, 28):
    a, b = bc[bc['day'] < cut], bc[bc['day'] >= cut]
    if len(a) < 50 or len(b) < 50:
        continue
    tbl = [[a['purchase'].sum(), (~a['purchase']).sum()],
           [b['purchase'].sum(), (~b['purchase']).sum()]]
    chi2 = stats.chi2_contingency(tbl, correction=False)[0]
    if best is None or chi2 > best[1]:
        best = (cut, chi2)

print('largest break at day', best[0], 'with chi2', round(best[1], 1))

largest break at day 13 with chi2 30.4

The scan ran across every candidate date from the 4th to the 27th and picked the 13th on the data alone. It matches the checkout release, which is reassuring rather than circular, because the scan was not told the release date. Had it landed three days earlier, that would have been the finding.

Locate the step, then the segment

CUT = best[0]
ses['period'] = np.where(ses['day'] >= CUT, 'from', 'before')

# every device and browser combination with enough volume to say anything
for (dev, br), g in ses.groupby(['device_category', 'browser']):
    a = g[(g['period'] == 'before') & g['begin_checkout']]
    b = g[(g['period'] == 'from')   & g['begin_checkout']]
    if len(a) < 20 or len(b) < 20:
        continue
    lo_a, hi_a = wilson(a['add_payment_info'].sum(), len(a))
    lo_b, hi_b = wilson(b['add_payment_info'].sum(), len(b))
    flag = 'SEPARATED' if hi_b < lo_a else 'overlap'
    print(f'{dev:8s} {br:17s} {a["add_payment_info"].mean():.3f} -> '
          f'{b["add_payment_info"].mean():.3f}  {flag}')

mobile   Safari            0.716 -> 0.133  SEPARATED
desktop  Firefox           0.833 -> 0.515  overlap
desktop  Edge              0.818 -> 0.736  overlap
desktop  Safari            0.829 -> 0.754  overlap
desktop  Chrome            0.803 -> 0.795  overlap
mobile   Chrome            0.773 -> 0.775  overlap
tablet   Safari            0.808 -> 0.815  overlap
mobile   Samsung Internet  0.857 -> 0.875  overlap

One segment out of 8. Safari on a phone, 71.6% to 13.3%, intervals 64.4% to 77.9% against 9.5% to 18.3%, nowhere near touching. Chi-square 139.3 on 169 and 226 checkouts, p below the smallest number floating point can represent.

Real, or tracking? The test that settles it

A broken payment form and a broken payment tag produce the same missing events. One thing separates them.

sm = ses[(ses['device_category'] == 'mobile') & (ses['browser'] == 'Safari')]

before = sm[sm['period'] == 'before']['begin_checkout'].sum()
after  = sm[sm['period'] == 'from'  ]['begin_checkout'].sum()
print(f'begin_checkout, Safari on mobile: {before} in 12 days, {after} in 18 days')
print(f'per day: {before/12:.1f} before, {after/18:.1f} after')

begin_checkout, Safari on mobile: 169 in 12 days, 226 in 18 days
per day: 14.1 before, 12.6 after

The duplicate purchase rate by segment. Flat across every browser and device, which is exactly what a tag problem looks like, and the opposite of the payment failure.

What it costs

pre_rate = sm[(sm['period'] == 'before') & sm['begin_checkout']]['purchase'].mean()
post     = sm[(sm['period'] == 'from')   & sm['begin_checkout']]

expected = len(post) * pre_rate
lost     = round(expected - post['purchase'].sum())   # whole orders only
aov      = round(pur.drop_duplicates('transaction_id')['value'].mean(), 2)

print(f'checkouts since the break : {len(post)}')
print(f'expected orders           : {expected:.0f}')
print(f'actual orders             : {post["purchase"].sum()}')
print(f'lost orders               : {lost:.0f}')
print(f'lost revenue              : {lost * aov:,.0f} over 18 days')
print(f'annualised                : {lost * aov / 18 * 365:,.0f}')

checkouts since the break : 226
expected orders           : 136
actual orders             : 24
lost orders               : 112
lost revenue              : 16,854 over 18 days
annualised                : 341,757

112 orders in 18 days, 16,854 pounds, running at 936 a day. Left alone for a year that is 341,757 pounds.

7cSensitivity and robustness

The cost rests on a counterfactual: what those checkouts would have converted at. Take that baseline from three different places and vary the order value.

Every version lands between 112 and 129 lost orders, and every version of the annualised figure is a number that gets a bug fixed the same week. The conclusion does not depend on the choice.

7dWhat would change the answer

• A second release on the same day. The scan finds a break, not a cause. The deployment log has to confirm what shipped on the 13th, and if two things shipped, this analysis cannot separate them.
• A Safari version split. The export carries a browser name and no version. If the failure is confined to one iOS version the fix is narrower and the traffic at risk is smaller. Adding browser version to the export costs nothing and would answer it.
• Sessions that never reached checkout. This measures the loss among people who got to the checkout page. If the failure also deterred returning customers from starting, the true cost is higher and this figure is a floor.
• The duplicate tag interacting with the counterfactual. It inflates purchases uniformly, so it inflates the pre-break baseline slightly and makes the estimate marginally conservative. Deduplicating first, as section 5 does, removes it.

Section 8Decision and Handoff

What to do, and who owns it

What not to do

• Do not treat the duplicate tag as the explanation. It inflates orders. The problem is that orders fell.
• Do not act on desktop Firefox. It is the second-worst row and its intervals overlap. 33 sessions cannot support a bug report.
• Do not deduplicate by dropping purchase rows blindly. Deduplicate on transaction id and keep the first. Dropping every row in a duplicate group would remove real orders too.
• Do not report this as a checkout conversion problem. It is one transition, on one browser, on one device, from one date. The narrower the statement, the faster it gets fixed.

Reproducibility

What to take from this

• Key your sessions on user plus session id. Every funnel built on the session id alone is a coincidence, and on a busy property it is a spectacular one.
• Localise to a transition before you attribute a cause. The step before and the step after were both healthy, and that pair of facts is what turned a vague complaint into a bug report.
• Real failures respect browser boundaries. Tracking failures do not. That asymmetry is the cheapest diagnostic in this whole project.
• Check the volume of the event before the missing one. If it still fires, the container is alive and the problem is the site.
• Find the break date from the data. Testing the release date you already suspect will confirm it whether or not it is true.
• Put an interval next to every rate. One of the 8 segments here looked broken and was not.

The argument in the room was whether the problem was real or tracking. The answer was both, they were unrelated, and only one of them was worth interrupting a sprint for. Getting to that took locating a single transition, on a single segment, from a single date, and everything else in this project exists to make that statement safe to make.