The Funnel in Numbers

From visit to loyal customer

A funnel follows people through the steps that matter and counts how many are left after each step. For an online shop the basic funnel is short: a visit, a first order, and then another order. Each step can be measured per marketing source, and each step can change the ranking of the channels.

Sessions to orders

In the first quarter of 2026 Alpenkorb had 3,677 sessions, and 678 of them ended in an order. The conversion rate is the number of ordering sessions divided by all sessions, and it differs a lot by source.

SELECT source,
       COUNT(*) AS sessions,
       SUM(CASE WHEN ordered THEN 1 ELSE 0 END) AS orders,
       ROUND(100.0 * SUM(CASE WHEN ordered THEN 1 ELSE 0 END) / COUNT(*), 1) AS conversion_pct
FROM sessions
WHERE session_date >= '2026-01-01'
  AND session_date < '2026-04-01'
GROUP BY source
ORDER BY conversion_pct DESC;

Direct visits convert at 36.4 % and email at 32.6 %, paid search at 16.1 % and social at 5.1 %. Writing 100.0 keeps the division decimal in every database. DuckDB divides with decimals anyway, but some databases, PostgreSQL among them, turn a division of two whole numbers into a whole number.

Direct and email convert well because most of their visitors are existing customers coming back. Comparing them with social, where most visitors are strangers, compares different populations. For acquisition the fairer count is first orders: of 1,028 social sessions, 14 ended in a first order; of 670 paid search sessions, 50 did.

Put per 1,000 sessions, the difference is easier to feel: in that quarter 1,000 social sessions produced about 14 new customers, while 1,000 paid search sessions produced about 75. A funnel does not say why. Perhaps social reaches people who are not ready to buy groceries online, perhaps the ads promise something the shop does not offer. It tells Lena where to look.

Do they come back?

A new customer is only worth the acquisition cost if they order again. To measure that fairly, Lena looks at customers who signed up in 2025, so that everyone had at least three months to return before the end of March 2026. Of those 603 customers, 359 placed at least two delivered orders. The lab splits this by acquisition source.

The lab uses WITH, which gives a query a name so that the next query can read it like a table. The per_customer step counts delivered orders for each customer; a LEFT JOIN keeps customers whose only order was cancelled, with a count of 0. Your part is the final summary.

• Time to return: customers who signed up in March 2026 had days, not months, to order again. Mixing them in makes recently active channels look worse.
• Last-click credit: acquisition_source records only the visit of the first order. A customer who saw a social ad and later searched for Alpenkorb counts as paid search.
• Small groups: 58 customers came through affiliates in 2025. With groups this small, a difference of a few percentage points can be chance.
• Orders, not delivered orders: ordered in sessions is true for every order, including the few that were later cancelled or returned. For a revenue question, join to orders and filter on status.