KPI · E-commerce

Time to second purchase: formula and example.

Time to second purchase is the number of days between a customer's first and second order, usually shown as the median across all customers who placed a second order. The second purchase is a key moment: a customer who buys twice is more likely to come back again than one who bought once. Time to second purchase tells you when best to contact a new customer again.

Book a demo All KPIs

Time to second purchase formula

Median of (date of second order − date of first order) in days

Worked example

Five new Acme Groothandel customers place their second order after 21, 34, 46, 70 and 112 days. The median is the middle value: 46 days. The mean is (21 + 34 + 46 + 70 + 112) ÷ 5 = 283 ÷ 5 = 56.6 days, pushed up by the one late buyer.

Fictitious figures for Acme Groothandel, a sample wholesaler.

How to read this KPI

A shorter time to second purchase means customers return sooner, which usually points to a good first experience or a product that runs out quickly. Compare it by product category and acquisition channel, and track it per cohort of new customers. Always report what share of customers places a second order at all, as the time says nothing about those who never return.

Pitfalls with time to second purchase

Using the mean instead of the median, so a few late buyers distort the picture.
Including recent new customers who have not yet had a chance to buy again.
Counting one order split into two deliveries as a second purchase.

SQL & DAX

Calculating time to second purchase in SQL and DAX

Two examples on a fictitious data warehouse for Acme Groothandel: a PostgreSQL query and a measure for a Power BI model. Adjust table and column names to your own model.

SQL · PostgreSQL

WITH ranked AS (
  SELECT customer_id, order_date,
         ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) AS n
  FROM orders
  WHERE status <> 'cancelled'
),
pairs AS (
  SELECT customer_id,
         MAX(order_date) FILTER (WHERE n = 2) - MAX(order_date) FILTER (WHERE n = 1) AS days
  FROM ranked
  WHERE n <= 2
  GROUP BY customer_id
)
SELECT COUNT(*) AS customers_with_second_order,
       percentile_cont(0.5) WITHIN GROUP (ORDER BY days) AS median_days
FROM pairs
WHERE days IS NOT NULL;

DAX · Power BI

Median Days to Second Purchase =
MEDIANX(
    FILTER(Customers, NOT ISBLANK(Customers[second_order_date])),
    DATEDIFF(Customers[first_order_date], Customers[second_order_date], DAY)
)

Share with Second Purchase =
DIVIDE(
    COUNTROWS(FILTER(Customers, NOT ISBLANK(Customers[second_order_date]))),
    COUNTROWS(FILTER(Customers, NOT ISBLANK(Customers[first_order_date])))
)

In ENABLE

Tracking this KPI in ENABLE

Plain-language questions

Ask for time to second purchase per month, customer or product in the SQL Explorer with AI. The AI writes the SQL, you see the query and run it yourself; the AI only receives metadata.

In your Power BI dashboard

Define the KPI as a measure in your Power BI model and show it in the portal, with "Last refreshed" next to it. Access profiles and optional row-level security decide who sees which figures.

Custom dashboards (pilot)

Add the KPI as a tile to a custom dashboard, compared with last year. The Dashboard Builder is a pilot and can be switched on per client.

Monthly targets

With the built-in Targets app you set a target for the KPI per month, so you see actuals and target side by side.

FAQ

Frequently asked questions

How do you calculate time to second purchase?

Work out, for each customer, the number of days between their first and second order, then take the median across all customers with a second order. The median works better than the mean because a few very late buyers pull the mean up sharply. Also report what percentage of customers places a second order.

Why is the second purchase so important?

A customer who buys a second time has shown that the first experience was good enough to return. In many online shops, the chance of a third purchase after that is clearly higher than the chance of a second. That is why many teams aim their follow-up emails specifically at the second order.

When should you send a follow-up email after the first purchase?

A practical starting point is just before the median time to second purchase, when many customers are close to their next order. Then test a few timings and compare what share of customers buys again within a fixed window. For consumables, you can also work from how long the product lasts.

Use AI on your own data. Securely, and without the headache.

In an online demo we show how ENABLE makes your data securely available and how you build on it: describing an app in the AI Builder, plain-language questions, dashboards and APIs. And how we set it up and manage it for you.

Book a demo info@enableyourdata.com