Skip to content
Tutorials Center
My learning

Topics

Lesson 112 of 125 ยท Projects

Project: E-commerce Funnel Analytics

Build an e-commerce analytics report in SQL. Measure a customer funnel, split new from returning revenue, build a retention cohort table and test revenue concentration.

Every online business asks the same few questions. How many visitors become customers? Do those customers come back? Where does the money come from, and how much of it depends on a handful of buyers? Answering them is the daily work of a data analyst, and SQL is the main tool.

In this project you'll work with the shop sample database and build four reports: a funnel (how far customers get), a new versus returning revenue split, a retention cohort table, and a revenue concentration check. Along the way you'll run into a data-quality problem that changes how one of the reports must be built, which is exactly how real analysis goes.

This project assumes you know joins, CTEs, window functions and CASE. It is a companion to Project: Sales Analysis, which covers revenue definitions and monthly trends; here we focus on customer behavior. The tables are customers, orders (with a status) and order_items (with the price paid in unit_price).

Ground rules

Agree on definitions first, because every number depends on them:

  • A kept order is an order whose status is not cancelled. Pending, shipped and delivered orders all count.
  • Revenue is the sum of quantity * unit_price over the lines of kept orders. Shipping is excluded.
  • A customer is active in a month if they have at least one kept order in it.

The queries below reuse one building block, a CTE with one row per kept order and its total. Building that row first, then aggregating, avoids the classic mistake of averaging order lines instead of orders (see Grouping in Practice).

Stage 1: the customer funnel

A funnel counts how many customers reach each successive step. Our steps: signed up, placed an order, kept an order (did not cancel everything), kept two or more, kept three or more. Each step is a subset of the one before it, so the numbers can only go down.

flowchart LR
  A["Signed up"] --> B["Placed an order"]
  B --> C["Kept an order"]
  C --> D["Kept 2 or more"]
  D --> E["Kept 3 or more"]

A funnel is a sequence of nested groups; the interesting numbers are the drop-offs between steps.

Each step is a small subquery, glued together with UNION ALL into one table, and the window functions turn raw counts into percentages. FIRST_VALUE gives the top of the funnel, and LAG gives the previous step.

SQL The funnel with drop-off percentages ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Of 40 sign-ups, 34 (85%) placed at least one order, 33 kept one, 29 kept two or more and 21 kept three or more. The most useful column is pct_of_previous, because it shows where customers are lost at each individual step. Six sign-ups never ordered at all, so onboarding emails or a first-order discount might pay off. Almost nobody who kept an order stopped after one (87.9% went on to a second), and 72.4% of repeat buyers went on to a third, the weakest step after the first order. That tells a business where to look: getting people to their first purchase, then to their third.

Stage 2: cohorts by sign-up year

A cohort is a group of customers who share a starting point, usually the month or year they joined. Comparing cohorts shows whether newer customers behave differently from older ones. Here is revenue per sign-up year:

SQL Revenue by sign-up year ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The LEFT JOIN matters: it keeps customers who never bought, so revenue_per_customer divides by everyone in the cohort and not only the buyers. Customers who joined in 2023 brought in 831.34 each, the 2024 cohort only 496.91 (18 sign-ups, but just 12 who bought), and 2025 recovered to 766.07. The three revenues add up to 26,515.90, the same total as the Sales Analysis project, which is a good reconciliation check.

Stage 3: can you trust the sign-up date?

Cohorts by sign-up date assume a customer cannot buy before signing up. Test that assumption before building anything on top of it:

SQL Orders placed before the customer signed up ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

22 orders from 8 customers are dated before their sign-up date. In a real company this would trigger a conversation: perhaps signed_up records the moment a guest checkout was converted into an account, or perhaps it is a bug. Until someone answers, treat signed_up as unreliable for behavior analysis.

The robust alternative is to define the cohort by something you can trust: the month of each customer's first kept order. That is called an acquisition cohort, and it needs no sign-up date at all.

When a data problem affects one column, don't fix it silently and don't ignore it. Switch to a definition that avoids the column, and write down why. The next analyst will thank you.

Stage 4: new versus returning revenue

ROW_NUMBER numbers each customer's kept orders from oldest to newest. Order number 1 is a new-customer order; everything after it comes from a returning customer. A CASE inside SUM splits the revenue, and a little date arithmetic groups months into quarters:

SQL New versus returning revenue per quarter ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The shape is typical for a business with a fixed customer base: returning revenue grows from 32.3% in the first quarter to 100% in the fourth, while new-customer revenue dries up (no customer made a first order in Q4). That is healthy loyalty, but also a warning: the store is living off existing customers and acquiring few new ones. The window-function recipe, ROW_NUMBER() OVER (PARTITION BY customer ORDER BY date), is the standard way to find "first" and "nth" events.

