Amazon Marketing Cloud (AMC) is Amazon's clean room analytics environment. It lets advertisers run custom SQL queries across their advertising and retail data, spanning Sponsored Ads, DSP, streaming TV, and even first-party uploaded audiences, all in one place.

Unlike the standard Amazon Ads console, which gives you pre-built dashboards and fixed metrics, AMC gives you raw event-level data and a SQL editor. You write the queries. You define the logic. You control the analysis.

  • Cross-channel analytics: See how Sponsored Products, Sponsored Brands, DSP, and Streaming TV work together (not just in isolation)
  • Customer journey mapping: Track the sequence of ad impressions and clicks that lead to a conversion
  • Multi-touch attribution: Build custom attribution models beyond Amazon's default last-touch
  • Audience creation: Build audiences from behavioral signals and push them into DSP campaigns
  • New-to-brand analysis: Understand which campaigns drive genuinely new customers vs. repeat purchasers
  • Overlap and frequency analysis: Find out how many users saw ads across multiple channels and how often
  • Agencies managing Amazon advertising for multiple brands
  • Brands with in-house Amazon advertising teams
  • Advertisers who need deeper analytics than the standard Amazon Ads console provides
  • Data teams building custom reporting pipelines and dashboards
Feature Amazon Ads Console AMC
Data format Pre-aggregated reports Event-level SQL access
Cross-channel Siloed by ad type JOINs across all ad types
Custom logic Fixed metrics only Write any SQL query
Attribution Last-touch only Build custom models
Audiences Pre-built segments SQL-defined audiences
Historical data Limited lookback Up to 25 months (ads); up to 5 years (retail)

AMC is a clean room, which means privacy is baked into the architecture. You can query event-level data, but you can never extract individual user information. Here's what that means in practice:

🔒 No SELECT *: AMC will reject any query that uses SELECT *. You must explicitly name every column you want. This prevents accidental exposure of sensitive fields.
🔒 Aggregation Thresholds: Query results are suppressed if any group contains too few users. If your GROUP BY produces a row representing fewer users than the minimum threshold, that row is silently dropped from the output. You won't get an error; you'll just get fewer rows than expected.
🔒 user_id Is Non-Viewable: The user_id column exists in most AMC tables and is essential for JOINing across tables. But you can never SELECT it into your output. You can use it in JOIN ON, WHERE, and GROUP BY clauses, but it cannot appear in your final result set.
Key Insight: Think of AMC as a "write queries in, get aggregated results out" system. You can analyze individual-level behavior inside your query, but everything that leaves AMC must be aggregated. The clean room sees everything; you only see summaries.

AMC uses a SQL dialect based on Presto/Trino, but with significant restrictions enforced by the clean room environment. If you've written SQL anywhere else, many of these will trip you up. This section covers every major difference.

Why This Section Matters: Most AMC query errors come from writing standard SQL that violates clean room rules. Learn these differences first and you'll avoid 80% of debugging time.
❌ This will fail:
SELECT campaign_name, SUM(spend) AS total_spend
FROM sponsored_ads_traffic
GROUP BY campaign_name
ORDER BY total_spend DESC
✅ Do this instead: Remove ORDER BY from your AMC query entirely. Export the results, then sort in your downstream tool (Excel, Python, your BI platform). AMC does not support ORDER BY as a query clause; it only works inside window functions such as ROW_NUMBER() OVER (...).
CORRECT AMC QUERY
SELECT campaign_name, SUM(spend) / 100000000.0 AS total_spend
FROM sponsored_ads_traffic
GROUP BY campaign_name
❌ Always rejected:
SELECT * FROM amazon_attributed_events_by_traffic_time

AMC's privacy model requires you to explicitly name every column. SELECT * is blocked to prevent accidental exposure of sensitive or non-viewable fields like user_id.
CORRECT APPROACH
SELECT campaign_id, ad_group_id, event_date_utc, conversion_event
FROM amazon_attributed_events_by_traffic_time
WHERE conversion_event = 'Purchase'
❌ This will fail:
SELECT a.campaign_name, b.total_cost
FROM dsp_impressions b
RIGHT JOIN sponsored_ads_traffic a ON a.user_id = b.user_id
✅ Do this instead: Swap the table order and use LEFT JOIN. A RIGHT JOIN of A→B is just a LEFT JOIN of B→A.
EQUIVALENT LEFT JOIN
SELECT a.campaign_name, b.total_cost
FROM sponsored_ads_traffic a
LEFT JOIN dsp_impressions b ON a.user_id = b.user_id
❌ This will fail:
SELECT campaign_id FROM sponsored_ads_traffic a
WHERE EXISTS (
  SELECT 1 FROM amazon_attributed_events_by_traffic_time b
  WHERE b.user_id = a.user_id
)

AMC does not allow subqueries that reference columns from the outer query (a.user_id inside the subquery).
✅ Do this instead: Rewrite as a JOIN or use a CTE.
REWRITTEN AS JOIN
SELECT DISTINCT a.campaign_id
FROM sponsored_ads_traffic a
INNER JOIN amazon_attributed_events_by_traffic_time b
  ON a.user_id = b.user_id
⚠️ Silent data loss: If a GROUP BY bucket contains too few unique users, that entire row is dropped from results, with no error and no warning. Your output just has fewer rows than you expected.

This commonly happens when you:

  • Group by too many dimensions at once (e.g., campaign + ad group + day + creative)
  • Filter to a very narrow time range or audience
  • Analyze low-traffic campaigns
✅ Mitigations:
  • Reduce the number of GROUP BY dimensions
  • Use broader time ranges
  • Group by week or month instead of day
  • Combine small campaigns into an "Other" bucket
❌ This will fail:
SELECT month, campaign_id, SUM(impressions) AS impressions
FROM dsp_impressions
GROUP BY month, campaign_id

month and at are reserved words in AMC's SQL dialect.
QUOTE RESERVED WORDS
SELECT "month", campaign_id, SUM(impressions) AS impressions
FROM dsp_impressions
GROUP BY "month", campaign_id
✅ Common reserved words in AMC: month, at, date, time, order, group, table, select. When in doubt, wrap column names in double quotes.
⚠️ Misleading field: purchase_month_utc returns only the month number (1–12) with no year. Grouping by this field merges January 2025 with January 2026 into a single bucket. This is almost never what you want.
❌ Wrong (loses year information):
SELECT purchase_month_utc, COUNT(*) AS purchases
FROM amazon_attributed_events_by_traffic_time
GROUP BY purchase_month_utc
CORRECT: USE DATE_TRUNC
SELECT DATE_TRUNC('MONTH', purchase_date_utc) AS purchase_month,
  COUNT(*) AS purchases
FROM amazon_attributed_events_by_traffic_time
GROUP BY DATE_TRUNC('MONTH', purchase_date_utc)
⚠️ Type mismatch trap: event_day_utc and impression_day_utc are INTEGER fields (e.g., 20260115), not dates or timestamps. Date functions like DATE_TRUNC, DATE_ADD, and comparisons with date literals will fail or produce wrong results.
❌ Wrong (integer, not a date):
SELECT DATE_TRUNC('MONTH', event_day_utc) AS event_month
FROM sponsored_ads_traffic
CORRECT: USE THE TIMESTAMP COLUMN
SELECT DATE_TRUNC('MONTH', event_date_utc) AS event_month
FROM sponsored_ads_traffic
✅ Rule of thumb: Always use event_date_utc (TIMESTAMP) instead of event_day_utc (INTEGER). Same for impression_date_utc over impression_day_utc.
⚠️ Different ad types use different units! If you don't convert, your numbers will be wildly wrong, off by factors of 100,000 or 100,000,000.
Source Field Unit Divide By Example: $5.00
Sponsored Ads spend Microcents 100,000,000 500000000
DSP total_cost Millicents 100,000 500000
❌ Wrong (raw values are not dollars):
SELECT campaign_name, SUM(spend) AS total_spend
FROM sponsored_ads_traffic
GROUP BY campaign_name
SPONSORED ADS: MICROCENTS TO DOLLARS
SELECT campaign_name,
  SUM(spend) / 100000000.0 AS total_spend_usd
FROM sponsored_ads_traffic
GROUP BY campaign_name
DSP: MILLICENTS TO DOLLARS
SELECT campaign_name,
  SUM(total_cost) / 100000.0 AS total_cost_usd
FROM dsp_impressions
GROUP BY campaign_name
✅ Pro tip: When combining Sponsored Ads and DSP spend in one query, convert both to dollars before summing. Never add raw values across different ad types.
How AMC date ranges work: Unlike standard SQL databases, AMC's date range is not set in your query. You configure it in the AMC UI when you execute the query. Your SQL doesn't need a WHERE date BETWEEN ... clause for the primary date filter. AMC handles that automatically.
  • Advertising data: Up to 25 months of lookback
  • Retail / purchase data: Up to 5 years (with Amazon Retail Purchases subscription)
  • The date range in the UI filters the primary event table in your query
✅ You can still use date filters in SQL for additional filtering within the UI-defined range, for example, filtering to weekends only, or to a specific hour of day.

Sometimes you need data from before the reporting window. For example, to check if a user who converted during your date range had prior ad exposure outside that window. AMC provides the EXTEND_TIME_WINDOW hint for this.

