# The Complete AMC Reference Guide

> Everything you need to understand Amazon Marketing Cloud and write error-proof SQL queries, whether you're a human or an AI.

Source: https://adlabs.app/guides/amc-reference-guide/ (updated 2026-10-19)

1 What Is AMC? 

**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](https://adlabs.app/guides/amazon-dsp-amc-guide/), 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.

What Is AMC For? 

* **Cross-channel analytics**: See how Sponsored Products, [Sponsored Brands](https://adlabs.app/guides/amazon-sponsored-brands-guide/), 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

Who Uses It? 

* **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

What Makes AMC Different from Standard Amazon Reporting? 

| 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) |

The Privacy Model 

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. 

2 AMC SQL: What's Different 

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. 

No ORDER BY 

**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

No SELECT \* 

**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'

No RIGHT JOIN 

**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

No Correlated Subqueries 

**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

Aggregation Thresholds 

**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

Reserved Words Must Be Aliased 

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

Broken Fields: purchase\_month\_utc 

**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)

Integer Date Fields: event\_day\_utc & impression\_day\_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`. 

Unit Conversions: Spend & Cost 

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

Date Range: Set in the UI 

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

EXTEND\_TIME\_WINDOW: Going Beyond the Reporting Window 

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. 

3 SQL Basics Reference 

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 & DISTINCT 

`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 

`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

JOIN Types 

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. 

JOIN vs UNION 

| 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 

`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 

`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 

`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 

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

VALUES: Inline Data in CTEs 

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

REGEX Patterns 

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. 

Common Table Expressions (CTEs) 

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

4 Functions Reference 

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

String Functions 

| Function            | Description                                                            | Syntax                                                       |                                |                                 |
| ------------------- | ---------------------------------------------------------------------- | ------------------------------------------------------------ | ------------------------------ | ------------------------------- |
| **CHARINDEX**       | Searches for a substring within a string and returns starting position | CHARINDEX(substring, string \[, start\_position\])           |                                |                                 |
| **CONCAT**          | Concatenates multiple strings                                          | CONCAT(expression \[, expression\]\*)                        |                                |                                 |
| **FORMAT**          | Formats a value according to a pattern                                 | FORMAT(string\_expression, format\_string)                   |                                |                                 |
| **INSTR**           | Finds first occurrence position of substring                           | INSTR(string, substring)                                     |                                |                                 |
| **LEFT**            | Extracts N characters from left side                                   | LEFT(string, length)                                         |                                |                                 |
| **LENGTH**          | Number of characters in string                                         | LENGTH(string)                                               |                                |                                 |
| **LIKE**            | Matches column to string with wildcards                                | column LIKE expression                                       |                                |                                 |
| **LOCATE**          | Alias of CHARINDEX                                                     | LOCATE(substring, string\[, start\_position\])               |                                |                                 |
| **LOWER**           | Converts to lowercase                                                  | LOWER(string)                                                |                                |                                 |
| **LTRIM**           | Removes leading whitespace                                             | LTRIM(string)                                                |                                |                                 |
| **POSITION**        | Finds first occurrence                                                 | POSITION(substring IN string)                                |                                |                                 |
| **REGEXP\_REPLACE** | Replace text matching regex                                            | REGEXP\_REPLACE(string, pattern, replacement)                |                                |                                 |
| **REGEXP\_SUBSTR**  | Extract substring matching regex                                       | REGEXP\_SUBSTR(string, pattern)                              |                                |                                 |
| **REGEXP\_SPLIT**   | Splits string by regex                                                 | REGEXP\_SPLIT(string, pattern)                               |                                |                                 |
| **REPLACE**         | Substitutes all occurrences                                            | REPLACE(string, search, replacement)                         |                                |                                 |
| **RIGHT**           | Extracts N characters from right                                       | RIGHT(string, length)                                        |                                |                                 |
| **RTRIM**           | Removes trailing whitespace                                            | RTRIM(string)                                                |                                |                                 |
| **SIMILAR TO**      | Evaluates regex match (Java regex syntax)                              | expression \[NOT\] SIMILAR TO expression                     |                                |                                 |
| **STRPOS**          | Position of substring                                                  | STRPOS(string, substring)                                    |                                |                                 |
| **STR\_TO\_MAP**    | Converts delimited string to key-value map                             | STR\_TO\_MAP(string, pair\_delimiter, key\_value\_delimiter) |                                |                                 |
| **SUBSTRING**       | Extracts portion of string                                             | SUBSTRING(string FROM start \[FOR length\])                  |                                |                                 |
| **TRIM**            | Removes space/characters from start/end                                | TRIM(str) \| TRIM(BOTH trimStr FROM str)                     | TRIM(LEADING trimStr FROM str) | TRIM(TRAILING trimStr FROM str) |
| **UPPER**           | Converts to uppercase                                                  | UPPER(string)                                                |                                |                                 |

Math Functions 

| Function   | Description                                 | Syntax                          |
| ---------- | ------------------------------------------- | ------------------------------- |
| **ABS**    | Absolute value                              | ABS(numeric\_expression)        |
| **CEIL**   | Rounds up to nearest integer                | CEIL(number)                    |
| **E()**    | Returns mathematical constant e (\~2.71828) | E()                             |
| **EXP**    | e raised to power                           | EXP(number)                     |
| **FLOOR**  | Rounds down to nearest integer              | FLOOR(number)                   |
| **LN**     | Natural logarithm                           | LN(number)                      |
| **LOG**    | Logarithm with specified base               | LOG(base, number)               |
| **LOG10**  | Base-10 logarithm                           | LOG10(number)                   |
| **POWER**  | Raises number to power                      | POWER(number, power)            |
| **RANDOM** | Random decimal 0-1                          | RANDOM()                        |
| **ROUND**  | Rounds to decimal places                    | ROUND(expression \[, integer\]) |
| **SQRT**   | Square root                                 | SQRT(number)                    |
| **TRUNC**  | Truncates without rounding                  | TRUNC(number\[, decimals\])     |

Aggregate Functions 

| Function  | Description            | Syntax                                              |
| --------- | ---------------------- | --------------------------------------------------- |
| **AVG**   | Average of values      | AVG(column\_name)                                   |
| **COUNT** | Total number of values | COUNT(column\_name) \| COUNT(DISTINCT column\_name) |
| **MIN**   | Minimum value          | MIN(column\_name)                                   |
| **MAX**   | Maximum value          | MAX(column\_name)                                   |
| **SUM**   | Sum of values          | SUM(column\_name)                                   |

Date/Time Functions 

| Function                           | Description                                                                                | Syntax                                                       |
| ---------------------------------- | ------------------------------------------------------------------------------------------ | ------------------------------------------------------------ |
| **CONVERT\_TIME\_ZONE\_FROM\_UTC** | Converts UTC to specified timezone                                                         | CONVERT\_TIME\_ZONE\_FROM\_UTC(inputTime, 'timeZone')        |
| **CURRENT\_DATE**                  | Returns current date in UTC                                                                | CURRENT\_DATE                                                |
| **CURRENT\_TIMESTAMP**             | Returns current date+time in UTC                                                           | CURRENT\_TIMESTAMP                                           |
| **DATE\_TRUNC**                    | Truncates timestamp to granularity (SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR) | DATE\_TRUNC('granularity', inputTime)                        |
| **DAYOFMONTH**                     | Day of month (1-31)                                                                        | DAYOFMONTH(date)                                             |
| **DAYOFWEEK**                      | Day of week (1-7, 1=Sunday)                                                                | DAYOFWEEK(date)                                              |
| **EPOCH\_TO\_UTC\_TIMESTAMP**      | Converts epoch to timestamp                                                                | EPOCH\_TO\_UTC\_TIMESTAMP(expression)                        |
| **EXTEND\_TIME\_WINDOW**           | Extends table time period beyond reporting window                                          | EXTEND\_TIME\_WINDOW(table\_name, retro\_days, future\_days) |
| **EXTRACT**                        | Extracts time unit from date/timestamp                                                     | EXTRACT(timeUnit FROM expression)                            |
| **HOUR**                           | Hour (0-23)                                                                                | HOUR(timestamp)                                              |
| **MINUTE**                         | Minute (0-59)                                                                              | MINUTE(timestamp)                                            |
| **MONTH**                          | Month (1-12)                                                                               | MONTH(date)                                                  |
| **SECOND**                         | Second (0-59)                                                                              | SECOND(timestamp)                                            |
| **WEEK**                           | Week number (1-53)                                                                         | WEEK(date)                                                   |
| **YEAR**                           | Year from date                                                                             | YEAR(date)                                                   |
| **SECONDS\_BETWEEN**               | Time difference in seconds                                                                 | SECONDS\_BETWEEN(firstInputTime, secondInputTime)            |

