01
About this report
Every active top-level customer whose last sale date is more than 180 days ago. Each row gives the sales rep, first and last sale dates, days since the last sale, and net sales over the last two years. Net sales are invoices and cash sales less credit memos, before tax, in base currency.
Sort by Sales, last 2 years to call the largest accounts first. The customer name opens the customer record.
02
Sample preview
Fictional figures in the shape you will see. Yours come from your account.
| Customer | Sales rep | First sale | Last sale | Days since last sale | Sales, last 2 years |
|---|---|---|---|---|---|
| Coastal Freightways | Marco Ruiz | 2022-03-14 | 2026-03-18 | 190 | 97,310 |
| Pinecrest Veterinary | Dana Whitfield | 2023-07-02 | 2026-02-27 | 209 | 41,280 |
| Lumen Architects | 2021-11-20 | 2025-10-05 | 354 | 12,900 | |
| Old Mill Bakery | Marco Ruiz | 2024-05-09 | 2025-06-30 | 451 | 3,150 |
03
The SuiteQL
This is the query each report runs. Read it, copy it, or change it after import.
SELECT c.id AS customer__id,
NVL(c.altname, c.entityid) AS customer,
BUILTIN.DF(c.salesrep) AS sales_rep,
TO_CHAR(c.firstsaledate, 'YYYY-MM-DD') AS first_sale,
TO_CHAR(c.lastsaledate, 'YYYY-MM-DD') AS last_sale,
TRUNC(SYSDATE) - TRUNC(c.lastsaledate) AS days_since,
NVL(s.sales, 0) AS sales_24
FROM customer c
LEFT JOIN (SELECT e.toplevelparent AS customer_id, SUM(-tl.foreignamount * t.exchangerate) AS sales
FROM transaction t
JOIN transactionline tl ON tl.transaction = t.id
JOIN entity e ON e.id = t.entity
WHERE t.type IN ('CustInvc', 'CashSale', 'CustCred')
AND t.posting = 'T'
AND tl.mainline = 'F'
AND tl.taxline = 'F'
AND NVL(tl.iscogs, 'F') = 'F'
AND tl.itemtype NOT IN ('TaxGroup', 'TaxItem', 'Subtotal', 'Description', 'EndGroup')
AND t.trandate > TRUNC(SYSDATE) - 730
GROUP BY e.toplevelparent) s ON s.customer_id = c.id
WHERE c.parent IS NULL
AND c.isinactive = 'F'
AND c.lastsaledate < TRUNC(SYSDATE) - 180
ORDER BY sales_24 DESC