Map AMC Paths Across DSP and Sponsored Ads

Ask the agent in your AI client to map the customer journey, and the Amazon Ads MCP runs the path-to-conversion workflow live in your Amazon Marketing Cloud (AMC) instance: it joins dsp_impressions, sponsored_ads_traffic, and amazon_attributed_events_by_traffic_time on user_id, groups campaigns the way the current "Path to Conversion by Campaign Groups" pattern prescribes, and returns a Sankey-ready source-to-destination dataset. Amazon Agent Atlas keeps it honest by surfacing that pattern from the Customer Journey Analytics Playbook instead of letting the agent guess. The answer exists because our media lead asked in Slack: "What's the actual journey our customers take across demand-side platform (DSP), Sponsored Products, Sponsored Brands, and Sponsored Display before they convert?" Everyone had an opinion about which campaign deserved credit. Nobody had the path.

A plain ChatGPT or Claude chat hits the same three walls here. No access to your data: the journey lives in pseudonymized, instance-scoped AMC tables. No way to take action: a chat cannot submit an AMC workflow or re-pull it when attribution closes. Generic knowledge, not Amazon's: it does not know which journey query is current. That adds up to disconnected, generic, manual work that ships silent mistakes. The MCP brings your data plus the tools to act, Atlas the private rule book, Skills the repeatable run.

The whole journey lives in exactly three AMC tables, joined on user_id

The Customer Journey Analytics Playbook's "Tables used" list has exactly three entries: dsp_impressions, sponsored_ads_traffic, and amazon_attributed_events_by_traffic_time. Not one table per ad product. sponsored_ads_traffic carries the traffic events, impressions and clicks, for Sponsored Products, Sponsored Brands, Sponsored Display, and Sponsored Television in one table; you split it by ad_product_type ('sponsored_products', 'sponsored_brands', 'sponsored_display'). The third table holds the conversion events: purchases, new_to_brand_purchases, detail page views.

The join rule comes straight from Amazon's guidance: join on user_id when you are interested in understanding user behavior. Every branch of the query filters user_id IS NOT NULL so the paths stay user-level.

Group your campaigns, or aggregation thresholds swallow most of the path as NULL rows

The old "Path to Conversion by Campaign" instructional query (IQ) is retired. Amazon's notice reads: "As of August 31, 2022, an improved version of this IQ has been made available in AMC. We recommend advertisers to use the new version: Path to Conversion by Campaign Groups." The improvements are operator-facing: grouping reduces the likelihood of violating aggregation thresholds so "users will see a smaller number of NULL rows," campaign and Amazon Standard Identification Number (ASIN) filters exist, and Sponsored Display conversions are included.

