Rule-based audience queries

Learn how to use Amazon Marketing Cloud (AMC) SQL for audience creation.

Always SELECT user_id

AMC audiences are cohorts of user_id values. When writing rule-based audience queries, the final SELECT statement should contain only user_id. For example, the query below generates an audience of all those user_id values that have added your products to cart in the query’s time window.

SELECT
  user_id
FROM  
  amazon_attributed_events_by_conversion_time_for_audiences
WHERE
  conversion_event_subtype = 'shoppingCart'

Determine audience size before submitting the audience

If you want to determine how large an audience is prior to submitting it, you can use AMC analytics queries to get that information. This is a best practice to follow, as audiences may fail to generate if they are of insufficient size. AMC rule-based audiences require a minimum audience size of 2,000 records; target 2,500 records or more to prevent failure. AMC lookalike audiences require a minimum of 500 and a maximum of 500,000.

Here’s an example of a query that you can use in AMC’s primary Query editor tab. This query example can help you count the number of user_id values given the same set of constraints used in the previous example:

SELECT
  COUNT(DISTINCT user_id)
FROM
  amazon_attributed_events_by_conversion_time  
WHERE
  conversion_event_subtype = 'shoppingCart'

In this example, you wrap the user_id in COUNT because user_id can never be returned in an analytics query.

Editing and duplicating audiences

Once you create an audience and it’s made available in Amazon DSP or for use in your sponsored ads campaigns, you can’t edit it. If your audience fails, however, you can edit the query, date range, and auto adjust the date. For more information, refer to Manage your Amazon Marketing Cloud audiences.

You can also create a copy of an existing audience to use as a starting point for a new audience. We recommend selecting a unique name and query for each audience. If you create a second audience with the same parameters (audience name, SQL query, and others), there will be two identical audiences differentiated by their audienceExecutionId, dspAudienceId, and dspCanonicalId.

Audience query results not sent to the S3 bucket

Unlike AMC analytics queries, the results of AMC audience queries can’t be exported from AMC and won’t be delivered to your S3 bucket (if connected). Instead, audiences are sent directly to Amazon DSP or your sponsored ads account.

Available tables for AMC audience queries

AMC audiences use the following unique set of tables that mirror those available in AMC for analytics queries:

Dataset category	Dataset	Available for Amazon DSP audience queries?	Available for sponsored ads audience queries?
Amazon ads	amazon_attributed_events_by_conversion_time_for_audiences	Y	Y
Amazon ads	amazon_attributed_events_by_traffic_time_for_audiences	Y	Y
Amazon ads	conversions_for_audiences	Y	Y
Amazon ads	conversions_with_relevance_for_audiences	Y	Y
Amazon ads	dsp_clicks_for_audiences	Y	Y
Amazon ads	dsp_impressions_by_matched_segments_for_audiences	Y	Y
Amazon ads	dsp_impressions_by_user_segments_for_audiences	Y	Y
Amazon ads	dsp_impressions_for_audiences	Y	Y
Amazon ads	dsp_views_for_audiences	Y	Y
Amazon ads	sponsored_ads_traffic_for_audiences	Y	Y
Paid features (subscription)	conversions_all_for_audiences	Y	N
Paid features (subscription)	audience_segments_amer_inmarket_for_audiences	Y	N
Paid features (subscription)	audience_segments_amer_lifestyle_for_audiences	Y	N
Paid features (subscription)	audience_segments_apac_inmarket_for_audiences	Y	N
Paid features (subscription)	audience_segments_apac_lifestyle_for_audiences	Y	N
Paid features (subscription)	audience_segments_eu_inmarket_for_audiences	Y	N
Paid features (subscription)	audience_segments_eu_lifestyle_for_audiences	Y	N
Paid features (subscription)	segment_metadata_for_audiences	Y	N
Advertiser Uploaded	First-party table names (Will not include the _for_audiences suffix)	Y	Y

Constraints for AMC audience creation

The _for_audiences tables almost exactly mirror their analytics counterparts. However there are some key differences, detailed below, which constrain AMC audience capabilities in certain use cases.

• The dsp_impressions_for_audiences table doesn’t contain the user_behavior_segment_ids and matched_behavior_segment_ids columns.
• Some tracked_asin values reflect products that are sensitive in nature; these ASIN values have been removed from both tracked_asin and tracked_item to prevent them from being used for audience creation.
• deal_id values aren’t available for querying from any of the following tables:
  • dsp_clicks_for_audiences
  • dsp_impressions_by_matched_segments_for_audiences
  • dsp_impressions_by_user_segments_for_audiences
  • dsp_impressions_for_audiences