Counting New-to-Brand Customers in AMC Without Double-Counting the Same Buyer

1. The question

A Slack from our paid team last Tuesday: "Of all the customers our campaigns reached who actually bought, how many were buying our brand for the first time?"

The honest answer is: nobody at the agency knows, the Amazon Ads console gives a partial number that excludes Amazon's demand-side platform (DSP), and ChatGPT will hand back a SQL query that compiles and counts the wrong table. The right answer lives in three Amazon Marketing Cloud (AMC) instructional queries plus a schema doc, none of which a model has cleanly in its training data. I handed the question to our agent, backed by Amazon Agent Atlas.

The matrix the agent grounded in before writing a line of SQL:

The IQ behind the answer is New to brand customers.

2. What a model without Atlas gets wrong

Four failure modes I watched in a single un-grounded draft of this query:

1. It picked amazon_attributed_events_by_traffic_time because the name sounds neutral. That table re-extends the conversion window by up to 30 days after the query, so the output changes after the workflow runs. A recurring NTB report pinned to it drifts.
2. It forgot purchases > 0 and counted every user the campaign reached, not just buyers. The denominator was wrong and so was the percentage.
3. It put SELECT user_id in the final output and the query ran but the column came back blocked. user_id carries an aggregation threshold and can only live inside a CTE that aggregates it away.
4. It confused the New-to-brand customers IQ with the New-to-brand purchases IQ. They use the same table but different denominators. The first counts distinct users; the second counts orders. The model swapped them and the answer to "how many first-time buyers did we acquire" came back as a purchase count inflated by repeat NTB orders.

None of these failures throw an error. The query runs. The number is wrong.

3. What Atlas retrieves

The agent didn't write SQL from training data. It pulled five chunks from Atlas and grounded the query in them:

• The New to brand customers instructional query: the canonical AMC IQ for this exact operator question, including the requirement language ("ASINs must be tracked to campaigns") and the policy window ("previous 365 day period").
• The Amazon Attributed Events Overview schema doc: defines the new_to_brand boolean column and the stability difference between amazon_attributed_events_by_conversion_time and the traffic-time variant. This is the source for the "use conversion_time for recurring workflows" rule. The table is wired through the Amazon Ads MCP, so the agent could resolve the column without resorting to a live web fetch via Amazon Agent Crawl.
• The New to brand purchases analysis IQ, adjacent to the customer IQ and the one a model would otherwise confuse with this one. Atlas surfaced it so the agent could write Section 8 distinguishing the two.
• The Understanding New-to-Brand ASINs on Amazon IQ, the natural follow-up. Once the operator knows the portfolio-level NTB%, the question becomes "which ASINs are the front door for NTB acquisition."
• The Data aggregation thresholds in AMC reference: the privacy-safeguard doc that explains why user_id SELECTs get blocked at a final-output grain.

4. The agent's working output

The SQL the agent returned, copy-pasteable into the AMC query editor:

-- New-to-brand customers: count and percentage by campaign.
-- Source IQ: New to brand customers (AMC instructional query).
-- NTB lookback: 365 days (Amazon-managed; not parameterizable in this IQ).
-- Run on the analytics table, NOT the for_audiences variant.
WITH ad_buyers AS (
  SELECT
    advertiser,
    campaign,
    user_id,
    MAX(new_to_brand) AS new_to_brand_flag
  FROM amazon_attributed_events_by_conversion_time
  WHERE purchases > 0
  GROUP BY 1, 2, 3
),
ntb_only AS (
  SELECT user_id, advertiser, campaign
  FROM ad_buyers
  WHERE new_to_brand_flag = TRUE
)
SELECT
  b.advertiser,
  b.campaign,
  COUNT(DISTINCT b.user_id) AS users_that_purchased,
  COUNT(DISTINCT n.user_id) AS ntb_users_that_purchased,
  COUNT(DISTINCT n.user_id) * 1.0
    / NULLIF(COUNT(DISTINCT b.user_id), 0) AS ntb_users_percentage