SYNTAX
--EXTEND_TIME_WINDOW
SELECT ...
FROM table_name
WHERE ...

Place --EXTEND_TIME_WINDOW as a SQL comment at the very top of your query. This tells AMC to include data from before the start date of your reporting window for JOINed tables, enabling lookback analysis.

✅ Common use cases:
  • New-to-brand analysis: Did this user see any ads before the reporting window?
  • Customer journey lookback: What was the first touchpoint, even if it was months ago?
  • Frequency capping analysis: Total lifetime impressions, not just in-window
Important: EXTEND_TIME_WINDOW extends the lookback for joined tables, not the primary table. The primary table still respects the UI date range.

This section is a quick-reference for SQL fundamentals as they work in AMC. If you're comfortable with SQL, skim for AMC-specific notes. If you're learning, this covers everything you need.

SELECT defines which columns appear in your output. DISTINCT removes duplicate rows.

SELECT
SELECT column1, column2, column3
FROM table_name
SELECT DISTINCT
SELECT DISTINCT campaign_name
FROM sponsored_ads_traffic
Remember: No SELECT * in AMC. Always list your columns explicitly.

FROM specifies the table(s) to query. In AMC, these are Amazon-managed tables like sponsored_ads_traffic, dsp_impressions, and amazon_attributed_events_by_traffic_time.

FROM
SELECT campaign_id, impressions
FROM sponsored_ads_traffic

JOINs combine rows from two or more tables based on a related column, most commonly user_id in AMC.

INNER JOIN

Returns only rows that have matching values in both tables.

INNER JOIN
SELECT a.campaign_name, b.conversion_event
FROM sponsored_ads_traffic a
INNER JOIN amazon_attributed_events_by_traffic_time b
  ON a.user_id = b.user_id

LEFT JOIN

Returns all rows from the left table, plus matching rows from the right. Non-matching right-side columns return NULL.

LEFT JOIN
SELECT a.campaign_name, b.total_cost
FROM sponsored_ads_traffic a
LEFT JOIN dsp_impressions b
  ON a.user_id = b.user_id

FULL OUTER JOIN

Returns all rows from both tables. Non-matching columns on either side return NULL.

FULL OUTER JOIN
SELECT
  COALESCE(a.user_id, b.user_id) AS user_id,
  a.impressions,
  b.conversions
FROM sponsored_ads_traffic a
FULL OUTER JOIN amazon_attributed_events_by_traffic_time b
  ON a.user_id = b.user_id

CROSS JOIN

Returns the Cartesian product: every row from the left table paired with every row from the right. Use sparingly; results grow multiplicatively.

CROSS JOIN
SELECT a.campaign_name, b.date_value
FROM campaigns a
CROSS JOIN date_spine b

Non-Equi JOIN

A JOIN where the condition uses an operator other than = (e.g., <, >, BETWEEN). Useful for time-window analysis.

NON-EQUI JOIN (Time Window)
SELECT a.user_id, a.impression_date_utc, b.purchase_date_utc
FROM dsp_impressions a
INNER JOIN amazon_attributed_events_by_traffic_time b
  ON a.user_id = b.user_id
  AND b.purchase_date_utc > a.impression_date_utc
  AND SECONDS_BETWEEN(a.impression_date_utc, b.purchase_date_utc) <= 14 * 86400
Reminder: No RIGHT JOIN in AMC. Swap the tables and use LEFT JOIN instead.
Operation What It Does Result
JOIN Combines columns from two tables side-by-side More columns, matched rows
UNION Stacks rows from two queries on top of each other Same columns, more rows
UNION (removes duplicates)
SELECT campaign_name, 'Sponsored' AS source FROM sponsored_ads_traffic
UNION
SELECT campaign_name, 'DSP' AS source FROM dsp_impressions
UNION ALL (keeps duplicates, faster)
SELECT campaign_name, 'Sponsored' AS source FROM sponsored_ads_traffic
UNION ALL
SELECT campaign_name, 'DSP' AS source FROM dsp_impressions
✅ Use UNION ALL unless you specifically need deduplication. It's faster because it skips the duplicate-check step.

WHERE filters rows before aggregation.

WHERE
SELECT campaign_name, SUM(impressions) AS total_impressions
FROM sponsored_ads_traffic
WHERE campaign_name LIKE '%Brand%'
GROUP BY campaign_name

GROUP BY groups rows that share values in specified columns, enabling aggregate functions like SUM, COUNT, AVG.

GROUP BY
SELECT campaign_name, ad_group_name,
  SUM(impressions) AS total_impressions,
  SUM(clicks) AS total_clicks
FROM sponsored_ads_traffic
GROUP BY campaign_name, ad_group_name
AMC note: Every non-aggregated column in your SELECT must appear in GROUP BY. This is standard SQL, but AMC enforces it strictly.

HAVING filters after aggregation. Use it to filter on aggregate values.

HAVING
SELECT campaign_name,
  SUM(spend) / 100000000.0 AS total_spend_usd
FROM sponsored_ads_traffic
GROUP BY campaign_name
HAVING SUM(spend) / 100000000.0 > 100
WHERE vs HAVING: WHERE filters individual rows before grouping. HAVING filters groups after aggregation. Use WHERE when possible; it's more efficient because it reduces data before aggregation.

Aliases give tables or columns temporary names, making queries more readable.

COLUMN ALIAS
SELECT campaign_name AS campaign,
  SUM(spend) / 100000000.0 AS spend_usd
FROM sponsored_ads_traffic
GROUP BY campaign_name
TABLE ALIAS
SELECT a.campaign_name, b.conversion_event
FROM sponsored_ads_traffic a
INNER JOIN amazon_attributed_events_by_traffic_time b
  ON a.user_id = b.user_id
✅ Best practice: Always alias tables in JOINs. Single letters (a, b, c) work for simple queries. For complex queries, use meaningful abbreviations (sat for sponsored_ads_traffic, dsp for dsp_impressions).

The VALUES clause lets you create inline data sets without a real table. Useful for mapping codes to labels, defining lookup lists, or creating date spines.

VALUES IN A CTE
WITH channel_map AS (
  SELECT * FROM (VALUES
    ('SP', 'Sponsored Products'),
    ('SB', 'Sponsored Brands'),
    ('SD', 'Sponsored Display'),
    ('DSP', 'Demand Side Platform')
  ) AS t(code, channel_name)
)
SELECT a.campaign_name, b.channel_name
FROM sponsored_ads_traffic a
LEFT JOIN channel_map b ON a.channel_code = b.code

AMC supports regular expressions via REGEXP_LIKE, REGEXP_EXTRACT, and REGEXP_REPLACE.

REGEXP_LIKE: Filter rows
SELECT campaign_name
FROM sponsored_ads_traffic
WHERE REGEXP_LIKE(campaign_name, '(?i)brand.*exact')
REGEXP_EXTRACT: Pull out a pattern
SELECT
  REGEXP_EXTRACT(campaign_name, '([A-Z]{2})_(\d{4})', 1) AS country_code,
  REGEXP_EXTRACT(campaign_name, '([A-Z]{2})_(\d{4})', 2) AS year
FROM sponsored_ads_traffic
REGEXP_REPLACE: Transform text
SELECT
  REGEXP_REPLACE(campaign_name, '_v\d+$', '') AS base_campaign
FROM sponsored_ads_traffic
✅ Tip: Use (?i) at the start of your pattern for case-insensitive matching. AMC regex follows Java regex syntax.

CTEs (the WITH clause) let you define temporary named result sets that you can reference later in the query. They make complex queries readable and modular.

BASIC CTE
WITH campaign_spend AS (
  SELECT campaign_name,
    SUM(spend) / 100000000.0 AS spend_usd
  FROM sponsored_ads_traffic
  GROUP BY campaign_name
)
SELECT campaign_name, spend_usd
FROM campaign_spend
WHERE spend_usd > 50
MULTIPLE CTEs
WITH impressions AS (
  SELECT campaign_name, COUNT(*) AS impression_count
  FROM sponsored_ads_traffic
  GROUP BY campaign_name
),
conversions AS (
  SELECT campaign_name, COUNT(*) AS conversion_count
  FROM amazon_attributed_events_by_traffic_time
  WHERE conversion_event = 'Purchase'
  GROUP BY campaign_name
)
SELECT a.campaign_name,
  a.impression_count,
  b.conversion_count,
  CAST(b.conversion_count AS DOUBLE) / a.impression_count AS conv_rate
FROM impressions a
LEFT JOIN conversions b ON a.campaign_name = b.campaign_name
Why CTEs matter in AMC: Since AMC doesn't support correlated subqueries, CTEs are your primary tool for breaking complex logic into manageable steps. Think of each CTE as a "virtual table" you can JOIN against.
✅ CTE best practices:
  • Give CTEs descriptive names (campaign_spend not cte1)
  • Do aggregation and filtering inside CTEs to keep the final SELECT clean
  • Chain CTEs: later CTEs can reference earlier ones
  • Use CTEs instead of nested subqueries for readability
✦ ADLABS FEATURE

Keyword Harvesting & Negation in Two Clicks

Harvest winning search terms into the right campaign in two clicks and negate waste in bulk. Less time mining search term reports means more time for AMC analysis.

