Your First AMC Queries: Tables, Filters, Dates

Useful Amazon Marketing Cloud (AMC) SQL query examples 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 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 lists the data sources in your authorized AMC instance and runs queries against it.
• Your judgment, running every time. The amazon-ads-marketing-cloud Skill preserves the rules your team expects, and Amazon 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.

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.

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:

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:

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 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.

-- 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.

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:

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:

-- 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.

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, 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, that becomes the front door for ad-hoc AMC questions, and it composes with the Selling Partner MCP and the rest of the Amazon Agent Data layer. Saving a workflow or a schedule to your instance is a change, and it may require approval. The dialect rules from part one 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.