⚡ THE ESSENTIALS IN 20 SECONDS
- With 6 or 7 Excel or Google Sheets functions you can do 80% of an Amazon Ads account analysis.
- The most useful: pivot tables, XLOOKUP, SUMIFS, IF, IFERROR and conditional formatting.
- The most valuable analysis is the search term report: finding winners and waste.
- You do not need to be an expert: you need a clear template and a weekly review.
IN THIS ARTICLE
Optimization tools are very useful, but there is something none of them replaces: understanding your data. And for that, Excel or Google Sheets are still the Swiss Army knife of PPC. Here are the formulas and tables I use most to optimize Amazon Ads campaigns.
PPC (pay-per-click): an advertising model in which you pay for every click on your ad. On Amazon, it refers to managing campaigns such as Sponsored Products, Sponsored Brands and Sponsored Display.
The reports you need
- Search term report (Sponsored Products): what the shopper typed and what happened.
- Targeting report: performance by keyword or product.
- Placement report: top of search, rest of search and product pages.
- Advertised product report: performance by ASIN.
Download them with the same date range (for example, the last 30 days) so you can cross them.
Basic calculated columns
| Metric | Excel formula (example) | What for |
|---|---|---|
| CTR | =IFERROR(Clicks/Impressions,0) | Ad appeal |
| CVR | =IFERROR(Orders/Clicks,0) | Listing conversion |
| CPC | =IFERROR(Spend/Clicks,0) | Actual cost per click |
| ACOS | =IFERROR(Spend/Sales,0) | Efficiency |
| ROAS | =IFERROR(Sales/Spend,0) | Return |
IFERROR avoids the annoying #DIV/0! errors when rows have no clicks or no sales.
Pivot tables
A pivot table lets you summarize thousands of rows in seconds. The ones I use most:
- Spend, sales and ACOS by campaign.
- Spend and sales by match type.
- Performance by placement.
- Performance by advertised ASIN.
Add an ACOS calculated field (Spend ÷ Sales) inside the pivot table so you do not have to calculate it row by row.
Finding winners and waste
| Filter | Condition (example) | Action |
|---|---|---|
| Winners | Orders ≥ 2 and ACOS ≤ target | Move to manual exact |
| Waste | Clicks ≥ 15 and Orders = 0 | Add as negative |
| Potential | High CTR and few impressions | Raise bid |
| Expensive | ACOS > 2× target | Lower bid or pause |
Adjust the thresholds to your conversion rate: if your average CVR is 10%, 15 clicks without a sale is already a signal. Then move winners as I explain in auto vs. manual.
XLOOKUP and SUMIFS
- XLOOKUP: pulls data from another table. Example: each ASIN’s margin from your cost sheet, to calculate real profitability.
- SUMIFS: sums with several conditions. Example: total spend on one brand’s campaigns in a date range.
- COUNTIFS: counts rows that meet conditions. Example: how many searches have clicks but zero orders.
N-gram analysis
An n-gram is a word or combination of words inside search terms. Instead of looking at thousands of searches, you add up performance by word. If the word “cheap” appears in 200 searches with high spend and few sales, you may want to add it as a negative phrase.
- Split each search term into words (TEXTSPLIT in Excel or SPLIT in Sheets).
- Create a unique list of words.
- Sum spend, clicks, orders and sales by word with SUMIFS or a pivot table.
- Sort by spend and review each word’s ACOS.
Conditional formatting
A traffic light on the ACOS column lets you see in seconds where to act. Do the same with the impression share of your main keywords.
A typical case (fictional)
A baby care brand had an account with thousands of search terms and did not know where to start. With an n-gram analysis it found that three words drove a large share of spend with no sales. It added them as negative phrases and moved its top 20 terms to exact campaigns. Within a month, ACOS fell and sales held.
You do not need to analyze every row: you need to find the patterns that move the money.
Weekly checklist
- Download reports with the same date range.
- Add CTR, CVR, CPC, ACOS and ROAS columns.
- Pivot table by campaign and match type.
- Winners and waste filter.
- N-gram analysis once a month.
- Log the changes you made and their date.
- Share a one-page summary with the team, using a common glossary.
Agency secrets about Excel for PPC
What I do on every account:
Use the same template every week: paste the report and let the formulas do the rest.
Write down every bid change or negative; that is how you know what worked.
Cross with margin by ASIN: a “good” ACOS may not be profitable on a low-margin product.
Excel does not optimize your campaigns, you do. But with the right formulas you see in minutes what used to take hours.
I’m Alonso Jiménez, a Retail Media account director. I manage campaigns on Amazon, Walmart, Target, Instacart and Mercado Libre every day, for brands in the US, Mexico and Latin America.
Frequently asked questions
Which Excel formulas help with Amazon Ads?
Pivot tables, XLOOKUP, SUMIFS, COUNTIFS, IFERROR and conditional formatting.
What is n-gram analysis?
Summing performance by word within search terms to find patterns.
Excel or Google Sheets?
Both work; the functions are equivalent.
Which report is the most useful?
The Sponsored Products search term report.