Build a Funnel Conversion Table from Event Data

A product team exports an event log — every visit, sign-up, trial start and purchase, one row per event — and asks the obvious question: of the people who came, how many signed up, how many started a trial, how many bought, and where do we lose them? That is a funnel. Counting distinct users per event type looks like the answer and is not: someone who bought without ever starting a trial, or who signed up a year after their visit, inflates the later steps, and a person with five visits counts five times unless you are careful. A proper funnel follows each user through the steps in order, within a time window, and counts each user once per step. This page builds that with pandas, adds conversion and drop-off, breaks the funnel down by acquisition channel, measures how long each step takes, and writes the table to Excel. It belongs to Creating Pivot Tables from Excel Data.

Prerequisites

pip install pandas openpyxl

events.xlsx is an event log of about 10,000 rows for 4,000 users in August and September 2026: user ID, event name (visit, signup, trial, purchase), timestamp and acquisition channel. Like real logs it is unsorted, has repeated visits per user, and contains a few out-of-order events — purchases without a trial, and sign-ups recorded before the first visit.

Step 1 — The naive count, and why it misleads

Counting distinct users per event is quick:

import pandas as pd

events = pd.read_excel("events.xlsx")
STEPS = ["visit", "signup", "trial", "purchase"]
naive = events.groupby("Event")["User"].nunique().reindex(STEPS)
print(naive.to_string())

These numbers count anyone who ever had each event, regardless of order. A user who bought directly through a salesperson appears under "purchase" without having started a trial, and a user whose sign-up was logged before their first visit appears under "signup" without having come through the front door first. Each such case makes the later steps look better than the journey actually performs. The differences are small here, but in real logs — with imports from other systems, re-used accounts and clock problems — they can be large, and they are invisible in a naive count.

Step 2 — First time each user reached each step

Reduce the log to one timestamp per user and step — the first time it happened — and spread the steps into columns:

first = (events.groupby(["User", "Event"])["Timestamp"].min()
               .unstack("Event").reindex(columns=STEPS))
channel = events.groupby("User")["Channel"].first()
print(first.head(4))
print("users:", len(first))

Each row is now one user and each column the first time they reached a step, or NaT if never. Repeated visits collapse to the earliest, so heavy browsers count once. The channel is taken per user (here every event of a user shares a channel; in real data you would choose the first-touch channel deliberately). This wide shape makes the ordered checks in the next step one comparison per column.

Step 3 — An ordered funnel with a conversion window

A user counts for a step only if they also reached the previous step, after it, and within a window — here 30 days from their first visit:

WINDOW = pd.Timedelta(days=30)
reached = pd.DataFrame(index=first.index)
reached["visit"] = first["visit"].notna()
prev_time = first["visit"]
for step in STEPS[1:]:
    ok = reached[STEPS[STEPS.index(step) - 1]] & first[step].notna() & (first[step] >= prev_time) \
         & (first[step] <= first["visit"] + WINDOW)
    reached[step] = ok
    prev_time = first[step].where(ok)

funnel = pd.DataFrame({"Users": reached.sum()})
funnel["Naive count"] = naive
funnel["Step conversion %"] = (funnel["Users"] / funnel["Users"].shift(1) * 100).round(1)
funnel["Overall %"] = (funnel["Users"] / funnel["Users"].iloc[0] * 100).round(1)
funnel["Lost at this step"] = (funnel["Users"].shift(1) - funnel["Users"]).fillna(0).astype(int)
print(funnel.to_string())

Each step requires the previous step to be reached, the event to happen no earlier than the previous one, and within 30 days of the first visit. The ordered counts are lower than the naive ones at every step after the first — 1,085 sign-ups instead of 1,118, and 227 purchases instead of 266. The sign-ups logged before a visit, the purchases without a trial and the purchases that came more than 30 days after the first visit no longer count. The purchase step shrinks by about 15%, which is exactly the kind of overstatement a naive funnel hands to whoever is setting conversion targets. Step conversion divides each step by the one before; overall conversion divides by the number of visitors. The "lost" column is what product teams act on — the largest loss is the visit-to-signup step, which is typical: most visitors leave without signing up. The window is a business choice; a longer window counts slow converters, a shorter one gives an earlier, comparable read on recent cohorts.

Step 4 — The funnel by channel, and time between steps

The same reached-flags grouped by channel show where each channel loses people, and the timestamps give the typical time between steps:

by_channel = reached.groupby(channel).sum()[STEPS]
rates = by_channel.div(by_channel["visit"], axis=0).mul(100).round(1)
rates.columns = [f"{s} %" for s in STEPS]
print(pd.concat([by_channel, rates.iloc[:, 1:]], axis=1).to_string())

def median_gap(a, b):
    mask = reached[b]
    return (first.loc[mask, b] - first.loc[mask, a]).median()

for a, b in zip(STEPS, STEPS[1:]):
    print(f"{a} → {b}: median {median_gap(a, b)}")

Email visitors convert best at every step — 12.2% of them end up purchasing — and Social worst at 2.1% — the kind of difference that changes where marketing money goes. The median time from visit to sign-up is under a day, from sign-up to trial a day or two, and from trial to purchase about a week, which suggests when follow-up emails should be sent. Medians are used because conversion times are heavily skewed: a few users who come back weeks later would drag a mean far from the typical journey.

Step 5 — Write the funnel to Excel

from openpyxl.formatting.rule import DataBarRule
from openpyxl.styles import Font

with pd.ExcelWriter("funnel.xlsx", engine="openpyxl") as xw:
    funnel.reset_index(names="Step").to_excel(xw, sheet_name="Funnel", index=False)
    pd.concat([by_channel, rates.iloc[:, 1:]], axis=1).to_excel(xw, sheet_name="By channel")
    ws = xw.sheets["Funnel"]
    ws.conditional_formatting.add(f"B2:B{len(funnel) + 1}", DataBarRule(start_type="num", start_value=0,
                                  end_type="max", color="4338CA", showValue=True))
    for c in ws[1]:
        c.font = Font(bold=True)
    for col, w in zip("ABCDEF", (10, 12, 12, 18, 11, 16)):
        ws.column_dimensions[col].width = w
print("written: Funnel and By channel sheets")

Data bars on the user counts turn the Funnel sheet into a funnel picture without a chart, and the By channel sheet holds the per-channel counts and rates for anyone who wants to dig further. Data bars and other visual conditional formats are covered in Add Data Bars and Colour Scales with openpyxl, and a chart version in Create a Funnel Chart in Excel with Python.

Funnels over time

A single funnel answers "how do we convert overall"; teams usually also want to know whether conversion is improving. Group the per-user flags by the week or month of the first visit — a cohort — instead of by channel, and each row becomes the funnel for the visitors who arrived in that period. Read the most recent cohorts with care: visitors from the last few days have not yet had the full 30-day window to sign up and buy, so their conversion looks artificially low. Either leave cohorts out until their window has closed, or show them greyed out with a note. The retention version of this idea is covered in Build a Cohort Retention Table from Excel Data.

Common pitfalls

• Counting events instead of users. Repeat visits inflate the top of the funnel; take the first per user.
• Ignoring order. Users who skip or reorder steps make later steps look better.
• No conversion window. Old visitors keep trickling into later steps; fix a window.
• Comparing recent cohorts too early. Users who visited yesterday have not had time to convert.
• Means for conversion times. Use medians; the distribution is skewed.

Conclusion

A conversion funnel from an event log takes three steps: reduce the log to each user's first timestamp per step, require each step to follow the previous one within a conversion window, and count users per step. From those flags come step and overall conversion, drop-off at each step, a channel breakdown and median times between steps. Write the table with data bars for a quick visual, and remember that recent visitors need time before their conversion rates are comparable.

Frequently asked questions

How do I calculate funnel conversion rates in pandas? Take each user's first timestamp for every step, mark a step as reached only if the previous step was reached earlier and within a time window, sum the flags per step, and divide each step by the previous one and by the first step.

Why is my funnel larger than expected at later steps? Counting distinct users per event includes people who skipped earlier steps or whose events are out of order. Require each step to follow the previous one.

What conversion window should I use? One that matches your sales cycle, for example 7 days for a checkout and 30 or 90 days for a subscription trial. Apply it consistently so periods can be compared.

How do I compare funnels between channels? Group the per-user reached flags by channel, sum them, and divide by each channel's number of visitors.

Related

• Up one level: Creating Pivot Tables from Excel Data — summaries and crosstabs.
• Build a Cohort Retention Table from Excel Data — what happens after the purchase.
• Count Distinct Values per Group in pandas — the naive count done properly.
• Build a Sales Pipeline Report — the B2B version of a funnel.