Amazon Marketing Cloud Attribution Model (Explained)
Do you think the sales that Amazon has attributed to one of your campaigns are 100% accurate, and was that campaign the only reason behind getting that order?
Maybe not.
Amazon by default attributes the sales to your campaign based on a last-touch attribution model, leaving brands with no visibility into the other interactions of the same customer before the purchase.
Fortunately, with the introduction of Amazon Marketing Cloud, things have changed.
Now, you can look into the same customer interaction from a different angle by changing the attribution model.
In this blog post, we will discuss what an attribution model is, the different types of attribution models available for use in Amazon Marketing Cloud, and how to use each of them.
What is the Attribution Model?
Think of it as a framework used in advertising to figure out how much credit to give different marketing campaigns or touchpoints for a customer’s conversion. It plays a crucial role in understanding how your different marketing activities translate into conversions or sales.
Why Attribution Model is Important?
An attribution model is essential for navigating the marketing funnel in Amazon advertising. Given the significance of Amazon ads in this process, an attribution model enables businesses to trace the impact of each ad interaction on the customer’s journey to conversion. Amazon attribution provides valuable insights, allowing marketers to attribute ad sales to specific campaigns and optimize budget allocation accordingly. This strategic approach ensures a more efficient and informed advertising strategy on the Amazon platform, enhancing the overall return on investment.
1. First Touch Attribution Model
First-touch attribution model emphasizes the initial interaction a customer has with an ad. In this model, credit for a conversion is assigned to the first touchpoint, providing insights into the entry point of a customer into the sales funnel. Advertisers leveraging first-touch attribution gain valuable insights into the channels and strategies that attract potential customers at the very beginning of their buying journey.
Benefits of First Touch Attribution
Uncovers the first touchpoint that introduces customers to your brand or product, providing insights into the entry point of the customer journey.
Helps in shaping strategies for brand awareness and top-of-funnel marketing by highlighting the channels that attract new customers.
Amazon Marketing Cloud Query for First Touch Attribution
You can use the following instructional query from Amazon to understand your ad performance if the sales are attributed to the first touchpoint campaign for a customer.
-- Instructional Query: Custom Attribution - First Touch --
/*
------- Customization Instructions -------
1) All advertisers should run the exploratory query first to select the appropriate campaign filters (required) and ASIN filters
(optional, but recommended for ASIN advertisers). Campaigns are updated at the top of the query. Search 'ASIN' to find ASIN filters.
2) To run this query across DSP and Sponsored Ads campaigns, search 'UPDATE' to identify all filters to update. Do not remove any sections.
3) To run this query across DSP campaigns only, search 'Sponsored Ads-specific'. Delete the Sponsored Ads sections.
Then search 'UPDATE' to update the remaining sections of the query.
4) To run this query across DSP campaigns only for pixel conversions, follow the instructions from #3.
Then search for 'pixel' in this query to change the conversion metric to pixel conversions.
You have the option to remove the calculated value for total_units_sold and total_product_sales, since they will not return values
for pixel conversions.
5) This query is view-aware by default for DSP campaigns.
To make this query non-view-aware for DSP campaigns, search 'DSP view-aware'. Remove this section.
6a) This query considers impressions and clicks for DSP.
The query will prioritize clicks over impressions. To only consider clicks for DSP attribution, search for 'UPDATE DSP Traffic Qualifications'.
6b) This query considers clicks only for Sponsored Products.
If you want to consider both clicks and impressions for Sponsored Products and prioritize clicks over impressions, search for 'UPDATE SP Traffic Qualifications'.
6c) This query considers clicks only for Sponsored Display.
If you want to consider both clicks and impressions for Sponsored Display and prioritize clicks over impressions, search for 'UPDATE SD Traffic Qualifications'.
6d) This query considers clicks only for Sponsored Brands.
If you want to consider both clicks and impressions for Sponsored Brands and prioritize clicks over impressions, search for 'UPDATE SB Traffic Qualifications'.
6e) To change the traffic priority and make clicks and impression equal, search 'UPDATE traffic priority'.
7) To run this query across Sponsored Ads campaigns only, search 'DSP-specific'. Delete the three DSP sections.
Then search 'UPDATE' to update the remaining sections of the query.
8) To change the lookback window from the default setting of 28 days, search 'UPDATE Lookback Window' for instructions.
*/
WITH ---------- start DSP-specific section 1 of 3 ----------
-- REQUIRED UPDATE: Add values for the DSP campaign_id(s)
dsp_campaigns (campaign_id) AS (
VALUES
(1111111111),
(2222222222),
(3333333333),
(4444444444)
),
dsp_traffic_type (traffic_event_type) AS (
/* OPTIONAL UPDATE DSP Traffic Qualifications: Delete '('IMP'),' below to attribute based on DSP clicks only.
To attribute based on the most recent traffic event (either the impression or click), do not make any changes below. */
VALUES
('IMP'),
('CLICK')
),
---------- end DSP-specific section 1 of 3 ----------
---------- start Sponsored Ads-specific section 1 of 3 ----------
sp_campaigns (campaign) AS (
/* REQUIRED UPDATE: Add Sponsored Ads campaign names. Use the query below if you do not have Sponsored Ads campaigns.
Please note we do not have campaign_id available yet for Sponsored Ads. */
VALUES
('campaign_name_1'),
('campaign_name_2'),
('campaign_name_3'),
('campaign_name_4')
),
sd_campaigns (campaign) AS (
/* REQUIRED UPDATE: Add Sponsored Ads campaign names. Use the query below if you do not have Sponsored Ads campaigns.
Please note we do not have campaign_id available yet for Sponsored Ads. */
VALUES
('campaign_name_1'),
('campaign_name_2'),
('campaign_name_3'),
('campaign_name_4')
),
sb_campaigns (campaign) AS (
/* REQUIRED UPDATE: Add Sponsored Ads campaign names. Use the query below if you do not have Sponsored Ads campaigns.
Please note we do not have campaign_id available yet for Sponsored Ads. */
VALUES
('campaign_name_1'),
('campaign_name_2'),
('campaign_name_3'),
('campaign_name_4')
),
sp_traffic_type (traffic_event_type) AS (
/* OPTIONAL UPDATE SP Traffic Qualifications: Uncomment '('IMP'),' below to attribute based on SP clicks + impressions and attribute based on the most recent traffic event
(either the impression or click), do not make any changes below. The default setting is to keep '('IMP'),' comment out to only consider SP clicks.*/
VALUES
-- ('IMP'),
('CLICK')
),
sd_traffic_type (traffic_event_type) AS (
/* OPTIONAL UPDATE SD Traffic Qualifications: Uncomment '('IMP'),' below to attribute based on SD clicks + impressions and attribute based on the most recent traffic event
(either the impression or click), do not make any changes below. The default setting is to keep '('IMP'),' comment out to only consider SD clicks.*/
VALUES
-- ('IMP'),
('CLICK')
),
sb_traffic_type (traffic_event_type) AS (
/* OPTIONAL UPDATE SB Traffic Qualifications: Uncomment '('IMP'),' below to attribute based on SB clicks + impressions and attribute based on the most recent traffic event
(either the impression or click), do not make any changes below. The default setting is to keep '('IMP'),' comment out to only consider SB clicks.*/
VALUES
-- ('IMP'),
('CLICK')
),
---------- end Sponsored Ads-specific section 1 of 3 ----------
traffic AS (
---------- start DSP-specific section 2 of 3 ----------
-- Read DSP impression events.
SELECT
user_id,
'dsp' AS ad_product_type,
campaign_id,
campaign,
impression_dt AS traffic_dt,
'IMP' AS traffic_event_type,
impressions,
0 AS clicks
FROM
/* OPTIONAL UPDATE Lookback Window: Include impression events from up to X days before conversion events. The setting below is 28 days.
It's not recommended to extend the window beyond 28 days for ASIN conversion advertisers because the conversions in conversions_with_relevance are only included
if a customer was served a traffic event over the last 28 days. Search 'custom attribution' in the instructional query library to learn more.
Pixel conversion advertisers have the option extend this beyond 28 days.
If updating, search 'TABLE(EXTEND_TIME_WINDOW' and update in all locations in this query. And also search 'match_age BETWEEN 0 AND 28' to update this as well. */
TABLE(
EXTEND_TIME_WINDOW('dsp_impressions', 'P28D', 'P0D')
)
WHERE
campaign_id IN (
SELECT
campaign_id
FROM
dsp_campaigns
)
AND user_id IS NOT NULL ---------- start DSP view-aware section ----------
-- Only include impressions for non-view-aware placements. For view-aware placements, we will use view events.
AND NOT COALESCE(supply_source_is_view_aware, TRUE)
AND NOT COALESCE(placement_is_view_aware, TRUE) -- Estimate whether or not the impression was viewable based on the view rate.
AND RANDOM() < COALESCE(placement_view_rate, supply_source_view_rate, 1.0)
UNION ALL
SELECT
user_id,
'dsp' AS ad_product_type,
campaign_id,
campaign,
event_dt AS traffic_dt,
'IMP' AS traffic_event_type,
viewable_impressions AS impressions,
0 AS clicks
FROM
/* OPTIONAL UPDATE Lookback Window: Include impression events from up to X days before conversion events. The setting below is 28 days.
It's not recommended to extend the window beyond 28 days for ASIN conversion advertisers because the conversions in conversions_with_relevance
are only included if a customer was served a traffic event over the last 28 days. Search 'custom attribution' in the instructional query library to learn more.
Pixel conversion advertisers have the option extend this beyond 28 days. If updating, search 'TABLE(EXTEND_TIME_WINDOW' and update in all locations in this query.
And also search 'match_age BETWEEN 0 AND 28' to update this as well.*/
TABLE(EXTEND_TIME_WINDOW('dsp_views', 'P28D', 'P0D'))
WHERE
-- dsp_views contains other types of events such as measurable impressions, so we filter to just views
event_type = 'VIEW'
OR event_type = 'SYNTHETIC_VIEW'
AND campaign_id IN (
SELECT
campaign_id
FROM
dsp_campaigns
)
AND user_id IS NOT NULL ---------- end DSP view-aware section ----------
UNION ALL
-- Read DSP click events.
SELECT
user_id,
'dsp' AS ad_product_type,
campaign_id,
campaign,
click_dt AS traffic_dt,
'CLICK' AS traffic_event_type,
0 AS impressions,
clicks
FROM
/* OPTIONAL UPDATE Lookback Window: Include click events from up to X days before conversion events. The setting below is 28 days.
It's not recommended to extend the window beyond 28 days for ASIN conversion advertisers. Pixel conversion advertisers have the option extend this beyond 28 days.
If updating, search 'TABLE(EXTEND_TIME_WINDOW' and update in all locations in this query. And also search 'match_age BETWEEN 0 AND 28' to update this as well.*/
TABLE(EXTEND_TIME_WINDOW('dsp_clicks', 'P28D', 'P0D'))
WHERE
campaign_id IN (
SELECT
campaign_id
FROM
dsp_campaigns
)
AND user_id IS NOT NULL ---------- end DSP-specific section 2 of 3----------
---------- start Sponsored Ads-specific section 2 of 3 (delete the line 'UNION ALL' if your query will omit DSP)----------
UNION ALL
-- Read Sponsored Ads click and impression events.
SELECT
user_id,
ad_product_type,
-- Sponsored Products do not have customer-facing campaign ids.
'' AS campaign_id,
campaign,
event_dt AS traffic_dt,
(
CASE
WHEN clicks < 1 THEN 'IMP'
ELSE 'CLICK'
END
) AS traffic_event_type,
impressions,
clicks
FROM
/*OPTIONAL UPDATE Lookback Window: Include impression and click events from up to X days before conversion events. The setting below is 28 days.
It's not recommended to extend the window beyond 28 days for Sponsored Ads.
If updating, search 'TABLE(EXTEND_TIME_WINDOW' and update in all locations in this query.
And also search 'match_age BETWEEN 0 AND 28' to update this as well.*/
TABLE(
EXTEND_TIME_WINDOW('sponsored_ads_traffic', 'P28D', 'P0D')
)
WHERE
campaign IN (
SELECT
campaign
FROM
sp_campaigns
)
AND user_id IS NOT NULL
UNION ALL
-- Read Sponsored Display click and impression events.
SELECT
user_id,
ad_product_type,
-- Sponsored Ads do not have customer-facing campaign ids.
'' AS campaign_id,
campaign,
event_dt AS traffic_dt,
(
CASE
WHEN clicks < 1 THEN 'IMP'
ELSE 'CLICK'
END
) AS traffic_event_type,
impressions,
clicks
FROM
/*OPTIONAL UPDATE Lookback Window: Include impression and click events from up to X days before conversion events. The setting below is 28 days.
It's not recommended to extend the window beyond 28 days for Sponsored Ads.
If updating, search 'TABLE(EXTEND_TIME_WINDOW' and update in all locations in this query.
And also search 'match_age BETWEEN 0 AND 28' to update this as well.*/
TABLE(
EXTEND_TIME_WINDOW('sponsored_ads_traffic', 'P28D', 'P0D')
)
WHERE
campaign IN (
SELECT
campaign
FROM
sd_campaigns
)
AND user_id IS NOT NULL
UNION ALL
-- Read Sponsored Brands click and impression events.
SELECT
user_id,
ad_product_type,
-- Sponsored Ads do not have customer-facing campaign ids.
'' AS campaign_id,
campaign,
event_dt AS traffic_dt,
(
CASE
WHEN clicks < 1 THEN 'IMP'
ELSE 'CLICK'
END
) AS traffic_event_type,
impressions,
clicks
FROM
/*OPTIONAL UPDATE Lookback Window: Include impression and click events from up to X days before conversion events. The setting below is 28 days.
It's not recommended to extend the window beyond 28 days for Sponsored Ads.
If updating, search 'TABLE(EXTEND_TIME_WINDOW' and update in all locations in this query.
And also search 'match_age BETWEEN 0 AND 28' to update this as well.*/
TABLE(
EXTEND_TIME_WINDOW('sponsored_ads_traffic', 'P28D', 'P0D')
)
WHERE
campaign IN (
SELECT
campaign
FROM
sb_campaigns
)
AND user_id IS NOT NULL ---------- end Sponsored Ads-specific section 2 of 3----------
),
-- Read conversion events with campaign relevance from the conversions_with_relevance data source.
cwr AS(
SELECT
conversion_id,
campaign_id,
campaign,
conversions,
total_units_sold,
total_product_sales,
user_id,
event_category,
event_dt
FROM
conversions_with_relevance
WHERE
-- OPTIONAL UPDATE change event_category to 'pixel' for pixel conversion advertisers.
event_category = 'purchase'
AND user_id IS NOT NULL
/* OPTIONAL UPDATE the ASINs based on your exploratory query.
The column tracked_item includes the ASINs tracked. */
-- AND tracked_item in ('ASIN1234', 'ASIN5678')
),
-- Join conversion events to traffic events based on the user ID and relevant campaign ID.
matched AS (
---------- start DSP-specific section 3 of 3 ----------
-- matched DSP conversions and traffic
SELECT
c.conversion_id,
c.campaign_id,
c.campaign,
IF(t.traffic_event_type = 'CLICK', 1, 2) AS match_type,
SECONDS_BETWEEN(t.traffic_dt, c.event_dt) AS match_age,
c.conversions,
c.total_units_sold,
c.total_product_sales,
c.user_id
FROM
cwr c
JOIN traffic t ON (
c.user_id = t.user_id
AND c.campaign_id = t.campaign_id
)
WHERE
t.campaign_id IN (
SELECT
campaign_id
FROM
dsp_campaigns
)
AND t.traffic_event_type IN (
SELECT
traffic_event_type
FROM
dsp_traffic_type
) ---------- end DSP-specific section 3 of 3 ----------
---------- start Sponsored Ads-specific section 3 of 3 (delete the line 'UNION ALL' if your query will omit DSP). ----------
UNION ALL
-- matched Sponsored Products conversions and traffic
SELECT
c.conversion_id,
c.campaign_id,
c.campaign,
IF(t.traffic_event_type = 'CLICK', 1, 2) AS match_type,
SECONDS_BETWEEN(t.traffic_dt, c.event_dt) AS match_age,
c.conversions,
c.total_units_sold,
c.total_product_sales,
c.user_id
FROM
traffic t
JOIN cwr c ON (
c.user_id = t.user_id
AND c.campaign = t.campaign
)
WHERE
t.campaign IN (
SELECT
campaign
FROM
sp_campaigns
)
AND t.traffic_event_type IN (
SELECT
traffic_event_type
FROM
sp_traffic_type
)
UNION ALL
-- matched Sponsored Display conversions and traffic
SELECT
c.conversion_id,
c.campaign_id,
c.campaign,
IF(t.traffic_event_type = 'CLICK', 1, 2) AS match_type,
SECONDS_BETWEEN(t.traffic_dt, c.event_dt) AS match_age,
c.conversions,
c.total_units_sold,
c.total_product_sales,
c.user_id
FROM
traffic t
JOIN cwr c ON (
c.user_id = t.user_id
AND c.campaign = t.campaign
)
WHERE
t.campaign IN (
SELECT
campaign
FROM
sd_campaigns
)
AND t.traffic_event_type IN (
SELECT
traffic_event_type
FROM
sd_traffic_type
)
UNION ALL
-- matched Sponsored Brands conversions and traffic
SELECT
c.conversion_id,
c.campaign_id,
c.campaign,
IF(t.traffic_event_type = 'CLICK', 1, 2) AS match_type,
SECONDS_BETWEEN(t.traffic_dt, c.event_dt) AS match_age,
c.conversions,
c.total_units_sold,
c.total_product_sales,
c.user_id
FROM
traffic t
JOIN cwr c ON (
c.user_id = t.user_id
AND c.campaign = t.campaign
)
WHERE
t.campaign IN (
SELECT
campaign
FROM
sb_campaigns
)
AND t.traffic_event_type IN (
SELECT
traffic_event_type
FROM
sb_traffic_type
) ---------- end Sponsored Ads-specific section 3 of 3 ----------
),
-- For each conversion event, rank all the matching traffic events based on match type and age, and filter out traffic outside of the lookback window.
ranked AS (
SELECT
campaign_id,
campaign,
--OPTIONAL UPDATE traffic priority if your query includes traffic from both clicks and impressions.
--By default, the ranking below prioritizes clicks over impressions. Remove 'match_type ASC,' to prioritize based on match_age only.
ROW_NUMBER() OVER(
PARTITION BY conversion_id
ORDER BY
match_age DESC,
match_type ASC
) AS match_rank,
conversions,
total_units_sold,
total_product_sales,
user_id
FROM
matched
WHERE
-- OPTIONAL UPDATE Filter out matches where the traffic event is after or more than 28 days before the conversion event.
match_age BETWEEN 0 AND 28 * 24 * 60 * 60
),
-- Filter to only the best matching traffic event for each conversion event.
-- Calculate conversion metric sums for each campaign.
attributed_conversions AS (
SELECT
r.campaign_id,
r.campaign,
COUNT(DISTINCT user_id) users_that_converted,
SUM(r.conversions) AS conversions,
SUM(r.total_units_sold) AS total_units_sold,
SUM(r.total_product_sales) AS total_product_sales
FROM
ranked r
WHERE
-- Filter to only the best matching traffic event.
r.match_rank = 1
GROUP BY
1,
2
),
-- Include campaign impressions, clicks and user reach info.
traffic_campaigns AS (
SELECT
campaign_id,
campaign,
ad_product_type,
COUNT(DISTINCT user_id) user_reach,
SUM(impressions) AS impressions,
SUM(clicks) AS clicks
FROM
traffic
GROUP BY
1,
2,
3
)
SELECT
'First Touch' AS attribution_model,
c.campaign_id,
c.campaign,
t.ad_product_type,
t.impressions,
t.clicks,
t.user_reach,
c.users_that_converted,
c.conversions,
c.users_that_converted / t.user_reach AS conversion_rate,
c.total_units_sold,
c.total_product_sales
FROM
attributed_conversions c
JOIN traffic_campaigns t ON c.campaign = t.campaignOutput
2. Last Touch Attribution Model
Contrary to the first touch, the last touch attribution model gives credit for a conversion solely to the final interaction before the conversion event. This model is particularly useful for advertisers looking to understand the specific touchpoints that directly contribute to the ultimate conversion. It helps identify the channels that are most effective in driving customers to complete a desired action. This is the default attribution model of most Amazon PPC campaigns.
Benefits of Last Touch Attribution
Pinpoints the specific touchpoints that directly contribute to conversions, aiding advertisers in understanding the critical steps in the decision-making process.
Streamlines budget allocation by focusing resources on the channels and interactions that play a decisive role in driving customers to complete desired actions.
Amazon Marketing Cloud Query for Last Touch Attribution
You can use the following instructional query from Amazon to understand your ad performance if the sales are attributed to the last touchpoint for a customer.
-- Instructional Query: Custom Attribution - Last Touch --
/*
------- Customization Instructions -------
1) All advertisers should run the exploratory query first to select the appropriate campaign filters (required) and ASIN filters
(optional, but recommended for ASIN advertisers). Campaigns are updated at the top of the query. Search 'ASIN' to find ASIN filters.
2) To run this query across DSP and Sponsored Ads campaigns, search 'UPDATE' to identify all filters to update. Do not remove any sections.
3) To run this query across DSP campaigns only, search 'Sponsored Ads-specific'. Delete the Sponsored Ads sections.
Then search 'UPDATE' to update the remaining sections of the query.
4) To run this query across DSP campaigns only for pixel conversions, follow the instructions from #3.
Then search for 'pixel' in this query to change the conversion metric to pixel conversions.
You have the option to remove the calculated value for total_units_sold and total_product_sales, since they will not return values
for pixel conversions.
5) This query is view-aware by default for DSP campaigns.
To make this query non-view-aware for DSP campaigns, search 'DSP view-aware'. Remove this section.
6a) This query considers impressions and clicks for DSP.
The query will prioritize clicks over impressions. To only consider clicks for DSP attribution, search for 'UPDATE DSP Traffic Qualifications'.
6b) This query considers clicks only for Sponsored Products.
If you want to consider both clicks and impressions for Sponsored Products and prioritize clicks over impressions, search for 'UPDATE SP Traffic Qualifications'.
6c) This query considers clicks only for Sponsored Display.
If you want to consider both clicks and impressions for Sponsored Display and prioritize clicks over impressions, search for 'UPDATE SD Traffic Qualifications'.
6d) This query considers clicks only for Sponsored Brands.
If you want to consider both clicks and impressions for Sponsored Brands and prioritize clicks over impressions, search for 'UPDATE SB Traffic Qualifications'.
6e) To change the traffic priority and make clicks and impression equal, search 'UPDATE traffic priority'.
If you include both clicks and impressions, the query will prioritize clicks over impressions.
To change this, search 'UPDATE traffic priority'.
7) To run this query across Sponsored Ads campaigns only, search 'DSP-specific'. Delete the three DSP sections.
Then search 'UPDATE' to update the remaining sections of the query.
8) To change the lookback window from the default setting of 28 days, search 'UPDATE Lookback Window' for instructions.
*/
WITH ---------- start DSP-specific section 1 of 3 ----------
-- REQUIRED UPDATE DSP Campaigns: Add values for the DSP campaign_id(s)
dsp_campaigns (campaign_id) AS (
VALUES
(1111111111),
(2222222222),
(3333333333),
(4444444444)
),
dsp_traffic_type (traffic_event_type) AS (
/* OPTIONAL UPDATE DSP Traffic Qualifications: Delete '('IMP'),' below to attribute based on DSP clicks only.
To attribute based on the most recent traffic event (either the impression or click), do not make any changes below.*/
VALUES
('IMP'),
('CLICK')
),
---------- end DSP-specific section 1 of 3 ----------
---------- start Sponsored Ads-specific section 1 of 3 ----------
sp_campaigns (campaign) AS (
-- REQUIRED UPDATE: Add Sponsored Ads campaign names. Use the query below if you do not have Sponsored Ads campaigns. Please note we do not have campaign_id available yet for Sponsored Ads.
VALUES
('campaign_name_1'),
('campaign_name_2'),
('campaign_name_3'),
('campaign_name_4')
),
sd_campaigns (campaign) AS (
/* REQUIRED UPDATE: Add Sponsored Ads campaign names. Use the query below if you do not have Sponsored Ads campaigns.
Please note we do not have campaign_id available yet for Sponsored Ads. */
VALUES
('campaign_name_1'),
('campaign_name_2'),
('campaign_name_3'),
('campaign_name_4')
),
sb_campaigns (campaign) AS (
/* REQUIRED UPDATE: Add Sponsored Ads campaign names. Use the query below if you do not have Sponsored Ads campaigns.
Please note we do not have campaign_id available yet for Sponsored Ads. */
VALUES
('campaign_name_1'),
('campaign_name_2'),
('campaign_name_3'),
('campaign_name_4')
),
sp_traffic_type (traffic_event_type) AS (
/* OPTIONAL UPDATE SP Traffic Qualifications: Uncomment '('IMP'),' below to attribute based on SP clicks + impressions and attribute based on the most recent traffic event
(either the impression or click), do not make any changes below. The default setting is to keep '('IMP'),' comment out to only consider SP clicks.*/
VALUES
-- ('IMP'),
('CLICK')
),
sd_traffic_type (traffic_event_type) AS (
/* OPTIONAL UPDATE SD Traffic Qualifications: Uncomment '('IMP'),' below to attribute based on SD clicks + impressions and attribute based on the most recent traffic event
(either the impression or click), do not make any changes below. The default setting is to keep '('IMP'),' comment out to only consider SD clicks.*/
VALUES
-- ('IMP'),
('CLICK')
),
sb_traffic_type (traffic_event_type) AS (
/* OPTIONAL UPDATE SB Traffic Qualifications: Uncomment '('IMP'),' below to attribute based on SB clicks + impressions and attribute based on the most recent traffic event
(either the impression or click), do not make any changes below. The default setting is to keep '('IMP'),' comment out to only consider SB clicks.*/
VALUES
-- ('IMP'),
('CLICK')
),
---------- end Sponsored Ads-specific section 1 of 3 ----------
traffic AS (
---------- start DSP-specific section 2 of 3 ----------
-- Read DSP impression events.
SELECT
user_id,
'dsp' AS ad_product_type,
campaign_id,
campaign,
impression_dt AS traffic_dt,
'IMP' AS traffic_event_type,
impressions,
0 AS clicks
FROM
/* OPTIONAL UPDATE Lookback Window: Include impression events from up to X days before conversion events. The setting below is 28 days.
It's not recommended to extend the window beyond 28 days for ASIN conversion advertisers because the conversions in conversions_with_relevance
are only included if a customer was served a traffic event over the last 28 days. Search 'custom attribution' in the instructional query library to learn more.
Pixel conversion advertisers have the option extend this beyond 28 days. If updating, search 'TABLE(EXTEND_TIME_WINDOW' and update in all locations in this query.
And also search 'match_age BETWEEN 0 AND 28' to update this as well. */
TABLE(
EXTEND_TIME_WINDOW('dsp_impressions', 'P28D', 'P0D')
)
WHERE
campaign_id IN (
SELECT
campaign_id
FROM
dsp_campaigns
)
AND user_id IS NOT NULL ---------- start DSP view-aware section ----------
-- Only include impressions for non-view-aware placements. For view-aware placements, we will use view events.
AND NOT COALESCE(supply_source_is_view_aware, TRUE)
AND NOT COALESCE(placement_is_view_aware, TRUE) -- Estimate whether or not the impression was viewable based on the view rate.
AND RANDOM() < COALESCE(placement_view_rate, supply_source_view_rate, 1.0)
UNION ALL
SELECT
user_id,
'dsp' AS ad_product_type,
campaign_id,
campaign,
event_dt AS traffic_dt,
'IMP' AS traffic_event_type,
viewable_impressions AS impressions,
0 AS clicks
FROM
/* OPTIONAL UPDATE Lookback Window: Include impression events from up to X days before conversion events. The setting below is 28 days.
It's not recommended to extend the window beyond 28 days for ASIN conversion advertisers because the conversions in conversions_with_relevance are only
included if a customer was served a traffic event over the last 28 days. Search 'custom attribution' in the instructional query library to learn more.
Pixel conversion advertisers have the option extend this beyond 28 days.
If updating, search 'TABLE(EXTEND_TIME_WINDOW' and update in all locations in this query. And also search 'match_age BETWEEN 0 AND 28' to update this as well. */
TABLE(EXTEND_TIME_WINDOW('dsp_views', 'P28D', 'P0D'))
WHERE
-- dsp_views contains other types of events such as measurable impressions, so we filter to just views
event_type = 'VIEW'
OR event_type = 'SYNTHETIC_VIEW'
AND campaign_id IN (
SELECT
campaign_id
FROM
dsp_campaigns
)
AND user_id IS NOT NULL ---------- end DSP view-aware section ----------
UNION ALL
-- Read DSP click events.
SELECT
user_id,
'dsp' AS ad_product_type,
campaign_id,
campaign,
click_dt AS traffic_dt,
'CLICK' AS traffic_event_type,
0 AS impressions,
clicks
FROM
/* OPTIONAL UPDATE Lookback Window: Include click events from up to X days before conversion events. The setting below is 28 days.
It's not recommended to extend the window beyond 28 days for ASIN conversion advertisers. Pixel conversion advertisers have the option extend this beyond 28 days.
If updating, search 'TABLE(EXTEND_TIME_WINDOW' and update in all locations in this query. And also search 'match_age BETWEEN 0 AND 28' to update this as well.*/
TABLE(EXTEND_TIME_WINDOW('dsp_clicks', 'P28D', 'P0D'))
WHERE
campaign_id IN (
SELECT
campaign_id
FROM
dsp_campaigns
)
AND user_id IS NOT NULL ---------- end DSP-specific section 2 of 3----------
---------- start Sponsored Ads-specific section 2 of 3 (delete the line 'UNION ALL' if your query will omit DSP)----------
UNION ALL
-- Read Sponsored Ads click and impression events.
SELECT
user_id,
ad_product_type,
-- Sponsored Ads do not have customer-facing campaign ids.
'' AS campaign_id,
campaign,
event_dt AS traffic_dt,
(
CASE
WHEN clicks < 1 THEN 'IMP'
ELSE 'CLICK'
END
) AS traffic_event_type,
impressions,
clicks
FROM
/* OPTIONAL UPDATE Lookback Window: Include impression and click events from up to X days before conversion events. The setting below is 28 days.
It's not recommended to extend the window beyond 28 days for Sponsored Ads. If updating, search 'TABLE(EXTEND_TIME_WINDOW' and update in all locations in this query.
And also search 'match_age BETWEEN 0 AND 28' to update this as well.*/
TABLE(
EXTEND_TIME_WINDOW('sponsored_ads_traffic', 'P28D', 'P0D')
)
WHERE
campaign IN (
SELECT
campaign
FROM
sp_campaigns
)
AND user_id IS NOT NULL
UNION ALL
-- Read Sponsored Display click and impression events.
SELECT
user_id,
ad_product_type,
-- Sponsored Ads do not have customer-facing campaign ids.
'' AS campaign_id,
campaign,
event_dt AS traffic_dt,
(
CASE
WHEN clicks < 1 THEN 'IMP'
ELSE 'CLICK'
END
) AS traffic_event_type,
impressions,
clicks
FROM
/*OPTIONAL UPDATE Lookback Window: Include impression and click events from up to X days before conversion events. The setting below is 28 days.
It's not recommended to extend the window beyond 28 days for Sponsored Ads.
If updating, search 'TABLE(EXTEND_TIME_WINDOW' and update in all locations in this query.
And also search 'match_age BETWEEN 0 AND 28' to update this as well.*/
TABLE(
EXTEND_TIME_WINDOW('sponsored_ads_traffic', 'P28D', 'P0D')
)
WHERE
campaign IN (
SELECT
campaign
FROM
sd_campaigns
)
AND user_id IS NOT NULL
UNION ALL
-- Read Sponsored Brands click and impression events.
SELECT
user_id,
ad_product_type,
-- Sponsored Ads do not have customer-facing campaign ids.
'' AS campaign_id,
campaign,
event_dt AS traffic_dt,
(
CASE
WHEN clicks < 1 THEN 'IMP'
ELSE 'CLICK'
END
) AS traffic_event_type,
impressions,
clicks
FROM
/*OPTIONAL UPDATE Lookback Window: Include impression and click events from up to X days before conversion events. The setting below is 28 days.
It's not recommended to extend the window beyond 28 days for Sponsored Ads.
If updating, search 'TABLE(EXTEND_TIME_WINDOW' and update in all locations in this query.
And also search 'match_age BETWEEN 0 AND 28' to update this as well.*/
TABLE(
EXTEND_TIME_WINDOW('sponsored_ads_traffic', 'P28D', 'P0D')
)
WHERE
campaign IN (
SELECT
campaign
FROM
sb_campaigns
)
AND user_id IS NOT NULL ---------- end Sponsored Ads-specific section 2 of 3----------
),
-- Read conversion events with campaign relevance from the conversions_with_relevance data source.
cwr AS(
SELECT
conversion_id,
campaign_id,
campaign,
conversions,
total_units_sold,
total_product_sales,
user_id,
event_category,
event_dt
FROM
conversions_with_relevance
WHERE
-- OPTIONAL UPDATE change event_category to 'pixel' for pixel conversion advertisers.
event_category = 'purchase'
AND user_id IS NOT NULL
/* OPTIONAL UPDATE the ASINs based on your exploratory query. The column tracked_item includes the ASINs tracked. */
-- AND tracked_item in ('ASIN1234', 'ASIN5678')
),
-- Join conversion events to traffic events based on the user ID and relevant campaign ID.
matched AS (
---------- start DSP-specific section 3 of 3 ----------
-- matched DSP conversions and traffic
SELECT
c.conversion_id,
c.campaign_id,
c.campaign,
IF(t.traffic_event_type = 'CLICK', 1, 2) AS match_type,
SECONDS_BETWEEN(t.traffic_dt, c.event_dt) AS match_age,
c.conversions,
c.total_units_sold,
c.total_product_sales,
c.user_id
FROM
cwr c
JOIN traffic t ON (
c.user_id = t.user_id
AND c.campaign_id = t.campaign_id
)
WHERE
t.campaign_id IN (
SELECT
campaign_id
FROM
dsp_campaigns
)
AND t.traffic_event_type IN (
SELECT
traffic_event_type
FROM
dsp_traffic_type
) ---------- end DSP-specific section 3 of 3 ----------
---------- start Sponsored Ads-specific section 3 of 3 (delete the line 'UNION ALL' if your query will omit DSP). ----------
UNION ALL
-- matched Sponsored Ads conversions and traffic
SELECT
c.conversion_id,
c.campaign_id,
c.campaign,
IF(t.traffic_event_type = 'CLICK', 1, 2) AS match_type,
SECONDS_BETWEEN(t.traffic_dt, c.event_dt) AS match_age,
c.conversions,
c.total_units_sold,
c.total_product_sales,
c.user_id
FROM
traffic t
JOIN cwr c ON (
c.user_id = t.user_id
AND c.campaign = t.campaign
)
WHERE
t.campaign IN (
SELECT
campaign
FROM
sp_campaigns
)
AND t.traffic_event_type IN (
SELECT
traffic_event_type
FROM
sp_traffic_type
)
UNION ALL
-- matched Sponsored Display conversions and traffic
SELECT
c.conversion_id,
c.campaign_id,
c.campaign,
IF(t.traffic_event_type = 'CLICK', 1, 2) AS match_type,
SECONDS_BETWEEN(t.traffic_dt, c.event_dt) AS match_age,
c.conversions,
c.total_units_sold,
c.total_product_sales,
c.user_id
FROM
traffic t
JOIN cwr c ON (
c.user_id = t.user_id
AND c.campaign = t.campaign
)
WHERE
t.campaign IN (
SELECT
campaign
FROM
sd_campaigns
)
AND t.traffic_event_type IN (
SELECT
traffic_event_type
FROM
sd_traffic_type
)
UNION ALL
-- matched Sponsored Brands conversions and traffic
SELECT
c.conversion_id,
c.campaign_id,
c.campaign,
IF(t.traffic_event_type = 'CLICK', 1, 2) AS match_type,
SECONDS_BETWEEN(t.traffic_dt, c.event_dt) AS match_age,
c.conversions,
c.total_units_sold,
c.total_product_sales,
c.user_id
FROM
traffic t
JOIN cwr c ON (
c.user_id = t.user_id
AND c.campaign = t.campaign
)
WHERE
t.campaign IN (
SELECT
campaign
FROM
sb_campaigns
)
AND t.traffic_event_type IN (
SELECT
traffic_event_type
FROM
sb_traffic_type
) ---------- end Sponsored Ads-specific section 3 of 3 ----------
),
-- For each conversion event, rank all the matching traffic events based on match type and age, and filter out traffic outside of the lookback window.
ranked AS (
SELECT
campaign_id,
campaign,
--OPTIONAL UPDATE traffic priority if your query includes traffic from both clicks and impressions.
--By default, the ranking below prioritizes clicks over impressions. Remove 'match_type ASC,' to prioritize based on match_age only.
ROW_NUMBER() OVER(
PARTITION BY conversion_id
ORDER BY
match_age ASC,
match_type ASC
) AS match_rank,
conversions,
total_units_sold,
total_product_sales,
user_id
FROM
matched
WHERE
-- Filter out matches where the traffic event is after or more than 28 days before the conversion event.
match_age BETWEEN 0 AND 28 * 24 * 60 * 60
),
-- Filter to only the best matching traffic event for each conversion event.
-- Calculate conversion metric sums for each campaign.
attributed_conversions AS (
SELECT
r.campaign_id,
r.campaign,
COUNT(DISTINCT user_id) users_that_converted,
SUM(r.conversions) AS conversions,
SUM(r.total_units_sold) AS total_units_sold,
SUM(r.total_product_sales) AS total_product_sales
FROM
ranked r
WHERE
-- Filter to only the best matching traffic event.
r.match_rank = 1
GROUP BY
1,
2
),
-- Include campaign impressions, clicks and user reach info.
traffic_campaigns AS (
SELECT
campaign_id,
campaign,
ad_product_type,
COUNT(DISTINCT user_id) user_reach,
SUM(impressions) AS impressions,
SUM(clicks) AS clicks
FROM
traffic
GROUP BY
1,
2,
3
)
SELECT
'Last Touch' AS attribution_model,
c.campaign_id,
c.campaign,
t.ad_product_type,
t.impressions,
t.clicks,
t.user_reach,
c.users_that_converted,
c.conversions,
c.users_that_converted / t.user_reach AS conversion_rate,
c.total_units_sold,
c.total_product_sales
FROM
attributed_conversions c
JOIN traffic_campaigns t ON c.campaign = t.campaignOutput
3. Linear Touch Attribution Model
The linear touch attribution model distributes credit for a conversion equally across all touchpoints in the customer’s journey. This model provides a holistic view of the customer’s interaction with the ad throughout the entire funnel. Linear touch attribution is beneficial for advertisers aiming to recognize the collective impact of various touchpoints in influencing the customer’s decision-making process.
Benefits of Linear Touch Attribution
Offers a comprehensive view of the customer journey by distributing credit equally across all touchpoints, helping advertisers understand the collective impact of various interactions.
Balances the attribution across the entire funnel, allowing advertisers to recognize the significance of each touchpoint in influencing customer decisions.
Amazon Marketing Cloud Query for Linear Touch Attribution
You can use the following instructional query from Amazon to understand your ad performance if the sales are equally distributed between each touchpoint of a customer.
-- Instructional Query: Custom Attribution - Linear Touch --
/*
------- Customization Instructions -------
1) All advertisers should run the exploratory query first to select the appropriate campaign filters (required) and ASIN filters
(optional, but recommended for ASIN advertisers). Campaigns are updated at the top of the query. Search 'ASIN' to find ASIN filters.
2) To run this query across DSP and Sponsored Ads campaigns, search 'UPDATE' to identify all filters to update. Do not remove any sections.
3) To run this query across DSP campaigns only, search 'Sponsored Ads-specific'. Delete the Sponsored Ads sections.
Then search 'UPDATE' to update the remaining sections of the query.
4) To run this query across DSP campaigns only for pixel conversions, follow the instructions from #3.
Then search for 'pixel' in this query to change the conversion metric to pixel conversions.
You have the option to remove the calculated value for total_units_sold and total_product_sales, since they will not return values
for pixel conversions.
5) This query is view-aware by default for DSP campaigns.
To make this query non-view-aware for DSP campaigns, search 'DSP view-aware'. Remove this section.
6a) This query considers impressions and clicks for DSP and Sponsored Products.
The query will prioritize clicks over impressions. To only consider clicks for DSP attribution, search for 'UPDATE DSP Traffic Qualifications'.
6b) This query considers clicks only for Sponsored Products.
If you want to consider both clicks and impressions for Sponsored Products and prioritize clicks over impressions, search for 'UPDATE SP Traffic Qualifications'.
6c) This query considers clicks only for Sponsored Display.
If you want to consider both clicks and impressions for Sponsored Display and prioritize clicks over impressions, search for 'UPDATE SD Traffic Qualifications'.
6d) This query considers clicks only for Sponsored Brands.
If you want to consider both clicks and impressions for Sponsored Brands and prioritize clicks over impressions, search for 'UPDATE SB Traffic Qualifications'.
6e) To change the traffic priority and make clicks and impression equal, search 'UPDATE traffic priority'.
7) To run this query across Sponsored Ads campaigns only, search 'DSP-specific'. Delete the three DSP sections.
Then search 'UPDATE' to update the remaining sections of the query.
8) To change the lookback window from the default setting of 28 days, search 'UPDATE Lookback Window' for instructions.
*/
WITH ---------- start DSP-specific section 1 of 3 ----------
-- REQUIRED UPDATE DSP Campaigns: Add values for the DSP campaign_id(s)
dsp_campaigns (campaign_id) AS (
VALUES
(1111111111),
(2222222222),
(3333333333),
(4444444444)
),
dsp_traffic_type (traffic_event_type) AS (
/* OPTIONAL UPDATE DSP Traffic Qualifications: Delete '('IMP'),' below to attribute based on DSP clicks only.
To attribute based on the most recent traffic event (either the impression or click), do not make any changes below.*/
VALUES
('IMP'),
('CLICK')
),
---------- end DSP-specific section 1 of 3 ----------
---------- start Sponsored Ads-specific section 1 of 3 ----------
sp_campaigns (campaign) AS (
/* REQUIRED UPDATE: Add Sponsored Ads campaign names. Use the query below if you do not have Sponsored Ads campaigns.
Please note we do not have campaign_id available yet for Sponsored Ads. */
VALUES
('campaign_name_1'),
('campaign_name_2'),
('campaign_name_3'),
('campaign_name_4')
),
sd_campaigns (campaign) AS (
/* REQUIRED UPDATE: Add Sponsored Ads campaign names. Use the query below if you do not have Sponsored Ads campaigns.
Please note we do not have campaign_id available yet for Sponsored Ads. */
VALUES
('campaign_name_1'),
('campaign_name_2'),
('campaign_name_3'),
('campaign_name_4')
),
sb_campaigns (campaign) AS (
/* REQUIRED UPDATE: Add Sponsored Ads campaign names. Use the query below if you do not have Sponsored Ads campaigns.
Please note we do not have campaign_id available yet for Sponsored Ads. */
VALUES
('campaign_name_1'),
('campaign_name_2'),
('campaign_name_3'),
('campaign_name_4')
),
sp_traffic_type (traffic_event_type) AS (
/* OPTIONAL UPDATE SP Traffic Qualifications:
Uncomment '('IMP'),' below to attribute based on SP clicks + impressions and attribute based on the most recent traffic event
(either the impression or click), do not make any changes below. The default setting is to keep '('IMP'),' comment out to only consider SP clicks.*/
VALUES
-- ('IMP'),
('CLICK')
),
sd_traffic_type (traffic_event_type) AS (
/* OPTIONAL UPDATE SD Traffic Qualifications: Uncomment '('IMP'),' below to attribute based on SD clicks + impressions and attribute based on the most recent traffic event
(either the impression or click), do not make any changes below. The default setting is to keep '('IMP'),' comment out to only consider SD clicks.*/
VALUES
-- ('IMP'),
('CLICK')
),
sb_traffic_type (traffic_event_type) AS (
/* OPTIONAL UPDATE SB Traffic Qualifications: Uncomment '('IMP'),' below to attribute based on SB clicks + impressions and attribute based on the most recent traffic event
(either the impression or click), do not make any changes below. The default setting is to keep '('IMP'),' comment out to only consider SB clicks.*/
VALUES
-- ('IMP'),
('CLICK')
),
---------- end Sponsored Ads-specific section 1 of 3 ----------
traffic AS (
---------- start DSP-specific section 2 of 3 ----------
-- Read DSP impression events.
SELECT
user_id,
'dsp' AS ad_product_type,
campaign_id,
campaign,
impression_dt AS traffic_dt,
'IMP' AS traffic_event_type,
impressions,
0 AS clicks
FROM
/* OPTIONAL UPDATE Lookback Window: Include impression events from up to X days before conversion events. The setting below is 28 days.
It's not recommended to extend the window beyond 28 days for ASIN conversion advertisers because the conversions in conversions_with_relevance are
only included if a customer was served a traffic event over the last 28 days. Search 'custom attribution' in the instructional query library to learn more.
Pixel conversion advertisers have the option extend this beyond 28 days. If updating, search 'TABLE(EXTEND_TIME_WINDOW' and update in all locations in this query.
And also search 'match_age BETWEEN 0 AND 28' to update this as well.*/
TABLE(
EXTEND_TIME_WINDOW('dsp_impressions', 'P28D', 'P0D')
)
WHERE
campaign_id IN (
SELECT
campaign_id
FROM
dsp_campaigns
)
AND user_id IS NOT NULL ---------- start DSP view-aware section ----------
-- Only include impressions for non-view-aware placements. For view-aware placements, we will use view events.
AND NOT COALESCE(supply_source_is_view_aware, TRUE)
AND NOT COALESCE(placement_is_view_aware, TRUE) -- Estimate whether or not the impression was viewable based on the view rate.
AND RANDOM() < COALESCE(placement_view_rate, supply_source_view_rate, 1.0)
UNION ALL
SELECT
user_id,
'dsp' AS ad_product_type,
campaign_id,
campaign,
event_dt AS traffic_dt,
'IMP' AS traffic_event_type,
viewable_impressions AS impressions,
0 AS clicks
FROM
/* OPTIONAL UPDATE Lookback Window: Include impression events from up to X days before conversion events. The setting below is 28 days.
It's not recommended to extend the window beyond 28 days for ASIN conversion advertisers because the conversions in conversions_with_relevance are only
included if a customer was served a traffic event over the last 28 days. Search 'custom attribution' in the instructional query library to learn more.
Pixel conversion advertisers have the option extend this beyond 28 days. If updating, search 'TABLE(EXTEND_TIME_WINDOW' and update in all locations in this query.
And also search 'match_age BETWEEN 0 AND 28' to update this as well. */
TABLE(EXTEND_TIME_WINDOW('dsp_views', 'P28D', 'P0D'))
WHERE
-- dsp_views contains other types of events such as measurable impressions, so we filter to just views
event_type = 'VIEW'
OR event_type = 'SYNTHETIC_VIEW'
AND campaign_id IN (
SELECT
campaign_id
FROM
dsp_campaigns
)
AND user_id IS NOT NULL ---------- end DSP view-aware section ----------
UNION ALL
-- Read DSP click events.
SELECT
user_id,
'dsp' AS ad_product_type,
campaign_id,
campaign,
click_dt AS traffic_dt,
'CLICK' AS traffic_event_type,
0 AS impressions,
clicks
FROM
/* OPTIONAL UPDATE Lookback Window: Include click events from up to X days before conversion events. The setting below is 28 days.
It's not recommended to extend the window beyond 28 days for ASIN conversion advertisers. Pixel conversion advertisers have the option extend this beyond 28 days.
If updating, search 'TABLE(EXTEND_TIME_WINDOW' and update in all locations in this query. And also search 'match_age BETWEEN 0 AND 28' to update this as well.*/
TABLE(EXTEND_TIME_WINDOW('dsp_clicks', 'P28D', 'P0D'))
WHERE
campaign_id IN (
SELECT
campaign_id
FROM
dsp_campaigns
)
AND user_id IS NOT NULL ---------- end DSP-specific section 2 of 3----------
---------- start Sponsored Ads-specific section 2 of 3 (delete the line 'UNION ALL' if your query will omit DSP)----------
UNION ALL
-- Read Sponsored Ads click and impression events.
SELECT
user_id,
ad_product_type,
-- Sponsored Ads do not have customer-facing campaign ids.
'' AS campaign_id,
campaign,
event_dt AS traffic_dt,
(
CASE
WHEN clicks < 1 THEN 'IMP'
ELSE 'CLICK'
END
) AS traffic_event_type,
impressions,
clicks
FROM
/* OPTIONAL UPDATE Lookback Window: Include impression and click events from up to X days before conversion events. The setting below is 28 days.
It's not recommended to extend the window beyond 28 days for Sponsored Ads.
If updating, search 'TABLE(EXTEND_TIME_WINDOW' and update in all locations in this query. And also search 'match_age BETWEEN 0 AND 28' to update this as well.*/
TABLE(
EXTEND_TIME_WINDOW('sponsored_ads_traffic', 'P28D', 'P0D')
)
WHERE
campaign IN (
SELECT
campaign
FROM
sp_campaigns
)
AND user_id IS NOT NULL
UNION ALL
-- Read Sponsored Display click and impression events.
SELECT
user_id,
ad_product_type,
-- Sponsored Ads do not have customer-facing campaign ids.
'' AS campaign_id,
campaign,
event_dt AS traffic_dt,
(
CASE
WHEN clicks < 1 THEN 'IMP'
ELSE 'CLICK'
END
) AS traffic_event_type,
impressions,
clicks
FROM
/*OPTIONAL UPDATE Lookback Window: Include impression and click events from up to X days before conversion events. The setting below is 28 days.
It's not recommended to extend the window beyond 28 days for Sponsored Ads.
If updating, search 'TABLE(EXTEND_TIME_WINDOW' and update in all locations in this query.
And also search 'match_age BETWEEN 0 AND 28' to update this as well.*/
TABLE(
EXTEND_TIME_WINDOW('sponsored_ads_traffic', 'P28D', 'P0D')
)
WHERE
campaign IN (
SELECT
campaign
FROM
sd_campaigns
)
AND user_id IS NOT NULL
UNION ALL
-- Read Sponsored Brands click and impression events.
SELECT
user_id,
ad_product_type,
-- Sponsored Ads do not have customer-facing campaign ids.
'' AS campaign_id,
campaign,
event_dt AS traffic_dt,
(
CASE
WHEN clicks < 1 THEN 'IMP'
ELSE 'CLICK'
END
) AS traffic_event_type,
impressions,
clicks
FROM
/*OPTIONAL UPDATE Lookback Window: Include impression and click events from up to X days before conversion events. The setting below is 28 days.
It's not recommended to extend the window beyond 28 days for Sponsored Ads.
If updating, search 'TABLE(EXTEND_TIME_WINDOW' and update in all locations in this query.
And also search 'match_age BETWEEN 0 AND 28' to update this as well.*/
TABLE(
EXTEND_TIME_WINDOW('sponsored_ads_traffic', 'P28D', 'P0D')
)
WHERE
campaign IN (
SELECT
campaign
FROM
sb_campaigns
)
AND user_id IS NOT NULL ---------- end Sponsored Ads-specific section 2 of 3----------
),
-- Read conversion events with campaign relevance from the conversions_with_relevance data source.
cwr AS(
SELECT
conversion_id,
campaign_id,
campaign,
conversions,
total_units_sold,
total_product_sales,
user_id,
event_category,
event_dt
FROM
conversions_with_relevance
WHERE
-- OPTIONAL UPDATE change event_category to 'pixel' for pixel conversion advertisers.
event_category = 'purchase'
AND user_id IS NOT NULL -- OPTIONAL UPDATE the ASINs based on your exploratory query. The column tracked_item includes the ASINs tracked.
-- AND tracked_item in ('ASIN1234', 'ASIN5678')
),
-- Join conversion events to traffic events based on the user ID and relevant campaign ID.
matched AS (
---------- start DSP-specific section 3 of 3 ----------
-- matched DSP conversions and traffic
SELECT
c.conversion_id,
c.campaign_id,
c.campaign,
IF(t.traffic_event_type = 'CLICK', 1, 2) AS match_type,
SECONDS_BETWEEN(t.traffic_dt, c.event_dt) AS match_age,
c.conversions,
c.total_units_sold,
c.total_product_sales,
c.user_id
FROM
cwr c
JOIN traffic t ON (
c.user_id = t.user_id
AND c.campaign_id = t.campaign_id
)
WHERE
t.campaign_id IN (
SELECT
campaign_id
FROM
dsp_campaigns
)
AND t.traffic_event_type IN (
SELECT
traffic_event_type
FROM
dsp_traffic_type
) ---------- end DSP-specific section 3 of 3 ----------
---------- start Sponsored Ads-specific section 3 of 3 (delete the line 'UNION ALL' if your query will omit DSP). ----------
UNION ALL
-- matched Sponsored Ads conversions and traffic
SELECT
c.conversion_id,
c.campaign_id,
c.campaign,
IF(t.traffic_event_type = 'CLICK', 1, 2) AS match_type,
SECONDS_BETWEEN(t.traffic_dt, c.event_dt) AS match_age,
c.conversions,
c.total_units_sold,
c.total_product_sales,
c.user_id
FROM
traffic t
JOIN cwr c ON (
c.user_id = t.user_id
AND c.campaign = t.campaign
)
WHERE
t.campaign IN (
SELECT
campaign
FROM
sp_campaigns
)
AND t.traffic_event_type IN (
SELECT
traffic_event_type
FROM
sp_traffic_type
)
UNION ALL
-- matched Sponsored Display conversions and traffic
SELECT
c.conversion_id,
c.campaign_id,
c.campaign,
IF(t.traffic_event_type = 'CLICK', 1, 2) AS match_type,
SECONDS_BETWEEN(t.traffic_dt, c.event_dt) AS match_age,
c.conversions,
c.total_units_sold,
c.total_product_sales,
c.user_id
FROM
traffic t
JOIN cwr c ON (
c.user_id = t.user_id
AND c.campaign = t.campaign
)
WHERE
t.campaign IN (
SELECT
campaign
FROM
sd_campaigns
)
AND t.traffic_event_type IN (
SELECT
traffic_event_type
FROM
sd_traffic_type
)
UNION ALL
-- matched Sponsored Brands conversions and traffic
SELECT
c.conversion_id,
c.campaign_id,
c.campaign,
IF(t.traffic_event_type = 'CLICK', 1, 2) AS match_type,
SECONDS_BETWEEN(t.traffic_dt, c.event_dt) AS match_age,
c.conversions,
c.total_units_sold,
c.total_product_sales,
c.user_id
FROM
traffic t
JOIN cwr c ON (
c.user_id = t.user_id
AND c.campaign = t.campaign
)
WHERE
t.campaign IN (
SELECT
campaign
FROM
sb_campaigns
)
AND t.traffic_event_type IN (
SELECT
traffic_event_type
FROM
sb_traffic_type
) ---------- end Sponsored Ads-specific section 3 of 3 ----------
),
-- Filter out matches that were outside of the traffic lookback window.
filtered AS (
SELECT
conversion_id,
campaign_id,
campaign,
match_age,
match_type,
-- Calculate the match rank for just this campaign-conversion event pair. Rank the match based on age.
-- The most recent event is prioritized.
ROW_NUMBER() OVER(
PARTITION BY conversion_id,
campaign
ORDER BY
match_age ASC
) AS match_rank,
user_id,
conversions,
total_units_sold,
total_product_sales
FROM
matched
WHERE
-- Filter out matches where the traffic event is after or more than X days before the conversion event. 28 days is used by default.
/* OPTIONAL UPDATE Lookback Window: Match age is reported in seconds.
If you updated the lookback window previously above from the default 28 days in the 'EXTEND_TIME_WINDOW' section(s),
change 28 (which represents 28 days) to your selected lookback window in days.*/
match_age BETWEEN 0 AND 28 * 24 * 60 * 60
),
/*Filter to only the best (based on match age) match for each campaign-conversion event pair so that we do not give
credit to the same campaign multiple times.*/
deduplicated AS (
SELECT
conversion_id,
campaign_id,
campaign,
conversions,
total_units_sold,
total_product_sales,
user_id
FROM
filtered
WHERE
/* Filter to only the best match for each traffic-conversion event pair.
The most recent event is prioritized. */
match_rank = 1
),
pruned AS (
SELECT
campaign_id,
campaign,
conversions,
total_units_sold,
total_product_sales,
-- Determine the total number of matches for each conversion.
COUNT(conversions) OVER(PARTITION BY conversion_id) AS total_match_count,
user_id
FROM
deduplicated
),
-- Calculate conversion metric sums for each campaign.
attributed_conversions AS (
SELECT
campaign_id,
campaign,
/* Divide up the metric sums by the total matches there were for the conversion
For every eligible conversion, each campaign will receive partial or full credit for the conversion. */
COUNT(DISTINCT user_id) AS users_that_converted,
SUM(conversions / total_match_count) AS conversions,
SUM(total_units_sold / total_match_count) AS total_units_sold,
SUM(total_product_sales / total_match_count) AS total_product_sales
FROM
pruned
GROUP BY
1,
2
),
-- Include campaign impressions, clicks and user reach info.
traffic_campaigns AS (
SELECT
campaign_id,
campaign,
ad_product_type,
COUNT(DISTINCT user_id) user_reach,
SUM(impressions) AS impressions,
SUM(clicks) AS clicks
FROM
traffic
GROUP BY
1,
2,
3
)
SELECT
'Linear' AS attribution_model,
c.campaign_id,
c.campaign,
t.impressions,
t.clicks,
round(t.user_reach) AS user_reach,
round(users_that_converted) AS users_that_converted,
round(c.conversions) AS conversions,
c.conversions / t.user_reach AS conversion_rate,
round(c.total_units_sold) AS total_units_sold,
round(c.total_product_sales, 2) AS total_product_sales
FROM
attributed_conversions c
JOIN traffic_campaigns t ON c.campaign = t.campaignOutput
4. Position-Based Attribution Model
Position-based attribution model, also known as U-shaped attribution, emphasizes both the first and last touchpoints in the customer’s journey. It allocates a higher percentage (70%) of credit to the last touchpoint while significantly lower (30%) to the first one, recognizing the significance of initiating and finalizing the conversion. You have the flexibility to decide the weightage between both ad interactions. For instance, you can split the credit evenly (50%/50%) or assign more credit to the first touch point or last touch point. This model is ideal for advertisers seeking the importance of both the initial and closing interactions in the attribution process.
Benefits of Position-Based Attribution
Emphasizes the importance of both the first and last touchpoints in the customer journey, but gives more weightage to the last touchpoint.
Guides advertisers in crafting comprehensive advertising strategies by acknowledging the significance of both initial engagement and the final conversion event.
Amazon Marketing Cloud Query for Position-Based Attribution
You can use the following instructional query from Amazon to understand your ad performance if 30% of sales are attributed to the first touchpoint while the remaining 70% weightage is attributed to the last touchpoint.
-- Instructional Query: custom attribution - position based - 30% first touch, 70% last touch --
-- Search 'UPDATE' to identify all filters to update.
/*
------- Customization Instructions -------
1) All advertisers should run the exploratory query first to select the appropriate campaign filters (required) and ASIN filters
(optional, but recommended for ASIN advertisers).
Campaigns are updated at the top of the query. Search 'ASIN' to find ASIN filters.
2) To run this query across DSP and Sponsored Ads campaigns, search 'UPDATE' to identify all filters to update. Do not remove any sections.
3) To run this query across DSP campaigns only, search 'Sponsored Ads-specific'. Delete the Sponsored Ads sections.
Then search 'UPDATE' to update the remaining sections of the query.
4) To run this query across DSP campaigns only for pixel conversions, follow the instructions from #3.
Then search for 'pixel' in this query to change the conversion metric to pixel conversions.
You have the option to remove the calculated value for total_units_sold and total_product_sales,
since they will not return values for pixel conversions.
5) This query is view-aware by default for DSP campaigns.
To make this query non-view-aware for DSP campaigns, search 'DSP view-aware'. Remove this section.
6a) This query considers impressions and clicks for DSP.
The query will prioritize clicks over impressions. To only consider clicks for DSP attribution, search for 'UPDATE DSP Traffic Qualifications'.
6b) This query considers clicks only for Sponsored Products.
If you want to consider both clicks and impressions for Sponsored Products and prioritize clicks over impressions, search for 'UPDATE SP Traffic Qualifications'.
6c) This query considers clicks only for Sponsored Display.
If you want to consider both clicks and impressions for Sponsored Display and prioritize clicks over impressions, search for 'UPDATE SD Traffic Qualifications'.
6d) This query considers clicks only for Sponsored Brands.
If you want to consider both clicks and impressions for Sponsored Brands and prioritize clicks over impressions, search for 'UPDATE SB Traffic Qualifications'.
6e) To change the traffic priority and make clicks and impression equal, search 'UPDATE traffic priority'.
7) To run this query across Sponsored Ads campaigns only, search 'DSP-specific'. Delete the three DSP sections.
Then search 'UPDATE' to update the remaining sections of the query.
8) To change the lookback window from the default setting of 28 days, search 'UPDATE Lookback Window' for instructions.
9) The default query setting is 30% credit for the first touch and 70% credit for the last touch.
To change this, search 'UPDATE weights' and update both locations.
*/
WITH ---------- start DSP-specific section 1 of 3 ----------
-- REQUIRED UPDATE DSP Campaigns: Add values for the DSP campaign_id(s)
dsp_campaigns (campaign_id) AS (
VALUES
(1111111111),
(2222222222),
(3333333333),
(4444444444)
),
dsp_traffic_type (traffic_event_type) AS (
/* OPTIONAL UPDATE DSP Traffic Qualifications: Delete '('IMP'),' below to attribute based on DSP clicks only.
To attribute based on the most recent traffic event (either the impression or click), do not make any changes below.*/
VALUES
('IMP'),
('CLICK')
),
---------- end DSP-specific section 1 of 3 ----------
---------- start Sponsored Ads-specific section 1 of 3 ----------
sp_campaigns (campaign) AS (
/* REQUIRED UPDATE: Add Sponsored Ads campaign names. Use the query below if you do not have Sponsored Ads campaigns.
Please note we do not have campaign_id available yet for Sponsored Ads. */
VALUES
('campaign_name_1'),
('campaign_name_2'),
('campaign_name_3'),
('campaign_name_4')
),
sd_campaigns (campaign) AS (
/* REQUIRED UPDATE: Add Sponsored Ads campaign names. Use the query below if you do not have Sponsored Ads campaigns.
Please note we do not have campaign_id available yet for Sponsored Ads. */
VALUES
('campaign_name_1'),
('campaign_name_2'),
('campaign_name_3'),
('campaign_name_4')
),
sb_campaigns (campaign) AS (
/* REQUIRED UPDATE: Add Sponsored Ads campaign names. Use the query below if you do not have Sponsored Ads campaigns.
Please note we do not have campaign_id available yet for Sponsored Ads. */
VALUES
('campaign_name_1'),
('campaign_name_2'),
('campaign_name_3'),
('campaign_name_4')
),
sp_traffic_type (traffic_event_type) AS (
/* OPTIONAL UPDATE SP Traffic Qualifications: Delete '('IMP'),' below to attribute based on SP clicks only.
To attribute based on the most recent traffic event (either the impression or click), do not make any changes below.*/
VALUES
-- ('IMP'),
('CLICK')
),
sd_traffic_type (traffic_event_type) AS (
/* OPTIONAL UPDATE SD Traffic Qualifications: Uncomment '('IMP'),' below to attribute based on SD clicks + impressions and attribute based on the most recent traffic event
(either the impression or click), do not make any changes below. The default setting is to keep '('IMP'),' comment out to only consider SD clicks.*/
VALUES
-- ('IMP'),
('CLICK')
),
sb_traffic_type (traffic_event_type) AS (
/* OPTIONAL UPDATE SB Traffic Qualifications: Uncomment '('IMP'),' below to attribute based on SB clicks + impressions and attribute based on the most recent traffic event
(either the impression or click), do not make any changes below. The default setting is to keep '('IMP'),' comment out to only consider SB clicks.*/
VALUES
-- ('IMP'),
('CLICK')
),
---------- end Sponsored Ads-specific section 1 of 3 ----------
traffic AS (
---------- start DSP-specific section 2 of 3 ----------
-- Read DSP impression events.
SELECT
user_id,
'dsp' AS ad_product_type,
campaign_id,
campaign,
impression_dt AS traffic_dt,
'IMP' AS traffic_event_type,
impressions,
0 AS clicks
FROM
/* OPTIONAL UPDATE Lookback Window: Include impression events from up to X days before conversion events. The setting below is 28 days.
It's not recommended to extend the window beyond 28 days for ASIN conversion advertisers because the conversions in conversions_with_relevance are only included if a customer was served a traffic event over the last 28 days. Search 'custom attribution' in the instructional query library to learn more.
Pixel conversion advertisers have the option extend this beyond 28 days.
If updating, search 'TABLE(EXTEND_TIME_WINDOW' and update in all locations in this query. And also search 'match_age BETWEEN 0 AND 28' to update this as well.*/
TABLE(
EXTEND_TIME_WINDOW('dsp_impressions', 'P28D', 'P0D')
)
WHERE
campaign_id IN (
SELECT
campaign_id
FROM
dsp_campaigns
)
AND user_id IS NOT NULL ---------- start DSP view-aware section ----------
-- Only include impressions for non-view-aware placements. For view-aware placements, we will use view events.
AND NOT COALESCE(supply_source_is_view_aware, TRUE)
AND NOT COALESCE(placement_is_view_aware, TRUE) -- Estimate whether or not the impression was viewable based on the view rate.
AND RANDOM() < COALESCE(placement_view_rate, supply_source_view_rate, 1.0)
UNION ALL
SELECT
user_id,
'dsp' AS ad_product_type,
campaign_id,
campaign,
event_dt AS traffic_dt,
'IMP' AS traffic_event_type,
viewable_impressions AS impressions,
0 AS clicks
FROM
/* OPTIONAL UPDATE Lookback Window: Include impression events from up to X days before conversion events. The setting below is 28 days.
It's not recommended to extend the window beyond 28 days for ASIN conversion advertisers because the conversions in conversions_with_relevance are
only included if a customer was served a traffic event over the last 28 days. Search 'custom attribution' in the instructional query library to learn more.
Pixel conversion advertisers have the option extend this beyond 28 days.
If updating, search 'TABLE(EXTEND_TIME_WINDOW' and update in all locations in this query. And also search 'match_age BETWEEN 0 AND 28' to update this as well.*/
TABLE(EXTEND_TIME_WINDOW('dsp_views', 'P28D', 'P0D'))
WHERE
-- dsp_views contains other types of events such as measurable impressions, so we filter to just views
event_type = 'VIEW'
OR event_type = 'SYNTHETIC_VIEW'
AND campaign_id IN (
SELECT
campaign_id
FROM
dsp_campaigns
)
AND user_id IS NOT NULL ---------- end DSP view-aware section ----------
UNION ALL
-- Read DSP click events.
SELECT
user_id,
'dsp' AS ad_product_type,
campaign_id,
campaign,
click_dt AS traffic_dt,
'CLICK' AS traffic_event_type,
0 AS impressions,
clicks
FROM
/* OPTIONAL UPDATE Lookback Window: Include click events from up to X days before conversion events. The setting below is 28 days.
It's not recommended to extend the window beyond 28 days for ASIN conversion advertisers. Pixel conversion advertisers have the option extend this beyond 28 days.
If updating, search 'TABLE(EXTEND_TIME_WINDOW' and update in all locations in this query. And also search 'match_age BETWEEN 0 AND 28' to update this as well.*/
TABLE(EXTEND_TIME_WINDOW('dsp_clicks', 'P28D', 'P0D'))
WHERE
campaign_id IN (
SELECT
campaign_id
FROM
dsp_campaigns
)
AND user_id IS NOT NULL ---------- end DSP-specific section 2 of 3----------
---------- start Sponsored Ads-specific section 2 of 3 (delete the line 'UNION ALL' if your query will omit DSP)----------
UNION ALL
-- Read Sponsored Ads click and impression events.
SELECT
user_id,
ad_product_type,
-- Sponsored Ads do not have customer-facing campaign ids.
'' AS campaign_id,
campaign,
event_dt AS traffic_dt,
(
CASE
WHEN clicks < 1 THEN 'IMP'
ELSE 'CLICK'
END
) AS traffic_event_type,
impressions,
clicks
FROM
/* OPTIONAL UPDATE Lookback Window: Include impression and click events from up to X days before conversion events. The setting below is 28 days.
It's not recommended to extend the window beyond 28 days for Sponsored Ads. If updating, search 'TABLE(EXTEND_TIME_WINDOW' and update in all locations in this query.
And also search 'match_age BETWEEN 0 AND 28' to update this as well.*/
TABLE(
EXTEND_TIME_WINDOW('sponsored_ads_traffic', 'P28D', 'P0D')
)
WHERE
campaign IN (
SELECT
campaign
FROM
sp_campaigns
)
AND user_id IS NOT NULL
UNION ALL
-- Read Sponsored Display click and impression events.
SELECT
user_id,
ad_product_type,
-- Sponsored Ads do not have customer-facing campaign ids.
'' AS campaign_id,
campaign,
event_dt AS traffic_dt,
(
CASE
WHEN clicks < 1 THEN 'IMP'
ELSE 'CLICK'
END
) AS traffic_event_type,
impressions,
clicks
FROM
/*OPTIONAL UPDATE Lookback Window: Include impression and click events from up to X days before conversion events. The setting below is 28 days.
It's not recommended to extend the window beyond 28 days for Sponsored Ads.
If updating, search 'TABLE(EXTEND_TIME_WINDOW' and update in all locations in this query.
And also search 'match_age BETWEEN 0 AND 28' to update this as well.*/
TABLE(
EXTEND_TIME_WINDOW('sponsored_ads_traffic', 'P28D', 'P0D')
)
WHERE
campaign IN (
SELECT
campaign
FROM
sd_campaigns
)
AND user_id IS NOT NULL
UNION ALL
-- Read Sponsored Brands click and impression events.
SELECT
user_id,
ad_product_type,
-- Sponsored Ads do not have customer-facing campaign ids.
'' AS campaign_id,
campaign,
event_dt AS traffic_dt,
(
CASE
WHEN clicks < 1 THEN 'IMP'
ELSE 'CLICK'
END
) AS traffic_event_type,
impressions,
clicks
FROM
/*OPTIONAL UPDATE Lookback Window: Include impression and click events from up to X days before conversion events. The setting below is 28 days.
It's not recommended to extend the window beyond 28 days for Sponsored Ads.
If updating, search 'TABLE(EXTEND_TIME_WINDOW' and update in all locations in this query.
And also search 'match_age BETWEEN 0 AND 28' to update this as well.*/
TABLE(
EXTEND_TIME_WINDOW('sponsored_ads_traffic', 'P28D', 'P0D')
)
WHERE
campaign IN (
SELECT
campaign
FROM
sb_campaigns
)
AND user_id IS NOT NULL ---------- end Sponsored Ads-specific section 2 of 3----------
),
-- Read conversion events with campaign relevance from the conversions_with_relevance data source.
cwr AS(
SELECT
conversion_id,
campaign_id,
campaign,
conversions,
total_units_sold,
total_product_sales,
user_id,
event_category,
event_dt
FROM
conversions_with_relevance
WHERE
-- OPTIONAL UPDATE change event_category to 'pixel' for pixel conversion advertisers.
event_category = 'purchase'
AND user_id IS NOT NULL -- OPTIONAL UPDATE the ASINs based on your exploratory query. The column tracked_item includes the ASINs tracked.
-- AND tracked_item in ('ASIN1234', 'ASIN5678')
),
-- Join conversion events to traffic events based on the user ID and relevant campaign ID.
matched AS (
---------- start DSP-specific section 3 of 3 ----------
-- matched DSP conversions and traffic
SELECT
c.conversion_id,
c.campaign_id,
c.campaign,
IF(t.traffic_event_type = 'CLICK', 1, 2) AS match_type,
SECONDS_BETWEEN(t.traffic_dt, c.event_dt) AS match_age,
c.conversions,
c.total_units_sold,
c.total_product_sales,
c.user_id
FROM
cwr c
JOIN traffic t ON (
c.user_id = t.user_id
AND c.campaign_id = t.campaign_id
)
WHERE
t.campaign_id IN (
SELECT
campaign_id
FROM
dsp_campaigns
)
AND t.traffic_event_type IN (
SELECT
traffic_event_type
FROM
dsp_traffic_type
) ---------- end DSP-specific section 3 of 3 ----------
---------- start Sponsored Ads-specific section 3 of 3 (delete the line 'UNION ALL' if your query will omit DSP). ----------
UNION ALL
-- matched Sponsored Products conversions and traffic
SELECT
c.conversion_id,
c.campaign_id,
c.campaign,
IF(t.traffic_event_type = 'CLICK', 1, 2) AS match_type,
SECONDS_BETWEEN(t.traffic_dt, c.event_dt) AS match_age,
c.conversions,
c.total_units_sold,
c.total_product_sales,
c.user_id
FROM
traffic t
JOIN cwr c ON (
c.user_id = t.user_id
AND c.campaign = t.campaign
)
WHERE
t.campaign IN (
SELECT
campaign
FROM
sp_campaigns
)
AND t.traffic_event_type IN (
SELECT
traffic_event_type
FROM
sp_traffic_type
)
UNION ALL
-- matched Sponsored Display conversions and traffic
SELECT
c.conversion_id,
c.campaign_id,
c.campaign,
IF(t.traffic_event_type = 'CLICK', 1, 2) AS match_type,
SECONDS_BETWEEN(t.traffic_dt, c.event_dt) AS match_age,
c.conversions,
c.total_units_sold,
c.total_product_sales,
c.user_id
FROM
traffic t
JOIN cwr c ON (
c.user_id = t.user_id
AND c.campaign = t.campaign
)
WHERE
t.campaign IN (
SELECT
campaign
FROM
sd_campaigns
)
AND t.traffic_event_type IN (
SELECT
traffic_event_type
FROM
sd_traffic_type
)
UNION ALL
-- matched Sponsored Brands conversions and traffic
SELECT
c.conversion_id,
c.campaign_id,
c.campaign,
IF(t.traffic_event_type = 'CLICK', 1, 2) AS match_type,
SECONDS_BETWEEN(t.traffic_dt, c.event_dt) AS match_age,
c.conversions,
c.total_units_sold,
c.total_product_sales,
c.user_id
FROM
traffic t
JOIN cwr c ON (
c.user_id = t.user_id
AND c.campaign = t.campaign
)
WHERE
t.campaign IN (
SELECT
campaign
FROM
sb_campaigns
)
AND t.traffic_event_type IN (
SELECT
traffic_event_type
FROM
sb_traffic_type
) ---------- end Sponsored Ads-specific section 3 of 3 ----------
),
-- Filter out matches that were outside of the traffic lookback window.
-- For each conversion event, rank all the matching traffic events based on match age.
ranked AS (
SELECT
campaign_id,
campaign,
/*OPTIONAL UPDATE traffic priority if your query includes traffic from both clicks and impressions.
By default, the ranking below prioritizes clicks over impressions. Remove 'match_type ASC,' to prioritize based on match_age only.*/
ROW_NUMBER() OVER(
PARTITION BY conversion_id
ORDER BY
match_type ASC,
match_age DESC
) AS first_match_rank,
ROW_NUMBER() OVER(
PARTITION BY conversion_id
ORDER BY
match_type ASC,
match_age ASC
) AS last_match_rank,
conversions,
total_units_sold,
total_product_sales,
user_id
FROM
matched
WHERE
--OPTIONAL UPDATE Filter out matches where the traffic event is after or more than 28 days before the conversion event.
match_age BETWEEN 0 AND 28 * 24 * 60 * 60
),
-- Filter to only the best matching campaign for first_match_rank and last_match_rank.
-- Calculate conversion metric sums for each campaign.
attributed_conversions AS (
SELECT
r.campaign_id,
r.campaign,
-- OPTIONAL UPDATE weights: change the weight assigned to the first touch point (The template assigns 30% to the first touchpoint)
COUNT(DISTINCT user_id) *.3 AS users_that_converted,
SUM(r.conversions) *.3 AS conversions,
SUM(r.total_units_sold) *.3 AS total_units_sold,
SUM(r.total_product_sales) *.3 AS total_product_sales
FROM
ranked r
WHERE
-- Filter to only the best matching traffic event.
r.first_match_rank = 1
GROUP BY
1,
2
UNION ALL
SELECT
a.campaign_id,
a.campaign,
-- OPTIONAL UPDATE weights: Change the weight assigned to the last touch point. The default value here is assigning 70% to the last touchpoint.
COUNT(DISTINCT user_id) *.7 AS users_that_converted,
SUM(a.conversions) *.7 AS conversions,
SUM(a.total_units_sold) *.7 AS total_units_sold,
SUM(a.total_product_sales) *.7 AS total_product_sales
FROM
ranked a
WHERE
a.last_match_rank = 1
GROUP BY
1,
2
),
total_attributed_conversions AS (
SELECT
campaign_id,
campaign,
SUM(users_that_converted) users_that_converted,
SUM(conversions) conversions,
SUM(total_units_sold) total_units_sold,
SUM(total_product_sales) total_product_sales
FROM
attributed_conversions
GROUP BY
1,
2
),
-- Include campaign impressions, clicks and user reach info.
traffic_campaigns AS (
SELECT
campaign_id,
campaign,
ad_product_type,
COUNT(DISTINCT user_id) user_reach,
SUM(impressions) AS impressions,
SUM(clicks) AS clicks
FROM
traffic
GROUP BY
1,
2,
3
)
SELECT
'Position Based' AS attribution_model,
c.campaign_id,
c.campaign,
t.impressions,
t.clicks,
round(t.user_reach) AS user_reach,
round(c.users_that_converted) AS users_that_converted,
round(c.conversions) AS conversions,
c.conversions / t.user_reach AS conversion_rate,
round(c.total_units_sold) AS total_units_sold,
round(c.total_product_sales, 2) AS total_product_sales
FROM
total_attributed_conversions c
JOIN traffic_campaigns t ON c.campaign = t.campaignOutput
How to leverage Amazon Marketing Cloud Attribution Queries
Identify High-Impact Touchpoints:
Leverage Amazon Marketing Cloud’s query capabilities to identify multiple touchpoints that have a significant impact on conversions. By analyzing data from various attribution models, pinpoint the channels and interactions that consistently contribute to successful outcomes, enabling you to focus your advertising strategy on these high-impact touchpoints.
Optimize Budget Allocation:
Use query insights to inform strategic decisions about budget allocation. Understand which channels and touchpoints are most effective in driving conversions, and allocate your budget accordingly. For example, if last-touch attribution indicates a specific channel’s effectiveness in closing sales, consider allocating a higher budget to maximize returns.
Gauge the Effectiveness of Awareness Campaigns
Are you running a lot of DSP or Sponsored Display ad campaigns to reach a new audience? Use the First Touch Attribution model to see the impact of your awareness ad in engaging with target customers.
Refine Messaging for Key Moments
Tailor your ad content and messaging based on insights from attribution queries. If the first touch attribution model reveals that certain touchpoints are crucial for initial engagement, ensure your content is compelling and relevant at these key moments. Similarly, if position-based attribution highlights the significance of both initial and final interactions, craft messages that resonate throughout the entire customer journey.
Test and Iterate Strategies
Constantly test and iterate your advertising strategies based on the insights gained from attribution queries. Experiment with different attribution models and analyze the results to refine your approach. This iterative process allows you to adapt to changing consumer behaviors, emerging trends, and evolving market dynamics, ensuring that your advertising strategy remains effective over time.
Frequently Asked Questions
What is Custom or Multi-touch attribution?
Custom or multi-touch attribution is an advanced approach to assessing the effectiveness of marketing touchpoints in a customer’s conversion path. Unlike single-touch models, it acknowledges and assigns value to multiple interactions, providing a more nuanced understanding of the customer journey.
What is a Marketing Funnel?
In the Amazon context, a marketing funnel outlines the stages a customer goes through before making a purchase. For example, generally, DSP or Sponsored Display ads are at the top of the funnel (for awareness) while Sponsored Product or Search Ads are crucial at the bottom of the funnel (for conversions)
What are some common challenges when attributing new-to-brand success on Amazon?
One challenge is the ‘last-click’ nature of ad attribution models. If someone sees your DSP ad early in their research journey but purchases your product through a Sponsored Product keyword-targeting ad, your SP ads will get the credit for the sale, and the actual ad that introduced your brand to a new customer will be overlooked. To combat this, consider using first-touch attribution or linear attribution to understand the impact of different campaigns in driving new-to-brand customers.
Can I see how many ads contributed to a conversion beyond the last touch?
Yes, you can use the “Path to Purchase” query in Amazon Marketing Cloud to find how many ads contributed to a conversion beyond the last touch.
What is the attribution model used for Conversion data displayed in the Amazon Advertising Console?
Amazon, by default, uses the last-touch attribution model to attribute conversions in the advertising console.
How Adbrew can help you?
Various attribution models offer significant value, but accessing them via Amazon Marketing Cloud may pose challenges for advertisers from the non-tech domain.
Thanks to Adbrew’s recent integration with Amazon Marketing Cloud, you can now effortlessly analyze your ad campaign performance using different attribution models in simple user-friendly charts and tables.
This powerful tool empowers you to gain valuable insights and answers to your most pressing business questions, allowing for informed decision-making and optimization of your advertising strategies.
This means you no longer have to delve into SQL or become an Amazon Marketing Cloud expert. Adbrew simplifies the process, providing you with actionable insights that can be immediately applied to enhance your advertising strategy.
Ready to get started with Adbrew?
Use Adbrew's AMC dashboard to view new-to-brand metrics for all your sponsored and DSP campaigns with ease.
Conclusion
Constantly test and iterate your advertising strategies based on the insights gained from attribution queries. Experiment with different attribution models and analyze the results to refine your approach. This iterative process allows you to adapt to changing consumer behaviors, emerging trends, and evolving market dynamics, ensuring that your advertising strategy remains effective over time.
Have you ever browsed through Amazon, adding items to your cart, but then got sidetracked and didn’t finish your purchase? […]
Having spent considerable time working with Amazon DSP ads, we’ve observed a common challenge: achieving and measuring optimal results from […]
In recent times, there has been notable growth in the Amazon Marketing Cloud market, with an increasing number of brands […]
Amazon Marketing Cloud offers free data analytics tools for analyzing advertising performance. However, the platform provides enhanced capabilities through its […]
Do you think the sales that Amazon has attributed to one of your campaigns are 100% accurate, and was that […]
Understanding the impact of our advertising efforts on attracting new customers is crucial for the growth of our brand. However, […]
Do you want to enhance the lifetime value of your customers on Amazon through targeted advertising campaigns? Upselling and cross-selling […]
It’s no secret that keyword targeting plays a crucial role in driving the audience to our product page on Amazon. […]
For brands navigating the complexities of Amazon advertising, understanding the customer journey from ad exposure to purchase used to be […]
As reported by Forbes, the global e-commerce marketplace boasts a staggering $6.3 trillion valuation, with Amazon contributing a significant 37.8% […]
In today’s data-driven era, brands can easily become disoriented without a complete understanding of how their target audiences are interacting […]
Do you think all of your customers purchase your product on the very first day when they are exposed to […]