N-Gram Analysis for Amazon PPC: Find the 28% of Spend With Zero Orders

What N-Gram Analysis Does That Your Search Term Report Cannot

Your search term report shows you one row per customer search term. Each row has spend, clicks, and orders attached. The problem is that expensive patterns hide inside cheap rows. A search term with two clicks and no sales does not look like a problem. Put 200 of those rows side by side and they all share the same word, and the cumulative spend on that one word is the real number that should make you react.

That is what n-gram analysis does. It takes every search term in your report, splits it into 1-word pieces (unigrams), 2-word pieces (bigrams), and 3-word pieces (trigrams), then rolls up the metrics for each piece across the whole report. Now you can see how every word and every two-word phrase is performing across thousands of search terms at once, not one row at a time. The waste that the row-level report hides becomes obvious in the n-gram view. If your PPC campaign structure is built on broad match and auto campaigns, this is exactly where the bleed happens and the n-gram view is the fastest way to find it.

On a $40k/month account we ran this on last quarter, the n-gram view surfaced roughly 28% of monthly spend sitting on patterns with zero historical orders. The seller had not seen it in the row-by-row report because no single search term hit their “negate after 7 clicks” threshold. The waste was real, it was just distributed. This is one of the most common patterns we see during an Amazon PPC audit, accounts that look healthy on the surface with 30-40% ACoS carrying 20-30% of their spend on patterns that have never converted.

The Exact Excel Process We Run on Every Account

Below is the workflow we use on supplement, beauty, pet, home, and apparel accounts. It assumes you know basic SUMIFS and pivot tables, and works in Excel 365, older Excel, or Google Sheets. Run it on a 30 to 60 day Summary report. The top 20 bigrams by spend will usually account for 50% to 70% of total spend and reveal every pattern worth acting on. This is the same process we walk through during an Amazon PPC audit for accounts over $5k/month in ad spend.

1. Pull the search term report. Seller Central, Advertising, Measurement and Reporting, Sponsored Ads Reports, Sponsored Products, Search Term, Summary, last 30 days. Save as CSV. You need at least these columns: Customer Search Term, Impressions, Clicks, Spend, Orders, Sales. If you need the full navigation path, our guide on how to pull a search term report walks through it step by step.

2. Clean the search term column. Lowercase everything. Replace punctuation with a single space. Trim. The cleaning matters because “Water Bottle” and “water bottle” produce different n-grams if you skip this. One helper column: =LOWER(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"-"," "),"."," "),","," ")))

3. Add a Bigram column. For Excel 365, this extracts the first two words: =IFERROR(INDEX(TEXTSPLIT(A2," "),1)&" "&INDEX(TEXTSPLIT(A2," "),2),""). For older Excel, use FIND and MID. Power Query handles trigrams cleanly if you have a lot of long-tail search terms.

4. Build the rollup. Copy the Bigram column to a new sheet, dedupe it, then use a pivot table with Bigram in Rows and Spend, Clicks, Orders, Sales in Values (all summed). Add a calculated field for ACoS (Spend / Sales) and Conversion Rate (Orders / Clicks).

5. Sort by Spend, descending. Scan the top 30. The patterns with high spend and zero orders go in the KILL batch this week. The patterns with high spend and strong conversion rate go into a new exact match campaign. Everything else stays.

Run the Analysis On Your Own Report

The tool below runs the same n-gram analysis on the search term report you upload. Your file never leaves your browser. Drop in the CSV, and the tool will generate unigrams and bigrams with rolled-up impressions, clicks, spend, orders, sales, ACoS, and conversion rate. Click any column header to sort. The threshold inputs flag NEGATE and HARVEST candidates automatically. ASINs are detected by their 10-character alphanumeric format and split into a separate ASIN Rollup tab, with automatic classification based on whether they appear in your Targeting column. The trigram view is off by default because it needs more data to be useful. If you want a deeper view, our guide on Amazon PPC tools covers what else is in the market.

