xmetricRecipes

Recipe Tableau and SQL

How do I build a conversion funnel from an event log?

Count a user at a step only if they reached it after the step before, within a time window. Counting everyone who fired each event, this meal-kit site's funnel says 149 of 717 visitors (21%) subscribe and the weakest step is signing up. Counted in order within 7 days, 8.4% subscribe, and the weakest step is Subscribe.

Updated

The data

Basil Box meal-kit site, product events, September to October 2025

Synthetic dataevents.csv

797 people on a meal-kit site over two months: visit, sign up, build a box, subscribe. Some are existing subscribers renewing, some block tracking, some build a box as a guest before signing up.

user_idevent_dateevent
U-03872025-09-01visit
U-03902025-09-01visit
U-04182025-09-01visit
U-05202025-09-01visit

Why do the two funnels disagree?

Because an event log holds more than your funnel. Count everyone who fired each event and 149 of 717 visitors appear to subscribe. But 67 of those subscribers never visited in this data: existing customers renewing, and people who block tracking. Count only people who visited, signed up, built a box and subscribed, in that order and within 7 days, and 60 of 717 remain. The open funnel puts the weakest step at signing up; in order, it is Subscribe (37.3%).

Step 1

Count everyone who fired each event

This is the open funnel, and the one most dashboards show. It mixes in people who never did the earlier steps in this data: existing subscribers renewing, visitors with ad blockers, guests. [Step] is a CASE on [event] that names the four steps; sort it by hand, not alphabetically.

Tableau

COUNTD([user_id])
// with [Step] on Columns, sorted 1 to 4

SQL

SELECT
  CASE event
    WHEN 'visit' THEN 1
    WHEN 'signup' THEN 2
    WHEN 'build_box' THEN 3
    ELSE 4
    END AS step_no,
  CASE event
    WHEN 'visit' THEN 'Visit'
    WHEN 'signup' THEN 'Sign up'
    WHEN 'build_box' THEN 'Build a box'
    ELSE 'Subscribe'
    END AS step,
  COUNT(DISTINCT user_id) : : INTEGER AS users
FROM input
GROUP BY ALL
ORDER BY step_no

Rows

Step noStepUsers
1Visit717
2Sign up319
3Build a box228
4Subscribe149

Step 2

Find each user's steps, in order

One row per visitor. Each date is the first time the user did that step after the step before and within the window from the visit. In Tableau, chain four FIXED fields the same way: [Visit date] is { FIXED [user_id] : MIN(IF [event] = 'visit' THEN [event_date] END) }, [Signup date] is the formula above, and [Box date] and [Subscribe date] each start from the date before them. An empty cell is where the user dropped off.

Tableau

{ FIXED [user_id] : MIN(IF [event] = 'signup' AND [event_date] >= [Visit date] AND [event_date] <= DATEADD('day', 7, [Visit date]) THEN [event_date] END) }

SQL

WITH v AS (
  SELECT user_id, MIN(event_date) AS visit_date
  FROM input
  WHERE event = 'visit'
  GROUP BY user_id
  HAVING '{knob.cohort}' = 'All visitors' OR MIN(event_date) <= (
    SELECT MAX(event_date)
    FROM input
  ) - 7
), s AS (
  SELECT v.user_id, v.visit_date, MIN(e.event_date) AS signup_date
  FROM v JOIN input e ON e.user_id = v.user_id AND e.event = 'signup' AND e.event_date BETWEEN v.visit_date AND v.visit_date + 7
  GROUP BY v.user_id, v.visit_date
), b AS (
  SELECT s.user_id, s.visit_date, MIN(e.event_date) AS box_date
  FROM s JOIN input e ON e.user_id = s.user_id AND e.event = 'build_box' AND e.event_date BETWEEN s.signup_date AND s.visit_date + 7
  GROUP BY s.user_id, s.visit_date
), p AS (
  SELECT b.user_id, MIN(e.event_date) AS subscribe_date
  FROM b JOIN input e ON e.user_id = b.user_id AND e.event = 'subscribe' AND e.event_date BETWEEN b.box_date AND b.visit_date + 7
  GROUP BY b.user_id
)
SELECT
  v.user_id,
  v.visit_date,
  s.signup_date,
  b.box_date,
  p.subscribe_date
