Finding the Three-Time Buyers Who've Gone Quiet: An AMC Audience for Lapsed Loyalists

The Slack message landed during a retention review: "We have customers who bought from us three or more times last year, and we haven't heard from them in 90 days. Can we get them into a DSP campaign before they churn for good?"

It sounds like a five-minute audience build. In practice the trap is in the tables, the lookback, and the size floor. AMC Audiences uses a different set of tables than the main query editor. The lookback you actually want is a 180-day rolling window, not a calendar quarter. The audience won't activate at all if it resolves to fewer than 2,000 distinct users, and you find that out only after pushing it. So I asked our agent, which has Amazon Agent Atlas behind it. Here is what came back.

What a model without Atlas gets wrong

I ran the same prompt through a frontier model with no retrieval. The response looked workable. It wasn't.

Four failure modes in a single un-grounded response:

1. It pulled from conversions_all. The Audiences editor only accepts the _for_audiences variants for SELECT user_id queries. The right table for this workflow is conversions_all_for_audiences, and pasting the un-suffixed name produces a "user_id not selectable" rejection in the AMC Audiences UI.
2. It built the 180-day lookback by hardcoding event_dt_utc >= CURRENT_DATE - INTERVAL '180 day'. The Atlas playbook uses TABLE(EXTEND_TIME_WINDOW('conversions_all_for_audiences', 'P180D', 'P0D')), which is the only construct that reaches outside the AMC Audiences query editor's default analysis window. Without it, your "180-day lookback" is silently truncated.
3. It dropped the exposure_type = 'non-ad-exposed' filter. The lapsed-loyalist audience is the one Atlas explicitly recommends building from organic repeat buyers, customers who came back on their own. Without the filter, the audience scoops up everyone you already retargeted, which is exactly the population you don't need to spend on.
4. It didn't mention the 2,000-user activation floor. AMC Audiences refuses to push an audience smaller than 2,000 distinct users to Amazon DSP. The query returns no error in that case; the audience appears to compile, then quietly fails to land in DSP. You discover the floor by missing a campaign launch.

Every one of these would have produced a query that runs without complaint and an audience that never activates. The combination would have eaten a week of debugging.

What Atlas retrieves

When the agent gets the question, it does a semantic search across the amazon_ads collection and pulls a handful of chunks before writing any SQL:

• The Flexible Shopping Insights Trial Guide, section 4.2 "Engage lapsed customers": the canonical AMC template for exactly this workflow, complete with the EXTEND_TIME_WINDOW lookback, the conv_cnt > 2 filter on repeat purchases, and the BUILT_IN_PARAMETER('TIME_WINDOW_START') exclusion that defines "haven't bought recently."
• The AMC Audiences table-variant rule, indexed from "Creating an Audience for Wishlist or Registry Additions": why conversions_all_for_audiences is the only table that permits SELECT user_id, and the suffix pattern that distinguishes Audiences-editor tables from main-editor tables.
• The Engage non-ad-exposed audience section of the same FSI Trial Guide: the sister query in section 4.1 that establishes the count(user_id) sizing trick, run in the main editor against the un-suffixed table, before any audience is pushed.
• The Introduction to AMC Lookalike Audiences playbook: the downstream activation path if the seed turns out to be too small to use directly, with the 500-to-500,000 lookalike seed constraint and the model-expansion behavior.

Atlas doesn't generate the SQL. It surfaces the playbook with its constraints intact, and the agent adapts the template to the operator's hero ASINs and window.

The agent's output

Here is the audience query the agent produced. It pastes directly into the AMC Audiences query editor, uses the 180-day extended window, and filters to customers with three or more organic purchases who have gone silent inside the current analysis window:

-- Audience Instructional Query: Re-engage lapsed three-time buyers
-- Source: AMC FSI Trial Guide, section 4.2 "Engage lapsed customers"
-- Run in: AMC Audiences query editor (not the main editor)
-- Table:  conversions_all_for_audiences (_for_audiences variant required)

WITH purchase AS (
  -- Count distinct organic conversions per user over the trailing 180 days,
  -- and capture each user's most recent purchase date.
  SELECT
    user_id,
    COUNT(DISTINCT conversion_id) AS conv_cnt,
    MAX(event_date_utc)           AS event_max
  FROM TABLE(EXTEND_TIME_WINDOW('conversions_all_for_audiences', 'P180D', 'P0D'))
  WHERE event_subtype = 'order'
    AND exposure_type = 'non-ad-exposed'
    AND user_id IS NOT NULL
  GROUP BY user_id
),
multiple_purchase AS (
  -- Keep only the customers with three or more organic orders in the lookback.
  SELECT user_id, event_max
  FROM purchase
  WHERE conv_cnt > 2
)
-- Final seed: three-plus buyers whose most recent order predates the analysis
-- window. With a 90-day window, that means no purchase in the last 90 days.
SELECT user_id
FROM multiple_purchase
WHERE user_id NOT IN (
  SELECT user_id
  FROM multiple_purchase
  WHERE event_max > BUILT_IN_PARAMETER('TIME_WINDOW_START')
)

