One row per manual keyword per shopper search term per day, for Sponsored Products. The unique key is (keyword_id, search_term, date) — in 32,267 production rows there were 32,267 distinct keys — so the same search term appears on several rows for the same day when several of your keywords caught it, and the same keyword appears on as many rows as there were distinct queries.

"Manual" means the request is the standard spSearchTerm report filtered to keyword types BROAD, PHRASE and EXACT: the keywords an advertiser typed in, not the targeting expressions of an auto campaign. Auto campaigns' search terms are the same 53 columns under a different filter, and a separate report.

The left of the row is who matched what: search_term is the query the shopper typed, and keyword, keyword_type and keyword_bid are the keyword that won the impression, with campaign, ad group and portfolio context. Two pairs here are duplicates by construction. keyword equals targeting on every row, and keyword_type equals match_type on every row — Amazon carries both names, so both are here, but they are not two dimensions. That is the opposite of the auto report, where keyword and targeting diverge because a category target carries an id in one and a name in the other.

search_term is not one of those duplicates. It equals keyword on 27.1% of rows — an exact-match keyword the shopper typed verbatim — and differs on the rest, where a broad or phrase keyword caught a longer or reordered query. Both cases are real data.

The middle is delivery: impressions, clicks, click_through_rate, cost, cost_per_click. The right is attribution, repeated per window. Purchases, units and sales each come at 1, 7, 14 and 30 days, with same-SKU subsets at every window; the other-SKU columns come at 7 days only; ACOS and ROAS come at 7 and 14 days only. The windows nest — sales_30d contains sales_7d — so they are never summed.

Through the Amazon Ads API v3, you request a report with reportTypeId: spSearchTerm, adProduct: SPONSORED_PRODUCTS, groupBy: ["searchTerm"], timeUnit: DAILY and format: GZIP_JSON, a keywordType filter restricted to BROAD, PHRASE and EXACT, and the full list of 53 columns. The request goes to the /reporting/reports endpoint; Amazon returns a report id you poll until it is complete, then download.

The trap is the column list. v3 returns exactly the columns you name and nothing else, so a column that is missing from your file was missing from your request, not from Amazon's schema. Requesting all 53 columns on this page returns all 53: every one arrived on every one of 14 production files checked, and the sparse ones are present-and-null, never absent.

The response is a JSON array of flat objects with Amazon's camelCase keys. Two things about it are worth knowing before you load it. Ids (keywordId, campaignId, adGroupId, portfolioId) arrive as bare numbers of 11 to 15 digits and want to be strings. And if you let a loader infer types, it will infer differently for every file: money and ratio columns flip between integer and double according to whether that advertiser happened to have a fractional value that week, the ACOS columns come out as an all-null type for an advertiser with no sales, and the column order follows whatever order the JSON happened to carry. Pin a schema.

One request covers one profile, so an advertiser in five marketplaces makes five requests and gets five files, each with its own campaign_budget_currency_code. One request also covers the whole window you asked for as a single file; date is the column you split it on.

In the Amazon Ads console, the equivalent is Measurement & Reporting → Sponsored ads reports, category Sponsored Products, report type Search term, daily time unit. The console report is not filtered to manual match types, so it carries the auto-targeting rows as well. Treat that path as dated: Amazon has said it is sunsetting Sponsored Ads reports by 31 December 2026 in favour of unified reporting at Measurement and Reporting → Reporting. The API request above is the route that outlasts the move.

Harvesting search terms into exact-match keywords. Filter to rows where search_term differs from keyword, keyword_type is BROAD or PHRASE, and purchases_7d is above your threshold. Each one is a query a shopper converted on that you are only reaching by approximation; adding it as an EXACT keyword lets you bid on it directly. sales_7d against cost tells you which are worth it.

Building a negative-keyword list. Rows with clicks and cost but zero purchases_14d are the queries you are paying for and not converting. Because ACOS is null rather than infinite on these rows, you find them by filtering the counts, not by sorting acos_clicks_14d — sorting on ACOS puts the worst rows at the bottom, where nulls go.

