How to calculate Repeat Purchase Rate

Formula

Repeat purchase rate = customers with 2+ orders ÷ customers with 1+ orders × 100

SQL query

This example assumes: orders(id, customer_id, ordered_at, status). It is written in PostgreSQL syntax; date functions differ slightly in MySQL, BigQuery and SQLite, and your column names will differ from these.

WITH per_customer AS (
  SELECT customer_id, COUNT(*) AS orders
  FROM orders
  WHERE status = 'paid' AND customer_id IS NOT NULL
  GROUP BY customer_id
)
SELECT COUNT(*) FILTER (WHERE orders >= 2) * 100.0 / NULLIF(COUNT(*), 0) AS repeat_pct
FROM per_customer;

Common mistakes that make Repeat Purchase Rate wrong

• Guest checkouts with no customer ID are dropped; the rate then describes registered customers only.
• Newer customers have had less time to reorder; compare a fixed window, such as repeat within 90 days.
• Two items bought in one order are one purchase, not two.

Ask for Repeat Purchase Rate in plain English

Instead of writing the query yourself, you can type the question. SchemaChat turns it into SQL against your connected database or uploaded file, runs it, and shows the SQL and the figures it used so you can check the answer before you share it. It does not replace your judgement about the definition: check that the query matches the formula your business uses.

• What share of customers have ordered more than once?
• What is the repeat rate within 90 days of the first order?