FROM v LEFT JOIN s USING (user_id) LEFT JOIN b USING (user_id) LEFT JOIN p USING (user_id)
ORDER BY v.visit_date, v.user_id

Rows

User IDEvent dateSignup dateBox dateSubscribe date
U-03872025-09-012025-09-022025-09-03–
U-03902025-09-01–––
U-04182025-09-01–––
U-05202025-09-01–––
U-06122025-09-012025-09-022025-09-02–
U-06372025-09-012025-09-032025-09-03–
U-07132025-09-01–––
U-00692025-09-02–––
U-02672025-09-02–––
U-03242025-09-02–––
U-03832025-09-022025-09-022025-09-022025-09-02
U-06102025-09-022025-09-022025-09-052025-09-06
U-06432025-09-02–––
U-06602025-09-022025-09-07––
U-07472025-09-022025-09-02––
U-01512025-09-03–––
U-05492025-09-03–––
U-06222025-09-032025-09-032025-09-03–
U-06942025-09-032025-09-04––
U-07022025-09-03–––
U-07422025-09-03–––
U-07522025-09-032025-09-032025-09-032025-09-03
U-02782025-09-042025-09-062025-09-06–
U-03552025-09-04–––
U-03662025-09-042025-09-042025-09-04–
U-04172025-09-04–––
U-05352025-09-04–––
U-05422025-09-042025-09-042025-09-04–
U-06322025-09-042025-09-04––
U-07382025-09-04–––
U-00032025-09-05–––
U-00402025-09-05–––
U-01132025-09-05–––
U-01652025-09-05–––
U-02042025-09-052025-09-05––
U-02612025-09-05–––
U-02952025-09-05–––
U-03072025-09-052025-09-052025-09-05–
U-04002025-09-05–––
U-04442025-09-05–––
U-05552025-09-052025-09-05––
U-05742025-09-052025-09-062025-09-06–
U-05912025-09-05–––
U-06262025-09-05–––
U-07332025-09-052025-09-07––
U-00812025-09-06–––
U-01332025-09-06–––
U-01922025-09-06–––
U-02092025-09-06–––
U-02252025-09-06–––

First 50 of 717 rows.

Step 3

Count users who reached each step in order

[Step date] is a CASE that returns [Visit date] on visit rows, [Signup date] on signup rows, and so on, so only the event that counts for the funnel matches. Every bar is now a subset of the one before it, and the drop between two bars is real drop-off.

Tableau

COUNTD(IF [event_date] = [Step date] THEN [user_id] END)

SQL

WITH v AS (
  SELECT user_id, MIN(event_date) AS visit_date
  FROM input
  WHERE event = 'visit'
  GROUP BY user_id
  HAVING '{knob.cohort}' = 'All visitors' OR MIN(event_date) <= (
    SELECT MAX(event_date)
    FROM input
  ) - 7
), s AS (
  SELECT v.user_id, v.visit_date, MIN(e.event_date) AS signup_date
  FROM v JOIN input e ON e.user_id = v.user_id AND e.event = 'signup' AND e.event_date BETWEEN v.visit_date AND v.visit_date + 7
  GROUP BY v.user_id, v.visit_date
), b AS (
  SELECT s.user_id, s.visit_date, MIN(e.event_date) AS box_date
  FROM s JOIN input e ON e.user_id = s.user_id AND e.event = 'build_box' AND e.event_date BETWEEN s.signup_date AND s.visit_date + 7
  GROUP BY s.user_id, s.visit_date
), p AS (
  SELECT b.user_id, MIN(e.event_date) AS subscribe_date
  FROM b JOIN input e ON e.user_id = b.user_id AND e.event = 'subscribe' AND e.event_date BETWEEN b.box_date AND b.visit_date + 7
  GROUP BY b.user_id
), steps AS (
  SELECT 1 AS step_no, 'Visit' AS step, COUNT(*) AS users
  FROM v
  UNION ALL 
  SELECT 2, 'Sign up', COUNT(*)
  FROM s
  UNION ALL 
  SELECT 3, 'Build a box', COUNT(*)
  FROM b
  UNION ALL 
  SELECT 4, 'Subscribe', COUNT(*)
  FROM p
)
SELECT step_no, step, users : : INTEGER AS users
FROM steps
ORDER BY step_no

