How to run an n-gram analysis on your Amazon search term report

Last updated: September 2, 2026

Verified as of 2026-09-02.

Reviewing search terms one at a time is how most people work a search term report, and it is why the same money keeps leaking. You sort by cost, negate the handful of terms at the top with no sales, and close the file. The terms that actually cost you are not at the top. They are three hundred rows down, one or two clicks each, and they all share a word.

An n-gram analysis finds that word. Instead of asking "did this search term convert", it asks "across every search term containing the word ceramic, what did I spend and what did I get back". One row of the answer can represent fifty search terms and a couple of hundred dollars that no per-term review would ever have flagged, because no individual term was big enough to notice.

This page is the whole procedure: what to export, real spreadsheet formulas that work, the thresholds to apply and where each one comes from, and the points where the spreadsheet stops being viable.

1. What n-gram analysis actually means

Three words get used interchangeably and they are not the same thing.

• A keyword is what you bid on.
• A search term is what the shopper typed. On broad and phrase match these differ, which is the entire reason the search term report exists.
• An n-gram is a single word or a short run of consecutive words extracted from a search term. In ceramic coffee mug set, the 1-grams are ceramic, coffee, mug, set; the 2-grams are ceramic coffee, coffee mug, mug set; the 3-grams are ceramic coffee mug and coffee mug set.

The reason to bother is arithmetic. Say you sell stainless steel travel mugs. Fifty search terms come in containing the word ceramic — ceramic mug, ceramic coffee cup for office, large ceramic tumbler, and forty-seven more. Each got one or two clicks, each sits below any sensible threshold, and each gets skipped every week. Together they are 80 clicks and zero orders, and they stay 80 clicks a month until one negative is added.

Per-term review structurally cannot see this. It evaluates rows in isolation, and the signal only exists in aggregate. That is the case for n-grams, and it is the only case you need.

The second thing n-grams give you is the mirror image: the word that is earning. If every search term containing insulated converts above your target, that is a theme worth its own campaign, not just a keyword worth a bid bump.

2. The method: the thresholds and the math

The two negation thresholds, honestly presented as two

There is no single canonical click count, and anyone who gives you one is rounding off a judgement call. We use two, from two different places:

Threshold	Meaning	Where it comes from
>= 5 clicks, 0 purchases (14-day)	Worth reviewing	The floor on our own wasted-spend view, chosen to keep one-click noise out of the list. It is a noise floor, not a verdict.
>= 20 clicks, 0 orders	Negate now, as exact	The upper-limit rule in our published optimization playbook. At 20 clicks with nothing to show, deliberation is not adding information.

Between 5 and 20 clicks, read the actual words. ceramic on a stainless steel mug account is wrong at five clicks and will still be wrong at fifty; negate it early. gift is ambiguous and deserves to accumulate more data before you cut it. The numbers exist to sort your attention, not to replace your judgement about what your product is.

Why a click threshold at all

Because the data is not final when you look at it. Amazon's search term reports lag 1 to 3 days behind real spend, and sales are attributed on a 14-day window. A term with a click from yesterday and zero purchases today may well convert next week and then be backfilled into the report. Negating on the first zero-order day systematically kills terms that were about to work. A click threshold is a crude but effective way of waiting long enough that the absence of a sale means something.

For the same reason, if you are about to negate something borderline, re-pull the report and look again before you commit.

The harvest threshold

The other half of the analysis, and the half people skip. Before you trust a click-through rate at all, you need enough impressions that the rate is not an artefact of small numbers — two clicks on four impressions is not a 50% CTR, it is a coin flip.

• >= 100 impressions and >= 3 clicks before a CTR means anything.
• On smaller accounts, under roughly $10K/month of spend, that floor excludes almost everything. Drop to >= 30 impressions and >= 2 clicks and treat the conclusions as lower confidence rather than pretending the floor didn't move.

Then filter to search terms that came in on broad or phrase keywords and converted, and promote them to exact match in an ad group where you control the bid directly. A converting search term sitting inside a broad keyword is being bid on by accident.

The ratio that tells you if the program is working

Accounts with healthy ACoS tend to carry three to five times more negatives than positives. That is a rule of thumb, not a target to game — adding junk negatives to hit a ratio helps nobody — but if your account has 400 keywords and 30 negatives, you have not been doing this, and the n-gram sweep will find a lot on its first run.

The scoreboard

Track wasted spend as a share of total spend:

Share of spend with clicks and no conversions	Read
Under 2%	Healthy. The analysis is mostly informational.
3-4%	Typical for an active Amazon Ads account.
Over 5%	Real money. Lead with this number when you report it.

One diagnostic worth calling out while you are in the data: if a search term that closely matches your own product name is taking clicks and converting nothing, that is not a keyword problem. That is a listing, price, review or stock problem on that ASIN, and a negative keyword would be exactly the wrong fix.

3. Doing it by hand

Budget 45 to 90 minutes per marketplace, weekly, once you have the sheet built. The first build takes longer.

Step 1 — Export the Sponsored Products search term report

The search term report lives in the reporting area of the Amazon Ads console; you create a report, choose Sponsored Products as the ad product and the search term report type, set your window, and download the result. Amazon renames these menus without notice and the exact labels differ by account and region, so if what you see does not match what anyone wrote down, search the console for "search term" rather than hunting for a remembered menu item.

You want, at minimum: search term, matched keyword, match type, campaign, ad group, impressions, clicks, cost, 14-day sales, 14-day purchases.

Step 2 — Export Sponsored Brands separately

Sponsored Brands is a second export with a different shape, and three differences matter:

1. Both reports call the shopper's query searchTerm. That column joins cleanly.
2. The targeted keyword column does not. It is keyword on Sponsored Products and keywordText on Sponsored Brands. A merge that assumes one name silently drops half your rows.
3. Sponsored Brands emits rows with matchType = THEME, whose keyword text is an Amazon placeholder such as keywords-related-to-your-landing-pages rather than anything you chose. The searchTerm on those rows is a real customer query and belongs in the analysis. If you filter to BROAD, PHRASE and EXACT you will quietly drop them — and on some accounts THEME is the dominant or only match type, so that filter can remove most of the spend.

Step 3 — Stitch the window together

Amazon retains roughly 95 days of Sponsored Products search term history and roughly 60 days of Sponsored Brands. Those are the depths our own historical backfill caps at, because past them Amazon stops returning data. Two consequences:

• A window longer than a single export returns has to be pulled in chunks and stacked. Add a column naming the source export so a failed chunk is visible, and check for overlapping dates before you total anything — one duplicated week inflates the cost behind every n-gram derived from it.
• Nothing gives you last year. If you want a year-over-year read on a search theme, the only way to have it is to have been storing exports before you needed them. Start now; there is no retroactive fix.

Step 4 — Clean and collapse

Normalise the text so Ceramic Mug, ceramic mug and ceramic mug. become one thing. In Google Sheets, with the raw search term in A2:

=REGEXREPLACE(REGEXREPLACE(LOWER(TRIM(A2)), "[^a-z0-9 ]", " "), " {2,}", " ")

Flag search terms that are ASINs rather than words, because an ASIN is one opaque token and contributes nothing to a word analysis:

=IF(REGEXMATCH(LOWER(A2), "(^| )b0[a-z0-9]{8}( |$)"), "asin", "text")

Then collapse to one row per cleaned search term, summing the metrics. The raw export has one row per search term per matched keyword per campaign, so the same clicks appear several times; tokenizing before you collapse multiplies that error through every n-gram. Keep the un-collapsed rows in a second tab — you need campaign and ad group context in step 8.

Step 5 — Tokenize

Layout that works: cleaned search term in A, metrics in B:F (impressions, clicks, cost, sales, purchases), a word block in G:T, a word-pair block starting at V.

Google Sheets, 1-grams. In G2, this single formula spills the words across the row:

=SPLIT($A2, " ")

