# Amazon Marketing Cloud Path to Conversion: Sankey

Canonical page: https://www.kuudo.com/guides/amc-path-to-conversion-sankey/

Map an Amazon Marketing Cloud path to conversion with campaign groups, aligned lookforward windows, and Sankey-ready source-to-destination rows.

Author: Thomas Spicer
Updated: Oct 19, 2026

## Summary

The journey needs exactly 3 Amazon Marketing Cloud (AMC) tables joined on `user_id`: `dsp_impressions`, `sponsored_ads_traffic`, and `amazon_attributed_events_by_traffic_time`. Use the Campaign Groups instructional query (IQ), because the by-campaign IQ was superseded on 2022-08-31 and ungrouped paths are more likely to return NULL rows under aggregation thresholds. The output explodes each ranked path into source-to-destination rows for Amazon QuickSight Sankey charts.

Amazon Marketing Cloud (AMC) path to conversion analysis reconstructs ordered DSP and Sponsored Ads exposures, groups campaigns before privacy aggregation erases sparse steps, and returns source-to-destination rows ready for a Sankey chart. The workflow also preserves the conversion lookforward window, so an early visualization is not mistaken for a final performance read.

Ask the agent in [your AI client](/features/ai-clients/) to map the customer journey, and the [Amazon Ads MCP](/features/amazon-ads-mcp/) runs the path-to-conversion workflow live in your [Amazon Marketing Cloud](/features/amc/) (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](/features/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.

### Keep the path analysis tied to your account

[ChatGPT, Claude, Perplexity, Microsoft Copilot, and whatever comes next](/features/ai-clients/) know nothing about your business out of the box. Your competitors use those tools too. Kuudo gives those same tools your edge: **your data**, **your rules**, and **the way your business operates**. That means **your account as it is right now**, **your judgment running every time**, and **your call before anything changes**.

The [Amazon Ads MCP](/features/amazon-ads-mcp/) reads and tests against your authorized instance, [Skills](/features/skills/) preserve the rules your team expects, [Amazon Agent Atlas](/features/agent-atlas/) supplies Amazon-specific guidance, and approval controls keep activation and other consequential changes with you.

## 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.

> **The AI is shared. The moat is yours.**
> - **Your judgment, running every time.** Ask the shared AI for the journey SQL and it invents per-product tables, an `sp_impressions` here, an `sb_traffic` there, that do not exist in AMC. All sponsored ads traffic lives in one table split by `ad_product_type`; the agent reads the real schema through the MCP before writing a line.
> - **Your account, as it is right now.** Even with correct table names, the joined journey only exists inside your AMC instance after the query runs there. You cannot paste `user_id`-level impression rows into a chat, so the chat never sees a single path.
> - **Your judgment, running every time.** The classic miss is joining the two traffic tables and stopping. Without `amazon_attributed_events_by_traffic_time` there is no conversion endpoint, only exposure. Atlas surfaces the exact three-table list, so the query lands complete.

## 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:

```sql
-- 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.

> **The AI is shared. The moat is yours.**
> - **Your judgment, running every time.** A chat reproduces the deprecated by-campaign pattern from stale training data. Run campaign-level paths in an instance of any real size and the aggregation thresholds NULL the small rows, exactly what the 2022 notice says grouping fixes.
> - **Your call, before anything changes.** The chat hands you SQL to carry by hand. The first hand-carried draft of this exact analysis died in AMC's parser with "ORDER BY unexpected" because it sorted in the outer query. The agent submits through the MCP, sees the parser's verdict immediately, and corrects the workflow; the version above sorts downstream, after export.

## 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.

> **The AI is shared. The moat is yours.**
> - **Your judgment, running every time.** The shared AI treats your query window as the whole story. It will not warn you that the same by_traffic_time workflow returns different numbers tomorrow, or that a path analysis run too early under-counts conversions still in flight.
> - **Your call, before anything changes.** A chat cannot wait out the attribution window and re-pull. You rerun by hand, if you remember to. The agent schedules the re-run for after attribution closes and compares the two outputs side by side.

## 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.

> **The AI is shared. The moat is yours.**
> - **Your judgment, running every time.** Ask a plain chat for "path to conversion" and you get a flat table, one row per campaign. No source/destination pairs means nothing QuickSight's Sankey visual can ingest.
> - **Your call, before anything changes.** Even when the shared AI explains the explode correctly, the chat cannot apply it to results it never had. You re-shape rows by hand in a spreadsheet; the agent hands back rows that already are the chart's input.

## What happens next: review the paths before changing spend {#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](/features/skills/), 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](/guides/amc-custom-attribution-models/). The path query is one surface of [Amazon Agent Flow](/features/amazon-agent-flow/), the Amazon Agent Data layer connecting the Amazon Ads MCP, the [Selling Partner MCP](/features/amazon-selling-partner-mcp/), Atlas, and Skills into end-to-end workflows.

### Reuse the reviewed path analysis

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](/guides/amc-sponsored-ads-dsp-overlap-4way/).*


## Frequently asked questions

### Which AMC tables do I need for a path-to-conversion analysis?

Three: dsp_impressions for DSP exposure, sponsored_ads_traffic for Sponsored Products, Brands, and Display events, and amazon_attributed_events_by_traffic_time for the conversions, joined on user_id.

### Why do my path-to-conversion rows fail or come back NULL?

Ungrouped campaign-level paths violate AMC's aggregation thresholds. Group campaigns with the campaign_group CTE; Amazon's 2022 Campaign Groups IQ exists specifically because grouping reduces NULL rows.

### Do Sponsored Products, Sponsored Brands, and Sponsored Display each have their own traffic table?

No. All sponsored ads traffic lives in sponsored_ads_traffic; split it by ad_product_type ('sponsored_products', 'sponsored_brands', 'sponsored_display').

### Why do my conversion numbers change when I rerun the same query?

amazon_attributed_events_by_traffic_time automatically extends the conversion window up to 30 days after your query window, so a workflow's output changes until attribution closes.

### How do I turn the AMC path output into a Sankey diagram?

Explode each ranked path into source-to-destination step rows (a 4-element path becomes 3 rows), then load the result into Amazon QuickSight's Sankey visual, which requires one source dimension and one destination dimension.

### Do I need a paid AMC subscription for this analysis?

No. It uses three standard tables; conversions_all (a Paid features subscription dataset) is not required.


## Sources

- [Amazon Marketing Cloud product overview](https://advertising.amazon.com/solutions/products/amazon-marketing-cloud/) — Amazon Ads

- [Amazon Marketing Cloud APIs are now part of the Amazon Ads API](https://advertising.amazon.com/resources/whats-new/amc-api-available-on-amazon-ads-api/) — Amazon Ads

- [Using Sankey diagrams in Amazon QuickSight](https://docs.aws.amazon.com/quicksight/latest/user/sankey-diagram.html) — AWS Documentation