Building an AMC Cart-Abandoner Audience: A Workflow That Compiles on the First Try

The Slack message came in on a Tuesday: "Can you build me a DSP audience of people who added one of our hero ASINs to cart in the last 30 days but never bought? I want to retarget them before the next promotion."

This is the kind of ask that should take ten minutes. In practice, it eats an afternoon — not because the SQL is hard, but because Amazon Marketing Cloud has a dozen quiet rules that aren't in any one place. The query has to hit the right table. The user-level fields have to come from the variant table that AMC Audiences will actually accept. The seed has to land between 500 and 500,000 users or the audience refresh fails silently. Attribution windows have to close before the numbers stop lying to you.

So I asked our agent instead. The agent has Amazon Agent Atlas behind it — a curated corpus of AMC playbooks, instructional queries, audience patterns, and decision rules indexed for semantic retrieval. Here's what came back.

What a model without Atlas gets wrong

I tested this same prompt against a frontier model with no retrieval. The response looked confident. It wasn't.

Warning — four failure modes in a single un-grounded response:

1. It pulled from conversions instead of conversions_for_audiences. AMC Audiences requires the _for_audiences table variants — they're the only ones that permit SELECT user_id. Pasting the wrong one into the Audiences query editor produces a "user_id not selectable" error that doesn't tell you which table to switch to.
2. It used event_type = 'add_to_cart'. The real value is event_subtype = 'shoppingCart'. Close enough to look right; wrong enough to return zero rows.
3. It didn't mention the seed-size constraint. An audience that resolves to 380 users will appear to compile and then quietly fail to activate.
4. It didn't mention the 48-hour activation lag in Amazon DSP. The operator was planning to launch retargeting "tomorrow."

None of this is exotic knowledge. It's all in Amazon's public docs. But it's scattered across a dozen pages, half of which surface in search rankings only when you already know what to type.

What Atlas retrieves

When the agent gets the question, it does a semantic search across the amazon_ads collection and pulls four chunks before it writes a single line of SQL:

• The Audience that added to cart but did not purchase instructional query — the canonical AMC template for this exact use case
• The AMC Audiences table-variant rule — why conversions_for_audiences is the only table that allows SELECT user_id, and the corresponding _for_audiences suffix pattern across other AMC datasets
• The AMC Audiences activation guide — the 500 to 500,000 seed-size constraint and the ~48-hour DSP activation window
• The Introduction to AMC Audiences companion measurement query — the trick of changing user_id to COUNT(user_id) and dropping the _for_audiences suffix to size your audience before you try to create it

Atlas doesn't generate the SQL. It surfaces the playbook, and the agent adapts it.

The agent's output

Here's the query the agent produced. It compiled on the first try and resolved to a seed of just over 12,000 users — comfortably inside the activation window:

/* AMC Audiences IQ: Added to cart but did not purchase
   Adapted for hero-ASIN retargeting, 30-day window.
   Run in the AMC Audiences query editor (not the main editor) — _for_audiences
   table variants are required for SELECT user_id. */

WITH asins (asin) AS (
  VALUES
    ('B0XXXXXXX1'),   -- hero ASIN #1
    ('B0XXXXXXX2'),   -- hero ASIN #2
    ('B0XXXXXXX3')    -- hero ASIN #3
),
purchase AS (
  SELECT
    user_id,
    MAX(event_dt_utc) AS purchase_dt_max
  FROM conversions_for_audiences
  WHERE event_subtype = 'order'
    AND tracked_item IN (SELECT asin FROM asins)
  GROUP BY 1
),
atc AS (
  SELECT
    user_id,
    MAX(event_dt_utc) AS atc_dt_max
  FROM conversions_for_audiences
  WHERE event_subtype = 'shoppingCart'
    AND tracked_item IN (SELECT asin FROM asins)
  GROUP BY 1
)
SELECT atc.user_id
FROM atc
LEFT JOIN purchase
  ON atc.user_id = purchase.user_id
WHERE atc_dt_max > purchase_dt_max
   OR purchase_dt_max IS NULL;

Three things to notice about what the agent chose to do:

The ASIN filter is in the CTEs, not the final SELECT. Filtering at the CTE level is the difference between a query that scans a focused slice of the conversions table and one that scans everything and discards 99% of it. AMC's compute budget isn't infinite, and the Atlas playbook flagged this explicitly.