The expression (month + 2) / 3 uses integer division to map months 1-3 to quarter 1, 4-6 to quarter 2, and so on. It is a pattern worth memorizing.

Stage 5: a retention cohort table

Retention asks: of the customers who made their first purchase in a given month, how many bought again one month later, two months later, and so on? The result is the famous triangle-shaped cohort table.

The plan, one CTE per idea:

  1. kept: each kept order with a month number, year * 12 + month - 1, so subtracting two month numbers gives the gap in months even across a year boundary.
  2. first_month: each customer's earliest month number (their acquisition cohort).
  3. activity: for every month a customer was active, how many months after their first month it was. DISTINCT counts a customer once per month, however many orders they placed.
  4. cohorts: the size of each cohort.
  5. The final query counts distinct active customers at gap 1, 2 and 3 with SUM(months_since = 1): in SQLite a comparison is 1 or 0, so summing it counts rows. In PostgreSQL write COUNT(*) FILTER (WHERE months_since = 1) or SUM(CASE WHEN months_since = 1 THEN 1 ELSE 0 END), which works everywhere.
flowchart TD
  A["orders (kept only)"] --> B["kept: one row per order<br/>with month_no"]
  B --> C["first_month: MIN(month_no)<br/>per customer"]
  B --> D["activity: months_since =<br/>month_no - first_no"]
  C --> D
  C --> E["cohorts: cohort size"]
  D --> F["Cohort table:<br/>count active per months_since"]
  E --> F

Retention analysis is a pipeline: number the months, find each customer's first one, measure the gap, then count per cohort.

SQL Monthly retention cohort table ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Read it row by row. The January 2025 cohort has 8 customers who bought for the first time that month; 1 of them bought again in February, 2 in March and none in April. The February cohort (6 customers) is the strongest early on: 3 came back the very next month. Cohorts after May are tiny, one or two people, so their percentages are meaningless; always look at the cohort size before reading a retention number.

Two cautions apply to every retention table. First, the most recent cohorts have had less time to come back, so their later columns are not "zero retention", they are not yet known. Production queries return NULL for those cells. Second, a customer who skips a month and returns later is counted as retained only in the months they were active, which is an unbounded-return definition; stricter variants require activity in every month.

Stage 6: how concentrated is the revenue?

If three customers produce half your revenue, losing one of them is a crisis. A cumulative share shows this at a glance. Rank customers by revenue and use a running SUM over the ranking:

SQL Top customers and cumulative revenue share ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The top customer is responsible for 6.5% of revenue, the top five for 29.4%, and the top ten of 33 buyers (about 30% of them) for 50.6%. That is a moderately concentrated business, not a dangerous one. A shop where the top customer exceeded 30% would need a different strategy. The ORDER BY ..., customer_id tie-breaker makes the ranking deterministic, which matters when two customers spend exactly the same.

Extending the project

Try these on your own; each one reuses a building block from above:

  • Turn the retention counts into percentages of cohort size, and show NULL for months the data doesn't cover yet.
  • Find the average number of days between a customer's first and second kept order.
  • Compute a simple customer lifetime value: total revenue per customer, split by the number of orders.
  • Segment customers by country and compare funnels (a GROUP BY over the per-customer CTE).
  • Find "lapsed" customers: bought in the first half of the year, nothing since July (an NOT EXISTS query).

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. In a funnel table, what does the pct_of_previous column tell you that pct_of_signups does not?

    pct_of_signups measures everything against the top of the funnel, so it only shows cumulative loss. pct_of_previous compares each step with the one before it, which pinpoints the step where customers drop off.
  2. 2. Why does the project build retention cohorts from each customer's first order instead of from signed_up?

    The data check found 22 orders from 8 customers dated before their sign-up date. A cohort defined from the first kept order does not depend on the suspect column.
  3. 3. What does ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY ordered_at) = 1 identify?

    The window restarts for each customer and numbers their orders from oldest to newest, so the row numbered 1 is their first order. Everything numbered above 1 is a repeat purchase.
  4. 4. Why should the most recent cohorts in a retention table be read with care?

    Retention for month 3 cannot be measured for a customer whose first order was last month. Production reports show NULL for such cells rather than zero.

Summary

  • Define terms (kept order, revenue, active customer) before writing any query, and reconcile your totals against another report.
  • A funnel is a set of nested counts; pct_of_previous shows where customers drop off.
  • A cohort groups customers by a shared starting point; check that the starting-point column is trustworthy before using it.
  • ROW_NUMBER() OVER (PARTITION BY ...) finds first and nth events; splitting revenue into new and returning is a CASE inside SUM.
  • A retention table needs month numbers, each customer's first month, the gap in months and a count per cohort. Mind small cohorts and cohorts that haven't had time yet.
  • Cumulative shares over a ranked list show how dependent a business is on a few customers.

Next: Project: Inventory and Orders Database, where you design a schema from scratch and protect it with constraints and transactions.