# Amazon Organic vs Paid Keywords in One View

Canonical page: https://www.kuudo.com/guides/amc-organic-paid-keyword-unified-view/

Amazon organic vs paid keywords: put Search Query Performance beside AMC paid search-term traffic, one row per query and week, with no ad math leaking.

Author: Thomas Spicer

## Summary

- Search Query Performance (SQP, GET_BRAND_ANALYTICS_SEARCH_QUERY_PERFORMANCE_REPORT) counts impressions, clicks, cart adds and purchases per search query for the ASINs (Amazon Standard Identification Numbers) you request. It has no ad spend field and doesn't split sponsored from organic, so organic can't be computed by subtracting your ad numbers from it.
- Amazon Marketing Cloud (AMC) carries customer_search_term on every sponsored ads impression in sponsored_ads_traffic, including impressions nobody clicked. The Sponsored Products search term report keeps only terms with at least 1 ad click.
- Aggregate each side to one row per search query and week before the join. SQP's total fields describe the whole query and repeat on every ASIN row, and paid rows repeat per campaign and keyword.
- Ad spend and ad-attributed sales stay in the paid columns. SQP purchases and ad-attributed purchases count different things, so no ratio mixes them.

Amazon organic vs paid keywords can't be split by subtracting one report from another. Put Brand Analytics Search Query Performance beside your paid search-term traffic instead: aggregate each to one row per search query and week, join on the query, and keep ad spend and ad-attributed sales in the paid columns only.

A brand manager asked me for the "organic version" of our top keywords. The tempting shortcut was SQP clicks minus ad clicks, which assumes the two reports measure the same thing on the same calendar. They don't. [Search Query Performance vs. Search Terms](/guides/amazon-ads-sqp-ppc-keyword-signals/) covers which report is which; this guide builds the view that puts them on one row.

The decision on the table was whether to cut bids on queries where we already win the search funnel. That depends on which queries our team defends no matter what, and a generic keyword report doesn't know that list. [Our agent](/features/ai-clients/) pulls SQP through the [Selling Partner MCP](/features/amazon-selling-partner-mcp/), runs the paid side in [Amazon Marketing Cloud](/features/amc/) through the [Amazon Ads MCP](/features/amazon-ads-mcp/), and checks our defended list before it marks any query as a bid-cut candidate.

## Search Query Performance counts the whole search funnel, so organic can't be backed out of it

Search Query Performance is `GET_BRAND_ANALYTICS_SEARCH_QUERY_PERFORMANCE_REPORT`, and [Amazon Agent Atlas](/features/agent-atlas/) holds Amazon's definition of it. It returns impressions, clicks, cart adds and purchases per search query for a space-separated list of ASINs, capped at 200 characters. It needs the Brand Analytics Selling Partner API role and Amazon Brand Registry, and it can only be requested, not scheduled through the API.

Each row carries two kinds of numbers:

| Field family | Example fields | Describes |
| --- | --- | --- |
| Query totals | `totalQueryImpressionCount`, `totalClickCount` | The whole query, every product |
| Your ASIN | `asinClickCount`, `asinPurchaseCount` | One requested ASIN |
| Shares | `asinClickShare`, `asinPurchaseShare` | ASIN count over query total |
| Demand | `searchQueryVolume`, `searchQueryScore` | How often, how important |

Nothing in that schema holds ad spend or ad-attributed sales, and nothing separates sponsored placements from organic results. So "organic" can't be derived from SQP by removing your ad clicks: Amazon doesn't document that the ad clicks sit inside these counts, on the same week, for the same ASINs.

> **Subtraction isn't a measurement**
> SQP clicks minus AMC ad clicks produces a number with no definition. Show the two side by side and let the gap be read, not computed.

## AMC sponsored_ads_traffic gives the paid side per search term, including impressions nobody clicked

