ReferenceSource Models
Amazon Ads Models
Copy-paste SQL model definitions for Amazon Ads reporting.
Copy and Paste these Amazon Ads Models into your Data Layer to quickly go from raw data souces to business ready Reporting. Please note:
- Models must be added chronological order from top to bottom (so that they can reference each other).
- When adding your own models you must update the reference to include your source names.
Base
amazon_ad_campaign_performance_base
select
md5(campaign_id || '-' || date) as id --PK
-- Stable primary key generated from campaign_id and date
, date
-- The reporting date for the campaign metrics (usually in advertiser account timezone)
, campaign_id
-- Unique identifier for the Amazon Ads campaign
, impressions as n_impressions
-- Number of times ads from this campaign were shown
, clicks as n_clicks
-- Number of times ads from this campaign were clicked
, cost as total_spend
-- Total advertising cost for the campaign for the given date (in account currency)
, purchases_1_d as n_purchases_1d
-- Number of attributed purchases that occurred within 1 day of an ad click
, purchases_7_d as n_purchases_7d
-- Number of attributed purchases that occurred within 7 days of an ad click
, purchases_14_d as n_purchases_14d
-- Number of attributed purchases that occurred within 14 days of an ad click
, purchases_30_d as n_purchases_30d
-- Number of attributed purchases that occurred within 30 days of an ad click
, sales_1_d as total_revenue_1d
-- Attributed revenue from purchases within 1 day of an ad click
, sales_7_d as total_revenue_7d
-- Attributed revenue from purchases within 7 days of an ad click
, sales_14_d as total_revenue_14d
-- Attributed revenue from purchases within 14 days of an ad click
, sales_30_d as total_revenue_30d
-- Attributed revenue from purchases within 30 days of an ad click
, top_of_search_impression_share as pct_top_of_search_impression_share
-- Percentage of impressions that appeared in the Top of Search placement
from {{sources.amazon_ads.campaign_level_report}}