Integration: Amazon Ads

This article will help you learn about how Daasity replicates data from Amazon Ads, limitations to the data we can extract and where the data is stored in the Amazon Ads schema.

Key Topics

Click on the links below to take you to the section where you can learn more about this Integration

Integration Overview

Daasity extracts data from 3 Amazon Ads solutions - Amazon Sponsored Products, Amazon Sponsored Display, and Amazon Sponsored Brands.

  • Amazon Sponsored Products are cost-per-click Amazon ads that help customers discover and purchase products that you sell on Amazon with ads that appear in shopping results and on product pages. 
  • Amazon Sponsored Display reaches relevant audiences who are browsing, discovering, or purchasing product on or off Amazon with ads that may appear on the Amazon home page, product detail pages, or shopping result pages as well as third-party websites and apps. 
  • Amazon Sponsored Brands are cost-per-click (CPC) ads that feature your brand logo, a custom headline, and multiple products. These ads appear in relevant shopping results and help drive discovery of your brand among customers shopping for products like yours. 

Integration Availability

This integration is available for:

  • Growth
  • Pro (V1 and V2)

NOTE: You are considered V2 if...

  • You are a Growth customer
  • Your account says Pro V2 in the Daasity App

Not sure? Reach out to Support@Daasity.com


API Endpoints

The Daasity Amazon Ads extractor is built based on this Amazon Ads API documentation. The following endpoints are used by Daasity to replicate data from Amazon Ads:

Amazon Ads API uses OAuth 2.0 for authenticating clients. Merchants will need to provide their SellerCentral login credentials and "authorize" Daasity to access their account.

The API parameters below outline what we need to obtain access_tokens thus to access data. Access token is only valid for 60 mins.

Entity Relationship Diagram (ERD)

The embedded diagram houses the ERD for the Daasity Amazon Ads integration illustrating the different tables and keys to join across tables.  Hover over the embedded diagram to reveal controls to zoom and scroll.

 

Amazon Ads Schema

The Daasity Amazon Ads extractor creates these tables using the endpoints and replication methods listed. The data is mapped from source API endpoint to the table based on the mapping logic outlined in each table.

Profiles

  • Endpoint: Profiles
  • Update Method: UPSERT
  • Table Name: amazon_ads.profiles
JSON Element Database Column
profileId profile_id
countryCode, limit: 8 country_code
currencyCode, limit: 5 currency_code
dailyBudget daily_budget
timezone timezone
accountInfo::marketplaceStringId account_info_marketplace_string_id
accountInfo::id account_info_id
accountInfo::type account_into_type
account_id _account_id
MD5(profileId + countryCode + currencyCode + dailyBudget + timezone + accountInfo::marketplaceStringId + accountInfo::id + accountInfo::type) __sync_key
Daasity: timestamp when loaded into DB __synced_at

Attribution

Attribution Advertisers

JSON Element Database Column
advertiserId advertiser_id
advertiserName advertiser_name
account_id _account_id
profile_id profile_id
MD5(advertiserId + advertiserName) __sync_key
Daasity: timestamp when loaded into DB __synced_at

Attribution Publishers

JSON Element Database Column
id publisher_id
name publisher_name
macroEnabled macro_enabled
account_id _account_id
profile_id profile_id
MD5(id + name + macroEnabled) __sync_key
Daasity: timestamp when loaded into DB __synced_at

Attribution Tags Macro-Enabled

JSON Element Database Column
publisher_ids[0] advertiser_id
publisher_ids[1] publisher_id
publisher_ids[2] publisher_tags
account_id _account_id
profile_id profile_id
MD5(publisher_ids[0] + publisher_ids[1] + publisher_ids[2]) __sync_key
Daasity: timestamp when loaded into DB __synced_at

Attribution Tags Non-Macro-Enabled

JSON Element Database Column
publisher_ids[0] advertiser_id
publisher_ids[1] publisher_id
publisher_ids[2] publisher_tags
account_id _account_id
profile_id profile_id
MD5(publisher_ids[0] + publisher_ids[1] + publisher_ids[2]) __sync_key
Daasity: timestamp when loaded into DB __synced_at

Attribution Reports

