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.