*fun fact: this blog first appeared in our Cherry Picked newsletter. To get access to exclusive monthly content, subscribe here.

AMC SQL is not like regular SQL; it’s the cool SQL.

But in actuality, AMC SQL can be more difficult to use and harder to pull insights from if not done correctly. Because Amazon Marketing Cloud is a clean room, there are higher aggregation thresholds put in place to protect a user’s information. And as a result, the SQL used to query the clean room needs to be adjusted to respect these aggregation thresholds.

If you (or your AMC analyst) wanted to query AMC to analyze which users purchased an ASIN from which campaign and for how much, it isn’t as simple as one would imagine.

In a normal non-AMC world, the user would be able to SELECT the columns “user_id”, “campaign”, and “total_product_sales” from the table and the output would be very detailed (see regular SQL columns in the image). You would then see that 2 users converted on the STV - Gigi - High LTV Lookalike campaign for $29.99 and 1 user converted on the Prime Video - Gigi - NTB Shopify Lookalike campaign. Great!

Well, in the AMC world, the user is not able to SELECT “user_id”. Why? The user_id field is a high aggregation threshold column, meaning it would be against privacy policies to “follow” one user in their journey and access details such as the zip code or state they purchased from. You’ll never be able to see user_id’s in the final output.

To adhere to this aggregation policy, we will need to adjust our SQL to count the user_id’s versus extract the actual user_id value. If you know SQL well, because you’re counting one of the values, you’ll also need to aggregate the other two columns you’re extracting, resulting in summing total_product_sales and grouping by campaign.

That’s not all. Once you’ve run your query, there is a rule where 2 or more (anonymous) users have to do the SAME action for you to get detailed results. In our example use case, 2 users did purchase on the STV - Gigi - High LTV Lookalike campaign therefore we get to view that information at an aggregated level (again, no user_id details but we counted the user_id’s thus it would be 2). We can also see the sum of the total_product_sales which is $29.99 + $29.99 = $59.98!

But…for the ONE user who purchased on Prime Video - Gigi - NTB Shopify Lookalike, unfortunately in the AMC SQL world, we cannot extract that detailed information. We will only see that 1 user purchased for $29.99 but no idea which campaign they did that on.

Because of this, AMC SQL needs to be crafted very carefully to extract the information wanted within these guardrails. But that’s what makes Gigi experts, and what makes it fun. Who doesn’t love a good challenge?

To learn more about how Gigi is building proprietary queries to surface the most impactful insights for your Streaming TV campaigns, reach out to us today 👇