Conditional Functions 

| Function     | Description              | Syntax                                                 |
| ------------ | ------------------------ | ------------------------------------------------------ |
| **CASE**     | Conditional expression   | CASE WHEN condition THEN result \[ELSE default\] END   |
| **COALESCE** | Returns first non-null   | COALESCE(expression \[, expression\]\*)                |
| **IF**       | Boolean conditional      | IF(booleanExpression, trueExpression, falseExpression) |
| **IFNULL**   | Alias of COALESCE        | IFNULL(expression, alternate\_value)                   |
| **IN**       | Multiple values in WHERE | IN(value1, value2, ..., valueN) \| NOT IN(...)         |
| **NULLIF**   | Returns NULL if equal    | NULLIF(expr1, expr2)                                   |
| **NVL**      | Alias of COALESCE        | NVL(expr1, expr2)                                      |
| **NVL2**     | Three-way NULL logic     | NVL2(expr1, expr2, expr3)                              |

Array Functions 

| Function              | Description                     | Syntax                                      |
| --------------------- | ------------------------------- | ------------------------------------------- |
| **ARRAY\_CAT**        | Concatenates two arrays         | ARRAY\_CAT(array1, array2)                  |
| **ARRAY\_CONTAINS**   | Returns true if value in array  | ARRAY\_CONTAINS(array, value)               |
| **ARRAY\_COMPLEMENT** | Elements in first not in second | ARRAY\_COMPLEMENT(array1, array2)           |
| **ARRAY\_DISTINCT**   | Removes duplicates              | ARRAY\_DISTINCT(array)                      |
| **ARRAY\_INTERSECT**  | Common elements                 | ARRAY\_INTERSECT(array1, array2)            |
| **ARRAY\_REMOVE**     | Removes occurrences of element  | ARRAY\_REMOVE(array, element)               |
| **ARRAY\_SORT**       | Sorts array                     | ARRAY\_SORT(COLLECT(DISTINCT column))       |
| **ARRAY\_TO\_STRING** | Converts array to string        | ARRAY\_TO\_STRING(array, separator)         |
| **ARRAY\_UNION**      | Combines unique elements        | ARRAY\_UNION(array1, array2)                |
| **CARDINALITY**       | Count elements in array         | CARDINALITY(array)                          |
| **COLLECT**           | Creates list of values          | COLLECT(column) \| COLLECT(DISTINCT column) |
| **EXPLODE**           | Array to multiple rows          | EXPLODE(array)                              |
| **FLATTEN**           | Nested array to flat            | FLATTEN(array)                              |

Window Functions 

| Function         | Description                        | Syntax                                             |
| ---------------- | ---------------------------------- | -------------------------------------------------- |
| **DENSE\_RANK**  | Rank without gaps                  | DENSE\_RANK() OVER (PARTITION BY col ORDER BY col) |
| **PARTITION BY** | Divides result set into partitions | Used with window functions                         |
| **RANK**         | Rank with gaps for ties            | RANK() OVER (PARTITION BY col ORDER BY col)        |
| **ROW\_NUMBER**  | Sequential row number              | ROW\_NUMBER() OVER (PARTITION BY col ORDER BY col) |

Statistical Functions 

| Function                  | Description                                                     | Syntax                                 |
| ------------------------- | --------------------------------------------------------------- | -------------------------------------- |
| **STDDEV\_POP**           | Standard deviation of population                                | STDDEV\_POP(values)                    |
| **VAR\_POP**              | Variance of population                                          | VAR\_POP(values)                       |
| **STDDEV\_SAMP / STDDEV** | Standard deviation of sample                                    | STDDEV\_SAMP(values) \| STDDEV(values) |
| **VAR\_SAMP / VARIANCE**  | Variance of sample                                              | VAR\_SAMP(values) \| VARIANCE(values)  |
| **SKEWNESS**              | Skewness of distribution                                        | SKEWNESS(values)                       |
| **PERCENTILE**            | Value at percentile (0-1)                                       | PERCENTILE(values, percentile)         |
| **APPROX\_PERCENTILE**    | Approximate percentile (rejected in our live tests; use MEDIAN) | APPROX\_PERCENTILE(values, percentile) |
| **MEDIAN**                | Middle value                                                    | MEDIAN(values)                         |

Other Functions 

| Function            | Description                              | Syntax                                     |
| ------------------- | ---------------------------------------- | ------------------------------------------ |
| **GREATEST**        | Largest of expressions (null-sensitive)  | GREATEST(expr \[, expr\]\*)                |
| **LEAST**           | Smallest of expressions (null-sensitive) | LEAST(expr \[, expr\]\*)                   |
| **HASH64**          | 64-bit hash                              | HASH64(expression)                         |
| **MD5**             | 128-bit MD5 hash                         | MD5(string)                                |
| **SHA1**            | 160-bit SHA-1 hash                       | SHA1(string)                               |
| **FROM\_VARBINARY** | Binary to string (hex/base64/utf-8)      | FROM\_VARBINARY(binary\_expression, type)  |
| **TO\_VARBINARY**   | String to binary                         | TO\_VARBINARY(string, type)                |
| **JSON\_EXTRACT**   | Extract from JSON                        | JSON\_EXTRACT(json\_string, json\_path)    |
| **JSON\_SERIALIZE** | JSON to string                           | JSON\_SERIALIZE(json\_value)               |
| **CAST**            | Type conversion                          | CAST(expression AS dataType)               |
| **NAMED\_ROW**      | Group columns under one name             | NAMED\_ROW('label1', col1, 'label2', col2) |

5 Table Schemas 

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.

| Table                                            | Columns | Primary Use                 |
| ------------------------------------------------ | ------- | --------------------------- |
| amazon\_retail\_purchases                        | 26      | Customer purchase data      |
| sponsored\_ads\_traffic                          | 79      | SP, SB, SD ad traffic       |
| dsp\_impressions                                 | 106     | DSP impression data         |
| dsp\_clicks                                      | 105     | DSP click data              |
| amazon\_attributed\_events\_by\_conversion\_time | 392     | Attribution/conversion data |
| conversions\_all                                 | 33      | All conversion events       |
| amazon\_live\_traffic                            | 20      | Amazon Live data            |
| **TOTAL**                                        | **761** |                             |

1\. amazon\_retail\_purchases (26 columns) 

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