Rows

Step noStepUsers
1Visit717
2Sign up294
3Build a box161
4Subscribe60

Step 4

Add step-to-step and overall conversion

LOOKUP(..., -1) reads the step on the left, so each rate is that step's users over the previous step's. LOOKUP(..., FIRST()) reads the first step, which gives the share of all visitors. In SQL these are LAG and FIRST_VALUE over the step number. Both depend on the steps being in funnel order.

Tableau

[Strict users] / LOOKUP([Strict users], -1)
// [Strict users] is the COUNTD from the step before; use LOOKUP([Strict users], FIRST()) for the share of visitors; Compute Using: Step

SQL

WITH v AS (
  SELECT user_id, MIN(event_date) AS visit_date
  FROM input
  WHERE event = 'visit'
  GROUP BY user_id
  HAVING '{knob.cohort}' = 'All visitors' OR MIN(event_date) <= (
    SELECT MAX(event_date)
    FROM input
  ) - 7
), s AS (
  SELECT v.user_id, v.visit_date, MIN(e.event_date) AS signup_date
  FROM v JOIN input e ON e.user_id = v.user_id AND e.event = 'signup' AND e.event_date BETWEEN v.visit_date AND v.visit_date + 7
  GROUP BY v.user_id, v.visit_date
), b AS (
  SELECT s.user_id, s.visit_date, MIN(e.event_date) AS box_date
  FROM s JOIN input e ON e.user_id = s.user_id AND e.event = 'build_box' AND e.event_date BETWEEN s.signup_date AND s.visit_date + 7
  GROUP BY s.user_id, s.visit_date
), p AS (
  SELECT b.user_id, MIN(e.event_date) AS subscribe_date
  FROM b JOIN input e ON e.user_id = b.user_id AND e.event = 'subscribe' AND e.event_date BETWEEN b.box_date AND b.visit_date + 7
  GROUP BY b.user_id
), steps AS (
  SELECT 1 AS step_no, 'Visit' AS step, COUNT(*) AS users
  FROM v
  UNION ALL 
  SELECT 2, 'Sign up', COUNT(*)
  FROM s
  UNION ALL 
  SELECT 3, 'Build a box', COUNT(*)
  FROM b
  UNION ALL 
  SELECT 4, 'Subscribe', COUNT(*)
  FROM p
)
SELECT
  step_no,
  step,
  users : : INTEGER AS users,
  ROUND(users / LAG(users) OVER (ORDER BY step_no), 3) AS step_conversion,
  ROUND(users / FIRST_VALUE(users) OVER (ORDER BY step_no), 3) AS share_of_visitors
FROM steps
ORDER BY step_no

Rows

Step noStepUsersStep conversionShare of visitors
1Visit717–100%
2Sign up29441%41%
3Build a box16154.8%22.5%
4Subscribe6037.3%8.4%

Common mistake

Reading drop-off from the open funnel

The open funnel says 65% of box builders subscribe and the worst step is signing up. In order, within a week, only 37% of box builders subscribe, and that last step is the worst. The open count includes 67 people who subscribed without a visit in this data: existing customers renewing, and visitors whose tracking was blocked.

WrongStarts with Visit, Sign up, Build a box…
Point at a bar to read it.
0200400600VisitSign upBuild a boxSubscribe

Tableau

COUNTD([user_id])
// per step, then LOOKUP(..., -1)

SQL

WITH o AS (
  SELECT
    CASE event
      WHEN 'visit' THEN 1
      WHEN 'signup' THEN 2
      WHEN 'build_box' THEN 3
      ELSE 4
      END AS step_no,
    CASE event
      WHEN 'visit' THEN 'Visit'
      WHEN 'signup' THEN 'Sign up'
      WHEN 'build_box' THEN 'Build a box'
      ELSE 'Subscribe'
      END AS step,
    COUNT(DISTINCT user_id) : : INTEGER AS users
  FROM input
  GROUP BY ALL
  ORDER BY step_no
)
SELECT
  step,
  users,
  ROUND(users / LAG(users) OVER (ORDER BY step_no), 3) AS step_conversion
