AMC cheat sheet: thresholds, joins and date functions on one page
In short: This page condenses AMC-SQL onto one printed page: the five aggregation thresholds from NONE to VERY HIGH, the supported JOIN types including the gap at RIGHT JOIN, the difference between UNION and UNION ALL and the key date functions. Print the page or save it as PDF through your browser.
Aggregation thresholds
Level
Minimum users per row
Note
NONE
1
mostly metrics such as impressions and clicks; no filtering restriction
LOW
2
mostly dimensions such as campaign_id; no filtering restriction
MEDIUM
100
for example bid_price; no filtering restriction
HIGH
100
no filters with literal values, for example not WHERE postal_code IN (10001, 10011)
VERY HIGH
never output
for example user_id; usable only inside aggregates, never in the final SELECT
JOIN and UNION
Construct
Note
INNER JOIN
only matching rows of both tables
LEFT JOIN
all rows on the left, NULL on the right without a match
RIGHT JOIN
not supported; use LEFT JOIN with the tables swapped
FULL JOIN, CROSS JOIN, non-equi JOIN
supported
UNION
keeps unique records; needs the same number of columns and types
UNION ALL
keeps all records including duplicates
Date functions
Function
Purpose
DATE_TRUNC('DAY', ts)
truncates to SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER or YEAR
CURRENT_DATE / CURRENT_TIMESTAMP
current date or time in UTC
DAYOFWEEK(date)
weekday 1 to 7, 1 is Sunday
DAYOFMONTH(date)
day of the month 1 to 31
EXTRACT(unit FROM expr)
pull one time unit, such as DOW, WEEK, MONTH, YEAR
CONVERT_TIME_ZONE_FROM_UTC(ts, 'Europe/Berlin')
convert a UTC timestamp to a time zone
EXTEND_TIME_WINDOW(table, retro, future)
extend a table window beyond the reporting period, applies to the whole query
Frequently used tables
dsp_impressions and dsp_clicks for Amazon DSP contacts.
sponsored_ads_traffic for sponsored ads contacts.
conversions and amazon_attributed_events_by_conversion_time or ..._by_traffic_time for conversions.
Many tables have a _for_audiences variant for building audiences.
To save: print the page and choose "Save as PDF" as the destination. Header, footer and navigation are hidden in print.