The tool auto-detects column names. It looks for “Customer Search Term,” “Search Term,” or “Query” as the term column, “Spend” or “Cost” for spend, “Orders” or “Conversions” for orders, and so on. If you see a “missing column” message, open the CSV in a text editor and confirm the first row is a clean header. Some accounts export with a metadata block at the top, which breaks auto-detect. Delete those rows and re-upload. If you have a lot of files to process, our bulk operations guide covers how to automate the entire pipeline.

Two filters run automatically on the word-level n-gram view. First, ASINs are detected by their 10-character alphanumeric format and stripped from words, so they never appear as a “word” in the unigram or bigram tables. Pure-numeric ASINs (like ISBNs) are also detected. Second, pure-numeric unigrams (sizes, quantities, durations) are filtered out because they almost always produce high-occurrence low-signal noise. A search term like “30 day supply” becomes the word “day” and “supply,” not the number “30.” If you want to see the numeric unigrams anyway, they will appear when you search for them in the filter box, but the default sort hides them.

How to Handle ASINs in Your Search Term Report

ASINs are the 10-character alphanumeric codes Amazon uses to identify every product. Format looks like B08147KPRP. They show up in your search term report in two specific ways, and most n-gram tools handle them wrong by either ignoring them or trying to split them as words. If you are new to ASINs, our ASIN basics guide covers the format and how Amazon assigns them.

The first way ASINs show up is when a shopper typed the ASIN directly into the Amazon search bar. Common on branded products where the buyer already knows the SKU. The search term is literally just the ASIN, or the ASIN plus a word like “review” or “alternative.” The second way is when you run Product Targeting campaigns (Sponsored Products or Sponsored Display) and bid on competitor ASINs. Amazon’s auto-targeting also surfaces ASINs when the algorithm matches your ad to a related product detail page. In both cases, the search term in your report is the ASIN or a phrase that includes the ASIN.

The right way to handle them

Two steps. First, strip ASINs out of the search term before doing n-gram analysis on words. If you do not, a search term like “b08147kprp alternative” gets split into a bigram “b08147kprp alternative” that has no meaning, and a unigram “b08147kprp” that is the same single token counted as a “word.” Both corrupt the word-level rollup. The tool above does this automatically using the standard 10-character alphanumeric pattern. This catches both letter-based ASINs (B08147KPRP) and pure-numeric ones (0123456789, common in books and some legacy catalogs).

Second, build a separate ASIN-level rollup. Each unique ASIN in your report gets its own row with its own aggregated spend, clicks, orders, and ACoS. This is where you make the actual decisions on Product Targeting campaigns. The tool above has a dedicated ASIN Rollup tab for exactly this. The classification is automatic. If your report includes a Targeting column (which Amazon Sponsored Products and Sponsored Brands reports always do), the tool reads it and flags each ASIN as one of two things: “You Bid On” means the ASIN appears in your Targeting column, so you are paying for traffic to that product. “Shoppers Typed” means the ASIN appears only as a search term, which is a shopper who already knew the product and searched for it directly. The two require different optimization moves and the tool surfaces them as separate rows with separate tags.

The 4-quadrant grid applied to ASINs

The same grid works. ASINs you bid on (Targeting column) with high spend and zero orders are KILL, drop them from your product targeting list. ASINs you bid on with strong conversion are HARVEST, raise the bid on those targets and find more like them. ASINs shoppers typed directly with strong conversion are KEEP, build a branded defensive campaign around them so you do not lose those shoppers to a competitor. ASINs with low spend regardless of conversion are WATCH. The thresholds are the same as the word-level view, the same CVR and spend floor inputs control both tabs.

What to Do This Week: A Real Walkthrough on the Sample Data