JSON Element Database Column
report_date report_date
creative_type creative_type
adGroupName ad_group_name
adGroupId  ad_group_id
 keywordId  keyword_id
 keywordText  keyword_text
 keywordStatus  keyword_status
 targetId  target_id
 targetingExpression  targeting_expression
 targetingText  targeting_text
 targetingType  targeting_type
 matchType  match_type
 currency  currency
 campaignName  campaign_name
 campaignId  campaign_id
 campaignStatus  campaignStatus
 campaignBudgetType  campaign_budget_type
 cost  cost
 impressions  impressions
 clicks  clicks
 campaignBudget  campaign_budget
 keywordBid  keyword_bid
 attributedDetailPageViewsClicks14d  attributed_detail_pageviewclicks_14d
 attributedSales14d  attributed_sales_14d
 attributedSales14dSameSKU attributed_sales_14d_same_sku
 attributedConversions14d  attributed_conversions_14d
 attributedConversions14dSameSKU  attributed_conversions_14d_same_sku
 attributedOrdersNewToBrand14d  attributed_orders_newtobrand_14d
 attributedOrdersNewToBrandPercentage14d  attributed_orders_newtobrand_percentage_14d
 attributedOrderRateNewToBrand14d  attributed_order_rate_newtobrand_14d
 attributedSalesNewToBrand14d  attributed_sales_newtobrand_14d
 attributedSalesNewToBrandPercentage14d  attributed_sales_newtobrand_percentage_14d
 attributedUnitsOrderedNewToBrand14d  attributed_units_ordered_newtobrand_14d
 attributedUnitsOrderedNewToBrandPercentage14d  attributed_units_ordered_newtobrand_percentage_14d
 unitsSold14d  units_sold_14d
 dpv14d  dpv_14d
 account_id  _account_id
 MD5(campaignId + advertiserName+ publisher + adGroupId + creativeId + report_date + profileId)  __sync_key
 Daasity: timestamp when loaded into DB  __synced_at

Sponsored Brands

Sponsored Brands Brands

JSON Element Database Column
brandId brand_id
brandEntityId brand_entity_id
brandRegistryName brand_registry_name
_account_id _account_id
profile_id profile_id
MD5(brandId + profile_id) __sync_key
Daasity: timestamp when loaded into DB __synced_at

Sponsored Brands Stores

JSON Element Database Column
entityID entity_id
storeName store_name
storePageInfo::storePageID store_page_id
storePageInfo::storePageURL store_page_url
storePageInfo::storePageName store_page_name
brandEntityID brand_entity_id
account_id _account_id
profile_id profile_id
MD5(entityID + storePageInfo::storePageID + profile_id) __sync_key
Daasity: timestamp when loaded into DB __synced_at

Sponsored Brands Campaigns

JSON Element Database Column
name name
budget budget
bidOptimization bid_optimization
portfolioId portfolio_id
adFormat ad_format
campaignId campaign_id
budgetType budget_type
startDate start_date
state state
servingStatus serving_status
creative::brandName creative_brand_name
creative::brandLogoAssetId creative_brand_logo_asset_id
creative::headline creative_headline
creative::shouldOptimizeAsins creative_should_optimize_asins
creative::asins creative_asins
creative::brandLogoUrl creative_brand_logo_url
landingPage::pageType landing_page_type
landingPage::url landing_page_url
account_id _account_id
profile_id profile_id
MD5(campaignId + profile_id) __sync_key
Daasity: timestamp when loaded into DB __synced_at

Sponsored Brands Ad Groups

JSON Element Database Column
adGroupId ad_group_id
name adgroup_name
campaignId campaign_id
account_id _account_id
profile_id profile_id
MD5(adGroupId + campaignId + profile_id) __sync_key
Daasity: timestamp when loaded into DB __synced_at

Sponsored Brands Keywords

JSON Element Database Column
keywordId keyword_id
adGroupId ad_group_id
campaignId campaign_id
keywordText keyword_text
matchType match_type
state state
bid bid
account_id _account_id
profile_id profile_id
MD5(campaignId + adGroupId + keywordId + profile_id) __sync_key
Daasity: timestamp when loaded into DB __synced_at

Sponsored Brands Negative Keywords

JSON Element Database Column
keywordId keyword_id
adGroupId ad_group_id
campaignId campaign_id
keywordText keyword_text
matchType match_type
state state
account_id _account_id
profile_id profile_id
MD5(campaignId + adGroupId + keywordId + profile_id) __sync_key
Daasity: timestamp when loaded into DB __synced_at

Sponsored Brands Product Targeting

