Sponsored Ads x DSP Overlap: The 4-Way AMC Analysis

{"workflow":"sponsored_ads_dsp_overlap","guardrails":{"min_days_after_query_end":14,"min_co_running_days":7,"same_product_period_required":true},"output":{"format":"table","grain":"exposure_group"}}

The Slack question was blunt: "When a shopper sees both our demand-side platform (DSP) ads and our Sponsored Products ads, does the combination actually move purchase rate, or are we paying twice for the same conversion?"

That is not a campaign-reporting question. Standard reports can show spend, clicks, impressions, and attributed sales by product line. They cannot tell you whether exposure to multiple ad products changes the purchase rate versus exposure to one. The answer lives in Amazon Marketing Cloud (AMC), but the wrong query gives a very confident wrong answer. I handed the question to our agent, backed by Amazon Agent Atlas, and asked it to build the overlap analysis before we made a budget call.

The decision matrix it used before writing SQL:

For this operator question, the right artifact is Analyzing Sponsored Ads and DSP Overlap, run through the Amazon Ads MCP so the agent can keep the table names, filters, and waiting rules intact.

What a model without Atlas gets wrong

Four silent failure modes showed up in the un-grounded version:

1. It used the older Sponsored Display and DSP overlap query even though the newer Sponsored Ads and DSP overlap IQ supersedes it for broader full-funnel analysis.
2. It treated Sponsored Products exposure as clicks. The IQ says exposure means impressions, including Sponsored Products impressions.
3. It skipped the 14-day attribution-close rule and produced a report that would undercount conversions if run immediately after the media window.
4. It compared campaigns that had not run for the same products in the same period. That makes the overlap group look like a performance difference when it is really a campaign-design difference.

None of those mistakes has to fail at runtime. The SQL can compile. The chart can look clean. The budget call that follows can still be wrong.

What Atlas retrieves

Atlas pulled the relevant chunks before the agent wrote the query:

• Analyzing Sponsored Ads and DSP Overlap: the improved instructional query. It states that the IQ measures 2-way, 3-way, and 4-way overlap and is an improved version of the older Sponsored Display and DSP overlap query.
• Sponsored Products and DSP Display Overlap Analysis: the narrower prior IQ. Atlas surfaced it as a contrast case so the agent could avoid using the two-product template for a broader Sponsored Ads question.
• Joining and Unioning Sponsored Ads Traffic with Conversions: the join pattern that points overlap queries at SPONSORED_ADS_TRAFFIC and AMAZON_ATTRIBUTED_EVENTS_BY_TRAFFIC_TIME through user_id.
• Creating Audiences from Clicked Sponsored Ads Without Purchases: the adjacent audience workflow. It reminded the agent that overlap measurement can become an activation workflow later, but measurement comes first.
• Analyzing the Overlap of Amazon DSP Display, Streaming TV, and Sponsored Products: the three-way precedent. It has the same one-week co-running and 14-day waiting logic, which is useful when the operator expands from two products to a larger full-funnel plan.

The important thing is not just that Atlas found a query. It found the older query, the improved query, the join pattern, and the timing constraints together. That bundle is what keeps an agent from solving the wrong version of the problem.

The agent's working output

The agent returned the AMC SQL as a measurement query, not as an audience. I kept the campaign filters in place but commented, matching the IQ style. Run it unfiltered first to validate the exposure groups, then narrow it to the campaign IDs you actually want to compare.