Below is the actual week-one workflow using the sample data the tool ships with. The numbers are real. The actions are the ones we would take on a real account. Open the tool, click “Load Sample Data,” and follow along. This walkthrough assumes a PPC strategy built on broad match and auto campaigns, which is the most common starting point and where n-gram analysis has the highest impact.

The sample account is a collagen peptides brand with roughly $1,876 in tracked search term spend, $5,812 in sales, 158 orders, and a blended ACoS of 32%. Target ACoS for this account is 30%. Anything above 30% is losing money. Anything below is profitable. The CVR threshold is set to 5% and the spend floor to $20, which surfaces patterns that are both meaningful in volume and statistically significant.

Action 1: Negate the 5 biggest KILL patterns this week

The bigram view surfaces 5 patterns with high spend and zero orders. These are the immediate wins. Each one is wasting between $110 and $220 per period with no return. The negative targeting approach for n-gram analysis uses phrase match, not exact match, because the goal is to block the pattern in any order or with other words around it.

Total recoverable spend from this batch: $720 across 5 negations. That is your first week’s win. Add all 5 as negative phrase match keywords at the campaign level. Wait 7 days. Move to the next batch.

One nuance worth understanding. “collagen supplement” is flagged as KILL because the bigram itself has $112 in spend with only 3 orders at 54% ACoS. But “collagen” is a profitable unigram, and “supplement” alone is a high-converting unigram (12.1% CVR on $193 spend, mostly in “collagen coffee creamer” type terms). The bigram “collagen supplement” is being dragged down by generic search terms like “joint supplement” and “bone supplement” that Amazon’s broad match is pulling in. Negating the bigram as phrase match does not affect “collagen” or “supplement” alone. It only blocks search terms that contain the exact two-word sequence “collagen supplement.” That is what you want.

Action 2: Harvest 3 bigram winners into a new exact match campaign

The HARVEST candidates in the bigram view are the patterns with strong spend and conversion below the target ACoS. These are the patterns worth scaling. The bid optimization on the new exact match campaign is the lever that determines whether this scales profitably or burns through budget.

For “collagen peptides,” do not add the two-word phrase as exact match. The phrase match is the right level because it allows modifiers like “collagen peptides powder” and “collagen peptides for hair” to still match. If you add it as exact match, you lose all the long-tail variations that are driving the conversion. Create a new manual campaign, add “collagen peptides” as phrase match with a bid 20% above the source CPC from your auto or broad campaign, and let it run for 7 days. Compare the phrase match ACoS to the source campaign ACoS. If it holds below 30%, scale the budget. If you need help structuring the new campaign, our guide on PPC campaign structure walks through the campaign-level setup.

For “collagen powder,” the 8.6% CVR and 19% ACoS are the best numbers in the entire report. This is your most profitable pattern. Add it as exact match in a dedicated campaign with a higher starting bid. The conversion rate tells you the bid can support being above average and still hit your target.

For “for hair,” the 6% CVR is good but the word “for” is too generic to use as exact match. Phrase match around “for hair” would also block “for hair growth,” “for hair loss,” and other relevant terms. Better action: look at the trigrams containing “for hair” in the Trigrams tab. “Collagen for hair” and “collagen for hair growth” are the specific patterns worth promoting. Negate “for hair” as phrase match at the source campaign level to stop the broad-match bleeding, and promote the specific trigrams as exact match in the new campaign.

Action 3: Check the unigram view for hidden signals

The unigram view shows the performance of individual words across the whole account. Use it to find patterns that the bigram view cannot see. This is where branded vs non-branded keyword analysis lives, since you can see which brand terms and which generic terms are carrying the spend.

Look at the top unigram: “collagen” at $1,541 spend, 6.0% CVR, 27% ACoS. This is the brand-relevant core word, and it is profitable. Good. The fact that “collagen” as a single word is profitable does NOT mean you should add it as an exact match. It means the broad match campaigns on “collagen” are working, and you should leave them alone. Harvesting a single-word unigram as exact match almost always loses money because exact match on “collagen” will trigger on irrelevant queries like “collagen cream” and “collagen mask” that broad match was filtering.