Comparing match types on the same query. The grain puts the same search_term on one row per keyword that caught it, so a query reached by both a BROAD and an EXACT keyword on the same date shows up twice. Grouping by search_term and comparing cost_per_click and sales_7d across match_type is a direct read on whether the broad keyword is doing anything the exact one is not.

Auditing bids against what clicks actually cost. keyword_bid and cost_per_click sit on the same row. A keyword whose CPC runs close to its bid every day is being priced by that bid; one whose CPC is far below it has headroom that the bid is not using.

Measuring the halo on a keyword. sales_other_sku_7d against attributed_sales_same_sku_7d is how much a search term sells of things you did not advertise on it. A keyword that looks poor on same-SKU ACOS and fine on total ACOS is a keyword that introduces shoppers to the catalogue.

Reporting spend by portfolio. portfolio_id is on the row, so spend and sales roll up to portfolio without a join — but it is null for 43.6% of rows, because campaigns in no portfolio have no portfolio, and those rows must be kept as their own bucket rather than dropped.

The row is a keyword crossed with a search term, not a search term. The unique key is (keyword_id, search_term, date), so the same query appears once for every manual keyword that caught it that day. Totals across the whole file are still right — each row's spend is its own — but anything keyed on search_term alone is not. Counting rows to count queries, or joining this file to another table on search_term, multiplies by however many keywords matched. Group by search_term first, then join.

Amazon says every row had a click. The search term report reference states that search term reports only include impressions that resulted in at least one ad click, so a query that was shown and never clicked is not in this file and cannot be found by filtering it. Every production row observed did carry spend, which is consistent, but that is not a test of the claim — treat it as Amazon's statement, not as something measured here.

Attribution windows nest — never add them. purchases_30d contains purchases_14d contains purchases_7d contains purchases_1d, and the same for units and sales. Summing across windows multiplies revenue roughly fourfold.

ACOS is a percentage, ROAS is a multiple, CTR is a percentage. None of them is a 0 to 1 fraction. Values observed run up to 342.8 for acos_clicks_7d, 4,952 for ROAS and 200.0 for click_through_rate. A pipeline that divides by 100 or multiplies by 100 on assumption gets it wrong by two orders of magnitude.

ACOS is null on most rows. acos_clicks_7d and acos_clicks_14d were null on 86.1% of production rows — every row with spend and no attributed sales. An average of the column ignores precisely the rows that spent money for nothing. Compute cost ÷ sales_7d from the two money columns yourself; they are carried at full precision and the ratio is not.

ACOS and ROAS exist only at 7 and 14 days; the other-SKU columns only at 7. There is no acos_clicks_30d or sales_other_sku_30d. Anything at another window is a division you do.

keyword and targeting are the same column; so are keyword_type and match_type. Both pairs were equal on all 32,267 rows. Pick one of each and do not treat them as separate facets.

search_term equal to keyword is not a defect. It happens on about a quarter of rows and means an exact-match keyword was typed verbatim. Filtering those rows out as "no search term data" removes your best-performing queries.

Ids are numbers in the JSON and must be strings in your table. keyword_id, campaign_id, ad_group_id and portfolio_id are 11 to 15 digit integers on the wire. Loaded as numbers they lose leading zeros silently and, in some tools, precision.

Nulls in portfolio_id and keyword_bid are structural. A campaign in no portfolio has no portfolio_id (43.6% of rows); a keyword under a bidding strategy with no per-keyword bid has no keyword_bid (1.4%). Neither is a missing-data problem.

There is no spend column. Unlike the campaign and advertised-product reports, this one carries cost alone. A join that expects spend finds nothing.

Don't average the rate columns. click_through_rate, cost_per_click, ACOS and ROAS are computed per row. Recompute from impressions, clicks, cost and sales_* when aggregating.

Names are as-of-now. campaign_name and ad_group_name reflect the current names, not the names on the row's date. Join on ids.

Auto campaigns are a separate report, and so are Sponsored Brands and Sponsored Display. This file is Sponsored Products, manual keywords only. A complete search term picture needs at least the auto report alongside it.