The OR purchase_dt_max IS NULL clause matters. Without it, the audience would only include people who previously bought and then abandoned again — missing every first-time prospect who added to cart and walked. The agent inherited this from the AMC IQ template, which spells out the join semantics.

The outer query is a plain SELECT user_id, not SELECT DISTINCT. AMC Audiences expects user IDs and de-duplicates internally. Adding DISTINCT doesn't help performance and occasionally trips the audience compiler.

The companion sizing query — run this first

Before the agent suggested pushing the audience to DSP, it produced a sizing check. This is the part most ad-hoc workflows skip, and it's why most "build me an audience" requests fail their first activation:

/* Audience sizing check — run this in the MAIN AMC query editor (not Audiences).
   Note the table name change: conversions_all, not conversions_for_audiences. */

SELECT COUNT(DISTINCT atc.user_id) AS audience_size
FROM (
  SELECT user_id, MAX(event_dt_utc) AS atc_dt_max
  FROM conversions_all
  WHERE event_subtype = 'shoppingCart'
    AND tracked_item IN ('B0XXXXXXX1','B0XXXXXXX2','B0XXXXXXX3')
  GROUP BY 1
) atc
LEFT JOIN (
  SELECT user_id, MAX(event_dt_utc) AS purchase_dt_max
  FROM conversions_all
  WHERE event_subtype = 'order'
    AND tracked_item IN ('B0XXXXXXX1','B0XXXXXXX2','B0XXXXXXX3')
  GROUP BY 1
) purchase
  ON atc.user_id = purchase.user_id
WHERE atc.atc_dt_max > purchase.purchase_dt_max
   OR purchase.purchase_dt_max IS NULL;

If this returns less than 500, the audience won't activate. If it returns more than 500,000, the audience won't refresh. The agent knows both thresholds and tells you to widen or tighten the ASIN list accordingly.

The footnotes the agent surfaced unprompted

This is the part that separates a retrieval-grounded agent from a fluent one. Without being asked, the agent included a short list of things the operator needed to know but didn't think to ask about:

Things Atlas surfaced that the operator didn't ask for:

• Seed size: 500 to 500,000. Outside that range, the audience either refuses to activate or refuses to refresh. Always run the sizing query first.
• DSP activation lag: ~48 hours. Build the audience two days before the campaign launch, not the day of.
• Sandbox limitations. AMC Sandbox doesn't populate certain event types reliably — including some Subscribe & Save signals. For audience work, run against production.
• Comment-line restriction. AMC Audiences queries cannot end on a comment line. Strip trailing -- annotations before pushing to audience creation, or the editor will reject the query.
• Upstream variants. Swap event_subtype = 'shoppingCart' for 'detailPageView' to reach further up the funnel, or for 'wishlist' to capture lower-intent interest. The agent will adjust the seed-size expectations accordingly.

Any one of these would have cost the operator an hour to discover. The compounding effect is why the workflow took ten minutes instead of an afternoon.

What happens next

The agent doesn't stop at SQL. Once the audience compiles and the sizing check passes, Atlas's activation playbook covers the next two hops: pushing the audience definition to AMC Audiences from the Audiences query editor, waiting for the activation window to close, and verifying the audience appears as targetable in Amazon DSP. The agent also flags that the audience is static at creation time — it doesn't auto-refresh as new users add to cart. For a retargeting program you'd want to run continuously, the agent recommends scheduling the audience as a recurring AMC workflow with a 7-day refresh cadence, and points to the corresponding Atlas playbook chunk on workflow scheduling.

That's the loop closed: ask, retrieve, build, size, activate, monitor.

Why this matters

A foundation model can write SQL. So can a junior analyst with a textbook. What neither can reliably do is produce a query that compiles against the specific table variants AMC exposes, respects the seed-size constraints DSP enforces, and bakes in the timing assumptions Amazon's attribution model requires — all without being told to.

Atlas isn't a magic upgrade to model capability. It's a corpus of Amazon's own playbook content, indexed and addressable by an agent at the moment of need. The agent doesn't have to remember any of this. It has to know where to look. That's a lower bar — and a much more reliable one.

If your agents are guessing at AMC, they don't have to be.

This is the first in a series on how agents grounded in Amazon Agent Atlas approach real Amazon Marketing Cloud workflows. Next: building a Subscribe & Save lift analysis that quantifies the spend gap between subscribers and one-off buyers — including the February 2024 repeatSnSOrder signal that most ungrounded models still don't know exists.