FROM o
ORDER BY step_no
RightStarts with Visit, Sign up, Build a box…
Point at a bar to read it.
0200400600VisitSign upBuild a boxSubscribe

Tableau

COUNTD(IF [event_date] = [Step date] THEN [user_id] END)
// per step, then LOOKUP(..., -1)

SQL

WITH v AS (
  SELECT user_id, MIN(event_date) AS visit_date
  FROM input
  WHERE event = 'visit'
  GROUP BY user_id
  HAVING TRUE
), s AS (
  SELECT v.user_id, v.visit_date, MIN(e.event_date) AS signup_date
  FROM v JOIN input e ON e.user_id = v.user_id AND e.event = 'signup' AND e.event_date BETWEEN v.visit_date AND v.visit_date + 7
  GROUP BY v.user_id, v.visit_date
), b AS (
  SELECT s.user_id, s.visit_date, MIN(e.event_date) AS box_date
  FROM s JOIN input e ON e.user_id = s.user_id AND e.event = 'build_box' AND e.event_date BETWEEN s.signup_date AND s.visit_date + 7
  GROUP BY s.user_id, s.visit_date
), p AS (
  SELECT b.user_id, MIN(e.event_date) AS subscribe_date
  FROM b JOIN input e ON e.user_id = b.user_id AND e.event = 'subscribe' AND e.event_date BETWEEN b.box_date AND b.visit_date + 7
  GROUP BY b.user_id
), steps AS (
  SELECT 1 AS step_no, 'Visit' AS step, COUNT(*) AS users
  FROM v
  UNION ALL 
  SELECT 2, 'Sign up', COUNT(*)
  FROM s
  UNION ALL 
  SELECT 3, 'Build a box', COUNT(*)
  FROM b
  UNION ALL 
  SELECT 4, 'Subscribe', COUNT(*)
  FROM p
)
SELECT
  step,
  users : : INTEGER AS users,
  ROUND(users / LAG(users) OVER (ORDER BY step_no), 3) AS step_conversion
FROM steps
ORDER BY step_no

How long a window should the funnel allow?

Turn the knob. A short window counts only quick deciders; a long one lets a discount email two weeks later count as a conversion. At 7 days, 8.4% of visitors subscribe and the weakest step is Subscribe. Stretch it to 30 days and late subscribers lift the last step past signing up, which becomes the weakest again. Pick the window from how long your customers really take, then keep it fixed so this month compares with last month.

130
Point at the line to read it.
1Visit7172Sign up2943Build a box1614Subscribe60

Why do recent visitors drag the rate down?

Someone who visited yesterday hasn't had a week to subscribe yet, so a funnel over "everyone so far" understates conversion. Switch Visitors counted to keep only people who visited at least a full window before the end of the data. At 30 days that leaves 352 visitors, and 12.5% of them subscribe, against 10.9% of all visitors. In Tableau, filter [Visit date] <= DATEADD('day', -{{window}}, { FIXED : MAX([event_date]) }).

Visitors counted
Point at the line to read it.
1Visit7172Sign up2943Build a box1614Subscribe60

Common mistake

Steps sorted by name

Sorted alphabetically, Build a box comes first and Visit last, so each rate divides by the wrong step and the first step of the funnel gets no rate at all. Sort the steps in funnel order, by hand in Tableau or by a step number in SQL, before any LOOKUP or LAG.

WrongStarts with Build a box, Sign up, Subscribe…
Point at a bar to read it.
0200400600Build a boxSign upSubscribeVisit

Tableau

LOOKUP(..., -1)
// Step sorted A to Z

SQL