Amazon Marketing Cloud records the keyword and the `customer_search_term` on every sponsored ads impression in `sponsored_ads_traffic`, including impressions from users who didn't click. That matters for the join. The Sponsored Products search term report includes only search terms that resulted in at least 1 ad click, so a query your ad showed on without a click never appears there.

This query is adapted from Amazon's instructional query in the AMC playbook "Use case: How to query sponsored ads keywords", with `spend` added. The agent runs it through the Amazon Ads MCP for the same Sunday-to-Saturday week as the SQP request:

```sql
/* Adapted from the instructional query: Traffic by keyword and search term */
SELECT
  targeting AS keyword,
  customer_search_term,
  match_type,
  campaign,
  ad_product_type,
  SUM(impressions) AS impressions,
  SUM(clicks) AS clicks,
  SUM(spend) AS spend_microcents
FROM
  sponsored_ads_traffic
WHERE
  match_type IN ('PHRASE', 'BROAD', 'EXACT')
GROUP BY
  1,
  2,
  3,
  4,
  5
```

The `match_type` filter keeps keyword-targeted traffic only. That leaves out automatic and product-attribute targeting, where Amazon's search term report shows alphanumeric ASIN strings in place of typed queries; those are product pages, not searches. AMC holds Sponsored Products keywords and search terms from the instance's creation date or 12.5 months back, whichever is sooner. The Ads API search term report keeps 65 days, at most 31 days per request.

Paid purchases and sales come from the same playbook's second query, with `total_product_sales` added, attributed to the search term the ad served on:

```sql
/* Adapted from the instructional query: Attributed conversions by keyword and search term */
SELECT
  targeting AS keyword,
  customer_search_term,
  match_type,
  campaign,
  ad_product_type,
  SUM(total_detail_page_view) AS total_dpv,
  SUM(total_purchases) AS total_purchases,
  SUM(total_product_sales) AS total_product_sales
FROM
  amazon_attributed_events_by_traffic_time
WHERE
  match_type IN ('PHRASE', 'BROAD', 'EXACT')
GROUP BY
  1,
  2,
  3,
  4,
  5
```

## Aggregate each side to one row per query and week before the join, or the join multiplies SQP

Both outputs repeat rows the join can't see. The AMC results carry one row per keyword, match type and campaign for each search term. SQP carries one row per requested ASIN for each query, and its total fields describe the whole query, so they repeat identically on every ASIN row.

Join the raw rows on the search query and every SQP row fans out across every paid row for that query. The agent applies these rules, from the grain Atlas grounds for each report, before it writes a single joined row:

| Step | SQP side | AMC paid side |
| --- | --- | --- |
| Grain | Query, week | Search term, same week |
| Your-ASIN counts | Sum across your ASINs | Sum across campaigns, keywords |
| Query totals | Take once, never sum | Not applicable |
| Shares | Sum across your ASINs | Not applicable |
| Join key | Search query, normalized | Search term, normalized |

Normalizing the key means the same case and spacing on both sides before matching. A query that exists on only one side stays in the view with the other side empty; a missing side is evidence, not an error.

## Ad spend and ad-attributed sales stay in the paid columns

The joined row keeps two sets of columns that never feed each other. SQP's columns describe the search funnel and carry no cost. The paid columns carry impressions, clicks, spend, ad-attributed purchases and ad-attributed sales, and any cost or return ratio is computed there and only there.

Two units matter:

- **`spend`** is in microcents. Divide it by 100,000,000 to get your currency.
- **`total_product_sales`** is already in local currency, and it includes brand halo sales: other products from the same brand bought after the ad.

SQP purchases and AMC's `total_purchases` count different events. One is a purchase SQP counts in the query's search funnel; the other is a purchase attributed to an ad that served on that search term. They don't share a denominator, so the view shows both and computes no blended rate.

The SQP side has its own published Skill. The [SQP ASIN N-gram Skill](/features/skills/amazon-seller/sqp-asin-ngram/) (a Reference Skill) scores each query and ASIN pair and tags every action as organic, operational or advertising, and its scope note rules out paid-search bid rules. The [Sponsored Products Search Terms Skill](/features/skills/amazon-ads/sponsored-products-search-terms/) (also Reference) works the paid report. No published [Skill](/features/skills/) builds the joined view, so the agent runs it on request or from a Skill your team writes.