FROM ad_buyers b
LEFT JOIN ntb_only n
  ON  b.user_id    = n.user_id
  AND b.advertiser = n.advertiser
  AND b.campaign   = n.campaign
GROUP BY 1, 2
ORDER BY ntb_users_percentage DESC

Three decisions in this query would otherwise cost an operator an afternoon. The table choice is amazon_attributed_events_by_conversion_time, not the traffic-time variant. The Atlas chunk for Amazon Attributed Events Overview is explicit: traffic-time will re-extend the conversion window by up to 30 days after the query runs, so a recurring NTB report pinned to it produces a different answer each time. Conversion-time is the only safe table for a recurring measurement workflow.

The WITH CTE exists because user_id carries an aggregation threshold. AMC will block any final SELECT that tries to expose user_id directly. The CTE collapses each user into a single row tagged with their NTB flag, and the outer SELECT operates only on COUNT DISTINCT. That is legal; it is also why the IQ template uses a CTE rather than a single-pass SELECT.

The grouping is by campaign, not by advertiser alone. A portfolio-level NTB% hides the campaigns doing the actual acquisition work. If three campaigns are running and one is a loyalty-retargeting campaign with deliberately low NTB%, the aggregate percentage drops and looks like a problem. Grouping by campaign separates the loyalty workload from the acquisition workload so each campaign gets judged on its own goal.

5. The footnotes the agent surfaced

Five things the agent surfaced unprompted, the ones an un-grounded model wouldn't include because it didn't know to:

1. Amazon's docs phrase the NTB window two ways: the IQ chunk says "previous 365 day period" and the events table schema doc says "previous 12 months." Same definition, different wording. The IQ language is the authoritative one for this query; surface the inconsistency to the reader so nobody chases a ghost.
2. The pixel-only path is a different query. Advertisers without a promoted ASIN that resulted in an ad-attributed purchase cannot run this IQ at all. The Atlas retrieval surfaced the amazon_retail_purchases NTB gateway IQ as the alternative, with a custom-lookback parameter (1095 days = 3 years is a common pick).
3. The DSP NTB count includes only promoted-ASIN purchases. Sponsored Ads NTB also includes Brand Halo (related ASINs). A mixed-product portfolio measured by this IQ under-counts NTB on the DSP side relative to Sponsored Ads. The contrast with the custom-attribution data sources is sharper here than the IQ doc admits.
4. The column is tracked_asin. The IQ doc prose refers to "promoted ASIN." Same thing. Operators who grep the table schema for promoted_asin come up empty and assume the data isn't there.
5. If a campaign was deliberately set up as existing-only (a Subscribe & Save win-back, for example), NTB% will be near zero. That is correct, not a bug. The IQ explicitly requires campaigns to "target both new and existing customers" for the result to be interpretable, and an existing-only campaign violates that requirement on purpose.

6. What happens next

The single-run query is the first step. The repeatable workflow is what the operator actually wanted.

Activate this query as a recurring Skill on a weekly cadence. Push the per-campaign NTB% to a BI dashboard so brand managers can read the trend without re-running SQL. Wire the underlying retrieval through the Amazon Ads MCP so the agent surfaces the same context to a human reviewer mid-month that it had at compose time.

The rule that fires off this measurement: campaigns below the brand's target NTB% get re-allocated toward upper-funnel placements (DSP awareness inventory, Sponsored Display NTB audiences). Campaigns above target hold steady. Campaigns dramatically below target with no upper-funnel exposure are the strongest signal that the budget is being spent on existing customers who would have purchased anyway.

The natural follow-on is the per-ASIN NTB breakdown. The portfolio number tells you whether acquisition is happening. The ASIN-level number tells you which products are doing the work.

7. The point

If your acquisition number is just "total buyers," you are not measuring acquisition.

Next up: the per-ASIN NTB gateway analysis, which ASINs are the front door for new-customer purchases, and how to find them in the path-to-conversion data.