Try Free →
AdLabs automated keyword harvesting and n-gram negation

Complete reference of all SQL functions available in AMC, organized by category.

FunctionDescriptionSyntax
CHARINDEXSearches for a substring within a string and returns starting positionCHARINDEX(substring, string [, start_position])
CONCATConcatenates multiple stringsCONCAT(expression [, expression]*)
FORMATFormats a value according to a patternFORMAT(string_expression, format_string)
INSTRFinds first occurrence position of substringINSTR(string, substring)
LEFTExtracts N characters from left sideLEFT(string, length)
LENGTHNumber of characters in stringLENGTH(string)
LIKEMatches column to string with wildcardscolumn LIKE expression
LOCATEAlias of CHARINDEXLOCATE(substring, string[, start_position])
LOWERConverts to lowercaseLOWER(string)
LTRIMRemoves leading whitespaceLTRIM(string)
POSITIONFinds first occurrencePOSITION(substring IN string)
REGEXP_REPLACEReplace text matching regexREGEXP_REPLACE(string, pattern, replacement)
REGEXP_SUBSTRExtract substring matching regexREGEXP_SUBSTR(string, pattern)
REGEXP_SPLITSplits string by regexREGEXP_SPLIT(string, pattern)
REPLACESubstitutes all occurrencesREPLACE(string, search, replacement)
RIGHTExtracts N characters from rightRIGHT(string, length)
RTRIMRemoves trailing whitespaceRTRIM(string)
SIMILAR TOEvaluates regex match (Java regex syntax)expression [NOT] SIMILAR TO expression
STRPOSPosition of substringSTRPOS(string, substring)
STR_TO_MAPConverts delimited string to key-value mapSTR_TO_MAP(string, pair_delimiter, key_value_delimiter)
SUBSTRINGExtracts portion of stringSUBSTRING(string FROM start [FOR length])
TRIMRemoves space/characters from start/endTRIM(str) | TRIM(BOTH trimStr FROM str) | TRIM(LEADING trimStr FROM str) | TRIM(TRAILING trimStr FROM str)
UPPERConverts to uppercaseUPPER(string)
FunctionDescriptionSyntax
ABSAbsolute valueABS(numeric_expression)
CEILRounds up to nearest integerCEIL(number)
E()Returns mathematical constant e (~2.71828)E()
EXPe raised to powerEXP(number)
FLOORRounds down to nearest integerFLOOR(number)
LNNatural logarithmLN(number)
LOGLogarithm with specified baseLOG(base, number)
LOG10Base-10 logarithmLOG10(number)
POWERRaises number to powerPOWER(number, power)
RANDOMRandom decimal 0-1RANDOM()
ROUNDRounds to decimal placesROUND(expression [, integer])
SQRTSquare rootSQRT(number)
TRUNCTruncates without roundingTRUNC(number[, decimals])
FunctionDescriptionSyntax
AVGAverage of valuesAVG(column_name)
COUNTTotal number of valuesCOUNT(column_name) | COUNT(DISTINCT column_name)
MINMinimum valueMIN(column_name)
MAXMaximum valueMAX(column_name)
SUMSum of valuesSUM(column_name)
FunctionDescriptionSyntax
CONVERT_TIME_ZONE_FROM_UTCConverts UTC to specified timezoneCONVERT_TIME_ZONE_FROM_UTC(inputTime, 'timeZone')
CURRENT_DATEReturns current date in UTCCURRENT_DATE
CURRENT_TIMESTAMPReturns current date+time in UTCCURRENT_TIMESTAMP
DATE_TRUNCTruncates timestamp to granularity (SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR)DATE_TRUNC('granularity', inputTime)
DAYOFMONTHDay of month (1-31)DAYOFMONTH(date)
DAYOFWEEKDay of week (1-7, 1=Sunday)DAYOFWEEK(date)
EPOCH_TO_UTC_TIMESTAMPConverts epoch to timestampEPOCH_TO_UTC_TIMESTAMP(expression)
EXTEND_TIME_WINDOWExtends table time period beyond reporting windowEXTEND_TIME_WINDOW(table_name, retro_days, future_days)
EXTRACTExtracts time unit from date/timestampEXTRACT(timeUnit FROM expression)
HOURHour (0-23)HOUR(timestamp)
MINUTEMinute (0-59)MINUTE(timestamp)
MONTHMonth (1-12)MONTH(date)
SECONDSecond (0-59)SECOND(timestamp)
WEEKWeek number (1-53)WEEK(date)
YEARYear from dateYEAR(date)
SECONDS_BETWEENTime difference in secondsSECONDS_BETWEEN(firstInputTime, secondInputTime)
FunctionDescriptionSyntax
CASEConditional expressionCASE WHEN condition THEN result [ELSE default] END
COALESCEReturns first non-nullCOALESCE(expression [, expression]*)
IFBoolean conditionalIF(booleanExpression, trueExpression, falseExpression)
IFNULLAlias of COALESCEIFNULL(expression, alternate_value)
INMultiple values in WHEREIN(value1, value2, ..., valueN) | NOT IN(...)
NULLIFReturns NULL if equalNULLIF(expr1, expr2)
NVLAlias of COALESCENVL(expr1, expr2)
NVL2Three-way NULL logicNVL2(expr1, expr2, expr3)
FunctionDescriptionSyntax
ARRAY_CATConcatenates two arraysARRAY_CAT(array1, array2)
ARRAY_CONTAINSReturns true if value in arrayARRAY_CONTAINS(array, value)
ARRAY_COMPLEMENTElements in first not in secondARRAY_COMPLEMENT(array1, array2)
ARRAY_DISTINCTRemoves duplicatesARRAY_DISTINCT(array)
ARRAY_INTERSECTCommon elementsARRAY_INTERSECT(array1, array2)
ARRAY_REMOVERemoves occurrences of elementARRAY_REMOVE(array, element)
ARRAY_SORTSorts arrayARRAY_SORT(COLLECT(DISTINCT column))
ARRAY_TO_STRINGConverts array to stringARRAY_TO_STRING(array, separator)
ARRAY_UNIONCombines unique elementsARRAY_UNION(array1, array2)
CARDINALITYCount elements in arrayCARDINALITY(array)
COLLECTCreates list of valuesCOLLECT(column) | COLLECT(DISTINCT column)
EXPLODEArray to multiple rowsEXPLODE(array)
FLATTENNested array to flatFLATTEN(array)
FunctionDescriptionSyntax
DENSE_RANKRank without gapsDENSE_RANK() OVER (PARTITION BY col ORDER BY col)
PARTITION BYDivides result set into partitionsUsed with window functions
RANKRank with gaps for tiesRANK() OVER (PARTITION BY col ORDER BY col)
ROW_NUMBERSequential row numberROW_NUMBER() OVER (PARTITION BY col ORDER BY col)
FunctionDescriptionSyntax
STDDEV_POPStandard deviation of populationSTDDEV_POP(values)
VAR_POPVariance of populationVAR_POP(values)
STDDEV_SAMP / STDDEVStandard deviation of sampleSTDDEV_SAMP(values) | STDDEV(values)
VAR_SAMP / VARIANCEVariance of sampleVAR_SAMP(values) | VARIANCE(values)
SKEWNESSSkewness of distributionSKEWNESS(values)
PERCENTILEValue at percentile (0-1)PERCENTILE(values, percentile)
APPROX_PERCENTILEApproximate percentile (rejected in our live tests; use MEDIAN)APPROX_PERCENTILE(values, percentile)
MEDIANMiddle valueMEDIAN(values)
FunctionDescriptionSyntax
GREATESTLargest of expressions (null-sensitive)GREATEST(expr [, expr]*)
LEASTSmallest of expressions (null-sensitive)LEAST(expr [, expr]*)
HASH6464-bit hashHASH64(expression)
MD5128-bit MD5 hashMD5(string)
SHA1160-bit SHA-1 hashSHA1(string)
FROM_VARBINARYBinary to string (hex/base64/utf-8)FROM_VARBINARY(binary_expression, type)
TO_VARBINARYString to binaryTO_VARBINARY(string, type)
JSON_EXTRACTExtract from JSONJSON_EXTRACT(json_string, json_path)
JSON_SERIALIZEJSON to stringJSON_SERIALIZE(json_value)
CASTType conversionCAST(expression AS dataType)
NAMED_ROWGroup columns under one nameNAMED_ROW('label1', col1, 'label2', col2)

This guide covers 7 main tables with a total of 761 columns, the ones you'll use most often. AMC contains many additional tables beyond these (including paid subscriptions and marketplace-specific datasets). Here's a complete reference for each of the 7: what it contains, the key columns you'll use most often, and the full column list for when you need it.

TableColumnsPrimary Use
amazon_retail_purchases26Customer purchase data
sponsored_ads_traffic79SP, SB, SD ad traffic
dsp_impressions106DSP impression data
dsp_clicks105DSP click data
amazon_attributed_events_by_conversion_time392Attribution/conversion data
conversions_all33All conversion events
amazon_live_traffic20Amazon Live data
TOTAL761

Customer purchase data from Amazon retail. Contains order-level detail with ASIN, price, and quantity information. Goes back up to 5 years with the Amazon Retail Purchases subscription.

Key Columns

ColumnTypeDescription
asinSTRINGAmazon Standard Identification Number for the purchased product
asin_nameSTRINGProduct title/name
asin_brandSTRINGBrand name of the product
purchase_date_utcTIMESTAMPDate/time of the purchase in UTC
unit_priceDECIMALPrice per unit at time of purchase
purchase_units_soldINTEGERNumber of units in this purchase
user_idSTRINGHashed user identifier (non-viewable; COUNT DISTINCT and JOINs only)
purchase_idSTRINGUnique identifier for the purchase/order
currency_codeSTRINGCurrency of the transaction (e.g., USD)
purchase_program_nameSTRINGPurchase program (e.g., Fresh, Pantry); empty for S&S
⚠️ Gotchas
  • purchase_month_utc returns month NUMBER (1–12) without year; use DATE_TRUNC('MONTH', purchase_date_utc) instead
  • purchase_program_name returns EMPTY for Subscribe & Save; S&S data is NOT available in this table
  • user_id is NON-VIEWABLE (hashed) and can only be used for COUNT(DISTINCT user_id) and JOINs
All 26 Columns
#Column
1asin
2asin_brand
3asin_name
4asin_parent
5currency_code
6event_id
7is_business_flag
8is_gift_flag
9marketplace_id
10marketplace_name
11no_3p_trackers
12origin_session_id
13purchase_date_utc
14purchase_day_utc
15purchase_dt_hour_utc
16purchase_dt_utc
17purchase_hour_utc
18purchase_id
19purchase_month_utc
20purchase_order_method
21purchase_program_name
22purchase_session_id
23purchase_units_sold
24unit_price
25user_id
26user_id_type

Traffic data from Sponsored Products, Sponsored Brands, and Sponsored Display campaigns. Contains impressions, clicks, spend, and targeting info. Full-Funnel campaigns (beta) show no spend or traffic here; their conversions appear only in the attribution tables.

Key Columns

ColumnTypeDescription
campaignSTRINGCampaign name
campaign_id_stringSTRINGCampaign ID (use this, not campaign_id)
ad_product_typeSTRINGAd type: Sponsored Products, Sponsored Brands, or Sponsored Display
creative_asinSTRINGASIN in the ad creative (SP only; NULL for SB/SD)
event_date_utcTIMESTAMPDate/time of the traffic event in UTC
spendBIGINTAd spend in microcents (÷ 100,000,000 for dollars)
clicksINTEGERNumber of clicks
impressionsINTEGERNumber of impressions
customer_search_termSTRINGThe search query that triggered the ad
match_typeSTRINGKeyword match type (exact, phrase, broad)
targetingSTRINGTargeting expression (keyword or product target)
ad_groupSTRINGAd group name
⚠️ Gotchas
  • spend is in MICROCENTS: divide by 100,000,000 to get dollars
  • event_day_utc is INTEGER, not a date; use event_date_utc (TIMESTAMP) instead
  • creative_asin is only populated for Sponsored Products, not SB/SD
All 79 Columns
#Column
1ad_group
2ad_group_end_dt
3ad_group_end_dt_utc
4ad_group_start_dt
5ad_group_start_dt_utc
6ad_group_status
7ad_group_type
8ad_product_type
9ad_product_type_code
10ad_product_type_id
11advertiser
12advertiser_id_internal
13advertiser_timezone
14campaign
15campaign_budget_type
16campaign_end_date
17campaign_end_date_utc
18campaign_end_dt
19campaign_end_dt_utc
20campaign_id
21campaign_id_string
22campaign_start_date
23campaign_start_date_utc
24campaign_start_dt
25campaign_start_dt_utc
26clicks
27creative
28creative_asin
29creative_type
30currency_iso_code
31currency_name
32customer_search_term
33entity_id
34event_date
35event_date_utc
36event_day
37event_day_utc
38event_dt
39event_dt_hour
40event_dt_hour_utc
41event_dt_utc
42event_hour
43event_hour_utc
44event_id
45five_sec_views
46impressions
47ip_address
48is_amazon_business
49line_item
50line_item_end_date
51line_item_end_date_utc
52line_item_id
53line_item_price_type
54line_item_start_date
55line_item_start_date_utc
56line_item_status
57line_item_type
58marketplace_id
59marketplace_name
60match_type
61matched_behavior_segment_ids
62no_3p_trackers
63operating_system
64os_version
65placement_type
66portfolio_id
67portfolio_name
68spend
69targeting
70unmeasurable_viewable_impressions
71user_id
72user_id_type
73video_complete_views
74video_first_quartile_views
75video_midpoint_views
76video_third_quartile_views
77video_unmutes
78view_definition
79viewable_impressions

Amazon DSP impression-level data. Contains impression costs, targeting, creative info, publisher data, and geographic details.

Key Columns

ColumnTypeDescription
campaignSTRINGCampaign name
campaign_id_stringSTRINGCampaign ID
impression_date_utcTIMESTAMPDate/time of the impression in UTC
total_costBIGINTTotal cost in millicents (÷ 100,000 for dollars)
impressionsINTEGERNumber of impressions
creativeSTRINGCreative name
line_itemSTRINGLine item name
device_typeSTRINGDevice type (desktop, mobile, tablet, etc.)
supply_sourceSTRINGWhere the ad was served (e.g., Amazon O&O, 3P exchange)
publisher_nameSTRINGPublisher/site name
city_nameSTRINGCity of the user
postal_codeSTRINGPostal/ZIP code of the user
⚠️ Gotchas
  • total_cost is in MILLICENTS: divide by 100,000 to get dollars
  • impression_day_utc is INTEGER; use impression_date_utc (TIMESTAMP) instead
  • Contains many fee columns (audience_fee, platform_fee, supply_cost, etc.) that are all in millicents
All 106 Columns
#Column
1ad_slot_size
2advertiser
3advertiser_account_frequency_group_ids
4advertiser_id
5advertiser_timezone
6app_bundle
7audience_fee
8bid_price
9browser_family
10campaign
11campaign_budget_amount
12campaign_country
13campaign_end_date
14campaign_end_date_utc
15campaign_flight_budget_amount
16campaign_flight_end_date_utc
17campaign_flight_id
18campaign_flight_name
19campaign_flight_start_date_utc
20campaign_id
21campaign_id_string
22campaign_insertion_order_id
23campaign_primary_goal
24campaign_sales_type
25campaign_source
26campaign_start_date
27campaign_start_date_utc
28campaign_status
29city_name
30creative
31creative_category
32creative_duration
33creative_id
34creative_is_link_in
35creative_size
36creative_type
37currency_iso_code
38currency_name
39deal_id
40deal_name
41demand_channel
42demand_channel_owner
43device_id
44device_make
45device_model
46device_type
47dma_code
48entity_id
49environment_type
50impression_cost
51impression_date
52impression_date_utc
53impression_day
54impression_day_utc
55impression_dt
56impression_dt_hour
57impression_dt_hour_utc
58impression_dt_utc
59impression_hour
60impression_hour_utc
61impressions
62ip_address
63is_amazon_owned
64iso_country_code
65iso_state_province_code
66line_item
67line_item_budget_amount
68line_item_end_date
69line_item_end_date_utc
70line_item_id
71line_item_price_type
72line_item_start_date
73line_item_start_date_utc
74line_item_status
75line_item_type
76managed_service_fee
77manager_account_frequency_group_ids
78matched_behavior_segment_ids
79merchant_id
80no_3p_trackers
81ocm_fee
82operating_system
83os_version
84page_type
85placement_is_view_aware
86placement_view_rate
87platform_fee
88postal_code
89product_line
90publisher_name
91request_tag
92segment_marketplace_id
93seller_id
94seller_name
95site
96slot_position
97supply_cost
98supply_source
99supply_source_id
100supply_source_is_view_aware
101supply_source_view_rate
102third_party_fees
103total_cost
104user_behavior_segment_ids
105user_id
106user_id_type

Amazon DSP click-level data. Nearly identical to dsp_impressions but for click events. Contains click costs, timing, and all the same targeting/creative/geographic dimensions.

Key Columns

ColumnTypeDescription
campaignSTRINGCampaign name
campaign_id_stringSTRINGCampaign ID
click_date_utcTIMESTAMPDate/time of the click in UTC
click_costBIGINTClick cost in millicents (÷ 100,000 for dollars)
clicksINTEGERNumber of clicks
creativeSTRINGCreative name
line_itemSTRINGLine item name
device_typeSTRINGDevice type
supply_sourceSTRINGWhere the ad was served
publisher_nameSTRINGPublisher/site name
⚠️ Gotchas
  • click_cost replaces total_cost from dsp_impressions, also in millicents (÷ 100,000)
  • No impressions column; this is clicks only
  • Has both impression_date_utc AND click_date_utc (the impression that led to the click)
All 105 Columns
#Column
1ad_slot_size
2advertiser
3advertiser_account_frequency_group_ids
4advertiser_id
5advertiser_timezone
6app_bundle
7audience_fee
8bid_price
9browser_family
10campaign
11campaign_budget_amount
12campaign_country
13campaign_end_date
14campaign_end_date_utc
15campaign_flight_budget_amount
16campaign_flight_end_date_utc
17campaign_flight_id
18campaign_flight_name
19campaign_flight_start_date_utc
20campaign_id
21campaign_id_string
22campaign_insertion_order_id
23campaign_primary_goal
24campaign_sales_type
25campaign_source
26campaign_start_date
27campaign_start_date_utc
28campaign_status
29city_name
30click_cost
31click_date
32click_date_utc
33click_day
34click_day_utc
35click_dt
36click_dt_hour
37click_dt_hour_utc
38click_dt_utc
39click_hour
40click_hour_utc
41clicks
42creative
43creative_category
44creative_duration
45creative_id
46creative_is_link_in
47creative_size
48creative_type
49currency_iso_code
50currency_name
51deal_id
52deal_name
53demand_channel
54demand_channel_owner
55device_id
56device_make
57device_model
58device_type
59dma_code
60entity_id
61environment_type
62impression_date
63impression_date_utc
64impression_day
65impression_day_utc
66impression_dt
67impression_dt_hour
68impression_dt_hour_utc
69impression_dt_utc
70impression_hour
71impression_hour_utc
72ip_address
73is_amazon_owned
74iso_country_code
75iso_state_province_code
76line_item
77line_item_budget_amount
78line_item_end_date
79line_item_end_date_utc
80line_item_id
81line_item_price_type
82line_item_start_date
83line_item_start_date_utc
84line_item_status
85line_item_type
86manager_account_frequency_group_ids
87merchant_id
88no_3p_trackers
89operating_system
90os_version
91page_type
92platform_fee
93postal_code
94product_line
95publisher_name
96request_tag
97seller_id
98seller_name
99site
100slot_position
101supply_cost
102supply_source
103supply_source_id
104user_id
105user_id_type

The LARGEST table: attribution/conversion data linking ad events to purchase/conversion outcomes. Contains every possible conversion metric broken down by clicks vs views, plus new-to-brand variants, brand halo metrics, and pixel events. This is where you get ROAS, NTB, and full-funnel attribution.

Key Columns

ColumnTypeDescription
campaignSTRINGCampaign name
campaign_id_stringSTRINGCampaign ID
ad_product_typeSTRINGAd type (sponsored_products, sponsored_brands, sponsored_display; DSP rows are NULL)
tracked_asinSTRINGThe ASIN being tracked for attribution
conversion_event_date_utcTIMESTAMPWhen the conversion happened
purchasesINTEGERTotal attributed purchases (clicks + views)
total_product_salesDECIMALTotal sales including brand halo
product_salesDECIMALSales for the tracked ASIN only
new_to_brandBOOLEANFlag: is this a new-to-brand customer (dimension, not metric)
new_to_brand_purchasesINTEGERPurchases from new-to-brand customers
units_soldINTEGERTotal units sold (clicks + views)
clicksINTEGERClicks on the ad
impressionsINTEGERAd impressions
⚠️ Gotchas
  • Most metric columns come in triplets: metric, metric_clicks, metric_views (e.g., purchases, purchases_clicks, purchases_views)
  • total_product_sales includes brand halo; product_sales is just the tracked ASIN
  • new_to_brand is a flag/dimension, not a metric; filter on it, don't sum it
  • Contains ~140 pixel_* columns for off-Amazon conversion tracking (most will be empty unless you have pixels configured)