JSON Element Database Column
targetId target_id
adGroupId ad_group_id
campaignId campaign_id
bid bid
state state
account_id _account_id
profile_id profile_id
MD5(adGroupId + targetId + campaignId + profile_id) __sync_key
Daasity: timestamp when loaded into DB __synced_at

Sponsored Brands Product Targeting Expressions

  • Endpoint: Sponsored Brands - Product Targeting Expressions
  • Update Method: UPSERT
  • Table Name: amazon_ads.sponsored_brands_product_targeting_expressions
JSON Element Database Column
targetId target_id
adGroupId ad_group_id
campaignId campaign_id
expressionType expression_type
type type
value value
account_id _account_id
profile_id profile_id
MD5(adGroupId + targetId + campaignId + profile_id) __sync_key
Daasity: timestamp when loaded into DB __synced_at

Sponsored Brands Reports

JSON Element Database Column
reportType(campaigns, keywords, adGroups) report_type
creativeType creative_type
date report_date
segment segment
campaignName campaign_name
campaignID campaign_id
campaignStatus campaign_status
campaignBudget campaign_budget
campaignBudgetType campaign_budget_type
adGroupName adgroup_name
adGroupId adgroup_id
keywordId keyword_id
keywordText keyword_text
keywordBid keyword_bid
keywordStatus keyword_status
targetID target_id
targetingExpression targeting_expression
targetingText targeting_text
targetingType targeting_type
matchType match_type
impressions impressions
clicks clicks
cost cost
attributedDetailPageViewsClicks14d attributed_detail_pageviewclicks_14d
attributedSales14d attributed_sales_14d
attributedSales14dSameSKU attributed_sales_14d_same_sku
attributedConversions14d attributed_conversions_14d
attributedConversions14dSameSKU attributed_conversions_14d_same_sku
attributedOrdersNewToBrand14d attributed_orders_newtobrand_14d
attributedOrdersNewToBrandPercentage14d attributed_orders_newtobrand_percentage_14d
attributedOrderRateNewToBrand14d attributed_order_rate_newtobrand_14d
attributedSalesNewToBrand14d attributed_sales_newtobrand_14d
attributedSalesNewToBrandPercentage14d attributed_sales_newtobrand_percentage_14d
unitsSold14d units_sold_14d
dpv14d dpv_14d
account_id _account_id
profile_id profile_id
MD5(campaignId + campaignName + adGroupName + adGroupId + keyId + keywordText + matchType + report_date + profile_id) __sync_key
Daasity: timestamp when loaded into DB __synced_at

Sponsored Display

Sponsored Display Campaigns

JSON Element Database Column
campaignId campaign_id
name campaign_name
tactic tactic
startDate start_date
state state
budget budget
servingStatus serving_status
creationDate created_at
lastUpdatedDate updated_at
budgetType budget_type
costType cost_type
deliveryProfile delivery_profile
account_id _account_id
profile_id profile_id
MD5(campaignId + profile_id) __sync_key
Daasity: timestamp when loaded into DB __synced_at

Sponsored Display Ad Groups

JSON Element Database Column
adGroupId ad_group_id
name ad_group_name
campaignId campaign_id
defaultBid default_bid
state state
servingStatus serving_status
creationDate created_at
lastUpdatedDate updated_at
tactic tactic
account_id _account_id
profile_id profile_id
MD5(adGroupId + campaignId + profileId) __sync_key
Daasity: timestamp when loaded into DB __synced_at

Sponsored Display Product Ads

JSON Element Database Column
adID ad_id
adGroupId ad_group_id
campaignId campaign_id
asin asin
sku sku
state state
ServingStatus serving_status
CreationDate created_at
LastUpdatedDate updated_at
account_id _account_id
profile_id profile_id
MD5(adGroupId + adId + campaignId + profileId) __sync_key
Daasity: timestamp when loaded into DB __synced_at

 Sponsored Display Targets

JSON Element Database Column
adGroupID ad_group_id
targetID target_id
expressionType expression_type
bid bid
state state
servingStatus serving_status
creationDate created_at
lastUpdatedDate updated_at
account_id _account_id
profile_id profile_id
MD5(adGroupId + targetId + profileId) __sync_key
Daasity: timestamp when loaded into DB __synced_at

Sponsored Display Target Expressions

  • Endpoint: Sponsored Display - Target Expressions
  • Update Method: UPSERT
  • Table Name: amazon_ads.sponsored_display_target_expressions
