# Amazon Marketing Cloud Queries: Your First SQL

Canonical page: https://www.kuudo.com/guides/amc-sql-first-queries/

Write Amazon Marketing Cloud queries with the right event table, campaign identifier, ad product, and date range before trusting the results.

Author: Thomas Spicer
Updated: Oct 19, 2026

## Summary

- Pick the table by the event, not the metric. `sponsored_ads_traffic` holds sponsored ads impressions and clicks; `dsp_impressions` holds Amazon DSP; attributed conversions live in the `amazon_attributed_events_by_*` tables.
- There are three campaign identifiers. `campaign` is the name, `campaign_id_string` is the ID you see in the ads console, `campaign_id` is the ID the Ads API uses.
- `ad_product_type` separates the sponsored ads families: `sponsored_products`, `sponsored_brands`, `sponsored_display`, `sponsored_television`. It does not separate ad formats.
- The date range is set on the run, not in the SQL. SQL filters, such as a cast date comparison or `SECONDS_BETWEEN`, narrow the events inside it.
- Wait two weeks after a campaign ends before trusting attributed conversions. Amazon's AMC playbooks cite a 14-day attribution window.

Useful Amazon Marketing Cloud (AMC) queries begin with four choices: the event table, the campaign identifier, the ad product type, and the date range. Get one wrong and a syntactically valid query can return zero rows or answer a different question. This guide turns those choices into first queries you can adapt.

None of these is a syntax error, so the query can run and still answer the wrong question. The common case: campaign IDs pasted out of the ads console and matched against `campaign_id`. For sponsored ads, the console ID lives in `campaign_id_string`, and the two can differ, so the filter misses the campaigns you meant.

[ChatGPT, Claude, Perplexity, Microsoft Copilot, and whatever comes next](/features/ai-clients/) know nothing about your business out of the box. Your competitors use those tools too. Kuudo gives those same tools your edge: **your data**, **your rules**, and **the way your business operates**.

- **Your account, as it is right now.** The [Amazon Ads MCP](/features/amazon-ads-mcp/) lists the data sources in your authorized [AMC](/features/amc/) instance and runs queries against it.
- **Your judgment, running every time.** The [amazon-ads-marketing-cloud Skill](/features/skills/amazon-ads/marketing-cloud/) preserves the rules your team expects, and [Amazon Agent Atlas](/features/agent-atlas/) supplies Amazon's own documentation and playbooks.
- **Your call, before anything changes.** Approval controls keep activation and other consequential changes with you.

## AMC SQL query examples start with the event table the Amazon Ads MCP lists

The instinct is to look for a table with the number you need. AMC is organized the other way: tables hold **events**, and your metric is an aggregate over them. The Amazon Ads MCP can list the data sources in your instance, so the agent starts from the tables you actually have.

| Table | What it holds |
| --- | --- |
| `sponsored_ads_traffic` | Impression and click events from Sponsored Products, Sponsored Brands, Sponsored Display and Sponsored TV, one record per event |
| `dsp_impressions` | Amazon DSP (demand-side platform) impressions |
| `amazon_attributed_events_by_traffic_time` | Traffic and conversion pairs whose traffic happened inside the run's date range; the conversions can land up to 30 days after it |
| `amazon_attributed_events_by_conversion_time` | Pairs whose conversion happened inside the date range; the traffic can be up to 14 days earlier |
| `conversions` | AMC conversion events more broadly; ad-attributed when the user was served a traffic event in the 28 days before |

One rule travels with the attributed tables: wait two weeks past the end of a campaign before trusting the totals. Amazon's AMC playbooks cite a 14-day attribution window, and Amazon notes that output from the traffic-time table may change over time as late conversions arrive.

> **The AI is shared. The moat is yours.**
> - **Your account, as it is right now.** The shared AI cannot list the tables in your instance, so it cannot tell you whether the events you want are in `sponsored_ads_traffic` or `dsp_impressions`.
> - **Your judgment, running every time.** It has no reason to know about the two-week attribution wait, so it will happily hand you a query to run the morning after a campaign ends.
> - **Your call, before anything changes.** It cannot run the query to see that your conversion totals are still climbing day over day, which is the symptom that would have told you.
> - The Amazon Ads MCP supplies the instance tables and results, while Atlas and the Skill preserve the attribution wait.

## Atlas settles which of three campaign identifiers you hold

Atlas retrieves Amazon's `How to identify your campaigns and campaign IDs` playbook for this. The first thing it settles is that AMC exposes the same campaign three ways, and they are not interchangeable:

| Column | What it is | Where you see it |
| --- | --- | --- |
| `campaign` | Campaign name | Ads console; Order name in DSP |
| `campaign_id_string` | Campaign ID, STRING | Ads console; Order ID in DSP |
| `campaign_id` | Campaign ID, LONG | Sponsored ads API responses; Order ID in DSP |

Use `campaign` when you have the name, `campaign_id_string` when you copied an ID out of the console, and `campaign_id` when the ID came from the Ads API. For Amazon DSP campaigns the two ID columns carry the same value, which is why the mistake survives a spot check against a DSP campaign and then fails on sponsored ads.

A first query worth running lists the campaigns the attributed events table knows about, in the shape Amazon's playbook uses:

```sql
SELECT
  campaign,
  campaign_id_string,
  ad_product_type
FROM
  amazon_attributed_events_by_traffic_time
GROUP BY
  1,
  2,
  3
```

Read the result with two limits in mind. Only campaigns with attributed events in the date range appear, so a campaign with traffic but no attributed conversions is missing. And Amazon DSP rows carry a NULL `ad_product_type`.

> **The AI is shared. The moat is yours.**
> - **Your judgment, running every time.** The shared AI reaches for `campaign_id` because that is the conventional name for an ID column. In AMC that is the Ads API ID, and for sponsored ads a console ID may not match it.
> - **Your account, as it is right now.** It cannot check whether the IDs you are holding came from the console or an API, so it cannot catch the mismatch.
> - **Your call, before anything changes.** The query returns zero rows when you run it by hand, which reads like "no data" rather than "wrong column."

## The Skill filters `ad_product_type` by family, not by format

Because `sponsored_ads_traffic` pools every sponsored ads product, `ad_product_type` is how you narrow it. The `How to filter by ad product type` playbook lists four values: `sponsored_products`, `sponsored_brands`, `sponsored_display` and `sponsored_television`.

```sql
-- Instructional Query: Ad Product Type - Sponsored Products
SELECT
  campaign,
  campaign_id_string,
  ad_product_type,
  SUM(impressions) AS impressions
FROM
  sponsored_ads_traffic
WHERE
  ad_product_type = 'sponsored_products'
GROUP BY
  1,
  2,
  3
```

The limit is worth knowing before it bites: `ad_product_type` distinguishes product families, not ad formats. Sponsored Brands covers product collection, store spotlight and video ads, all under one value. Amazon's Sponsored Brands playbook separates them with the video viewership metrics on the same table, the columns beginning `video_` plus `five_sec_views`. Within Sponsored Brands, those are populated only for Sponsored Brands Video.

> **The AI is shared. The moat is yours.**
> - **Your judgment, running every time.** Ask the shared AI for Sponsored Brands Video and it may invent a filter value like `'sponsored_brands_video'`. There is no such value, so the filter matches nothing and the query returns zero rows.
> - **Your account, as it is right now.** It cannot look at your rows to notice that the `video_` columns are empty on most Sponsored Brands rows, which is the signal that separates the formats.
> - **Your call, before anything changes.** You find out when you run the query and the result comes back empty.
> - Atlas supplies the documented product values, and the Amazon Ads MCP lets the Skill test them against the live rows.

## Your AI client sets the date range on the run, then filters inside it

The date range is not part of the SQL. In the AMC query editor you pick it with the calendar. Through the API, a workflow execution takes `timeWindowStart` and `timeWindowEnd`, or a `timeWindowType` such as `MOST_RECENT_WEEK`. AMC uses UTC unless you choose a time zone. For "my campaigns last month", last month is the date range.

AMC data lags by up to 48 hours, so Amazon recommends a range that ends at least 48 hours before now. A range that ends yesterday can fail as unavailable.

Inside that range, SQL filters narrow further. To keep only campaigns that started after a given date, cast both sides so the comparison is date to date, as Amazon's playbook does:

```sql
SELECT DISTINCT
  cast(campaign_start_date AS Date)
FROM
  amazon_attributed_events_by_conversion_time
WHERE
  cast(campaign_start_date AS Date) > cast('2026-01-31' AS Date)
```

For a custom conversion window, measure the gap between the traffic event and the conversion event. This one counts purchases, brand halo purchases included, that landed within nine days of the ad exposure, a question the standard attribution windows do not answer:

```sql
-- Instructional Query: How to Filter by Custom Date --
SELECT
  campaign,
  sum(total_purchases) AS total_orders_9d
FROM
  amazon_attributed_events_by_traffic_time
WHERE
  SECONDS_BETWEEN (traffic_event_dt_utc, conversion_event_dt_utc) <= 60 * 60 * 24 * 9
GROUP BY
  campaign
```

Writing the window as `60 * 60 * 24 * 9` rather than `777600` keeps the intent legible to the next person, and to you in three months.