Fill down. Size the block by the longest search term you have:

=MAX(ARRAYFORMULA(IF($A$2:$A="",,LEN($A$2:$A)-LEN(SUBSTITUTE($A$2:$A," ",""))+1)))

Google Sheets, 2-grams. In V2, filled right and down:

=IF(INDEX($G2:$T2,1,COLUMN()-COLUMN($V$2)+2)="","",
    INDEX($G2:$T2,1,COLUMN()-COLUMN($V$2)+1)&" "&
    INDEX($G2:$T2,1,COLUMN()-COLUMN($V$2)+2))

For 3-grams, the same shape with a +3 bound and a third concatenated INDEX.

Excel 365. TEXTSPLIT replaces SPLIT, and MAP over a SEQUENCE gives you every pair from one cell:

=TEXTSPLIT($A2, " ")

=LET(t, TEXTSPLIT($A2, " "), n, COLUMNS(t),
     IF(n < 2, "",
        MAP(SEQUENCE(1, n-1),
            LAMBDA(i, INDEX(t,1,i) & " " & INDEX(t,1,i+1)))))

TEXTJOIN is the tool for the reverse trip — rebuilding a phrase from a run of columns when you want a 3-gram written out, e.g. =TEXTJOIN(" ", TRUE, G2:I2).

Step 6 — Pivot by n-gram

You need a long table: one row per n-gram occurrence, carrying that search term's metrics. In Sheets, this one formula stacks the word block and aggregates it:

