Amazon Ads Search Term Report for Books: Step-by-Step Template With Formulas
InteliAds Team
KDP Ads
An Amazon Ads search term report for books lists the queries shoppers typed before clicking your ad. You read it to find two things: searches that cost you money and never buy, and searches that buy often enough to deserve their own exact-match keyword. This guide shows how to pull the report, lay it out in a spreadsheet, paste in the formulas, and turn each row into a decision. The example numbers are invented, and so is the book.
The method works for a single book or a catalog. The spreadsheet takes about 20 minutes to build once and a few minutes to refresh after that.
What an Amazon Ads search term report for books shows, and what it cannot
Amazon describes the Sponsored Products search term report as the search terms shoppers entered that resulted in at least one click. Amazon suggests using it to find high-performing searches that you can add as keywords, and under-performing searches that you can add as negative keywords.
Three consequences follow from that definition:
- Searches that showed your ad but got no click do not appear. You cannot find "impressions without clicks" in this report.
- For automatic campaigns, targeted ASINs that earn clicks appear in the same search term column, so you will see product codes next to phrases.
- Depending on how you build it, the same search can appear on many rows, for example once per campaign or once per day. Add the rows up before you judge a term.
Step 1: Pick the date range
Choose a window that ends at least three days ago. Barry of Barry KDP tells advertisers never to judge the most recent 48 hours, because the numbers are still being filled in. He also checks several windows (7 days, a month, last month, and up to 65 days). For a first pass, use the last 60 days. Amazon's authors guide advises waiting about two weeks after launching a campaign before changing its bidding strategy, so leave brand-new campaigns out of the first review.
Step 2: Pull the report from unified reporting
Amazon's unified reporting became generally available on June 8, 2026. In the same announcement, Amazon says the older Sponsored Ads reports are being sunset by December 31, 2026. In the Ads Console, unified reporting is under Measurement and Reporting, then Reporting, where you can build a custom report or start from a template. Amazon says it keeps up to 15 months of daily data. Read Amazon's announcement for the current menu names, because the console changes.
What to select, in general terms:
- Ad product: Sponsored Products.
- Report type or table: the search term report.
- Rows: customer search term, plus campaign, ad group, targeting and match type if Amazon offers them.
- Metrics: impressions, clicks, spend, orders and sales. Add page reads if your export has them.
- Time unit: daily or summary. Summary is easier. Daily lets you cut off the last two days yourself.
- Export as CSV.
We could not verify the exact field names Amazon uses inside the search term report in unified reporting, so the column headers in this article are generic. Rename them to match your file.
Step 3: Set up the sheet
Make two tabs, called Settings and Data.
On Settings, create these five cells and give each a name. In Google Sheets, use Data, then Named ranges. In Excel, use Formulas, then Define Name.
| Name | What it holds | Example value | How to choose |
|---|---|---|---|
| Royalty | Your net royalty per sale | 5.00 | KDP royalty for this format and marketplace |
| ListPrice | The book's list price | 15.00 | Your KDP price |
| NegateClicks | Clicks with no order before you negate | 10 | 10 is common among creators. Amazon suggests 20. |
| HarvestOrders | Orders before you promote a term | 2 | Ken Fornari uses at least 2 sales |
| TargetACOS | Highest ACoS you will accept for a promoted term | 0.30 | Ken uses 30% or less |
Break-even ACoS is Royalty divided by ListPrice. With the example values, it is 5 / 15 = 33.3%.
On Data, paste your export so that its columns match this layout. Columns A to I are your raw data. Columns J to Q are formulas you type once in row 2 and fill down.
| Col | Header | Formula for row 2 |
|---|---|---|
| A | Customer search term | (pasted) |
| B | Campaign | (pasted) |
| C | Targeting | (pasted) |
| D | Match type | (pasted) |
| E | Impressions | (pasted) |
| F | Clicks | (pasted) |
| G | Spend | (pasted, as a number) |
| H | Orders | (pasted) |
| I | Sales | (pasted, as a number) |
| J | CTR | =IF(E2=0,0,F2/E2) |
| K | CPC | =IF(F2=0,0,G2/F2) |
| L | CVR | =IF(F2=0,0,H2/F2) |
| M | ACoS | =IF(I2=0,"",G2/I2) |
| N | Estimated royalty | =H2*Royalty |
| O | Profit after ad cost | =N2-G2 |
| P | Break-even CPC | =L2*Royalty |
| Q | Verdict | see below |
The Verdict formula, in one line, to paste into Q2:
=IF(F2=0,"No clicks",IF(AND(H2=0,F2>=NegateClicks),"Negate",IF(AND(H2=0,G2>=Royalty),"Spend over one royalty, no sale",IF(AND(H2>=HarvestOrders,I2>0,G2<=TargetACOS*I2),"Harvest",IF(AND(H2>=HarvestOrders,O2<0),"Loses money: lower bid","Keep or wait")))))
The formula checks the rules in order, and the first one that is true wins. If you want a header row to paste straight into row 1, copy the line below (it is tab-separated):
Customer search term Campaign Targeting Match type Impressions Clicks Spend Orders Sales CTR CPC CVR ACoS Estimated royalty Profit after ad cost Break-even CPC Verdict
Before you paste a CSV, check that Spend and Sales are numbers and not text such as "$12.10". If a column is left-aligned after pasting, it is text. Remove the currency symbols and convert it. Keep all rows in one currency. Do not mix marketplaces in one sheet unless you convert them first.
Step 4: A worked example with invented data
Here are eight rows for an invented puzzle-book listing, using the Settings above (royalty $5.00, list price $15.00, negate at 10 clicks, harvest at 2 orders and 30% ACoS). Campaign, ad group, targeting and match type columns are left out to keep the table narrow. All eight rows are invented.
| Customer search term | Impressions | Clicks | Spend | Orders | Sales | CTR | CPC | CVR | ACoS | Est. royalty | Profit after ads | Break-even CPC | Verdict |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| word search large print | 900 | 14 | 8.40 | 0 | 0.00 | 1.56% | 0.60 | 0.0% | 0.00 | -8.40 | 0.00 | Negate | |
| puzzle book for seniors | 1800 | 22 | 12.10 | 3 | 45.00 | 1.22% | 0.55 | 13.6% | 26.9% | 15.00 | 2.90 | 0.68 | Harvest |
| sudoku | 4000 | 31 | 22.60 | 2 | 30.00 | 0.78% | 0.73 | 6.5% | 75.3% | 10.00 | -12.60 | 0.32 | Loses money: lower bid |
| b0exampleasin (an ASIN) | 500 | 6 | 3.00 | 0 | 0.00 | 1.20% | 0.50 | 0.0% | 0.00 | -3.00 | 0.00 | Keep or wait | |
| crossword for adults | 700 | 8 | 6.40 | 0 | 0.00 | 1.14% | 0.80 | 0.0% | 0.00 | -6.40 | 0.00 | Spend over one royalty, no sale | |
| brain games | 1500 | 12 | 7.20 | 1 | 15.00 | 0.80% | 0.60 | 8.3% | 48.0% | 5.00 | -2.20 | 0.42 | Keep or wait |
| large print sudoku | 600 | 9 | 4.50 | 2 | 30.00 | 1.50% | 0.50 | 22.2% | 15.0% | 10.00 | 5.50 | 1.11 | Harvest |
| activity book | 5200 | 40 | 18.00 | 0 | 0.00 | 0.77% | 0.45 | 0.0% | 0.00 | -18.00 | 0.00 | Negate |
How to read the verdicts:
- Word search large print and activity book: Negate. They have 14 and 40 clicks and no orders. Together they spent $26.40 of the $82.20 in the table, which is 32.1% of spend. That share is a property of this invented data, not a benchmark.
- Puzzle book for seniors and large print sudoku: Harvest. Both have at least 2 orders and an ACoS under 30%. Note that "large print sudoku" has only 9 clicks, which the 2-order rule alone would let through. Two orders can be luck, so give it a longer window before you commit.
- Sudoku: Loses money. It has orders, but at a 75.3% ACoS it loses $12.60. The break-even CPC (0.32) is well under the actual CPC (0.73). Lower the bid on the keyword that matched. Do not negate a term that sells.
- Crossword for adults: spend over one royalty, no sale. It has not reached 10 clicks, but it has already cost more than one sale would pay. Watch it, or trim its bid.
- Brain games: Keep or wait. One order at 48% ACoS is too little evidence. Chris Rawlings, who teaches Amazon ads in general, says not to decide from a single order.
- The ASIN row: Keep or wait. Six clicks is too early.
Step 5: Add the rows up by search term
If your export has one row per campaign per search term, the same term appears many times. Build a third tab called ByTerm to total it.
In Google Sheets, put this in A2 of ByTerm to list each term once:
=UNIQUE(Data!A2:A)
Then total each metric for that term (this example sums Clicks; change the column letter for the others):
=SUMIFS(Data!F:F,Data!A:A,$A2)
Do this for Impressions (E), Clicks (F), Spend (G), Orders (H) and Sales (I). Copy the J to Q formulas from the Data tab across, pointing at ByTerm's own columns. Judge the totals, not the individual rows.
To see how much of your spend is going to terms marked "Negate", use:
=SUMIFS(G:G,Q:Q,"Negate")/SUM(G:G)
Step 6: Choose your thresholds
The numbers in Settings are starting points. Here is where they come from, and where experienced advertisers disagree.
| Decision | Number | Source | Disagreement |
|---|---|---|---|
| Negate after clicks with no order | 10 | Barry KDP, Gerald of Driven Publishers | Amazon recommends at least 20 clicks. Chris Rawlings waits for 30 to 40 clicks at a 10% conversion rate. Gerald also describes 5 or even 1 click for aggressive accounts. |
| Promote after orders | 2 | Ken Fornari: at least 2 sales and ACoS 30% or less | Yevgen of AsDroid moves a keyword to its own campaign at 10 or more orders. |
| Acceptable ACoS for a promoted term | 30% | Ken Fornari | The right line is your own break-even ACoS, which is 33.3% for the example book. |
| Spend with no sale before you act | 1 royalty | Barry KDP | Yevgen uses $20 to $30 for pausing an unattended target. |
The probability behind the click threshold matters. If a term truly converts at 10%, the chance of no orders in n clicks is 0.9 to the power of n. That is 59% at 5 clicks, 34.9% at 10 clicks, 12.2% at 20 clicks and 4.2% at 30 clicks. At 10 clicks, about one good term in three will look dead. If you cannot afford to lose a good term, raise NegateClicks.
Step 7: Act, and write it down
Work through the Verdict column from the top.
Negate. Add the term as a negative. Amazon describes two negative match types, negative exact and negative phrase, and says both block close variations. Use negative exact for a single bad query. Use negative phrase only when every query containing the phrase is wrong. For an ASIN, add a negative product target. Read the ASIN list first, because you do not want to negate your own other books.
Harvest. Add the term as an exact-match keyword in a manual campaign. Ken sets the starting bid to the CPC the term already had. After you add it, negate the same term in the automatic campaign it came from, so the two campaigns do not bid against each other. Sam of Samazonkdp gives that advice.
Lower bid. Lower the bid on the keyword that matched, in a small step. Gerald warns that cutting too far can push your ad to a worse position and lower conversion.
Spend over one royalty, no sale. Put it on a watch list and check again at the next review.
Keep a log of every action. Five columns are enough.
| Date | Search term | Action | Where applied | Evidence at the time |
|---|---|---|---|---|
| 2026-11-13 | word search large print | Negative exact | Auto campaign, book A | 14 clicks, 0 orders, $8.40 (example row) |
The log lets you check later whether your decisions worked. Without it, you will repeat the same review every month with no memory of what you changed.
Mistakes that make this report misleading
- Judging the last two days. Orders can arrive after the click. Cut the range off early.
- Acting on one order. Wait for more data, as above.
- Treating attributed sales as royalty. The Sales column is what Amazon attributes to the ad. Your royalty is a different number, which is why the sheet multiplies orders by Royalty instead of using Sales.
- Ignoring page reads. If your ebook earns through Kindle Unlimited page reads, the sheet understates its income. Add a column for pages read and multiply by a per-page rate you choose. We do not give a rate, because the rate changes month to month.
- Negating a term that matches another book of yours. Check the ASINs.
- Mixing currencies or marketplaces in one sheet.
- Never refreshing. Experienced advertisers review search terms on a routine. Barry and Yevgen both describe two to three checks a week for a book that is being worked, and Ken harvests weekly. Choose a rhythm you will keep.
Frequently asked questions
Where is the search term report in 2026?
In unified reporting, in the Ads Console under Measurement and Reporting, then Reporting. Amazon says the older Sponsored Ads reports are being sunset by December 31, 2026, so rebuild any saved report there before the end of the year.
How many clicks should a search term get before I negate it?
Creators use about 10 clicks with no sale. Amazon recommends at least 20 clicks before adding a negative keyword. If you want to be careful, use 20, and use 30 or more for terms that look relevant to your book.
What is the difference between targeting and a search term?
Targeting is the keyword, ASIN or category you chose or that Amazon chose for an automatic campaign. The search term is what the shopper actually typed. One keyword can match many search terms, which is why you must keep both columns in the sheet.
Why do my search term totals not match my campaign totals?
Both reports come from Amazon, but they can be run on different dates, and recent days keep changing as orders are attributed. Run both for the same closed date range, at least three days old, and expect small gaps.
Can I use ChatGPT or Claude to read my search term report?
You can use one to summarize a table, but check the math. AI tools can get arithmetic wrong. Give the assistant your aggregated ByTerm table, ask it to cite the rows it uses, and verify any term it recommends against the sheet's formulas before you add a negative.
How often should I review the report?
Pick a rhythm. Several creators review two to three times a week for active books, and weekly or monthly for quiet ones. Whatever you choose, use the same date range each time and write down what you change.
Sources
- Amazon Ads: creating and using reports for book advertising
- Amazon Ads: unified reporting announcement (general availability June 8, 2026; sunset by December 31, 2026)
- Amazon Ads: targeting with Sponsored Products
- Amazon Ads: Sponsored Products for authors
- Barry KDP: masterclass
- Driven Publishers (Gerald): advanced bidding
- Ken Fornari: strategy video
- Yevgen (AsDroid) with Sean Dollwet: interview
- Chris Rawlings: bids and clicks
- Samazonkdp: three steps
How InteliAds does this
InteliAds syncs search terms from your connected Amazon Ads account, so there is no export step, and lets you filter keywords, product targets and search terms by the same metrics used above. The IF/THEN rules can act on search terms: add as a negative, or add as a target. Our 12 rules article turns Steps 6 and 7 into rules, and Dry Run lets you see what a rule would do first.
There is also a free search term report analyzer. You upload a CSV with Customer Search Term, Clicks, Spend and Orders columns, and it flags wasted spend, terms to negate, and terms to harvest. The page says the file stays in your browser and is not uploaded. The spreadsheet above does more, because it uses your royalty and your thresholds. The analyzer is faster for a quick look. If you prefer to automate this review, see InteliAds plans. Either way, an Amazon Ads search term report for books is only useful if you act on it on a schedule.