> **The AI is shared. The moat is yours.**
> - **Your judgment, running every time.** It writes `GETDATE()` into the SQL to build a relative window. AMC does not support `GETDATE()`, and the run's date range already sets the window.
> - **Your account, as it is right now.** It cannot see your campaign start dates, so a range it invents may not overlap your traffic at all.
> - **Your call, before anything changes.** And if you paste a sample of your result rows back into the chat to check the window looks right, your campaign data is now sitting in a place it should not be.
> - The Amazon Ads MCP supplies the campaign dates and runs the workflow with the date range set, while the Skill applies the Atlas-grounded rules before execution.

## What happens next: the Amazon Ads MCP runs the workflow

Once these four decisions are resolved, the query is mechanical, which is why it is worth handing over. Your AI client lists your instance's data sources through the [Amazon Ads MCP](/features/amazon-ads-mcp/), takes the identifier rules from Atlas, and matches the IDs you have to the column that holds them. It applies the `ad_product_type` filter, sets the date range, runs the workflow, and returns the result with the SQL it ran.

Saved as a reusable [Skill](/features/skills/), that becomes the front door for ad-hoc AMC questions, and it composes with the [Selling Partner MCP](/features/amazon-selling-partner-mcp/) and the rest of the [Amazon Agent Data layer](/features/amazon-agent-flow/). Saving a workflow or a schedule to your instance is a change, and it may require approval. The dialect rules from [the AMC SQL introduction](/guides/amc-sql-introduction/) still apply on top:

- No `SELECT *`: AMC does not export raw rows.
- No `ORDER BY`, except inside a window function's `PARTITION BY`.
- Aggregation thresholds: by default, rows under the minimum distinct-user count are dropped from the output.

Four decisions, made in order, and the blank editor stops being intimidating. The queries that fail after this point fail for a different reason: they are too expensive to finish.

*Next in this series: why AMC queries time out, and the sequence that fixes it.*


## Frequently asked questions

### Which AMC table should I query for sponsored ads impressions?

`sponsored_ads_traffic`. It holds impression and click events from Sponsored Products, Sponsored Brands, Sponsored Display and Sponsored TV campaigns, one record per event. Amazon DSP impressions live separately in `dsp_impressions`.

### What is the difference between campaign_id and campaign_id_string in AMC?

`campaign_id_string` is the Campaign ID shown in the ads console, in STRING format. `campaign_id` is the LONG-format ID that the sponsored ads APIs use. For Amazon DSP campaigns the two hold the same value.

### Why does my AMC query return no rows for a campaign I know is running?

You are probably matching a console ID against `campaign_id`. Use `campaign_id_string` for IDs copied from the ads console, or filter on `campaign` if you have the name. Then check that the run's date range covers the campaign.

### How do I filter to only Sponsored Products campaigns?

Filter `ad_product_type = 'sponsored_products'`. The documented values are `sponsored_products`, `sponsored_brands`, `sponsored_display` and `sponsored_television`.

### How do I separate Sponsored Brands Video from other Sponsored Brands ads?

`ad_product_type` does not distinguish ad formats. Use the video viewership metrics on `sponsored_ads_traffic`: `five_sec_views` and the columns beginning `video_`. Amazon's Sponsored Brands playbook says they are populated only for Sponsored Brands Video and show NULL for the other Sponsored Brands formats.

### How do I filter an AMC query to a custom date range?

Set the range on the run: the date range in the query editor, or `timeWindowStart` and `timeWindowEnd` on a workflow execution. Inside it, SQL filters narrow further, as in `cast(campaign_start_date AS Date) > cast('2026-01-31' AS Date)`, or `SECONDS_BETWEEN(traffic_event_dt_utc, conversion_event_dt_utc)` for a custom conversion window.

### How long should I wait before querying attributed conversions?

Two weeks past the end of the campaign. Amazon's AMC playbooks cite a 14-day attribution window, so querying earlier undercounts conversions that have not yet been attributed.


## Sources

- [Sponsored ads traffic table](https://advertising.amazon.com/API/docs/en-us/guides/amazon-marketing-cloud/datasources/sponsored_ads_traffic)

- [Amazon attributed events tables](https://advertising.amazon.com/API/docs/en-us/guides/amazon-marketing-cloud/datasources/amazon_attributed_events_by_conversion_time)

- [Conversions table](https://advertising.amazon.com/API/docs/en-us/guides/amazon-marketing-cloud/datasources/conversions)

- [Amazon Marketing Cloud data sources](https://advertising.amazon.com/API/docs/en-us/guides/amazon-marketing-cloud/datasources/overview)

- [Manage workflow executions](https://advertising.amazon.com/API/docs/en-us/guides/amazon-marketing-cloud/reporting/execute-workflow)

- [AMC SQL limitations](https://advertising.amazon.com/API/docs/en-us/guides/amazon-marketing-cloud/amc-sql/limitations)

- [AMC SQL reference](https://advertising.amazon.com/API/docs/en-us/guides/amazon-marketing-cloud/amc-sql/overview)