| Column                  | Type      | Description                                                          |
| ----------------------- | --------- | -------------------------------------------------------------------- |
| asin                    | STRING    | Amazon Standard Identification Number for the purchased product      |
| asin\_name              | STRING    | Product title/name                                                   |
| asin\_brand             | STRING    | Brand name of the product                                            |
| purchase\_date\_utc     | TIMESTAMP | Date/time of the purchase in UTC                                     |
| unit\_price             | DECIMAL   | Price per unit at time of purchase                                   |
| purchase\_units\_sold   | INTEGER   | Number of units in this purchase                                     |
| user\_id                | STRING    | Hashed user identifier (non-viewable; COUNT DISTINCT and JOINs only) |
| purchase\_id            | STRING    | Unique identifier for the purchase/order                             |
| currency\_code          | STRING    | Currency of the transaction (e.g., USD)                              |
| purchase\_program\_name | STRING    | Purchase 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                  |
| -- | ----------------------- |
| 1  | asin                    |
| 2  | asin\_brand             |
| 3  | asin\_name              |
| 4  | asin\_parent            |
| 5  | currency\_code          |
| 6  | event\_id               |
| 7  | is\_business\_flag      |
| 8  | is\_gift\_flag          |
| 9  | marketplace\_id         |
| 10 | marketplace\_name       |
| 11 | no\_3p\_trackers        |
| 12 | origin\_session\_id     |
| 13 | purchase\_date\_utc     |
| 14 | purchase\_day\_utc      |
| 15 | purchase\_dt\_hour\_utc |
| 16 | purchase\_dt\_utc       |
| 17 | purchase\_hour\_utc     |
| 18 | purchase\_id            |
| 19 | purchase\_month\_utc    |
| 20 | purchase\_order\_method |
| 21 | purchase\_program\_name |
| 22 | purchase\_session\_id   |
| 23 | purchase\_units\_sold   |
| 24 | unit\_price             |
| 25 | user\_id                |
| 26 | user\_id\_type          |

2\. sponsored\_ads\_traffic (79 columns) 

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