All 392 Columns
#Column
1ad_product_type
2ad_slot_size
3add_to_cart
4add_to_cart_clicks
5add_to_cart_views
6advertiser
7advertiser_conversion_definition_dim_id
8advertiser_id
9advertiser_id_internal
10advertiser_timezone
11appstore_first_opens
12appstore_first_opens_clicks
13appstore_first_opens_views
14appstore_first_session_hours
15appstore_first_session_hours_clicks
16appstore_first_session_hours_views
17appstore_opens
18appstore_opens_clicks
19appstore_opens_views
20appstore_repeat_opens
21appstore_repeat_opens_clicks
22appstore_repeat_opens_views
23appstore_repeat_session_hours
24appstore_repeat_session_hours_clicks
25appstore_repeat_session_hours_views
26appstore_usage_hours
27appstore_usage_hours_clicks
28appstore_usage_hours_views
29audience_fee
30bid_price
31brand_halo_add_to_cart
32brand_halo_add_to_cart_clicks
33brand_halo_add_to_cart_views
34brand_halo_detail_page_view
35brand_halo_detail_page_view_clicks
36brand_halo_detail_page_view_views
37brand_halo_product_sales
38brand_halo_product_sales_clicks
39brand_halo_product_sales_views
40brand_halo_purchases
41brand_halo_purchases_clicks
42brand_halo_purchases_views
43brand_halo_units_sold
44brand_halo_units_sold_clicks
45brand_halo_units_sold_views
46browser_family
47campaign
48campaign_budget_amount
49campaign_end_date
50campaign_end_date_utc
51campaign_end_dt
52campaign_end_dt_utc
53campaign_flight_budget_amount
54campaign_flight_end_date_utc
55campaign_flight_id
56campaign_flight_name
57campaign_flight_start_date_utc
58campaign_id
59campaign_id_string
60campaign_insertion_order_id
61campaign_primary_goal
62campaign_start_date
63campaign_start_date_utc
64campaign_status
65city_name
66click_cost
67click_date
68click_date_utc
69click_day
70click_day_utc
71click_dt
72click_dt_hour
73click_dt_hour_utc
74click_dt_utc
75click_hour
76click_hour_utc
77clicks
78combined_sales
79conversion_event_category
80conversion_event_date
81conversion_event_date_utc
82conversion_event_dt
83conversion_event_dt_hour
84conversion_event_dt_hour_utc
85conversion_event_dt_utc
86conversion_event_hour
87conversion_event_hour_utc
88conversion_event_marketplace_id
89conversion_event_marketplace_name
90conversion_event_name
91conversion_event_source_name
92conversion_event_subtype
93conversion_event_type
94conversion_event_type_class
95conversion_event_type_description
96conversions
97creative
98creative_category
99creative_duration
100creative_id
101creative_is_link_in
102creative_size
103creative_type
104customer_search_term
105deal_id
106deal_name
107detail_page_view
108detail_page_view_clicks
109detail_page_view_views
110device_make
111device_model
112device_type
113digital_app_subscription_signup_purchases
114digital_app_subscription_signup_purchases_clicks
115digital_app_subscription_signup_purchases_views
116digital_free_trial_app_subscription_signup_purchases
117digital_free_trial_app_subscription_signup_purchases_clicks
118digital_free_trial_app_subscription_signup_purchases_views
119digital_free_trial_subscription_signup_purchases
120digital_free_trial_subscription_signup_purchases_clicks
121digital_free_trial_subscription_signup_purchases_views
122digital_paid_app_subscription_signup_purchases
123digital_paid_app_subscription_signup_purchases_clicks
124digital_paid_app_subscription_signup_purchases_views
125digital_paid_subscription_signup_purchases
126digital_paid_subscription_signup_purchases_clicks
127digital_paid_subscription_signup_purchases_views
128digital_subscription_signup_purchases
129digital_subscription_signup_purchases_clicks
130digital_subscription_signup_purchases_views
131dma_code
132entity_id
133environment_type
134impression_date
135impression_date_utc
136impression_day
137impression_day_utc
138impression_dt
139impression_dt_hour
140impression_dt_hour_utc
141impression_dt_utc
142impression_hour
143impression_hour_utc
144impressions
145is_amazon_business
146is_event_type_modeled_view
147iso_country_code
148iso_state_province_code
149line_item
150line_item_budget_amount
151line_item_end_date
152line_item_end_date_utc
153line_item_id
154line_item_price_type
155line_item_start_date
156line_item_start_date_utc
157line_item_status
158line_item_type
159match_type
160new_to_brand
161new_to_brand_brand_halo_product_sales
162new_to_brand_brand_halo_product_sales_clicks
163new_to_brand_brand_halo_product_sales_views
164new_to_brand_brand_halo_purchases
165new_to_brand_brand_halo_purchases_clicks
166new_to_brand_brand_halo_purchases_views
167new_to_brand_brand_halo_units_sold
168new_to_brand_brand_halo_units_sold_clicks
169new_to_brand_brand_halo_units_sold_views
170new_to_brand_product_sales
171new_to_brand_product_sales_clicks
172new_to_brand_product_sales_views
173new_to_brand_purchases
174new_to_brand_purchases_clicks
175new_to_brand_purchases_views
176new_to_brand_total_product_sales
177new_to_brand_total_product_sales_clicks
178new_to_brand_total_product_sales_views
179new_to_brand_total_purchases
180new_to_brand_total_purchases_clicks
181new_to_brand_total_purchases_views
182new_to_brand_total_units_sold
183new_to_brand_total_units_sold_clicks
184new_to_brand_total_units_sold_views
185new_to_brand_units_sold
186new_to_brand_units_sold_clicks
187new_to_brand_units_sold_views
188no_3p_trackers
189off_amazon_conversion_value
190off_amazon_product_sales
191operating_system
192os_version
193page_type
194pixel_accept
195pixel_accept_clicks
196pixel_accept_views
197pixel_add_to_shopping_cart
198pixel_add_to_shopping_cart_clicks
199pixel_add_to_shopping_cart_views
200pixel_application
201pixel_application_clicks
202pixel_application_views
203pixel_banner_interaction
204pixel_banner_interaction_clicks
205pixel_banner_interaction_views
206pixel_brand_store_engagement_1
207pixel_brand_store_engagement_1_clicks
208pixel_brand_store_engagement_1_views
209pixel_brand_store_engagement_2
210pixel_brand_store_engagement_2_clicks
211pixel_brand_store_engagement_2_views
212pixel_brand_store_engagement_3
213pixel_brand_store_engagement_3_clicks
214pixel_brand_store_engagement_3_views
215pixel_brand_store_engagement_4
216pixel_brand_store_engagement_4_clicks
217pixel_brand_store_engagement_4_views
218pixel_brand_store_engagement_5
219pixel_brand_store_engagement_5_clicks
220pixel_brand_store_engagement_5_views
221pixel_brand_store_engagement_6
222pixel_brand_store_engagement_6_clicks
223pixel_brand_store_engagement_6_views
224pixel_brand_store_engagement_7
225pixel_brand_store_engagement_7_clicks
226pixel_brand_store_engagement_7_views
227pixel_click_on_redirect
228pixel_click_on_redirect_clicks
229pixel_click_on_redirect_views
230pixel_decline
231pixel_decline_clicks
232pixel_decline_views
233pixel_drop_down_selection
234pixel_drop_down_selection_clicks
235pixel_drop_down_selection_views
236pixel_email_interaction
237pixel_email_interaction_clicks
238pixel_email_interaction_views
239pixel_email_load
240pixel_email_load_clicks
241pixel_email_load_views
242pixel_game_interaction
243pixel_game_interaction_clicks
244pixel_game_interaction_views
245pixel_game_load
246pixel_game_load_clicks
247pixel_game_load_views
248pixel_homepage_visit
249pixel_homepage_visit_clicks
250pixel_homepage_visit_views
251pixel_marketing_landing_page
252pixel_marketing_landing_page_clicks
253pixel_marketing_landing_page_views
254pixel_mashup_add_to_cart
255pixel_mashup_add_to_cart_clicks
256pixel_mashup_add_to_cart_views
257pixel_mashup_add_to_wishlist
258pixel_mashup_add_to_wishlist_clicks
259pixel_mashup_add_to_wishlist_views
260pixel_mashup_backup_image
261pixel_mashup_backup_image_clicks
262pixel_mashup_backup_image_views
263pixel_mashup_click_to_page
264pixel_mashup_click_to_page_clicks
265pixel_mashup_click_to_page_views
266pixel_mashup_clip_coupon
267pixel_mashup_clip_coupon_clicks
268pixel_mashup_clip_coupon_views
269pixel_mashup_shop_now
270pixel_mashup_shop_now_clicks
271pixel_mashup_shop_now_views
272pixel_mashup_subscribe_and_save
273pixel_mashup_subscribe_and_save_clicks
274pixel_mashup_subscribe_and_save_views
275pixel_message_sent
276pixel_message_sent_clicks
277pixel_message_sent_views
278pixel_mobile_app_first_start
279pixel_mobile_app_first_start_clicks
280pixel_mobile_app_first_start_views
281pixel_product_purchased
282pixel_product_purchased_clicks
283pixel_product_purchased_views
284pixel_purchase_button
285pixel_purchase_button_clicks
286pixel_purchase_button_views
287pixel_referral
288pixel_referral_clicks
289pixel_referral_views
290pixel_registration_confirm_page
291pixel_registration_confirm_page_clicks
292pixel_registration_confirm_page_views
293pixel_registration_form
294pixel_registration_form_clicks
295pixel_registration_form_views
296pixel_sign_up_button
297pixel_sign_up_button_clicks
298pixel_sign_up_button_views
299pixel_sign_up_page
300pixel_sign_up_page_clicks
301pixel_sign_up_page_views
302pixel_store_locator_page
303pixel_store_locator_page_clicks
304pixel_store_locator_page_views
305pixel_submit_button
306pixel_submit_button_clicks
307pixel_submit_button_views
308pixel_subscription_button
309pixel_subscription_button_clicks
310pixel_subscription_button_views
311pixel_subscription_page
312pixel_subscription_page_clicks
313pixel_subscription_page_views
314pixel_success_page
315pixel_success_page_clicks
316pixel_success_page_views
317pixel_survey_finish
318pixel_survey_finish_clicks
319pixel_survey_finish_views
320pixel_survey_start
321pixel_survey_start_clicks
322pixel_survey_start_views
323pixel_thank_you_page
324pixel_thank_you_page_clicks
325pixel_thank_you_page_views
326pixel_video_end
327pixel_video_end_clicks
328pixel_video_end_views
329pixel_video_started
330pixel_video_started_clicks
331pixel_video_started_views
332pixel_widget_interaction
333pixel_widget_interaction_clicks
334pixel_widget_interaction_views
335pixel_widget_load
336pixel_widget_load_clicks
337pixel_widget_load_views
338placement_type
339platform_fee
340postal_code
341product_line
342product_sales
343product_sales_clicks
344product_sales_views
345purchase_currency
346purchase_price
347purchase_quantity
348purchase_retail_program
349purchase_unit_price
350purchases
351purchases_clicks
352purchases_views
353request_tag
354site
355supply_cost
356supply_source
357targeting
358total_add_to_cart
359total_add_to_cart_clicks
360total_add_to_cart_views
361total_detail_page_view
362total_detail_page_view_clicks
363total_detail_page_view_views
364total_product_sales
365total_product_sales_clicks
366total_product_sales_views
367total_purchases
368total_purchases_clicks
369total_purchases_views
370total_units_sold
371total_units_sold_clicks
372total_units_sold_views
373tracked_asin
374tracked_item
375traffic_event_date
376traffic_event_date_utc
377traffic_event_dt
378traffic_event_dt_hour
379traffic_event_dt_hour_utc
380traffic_event_dt_utc
381traffic_event_hour
382traffic_event_hour_utc
383traffic_event_id
384traffic_event_marketplace_id
385traffic_event_marketplace_name
386traffic_event_subtype
387traffic_event_type
388units_sold
389units_sold_clicks
390units_sold_views
391user_id
392user_id_type

All conversion events in a unified table. Includes purchase events, Subscribe & Save subscription data, and various conversion types across the funnel.

Key Columns

ColumnTypeDescription
event_date_utcTIMESTAMPDate/time of the conversion event
event_typeSTRINGType of conversion event
event_subtypeSTRINGSubtype for more granular classification
conversion_event_nameSTRINGName of the conversion event
total_product_salesDECIMALTotal product sales amount
total_units_soldINTEGERTotal units sold
new_to_brandBOOLEANWhether this is a new-to-brand customer
tracked_asinSTRINGThe tracked ASIN
user_idSTRINGHashed user identifier
exposure_typeSTRINGHow the user was exposed (click vs view)
purchase_retail_programSTRINGRetail program (includes S&S identifiers)
sns_subscription_idSTRINGSubscribe & Save subscription ID
🔴 Gotchas
  • S&S data is HERE (via purchase_retail_program and sns_subscription_id), NOT in amazon_retail_purchases
  • event_day_utc is INTEGER; use event_date_utc (TIMESTAMP)
  • combined_sales may differ from total_product_sales; check which metric you need
All 33 Columns
#Column
1combined_sales
2conversion_event_name
3conversion_event_source_name
4conversion_id
5conversions
6event_category
7event_date_utc
8event_day_utc
9event_dt_hour_utc
10event_dt_utc
11event_hour_utc
12event_subtype
13event_type
14event_type_class
15event_type_description
16exposure_type
17ip_address
18marketplace_id
19marketplace_name
20new_to_brand
21no_3p_trackers
22off_amazon_conversion_value
23off_amazon_product_sales
24purchase_currency
25purchase_retail_program
26purchase_unit_price
27sns_subscription_id
28total_product_sales
29total_units_sold
30tracked_asin
31tracked_item
32user_id
33user_id_type

Traffic data from Amazon Live streams. Contains impression, click, and view metrics for live broadcast events.

Key Columns

ColumnTypeDescription
broadcast_nameSTRINGName of the live broadcast
broadcast_idSTRINGUnique broadcast identifier
event_date_utcTIMESTAMPDate/time of the event
impressionsINTEGERNumber of impressions
clicksINTEGERNumber of clicks
viewsINTEGERNumber of video views
creative_asinSTRINGASIN featured in the broadcast
user_idSTRINGHashed user identifier
⚠️ Gotchas
  • Smallest table, only relevant if you use Amazon Live
  • event_day_utc may be INTEGER; use event_date_utc for date operations
All 20 Columns
#Column
1ad_product_type
2advertiser
3broadcast_date_utc
4broadcast_id
5broadcast_name
6clicks
7creative_asin
8creative_id
9event_date_utc
10event_day_utc
11event_dt_hour_utc
12event_dt_utc
13event_hour_utc
14event_id
15impressions
16site
17slot_name
18user_id
19user_id_type
20views

Beyond the 7 core tables above, AMC includes several additional tables and variants that may be available in your instance:

TableDescriptionAccess
amazon_attributed_events_by_traffic_timeSame structure as _by_conversion_time (~392 cols), but organized by the time of the ad exposure (impression/click) rather than the conversion event. Use when you want to attribute conversions back to when the ad was shown.Default
amazon_attributed_events_products_1dProduct-level attribution with a 1-day lookback windowDefault
amazon_attributed_events_products_7dProduct-level attribution with a 7-day lookback windowDefault
amazon_attributed_events_products_14dProduct-level attribution with a 14-day lookback windowDefault
amazon_attributed_events_products_30dProduct-level attribution with a 30-day lookback windowDefault
dsp_viewsDSP viewability and video view eventsDefault (DSP)
dsp_impressions_with_conversions_1dPre-joined DSP impressions with 1-day conversion data for faster queriesDefault (DSP)
💡 Pro Tip

The _by_traffic_time vs _by_conversion_time distinction matters for reporting alignment. Use conversion time when you want to see "what converted this month" and traffic time when you want to see "what did my ads this month ultimately drive." Most weekly/monthly reports use conversion time.

Amazon offers paid subscriptions that unlock additional data within your AMC instance. These appear in the AMC UI under "Paid Features" and can be activated there or through your rep. Amazon has made its first-party paid features, including Amazon Retail Purchases, free to subscribe through December 31, 2026:

SubscriptionWhat It UnlocksEligibility
Amazon InsightsA collection of Amazon-published datasets. Includes access to Amazon Flexible Shopping Insights (FSI) and Amazon audience segment membership. Using FSI, generate combined advertising and shopping insights for products you advertise. Enables segment analysis across Sponsored Ads traffic events, non-ad-exposed conversion events, and advertiser uploaded signals.Sellers, vendor brand owners
Amazon Retail PurchasesProvides asin-level purchase event records across Amazon retail marketplaces with an extended data retention policy. Join with advertising reporting to gain a more holistic perspective on purchase activities. Generate combined advertiser and audience segment insights for measurement, activation, and planning. Enables audience overlap analysis across advertising traffic and advertiser uploaded signals. Contains 5 years of event records.Sellers, vendor brand owners
Amazon Your GarageEnables Amazon marketplace customers (within supported markets) to add vehicles to their Amazon Your Garage account to expedite product discovery and configure service recommendations. Conduct segment analysis across Amazon Advertising and Amazon Your Garage signals for a more holistic perspective on marketing activities.Supported markets only
Brand Store InsightsWeb and engagement metrics for Amazon Stores. Metrics include page views, impressions, and clicks. Understand the impact of your Brand Store by joining with traffic events to understand the impact of ad traffic on store visits, and by joining with conversion events to understand the impact of brand stores on lower-funnel conversions.At least 1 active Brand Store
Experian Vehicle Purchase InsightsInsight into US vehicle purchases, refreshed on a monthly cadence. Measure the impact of your Amazon Ads campaigns on vehicle purchases, understand the purchase journey, and discover new audiences to reach. 12.5 month lookback, 2 month delay.Contact Amazon Ads rep
NCS CPG Insights StreamModeled offline transaction signals for all US households. Transaction signals (dollars and units) aggregated at the weekly household level for specified product categories. Acquired from a diverse and balanced set of brick-and-mortar stores covering over 3 million UPCs. Understand cross-channel purchasing journeys and discover audiences with different purchasing patterns online and offline. 12.5 month lookback, 19 day delay, refreshed weekly (Fridays).Contact Amazon Ads rep
Foursquare Store VisitPhysical foot traffic attribution: measure whether ad-exposed users visited a brick-and-mortar store. Requires Foursquare integration.Contact Amazon Ads rep
🔑 Key Insight

Many features people expect as "tables" (like branded search data, organic traffic, or total traffic) are actually either: (a) fields within the existing attribution tables, (b) behind the paid Flexible Shopping Insights subscription, or (c) available through the AMC Schema Explorer but not yet documented publicly. Amazon does not publish a single master list of all AMC tables; the Schema Explorer in the AMC UI is the definitive source for what's available in your specific instance.