Look at the unigram “coffee” at $193 spend, 12.1% CVR, 13% ACoS. This is an exceptionally strong pattern. The bigram “collagen coffee” is behind it, and “collagen peptides for coffee” is the trigram. This is a use-case angle your product serves. The data is telling you that shoppers searching for “collagen coffee” are high-converting buyers. Consider building a dedicated campaign around coffee-use-case terms: “collagen for coffee,” “collagen coffee creamer,” “collagen in coffee.” You can find these in the Bigrams and Trigrams tabs.

Look at the unigram “supplement” at $226 spend, 2.7% CVR, 64% ACoS. This is a NEGATE candidate at the unigram level. But you cannot negate “supplement” as a single word because you want to keep terms like “collagen supplement” in some contexts. The right move is to look at the bigrams and trigrams that contain “supplement” and negate only the underperforming ones: “joint supplement,” “bone supplement,” “hair skin and nails supplement.” Leave “collagen supplement” alone if it has any conversion, and let the phrase-match negations from Action 1 handle the worst offenders.

Action 4: Review the ASIN rollup for Product Targeting waste

Switch to the ASIN Rollup tab. Sort by Spend. The sample data has ASINs flagged as both “You Bid On” and “Shoppers Typed.” Look at the top “You Bid On” ASINs. If any of them have zero orders across the reporting period and more than $50 in spend, they are candidates to remove from your Product Targeting list immediately. Do not wait for the weekly batch. Dead ASIN targets are pure waste with no discovery value. This step is often missed because it lives in a separate report from the search term report, and many sellers never cross-reference the two. Our guide on PPC and organic ranking explains why keeping a healthy balance of paid and organic visibility matters.

The end-of-week checklist

After 7 days of running the actions above, pull a fresh search term report. Load it into the tool. Compare the summary card to last week’s numbers.

• Did “Spend sitting on KILL bigrams” drop? If yes, your negations worked. Repeat the same scan, find the next batch of 5-10 KILL patterns, repeat.
• Did “Sales from HARVEST bigrams” go up? If yes, your exact match promotions are scaling profitably. Increase the budget on the new campaign by 20-30%.
• Did the blended ACoS drop? If the ACoS is trending down, the workflow is working. If it is flat or rising, you are negating the wrong patterns and need to investigate which negations actually had traffic.
• Are new KILL patterns emerging? As you optimize, new search terms will surface that were hidden before. The top 5 KILL patterns this week will be different from the top 5 next week. That is normal. The discipline is the weekly scan, not a one-time cleanup.

The whole process, from upload to action to verification, takes 20-30 minutes per account per week. On a portfolio of 10 accounts, that is 4-5 hours per week for the full n-gram optimization across the entire book. We have seen accounts drop ACoS by 10-15 points in the first 30 days of running this process weekly, and the improvement compounds because the negative keyword list keeps growing.

The 4-Quadrant Decision Grid for N-Grams

Once you have the n-gram rollup in front of you, the temptation is to react to every line. Resist it. We use a two-axis grid, spend on one axis, conversion rate on the other. Every n-gram falls into one of four quadrants, and the action is different in each. The grid is the simplest part of the process and the part sellers skip most often, which is why the same waste shows up in their account every month. The same bidding strategy that drives your overall account also applies here, scaled to the n-gram level.

Set the spend threshold at 5% of the campaign’s monthly spend. Set the conversion rate threshold at 50% of the account’s average conversion rate. The thresholds matter more than the quadrants. A 2% CVR threshold on a 4% account is reasonable. A 1% threshold on a 1% account is too generous and will flag patterns that are actually performing at the category average. The same math applies when you calculate your break-even ROAS for the account overall, which sets the floor for every n-gram decision below.

Questions We Get Every Week

Does n-gram analysis work for Sponsored Brands and Sponsored Display?

Yes, with caveats. Sponsored Brands has the same search term report structure as Sponsored Products, and the n-gram process is identical. Sponsored Display does not have a downloadable search term report, so the n-gram view on SD has to come from AMC audiences or the Search Query Performance report inside Amazon Ads. The decision logic is the same. The data source is different.

What is a good ACoS benchmark for the n-gram view?

It depends on category, margin, and goal. We look at the account’s blended ACoS, then judge n-gram ACoS as a ratio to that. An n-gram with ACoS 50% above the blended is a KILL candidate. An n-gram with ACoS 30% below the blended is a HARVEST candidate. The ratio matters more than the absolute number, because absolute ACoS varies by category while relative underperformance is universal. For a deeper look at how ACoS and ROAS relate across the account, our ACoS vs ROAS guide covers the math.

Phrase match or exact match for the negations?

Phrase match, almost always. The point of n-gram negations is to block the pattern in any order or with other words around it. Negative exact match only blocks the exact search term, which is too narrow. Negative phrase match blocks any search term that contains the n-gram as a phrase. For single high-intent words like “free” or “cheap” or “DIY,” use negative exact, because phrase match on a single word is over-broad. The full negative targeting framework covers when to use each match type.

How does this work with ASINs in the search term report?

ASINs are detected automatically using the standard 10-character alphanumeric pattern. They are stripped from the word-level n-gram view and built into a separate ASIN Rollup tab. The classification is automatic: if the ASIN appears in your Targeting column, it is tagged “You Bid On.” If it appears only as a search term, it is tagged “Shoppers Typed.” Both reports from Amazon include the Targeting column, so no manual input is needed. The decision grid (NEGATE, HARVEST, PROMOTE, WATCH) applies to both views with the same threshold inputs.

How is this different from Helium 10’s built-in n-gram tool?

Helium 10’s tool is the best built-in option at the unigram level. The bigram and trigram coverage is more limited, the decision thresholds are fixed, and ASINs are either filtered out entirely or treated as a single token without a separate rollup. The tool above runs entirely in your browser, is free, lets you set your own thresholds, and has a dedicated ASIN rollup with automatic classification based on your Targeting column. For a comparison of the broader Amazon PPC tools landscape, that guide covers the main options. The right tool depends on whether you want to do this yourself, semi-automate it, or fully outsource it.

The Three Things That Actually Move the Number

Pull the search term report and store it every month. Amazon only keeps 65 days. If you are not saving your own copy, you are running n-gram analysis on a shrinking base. Set a recurring download on the first of the month and store the file in a dated folder. The first time you try to compare quarters and realize two months are missing, you will wish you had started this earlier. This is the same reason we built the search term report workflow into every PPC audit we run.

Run the n-gram analysis on the bigram level every week. Bigrams are where the actionable signal lives for most accounts. Sort by spend, scan the top 30, apply the 4-quadrant decision grid. KILL goes into the weekly negation batch. HARVEST goes into a new exact match campaign. Twenty minutes per account, every week. That is the whole discipline. This is one of the highest-ROI weekly tasks you can add to a PPC strategy.

Negate in small batches, not in one big purge. Adding 80 phrase match negatives in a single day will not give you a clear read on what worked. Negate 10 to 20 n-grams per week, monitor for 5 to 7 days, then move on. The discipline of small batches is what makes n-gram analysis a sustainable operating rhythm instead of a one-time cleanup that backslides within two months. We have seen accounts that ran this weekly for 90 days drop ACoS by 10-15 points and hold it.

One next step. Open the tool above, click “Load Sample Data,” and run through the bigram view. Sort by Spend. Identify the two or three n-grams that are obviously KILL and the two or three that are obviously HARVEST. Then pull your own search term report, upload it, and apply the same logic. Twenty minutes of work, every week, is the entire process.