Amazon Marketing Cloud

AMC SQL Fallstricke - Privacy-Masking, Retention & Schema-Stolperfallen

Stand: DE-only

Worum es geht

Diese Seite sammelt AMC-SQL-Fallstricke, die in der Praxis wiederholt Zeit kosten: Queries, die ohne Fehler laufen, aber falsche, maskierte oder stillschweigend beschnittene Ergebnisse liefern. AdLabs-Query-Patterns (User Overlap, Path to Conversion, Campaign Lift, Reach & Frequency) sind hier bewusst NICHT doppelt dokumentiert - siehe /guides/amc/queries/.

TL;DR

Vier Klassen, die jede AMC-SQL-Query treffen koennen: (1) Privacy-Masking maskiert Output-Zeilen ohne Fehlermeldung, sobald User-Aggregate mit Divisionen / Self-Joins kombiniert werden. (2) Die Attribution-Tabellen haben ~13 Monate rollierende Retention - "Voll-Jahr"-Fenster sind still Rumpf-Kohorten. (3) Schema-Annahmen aus Bestands-Queries nicht erben - Spalten aktiv testen. (4) Cost-Spalten leben auf _by_traffic_time, nicht auf _by_conversion_time.

Die 14 Fallstricke

Sortiert nach Schweregrad (Kritisch zuerst, dann Hoch, dann Mittel). Jeder Eintrag hat eine stabile id fuer Deep-Links (#pitfall-<id>).

  1. #1 Kritisch pitfall-retention-rolling-window

    AMC-Attributions-Tabellen haben ~13 Monate rollierende Retention - "Voll-Jahr"-Kohorten sind still Rumpf-Kohorten

    Symptom
    Kein Fehler, keine Warnung. Eine Query auf amazon_attributed_events_by_conversion_time mit Fenster 2025-01-01..2025-12-31 liefert ohne Beanstandung Daten - aber 2025-Q1 fehlt vollstaendig. Der Output *sieht* wie ein voller Zeitraum aus.
    Ursache
    Retention (kein Query-Bug): Retail-Daten reichen ~30 Monate zurueck, Ad-Attribution nur ~13. Gemessene Grenze am 2026-07-13 war ~2025-06-13. Der Effekt trifft *alle* time-bezogenen Queries auf amazon_attributed_events_*.
    Workaround
    Vor jeder Zeitraum-Aussage Monatsvolumen pruefen (eine billige Count-Query pro Monat), den ersten "gesunden" Monat als echten Kohorten-Start ausweisen, und Retail als Kontrollgruppe mitfuehren. Lauf-Datum auf jedes Deliverable stempeln - Re-Runs sind ueber die Zeit NICHT reproduzierbar.
    Quelle
    Quelle: Gotchas 2026-07-13: ~13 Monate rollierende Retention (LDB-09 Retention-Check).
  2. #2 Kritisch pitfall-masking-differencing-guard

    Privacy-Masking: Differencing-Guard maskiert die *ganze* Output-Zeile bei Divisionen / Self-Joins mit User-Aggregaten

    Symptom
    Eine Query liefert ausschliesslich leere Matrix-Zeilen - nur Summary-Zeilen ohne User-Daten ueberleben. Jeder Einzelfilter (Advertiser, ASIN, Datumsbereich) liefert isoliert echte Werte, in Kombination ist die ganze Zeile weg.
    Ursache
    AMC maskiert eine Output-Zeile komplett, sobald ein User-basiertes Aggregat auf zu wenigen beitragenden Usern beruht - und der Differencing-Guard feuert auf JEDE in-query Division durch ein User-Count-Aggregat anderer Granularitaet (Cost/N, CPA, Share). Self-Joins auf Aggregat-Ebene koennen ihn ebenfalls triggern.
    Workaround
    In AMC nur rohe Counts und ungeteilte gemessene Spend-Summen ausgeben; alle Ratios/Shares/CPAs in Excel/Python nachberechnen. Bei gemischten Queries: toxische Spalten-Klasse per Staged-UNION-ALL-Bisect isolieren. User-basierte Hilfsmetriken bewusst dicht halten (groberer Join > praeziser Join).
    Quelle
    Quelle: Gotchas 2026-06-12 (Masking-Bisect, Sparse-Aggregate-Mask) + 2026-07-03 (Self-Join-Trigger).
  3. #3 Kritisch pitfall-ldb-09-cost-undivided

    cost pro creative_asin ist der ASIN-Gesamtwert - Ein-Segment-Analysen muessen explizit allokieren

    Symptom
    Eine Auswertung nur fuer "New-to-Brand" sieht plausibel aus, liefert aber systematisch zu niedrige ROAS und verdreht die ASIN-Rangfolge. Kein Fehler, keine Warnung.
    Ursache
    AMC misst Spend nur pro creative_asin, nicht pro Kundensegment. Der Cost-Wert steht identisch auf allen Segment-Zeilen einer ASIN - "undivided, duplicated across all rows".
    Workaround
    Im Header der Query und in jeder Downstream-Auswertung klar ausweisen: cost = creative_asin-Total ueber ALLE Segmente. Vor einer Ein-Segment-Analyse (NTB, Existing, ...) explizit allokieren (z.B. ueber Customer-Count-Share oder gemessene Entry-Spend-Events pro Segment).
    Quelle
    Quelle: Gotcha 2026-07-13: LDB-09 cost ist ASIN-Gesamtwert ueber alle Segmente.
  4. #4 Hoch pitfall-ad-product-type-blank-dsp

    ad_product_type fuer DSP ist BLANK - 'DSP' / 'AMAZON_DSP' als Filter liefert 0 Zeilen

    Symptom
    WHERE ad_product_type = 'DSP' (oder 'AMAZON_DSP', 'DEMAND_SIDE_PLATFORM') liefert null Zeilen ohne Fehler - obwohl DSP-Aktivitaet mit Volumen in der Tabelle existiert.
    Ursache
    SELECT DISTINCT ad_product_type GROUP BY zeigt in dieser Instanz genau 3 Gruppen: sponsored_display, sponsored_brands, und eine leere/NULL-Gruppe - mit weitem Abstand die groesste, die einzige, die zum DSP-Volumen passt. DSP-Zeilen tragen schlicht KEIN ad_product_type.
    Workaround
    WHERE (ad_product_type IS NULL OR ad_product_type = '') fuer DSP auf amazon_attributed_events_*. Besser: dsp_impressions direkt nutzen (per Konstruktion DSP-only, keine ad_product_type-Spalte).
    Quelle
    Quelle: Gotcha 2026-07-09: ad_product_type fuer DSP ist BLANK.
  5. #5 Hoch pitfall-count-star-window-rejected

    COUNT(*) als Fensterfunktion wird abgelehnt - nur COUNT(<spalte>) OVER (...) erlaubt

    Symptom
    Verbatim-Fehler: "Query failed - The workflow was determined to be invalid: The COUNT window expression had 0 value expressions - window expressions type of this type must have exactly 1 value expression." Die Meldung erwaehnt nicht, dass der Stern das Problem ist.
    Ursache
    AMCs Planer akzeptiert * nur als gewoehnliches Aggregat (COUNT(*) ... GROUP BY laeuft problemlos). Als Fensterfunktion verlangt er genau einen Wert-Ausdruck - * zaehlt als null Ausdruecke. Reines Dialekt-Verhalten.
    Workaround
    Eine Spalte statt * zaehlen - idealerweise eine, die im Kontext garantiert NOT NULL ist. Dann ist das Ergebnis identisch zum Zeilen-Count der Partition. ROW_NUMBER() OVER (...) ist nicht betroffen (braucht ohnehin keinen Wert-Ausdruck).
    SQL-Snippet
    -- FEHLER:
COUNT(*) OVER (PARTITION BY asin) AS nodes_for_asin

-- OK (browse_node_name ist upstream NOT NULL-gefiltert):
COUNT(browse_node_name) OVER (PARTITION BY asin) AS nodes_for_asin
    Quelle
    Quelle: Gotcha 2026-07-13: COUNT(*) als Fensterfunktion abgelehnt.
  6. #6 Hoch pitfall-total-cost-only-on-traffic-time

    total_cost lebt nur auf _by_traffic_time - nicht auf _by_conversion_time

    Symptom
    Column 'total_cost' not found in table 'ae' bei Query gegen amazon_attributed_events_by_conversion_time, obwohl dieselbe Spalte auf der _by_traffic_time-Variante funktioniert.
    Ursache
    Die conversion-time-Variante enthaelt KEINE Cost-Spalten - sie fuehrt nur Conversion-Metriken. Cost haengt am Impression/Click, nicht an der Conversion, und lebt daher auf _by_traffic_time mit impression_date_utc.
    Workaround
    Zwei separate CTEs: attributed_events aus _by_conversion_time (purchases + sales, conversion_event_date_utc), attributed_traffic aus _by_traffic_time (total_cost, impression_date_utc). Aggregation pro user x ASIN auf der traffic-time-Seite, dann LEFT JOIN auf customer-level Klassifikation.
    SQL-Snippet
    attributed_traffic AS (
  SELECT user_id,
         tracked_asin AS asin,
         SUM(total_cost) AS user_asin_cost
  FROM amazon_attributed_events_by_traffic_time
  WHERE total_cost > 0
    AND impression_date_utc BETWEEN ... AND ...
  GROUP BY user_id, tracked_asin
)
    Quelle
    Quelle: Gotcha 2026-05-06: total_cost nicht auf _by_conversion_time.
  7. #7 Hoch pitfall-sat-creative-asin-direct-spend

    sponsored_ads_traffic.creative_asin = direkter ASIN-Spend (SP/SD) - strukturell doch messbar

    Symptom
    Wer "ASIN-Level-Spend existiert bei Amazon strukturell nicht" als gegeben hinnimmt, baut wochenlang Pro-Rating-Modelle (Impression-/Click-Share), obwohl SAT die Spalte creative_asin direkt liefert.
    Ursache
    Fruehere Schema-Probes testeten die Spaltennamen "asin" und "ad" - beide existieren nicht und lieferten Schema-Errors. Der korrekte Name steht im AMC-Schema-Browser und in der Repo-Doku (amazon-amc/documentation/analysis/AD_SPEND_SOURCES_ANALYSIS.md). Empirisch: SP ~96% Spend-Coverage mit ASIN, SD ebenfalls, SB leer (Multi-Produkt-Creatives).
    Workaround
    ASIN-Level-Spend direkt summieren statt pro-raten. Anonymous-Events (user_id NULL) tragen creative_asin trotzdem - ASIN-Zuordnung ist vollstaendig, Anonymitaet betrifft nur User-Level-Splits. Schema-Browser bei jeder neuen Spalte als erstes screen-shotten statt Namen raten.
    SQL-Snippet
    SELECT LOWER(TRIM(customer_search_term)) AS search_term,
       UPPER(TRIM(creative_asin))     AS asin,
       SUM(spend) / 100000000.0        AS spend_eur
FROM sponsored_ads_traffic
WHERE spend > 0
  AND creative_asin IS NOT NULL
  AND UPPER(TRIM(creative_asin)) LIKE 'B0________'
GROUP BY 1, 2
    Quelle
    Quelle: Gotcha 2026-06-12: creative_asin = direkter ASIN-Spend.
  8. #8 Hoch pitfall-sat-spend-microcents

    sponsored_ads_traffic.spend in MICROCENTS (geteilt durch 1e8 fuer EUR)

    Symptom
    Spend-Summen sind um Faktor 1e8 zu gross - ein 500-EUR-Tag erscheint als 50.000.000.000 in der Spalte spend.
    Ursache
    SAT speichert Spend in Microcents (1 EUR = 1e8 Microcents). Microcents vermeiden Floating-Point-Rundung in den Aggregationen.
    Workaround
    SUM(spend) / 100000000.0 AS spend_eur - IMMER in der Aggregation umrechnen, nie erst danach in Excel. Microcents sind Instanz-weit konsistent, aber nie durch ein computed field dokumentiert.
    Quelle
    Quelle: Gotcha 2026-05-06: spend in MICROCENTS (KORRIGIERT).
  9. #9 Hoch pitfall-sat-aaebct-join-click-date

    SAT x AAEbct JOIN braucht click_date_utc, NICHT conversion_event_date_utc

    Symptom
    JOIN auf (campaign_id_string, day) liefert 0 Matches, obwohl Campaign-IDs in beiden Tabellen existieren (verifiziert: 55 SAT_AND_AAEbct matches). Cost-Spalte komplett NULL.
    Ursache
    Beide Tabellen haben unterschiedliche Date-Semantiken: SAT.event_dt_utc = Traffic-Time (Impression/Click-Datum), AAEbct.conversion_event_date_utc = Conversion-Time (bis +14 Tage nach Click durch Attribution-Window), AAEbct.click_date_utc = Click-Time (= echtes Traffic-Date, alignt mit SAT).
    Workaround
    Konsistent click_date_utc fuer alle Click-bezogenen Aggregationen auf AAEbct nutzen, dann DATE_TRUNC("day", click_date_utc) gegen SAT.event_dt_utc joinen.
    SQL-Snippet
    campaign_total_clicks AS (
  SELECT campaign_id_string,
         DATE_TRUNC('day', click_date_utc) AS event_day,
         SUM(clicks) AS total_clicks
  FROM amazon_attributed_events_by_conversion_time
  WHERE clicks > 0
    AND click_date_utc IS NOT NULL
    AND click_date_utc BETWEEN ... AND ...
  GROUP BY campaign_id_string, DATE_TRUNC('day', click_date_utc)
)
    Quelle
    Quelle: Gotcha 2026-05-06: SAT x AAEbct JOIN braucht click_date_utc.
  10. #10 Mittel pitfall-unicode-apostrophe-comments-break-parser

    Apostrophe und Unicode-Zeichen in Kommentaren brechen den Parser

    Symptom
    Encountered "WITH" at line N, column 1. Was expecting one of: ... UESCAPE ... <QUOTED_STRING> ... - Parser erwartet String-Fortsetzung direkt am Query-Anfang, obwohl davor nur Kommentare stehen. Der Fehler zeigt auf das erste SQL-Token NACH dem Header, nicht auf die Ursache.
    Ursache
    AMCs Comment-Stripping stolpert ueber Apostrophe (Amazon's, user's) und Unicode (EUR, ->, x, >=) in -- Kommentaren. Teile des Headers werden als offenes String-Literal gelesen.
    Workaround
    Kommentare strikt ASCII-only und ohne Apostrophe/Anfuehrungszeichen halten. 'users' statt 'user's', 'EUR' statt 'EUR', '->' statt '->', 'x' statt 'x'.
    Quelle
    Quelle: Gotcha 2026-05-06: Apostrophe/Unicode in Kommentaren brechen Parser.
  11. #11 Mittel pitfall-sat-campaign-id-string-mismatch

    campaign_id_string matched NICHT zwischen sponsored_ads_traffic und category_insights_advertiser_sponsored_impressions

    Symptom
    JOIN zwischen SAT und CI-Impressions auf campaign_id_string liefert 0 Matches - Campaign-IDs haben aber dasselbe Format in beiden Tabellen (verifiziert).
    Ursache
    CI-Impressions fuehrt eine eigene Campaign-ID-Semantik (andere Laenge, andere Prefix-Konvention). Ein direkter Join auf campaign_id_string klappt daher nie - trotz formal gleicher Spalte.
    Workaround
    SAT ueber user_id x event_dt_utc mit AAEbct joinen, CI-Impressions separat aggregieren und auf User-Ebene ueber user_id verbinden. Campaign-IDs aus CI niemals als Join-Key zu SAT verwenden.
    Quelle
    Quelle: Gotcha 2026-05-06: campaign_id_string matched NICHT.
  12. #12 Mittel pitfall-pro-rating-denominator-all-conversions

    Pro-Rating-Denominator muss ALLE Conversions nehmen, nicht ASIN-gefiltert

    Symptom
    Pro-Rating-Faktoren springen um Faktoren 2-5 zwischen Queries, die "logisch identisch" aussehen.
    Ursache
    Wird der Denominator (z.B. SUM(conversions)) auf das Entry-ASIN gefiltert, verliert man den User-Pool, der Multi-ASIN-Kaeufer - die Pro-Rating-Verteilung verzerrt massiv.
    Workaround
    Denominator immer ueber die volle Conversion-Population (alle ASINs), dann ASIN-Splits als Zaehler berechnen. Vor jeder Pro-Rating-Aenderung: Nenner und Zaehler pro Cohort separat ausgeben.
    Quelle
    Quelle: Gotcha 2026-05-06: Pro-Rating-Denominator muss ALLE Conversions nehmen.
  13. #13 Mittel pitfall-aaebct-multi-row-dedup

    amazon_attributed_events_by_conversion_time ist Multi-Row-per-Conversion - Dedup Pflicht

    Symptom
    Conversion-Counts sind deutlich zu hoch (oft Faktor 2-4) gegenueber Retail-Truth.
    Ursache
    AAEbct fuehrt fuer eine Conversion bis zu 4 Rows: eine pro "touch point" plus eine finale - und pro touch point ein eigener Conversion-Eintrag, der in naiven COUNT(*) mitzaehlt.
    Workaround
    Conversion-IDs aus dem Schema-Browser identifizieren, pro (user_id, conversion_event_id) dedupzieren (ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...)) und nur die "finale" Row behalten. Ohne Dedup ist JEDE Conversion-Metrik aus AAEbct systematisch zu hoch.
    Quelle
    Quelle: Gotcha 2026-05-06: Multi-Row-per-Conversion, Dedup-Pflicht.
  14. #14 Mittel pitfall-reserved-words-as-alias

    Reservierte Woerter als Spalten-/Tabellen-Alias brechen den Calcite-Parser

    Symptom
    Bisect-Tests: exakt dieselbe Query laeuft mit AS c1, schlaegt mit AS day fehl. Fehler zeigt auf das Token NACH dem problematischen Alias, nicht auf den Alias selbst.
    Ursache
    AMCs Calcite-Parser fuehrt eine reservierte-Wort-Liste (day, at, ...). Aliasse, die SQL-Schluesselwoertern entsprechen, werden stillschweigend zurueckgewiesen - ohne den Alias im Fehlertext zu nennen.
    Workaround
    Niemals day, at, order, group, user, table, ... als Alias verwenden. Stattdessen day_idx, at_ts, order_id, group_key, user_pk, tbl_name.
    Quelle
    Quelle: Gotcha 2026-05-06: day/at als Alias, weitere Reserved-Words.

Quellen

Saemtliche Eintraege stammen aus dem append-only Gotchas-Log (datierte Eintraege 2026-05-06 bis 2026-07-24) - jede Fallstrike hat dort Endpoint, Symptom, Cause, Workaround und Source-Commit. Der Log wird laufend gepflegt und enthaelt auch DSP- / Ad-API-Gotchas ausserhalb des SQL-Bereichs.

Seiten-URL: /guides/amc-sql-fallstricke/