To evaluate the performance of audiences targeted through bid boosts in Sponsored Products (SP) and Sponsored Brands (SB) campaigns, you can leverage Amazon Marketing Cloud (AMC). The SQL query provided below will help you analyze the effectiveness of these audiences by offering insights into how they engage across various campaigns and contribute to conversions.
This guide outlines the SQL query to use and the step-by-step instructions to run it in your AMC console.
SQL Query
Please use the following SQL query to analyze audience performance across different campaign types (SP, SB):
WITH campaigns (campaign_id_string, matched_behavior_segment_ids) AS (
SELECT
campaign_id_string,
matched_behavior_segment_ids
FROM
sponsored_ads_traffic
WHERE
matched_behavior_segment_ids IS NOT NULL
AND ad_product_type IN ('sponsored_products', 'sponsored_brands')
),
traffic AS (
SELECT
campaign,
campaign_id_string,
ad_product_type,
user_id,
matched_behavior_segment_ids,
event_id,
SUM(impressions) AS impressions,
SUM(clicks) AS clicks
FROM
sponsored_ads_traffic
WHERE
ad_product_type IN ('sponsored_products', 'sponsored_brands')
AND campaign_id_string IN (
SELECT
campaign_id_string
FROM
campaigns
)
GROUP BY
1,
2,
3,
4,
5,
6
),
attributed_events AS (
SELECT
campaign,
campaign_id_string,
ad_product_type,
user_id,
traffic_event_id,
SUM(purchases) AS purchases
FROM
amazon_attributed_events_by_conversion_time
WHERE
campaign_id_string IN (
SELECT
campaign_id_string
FROM
campaigns
)
GROUP BY
1,
2,
3,
4,
5
),
combined AS (
SELECT
t.campaign,
t.campaign_id_string,
t.ad_product_type,
t.matched_behavior_segment_ids,
t.impressions,
t.clicks,
t.user_id,
a.purchases
FROM
traffic t
LEFT JOIN attributed_events a ON t.campaign = a.campaign
AND t.user_id = a.user_id
AND t.event_id = a.traffic_event_id
)
SELECT
campaign,
campaign_id_string,
ad_product_type,
matched_behavior_segment_ids,
SUM(impressions) AS impressions,
SUM(clicks) AS clicks,
SUM(purchases) AS purchases
FROM
combined c
GROUP BY
1,
2,
3,
4
Steps to Run the Query in AMC
Log in to the AMC Console
Access your AMC instance using the credentials provided to your account.
Navigate to the Query Editor
Create a New Query
Click on the
+icon to start a new query.Paste the Query
Copy the SQL query provided above and paste it into the editor window.
Set the Date Range
Choose the appropriate date range during which the bid boosts were applied. This will ensure you are evaluating the correct performance window.
Run the Query
Click on “Run” to execute the query. Depending on your dataset, it may take 2–5 minutes.
Download the Results
Once the query has finished running, download the results to analyze how different audience exposures have contributed to conversions and performance metrics like impressions, spends, and sales.
Notes
To check the performance of audience targeted to SD campaigns, you can check the performance of the respective audience targeted in the campaign.
Feel free to reach out to the Adbrew team for any assistance.