=QUERY(
  {FLATTEN($G$2:$T$5000),
   FLATTEN(IF($G$2:$T$5000="",,$B$2:$B$5000)),
   FLATTEN(IF($G$2:$T$5000="",,$C$2:$C$5000)),
   FLATTEN(IF($G$2:$T$5000="",,$D$2:$D$5000)),
   FLATTEN(IF($G$2:$T$5000="",,$E$2:$E$5000)),
   FLATTEN(IF($G$2:$T$5000="",,$F$2:$F$5000))},
  "select Col1, sum(Col2), sum(Col3), sum(Col4), sum(Col5), sum(Col6)
   where Col1 is not null and Col1 <> ''
   group by Col1
   order by sum(Col4) desc
   label sum(Col2) 'impr', sum(Col3) 'clicks', sum(Col4) 'cost',
         sum(Col5) 'sales', sum(Col6) 'purch'", 0)

The IF(block="",,column) trick broadcasts a single metric column across the width of the token block so FLATTEN lines the two up. Point the same formula at $V:$AH for 2-grams.

In Excel, the honest answer is Power Query: load the collapsed table, Split Column by Delimiter with Advanced options → Split into Rows, and you have the long 1-gram table natively; a PivotTable finishes the job. Word pairs need a custom column, at which point you are writing M rather than using the UI.

Now sort by cost descending, and drop obvious stopwords (for, with, and, the) or ignore them — they aggregate to large numbers and mean nothing.

Be honest about 3-grams. They are where this stops being pleasant. The block gets wide, most 3-grams occur once or twice so almost nothing clears a threshold, and recalculation on a few thousand rows gets slow enough to break your concentration. In practice 1-grams find the bleeding themes and 2-grams find the intent mismatches; 3-grams earn their keep occasionally, on large accounts, for phrases like for cast iron.

Step 7 — Score

Apply section 2 to the pivot. Sort the n-gram table by cost, filter to zero purchases, and walk down: anything at 20+ clicks goes on the negative list immediately; 5 to 20 clicks gets read rather than counted. Then re-filter for the harvest side, at 100 impressions and 3 clicks, and pull the converting broad and phrase terms for promotion to exact.

Step 8 — Apply the negatives, at the right scope

A negative keyword can be applied at two different scopes, and picking the wrong one is a common unforced error:

• Ad group negative — the word is wrong for this product but right for another product in the same campaign. Blocking it at ad group level steers that traffic to the ad group that should have it instead of throwing it away.
• Campaign negative — the word is wrong for everything the campaign sells. ceramic on a stainless steel campaign, free on a paid product, a competitor's brand you have no business bidding on.

The default should be campaign level for genuine irrelevance and ad group level for internal cannibalisation. Getting this backwards means either leaking the same traffic through a sibling ad group, or blocking a term that one of your products actually wanted.

Applying them is either the console, one at a time, which is fine for a dozen and miserable for two hundred; or a bulk upload. If you go the bulk route, download the template Amazon generates for your account and take the record types and column names from that file — the format changes, and a sheet built from a blog post's remembered column list will fail validation or, worse, apply something you did not intend.

Afterwards, check the ratio: three to five negatives per positive keyword is what healthy accounts look like.

Step 9 — Cadence

Weekly for fresh waste on a 7 to 14 day window. Monthly for the full n-gram sweep on 30 to 60 days, because a word bleeding at one click per term per week needs a wider window before any single n-gram crosses a threshold.

4. What breaks at scale

The spreadsheet is genuinely fine for one brand in one marketplace. Here is where it stops.

Row count. Somewhere around 5,000 to 10,000 search term rows the tokenization becomes the problem rather than the analysis. Sheets recalculation on a wide helper block gets slow, 2-gram blocks push past what fits comfortably, and 3-grams stop being attempted at all. You feel this first as "I'll skip it this week."

Report count. One marketplace is two exports, Sponsored Products and Sponsored Brands. Five marketplaces is ten. Add Sponsored Display and a second brand and you are at thirty downloads before any analysis happens, and each one is a chance to grab the wrong date range.

The retention clock. 95 days and 60 days are hard walls. Any question that needs more history than that — seasonality, year-over-year on a theme, whether a negative you added in March actually helped — is unanswerable from exports unless somebody was already archiving them. Most people discover this at the moment they need the answer.

Consistency. The analysis only works if it runs, and skipped weeks compound: the bleed you missed has accumulated more clicks, the backlog makes the next session longer, and a longer session gets skipped.

Judgement does not scale, and it is the part that matters. Deciding that ceramic is fatal and gift is ambiguous requires knowing what you sell. That is not a threshold, and it is the reason a purely mechanical rule applied across a whole account eventually negates something that was working.

5. Doing it with AI

Everything above is mechanical except step 7. Tokenizing, pivoting and thresholding are exactly the work a machine should be doing; the reason it usually isn't is not capability, it is that the data sits in a console behind a manual export.

Any AI assistant can do this analysis competently if, and only if, it can reach the underlying report data. That is the real requirement, and "use AI on your search term report" is usually sold without it:

• The assistant needs query access to the report rows, not a pasted CSV. A pasted export is a snapshot it cannot re-query, re-window or join.
• It needs more history than the console retains, or it inherits the same 95/60-day wall you have.
• It needs to know the column shape differences — keyword versus keywordText, THEME rows — or it will silently analyse a subset.
• It needs a write path if it is going to apply negatives, and that write path needs to know the difference between ad group and campaign scope.

The general shape of this is the Model Context Protocol: a server that exposes your advertising data to an assistant like Claude or ChatGPT as queryable tools rather than as files. Then the analysis on this page becomes a question you ask in plain language — "which 1-grams and 2-grams have over 20 clicks and no orders in the last 60 days, across Sponsored Products and Sponsored Brands" — and the answer arrives with the tokenization, the dedupe and the thresholds already applied. You still make the judgement calls in step 7, which is the correct division of labour.

That is what we built. Marketplace Ad Pros connects your Amazon Ads and Seller Central accounts, keeps report history past Amazon's retention window, and exposes it to your assistant over MCP — so the sweep, the harvest list and the negatives become a conversation rather than a Tuesday morning. If you would rather keep the spreadsheet, the procedure above is complete and it works; that is the point of writing it down.

Frequently asked questions

What is the difference between a search term and a keyword on Amazon?

A keyword is what you bid on. A search term is what the shopper actually typed. On a broad or phrase match keyword the two are usually different, which is the whole reason the search term report exists. In the Amazon Ads reports both Sponsored Products and Sponsored Brands name the shopper's query column searchTerm; the targeted keyword sits beside it in a column called keyword on Sponsored Products and keywordText on Sponsored Brands. An n-gram is a third thing again: a single word or a short run of consecutive words pulled out of a search term, which lets you total performance across every search term that contains it.

How many clicks before you add a negative keyword on Amazon?

There are two defensible numbers and they are not the same number. At 5 clicks with zero 14-day purchases a term is worth reviewing; that is the floor we use to keep a wasted-spend view from filling with one-click noise, not a rule to act on blindly. At 20 clicks with zero orders it becomes a negative exact, no further deliberation. Between the two, judgement beats arithmetic: negate an obviously irrelevant word early and give an ambiguous one more clicks. The reason for using a click threshold at all rather than negating on the first zero-order day is that Amazon's report lags 1 to 3 days and attributes sales over a 14-day window, so today's zero can still become tomorrow's sale.

How far back does the Amazon search term report go?

Roughly 95 days for Sponsored Products and roughly 60 days for Sponsored Brands. Those are the depths we cap our own historical backfill at, because they mirror what Amazon will still return. Past that horizon the data is gone unless something has been storing it for you, so if you want a year-over-year read on a search theme you have to start keeping your own copies now. Within the window, a long lookback often still needs several exports stitched together, and you should check for overlapping dates before totalling anything, since a duplicated week inflates every n-gram derived from it.

Can you do n-gram analysis in Google Sheets without an add-on?

Yes, with SPLIT, FLATTEN and QUERY, and no add-on. Clean the search terms with REGEXREPLACE and LOWER, spill the words across a helper block with a SPLIT of the search term cell on a single space, build word pairs by concatenating adjacent helper columns, then stack the token block with FLATTEN and aggregate with a QUERY that groups by token and sums clicks, cost, sales and purchases. Excel 365 does the same with TEXTSPLIT plus MAP and LAMBDA, or with Power Query's Split Column by Delimiter into Rows. The exact formulas are written out in section 3 of this page. Single words and word pairs are comfortable. Three-word grams are where it gets unwieldy: the number of tokens grows with search term length, the helper block gets wide, and recalculation on a few thousand rows becomes slow enough to be annoying.

How often should you run an n-gram analysis on Amazon search terms?

Weekly for fresh waste, monthly for the full sweep. The weekly pass looks at the last 7 to 14 days and catches terms that have already blown past the 20-click threshold. The monthly pass is the one that finds shared themes, because a bad word spread across fifty search terms at one or two clicks each needs a wider window before any single n-gram accumulates enough clicks to be conclusive. A monthly n-gram sheet to strip low-quality themes is the cadence we publish in our own optimization playbook.

Does the Sponsored Brands search term report use the same format as Sponsored Products?

Not quite, and the differences will break a naive merge. Both reports name the shopper's query searchTerm, so that column joins cleanly. The targeted keyword column does not: it is keyword on Sponsored Products and keywordText on Sponsored Brands. Sponsored Brands also emits rows with a match type of THEME whose keyword text is an Amazon placeholder such as keywords-related-to-your-landing-pages rather than anything you chose. The searchTerm on those rows is a real customer query and belongs in the analysis, so do not filter to BROAD, PHRASE and EXACT and assume you have everything. On some accounts THEME is the dominant match type. Retention differs too: about 95 days for Sponsored Products against about 60 for Sponsored Brands.

Related reading

• Amazon keyword optimization — where search term work sits in a wider keyword program
• Amazon advertising reports — the full set of reports behind this analysis
• Amazon Search Query Performance reports — the organic-side view of the same queries
• Amazon ads keyword research with AI — the harvest half, in more depth
• Best Amazon ads reporting and analytics tools in 2026 — if you would rather buy the tooling than build the sheet

---

Console navigation and export mechanics age fast, because Amazon changes its interface without notice. This page is re-verified quarterly. Verified as of 2026-09-02.