Level 2 · Do they come back?

Two loyalty numbers

Repeat rate is the share of customers who bought at least twice. Purchase frequency (orders per customer) is how many orders the average customer placed in the period. Together they say whether customers come back and how often.

Both are customer metrics, so the first step is always the same: collapse orders to one row per customer. Everything after that is counting rows.

WITH per_customer AS (
  SELECT customer_id, COUNT(*) AS n_orders
  FROM orders
  WHERE status IN ('completed', 'refunded')
  GROUP BY customer_id
)

The denominator decides the number

Who is “a customer”? This level uses people with at least one booked order: 1,740 of them. The customers table has 2,018 rows, but that includes sign-ups who never paid and 18 duplicate records. Dividing by 2,018 would lower the repeat rate from 81.21% to about 70% without anyone changing their behaviour. Write the denominator into the metric’s name or footnote.

The period matters as much. Over three years most customers have had time to come back; over a single month almost nobody has. A repeat rate is only comparable with another repeat rate measured over the same length of time.

Mean and median frequency

The mean is 6.41 orders per customer, but the median customer has 3. A few heavy buyers order constantly: 19 customers with 50 or more orders each placed 14.11% of all booked orders, one of them 160. Business customers have a median of 6 orders against 3 for consumers. If the loyalty programme is aimed at consumers, 6.41 describes almost none of them.

Common mistakes

• Counting orders instead of customers in the numerator, for example COUNT(*) FILTER (WHERE ...) directly on orders. That gives repeat orders, a different metric.
• Including cancelled orders. A customer with one paid order and one cancelled attempt is not a repeat buyer.
• Integer division. SUM(n_orders) / COUNT(*) on integers can round the answer away; multiply by 1.0 first.

Interview note

When asked to “measure customer loyalty”, name the repeat rate and the frequency, state the window (here 2023 to 2025) and the denominator (customers with a paid order). Mentioning that the mean frequency is skewed by a few large accounts shows you looked at the distribution, not only the average.