If you use Amazon Marketing Cloud on your own—without the assistance of a platform like Intentwise Explore—you’re going to have to learn to customize queries.
The quick background: When you use AMC, you are asking the platform your pressing business questions—such as, “show me a list of shoppers who added to cart but did not purchase.” Those questions are posed in the form of queries written in SQL.
To get you started, Amazon gives you a library of several dozen instructional queries and audiences. But these queries have a number of important limitations. Inevitably, you’re going to want to modify those queries to meet your own specifications.
But how do you actually modify an instructional query? In theory, it’s just some code tweaks—but the more complex you get, the more difficult it becomes.
Our team customizes queries for clients every single day, so we know this process inside and out. And we know it can sometimes feel a bit opaque how queries actually work.
We wanted to pull back the curtain for a second, and show you what’s actually happening when you modify an instructional query or audience.
What do instructional queries actually say?
To start, we’re going to show you a very simple example—the instructional audience for shoppers who added a product to cart but didn’t purchase. This is a high-utility audience that you can’t create in DSP.
We’re choosing this example because it is one of the simplest instructional audiences available. Keep in mind that modifying pretty much any other instructional query or audience will be more complicated than this.
In SQL code, the “shoppers who added to cart but didn’t purchase” audience looks like this:
What is all of this SQL actually saying? It helps to think of AMC queries as a series of tables, where each table contains signals from a set of shopper actions. AMC works by joining together those parts of the table that fit your specifications, and then creating a new output based on your specification.
In this case, AMC’s instructional query references 2 tables:
- A table of shoppers sorted by their most recent purchase (called “max purchase event”)
- A table of shoppers sorted by their most recent add to cart event (called “max ATC event”)
Those tables might look a bit like this:
Once those tables are created, the work of narrowing down your audience comes in. The instructional query instructs AMC to create a pool of shoppers who added to cart but did not purchase.
That means it wants to find the set of shoppers whose most recent add to cart date was after their most recent purchase date.
We’re simplifying a bit, but in a sense what AMC does is take those tables and highlight the shoppers who added a product to cart that they have yet to purchase. Using the logic of SQL, you might visualize the process along these lines:
As you can see, user 1 and user 4 each had a last purchase date more recent than their last add-to-cart date. That means they are disqualified from our final audience.
The three other users—highlighted in green—had an add-to-cart date more recent than their last purchase date, meaning they added a product to cart that they have yet to purchase.
Those three users will make it into your final audience, producing a table of user 2, user 3, and user 5.
But how do I modify the instructional query?
What we just outlined is a very simple, boilerplate instructional query. What if you want to make it more specific? For instance, what if you want to track shoppers who added specific ASINs to their cart, but did not purchase them?
In this case, it’s a relatively simple change, amounting to adding another column to your existing tables.
In the SQL code, what you want to do is take those two groups—those add-to-cart and purchase tables—and then add “ASIN” as a filter for both.
The new column specifies which ASIN each customer bought or added to their cart. Like this:
Then, through SQL code, you tell AMC to narrow down the tables. You add just a few extra lines of code, such as in the following example. The SQL we added is in bold:
As before, you first want to find the users who added a product to cart more recently than they made a purchase. Once again, you’ll be left with users 2, 3, and 5.
But now, with our modifications to the instructional query, you only are interested in users who added specific ASINs to cart but did not purchase. We used the tracked_asin column in order to focus on those specific ASINs.
For example, let’s say you’re only interested in ASIN 3. That means you only want to see an audience of shoppers who added ASIN 3 to their cart but did not purchase it.
Your final table will look like this, with only user 5 in your audience:
How hard is modifying queries, really?
Even if you don’t have detailed knowledge of SQL, you might be able to get by with making a simple modification to an easy instructional query like this one. But remember, editing the code is only one part of the task of customizing a query or audience.
You also have to:
- Validate the query to be sure the data is accurate.
- Ensure your audience is larger than Amazon’s 2k shopper minimum threshold. Remember, as you add more constraints, you’re creating a higher probability that the audience will fall below that 2k minimum.
- Run a cost-benefit analysis of whether the audience is worth all the trouble in the first place. Even if it clears the 2k threshold, is it a big enough or valuable enough audience to make back the money you spend creating it?
- Examine all of the available data sets in AMC to verify you are using the best possible data sets, along with the proper columns in those data sets. It therefore helps to have someone on your team with domain knowledge of AMC. In this case, the proper field to customize the audience was the tracked_asin column.
Only after taking those steps will you know if your custom query is good to go.
What about more complicated modifications?
Oftentimes, you won’t just want to add one tiny ASIN filter to AMC’s instructional audiences. You may want to do something a bit more dramatic.
Say you want to create an audience of shoppers who were exposed to Sponsored Products and Sponsored Brands ads—but not Sponsored Display ads—and did not make a purchase.
You’ll want to take the instructional query audience, “Audience Exposed to Amazon Streaming TV Campaign but not Display Campaign,” and then modify it to replace “Exposed to Amazon Streaming TV Campaign” with “Exposed to SP and SB ads.” Then, you also need to add the requirement that these shoppers haven’t purchased.
What this audience calls for is an entirely new series of tables. First, build two common table expressions—one for people exposed to Sponsored Products ads and another for people exposed to Sponsored Brands ads. Join them together, so you have an audience of people exposed to both SP and SB.
Then, create a table of people who saw your Sponsored Display ads. Negate out all of the shoppers from your SP+SB table who saw the SD ads. Then, you’ll have a new table that is essentially (SP+SB) -SD.
Finally, you want to create a table of shoppers who purchased from you. Again, negate out those purchasers from your (SP+SB) -SD table. At long last, you’ll have your audience of people exposed to SB+SD, but not SD, who didn’t purchase.
This customization takes a lot of work. The original instructional query was about 20 lines of SQL code. Now, the final custom query will probably be more than double that—about 45 lines of code.
This modification is intensive enough that you will basically be writing a whole new query from scratch. It’s a lot of work, and it requires expertise. If you want to learn how to do more modifications like this, we have a dedicated SQL training module that is widely used across the industry.
Or, of course, you can just use Intentwise Explore to pull insights from Amazon Marketing Cloud. When you use Intentwise Explore, get access to a vast library of fully customizable queries and audiences, which you can schedule to run in the background.
Bonus: Get your ultimate AMC guide
We just published our newest whitepaper, “The Amazon Marketing Cloud Strategy Guide,” which is the most robust guide to AMC available online.
Across 16 pages, we break down the best practices for AMC, the key events you can track, the differences between free and paid datasets, and the benefit of uploading your 1P data to AMC.
If you’re still trying to make sense of AMC, this is the document for you. Share it with your team, share it with your clients, and level up the sophistication of your internal discussions about how to leverage AMC.
Download it now for free.