If you need tables beyond the defaults:

  • Check your Schema Explorer: In the AMC UI, the Schema Explorer shows every table available in your instance with full column documentation
  • Contact your Amazon Ads representative: Some tables require manual enablement (especially DSP-related tables if you're new to DSP)
  • Subscribe to paid features: Flexible Shopping Insights and other paid add-ons can be activated through your Amazon Ads console or rep
  • Regional availability: Some tables are marketplace-specific. Tables available in the US may not be available in EU or JP instances
💡 Pro Tip

When working with AI tools to write AMC queries, always provide the actual table schemas from your Schema Explorer. Tables and columns can vary between instances based on your advertiser type, enabled features, and marketplace.

✦ ADLABS FEATURE

Multi-Marketplace Management

Manage US, CA, UK, and EU campaigns from one dashboard. Same Automations and Optimization Groups, unified reporting across all marketplaces, so your AMC insights translate into action everywhere you sell.

Try Free →
AdLabs multi-marketplace reporting dashboard

AMC queries run against massive datasets with strict timeout limits. Understanding how the query engine processes your SQL, and structuring queries accordingly, is the difference between a 30-second result and a timeout error.

SQL doesn't execute in the order you write it. Understanding the actual execution order helps you optimize where it matters most:

AMC SQL Execution Order
1. FROM    →   2. WHERE    →   3. JOIN

4. GROUP BY    →   5. HAVING    →   6. SELECT

Note: AMC does not support ORDER BY (outside window functions) or LIMIT. Sort your results post-export.

Why this matters: Filters in WHERE execute before joins and aggregations. The earlier you reduce data volume, the faster everything downstream runs. A filter in HAVING runs after the expensive GROUP BY, so move it to WHERE if possible.

A sargable (Search ARGument ABLE) predicate allows the query engine to use indexes and skip irrelevant data. A non-sargable predicate forces the engine to read every single record.

❌ Non-Sargable: Forces Full Table Scan
Wrapping a column in a function prevents the engine from using efficient lookups. This reads ALL records just to check each one:
❌ Bad: Function in WHERE Clause
SELECT *
FROM sponsored_ads_traffic
WHERE ARRAY_CONTAINS(
  ARRAY['1234567', '2345678'],
  campaign_id_string
)

Instead, use a CTE with an INNER JOIN or IN clause. Both are sargable and let the engine skip non-matching records:

✅ Good: CTE + INNER JOIN
WITH target_campaigns AS (
  SELECT '1234567' AS campaign_id_string
  UNION ALL
  SELECT '2345678'
)
SELECT sat.*
FROM sponsored_ads_traffic sat
INNER JOIN target_campaigns tc
  ON sat.campaign_id_string = tc.campaign_id_string
✅ Also Good: IN Clause
SELECT *
FROM sponsored_ads_traffic
WHERE campaign_id_string IN ('1234567', '2345678')

When you need to combine data from multiple tables but don't need row-by-row matching, UNION ALL is significantly cheaper than a JOIN.

When to use each:
  • UNION ALL: You want to stack rows vertically from similar tables (e.g., combining Sponsored Ads spend + DSP spend into one list). No matching logic needed.
  • JOIN: You need to match rows across tables on a shared key (e.g., linking impressions to conversions by user_id).
UNION ALL simply appends rows. JOIN compares every row combination. If you don't need matching, don't pay for it.

The order you join tables matters. Start with the smallest table and join outward to reduce the intermediate result set at every step.

  • Join smallest table first: reduces the working set early
  • Use EQUI-JOINs (=): equality joins are far more efficient than range or inequality joins
  • Include ON clause at proper granularity: join on the most specific key available
Join Order Example
-- t1 = 10M rows, t2 = 1M rows, t3 = 100 rows
-- ❌ Bad: Start with the biggest table
-- FROM t1 JOIN t2 ... JOIN t3 ...

-- ✅ Good: Start with the smallest table
FROM t3
INNER JOIN t2 ON t3.key = t2.key
INNER JOIN t1 ON t2.key = t1.key

A cartesian join (cross join) happens when every row in one table matches every row in another. This can happen accidentally when your join key isn't at the right granularity.

❌ Hidden Cartesian: Wrong Join Granularity
If you join two tables on user_id but each table has multiple rows per user (e.g., multiple campaigns), you get a cartesian product within each user, massively inflating your result set.
❌ Problem: Join Creates Duplicates
-- User A has 5 campaigns in table_1 and 3 campaigns in table_2
-- Joining on user_id produces 5 × 3 = 15 rows for User A!

SELECT *
FROM table_1 t1
JOIN table_2 t2 ON t1.user_id = t2.user_id
✅ Fix: Aggregate Before Joining or Add Join Keys
-- Option 1: Aggregate first
WITH t1_agg AS (
  SELECT user_id, SUM(revenue) AS total_revenue
  FROM table_1 GROUP BY user_id
)
SELECT * FROM t1_agg
JOIN table_2 t2 ON t1_agg.user_id = t2.user_id

-- Option 2: Join at the correct granularity
SELECT * FROM table_1 t1
JOIN table_2 t2
  ON t1.user_id = t2.user_id
  AND t1.campaign_id = t2.campaign_id

Every calculation inside AMC burns compute time. Delay aggregation functions until the final SELECT, and move derived metrics outside AMC entirely when possible.

Move calculations outside AMC. Instead of computing CPM, ROAS, or purchase rate inside your query, export the raw components (spend, impressions, revenue, purchases) and calculate the ratios in your reporting tool. This reduces AMC compute time and gives you more flexibility to recalculate later.

One of the most common performance killers is carrying too much detail through your CTEs when you don't need it.

Aggregate CTEs as early as possible:
  • Use GROUP BY instead of DISTINCT: GROUP BY lets you aggregate in the same step, while DISTINCT just deduplicates without reducing columns.
  • Don't SELECT columns you won't use later: every extra column in a CTE increases memory usage and slows downstream operations.
  • Aggregate at the CTE level, not the final query: if a CTE feeds into a JOIN, pre-aggregate it to reduce the join's row count.

Every CTE that touches a base table should have a WHERE clause. The more restrictive your filters, the less data the engine has to process.

Add WHERE clauses to every CTE:
  • Filter by date range in every CTE, not just the final query
  • Use the most restrictive filters possible: filter by campaign, ASIN, or ad product type if your analysis is scoped
  • Add predicates at the earliest possible stage: a filter in CTE #1 reduces data for CTE #2, #3, and beyond

If your query still isn't performing well after applying these techniques, follow this escalation path:

  1. Optimize your query: apply the techniques above (filter early, aggregate CTEs, fix join order)
  2. Reduce the date range: try 1 month instead of 12, or 2 weeks instead of 1 month
  3. Submit a support ticket: if the query is optimized and still timing out, contact AMC support with your query and error details

Reusable query patterns that serve as starting points for common AMC analyses. Copy, modify, and combine these templates for your specific needs. For fully tested, production-ready queries with sample output, see the AMC Query Library.

The most common starting point: aggregate purchases by month and ASIN to see revenue trends and customer counts.

Monthly Aggregation
SELECT
  DATE_TRUNC('MONTH', purchase_date_utc) AS purchase_month,
  asin,
  COUNT(DISTINCT user_id) AS unique_customers,
  SUM(unit_price * purchase_units_sold) AS revenue,
  SUM(purchase_units_sold) AS units
FROM amazon_retail_purchases
GROUP BY 1, 2

Track how customer cohorts (grouped by their first purchase month) generate revenue over time. Essential for LTV analysis.

Customer Cohort Analysis
WITH first_purchase AS (
  SELECT
    user_id,
    MIN(DATE_TRUNC('MONTH', purchase_date_utc)) AS cohort_month
  FROM amazon_retail_purchases
  GROUP BY user_id
)
SELECT
  fp.cohort_month,
  DATE_TRUNC('MONTH', rp.purchase_date_utc) AS revenue_month,
  COUNT(DISTINCT rp.user_id) AS active_customers,
  SUM(rp.unit_price * rp.purchase_units_sold) AS revenue
FROM amazon_retail_purchases rp
INNER JOIN first_purchase fp ON rp.user_id = fp.user_id
GROUP BY 1, 2

Classify new customers by whether they interacted with any ads before their first purchase. Useful for understanding paid vs organic acquisition.

Ad-Touched vs Organic Classification
WITH first_purchase AS (
  SELECT user_id,
    MIN(DATE_TRUNC('MONTH', purchase_date_utc)) AS cohort_month
  FROM amazon_retail_purchases
  GROUP BY user_id
),
ad_touched AS (
  SELECT user_id, 1 AS is_paid
  FROM sponsored_ads_traffic WHERE clicks > 0
  GROUP BY user_id
  UNION
  SELECT user_id, 1 AS is_paid
  FROM dsp_clicks WHERE clicks > 0
  GROUP BY user_id
)
SELECT
  fp.cohort_month,
  CASE WHEN adtch.user_id IS NOT NULL
    THEN 'paid_touched'
    ELSE 'organic_only'
  END AS acquisition_type,
  COUNT(DISTINCT fp.user_id) AS new_customers
FROM first_purchase fp
LEFT JOIN ad_touched adtch ON fp.user_id = adtch.user_id
GROUP BY 1, 2

Discover which products customers buy second after their first purchase. Reveals natural cross-sell opportunities for bundling or sequential targeting.

Cross-Sell Path Analysis
WITH ranked AS (
  SELECT user_id, asin, purchase_day_utc,
    ROW_NUMBER() OVER (
      PARTITION BY user_id
      ORDER BY purchase_day_utc, asin
    ) AS rn
  FROM amazon_retail_purchases
)
SELECT
  r1.asin AS first_asin,
  r2.asin AS second_asin,
  COUNT(DISTINCT r1.user_id) AS customer_count
FROM ranked r1
INNER JOIN ranked r2
  ON r1.user_id = r2.user_id AND r2.rn = 2
WHERE r1.rn = 1
GROUP BY 1, 2
HAVING COUNT(DISTINCT r1.user_id) >= 10

Convert raw spend values to dollars. Remember: Sponsored Ads stores spend in microcents (÷ 100,000,000) and DSP stores cost in millicents (÷ 100,000).

Sponsored Ads Spend (Microcents → Dollars)
SELECT
  DATE_TRUNC('MONTH', event_date_utc) AS spend_month,
  SUM(spend) / 100000000.0 AS ad_spend_dollars
FROM sponsored_ads_traffic
GROUP BY 1
DSP Spend (Millicents → Dollars)
SELECT
  DATE_TRUNC('MONTH', impression_date_utc) AS spend_month,
  SUM(total_cost) / 100000.0 AS dsp_spend_dollars
FROM dsp_impressions
GROUP BY 1

Pull new-to-brand metrics from the attributed events table. This is the go-to template for NTB reporting by campaign and ASIN.

New-to-Brand Classification
SELECT
  DATE_TRUNC('MONTH',
    CAST(conversion_event_date_utc AS TIMESTAMP)
  ) AS conversion_month,
  ad_product_type,
  campaign,
  tracked_asin,
  new_to_brand,
  COUNT(DISTINCT user_id) AS customers,
  SUM(purchases) AS purchases,
  SUM(total_product_sales) AS attributed_revenue,
  SUM(new_to_brand_purchases) AS ntb_purchases,
  SUM(new_to_brand_total_product_sales) AS ntb_revenue
FROM amazon_attributed_events_by_conversion_time
WHERE purchases > 0
GROUP BY 1, 2, 3, 4, 5

A dedicated collection of every error, trap, and "gotcha" we've encountered working with AMC. Bookmark this section; it'll save you hours of debugging.

🚩 RED FLAG: purchase_month_utc returns the month number (1–12) without the year. If you GROUP BY purchase_month_utc, January 2025 and January 2026 both become "1" and merge together silently.

✅ FIX: Use DATE_TRUNC('MONTH', purchase_date_utc) instead. This returns a full timestamp like 2026-01-01 that preserves the year.
⚠️ MISTAKE: Using event_day_utc or impression_day_utc in date operations like DATE_TRUNC. They're INTEGER fields (e.g., 20260115), not timestamps. Date functions will throw errors or return wrong results.

✅ FIX: Use the timestamp versions: event_date_utc / impression_date_utc.
⚠️ MISTAKE: Using month or at as column aliases. They're SQL reserved words. Your query will fail with a cryptic syntax error.

✅ FIX: Use descriptive aliases like spend_month, purchase_month, or ad_touched. If you must use a reserved word, wrap it in double quotes: "month".
🚩 RED FLAG: amazon_retail_purchases.purchase_program_name returns EMPTY for Subscribe & Save orders. If you're filtering or grouping by this field, S&S revenue silently disappears from your results.

✅ FIX: S&S data lives in the conversions_all table. Use the purchase_retail_program and sns_subscription_id fields to identify and segment Subscribe & Save orders.
⚠️ Query keeps timing out? Try these fixes in order:
  1. Reduce date range: go from 12 months → 1 month → 2 weeks to isolate the issue
  2. Optimize CTEs: aggregate as early as possible to reduce row counts
  3. Remove unnecessary columns: don't SELECT columns in CTEs that you won't use later
  4. Use INNER JOIN instead of LEFT JOIN: where your logic allows, INNER JOINs are faster because they eliminate non-matching rows
🚩 RED FLAG: Rows are missing or come back with blank values. AMC enforces privacy minimums: if a group in your GROUP BY has too few unique users, AMC silently drops that row (or blanks some of its values) instead of returning an error.

✅ FIX: Broaden your grouping dimensions (e.g., remove ASIN-level granularity) or increase the date range so each group contains more records. You can also try removing high-cardinality dimensions (like search term) from GROUP BY.
⚠️ Query returns 0 rows? Check these common causes:
  • Wrong date range in the AMC UI: the UI date range limits the data first, so SQL date filters outside it return nothing
  • Filtering on a value that doesn't exist: double-check campaign IDs, ASINs, or string values
  • Using WHERE purchases > 0: if no conversions occurred in the date range, every row gets filtered out
  • INNER JOIN with no matching records: if one side of the join is empty, the entire result is empty
  • Case sensitivity in string comparisons: AMC string comparisons are case-sensitive; 'SP' ≠ 'sp'
Debugging tip: Start by running each CTE independently (as a standalone query) to verify it returns data. Then add joins one at a time to find where rows disappear.

Written by

AdLabs team

Amazon Ads Verified Partner

Amazon PPC software for brands and agencies, used by 6,500+ advertisers.