Your First AMC Queries: Tables, Filters, Dates

Every Amazon Marketing Cloud (AMC) query starts with the same four decisions: which table holds the event you are asking about, which of the three campaign identifiers scopes it to your campaigns, which ad_product_type you actually mean, and which date window applies. Get those four right and the query runs on the first submit. Our agent resolves all four through the Amazon Ads MCP, grounded by Amazon Agent Atlas, before it writes a line.

The reason this is a guide and not a footnote is that all four fail quietly. A colleague sent me a query last month that ran clean and returned nothing. She had pasted campaign IDs out of the ads console and matched them against campaign_id. The console shows campaign_id_string. Same campaigns, different column, zero rows, no error.

Why not ask ChatGPT or Claude to write it? A plain chat hits the same three walls on any Amazon job. It has no access to your data, so it cannot list your instance's tables or tell you which of your campaigns are Sponsored Brands. It has no way to take action, so it cannot submit the query and see the empty result; you paste it into the query editor and find out yourself. And it runs on generic knowledge, not Amazon's, so it treats campaign_id as the obvious ID column because that is what an ID column is called everywhere else. You get disconnected, generic, manual work that returns a clean, empty, wrong answer. Each decision below hits one of those walls, and shows how the Amazon Ads MCP (your instance, plus the tools to run against it), Skills (the repeatable workflow), and Atlas grounding (the private rule book) get past it.

Pick the table by the event, not by the metric you want

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.

sponsored_ads_traffic contains traffic events for all sponsored ads products, so Sponsored Products, Sponsored Brands, Sponsored Display and Sponsored Television all land there together. Amazon DSP (demand-side platform) traffic is separate, in dsp_impressions. Ad-attributed conversions live in the attributed events tables, amazon_attributed_events_by_traffic_time and amazon_attributed_events_by_conversion_time, which differ in whether a conversion is dated to the traffic event or to the conversion itself. The conversions table holds AMC conversion events more broadly, and counts a conversion as ad-attributed when a traffic event was served in the 28-day period before it.

One rule travels with the attributed tables and costs people real numbers: wait two weeks past the end of a campaign before trusting the totals. The 14-day attribution window is still open before that, so conversions are still being attributed while you are reading your report.

Three campaign identifiers, and the wrong one returns zero rows

Atlas retrieves the How to identify your campaigns and campaign IDs playbook for this, and the first thing it settles is that AMC exposes the same campaign three ways, which 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 an API. For Amazon DSP campaigns campaign_id and campaign_id_string carry the same value, which is exactly why the mistake survives a spot check against a DSP campaign and then fails on sponsored ads.

A first query worth running is the one that just lists what you have:

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

ad_product_type separates the sponsored ads families, but not the formats

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 the four accepted 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 both retail formats and Sponsored Brands Video, and they share one value. To isolate SBV you use the video viewership metrics on the same table, the columns beginning video_ plus five_sec_views, which populate only for video ads and read NULL for everything else.

Dates are a filter you write, not a setting you pick

There is no date picker in the query. The window is part of the SQL, and it takes one of two shapes depending on what you are asking.

For a calendar comparison, cast both sides so you are comparing dates rather than strings:

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 directly. This one counts purchases that landed within nine days of the ad exposure, which is how you ask 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

The arithmetic is deliberate. 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

Once these four decisions are resolved, the query is mechanical, which is exactly why it is worth handing over. The agent reads the table list and the identifier rules from Atlas, matches the IDs you actually have to the column that holds them, applies the ad_product_type filter for the family you named, and writes the date window in the shape your question needs. It runs the workflow through the Amazon Ads MCP and returns the result rather than the SQL.

Saved as a reusable Skill, that becomes the front door for every ad-hoc AMC question your team asks, and it composes with the workflows you already run through the Selling Partner MCP and the rest of the Amazon Agent Data layer. The dialect rules from part one still apply on top: no SELECT *, no ORDER BY, and thresholds that quietly drop thin rows.

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 two levers that fix it.