When did each customer first and last order?

The data

In a spreadsheet

First order =MINIFS(Orders[Date], Orders[Customer], A2)
Last order  =MAXIFS(Orders[Date], Orders[Customer], A2)
Orders      =COUNTIF(Orders[Customer], A2)
Days since  =DATE(2026,10,7) - [@[Last order]]

In SQL

SELECT customer, MIN(order_date) AS first_order, MAX(order_date) AS last_order, COUNT(*) AS orders
FROM orders
GROUP BY customer
ORDER BY first_order;

On 7 October, ben@hart.co.uk hasn't ordered for 239 days and kim@kiln.io for 99 days — candidates for a win-back email.

Across files and stores

• Stack the files first (see combining sheets into one); MINIFS on separate files would give each file's first order, not the customer's.
• Make sure dates are real dates, not text — MINIFS ignores text and returns 0.
• Use one customer key across stores (email) so a customer's Web and Amazon orders combine.

Ask it in Analistable: “Which customers haven't ordered in 90 days, with their first and last order dates?”

Recency segments

Segment =IFS([@[Days since]] <= 30, "Active",
             [@[Days since]] <= 90, "Cooling",
             [@[Days since]] <= 180, "At risk",
             TRUE, "Lapsed")

With the example's dates on 7 October, eva@nordic.dk (25 days) is Active, ana@lune.fr (47 days) is Cooling, kim@kiln.io (99 days) is At risk and ben@hart.co.uk (239 days) is Lapsed. Adjust the thresholds to your normal buying cycle.

Without MINIFS: pivot tables and older Excel

MINIFS and MAXIFS need Excel 2019 or later (or Microsoft 365, including Excel for Mac). In older versions, a pivot table does the same job: put Customer in Rows, then drag Date into Values twice, set the first to Min and the second to Max (Value Field Settings), and format both as dates. Add Order to Values as Count for the number of orders.

In Google Sheets, MINIFS and MAXIFS work as in Excel, or use one formula for the whole table: =QUERY(Orders!A2:C, "select A, min(C), max(C), count(B) where A is not null group by A", 0) with customer, order and date in columns A–C.

A second measure: average gap between orders

Avg gap (days) =IF([@Orders] < 2, "", ([@[Last order]] - [@[First order]]) / ([@Orders] - 1))

Comparing days since the last order with a customer's own average gap is sharper than one threshold for everyone: ana@lune.fr at 47 days is well within her usual 109-day rhythm, so “Cooling” may be premature for her.

Troubleshooting

Refresh monthly by appending the new export and recalculating; use a fixed report date in a cell instead of TODAY() if you need the numbers to stay reproducible. Related: customers who stopped ordering.

Frequently asked questions

How do I find the first order date per customer?

Use =MINIFS(date_column, customer_column, customer) on the stacked orders, or MIN(order_date) with GROUP BY in SQL.

Why does MINIFS return 0?

No matching rows, or the dates are stored as text. Convert them to real dates.

How do I find customers who haven't ordered recently?

Calculate days since the last order (today − MAXIFS) and filter those above your threshold.

Can I get the amount of the last order too?

Yes: look up the amount where customer and date both match, for example with XLOOKUP on a combined customer-and-date key, or a window function in SQL.

What if a customer ordered twice on the same day?

The first and last dates are the same either way; count orders separately so both are included.

How do I handle orders from different stores?

Stack them with a Store column and one customer key, then run the same MINIFS and MAXIFS.

Related guides

• Are any invoice numbers missing from the sequence?
• Which keywords do both sites rank for?
• Which suppliers deliver late, and by how much?
• Which leads never became customers?
• Why don't these two reports add up to the same total?