Three things to notice about what the agent chose to do. First, the lookback uses EXTEND_TIME_WINDOW('conversions_all_for_audiences', 'P180D', 'P0D'), not a hand-rolled date filter. Inside the AMC Audiences editor, the default analysis window is shorter than the 180 days this audience needs, and EXTEND_TIME_WINDOW is the only mechanism that opens it. The P180D is an ISO-8601 duration; P0D means the window ends at the current analysis-window boundary. The agent inherited this directly from the FSI playbook.

The exposure_type = 'non-ad-exposed' filter is the entire point of the audience. The retention lead wanted organic loyalists, the customers who came back without seeing ads, because those are the ones whose silence is a churn signal rather than ad fatigue. Without the filter, the seed is contaminated with customers you've already retargeted, and the DSP campaign you build from it overlaps with campaigns you're already running.

Finally, the lapsed condition is expressed as a NOT IN against the same multiple_purchase CTE, gated on BUILT_IN_PARAMETER('TIME_WINDOW_START'). That parameter resolves to the start of the analysis window the operator sets in the editor, so a 90-day analysis window automatically defines "lapsed" as "no order in the last 90 days." Change the window in the editor, and the lapsed definition shifts with it. No code edits required.

The footnotes the agent surfaced unprompted

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

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

• Size the audience before you push it. AMC Audiences refuses to activate any audience under 2,000 distinct users. Run the query in the main editor first with user_id swapped for count(user_id) and the _for_audiences suffix removed from the table name; that returns the seed size without creating anything.
• Loosen the conditions when the floor isn't met. The FSI playbook calls out the relaxation order explicitly: change conv_cnt > 2 to conv_cnt > 1 first (two-plus buyers instead of three-plus), then extend the lookback from 180 to 270 days. Both are documented adjustments, not hacks.
• Don't end the query on a comment line. AMC's audience editor rejects any submission whose last non-blank line is a -- comment. Strip trailing annotations before pasting, or the editor will swallow the query without a useful error.
• event_subtype = 'order' is required. conversions_all_for_audiences carries every conversion subtype Amazon tracks, not just purchases. Without the 'order' filter, detail-page views, wishlist adds, and Subscribe & Save subscription events count toward the conv_cnt and inflate the seed with users who never actually bought.
• Flexible Shopping Insights is a paid feature. The exposure_type column and the non-ad-exposed signal only populate when FSI is enabled on the AMC instance. Without it, the filter returns zero rows and the audience appears empty for reasons the editor will not surface.

Any one of these would have cost the operator a half-day to discover. The compounding effect is why this kind of audience normally takes a week to ship and not an afternoon.

What happens next

Once the seed sizes above 2,000 users, the agent flips into activation mode and routes the audience through the standard AMC Audiences-to-DSP path: paste the query into the Audiences editor, push it to a named audience, wait for the activation window to close, and verify the audience appears as targetable in Amazon DSP. If the seed comes back undersized even after both relaxations, the agent recommends pivoting to AMC Lookalike Audiences, using the lapsed loyalists as a seed (even a small one) and letting the lookalike model expand it. That path uses the same workflow we walked through for cart abandoners, but with the loyalist seed in place of the cart-abandoner seed and a 500-to-500,000 lookalike size band instead of the 2,000-user activation floor. Either way, the agent doesn't stop at SQL: it carries the audience to the activation handoff and tells you which path it took and why.

Why this matters

The lapsed-loyalist workflow is the kind of audience build that is easy to ask for, hard to ship, and impossible to debug after the fact. The query has to use the right table variant, the right time-window construct, the right exposure filter, and the right size floor; miss any one of them and the audience either fails to compile, fails to activate, or activates against the wrong population. None of this is exotic knowledge, but it lives in four different sections of two different playbooks, and a model without retrieval has to guess at all of it.

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

Part of an ongoing series on how agents grounded in Amazon Agent Atlas approach real AMC workflows. Next: turning a too-small loyalist seed into an activated audience through AMC Lookalike Audiences, and the rules the lookalike model uses to expand a seed without diluting it.