## What happens next: refresh the joined view every week after SQP lands

[Amazon Agent Flow](/features/amazon-agent-flow/), the Amazon Agent Data layer, schedules both pulls and lands them in your private lake. Brand Analytics data is generally available within 72 hours of a period's close, so the weekly run goes in this order:

1. Request SQP for last week's Sunday-to-Saturday period, for the ASINs you track.
2. Run both AMC queries for the same window through the Amazon Ads MCP.
3. Re-run the attributed-conversions query for every earlier week that ended within the last 30 days, and replace those weeks' paid purchase and sales columns. `amazon_attributed_events_by_traffic_time` can add conversions up to 30 days after a window closes, so a week's paid conversions are provisional until then.
4. Aggregate each side to query and week, then join them in the lake with `query_lake_sql`.
5. List queries where the brand's SQP share is strong and paid clicks are high, and check each against the team's defended list. Treat a week still inside its 30 days as provisional.

Any bid change that comes out of step 5 goes through the Amazon Ads MCP and, depending on your workspace policy, may wait for your approval. Two reports, one row, no subtraction: that's the whole pattern.

*Next: how a query from this view becomes a bid rule that doesn't thrash, in [Amazon PPC Bidding Rules That Prevent Bid Thrash](/guides/rules-agent-bidding-rulebook/).*


## Frequently asked questions

### Can I calculate organic clicks on Amazon by subtracting ad clicks from Search Query Performance?

No. Search Query Performance counts impressions, clicks, cart adds and purchases per search query, and Amazon's schema doesn't split sponsored from organic activity. Subtracting ad clicks assumes the ad clicks are inside those counts, on the same window and the same ASINs, and none of that is documented.

### Why are search terms missing from my Sponsored Products search term report?

The report includes only search terms that resulted in at least 1 ad click, so a query your ad showed on without a click never appears. Amazon Marketing Cloud's sponsored_ads_traffic carries customer_search_term on every impression, including users who didn't click.

### How far back can I compare SQP with paid search term data?

The Amazon Ads API keeps Sponsored Products search term data for 65 days, with a 31-day maximum per request. AMC holds Sponsored Products keywords and search terms from the instance's creation date or 12.5 months back, whichever is sooner.

### What week does Search Query Performance use?

A WEEK request must start on a Sunday and end on a Saturday, and a request can't span two periods. Set the AMC query's time window to the same Sunday-to-Saturday week before joining.

### Why does my joined SQP and paid table show impossible numbers?

The join probably ran on raw rows. Paid rows repeat per campaign, keyword and match type, and SQP's total fields repeat on every ASIN row, so a raw join multiplies them. Aggregate each side to one row per search query and week, then join.

### Can I compute ACoS for organic keywords?

No. ACoS (advertising cost of sales) needs ad spend. Search Query Performance has no ad spend or ad-attributed sales fields, so any cost or return ratio belongs to the paid columns only. SQP purchases and ad-attributed purchases are different counts and don't share a ratio.


## Sources

- [Analytics report type values (Selling Partner API)](https://developer-docs.amazon.com/sp-api/docs/report-type-values-analytics)

- [Search term report for Sponsored Products](https://advertising.amazon.com/help/G3HEFZYWZF84NPS9)

- [Search term reports (Amazon Ads API)](https://advertising.amazon.com/API/docs/en-us/guides/reporting/v3/report-types/search-term)

- [Brand Analytics (Seller Central help)](https://sellercentral.amazon.com/help/hub/reference/GH2Z5B4HMF5ZXCG2)

- [Search Query Performance report schema](https://github.com/amzn/selling-partner-api-models/blob/main/schemas/reports/sellingPartnerSearchQueryPerformanceReport.json)