JSON Element Database Column
adGroupId ad_group_id
targetId target_id
expressionType expression_type
key type
value value
account_id _account_id
profile_id profile_id
MD5(adGroupId + targetId + profileId) __sync_key
Daasity: timestamp when loaded into DB __synced_at

Sponsored Display Reports

JSON Element Database Column
reportType(campaign, adGroup, product ads) report_type
tactic tactic
date report_date
campaignName campaign_name
campaignID campaign_id
impressions impressions
clicks clicks
cost cost
currency currency
attributedConversions1d attributed_conversions_1d
attributedConversions7d attributed_conversions_7d
attributedConversions14d attributed_conversion_14d
attributedConversions30d attributed_conversion_30d
attributedConversions1dSameSKU attributed_conversions_1d_same_sku
attributedConversions7dSameSKU attributed_conversions_7d_same_sku
attributedConversions14dSameSKU attributed_conversions_14d_same_sku
attributedConversions30dSameSKU attributed_conversions_30d_same_sku
attributedUnitsOrdered1d attributed_units_ordered_1d
attributedUnitsOrdered7d attributed_units_ordered_7d
attributedUnitsOrdered14d attributed_units_ordered_14d
attributedUnitsOrdered30d attributed_units_ordered_30d
attributedSales1d attributed_sales_1d
attributedSales7d attributed_sales_7d
attributedSales14d attributed_sales_14d
attributedSales30d attributed_sales_30d
attributedSales1dSameSKU attributed_sales_1d_same_sku
attributedSales7dSameSKU attributed_sales_7d_same_sku
attributedSales14dSameSKU attributed_sales_14d_same_sku
attributedSales30dSameSKU attributed_sales_30d_same_sku
adGroupName ad_group_name
adGroupID ad_group_id
asin asin
sku sku
adID ad_id
account_id _account_id
profile_id profile_id
MD5(campaignId + campaignName + adGroupName + adGroupId + asin + sku + adId + report_date + profileId) __sync_key
Daasity: timestamp when loaded into DB __synced_at

Sponsored Products

Sponsored Products Campaigns

JSON Element Database Column
campaignID campaign_id
name campaign_name
campaignType campaign_type
targetingType targeting_type
premiumBidAdjustment premium_bid_adjustments
dailyBudget daily_budget
startDate start_date
state state
portfolioID portfolio_id
servingStatus serving_status
creationDate created_at
lastUpdatedDate updated_at
account_id _account_id
profile_id profile_id
MD5(campaignId + profileId) __sync_key
Daasity: timestamp when loaded into DB __synced_at

Sponsored Products Campaigns Bid Strategy

  • Endpoint: Sponsored Products - Campaign Bidding Strategy
  • Update Method: UPSERT
  • Table Name: amazon_ads.sponsored_products_campaigns_bid_strategy
JSON Element Database Column
campaignID campaign_id
bidding::Strategy bid_strategy
bidding::Adjustments::Predicate bid_adjustment_predicate
bidding::Adjustments::Percentage bid_adjustment_percentage
account_id _account_id
profile_id profile_id
MD5(campaignId + bidding::strategy + profileId) __sync_key
Daasity: timestamp when loaded into DB __synced_at

Sponsored Products Ad Groups

JSON Element Database Column
adgroupID ad_group_id
name adgroup_name
campaignID campaign_id
defaultBid default_bid
state state
servingStatus serving_status
creationDate created_at
lastUpdatedDate updated_at
account_id _account_id
profile_id profile_id
MD5(adGroupId + profileId) __sync_key
Daasity: timestamp when loaded into DB __synced_at

Sponsored Products Ad Group Bid Recommendations

JSON Element Database Column
adGroupId ad_group_id
suggestedBid::rangeEnd suggested_bid_range_end
suggestedBid::rangeStart suggested_bid_range_start
suggestedBid::suggested suggested_bid_suggested
current_timestamp created_at
current_timestamp updated_at
account_id _account_id
profile_id profile_id
MD5(adGroupId + profile_id) __sync_key
Daasity: timestamp when loaded into DB __synced_at

Sponsored Products Ad Group Keywords

JSON Element Database Column
keywordId keyword_id
adGroupId adgroup_id
campaignId campaign_id
keywordText keyword_text
matchType match_type
state state
bid bid
servingStatus serving_status
creationDate created_at
lastUpdatedDate updated_at
account_id _account_id
profile_id profile_id
MD5(keywordId + profile_id) __sync_key
Daasity: timestamp when loaded into DB __synced_at

