Funnels, Cohorts and Retention

Two requests sit at the top of the queue. Huda: “Our paid search budget is up for review. Which traffic sources actually turn visits into orders, and where do people drop off?” Rania: “We acquired a lot of customers in Black Friday months. Do they come back as often as everyone else?” The first is a funnel, the second a cohort retention analysis. Both are built from things you already know: conditional aggregation, dates and CTEs.

A funnel from session events

Each row of web_sessions is one visit, with flags for the steps it reached: added_to_cart and purchased (0 or 1). Summing a 0/1 flag counts the sessions that reached the step, so a funnel is one GROUP BY:

SELECT
  source,
  COUNT(*)            AS sessions,
  SUM(added_to_cart)  AS carts,
  SUM(purchased)      AS purchases,
  ROUND(100.0 * SUM(added_to_cart) / COUNT(*), 1)                  AS cart_rate,
  ROUND(100.0 * SUM(purchased) / NULLIF(SUM(added_to_cart), 0), 1) AS checkout_rate,
  ROUND(100.0 * SUM(purchased) / COUNT(*), 1)                      AS conversion
FROM web_sessions
WHERE started_at >= '2025-01-01'
GROUP BY source
ORDER BY conversion DESC;

Email converts best overall, 10.9% of sessions, and referral worst at 7.1%. The step rates tell the more useful story. Referral visitors rarely add to cart (13.3%), but those who do check out more often than anyone (53.6%). Email is the reverse. Those are different problems with different fixes: referral needs better landing pages, email needs a smoother checkout.

That’s why a funnel reports step rates, each step divided by the previous one, not only end-to-end conversion. Make each denominator explicit in the column name; “conversion rate” without a denominator is the most argued-about number in any marketing meeting. NULLIF guards the division for a source with no carts.

Swap source for device and a sharper finding appears: desktop sessions that reach the cart check out 56% of the time, mobile sessions only 37%. Mobile brings the most traffic, so that gap is worth more than any change to the ad budget.

Cohorts: grouping customers by when they started

A cohort is a group of customers who share a starting period. Here, the month of their first non-cancelled order. Retention then asks: of the customers who started in month M, what share ordered again 1, 2, 3 months later?

Three steps, three CTEs. First, give every order a month index, year * 12 + month, so that month differences are simple subtraction even across a year boundary. Second, find each customer’s first month. Third, list each customer’s active months relative to their cohort:

WITH orders_ok AS (
  SELECT customer_id, order_date,
         CAST(strftime('%Y', order_date) AS INTEGER) * 12
           + CAST(strftime('%m', order_date) AS INTEGER) AS month_index
  FROM orders
  WHERE status <> 'cancelled'
),
firsts AS (
  -- grain: one row per customer
  SELECT customer_id,
         MIN(month_index) AS cohort_index,
         strftime('%Y-%m', MIN(order_date)) AS cohort
  FROM orders_ok
  GROUP BY customer_id
),
activity AS (
  -- grain: one row per customer per active month
  SELECT DISTINCT f.cohort, f.cohort_index, o.customer_id,
         o.month_index - f.cohort_index AS month_number
  FROM orders_ok AS o
  JOIN firsts AS f ON f.customer_id = o.customer_id
)
SELECT
  cohort,
  COUNT(*) FILTER (WHERE month_number = 0) AS customers,
  ROUND(100.0 * COUNT(*) FILTER (WHERE month_number = 1)
        / COUNT(*) FILTER (WHERE month_number = 0), 1) AS m1_pct,
  CASE WHEN MAX(cohort_index) + 2 <= 2025 * 12 + 12 THEN
    ROUND(100.0 * COUNT(*) FILTER (WHERE month_number = 2)
          / COUNT(*) FILTER (WHERE month_number = 0), 1)
  END AS m2_pct
FROM activity
WHERE cohort >= '2025-07'
GROUP BY cohort
ORDER BY cohort;

The final SELECT pivots month numbers into columns with FILTER. The DISTINCT in activity is essential: retention counts people, and a customer who orders three times in a month is one active customer.

Unknown is not zero

Look at the December 2025 row in the query’s output: m1_pct is 0.0. That isn’t churn. Month 1 for December’s cohort is January 2026, and the data ends on 31 December. The m2_pct column shows the fix: a CASE expression checks whether the cohort has lived through that month (cohort_index + 2 is still within December 2025) and returns NULL otherwise. A retention chart with honest blanks is a triangle; one with zeros shows a fake collapse in every recent cohort.

Back to Rania’s question. Change the filter to 2024 and the November 2024 cohort, Cartwheel’s first Black Friday crowd and the largest of that year at 35 customers, retains only 14.3% in month 1, below most 2024 cohorts. Deep-discount customers tend to come for the deal. That’s an answer she can use when planning the next promotion. The exercise asks a related question: what share of each cohort comes back at all within three months?

This closes the analytical toolkit. The final section is about trust: making sure the data and your queries deserve it.