-- Instructional Query: Sponsored Ads and DSP Overlap
-- Use after all compared ad products ran for at least one week
-- in the same period. Wait 14 full days after the query end date.
WITH dsp_campaigns (campaign_id_string) AS (
  VALUES
    ('1111111111111'),
    ('2222222222222')
),
sa_campaigns (campaign_id_string) AS (
  VALUES
    ('3333333333333'),
    ('4444444444444')
),
impressions_cte AS (
  SELECT
    user_id,
    ARRAY_SORT(COLLECT(DISTINCT ad_product_type)) AS exposure_group,
    MIN(impression_dt) AS min_impression_dt,
    SUM(impressions) AS impressions
  FROM (
    SELECT
      i.user_id,
      'DSP' AS ad_product_type,
      i.impressions,
      i.impression_dt
    FROM dsp_impressions i
    /* Optional DSP filter:
    WHERE i.campaign_id_string IN (
      SELECT campaign_id_string FROM dsp_campaigns
    )
    */

    UNION ALL

    SELECT
      i.user_id,
      i.ad_product_type,
      i.impressions,
      i.event_dt AS impression_dt
    FROM sponsored_ads_traffic i
    /* Optional Sponsored Ads filter:
    WHERE i.campaign_id_string IN (
      SELECT campaign_id_string FROM sa_campaigns
    )
    */
  )
  GROUP BY 1
),
reach_by_group AS (
  SELECT
    exposure_group,
    COUNT(DISTINCT user_id) AS ad_exposed_users
  FROM impressions_cte
  GROUP BY 1
),
purchases_by_group AS (
  SELECT
    i.exposure_group,
    COUNT(DISTINCT p.user_id) AS users_purchased,
    SUM(p.total_purchases) AS total_purchases,
    SUM(p.total_product_sales) AS total_product_sales
  FROM amazon_attributed_events_by_traffic_time p
  INNER JOIN impressions_cte i
    ON i.user_id = p.user_id
  WHERE
    p.total_purchases > 0
    AND p.conversion_event_dt > i.min_impression_dt
    /* Optional matched campaign filter:
    AND (
      p.campaign_id_string IN (SELECT campaign_id_string FROM dsp_campaigns)
      OR p.campaign_id_string IN (SELECT campaign_id_string FROM sa_campaigns)
    )
    */
  GROUP BY 1
)
SELECT
  r.exposure_group,
  r.ad_exposed_users AS unique_reach,
  COALESCE(p.users_purchased, 0) AS users_that_purchased,
  COALESCE(p.total_purchases, 0) AS total_purchases,
  COALESCE(p.total_product_sales, 0) AS total_product_sales,
  COALESCE(p.users_purchased, 0) * 1.0
    / NULLIF(r.ad_exposed_users, 0) AS purchase_rate
FROM reach_by_group r
LEFT JOIN purchases_by_group p
  ON r.exposure_group = p.exposure_group
ORDER BY purchase_rate DESC;

The output is a table of exposure groups. A row might be ['DSP'], ['sponsored_products'], or ['DSP','sponsored_products'], depending on what the shopper saw during the window. The comparison that matters is not total sales. It is purchase rate by exposure group after the same products, same period, and attribution-close rules are satisfied.

The ARRAY_SORT(COLLECT(DISTINCT ad_product_type)) choice matters. Without sorting and distinct collection, the same set of exposures can appear as different labels depending on event order. The agent grouped the exposures as a set, not as a sequence. If you need sequence, that is a different guide: the path-to-conversion sankey.

The conversion join uses AMAZON_ATTRIBUTED_EVENTS_BY_TRAFFIC_TIME because this is an exposure-window analysis. The query asks whether users who saw an ad product later purchased, so the conversion event has to occur after the user's minimum impression timestamp. That conversion_event_dt > min_impression_dt predicate is the line that turns a raw join into a causal sanity check.

The footnotes the agent surfaced

Five caveats the agent brought back before I asked:

1. The improved Sponsored Ads and DSP overlap IQ is not the same as the older Sponsored Display and DSP overlap IQ. Use the improved one when Sponsored Products or Sponsored Brands are part of the comparison.
2. Sponsored Products exposure in this IQ means impressions. If the team expects click-only logic, say that before anyone interprets the result.
3. Every ad type in the comparison should advertise the same products during the same period and run for at least one week. Otherwise the exposure groups are not comparable.
4. Wait at least 14 full days after the query end date. Running earlier makes the multi-exposure group look weaker because conversions have not fully closed.
5. The result is set overlap, not touch order. If the business question is "which touch came first," use the path-to-conversion workflow instead.

Those are the guardrails that make the chart reviewable. A generic model can write a join. The hard part is knowing which join should not be trusted yet.

What happens next

Run the unfiltered query once to see whether the overlap groups exist at a usable volume. If the ['DSP','sponsored_products'] row is tiny, do not make a purchase-rate call yet. Extend the window, widen the campaign set, or accept that the campaigns did not create enough overlap to measure.

If the overlap group is large enough, package the query as a recurring Skill with an approval gate. The agent should check the three preconditions before each run: same products, at least one week of co-running media, and 14 full days since query end date. If one fails, the Skill should report "not ready" instead of sending a chart to Slack.

The budget action comes after review. If the combined exposure group has materially higher purchase rate than either single-exposure group, the DSP and Sponsored Ads plan is doing real full-funnel work. If the combined group is flat and reach is duplicated, move budget toward the ad product that drives incremental reach or test new audience definitions before increasing spend.

The point

Overlap analysis is only useful when the setup rules are as visible as the result.

Next up: use the same exposure groups as inputs to a path-to-conversion sankey so the agent can separate overlap from sequence.