| Column                 | Type      | Description                                                         |
| ---------------------- | --------- | ------------------------------------------------------------------- |
| campaign               | STRING    | Campaign name                                                       |
| campaign\_id\_string   | STRING    | Campaign ID (use this, not campaign\_id)                            |
| ad\_product\_type      | STRING    | Ad type: Sponsored Products, Sponsored Brands, or Sponsored Display |
| creative\_asin         | STRING    | ASIN in the ad creative (SP only; NULL for SB/SD)                   |
| event\_date\_utc       | TIMESTAMP | Date/time of the traffic event in UTC                               |
| spend                  | BIGINT    | Ad spend in **microcents** (÷ 100,000,000 for dollars)              |
| clicks                 | INTEGER   | Number of clicks                                                    |
| impressions            | INTEGER   | Number of impressions                                               |
| customer\_search\_term | STRING    | The search query that triggered the ad                              |
| match\_type            | STRING    | Keyword match type (exact, phrase, broad)                           |
| targeting              | STRING    | Targeting expression (keyword or product target)                    |
| ad\_group              | STRING    | Ad 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                                                                       |
| -- | ---------------------------------------------------------------------------- |
| 1  | ad\_group                                                                    |
| 2  | ad\_group\_end\_dt                                                           |
| 3  | ad\_group\_end\_dt\_utc                                                      |
| 4  | ad\_group\_start\_dt                                                         |
| 5  | ad\_group\_start\_dt\_utc                                                    |
| 6  | ad\_group\_status                                                            |
| 7  | ad\_group\_type                                                              |
| 8  | ad\_product\_type                                                            |
| 9  | ad\_product\_type\_code                                                      |
| 10 | ad\_product\_type\_id                                                        |
| 11 | advertiser                                                                   |
| 12 | advertiser\_id\_internal                                                     |
| 13 | advertiser\_timezone                                                         |
| 14 | campaign                                                                     |
| 15 | campaign\_[budget](https://adlabs.app/guides/amazon-ppc-budget-guide/)\_type |
| 16 | campaign\_end\_date                                                          |
| 17 | campaign\_end\_date\_utc                                                     |
| 18 | campaign\_end\_dt                                                            |
| 19 | campaign\_end\_dt\_utc                                                       |
| 20 | campaign\_id                                                                 |
| 21 | campaign\_id\_string                                                         |
| 22 | campaign\_start\_date                                                        |
| 23 | campaign\_start\_date\_utc                                                   |
| 24 | campaign\_start\_dt                                                          |
| 25 | campaign\_start\_dt\_utc                                                     |
| 26 | clicks                                                                       |
| 27 | creative                                                                     |
| 28 | creative\_asin                                                               |
| 29 | creative\_type                                                               |
| 30 | currency\_iso\_code                                                          |
| 31 | currency\_name                                                               |
| 32 | customer\_search\_term                                                       |
| 33 | entity\_id                                                                   |
| 34 | event\_date                                                                  |
| 35 | event\_date\_utc                                                             |
| 36 | event\_day                                                                   |
| 37 | event\_day\_utc                                                              |
| 38 | event\_dt                                                                    |
| 39 | event\_dt\_hour                                                              |
| 40 | event\_dt\_hour\_utc                                                         |
| 41 | event\_dt\_utc                                                               |
| 42 | event\_hour                                                                  |
| 43 | event\_hour\_utc                                                             |
| 44 | event\_id                                                                    |
| 45 | five\_sec\_views                                                             |
| 46 | impressions                                                                  |
| 47 | ip\_address                                                                  |
| 48 | is\_amazon\_business                                                         |
| 49 | line\_item                                                                   |
| 50 | line\_item\_end\_date                                                        |
| 51 | line\_item\_end\_date\_utc                                                   |
| 52 | line\_item\_id                                                               |
| 53 | line\_item\_price\_type                                                      |
| 54 | line\_item\_start\_date                                                      |
| 55 | line\_item\_start\_date\_utc                                                 |
| 56 | line\_item\_status                                                           |
| 57 | line\_item\_type                                                             |
| 58 | marketplace\_id                                                              |
| 59 | marketplace\_name                                                            |
| 60 | match\_type                                                                  |
| 61 | matched\_behavior\_segment\_ids                                              |
| 62 | no\_3p\_trackers                                                             |
| 63 | operating\_system                                                            |
| 64 | os\_version                                                                  |
| 65 | placement\_type                                                              |
| 66 | portfolio\_id                                                                |
| 67 | portfolio\_name                                                              |
| 68 | spend                                                                        |
| 69 | targeting                                                                    |
| 70 | unmeasurable\_viewable\_impressions                                          |
| 71 | user\_id                                                                     |
| 72 | user\_id\_type                                                               |
| 73 | video\_complete\_views                                                       |
| 74 | video\_first\_quartile\_views                                                |
| 75 | video\_midpoint\_views                                                       |
| 76 | video\_third\_quartile\_views                                                |
| 77 | video\_unmutes                                                               |
| 78 | view\_definition                                                             |
| 79 | viewable\_impressions                                                        |

3\. dsp\_impressions (106 columns) 

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

#### Key Columns

| Column                | Type      | Description                                             |
| --------------------- | --------- | ------------------------------------------------------- |
| campaign              | STRING    | Campaign name                                           |
| campaign\_id\_string  | STRING    | Campaign ID                                             |
| impression\_date\_utc | TIMESTAMP | Date/time of the impression in UTC                      |
| total\_cost           | BIGINT    | Total cost in **millicents** (÷ 100,000 for dollars)    |
| impressions           | INTEGER   | Number of impressions                                   |
| creative              | STRING    | Creative name                                           |
| line\_item            | STRING    | Line item name                                          |
| device\_type          | STRING    | Device type (desktop, mobile, tablet, etc.)             |
| supply\_source        | STRING    | Where the ad was served (e.g., Amazon O&O, 3P exchange) |
| publisher\_name       | STRING    | Publisher/site name                                     |
| city\_name            | STRING    | City of the user                                        |
| postal\_code          | STRING    | Postal/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                                     |
| --- | ------------------------------------------ |
| 1   | ad\_slot\_size                             |
| 2   | advertiser                                 |
| 3   | advertiser\_account\_frequency\_group\_ids |
| 4   | advertiser\_id                             |
| 5   | advertiser\_timezone                       |
| 6   | app\_bundle                                |
| 7   | audience\_fee                              |
| 8   | bid\_price                                 |
| 9   | browser\_family                            |
| 10  | campaign                                   |
| 11  | campaign\_budget\_amount                   |
| 12  | campaign\_country                          |
| 13  | campaign\_end\_date                        |
| 14  | campaign\_end\_date\_utc                   |
| 15  | campaign\_flight\_budget\_amount           |
| 16  | campaign\_flight\_end\_date\_utc           |
| 17  | campaign\_flight\_id                       |
| 18  | campaign\_flight\_name                     |
| 19  | campaign\_flight\_start\_date\_utc         |
| 20  | campaign\_id                               |
| 21  | campaign\_id\_string                       |
| 22  | campaign\_insertion\_order\_id             |
| 23  | campaign\_primary\_goal                    |
| 24  | campaign\_sales\_type                      |
| 25  | campaign\_source                           |
| 26  | campaign\_start\_date                      |
| 27  | campaign\_start\_date\_utc                 |
| 28  | campaign\_status                           |
| 29  | city\_name                                 |
| 30  | creative                                   |
| 31  | creative\_category                         |
| 32  | creative\_duration                         |
| 33  | creative\_id                               |
| 34  | creative\_is\_link\_in                     |
| 35  | creative\_size                             |
| 36  | creative\_type                             |
| 37  | currency\_iso\_code                        |
| 38  | currency\_name                             |
| 39  | deal\_id                                   |
| 40  | deal\_name                                 |
| 41  | demand\_channel                            |
| 42  | demand\_channel\_owner                     |
| 43  | device\_id                                 |
| 44  | device\_make                               |
| 45  | device\_model                              |
| 46  | device\_type                               |
| 47  | dma\_code                                  |
| 48  | entity\_id                                 |
| 49  | environment\_type                          |
| 50  | impression\_cost                           |
| 51  | impression\_date                           |
| 52  | impression\_date\_utc                      |
| 53  | impression\_day                            |
| 54  | impression\_day\_utc                       |
| 55  | impression\_dt                             |
| 56  | impression\_dt\_hour                       |
| 57  | impression\_dt\_hour\_utc                  |
| 58  | impression\_dt\_utc                        |
| 59  | impression\_hour                           |
| 60  | impression\_hour\_utc                      |
| 61  | impressions                                |
| 62  | ip\_address                                |
| 63  | is\_amazon\_owned                          |
| 64  | iso\_country\_code                         |
| 65  | iso\_state\_province\_code                 |
| 66  | line\_item                                 |
| 67  | line\_item\_budget\_amount                 |
| 68  | line\_item\_end\_date                      |
| 69  | line\_item\_end\_date\_utc                 |
| 70  | line\_item\_id                             |
| 71  | line\_item\_price\_type                    |
| 72  | line\_item\_start\_date                    |
| 73  | line\_item\_start\_date\_utc               |
| 74  | line\_item\_status                         |
| 75  | line\_item\_type                           |
| 76  | managed\_service\_fee                      |
| 77  | manager\_account\_frequency\_group\_ids    |
| 78  | matched\_behavior\_segment\_ids            |
| 79  | merchant\_id                               |
| 80  | no\_3p\_trackers                           |
| 81  | ocm\_fee                                   |
| 82  | operating\_system                          |
| 83  | os\_version                                |
| 84  | page\_type                                 |
| 85  | placement\_is\_view\_aware                 |
| 86  | placement\_view\_rate                      |
| 87  | platform\_fee                              |
| 88  | postal\_code                               |
| 89  | product\_line                              |
| 90  | publisher\_name                            |
| 91  | request\_tag                               |
| 92  | segment\_marketplace\_id                   |
| 93  | seller\_id                                 |
| 94  | seller\_name                               |
| 95  | site                                       |
| 96  | slot\_position                             |
| 97  | supply\_cost                               |
| 98  | supply\_source                             |
| 99  | supply\_source\_id                         |
| 100 | supply\_source\_is\_view\_aware            |
| 101 | supply\_source\_view\_rate                 |
| 102 | third\_party\_fees                         |
| 103 | total\_cost                                |
| 104 | user\_behavior\_segment\_ids               |
| 105 | user\_id                                   |
| 106 | user\_id\_type                             |

4\. dsp\_clicks (105 columns) 

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

| Column               | Type      | Description                                          |
| -------------------- | --------- | ---------------------------------------------------- |
| campaign             | STRING    | Campaign name                                        |
| campaign\_id\_string | STRING    | Campaign ID                                          |
| click\_date\_utc     | TIMESTAMP | Date/time of the click in UTC                        |
| click\_cost          | BIGINT    | Click cost in **millicents** (÷ 100,000 for dollars) |
| clicks               | INTEGER   | Number of clicks                                     |
| creative             | STRING    | Creative name                                        |
| line\_item           | STRING    | Line item name                                       |
| device\_type         | STRING    | Device type                                          |
| supply\_source       | STRING    | Where the ad was served                              |
| publisher\_name      | STRING    | Publisher/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                                     |
| --- | ------------------------------------------ |
| 1   | ad\_slot\_size                             |
| 2   | advertiser                                 |
| 3   | advertiser\_account\_frequency\_group\_ids |
| 4   | advertiser\_id                             |
| 5   | advertiser\_timezone                       |
| 6   | app\_bundle                                |
| 7   | audience\_fee                              |
| 8   | bid\_price                                 |
| 9   | browser\_family                            |
| 10  | campaign                                   |
| 11  | campaign\_budget\_amount                   |
| 12  | campaign\_country                          |
| 13  | campaign\_end\_date                        |
| 14  | campaign\_end\_date\_utc                   |
| 15  | campaign\_flight\_budget\_amount           |
| 16  | campaign\_flight\_end\_date\_utc           |
| 17  | campaign\_flight\_id                       |
| 18  | campaign\_flight\_name                     |
| 19  | campaign\_flight\_start\_date\_utc         |
| 20  | campaign\_id                               |
| 21  | campaign\_id\_string                       |
| 22  | campaign\_insertion\_order\_id             |
| 23  | campaign\_primary\_goal                    |
| 24  | campaign\_sales\_type                      |
| 25  | campaign\_source                           |
| 26  | campaign\_start\_date                      |
| 27  | campaign\_start\_date\_utc                 |
| 28  | campaign\_status                           |
| 29  | city\_name                                 |
| 30  | click\_cost                                |
| 31  | click\_date                                |
| 32  | click\_date\_utc                           |
| 33  | click\_day                                 |
| 34  | click\_day\_utc                            |
| 35  | click\_dt                                  |
| 36  | click\_dt\_hour                            |
| 37  | click\_dt\_hour\_utc                       |
| 38  | click\_dt\_utc                             |
| 39  | click\_hour                                |
| 40  | click\_hour\_utc                           |
| 41  | clicks                                     |
| 42  | creative                                   |
| 43  | creative\_category                         |
| 44  | creative\_duration                         |
| 45  | creative\_id                               |
| 46  | creative\_is\_link\_in                     |
| 47  | creative\_size                             |
| 48  | creative\_type                             |
| 49  | currency\_iso\_code                        |
| 50  | currency\_name                             |
| 51  | deal\_id                                   |
| 52  | deal\_name                                 |
| 53  | demand\_channel                            |
| 54  | demand\_channel\_owner                     |
| 55  | device\_id                                 |
| 56  | device\_make                               |
| 57  | device\_model                              |
| 58  | device\_type                               |
| 59  | dma\_code                                  |
| 60  | entity\_id                                 |
| 61  | environment\_type                          |
| 62  | impression\_date                           |
| 63  | impression\_date\_utc                      |
| 64  | impression\_day                            |
| 65  | impression\_day\_utc                       |
| 66  | impression\_dt                             |
| 67  | impression\_dt\_hour                       |
| 68  | impression\_dt\_hour\_utc                  |
| 69  | impression\_dt\_utc                        |
| 70  | impression\_hour                           |
| 71  | impression\_hour\_utc                      |
| 72  | ip\_address                                |
| 73  | is\_amazon\_owned                          |
| 74  | iso\_country\_code                         |
| 75  | iso\_state\_province\_code                 |
| 76  | line\_item                                 |
| 77  | line\_item\_budget\_amount                 |
| 78  | line\_item\_end\_date                      |
| 79  | line\_item\_end\_date\_utc                 |
| 80  | line\_item\_id                             |
| 81  | line\_item\_price\_type                    |
| 82  | line\_item\_start\_date                    |
| 83  | line\_item\_start\_date\_utc               |
| 84  | line\_item\_status                         |
| 85  | line\_item\_type                           |
| 86  | manager\_account\_frequency\_group\_ids    |
| 87  | merchant\_id                               |
| 88  | no\_3p\_trackers                           |
| 89  | operating\_system                          |
| 90  | os\_version                                |
| 91  | page\_type                                 |
| 92  | platform\_fee                              |
| 93  | postal\_code                               |
| 94  | product\_line                              |
| 95  | publisher\_name                            |
| 96  | request\_tag                               |
| 97  | seller\_id                                 |
| 98  | seller\_name                               |
| 99  | site                                       |
| 100 | slot\_position                             |
| 101 | supply\_cost                               |
| 102 | supply\_source                             |
| 103 | supply\_source\_id                         |
| 104 | user\_id                                   |
| 105 | user\_id\_type                             |

5\. amazon\_attributed\_events\_by\_conversion\_time (392 columns) 

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

| Column                       | Type      | Description                                                                             |
| ---------------------------- | --------- | --------------------------------------------------------------------------------------- |
| campaign                     | STRING    | Campaign name                                                                           |
| campaign\_id\_string         | STRING    | Campaign ID                                                                             |
| ad\_product\_type            | STRING    | Ad type (sponsored\_products, sponsored\_brands, sponsored\_display; DSP rows are NULL) |
| tracked\_asin                | STRING    | The ASIN being tracked for attribution                                                  |
| conversion\_event\_date\_utc | TIMESTAMP | When the conversion happened                                                            |
| purchases                    | INTEGER   | Total attributed purchases (clicks + views)                                             |
| total\_product\_sales        | DECIMAL   | Total sales including brand halo                                                        |
| product\_sales               | DECIMAL   | Sales for the tracked ASIN only                                                         |
| new\_to\_brand               | BOOLEAN   | Flag: is this a new-to-brand customer (dimension, not metric)                           |
| new\_to\_brand\_purchases    | INTEGER   | Purchases from new-to-brand customers                                                   |
| units\_sold                  | INTEGER   | Total units sold (clicks + views)                                                       |
| clicks                       | INTEGER   | Clicks on the ad                                                                        |
| impressions                  | INTEGER   | Ad 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                                                             |
| --- | ------------------------------------------------------------------ |
| 1   | ad\_product\_type                                                  |
| 2   | ad\_slot\_size                                                     |
| 3   | add\_to\_cart                                                      |
| 4   | add\_to\_cart\_clicks                                              |
| 5   | add\_to\_cart\_views                                               |
| 6   | advertiser                                                         |
| 7   | advertiser\_conversion\_definition\_dim\_id                        |
| 8   | advertiser\_id                                                     |
| 9   | advertiser\_id\_internal                                           |
| 10  | advertiser\_timezone                                               |
| 11  | appstore\_first\_opens                                             |
| 12  | appstore\_first\_opens\_clicks                                     |
| 13  | appstore\_first\_opens\_views                                      |
| 14  | appstore\_first\_session\_hours                                    |
| 15  | appstore\_first\_session\_hours\_clicks                            |
| 16  | appstore\_first\_session\_hours\_views                             |
| 17  | appstore\_opens                                                    |
| 18  | appstore\_opens\_clicks                                            |
| 19  | appstore\_opens\_views                                             |
| 20  | appstore\_repeat\_opens                                            |
| 21  | appstore\_repeat\_opens\_clicks                                    |
| 22  | appstore\_repeat\_opens\_views                                     |
| 23  | appstore\_repeat\_session\_hours                                   |
| 24  | appstore\_repeat\_session\_hours\_clicks                           |
| 25  | appstore\_repeat\_session\_hours\_views                            |
| 26  | appstore\_usage\_hours                                             |
| 27  | appstore\_usage\_hours\_clicks                                     |
| 28  | appstore\_usage\_hours\_views                                      |
| 29  | audience\_fee                                                      |
| 30  | bid\_price                                                         |
| 31  | brand\_halo\_add\_to\_cart                                         |
| 32  | brand\_halo\_add\_to\_cart\_clicks                                 |
| 33  | brand\_halo\_add\_to\_cart\_views                                  |
| 34  | brand\_halo\_detail\_page\_view                                    |
| 35  | brand\_halo\_detail\_page\_view\_clicks                            |
| 36  | brand\_halo\_detail\_page\_view\_views                             |
| 37  | brand\_halo\_product\_sales                                        |
| 38  | brand\_halo\_product\_sales\_clicks                                |
| 39  | brand\_halo\_product\_sales\_views                                 |
| 40  | brand\_halo\_purchases                                             |
| 41  | brand\_halo\_purchases\_clicks                                     |
| 42  | brand\_halo\_purchases\_views                                      |
| 43  | brand\_halo\_units\_sold                                           |
| 44  | brand\_halo\_units\_sold\_clicks                                   |
| 45  | brand\_halo\_units\_sold\_views                                    |
| 46  | browser\_family                                                    |
| 47  | campaign                                                           |
| 48  | campaign\_budget\_amount                                           |
| 49  | campaign\_end\_date                                                |
| 50  | campaign\_end\_date\_utc                                           |
| 51  | campaign\_end\_dt                                                  |
| 52  | campaign\_end\_dt\_utc                                             |
| 53  | campaign\_flight\_budget\_amount                                   |
| 54  | campaign\_flight\_end\_date\_utc                                   |
| 55  | campaign\_flight\_id                                               |
| 56  | campaign\_flight\_name                                             |
| 57  | campaign\_flight\_start\_date\_utc                                 |
| 58  | campaign\_id                                                       |
| 59  | campaign\_id\_string                                               |
| 60  | campaign\_insertion\_order\_id                                     |
| 61  | campaign\_primary\_goal                                            |
| 62  | campaign\_start\_date                                              |
| 63  | campaign\_start\_date\_utc                                         |
| 64  | campaign\_status                                                   |
| 65  | city\_name                                                         |
| 66  | click\_cost                                                        |
| 67  | click\_date                                                        |
| 68  | click\_date\_utc                                                   |
| 69  | click\_day                                                         |
| 70  | click\_day\_utc                                                    |
| 71  | click\_dt                                                          |
| 72  | click\_dt\_hour                                                    |
| 73  | click\_dt\_hour\_utc                                               |
| 74  | click\_dt\_utc                                                     |
| 75  | click\_hour                                                        |
| 76  | click\_hour\_utc                                                   |
| 77  | clicks                                                             |
| 78  | combined\_sales                                                    |
| 79  | conversion\_event\_category                                        |
| 80  | conversion\_event\_date                                            |
| 81  | conversion\_event\_date\_utc                                       |
| 82  | conversion\_event\_dt                                              |
| 83  | conversion\_event\_dt\_hour                                        |
| 84  | conversion\_event\_dt\_hour\_utc                                   |
| 85  | conversion\_event\_dt\_utc                                         |
| 86  | conversion\_event\_hour                                            |
| 87  | conversion\_event\_hour\_utc                                       |
| 88  | conversion\_event\_marketplace\_id                                 |
| 89  | conversion\_event\_marketplace\_name                               |
| 90  | conversion\_event\_name                                            |
| 91  | conversion\_event\_source\_name                                    |
| 92  | conversion\_event\_subtype                                         |
| 93  | conversion\_event\_type                                            |
| 94  | conversion\_event\_type\_class                                     |
| 95  | conversion\_event\_type\_description                               |
| 96  | conversions                                                        |
| 97  | creative                                                           |
| 98  | creative\_category                                                 |
| 99  | creative\_duration                                                 |
| 100 | creative\_id                                                       |
| 101 | creative\_is\_link\_in                                             |
| 102 | creative\_size                                                     |
| 103 | creative\_type                                                     |
| 104 | customer\_search\_term                                             |
| 105 | deal\_id                                                           |
| 106 | deal\_name                                                         |
| 107 | detail\_page\_view                                                 |
| 108 | detail\_page\_view\_clicks                                         |
| 109 | detail\_page\_view\_views                                          |
| 110 | device\_make                                                       |
| 111 | device\_model                                                      |
| 112 | device\_type                                                       |
| 113 | digital\_app\_subscription\_signup\_purchases                      |
| 114 | digital\_app\_subscription\_signup\_purchases\_clicks              |
| 115 | digital\_app\_subscription\_signup\_purchases\_views               |
| 116 | digital\_free\_trial\_app\_subscription\_signup\_purchases         |
| 117 | digital\_free\_trial\_app\_subscription\_signup\_purchases\_clicks |
| 118 | digital\_free\_trial\_app\_subscription\_signup\_purchases\_views  |
| 119 | digital\_free\_trial\_subscription\_signup\_purchases              |
| 120 | digital\_free\_trial\_subscription\_signup\_purchases\_clicks      |
| 121 | digital\_free\_trial\_subscription\_signup\_purchases\_views       |
| 122 | digital\_paid\_app\_subscription\_signup\_purchases                |
| 123 | digital\_paid\_app\_subscription\_signup\_purchases\_clicks        |
| 124 | digital\_paid\_app\_subscription\_signup\_purchases\_views         |
| 125 | digital\_paid\_subscription\_signup\_purchases                     |
| 126 | digital\_paid\_subscription\_signup\_purchases\_clicks             |
| 127 | digital\_paid\_subscription\_signup\_purchases\_views              |
| 128 | digital\_subscription\_signup\_purchases                           |
| 129 | digital\_subscription\_signup\_purchases\_clicks                   |
| 130 | digital\_subscription\_signup\_purchases\_views                    |
| 131 | dma\_code                                                          |
| 132 | entity\_id                                                         |
| 133 | environment\_type                                                  |
| 134 | impression\_date                                                   |
| 135 | impression\_date\_utc                                              |
| 136 | impression\_day                                                    |
| 137 | impression\_day\_utc                                               |
| 138 | impression\_dt                                                     |
| 139 | impression\_dt\_hour                                               |
| 140 | impression\_dt\_hour\_utc                                          |
| 141 | impression\_dt\_utc                                                |
| 142 | impression\_hour                                                   |
| 143 | impression\_hour\_utc                                              |
| 144 | impressions                                                        |
| 145 | is\_amazon\_business                                               |
| 146 | is\_event\_type\_modeled\_view                                     |
| 147 | iso\_country\_code                                                 |
| 148 | iso\_state\_province\_code                                         |
| 149 | line\_item                                                         |
| 150 | line\_item\_budget\_amount                                         |
| 151 | line\_item\_end\_date                                              |
| 152 | line\_item\_end\_date\_utc                                         |
| 153 | line\_item\_id                                                     |
| 154 | line\_item\_price\_type                                            |
| 155 | line\_item\_start\_date                                            |
| 156 | line\_item\_start\_date\_utc                                       |
| 157 | line\_item\_status                                                 |
| 158 | line\_item\_type                                                   |
| 159 | match\_type                                                        |
| 160 | new\_to\_brand                                                     |
| 161 | new\_to\_brand\_brand\_halo\_product\_sales                        |
| 162 | new\_to\_brand\_brand\_halo\_product\_sales\_clicks                |
| 163 | new\_to\_brand\_brand\_halo\_product\_sales\_views                 |
| 164 | new\_to\_brand\_brand\_halo\_purchases                             |
| 165 | new\_to\_brand\_brand\_halo\_purchases\_clicks                     |
| 166 | new\_to\_brand\_brand\_halo\_purchases\_views                      |
| 167 | new\_to\_brand\_brand\_halo\_units\_sold                           |
| 168 | new\_to\_brand\_brand\_halo\_units\_sold\_clicks                   |
| 169 | new\_to\_brand\_brand\_halo\_units\_sold\_views                    |
| 170 | new\_to\_brand\_product\_sales                                     |
| 171 | new\_to\_brand\_product\_sales\_clicks                             |
| 172 | new\_to\_brand\_product\_sales\_views                              |
| 173 | new\_to\_brand\_purchases                                          |
| 174 | new\_to\_brand\_purchases\_clicks                                  |
| 175 | new\_to\_brand\_purchases\_views                                   |
| 176 | new\_to\_brand\_total\_product\_sales                              |
| 177 | new\_to\_brand\_total\_product\_sales\_clicks                      |
| 178 | new\_to\_brand\_total\_product\_sales\_views                       |
| 179 | new\_to\_brand\_total\_purchases                                   |
| 180 | new\_to\_brand\_total\_purchases\_clicks                           |
| 181 | new\_to\_brand\_total\_purchases\_views                            |
| 182 | new\_to\_brand\_total\_units\_sold                                 |
| 183 | new\_to\_brand\_total\_units\_sold\_clicks                         |
| 184 | new\_to\_brand\_total\_units\_sold\_views                          |
| 185 | new\_to\_brand\_units\_sold                                        |
| 186 | new\_to\_brand\_units\_sold\_clicks                                |
| 187 | new\_to\_brand\_units\_sold\_views                                 |
| 188 | no\_3p\_trackers                                                   |
| 189 | off\_amazon\_conversion\_value                                     |
| 190 | off\_amazon\_product\_sales                                        |
| 191 | operating\_system                                                  |
| 192 | os\_version                                                        |
| 193 | page\_type                                                         |
| 194 | pixel\_accept                                                      |
| 195 | pixel\_accept\_clicks                                              |
| 196 | pixel\_accept\_views                                               |
| 197 | pixel\_add\_to\_shopping\_cart                                     |
| 198 | pixel\_add\_to\_shopping\_cart\_clicks                             |
| 199 | pixel\_add\_to\_shopping\_cart\_views                              |
| 200 | pixel\_application                                                 |
| 201 | pixel\_application\_clicks                                         |
| 202 | pixel\_application\_views                                          |
| 203 | pixel\_banner\_interaction                                         |
| 204 | pixel\_banner\_interaction\_clicks                                 |
| 205 | pixel\_banner\_interaction\_views                                  |
| 206 | pixel\_brand\_store\_engagement\_1                                 |
| 207 | pixel\_brand\_store\_engagement\_1\_clicks                         |
| 208 | pixel\_brand\_store\_engagement\_1\_views                          |
| 209 | pixel\_brand\_store\_engagement\_2                                 |
| 210 | pixel\_brand\_store\_engagement\_2\_clicks                         |
| 211 | pixel\_brand\_store\_engagement\_2\_views                          |
| 212 | pixel\_brand\_store\_engagement\_3                                 |
| 213 | pixel\_brand\_store\_engagement\_3\_clicks                         |
| 214 | pixel\_brand\_store\_engagement\_3\_views                          |
| 215 | pixel\_brand\_store\_engagement\_4                                 |
| 216 | pixel\_brand\_store\_engagement\_4\_clicks                         |
| 217 | pixel\_brand\_store\_engagement\_4\_views                          |
| 218 | pixel\_brand\_store\_engagement\_5                                 |
| 219 | pixel\_brand\_store\_engagement\_5\_clicks                         |
| 220 | pixel\_brand\_store\_engagement\_5\_views                          |
| 221 | pixel\_brand\_store\_engagement\_6                                 |
| 222 | pixel\_brand\_store\_engagement\_6\_clicks                         |
| 223 | pixel\_brand\_store\_engagement\_6\_views                          |
| 224 | pixel\_brand\_store\_engagement\_7                                 |
| 225 | pixel\_brand\_store\_engagement\_7\_clicks                         |
| 226 | pixel\_brand\_store\_engagement\_7\_views                          |
| 227 | pixel\_click\_on\_redirect                                         |
| 228 | pixel\_click\_on\_redirect\_clicks                                 |
| 229 | pixel\_click\_on\_redirect\_views                                  |
| 230 | pixel\_decline                                                     |
| 231 | pixel\_decline\_clicks                                             |
| 232 | pixel\_decline\_views                                              |
| 233 | pixel\_drop\_down\_selection                                       |
| 234 | pixel\_drop\_down\_selection\_clicks                               |
| 235 | pixel\_drop\_down\_selection\_views                                |
| 236 | pixel\_email\_interaction                                          |
| 237 | pixel\_email\_interaction\_clicks                                  |
| 238 | pixel\_email\_interaction\_views                                   |
| 239 | pixel\_email\_load                                                 |
| 240 | pixel\_email\_load\_clicks                                         |
| 241 | pixel\_email\_load\_views                                          |
| 242 | pixel\_game\_interaction                                           |
| 243 | pixel\_game\_interaction\_clicks                                   |
| 244 | pixel\_game\_interaction\_views                                    |
| 245 | pixel\_game\_load                                                  |
| 246 | pixel\_game\_load\_clicks                                          |
| 247 | pixel\_game\_load\_views                                           |
| 248 | pixel\_homepage\_visit                                             |
| 249 | pixel\_homepage\_visit\_clicks                                     |
| 250 | pixel\_homepage\_visit\_views                                      |
| 251 | pixel\_marketing\_landing\_page                                    |
| 252 | pixel\_marketing\_landing\_page\_clicks                            |
| 253 | pixel\_marketing\_landing\_page\_views                             |
| 254 | pixel\_mashup\_add\_to\_cart                                       |
| 255 | pixel\_mashup\_add\_to\_cart\_clicks                               |
| 256 | pixel\_mashup\_add\_to\_cart\_views                                |
| 257 | pixel\_mashup\_add\_to\_wishlist                                   |
| 258 | pixel\_mashup\_add\_to\_wishlist\_clicks                           |
| 259 | pixel\_mashup\_add\_to\_wishlist\_views                            |
| 260 | pixel\_mashup\_backup\_image                                       |
| 261 | pixel\_mashup\_backup\_image\_clicks                               |
| 262 | pixel\_mashup\_backup\_image\_views                                |
| 263 | pixel\_mashup\_click\_to\_page                                     |
| 264 | pixel\_mashup\_click\_to\_page\_clicks                             |
| 265 | pixel\_mashup\_click\_to\_page\_views                              |
| 266 | pixel\_mashup\_clip\_coupon                                        |
| 267 | pixel\_mashup\_clip\_coupon\_clicks                                |
| 268 | pixel\_mashup\_clip\_coupon\_views                                 |
| 269 | pixel\_mashup\_shop\_now                                           |
| 270 | pixel\_mashup\_shop\_now\_clicks                                   |
| 271 | pixel\_mashup\_shop\_now\_views                                    |
| 272 | pixel\_mashup\_subscribe\_and\_save                                |
| 273 | pixel\_mashup\_subscribe\_and\_save\_clicks                        |
| 274 | pixel\_mashup\_subscribe\_and\_save\_views                         |
| 275 | pixel\_message\_sent                                               |
| 276 | pixel\_message\_sent\_clicks                                       |
| 277 | pixel\_message\_sent\_views                                        |
| 278 | pixel\_mobile\_app\_first\_start                                   |
| 279 | pixel\_mobile\_app\_first\_start\_clicks                           |
| 280 | pixel\_mobile\_app\_first\_start\_views                            |
| 281 | pixel\_product\_purchased                                          |
| 282 | pixel\_product\_purchased\_clicks                                  |
| 283 | pixel\_product\_purchased\_views                                   |
| 284 | pixel\_purchase\_button                                            |
| 285 | pixel\_purchase\_button\_clicks                                    |
| 286 | pixel\_purchase\_button\_views                                     |
| 287 | pixel\_referral                                                    |
| 288 | pixel\_referral\_clicks                                            |
| 289 | pixel\_referral\_views                                             |
| 290 | pixel\_registration\_confirm\_page                                 |
| 291 | pixel\_registration\_confirm\_page\_clicks                         |
| 292 | pixel\_registration\_confirm\_page\_views                          |
| 293 | pixel\_registration\_form                                          |
| 294 | pixel\_registration\_form\_clicks                                  |
| 295 | pixel\_registration\_form\_views                                   |
| 296 | pixel\_sign\_up\_button                                            |
| 297 | pixel\_sign\_up\_button\_clicks                                    |
| 298 | pixel\_sign\_up\_button\_views                                     |
| 299 | pixel\_sign\_up\_page                                              |
| 300 | pixel\_sign\_up\_page\_clicks                                      |
| 301 | pixel\_sign\_up\_page\_views                                       |
| 302 | pixel\_store\_locator\_page                                        |
| 303 | pixel\_store\_locator\_page\_clicks                                |
| 304 | pixel\_store\_locator\_page\_views                                 |
| 305 | pixel\_submit\_button                                              |
| 306 | pixel\_submit\_button\_clicks                                      |
| 307 | pixel\_submit\_button\_views                                       |
| 308 | pixel\_subscription\_button                                        |
| 309 | pixel\_subscription\_button\_clicks                                |
| 310 | pixel\_subscription\_button\_views                                 |
| 311 | pixel\_subscription\_page                                          |
| 312 | pixel\_subscription\_page\_clicks                                  |
| 313 | pixel\_subscription\_page\_views                                   |
| 314 | pixel\_success\_page                                               |
| 315 | pixel\_success\_page\_clicks                                       |
| 316 | pixel\_success\_page\_views                                        |
| 317 | pixel\_survey\_finish                                              |
| 318 | pixel\_survey\_finish\_clicks                                      |
| 319 | pixel\_survey\_finish\_views                                       |
| 320 | pixel\_survey\_start                                               |
| 321 | pixel\_survey\_start\_clicks                                       |
| 322 | pixel\_survey\_start\_views                                        |
| 323 | pixel\_thank\_you\_page                                            |
| 324 | pixel\_thank\_you\_page\_clicks                                    |
| 325 | pixel\_thank\_you\_page\_views                                     |
| 326 | pixel\_video\_end                                                  |
| 327 | pixel\_video\_end\_clicks                                          |
| 328 | pixel\_video\_end\_views                                           |
| 329 | pixel\_video\_started                                              |
| 330 | pixel\_video\_started\_clicks                                      |
| 331 | pixel\_video\_started\_views                                       |
| 332 | pixel\_widget\_interaction                                         |
| 333 | pixel\_widget\_interaction\_clicks                                 |
| 334 | pixel\_widget\_interaction\_views                                  |
| 335 | pixel\_widget\_load                                                |
| 336 | pixel\_widget\_load\_clicks                                        |
| 337 | pixel\_widget\_load\_views                                         |
| 338 | placement\_type                                                    |
| 339 | platform\_fee                                                      |
| 340 | postal\_code                                                       |
| 341 | product\_line                                                      |
| 342 | product\_sales                                                     |
| 343 | product\_sales\_clicks                                             |
| 344 | product\_sales\_views                                              |
| 345 | purchase\_currency                                                 |
| 346 | purchase\_price                                                    |
| 347 | purchase\_quantity                                                 |
| 348 | purchase\_retail\_program                                          |
| 349 | purchase\_unit\_price                                              |
| 350 | purchases                                                          |
| 351 | purchases\_clicks                                                  |
| 352 | purchases\_views                                                   |
| 353 | request\_tag                                                       |
| 354 | site                                                               |
| 355 | supply\_cost                                                       |
| 356 | supply\_source                                                     |
| 357 | targeting                                                          |
| 358 | total\_add\_to\_cart                                               |
| 359 | total\_add\_to\_cart\_clicks                                       |
| 360 | total\_add\_to\_cart\_views                                        |
| 361 | total\_detail\_page\_view                                          |
| 362 | total\_detail\_page\_view\_clicks                                  |
| 363 | total\_detail\_page\_view\_views                                   |
| 364 | total\_product\_sales                                              |
| 365 | total\_product\_sales\_clicks                                      |
| 366 | total\_product\_sales\_views                                       |
| 367 | total\_purchases                                                   |
| 368 | total\_purchases\_clicks                                           |
| 369 | total\_purchases\_views                                            |
| 370 | total\_units\_sold                                                 |
| 371 | total\_units\_sold\_clicks                                         |
| 372 | total\_units\_sold\_views                                          |
| 373 | tracked\_asin                                                      |
| 374 | tracked\_item                                                      |
| 375 | traffic\_event\_date                                               |
| 376 | traffic\_event\_date\_utc                                          |
| 377 | traffic\_event\_dt                                                 |
| 378 | traffic\_event\_dt\_hour                                           |
| 379 | traffic\_event\_dt\_hour\_utc                                      |
| 380 | traffic\_event\_dt\_utc                                            |
| 381 | traffic\_event\_hour                                               |
| 382 | traffic\_event\_hour\_utc                                          |
| 383 | traffic\_event\_id                                                 |
| 384 | traffic\_event\_marketplace\_id                                    |
| 385 | traffic\_event\_marketplace\_name                                  |
| 386 | traffic\_event\_subtype                                            |
| 387 | traffic\_event\_type                                               |
| 388 | units\_sold                                                        |
| 389 | units\_sold\_clicks                                                |
| 390 | units\_sold\_views                                                 |
| 391 | user\_id                                                           |
| 392 | user\_id\_type                                                     |

6\. conversions\_all (33 columns) 

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

#### Key Columns

| Column                    | Type      | Description                               |
| ------------------------- | --------- | ----------------------------------------- |
| event\_date\_utc          | TIMESTAMP | Date/time of the conversion event         |
| event\_type               | STRING    | Type of conversion event                  |
| event\_subtype            | STRING    | Subtype for more granular classification  |
| conversion\_event\_name   | STRING    | Name of the conversion event              |
| total\_product\_sales     | DECIMAL   | Total product sales amount                |
| total\_units\_sold        | INTEGER   | Total units sold                          |
| new\_to\_brand            | BOOLEAN   | Whether this is a new-to-brand customer   |
| tracked\_asin             | STRING    | The tracked ASIN                          |
| user\_id                  | STRING    | Hashed user identifier                    |
| exposure\_type            | STRING    | How the user was exposed (click vs view)  |
| purchase\_retail\_program | STRING    | Retail program (includes S&S identifiers) |
| sns\_subscription\_id     | STRING    | Subscribe & 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                          |
| -- | ------------------------------- |
| 1  | combined\_sales                 |
| 2  | conversion\_event\_name         |
| 3  | conversion\_event\_source\_name |
| 4  | conversion\_id                  |
| 5  | conversions                     |
| 6  | event\_category                 |
| 7  | event\_date\_utc                |
| 8  | event\_day\_utc                 |
| 9  | event\_dt\_hour\_utc            |
| 10 | event\_dt\_utc                  |
| 11 | event\_hour\_utc                |
| 12 | event\_subtype                  |
| 13 | event\_type                     |
| 14 | event\_type\_class              |
| 15 | event\_type\_description        |
| 16 | exposure\_type                  |
| 17 | ip\_address                     |
| 18 | marketplace\_id                 |
| 19 | marketplace\_name               |
| 20 | new\_to\_brand                  |
| 21 | no\_3p\_trackers                |
| 22 | off\_amazon\_conversion\_value  |
| 23 | off\_amazon\_product\_sales     |
| 24 | purchase\_currency              |
| 25 | purchase\_retail\_program       |
| 26 | purchase\_unit\_price           |
| 27 | sns\_subscription\_id           |
| 28 | total\_product\_sales           |
| 29 | total\_units\_sold              |
| 30 | tracked\_asin                   |
| 31 | tracked\_item                   |
| 32 | user\_id                        |
| 33 | user\_id\_type                  |

7\. amazon\_live\_traffic (20 columns) 

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

#### Key Columns

| Column           | Type      | Description                    |
| ---------------- | --------- | ------------------------------ |
| broadcast\_name  | STRING    | Name of the live broadcast     |
| broadcast\_id    | STRING    | Unique broadcast identifier    |
| event\_date\_utc | TIMESTAMP | Date/time of the event         |
| impressions      | INTEGER   | Number of impressions          |
| clicks           | INTEGER   | Number of clicks               |
| views            | INTEGER   | Number of video views          |
| creative\_asin   | STRING    | ASIN featured in the broadcast |
| user\_id         | STRING    | Hashed 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               |
| -- | -------------------- |
| 1  | ad\_product\_type    |
| 2  | advertiser           |
| 3  | broadcast\_date\_utc |
| 4  | broadcast\_id        |
| 5  | broadcast\_name      |
| 6  | clicks               |
| 7  | creative\_asin       |
| 8  | creative\_id         |
| 9  | event\_date\_utc     |
| 10 | event\_day\_utc      |
| 11 | event\_dt\_hour\_utc |
| 12 | event\_dt\_utc       |
| 13 | event\_hour\_utc     |
| 14 | event\_id            |
| 15 | impressions          |
| 16 | site                 |
| 17 | slot\_name           |
| 18 | user\_id             |
| 19 | user\_id\_type       |
| 20 | views                |

Additional Tables & Variants 

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

| Table                                         | Description                                                                                                                                                                                                                        | Access        |
| --------------------------------------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ------------- |
| amazon\_attributed\_events\_by\_traffic\_time | Same 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\_1d      | Product-level attribution with a 1-day lookback window                                                                                                                                                                             | Default       |
| amazon\_attributed\_events\_products\_7d      | Product-level attribution with a 7-day lookback window                                                                                                                                                                             | Default       |
| amazon\_attributed\_events\_products\_14d     | Product-level attribution with a 14-day lookback window                                                                                                                                                                            | Default       |
| amazon\_attributed\_events\_products\_30d     | Product-level attribution with a 30-day lookback window                                                                                                                                                                            | Default       |
| dsp\_views                                    | DSP viewability and video view events                                                                                                                                                                                              | Default (DSP) |
| dsp\_impressions\_with\_conversions\_1d       | Pre-joined DSP impressions with 1-day conversion data for faster queries                                                                                                                                                           | Default (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.

Paid Add-on Tables 

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:

| Subscription                           | What It Unlocks                                                                                                                                                                                                                                                                                                                                                                                                                                                           | Eligibility                   |
| -------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ----------------------------- |
| **Amazon Insights**                    | A 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 Purchases**            | Provides 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 Garage**                 | Enables 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 Insights**               | Web 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 Insights** | Insight 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 Stream**            | Modeled 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 Visit**             | Physical 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.

Getting Access to Additional Tables 

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.

6 Query Optimization 

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.

Understanding Query Execution Order 

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. 

Filter Data Early (Sargable vs Non-Sargable) 

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') 

UNION ALL vs JOIN 

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. 

Optimize Join Order 

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 

Avoid Hidden Cartesian Joins 

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 

Eliminate & Reduce Calculations 

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. 

Missed Aggregation Opportunities 

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.

Non-Restrictive Predicates 

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

Getting More Help 

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

7 Common Patterns & Templates 

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](https://adlabs.app/guides/amc-query-library/).

Monthly Aggregation Pattern 

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 

Customer Cohort Pattern 

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 

Ad-Touched vs Organic Pattern 

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 

Cross-Sell Path Pattern 

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 

Spend Conversion Pattern 

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 

NTB Classification Pattern 

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 

8 Gotchas & Troubleshooting 

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.

1\. The purchase\_month\_utc Trap 

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

2\. Integer vs Timestamp Date Fields 

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

3\. Reserved Word Errors 

**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"`. 

4\. Missing Subscribe & Save Data 

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

5\. Query Timeouts 

**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

6\. Aggregation Threshold Violations 

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

7\. How to Debug "0 Rows Returned" 

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