Right now, plenty of agencies and brands are beginning to explore the possibilities of Amazon Marketing Cloud, Amazon’s newest ads measurement solution.
As we’ve discussed before, AMC lets you unlock everything from path-to-purchase mapping to New-To-Brand metrics for Sponsored Products ads. Then, you can turn your query results into custom audiences that you can target with ads later.
But how do you actually extract these insights? Most AMC newcomers will probably rely on one of AMC’s ~60 instructional queries.
These instructional queries include pre-written SQL code to get you started. Among the instructional queries that AMC offers, for instance, is an “Audience that Added to Cart but Did not Purchase.”
The instructional queries are a good start. But many of them need to be modified. What if you wanted to do something a little more sophisticated—or more custom—in AMC?
While the possibilities with AMC are pretty much limitless, the instructional query library does have limits:
- There are only so many instructional queries.
- Most of them are not designed in a way that will meet your brand’s unique reporting and analytics needs. Modifications are usually needed.
(An example of an instructional query in AMC)
How do instructional queries fall short?
Let’s take an example: path to purchase.
AMC’s instructional query for path to purchase automatically removes duplicate views of the same ad format. Let’s say a customer viewed a Sponsored Products ad, then another Sponsored Products ad, and then a Sponsored Display ad before converting.
The instructional query will remove the second Sponsored Products ad, and show the path as 1 SP, then 1 SD.
What if you didn’t want those duplicates removed? So that the path to purchase shows 1 SP, 1 SP, and 1 SD?
The answer: You’re probably going to have to write a custom SQL query.
We recently created a custom query for a client exactly along these lines. The client wanted to see a more complete view of how its ads were informing the path to purchase, so we wrote them a SQL query that didn’t condense the path to purchase.
Or take another example: AMC’s instructional path-to-purchase only tracks impressions, not clicks, when measuring the path to purchase.
That can get unwieldy quickly, considering it’s hard to visit Amazon without seeing a bunch of search ads.
What if you wanted to show only ads that a customer actually clicked in the path to purchase results? Again, you’ll need to write your own query.
These are the kinds of limitations that pop up all the time with instructional AMC queries.
Yes, the built-in queries give you a great starting place to pull incredible new kinds of data. But if you want to analyze that data in a way that speaks the language of your brand, you’re probably going to need to customize your queries.
How do you write a custom query?
Intentwise now writes custom queries for AMC as a part of Intentwise Analytics Services, so this topic has been top of mind.
So far, we’ve learned a few things from crafting custom queries for clients. Namely: Writing a custom AMC query is probably impossible if you’re not well versed in SQL and Amazon ads.
Amazon’s instructional queries have around 300 lines of code, and some sophisticated custom queries can stretch far longer.
So how does it work? One of the key tasks of creating custom queries is joining together disparate AMC tables.
You have to decide which tables to use, and then you have to unify them into a single query result.
For example: To create your own path-to-purchase query, you have to join together data tables using a series of customer user IDs. These user IDs will ensure that you’re tracking the same shopper across all of the tables.
In this case, we joined the following tables:
- dsp_impressions
- dsp_clicks
- sponsored_ads_traffic
- amazon_attributed_events_by_traffic_time
These aren’t simple coding tasks. Ultimately, to build a custom query, you need a few skill sets:
- You should know SQL front to back.
- You should be familiar with joining complex tables of data.
- You should have intimate knowledge of AMC and Amazon Ads, so nothing gets lost in translation.
Don’t forget: Validate, validate, validate
Many newcomers to AMC often forget the most critical step of building a new query: you have to validate that it produces the correct results.
Any number of errors can crop up during the coding process. Once you’ve written your query, you need to make sure the data it spits out is right. At Intentwise, for example, we run several rounds of validation for each of the SQL queries we write.
To do this, verify that the total number of impressions, sales, product units, etc from your AMC query matches your actual data for the same period.
If you skip the validation step, you risk relying on bad data. A non-validated SQL query could spit out inaccurate results that you might, unknowingly, use to make business decisions.
For instance, a faulty SQL query might suggest that one specific path to purchase is optimal for your brand, when in reality it isn’t one of the top paths at all.
If you don’t realize that your tables were joined poorly, you might put extra marketing spend behind this less-than-optimal path to purchase.
That’s why SQL and AMC expertise is so important right now. Instructional queries have their limits. And you also want to make sure you aren’t building faulty SQL queries of your own.