The grouping mechanism is a small VALUES mapping: list each campaign with its group, then COALESCE everything ungrouped into 'DSP-Others', 'SP-Others', 'SD-Others', or 'SB-Others' so no row falls below the thresholds. (The playbook also ships a campaign-category variant that maps campaign names to funnel stages with CASE ... SIMILAR TO, but those categories depend on each advertiser's naming conventions.) Here is the workflow the agent submitted through the Amazon Ads MCP:

-- Path to conversion across DSP + sponsored ads, Sankey-ready output
-- Pattern: "Path to Conversion by Campaign Groups" IQ (supersedes the
-- "Path to Conversion by Campaign" IQ as of 2022-08-31)
WITH campaign_group (campaign, campaign_group) AS (
  VALUES
    -- placeholder campaign names/IDs; ungrouped rows fall back to
    -- '<product>-Others' so aggregation thresholds aren't violated
    ('111111111111111111', 'group 1'),
    ('222222222222222222', 'group 1'),
    ('SP_campaign', 'group 2'),
    ('SD_campaign', 'group 3'),
    ('SB_campaign', 'group 4')
),
impressions AS (
  SELECT
    COALESCE(g.campaign_group, 'DSP-Others') AS campaign_group,
    'DSP' AS product_type,
    i.user_id,
    MIN(i.impression_dt) AS impression_dt_first,
    MAX(i.impression_dt) AS impression_dt_last,
    SUM(i.impressions) AS impressions,
    SUM(i.total_cost) AS total_cost
  FROM dsp_impressions i
    LEFT JOIN campaign_group g ON g.campaign = i.campaign
  WHERE i.user_id IS NOT NULL
  GROUP BY 1, 2, 3
  UNION ALL
  SELECT
    COALESCE(g.campaign_group, 'SP-Others') AS campaign_group,
    'SP' AS product_type,
    a.user_id,
    MIN(a.event_dt) AS impression_dt_first,
    MAX(a.event_dt) AS impression_dt_last,
    SUM(a.impressions) AS impressions,
    SUM(a.spend) AS total_cost
  FROM sponsored_ads_traffic a
    LEFT JOIN campaign_group g ON g.campaign = a.campaign
  WHERE a.user_id IS NOT NULL
    AND a.ad_product_type = 'sponsored_products'
  GROUP BY 1, 2, 3
  UNION ALL
  SELECT
    COALESCE(g.campaign_group, 'SD-Others') AS campaign_group,
    'SD' AS product_type,
    a.user_id,
    MIN(a.event_dt) AS impression_dt_first,
    MAX(a.event_dt) AS impression_dt_last,
    SUM(a.impressions) AS impressions,
    SUM(a.spend) AS total_cost
  FROM sponsored_ads_traffic a
    LEFT JOIN campaign_group g ON g.campaign = a.campaign
  WHERE a.user_id IS NOT NULL
    AND a.ad_product_type = 'sponsored_display'
  GROUP BY 1, 2, 3
  UNION ALL
  SELECT
    COALESCE(g.campaign_group, 'SB-Others') AS campaign_group,
    'SB' AS product_type,
    a.user_id,
    MIN(a.event_dt) AS impression_dt_first,
    MAX(a.event_dt) AS impression_dt_last,
    SUM(a.impressions) AS impressions,
    SUM(a.spend) AS total_cost
  FROM sponsored_ads_traffic a
    LEFT JOIN campaign_group g ON g.campaign = a.campaign
  WHERE a.user_id IS NOT NULL
    AND a.ad_product_type = 'sponsored_brands'
  GROUP BY 1, 2, 3
),
converted AS (
  -- conversions attributed to in-window traffic; events can land up to
  -- 30 days after the window, so totals move until attribution closes
  SELECT
    user_id,
    SUM(conversions) AS conversions
  FROM amazon_attributed_events_by_traffic_time
  WHERE user_id IS NOT NULL
  GROUP BY 1
),
ranked AS (
  SELECT
    user_id,
    campaign_group,
    ROW_NUMBER() OVER (
      PARTITION BY user_id
      ORDER BY impression_dt_first
    ) AS path_rank
  FROM impressions
),
steps AS (
  -- explode the path into source -> destination pairs: QuickSight's
  -- Sankey visual needs one source and one destination dimension per row
  SELECT
    r1.user_id,
    r1.campaign_group AS path_step_source,
    r2.campaign_group AS path_step_destination
  FROM ranked r1
    LEFT JOIN ranked r2
      ON r2.user_id = r1.user_id
      AND r2.path_rank = r1.path_rank + 1
)
SELECT
  s.path_step_source,
  s.path_step_destination,
  COUNT(DISTINCT s.user_id) AS path_occurrences,
  SUM(c.conversions) AS conversions
FROM steps s
  LEFT JOIN converted c ON c.user_id = s.user_id
GROUP BY 1, 2
-- NO ORDER BY here: AMC rejects ORDER BY in the outer query of a
-- workflow ("ORDER BY unexpected"). Sort downstream, post-export.

Swap the placeholder VALUES rows for your own campaign names or IDs and the rest runs as-is.

Conversions keep landing for 30 days after your window, so day-zero numbers are not final

amazon_attributed_events_by_traffic_time has a property that surprises people: the traffic events all sit inside your query window, but the attributed conversions "may have occurred up to 30 days after the time window." Amazon extends the conversion range automatically and says plainly that "the output of a workflow that uses this table may change over time." The lookback itself is fixed per campaign and cannot be changed in AMC: 14 days for Brands, 7 days for Sellers' Sponsored Products.

I was skeptical when the agent flagged this, so we re-pulled the identical workflow later in the attribution window. The conversion column grew, and nothing was wrong; attribution was still filling in. Run the analysis the morning after your window closes and you under-count whatever is still inside that 14-day lookback, which is why the agent derives a safe re-pull date from the lookback rather than treating day-zero output as final.

One eligibility wrinkle from the Custom Attribution Overview is worth knowing too: DSP conversions require a viewable impression and Sponsored Products require a click, so impression-only SP exposures never appear in the attributed datasets at all.

Sankey-ready means source-to-destination rows, and the agent returns the dataset already in that shape

Amazon's path output arrives as ranked arrays, paths like [[1, DSP-Display], [2, DSP-Video], [3, BSI], [4, SB-Others]] with path_occurrences and impressions alongside. QuickSight cannot chart that directly. The playbook is explicit: the "Sankey diagram is a visual type within Amazon QuickSight and requires a one dimension in source and one dimension in destination." A four-element path "will 'explode into' 3 separate steps/rows," metrics repeated on every exploded row; the playbook does this in a notebook ending with df_final.to_csv('Sankey-Diagram_Input.csv', index=False).

The query above skips the notebook step. The ROW_NUMBER self-join in the steps CTE emits the exploded pairs directly, one row per hop, ordered by first impression time. A user's terminal step carries a NULL destination (single-exposure users keep one row); label that terminal node from the converted join and the diagram shows where each journey ends. Read the conversions column as the conversion total of users who traversed that hop, not as credit attributed to the hop itself.

What happens next

Reading the diagram is the fast part. The playbook's worked example charts the top 20 paths by purchase, with high-purchase users usually exposed to loyalty and consideration campaign categories and awareness overlap comparatively lower. The move: reuse what the high-overlap groups share (impression frequency, cost, line items) in the next campaign, and seed lookalikes from those audiences. One row to ignore: the "none" exposure group, purchasers with no impression recorded under their user_id; Amazon says to skip it.

From there I have the agent re-run the workflow as a recurring Skill, timed past attribution close, so each month's diagram compares settled numbers to settled numbers.

When the fixed 14-day window is the wrong lens, the custom attribution IQs (First Touch, Last Touch, Linear, Position Based) extend the lookback to 28 days and re-credit the same journey; that choice is its own guide: choosing a custom attribution model in AMC. The path query is one surface of Amazon Agent Flow, the Amazon Agent Data layer connecting the Amazon Ads MCP, the Selling Partner MCP, Atlas, and Skills into end-to-end workflows.

The pattern is the one this series keeps landing on: the agent runs Amazon's current query in your instance, grounded in Amazon's own playbook, and returns an artifact you can act on the same day. The journey was always in your data. The walls were in the chat.

Next up: how much your DSP and sponsored ads audiences actually overlap, and what that says about incrementality, in the four-way sponsored ads and DSP overlap analysis.