WITH v AS (
  SELECT user_id, MIN(event_date) AS visit_date
  FROM input
  WHERE event = 'visit'
  GROUP BY user_id
  HAVING TRUE
), s AS (
  SELECT v.user_id, v.visit_date, MIN(e.event_date) AS signup_date
  FROM v JOIN input e ON e.user_id = v.user_id AND e.event = 'signup' AND e.event_date BETWEEN v.visit_date AND v.visit_date + 7
  GROUP BY v.user_id, v.visit_date
), b AS (
  SELECT s.user_id, s.visit_date, MIN(e.event_date) AS box_date
  FROM s JOIN input e ON e.user_id = s.user_id AND e.event = 'build_box' AND e.event_date BETWEEN s.signup_date AND s.visit_date + 7
  GROUP BY s.user_id, s.visit_date
), p AS (
  SELECT b.user_id, MIN(e.event_date) AS subscribe_date
  FROM b JOIN input e ON e.user_id = b.user_id AND e.event = 'subscribe' AND e.event_date BETWEEN b.box_date AND b.visit_date + 7
  GROUP BY b.user_id
), steps AS (
  SELECT 1 AS step_no, 'Visit' AS step, COUNT(*) AS users
  FROM v
  UNION ALL 
  SELECT 2, 'Sign up', COUNT(*)
  FROM s
  UNION ALL 
  SELECT 3, 'Build a box', COUNT(*)
  FROM b
  UNION ALL 
  SELECT 4, 'Subscribe', COUNT(*)
  FROM p
)
SELECT
  step,
  users : : INTEGER AS users,
  ROUND(users / LAG(users) OVER (ORDER BY step), 3) AS step_conversion
FROM steps
ORDER BY step
RightStarts with Visit, Sign up, Build a box…
Point at a bar to read it.
0200400600VisitSign upBuild a boxSubscribe

Tableau

LOOKUP(..., -1)
// Step sorted by hand: Visit, Sign up, Build a box, Subscribe

SQL

WITH v AS (
  SELECT user_id, MIN(event_date) AS visit_date
  FROM input
  WHERE event = 'visit'
  GROUP BY user_id
  HAVING TRUE
), s AS (
  SELECT v.user_id, v.visit_date, MIN(e.event_date) AS signup_date
  FROM v JOIN input e ON e.user_id = v.user_id AND e.event = 'signup' AND e.event_date BETWEEN v.visit_date AND v.visit_date + 7
  GROUP BY v.user_id, v.visit_date
), b AS (
  SELECT s.user_id, s.visit_date, MIN(e.event_date) AS box_date
  FROM s JOIN input e ON e.user_id = s.user_id AND e.event = 'build_box' AND e.event_date BETWEEN s.signup_date AND s.visit_date + 7
  GROUP BY s.user_id, s.visit_date
), p AS (
  SELECT b.user_id, MIN(e.event_date) AS subscribe_date
  FROM b JOIN input e ON e.user_id = b.user_id AND e.event = 'subscribe' AND e.event_date BETWEEN b.box_date AND b.visit_date + 7
  GROUP BY b.user_id
), steps AS (
  SELECT 1 AS step_no, 'Visit' AS step, COUNT(*) AS users
  FROM v
  UNION ALL 
  SELECT 2, 'Sign up', COUNT(*)
  FROM s
  UNION ALL 
  SELECT 3, 'Build a box', COUNT(*)
  FROM b
  UNION ALL 
  SELECT 4, 'Subscribe', COUNT(*)
  FROM p
)
SELECT
  step,
  users : : INTEGER AS users,
  ROUND(users / LAG(users) OVER (ORDER BY step_no), 3) AS step_conversion
FROM steps
ORDER BY step_no

Run it on your own data

Any event log with a user id, a date (or timestamp) and an event name works. Drop your CSV, pick the columns and name your steps in order; the funnel recomputes in your browser, and nothing is uploaded.

Drop a CSV anywhere on this page

Up to 10,000 rows. The sample, events.csv, shows the shape.

Questions

Why does a later step have more users than an earlier one?

You're counting every user who fired each event. Returning customers, other entry points and blocked tracking put people at later steps without the earlier ones. Count only users who reached each step in order.

What conversion window should I use?

The time a real customer takes to decide. Look at the time from the first step to the last for users who converted, and pick a window that covers most of them. A week suits a meal kit; a B2B trial may need 30 days or more.

Should I use dates or timestamps?

Timestamps when you have them. With dates, two events on the same day count as in order whichever came first. With timestamps, compare times so a box built before signing up stays out of order.

How do I get the median time between two steps?

Compute both dates per user with the FIXED fields from this page, then take MEDIAN(DATEDIFF('day', [Visit date], [Signup date])), or MEDIAN(date_diff('day', visit_date, signup_date)) in SQL.

Sources