How to Find Revenue Leakage in NetSuite (with SuiteQL)

There is a reason revenue leakage survives in businesses with perfectly competent finance functions and good reporting. Every standard report aggregates, and leakage is not an average problem. Your gross margin on a product line can sit exactly on target while a quarter of the transactions inside it are unprofitable, because the good ones are carrying the bad ones and the report only shows you the sum.

So the method is always the same: go to the transaction grain, look at the shape of the distribution rather than its centre, and quantify the tail. What follows is three queries that do that, in ascending order of how uncomfortable the answer usually is.

Before you start: get the grain right

Two decisions determine whether this analysis is worth anything, and both need making before you run a single query.

First, decide what "price" means. The rate on the sales order line is not what the customer paid. Settlement discounts, retrospective rebates, freight absorbed rather than recharged and credit notes all sit between the order line and the cash. If those are material in your business, analysing order-line rate will systematically overstate your realised price — and the customers with the worst terms will look like your best ones. Start with order-line rate because it's available, but know which way the error points.

Second, decide what "cost" means. NetSuite's costestimate on a transaction line, standard cost on the item record, and actual landed cost are three different numbers and they routinely disagree by several points of margin. Pick one, state which, and use it consistently. The queries below use the transaction line's own cost estimate because it is the version captured at the moment of sale, which is the right basis for asking whether a specific decision was sound.

Query 1 — price dispersion by item

The single most revealing view in most distribution and manufacturing businesses: for each product, how wide is the spread of prices actually paid, and how much volume sits at the bottom of it?

-- Price dispersion by item over the last 12 months.
-- Compares each item's median realised price against its lower quartile.
WITH lines AS (
  SELECT
    tl.item                              AS item_id,
    BUILTIN.DF(tl.item)                AS item_name,
    t.entity                             AS customer_id,
    ABS(tl.quantity)                    AS qty,
    tl.rate                              AS unit_price
  FROM transaction t
  JOIN transactionline tl ON tl.transaction = t.id
  WHERE t.type     = 'SalesOrd'
    AND t.voided   = 'F'
    AND tl.mainline = 'F'
    AND tl.taxline  = 'F'
    AND tl.rate     > 0
    AND t.trandate >= ADD_MONTHS(CURRENT_DATE, -12)
)
SELECT
  item_name,
  COUNT(*)                                     AS line_count,
  COUNT(DISTINCT customer_id)                  AS customers,
  ROUND(MEDIAN(unit_price), 2)                 AS median_price,
  ROUND(PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY unit_price), 2) AS lower_quartile,
  ROUND(MIN(unit_price), 2)                    AS min_price,
  ROUND(MAX(unit_price), 2)                    AS max_price,
  ROUND(
    (MEDIAN(unit_price)
     - PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY unit_price))
    * SUM(qty) * 0.25, 2)                      AS indicative_uplift
FROM lines
GROUP BY item_name
HAVING COUNT(*) >= 20
   AND COUNT(DISTINCT customer_id) >= 5
ORDER BY indicative_uplift DESC

The HAVING thresholds matter. An item sold four times to two customers has a meaningless "spread". Twenty lines across five customers is a reasonable floor; raise it if you have the volume.

What indicative_uplift is doing. It estimates what you'd gain by moving the bottom quartile of transactions up to the median, assuming a quarter of volume sits there. It is deliberately conservative and it is an indication, not a forecast — it assumes no volume loss, which is never true. Treat it as a way of ranking which items are worth investigating, not as a number to put in a budget.

Reading it. Sort descending and look at the top 20 rows. For each, the question is whether the customers at the bottom of the range have a reason to be there — volume, contract, strategic account. Often three or four do and the rest are historical accident. That subset is your actionable list.

Query 2 — order lines below the margin floor

Dispersion tells you about price. This tells you about the decisions people are actually making, which is more actionable because it has a name attached to it.

-- Sales order lines whose realised gross margin falls below a target floor.
SELECT
  t.tranid                               AS order_ref,
  t.trandate,
  BUILTIN.DF(t.entity)                    AS customer,
  BUILTIN.DF(tl.item)                    AS item,
  ABS(tl.quantity)                        AS qty,
  tl.rate                                AS unit_price,
  tl.costestimate                        AS line_cost,
  ROUND(
    ((tl.rate * ABS(tl.quantity)) - tl.costestimate)
    / NULLIF(tl.rate * ABS(tl.quantity), 0) * 100, 1) AS gm_pct,
  ROUND((tl.rate * ABS(tl.quantity)) - tl.costestimate, 2) AS gross_profit
FROM transaction t
JOIN transactionline tl ON tl.transaction = t.id
WHERE t.type     = 'SalesOrd'
  AND t.voided   = 'F'
  AND tl.mainline = 'F'
  AND tl.taxline  = 'F'
  AND tl.rate     > 0
  AND tl.costestimate > 0
  AND t.trandate >= ADD_MONTHS(CURRENT_DATE, -12)
  AND ((tl.rate * ABS(tl.quantity)) - tl.costestimate)
      / NULLIF(tl.rate * ABS(tl.quantity), 0) < 0.20   -- your floor
ORDER BY gross_profit ASC

Set 0.20 to your actual target floor. Sorting ascending by gross_profit rather than by percentage puts the largest absolute losses first, which is where the money is — a −40% margin on a £30 line matters less than a +4% margin on a £90,000 one.

The pattern to look for is repetition. One bad line is a bad day. The same customer appearing thirty times, or the same item appearing across twelve customers, is a pricing rule that's wrong. Group the output by customer and by item and count — the counts tell you far more than the individual rows.

This is also the query that surfaces the discount-authority problem described in the five types of revenue leakage: if your approval rules are expressed as percentage-off-list rather than as a margin floor, this query is where that shows up as a systematic bias rather than an occasional lapse.

Query 3 — silent cost drift

The most insidious of the three, because nobody did anything at all. Your purchase cost rose, your sales price didn't, and the margin quietly compressed on every transaction since.

-- Items whose average purchase cost rose materially between the
-- prior 6 months and the most recent 6 months.
WITH purch AS (
  SELECT
    tl.item                                AS item_id,
    CASE WHEN t.trandate >= ADD_MONTHS(CURRENT_DATE, -6)
         THEN 'recent' ELSE 'prior' END      AS period,
    tl.rate                                AS cost_rate
  FROM transaction t
  JOIN transactionline tl ON tl.transaction = t.id
  WHERE t.type     = 'VendBill'
    AND t.voided   = 'F'
    AND tl.mainline = 'F'
    AND tl.item    IS NOT NULL
    AND tl.rate    > 0
    AND t.trandate >= ADD_MONTHS(CURRENT_DATE, -12)
),
sold AS (
  SELECT
    tl.item                                AS item_id,
    AVG(tl.rate)                           AS avg_sell,
    SUM(ABS(tl.quantity))                   AS qty_sold
  FROM transaction t
  JOIN transactionline tl ON tl.transaction = t.id
  WHERE t.type = 'SalesOrd' AND t.voided = 'F'
    AND tl.mainline = 'F' AND tl.taxline = 'F' AND tl.rate > 0
    AND t.trandate >= ADD_MONTHS(CURRENT_DATE, -6)
  GROUP BY tl.item
)
SELECT
  BUILTIN.DF(p.item_id)                    AS item,
  ROUND(AVG(CASE WHEN p.period = 'prior'  THEN p.cost_rate END), 2) AS cost_prior,
  ROUND(AVG(CASE WHEN p.period = 'recent' THEN p.cost_rate END), 2) AS cost_recent,
  ROUND(s.avg_sell, 2)                     AS avg_sell_price,
  s.qty_sold,
  ROUND((AVG(CASE WHEN p.period = 'recent' THEN p.cost_rate END)
       - AVG(CASE WHEN p.period = 'prior'  THEN p.cost_rate END))
       * s.qty_sold, 2)                   AS margin_lost
FROM purch p
JOIN sold s ON s.item_id = p.item_id
GROUP BY p.item_id, s.avg_sell, s.qty_sold
HAVING AVG(CASE WHEN p.period = 'recent' THEN p.cost_rate END)
     > AVG(CASE WHEN p.period = 'prior'  THEN p.cost_rate END) * 1.03
ORDER BY margin_lost DESC

The 1.03 is a 3% materiality threshold on the cost increase. margin_lost is the annualised cost of not having passed that increase through.

This query tends to produce the shortest list and the least argument. A supplier put their price up, it's in your own purchase records, and your sell price is unchanged. There's no interpretation required — only a decision about whether to pass it on.

Turning findings into a defensible number

The temptation at this point is to add up every indicative_uplift and present the total. Don't. It will be a large number, it will not survive contact with a sceptical CFO, and losing that argument once will close the topic for a year.

A number that holds up is built like this:

1. Take the top 20 findings by value, not all of them. The tail is noise and defending it costs you the head.
2. Manually verify every one. Open the transactions. Some will have a perfectly good explanation and should be struck out — expect to lose 30–50% at this stage, and say so upfront.
3. Apply a realisation factor. You will not recover 100% of theoretical uplift, because some customers will refuse and some volume will go. Between 30% and 60% of the verified figure is a defensible range for a first pass.
4. Present the method, not just the total. "Here are 20 transactions, here is why each is wrong, here is the money" beats a single large number every time.

Four ways this analysis goes wrong

Mixing units of measure. If the same item is sold in eaches and in boxes of twelve, comparing unit price across those lines produces spectacular and entirely fictional dispersion. Check the units on your top findings before you present anything.

Ignoring the reason for the discount. The customer at the bottom of the price range might be there because they take full pallets, pay in seven days, and collect their own goods. Price dispersion is a question, not a verdict.

Using the wrong cost. If costestimate isn't populated reliably in your account — and in many accounts it isn't — the margin query will produce confident nonsense. Spot-check ten lines against what you know the item actually costs before trusting the other ten thousand.

Analysing orders rather than invoices. These queries use SalesOrd because it captures the pricing decision at the point it was made. If you want realised revenue including credits and returns, run the same logic against CustInvc and net off CustCred. The two answers differ, and knowing which question you're asking matters.

What to do with the answer

Running these three queries once gives you a snapshot: here is roughly where the margin is going. That is genuinely useful and it's usually enough to justify doing something.

What it doesn't give you is control. Prices drift continuously, new customers get quoted every week, and suppliers put costs up on their own schedule. A snapshot ages badly — within a quarter it describes a business that no longer exists.

The step that changes outcomes is turning the check into a standing one: same logic, run weekly, ranked by value, with findings routed to whoever can act on them. That's a different exercise from an analysis, and it's worth being honest with yourself about which one you're committing to before you start.