Dormant customers — no purchase in X days
Active customers who've gone quiet — with days since their last order plus lifetime orders and spend, for a prioritized win-back list.
What people ask
- Which customers are dormant — haven't purchased in X days?”
- Customers who haven't ordered in the last 90 days”
- Inactive customers based on last purchase date”
The query
SELECT c.name AS customer_name, c.number AS customer_number, MAX(so.dateissued) AS last_order_date, DATEDIFF(CURRENT_DATE, MAX(so.dateissued)) AS days_dormant, COUNT(so.id) AS total_orders, ROUND(SUM(so.totalprice),2) AS total_spent FROM customer c LEFT JOIN so ON so.customerid = c.id AND so.statusid IN (20,25,60,70) WHERE c.activeflag = 1 GROUP BY c.id, c.name, c.number HAVING MAX(so.dateissued) IS NOT NULL AND DATEDIFF(CURRENT_DATE, MAX(so.dateissued)) >= 90 ORDER BY days_dormant DESC;
Preview · demo data
| Customer | Last order | Days dormant | Orders | Total spent |
|---|---|---|---|---|
| Acme Bikes | 2025-12-02 | 200 | 47 | $128,400.00 |
| Globex Cycles | 2026-01-15 | 156 | 12 | $34,210.00 |
| Initech Sports | 2026-02-20 | 120 | 8 | $19,880.00 |
Notes & gotchas
- The 90-day threshold is a parameter in the app ($NUM{Days_Dormant|90}) — change it without editing SQL.
- statusid IN (20,25,60,70) counts issued/fulfilled orders only.
- Lifetime orders + spend are included so you can prioritize the highest-value lapsed accounts.
- Drops never-ordered customers via HAVING MAX(dateissued) IS NOT NULL — remove it to include them.
I'll take active customers, find each one's most recent issued order, and keep those past your threshold. I'll also pull lifetime orders and spend so you can prioritize the valuable lapsed accounts. I've made the 90 days a parameter so you can adjust it.
SELECT c.name AS customer_name, c.number AS customer_number, MAX(so.dateissued) AS last_order_date, DATEDIFF(CURRENT_DATE, MAX(so.dateissued)) AS days_dormant, COUNT(so.id) AS total_orders, ROUND(SUM(so.totalprice),2) AS total_spent FROM customer c LEFT JOIN so ON so.customerid = c.id AND so.statusid IN (20,25,60,70) WHERE c.activeflag = 1 GROUP BY c.id, c.name, c.number HAVING MAX(so.dateissued) IS NOT NULL AND DATEDIFF(CURRENT_DATE, MAX(so.dateissued)) >= 90 ORDER BY days_dormant DESC;
Here's the win-back list on demo data, most-dormant first. Bump Days_Dormant to 180 for a stricter cut, or I can include never-ordered customers too.
| Customer | Last order | Days dormant | Orders | Total spent |
|---|---|---|---|---|
| Acme Bikes | 2025-12-02 | 200 | 47 | $128,400.00 |
| Globex Cycles | 2026-01-15 | 156 | 12 | $34,210.00 |
| Initech Sports | 2026-02-20 | 120 | 8 | $19,880.00 |
Want one like this for your Fishbowl?
Send a short brief — an ILC expert builds and tests it, and you get the file ready to import. The quote is free.