Sponsored Products Keyword Bid Recommendations

JSON Element Database Column
keywordId keyword_id
adGroupId ad_group_id
suggestedBid::rangeEnd suggested_bid_range_end
suggestedBid::rangeStart suggested_bid_range_start
suggestedBid::suggested suggested_bid_suggested
current_timestamp created_at
current_timestamp updated_at
account_id _account_id
profile_id profile_id
MD5(keywordId + profile_id) __sync_key
Daasity: timestamp when loaded into DB __synced_at

Sponsored Products Suggested Keywords

JSON Element Database Column
adGroupId ad_group_id
keywordText keyword_text
matchType match_type
current_timestamp created_at
current_timestamp updated_at
account_id _account_id
profile_id profile_id
MD5(ad_group_id + keywordText + matchType + profile_id)  __sync_key
Daasity: timestamp when loaded into DB __synced_at

Sponsored Products Product Ads

JSON Element Database Column
adId ad_id
adGroupId ad_group_id
campaignId campaign_id
asin asin
sku sku
state state
servingStatus serving_status
creationDate created_at
lastUpdatedDate updated_at
account_id _account_id
profile_id profile_id
MD5(adId + profile_id)  __sync_key
Daasity: timestamp when loaded into DB __synced_at

Sponsored Products Reports

JSON Element Database Column
ReportType(campaign, keyword, adGroup, productAds) report_type
bigplus bigplus
date report_date
segment segment
campaignName campaign_name
campaignId campaign_id
adGroupName ad_group_name
adGroupId ad_group_id
keywordId keyword_id
keywordText keyword_text
matchType match_type
campaignStatus campaign_status
campaignBudget campaign_budget
targetID target_id
targetingExpression targeting_expression
targetingText targeting_text
targetingType targeting_type
impressions impressions
clicks clicks
cost cost
currency currency
asin asin
otherAsin other_asin
sku sku
attributedConversions1d attributed_conversions_1d
attributedConversions7d attributed_conversions_7d
attributedConversions14d attributed_conversion_14d
attributedConversions30d attributed_conversion_30d
attributedConversions1dSameSKU attributed_conversions_1d_same_sku
attributedConversions7dSameSKU attributed_conversions_7d_same_sku
attributedConversions14dSameSKU attributed_conversions_14d_same_sku
attributedConversions30dSameSKU attributed_conversions_30d_same_sku
attributedUnitsOrdered1d attributed_units_ordered_1d
attributedUnitsOrdered7d attributed_units_ordered_7d
attributedUnitsOrdered14d attributed_units_ordered_14d
attributedUnitsOrdered30d attributed_units_ordered_30d
attributedUnitsOrdered1dOtherSKU attributed_units_ordered_1d_osku
attributedUnitsOrdered7dOtherSKU attributed_units_ordered_7d_osku
attributedUnitsOrdered14dOtherSKU attributed_units_ordered_14d_osku
attributedUnitsOrdered30dOtherSKU attributed_units_ordered_30d_osku
attributedSales1d attributed_sales_1d
attributedSales7d attributed_sales_7d
attributedSales14d attributed_sales_14d
attributedSales30d attributed_sales_30d
attributedSales1dSameSKU attributed_sales_1d_same_sku
attributedSales7dSameSKU attributed_sales_7d_same_sku
attributedSales14dSameSKU attributed_sales_14d_same_sku
attributedSales30dSameSKU attributed_sales_30d_same_sku
attributedSales1dOtherSKU attributed_sales_1d_osku
attributedSales7dOtherSKU attributed_sales_7d_osku
attributedSales14dOtherSKU attributed_sales_14d_osku
attributedSales30dOtherSKU attributed_sales_30d_osku
attributedUnitsOrdered1dSameSKU attributed_units_ordered_1d_same_sku
attributedUnitsOrdered7dSameSKU attributed_units_ordered_7d_same_sku
attributedUnitsOrdered14dSameSKU attributed_units_ordered_14d_same_sku
attributedUnitsOrdered30dSameSKU attributed_units_ordered_30d_same_sku
account_id _account_id
profile_id profile_id
MD5(campaignId + campaignName + adGroupName + adGroupId + asin + sku + keyword + targetId + profile_id)  __sync_key
Daasity: timestamp when loaded into DB __synced_at

Related Resources

  • Dashboard: Marketing
  • Check out this Digestible Data video for more information on Amazon Ads data: