SmartScout Data Lake

Amazon marketplace data, in your warehouse, query-ready.

The SmartScout Data Lake delivers structured Amazon datasets to your Snowflake, Google BigQuery, or Databricks environment, or to Amazon S3. No pipelines to build, no scrapers to maintain, no API rate limits to work around. You write SQL against billions of rows the same way you query your own tables.

Snowflake, BigQuery, Databricks, and S3 Core tables refresh 2× weekly 6–24 months of backfilled history
Existing enterprise subscription?
Talk to your account manager
Interested to learn more?
Talk to sales
Browse the table catalog
worksheet — smartscoutus.public Snowflake
select
  b.Name, h.Date,
  sum(h.EstimatedUnitSales) as Units,
  sum(h.EstimatedUnitSales * h.BuyBoxPrice) as Revenue
from smartscoutus.public.ProductHistories h
join smartscoutus.public.Products p on p.Id = h.ProductId
join smartscoutus.public.Brands b on b.Id = p.BrandId
where b.Name = 'Your brand'
group by 1, 2
order by h.Date;
Returns
Name Date Units Revenue
02

At a glance

The full specification, in one screenshot.

Delivery method
Native data share on Snowflake, Google BigQuery, and Databricks, or delivery to Amazon S3
Warehouses supported
Snowflake, Google BigQuery, Databricks, and Amazon S3
Cloud providers
AWS, Azure, GCP, etc.
Refresh cadence
Core tables refresh 2× weekly on Snowflake. All other tables, and all other platforms, refresh at least weekly.
Historical depth
6–24 months on the historical tables. Current-state tables always reflect the most recently tracked values.
Time series grain
Weekly for ProductHistories, BrandCoverageHistories, and SearchTermHistories.
Marketplaces
Amazon US, Canada, Mexico, UK, Germany, France, Italy, and Spain. Search term and advertising tables cover US, UK, and Germany. TikTok Shop tables cover US.
Query limits
None. You query on your own compute.
Portal access included
Yes. Data Lake clients receive SmartScout web app seats and access at the enterprise level.
Setup time
Typically within two business days, depending on your platform, region, and cloud provider.
03

How the share works

The steps below are for Snowflake. For Google BigQuery, Databricks, or Amazon S3, your account manager sets up delivery with you. Databricks teams can also query our BigQuery listing.

Snowflake Secure Data Sharing means SmartScout grants your account read access to live tables in ours. Nothing gets copied into your storage, nothing gets ingested, and you never pay to store our data. When we refresh a table, you see the new rows on your next query.

Before you start, have these ready

  1. Your Snowflake account locator — under Admin → Accounts, or run SELECT CURRENT_ACCOUNT()
  2. Your Snowflake region
  3. Your cloud provider — AWS, Azure, or GCP
  4. A user with IMPORT SHARE privilege, or an ACCOUNTADMIN who can create the database from the share
1
Scope the share
Pick your tables, fields where field-level scoping applies, and marketplaces. Your account manager confirms what your agreement covers.
2
Send us your account details
Account locator, region, cloud provider.
3
We activate
We add your account as a consumer on the share and confirm by email.
4
Create the database and query
One statement on your side.
Run once, as ACCOUNTADMINCopy
CREATE DATABASE smartscoutus FROM SHARE <provider_account>.<share_name>;
GRANT IMPORTED PRIVILEGES ON DATABASE smartscoutus TO ROLE analyst;
Replace <provider_account> and <share_name> with the values from your SmartScout rep. Each marketplace is its own database, named smartscout plus the marketplace code, such as smartscoutus or smartscoutuk. Run this once for each marketplace in your agreement.
Troubleshooting
Share not visible
Almost always a region or cloud mismatch, or the account locator was sent in the wrong format.
Database does not exist or not authorized
The querying role lacks imported privileges.
Do I need my own Snowflake account?
For Snowflake delivery, yes. SmartScout does not provision reader accounts. The share is consumed in your own Snowflake account.
04

What's in the data

Thirty tables in six domains. Select a card to open that table in the field reference.

Join map Six domains and the keys that connect them
ProductIdProductId ·BrowseNodeIdBrandId · BrowseNodeIdSellerIdAmazonBrandIdBrandIdProductIdPromotionsOffer windows, per ASINLightningDealsPrimeExclusiveOffersCouponsCouponTypesCatalog and productsThe hub. Everything joins through Products.IdProductsProductHistoriesSubcategoriesSubcategoryDescendantsSubcategoryBrandsProducts.BrowseNodeId → Subcategories.BrowseNodeIdSearch and advertisingTerm-level organic rank and paid placementSearchTerms · SearchTermHistoriesSearchTermIntentsSearchTermProductOrganics (+ history)SearchTermProductPaids (+ history)SearchTermBrandPaids (+ history)SearchTermSubcategoryOrganicsSellers and ownershipWho sells what, and how muchSellersSellerProductsBrands and market shareBrand performance and who controls itBrandsBrandHistoriesBrandPaidAdHistoriesBrandCoveragesBrandCoverageHistoriesTikTok ShopShops, products, variants, and categoriesTikTokShopsTikTokProductsTikTokVariantsTikTokCategoriesJoined by ShopId, ProductId, and CategoryIdEach line is a shared key. Select a table to open it in the field reference.

Catalog and products

What is listed, at what price, selling how much, from how many sellers.
Products
Current state of every tracked ASIN, with price, rank, sellers, and revenue estimate.
One row per ASIN, current state46 fields
ProductHistories
The workhorse for any trendline.
One row per ASIN per period10 fields
Subcategories
The category hierarchy. PathById rolls results up without hardcoding.
One row per browse node6 fields
SubcategoryDescendants
Every node beneath a category, for rollups at any depth.
One row per node per descendant2 fields
SubcategoryBrands
Brand performance and market share inside each subcategory.
One row per subcategory per brand21 fields

Sellers and ownership

Who is selling this, are they authorized, how much of the buy box do they hold.
Sellers
Business name, registered address, suspension flags.
One row per Amazon seller account17 fields
SellerProducts
The table behind unauthorized seller and buy box share work.
One row per seller per ASIN6 fields

Brands and market share

How is a brand performing, who controls its revenue, is it growing.
Brands
Aggregate performance, growth, and ad metrics per brand.
One row per brand39 fields
BrandHistories
Brand score and traffic over time.
One row per brand per period4 fields
BrandPaidAdHistories
Brand paid placement win rates over time.
One row per brand per period7 fields
BrandCoverages
Current revenue each seller generates on each brand.
One row per seller per brand, current3 fields
BrandCoverageHistories
Watch a distributor's share of a brand erode or grow.
One row per seller per brand per period4 fields

Search and advertising

What shoppers search, who ranks organically, who buys the placement.
SearchTerms
Estimated volume, estimated CPC, intent, and top clicked ASINs.
One row per search term22 fields
SearchTermHistories
Seasonality work lives here.
One row per search term per period4 fields
SearchTermIntents
The intent lookup.
One row per intent value2 fields
SearchTermProductOrganics
Organic rank per term per ASIN.
One row per term per ASIN, current4 fields
SearchTermProductOrganicHistories
Organic rank over time.
One row per term per ASIN per period4 fields
SearchTermProductPaids
Paid placement win rates and estimated spend, per ASIN.
One row per term per ASIN12 fields
SearchTermProductPaidHistories
Paid placement per ASIN over time.
One row per term per ASIN per period12 fields
SearchTermBrandPaids
The same paid metrics rolled up to brand.
One row per term per brand11 fields
SearchTermBrandPaidHistories
Brand paid placement per term over time.
One row per term per brand per period8 fields
SearchTermSubcategoryOrganics
How each subcategory shows up organically on each term.
One row per term per subcategory5 fields

Promotions

Who is discounting, when, and how deep.
LightningDeals
Promotional price alongside the buy box price.
One row per ASIN per offer window5 fields
PrimeExclusiveOffers
Promotional price alongside the buy box price.
One row per ASIN per offer window5 fields
Coupons
Coupon windows and discounts per ASIN.
One row per ASIN per coupon window5 fields
CouponTypes
The coupon type lookup.
One row per coupon type2 fields

TikTok Shop

Shops, products, variants, and categories selling on TikTok Shop US.
TikTokShops
Revenue, units, and growth per shop.
One row per shop16 fields
TikTokProducts
Products listed by each shop.
One row per product13 fields
TikTokVariants
Price, stock, and revenue per variant.
One row per variant13 fields
TikTokCategories
The TikTok Shop category tree, with revenue and counts.
One row per category15 fields

Search term and advertising tables cover US, UK, and Germany. TikTok Shop tables cover US. Everything else covers all 8 marketplaces.

05

Field reference

Every field in the share, with its table context. 323 fields across 30 tables in six domains. Select a table to expand it.

Products46 fieldsCatalog and productsGrainOne row per ASIN, current stateKeyIdMarketplacesAll 8
Joins toSubcategoriesBrandsProductHistoriesSellerProductsSearchTermProductOrganicsSearchTermProductOrganicHistoriesSearchTermProductPaidsSearchTermProductPaidHistoriesLightningDealsPrimeExclusiveOffersCoupons
IdPK
Internal SmartScout product identifier
Asin
Amazon's product identifier
Title
Product title
CategoryBrowseNodeId
Top level category node ID
BrowseNodeId→ Subcategories
Subcategory node ID
ReviewRating
Average review rating
ReviewCount
Number of reviews
DateLaunched
Product launch date
ParentAsin
Parent ASIN when this row is a child variation
Color
Variation color
Size
Variation size
ListedSince
Date the listing first appeared
TotalRatings
Total ratings for this ASIN, variant specific
BrandId→ Brands
Brand this ASIN belongs to
UPC
Universal Product Code
PartNumber
Part number as listed on Amazon
NumberOfSellers
Count of sellers, FBA and FBM combined
NumberFbaSellers
Count of FBA sellers only
AmazonISR
Amazon's in stock rate as a seller over the trailing 90 days
Rank
Best Sellers Rank
SubcategoryRank
Rank within its subcategory
BuyBoxPrice
Most recently tracked buy box price
AverageBuyBoxPrice
Average buy box price over the trailing 30 days
MonthlyRevenueEstimate
Estimated revenue, trailing 30 days
MonthlyUnitsSold
Estimated units sold, trailing 30 days
OutOfStockNow
Whether the product is currently out of stock
ProductPageScore
SmartScout listing quality score, 0 to 10, based on title, features, images, and description
IsVariation
Whether this row is a variation of a parent ASIN
ImageURL
Path segment for the product image
BuyBoxEquity
Buy box share split evenly across all sellers on the listing
RevenueEquity
Estimated revenue split evenly across all sellers on the listing
MarginEquity
Estimated margin contribution split evenly across sellers on the listing
Model
Model number as listed, where available
Manufacturer
Manufacturer as listed, where available
NumberOfItems
Count of individual items in the listed unit
PackageQuantity
Quantity per package as listed
Length / Height / Width / Weight
Dimensions and weight as listed, where available
DateSeen
Most recent date the product was tracked
TTMRevenue
Trailing 12 months estimated revenue
TTMUnitsSold
Trailing 12 months estimated units sold
MoMGrowth
Month over month growth
MoMGrowth12
Year over year growth
HasAPlus
Whether the listing has A+ content
ProductHistories10 fieldsCatalog and productsGrainOne row per ASIN per periodKeyProductId + DateMarketplacesAll 8
Joins toProducts
Id
Row identifier
ProductIdPK→ Products
The ASIN this period belongs to
DatePK
Period start date
BuyBoxPrice
Buy box price for the period
EstimatedUnitSales
Estimated units sold in the period
SalesRank
Amazon sales rank
SubcategoryRank
Rank within its subcategory for the period
Rating
Review rating
Reviews
Review count
NumberOfSellers
Count of sellers on the listing during the period
Subcategories6 fieldsCatalog and productsGrainOne row per browse nodeKeyBrowseNodeIdMarketplacesAll 8
Joins toProductsSubcategoryDescendantsSubcategoryBrandsBrandsSearchTermSubcategoryOrganics
BrowseNodeIdPK
Subcategory node ID
Name
Subcategory name
Context
Fuller descriptive label, useful when names repeat across categories
PathById
Hierarchical list of browse node IDs from this node up to the top level
ParentBrowseNodeId
Immediate parent node
MonthlyRevenue
Estimated monthly revenue across the subcategory
SubcategoryDescendants2 fieldsCatalog and productsGrainOne row per node per descendantKeyBrowseNodeId + DescendantBrowseNodeIdMarketplacesAll 8
Joins toSubcategories
BrowseNodeIdPK→ Subcategories
The parent node
DescendantBrowseNodeIdPK
A node anywhere beneath it
SubcategoryBrands21 fieldsCatalog and productsGrainOne row per subcategory per brandKeyBrowseNodeId + BrandIdMarketplacesAll 8
Joins toSubcategoriesBrands
Ad spend fields are blank outside US, UK, and Germany.
BrowseNodeIdPK→ Subcategories
The subcategory
BrandIdPK→ Brands
The brand
ProductCount
The brand's product count in the subcategory
Revenue
The brand's estimated revenue in the subcategory
UnitsSold
The brand's estimated units sold in the subcategory
ReviewCount
The brand's review count in the subcategory
AvgRating
Average rating
AvgPrice
Average price
AvgSellerCount
Average sellers per product
AvgPageScore
Average product page score
AvgVolume
Average volume per product
AvgReviews
Average reviews per product
MarketShare
The brand's share of the subcategory
MoMMarketShareChange
Month over month change in market share
MoMUnitsSoldChange
Month over month change in units sold
MoMRevenueChange
Month over month change in revenue
SponsoredBrandSpend / TopSpotSpend / TopGroupSpend / SponsoredVideoSpend
Estimated ad spend by placement type
AdSpendShare
The brand's share of ad spend in the subcategory
Sellers17 fieldsSellers and ownershipGrainOne row per Amazon seller accountKeyIdMarketplacesAll 8
Joins toSellerProductsBrandsBrandCoveragesBrandCoverageHistories
IdPK
Internal seller identifier
AmazonSellerId
Amazon's seller identifier
Name
Storefront display name
Street / City / State / Country / ZipCode
Registered business address
NumberAsins
Count of ASINs SmartScout tracks for this seller
EstimateSales
Estimated sales across the seller's tracked catalog
SellerTypeId
Seller type classification
BusinessName
Registered legal business name
LifetimeRatingsCount
Lifetime feedback rating count
ThirtyDayRatingsCount
Feedback ratings in the trailing 30 days
Suspended
Whether the account is currently suspended
LastSuspendedDate
Most recent suspension date
StartedSellingDate
Date the seller account first activated on Amazon
SellerProducts6 fieldsSellers and ownershipGrainOne row per seller per ASINKeySellerId + ProductIdMarketplacesAll 8
Joins toSellersProducts
SellerIdPK→ Sellers
The seller on this listing
ProductIdPK→ Products
The ASIN being sold
MonthlyRevenue
Estimated monthly revenue for this seller on this ASIN
BuyBoxPercentage
Share of the buy box held by this seller on this ASIN
IsFba
Whether this seller fulfills the ASIN through FBA
Price
This seller's price for the ASIN
Brands39 fieldsBrands and market shareGrainOne row per brandKeyIdMarketplacesAll 8
Joins toSubcategoriesSellersProductsSubcategoryBrandsBrandHistoriesBrandPaidAdHistoriesBrandCoveragesBrandCoverageHistoriesSearchTermBrandPaidsSearchTermBrandPaidHistoriesTikTokShops
Ad spend and win rate fields are blank outside US, UK, and Germany.
IdPK
Brand identifier
Name
Brand name
CategoryBrowseNodeId
Primary category node
BrowseNodeId→ Subcategories
Primary subcategory node
MonthGrowth
Month over month revenue growth
MonthGrowth12
Year over year revenue growth
Trailing12Months
Trailing 12 month estimated revenue
TTMUnits
Trailing 12 month estimated units
AmazonISR
Amazon's in stock rate across the brand's catalog
AvgFBASellers
Average FBA sellers per ASIN
AvgSellers
Average total sellers per ASIN
AvgPrice
Average price across the brand's catalog
AvgVolume
Average units sold per ASIN
ReviewRating
Average review rating across the catalog
TotalProducts
Count of tracked ASINs
TotalReviews
Total reviews across the catalog
MonthlyRevenue
Estimated trailing 30 day revenue
MonthlyUnitsSold
Estimated trailing 30 day units
BrandScore
SmartScout brand strength score
HasStorefront
Whether the brand operates an Amazon storefront
HasSingleSeller
Whether one seller controls the brand's listings
DominantSellerId→ Sellers
Largest seller by revenue
DominantSellerBrandCoverage
That seller's revenue share of the brand
Domain
The brand's website domain
TotalEstFBAFees30Day
Estimated FBA fees across the catalog, trailing 30 days
AvgInStockRate90Day
Average in stock rate, trailing 90 days
AvgPctBBSuppressed45Day
Average share of time the buy box was suppressed, trailing 45 days
AzRevenuePct
Share of the brand's revenue sold by Amazon
SearchTerms
Count of search terms the brand appears on
Products
Count of the brand's products in paid placements
SponsoredBrandWinRate / TopSpotWinRate / TopGroupWinRate / SponsoredVideoWinRate
Paid placement win rates by placement type
SponsoredBrandSpend / TopSpotSpend / TopGroupSpend / SponsoredVideoSpend
Estimated ad spend by placement type
TotalAdSpend
Estimated total ad spend across placement types
BrandHistories4 fieldsBrands and market shareGrainOne row per brand per periodKeyBrandId + HistoryDateMarketplacesAll 8
Joins toBrands
BrandIdPK→ Brands
The brand
HistoryDatePK
Period date
BrandScore
Brand score for the period
Traffic
Traffic for the period
BrandPaidAdHistories7 fieldsBrands and market shareGrainOne row per brand per periodKeyBrandId + HistoryDateMarketplacesUS, UK, Germany
Joins toBrands
BrandIdPK→ Brands
The brand
HistoryDatePK
Period date
Products
Count of the brand's products in paid placements
SponsoredBrandWinRate / TopSpotWinRate / TopGroupWinRate / SponsoredVideoWinRate
Paid placement win rates by placement type for the period
BrandCoverages3 fieldsBrands and market shareGrainOne row per seller per brand, currentKeySellerId + BrandIdMarketplacesAll 8
Joins toSellersBrands
SellerIdPK→ Sellers
The seller
BrandIdPK→ Brands
The brand
MonthlyRevenue
Estimated monthly revenue this seller generates on this brand
BrandCoverageHistories4 fieldsBrands and market shareGrainOne row per seller per brand per periodKeySellerId + BrandId + HistoryDateMarketplacesAll 8
Joins toBrandsSellers
BrandIdPK→ Brands
The brand
SellerIdPK→ Sellers
The seller
HistoryDatePK
Period date
CoveragePercentage
This seller's share of the brand's revenue
SearchTerms22 fieldsSearch and advertisingGrainOne row per search termKeyIdMarketplacesUS, UK, Germany
Joins toSearchTermIntentsSearchTermHistoriesSearchTermProductOrganicsSearchTermProductOrganicHistoriesSearchTermProductPaidsSearchTermProductPaidHistoriesSearchTermBrandPaidsSearchTermBrandPaidHistoriesSearchTermSubcategoryOrganics
IdPK
Search term identifier
SearchTermValue
The search phrase
EstimateSearches
Estimated search volume
Brands
Count of distinct brands appearing in results for this term
Products
Count of distinct ASINs appearing in results for this term
SponsoredProducts
Count of sponsored products on this term
SuperCharge
Supercharge indicator for this term
SearchTermIntentId→ SearchTermIntents
Intent classification for this term
EstimatedCPC
Estimated cost per click
ClickedAsin1 / ClickedAsin2 / ClickedAsin3
The top three clicked ASINs for this term
ConversionShare1 / ConversionShare2 / ConversionShare3
Conversion share for each of those three ASINs
SeenInTheLast30Days
Whether the term appeared in the trailing 30 days
KeywordSales
Estimated sales tied to this term
LaunchVelocity
SmartScout launch velocity score
OpportunityScore
SmartScout opportunity score
Current4WkVolume
Search volume over the most recent 4 weeks
PriorYear4WkVolume
Search volume for the same 4 weeks a year earlier
YoYChangeRate
Year over year change in search volume
SearchTermHistories4 fieldsSearch and advertisingGrainOne row per search term per periodKeySearchTermId + HistoryDateMarketplacesUS, UK, Germany
Joins toSearchTerms
SearchTermIdPK→ SearchTerms
The search term
HistoryDatePK
Period date
EstimateSearches
Estimated search volume for the period
EstimatedCPC
Estimated cost per click for the period
SearchTermIntents2 fieldsSearch and advertisingGrainOne row per intent valueKeyIdMarketplacesUS, UK, Germany
Joins toSearchTerms
IdPK
Intent identifier
Intent
Intent classification
SearchTermProductOrganics4 fieldsSearch and advertisingGrainOne row per term per ASIN, currentKeySearchTermId + ProductIdMarketplacesUS, UK, Germany
Joins toSearchTermsProducts
SearchTermIdPK→ SearchTerms
The search term
ProductIdPK→ Products
The ranked ASIN
AvgRank
Average organic position for this ASIN on this term
LatestRank
Most recent observed organic position
SearchTermProductOrganicHistories4 fieldsSearch and advertisingGrainOne row per term per ASIN per periodKeySearchTermId + ProductId + HistoryDateMarketplacesUS, UK, Germany
Joins toSearchTermsProducts
SearchTermIdPK→ SearchTerms
The search term
ProductIdPK→ Products
The ranked ASIN
HistoryDatePK
Period date
AvgRank
Average organic position for this ASIN on this term in the period
SearchTermProductPaids12 fieldsSearch and advertisingGrainOne row per term per ASINKeySearchTermId + ProductIdMarketplacesUS, UK, Germany
Joins toSearchTermsProducts
SearchTermIdPK→ SearchTerms
The search term
ProductIdPK→ Products
The advertised ASIN
AvgRank
Average paid position
SponsoredBrandWinRate
Share of observations where the advertiser held sponsored brand placement
TopSpotWinRate
Share of observations holding the top sponsored slot
TopGroupWinRate
Share of observations holding a placement in the top sponsored group
SponsoredVideoWinRate
Share of observations holding sponsored video placement
SponsoredBrandSpend / TopSpotSpend / TopGroupSpend / SponsoredVideoSpend
Estimated ad spend by placement type
EstimatedClicks
Estimated clicks
SearchTermProductPaidHistories12 fieldsSearch and advertisingGrainOne row per term per ASIN per periodKeySearchTermId + ProductId + HistoryDateMarketplacesUS, UK, Germany
Joins toSearchTermsProducts
SearchTermIdPK→ SearchTerms
The search term
ProductIdPK→ Products
The advertised ASIN
HistoryDatePK
Period date
AvgRank
Average paid position in the period
SponsoredBrandWinRate / TopSpotWinRate / TopGroupWinRate / SponsoredVideoWinRate
Paid placement win rates by placement type for the period
SponsoredBrandSpend / TopSpotSpend / TopGroupSpend / SponsoredVideoSpend
Estimated ad spend by placement type for the period
SearchTermBrandPaids11 fieldsSearch and advertisingGrainOne row per term per brandKeySearchTermId + BrandIdMarketplacesUS, UK, Germany
Joins toSearchTermsBrands
SearchTermIdPK→ SearchTerms
The search term
BrandIdPK→ Brands
The advertising brand
Products
Count of sponsored products for the brand on this term
SponsoredBrandWinRate / TopSpotWinRate / TopGroupWinRate / SponsoredVideoWinRate
Paid placement win rates by placement type
SponsoredBrandSpend / TopSpotSpend / TopGroupSpend / SponsoredVideoSpend
Estimated ad spend by placement type
SearchTermBrandPaidHistories8 fieldsSearch and advertisingGrainOne row per term per brand per periodKeySearchTermId + BrandId + HistoryDateMarketplacesUS, UK, Germany
Joins toSearchTermsBrands
SearchTermIdPK→ SearchTerms
The search term
BrandIdPK→ Brands
The advertising brand
HistoryDatePK
Period date
Products
Count of sponsored products for the brand on this term in the period
SponsoredBrandWinRate / TopSpotWinRate / TopGroupWinRate / SponsoredVideoWinRate
Paid placement win rates by placement type for the period
SearchTermSubcategoryOrganics5 fieldsSearch and advertisingGrainOne row per term per subcategoryKeySearchTermId + BrowseNodeIdMarketplacesUS, UK, Germany
Joins toSearchTermsSubcategories
SearchTermIdPK→ SearchTerms
The search term
BrowseNodeIdPK→ Subcategories
The subcategory
Products
Count of products from this subcategory ranking on the term
OrganicReach
Organic reach of this subcategory on the term
Relevancy
Relevancy of the term to this subcategory
LightningDeals5 fieldsPromotionsGrainOne row per ASIN per offer windowKeyProductId + StartDateMarketplacesAll 8
Joins toProducts
ProductIdPK→ Products
The discounted ASIN
StartDate / EndDate
Offer window
Price
Promotional price
BuyBoxPrice
Buy box price during the window, for comparison
PrimeExclusiveOffers5 fieldsPromotionsGrainOne row per ASIN per offer windowKeyProductId + StartDateMarketplacesAll 8
Joins toProducts
ProductIdPK→ Products
The discounted ASIN
StartDate / EndDate
Offer window
Price
Promotional price
BuyBoxPrice
Buy box price during the window, for comparison
Coupons5 fieldsPromotionsGrainOne row per ASIN per coupon windowKeyProductId + StartDateMarketplacesAll 8
Joins toProductsCouponTypes
ProductIdPK→ Products
The ASIN with the coupon
CouponTypeId→ CouponTypes
Coupon type
StartDate / EndDate
Coupon window
Discount
Coupon discount
CouponTypes2 fieldsPromotionsGrainOne row per coupon typeKeyIdMarketplacesAll 8
Joins toCoupons
IdPK
Coupon type identifier
Name
Coupon type name
TikTokShops16 fieldsTikTok ShopGrainOne row per shopKeyIdMarketplacesTikTok Shop US
Joins toTikTokCategoriesBrandsTikTokProducts
IdPK
Shop identifier
Name
Shop name
StoreURL
Link to the shop
Country
Shop country
CategoryId / SubcategoryId→ TikTokCategories
The shop's category and subcategory
AmazonBrandId→ Brands
The matching Amazon brand, where linked
MonthlyRevenue / LifetimeRevenue
Estimated revenue, monthly and lifetime
MonthlyUnitSales / LifetimeUnitSales
Estimated units sold, monthly and lifetime
OneMonthGrowth
Month over month growth
ProductCount
Count of products in the shop
SubcategoryShare
The shop's share of its subcategory
AvgRating
Average product rating
AvgPrice
Average product price
TikTokProducts13 fieldsTikTok ShopGrainOne row per productKeyIdMarketplacesTikTok Shop US
Joins toTikTokShopsTikTokCategoriesTikTokVariants
IdPK
Product identifier
ShopId→ TikTokShops
The shop selling it
ProductName
Product name
ProductDescription
Product description
ImageURL
Product image
CategoryId / SubcategoryId→ TikTokCategories
The product's category and subcategory
ReviewCount
Number of reviews
AvgRating
Average rating
VariantCount
Count of variants
MonthlyUnitSales / LifetimeUnitSales
Estimated units sold, monthly and lifetime
MonthlyRevenue
Estimated monthly revenue
TikTokVariants13 fieldsTikTok ShopGrainOne row per variantKeyIdMarketplacesTikTok Shop US
Joins toTikTokProducts
IdPK
Variant identifier
ProductId→ TikTokProducts
The parent product
Variant
Variant name
ImageURL
Variant image
Price
Current price
AvgPrice
Average price
Shipping
Shipping cost
FreeShipping
Whether shipping is free
DiscountPromo
Active discount or promotion
QuantityAvailable
Units available
MonthlyRevenue / LifetimeRevenue
Estimated revenue, monthly and lifetime
MonthlyUnitSales
Estimated monthly units sold
TikTokCategories15 fieldsTikTok ShopGrainOne row per categoryKeyIdMarketplacesTikTok Shop US
Joins toTikTokShopsTikTokProducts
IdPK
Category identifier
ParentId
Parent category
Level
Depth in the category tree
Name
Category name
IsLeafNode
Whether the category has no children
MonthlyRevenue / TwelveMonthRevenue / LifetimeRevenue
Estimated revenue, monthly, trailing 12 months, and lifetime
MonthlyUnitSales / TwelveMonthUnitSales
Estimated units sold, monthly and trailing 12 months
OneMonthGrowth
Month over month growth
ShopCount
Count of shops in the category
ProductCount
Count of products in the category
AvgRating
Average product rating
AvgPrice
Average product price
06

What clients build with it

Organized by who is reading.

Brands and manufacturers
  • Market share and category tracking against a defined competitive set
  • Unauthorized seller and MAP monitoring using SellerProducts and BrandCoverages
  • Product launch tracking from DateLaunched and early ProductHistories
  • Assortment and white-space analysis by subcategory
  • Share-of-voice on priority search terms, organic and paid
Agencies and retail media
  • Client reporting at scale, one query pattern across every brand you manage
  • Competitive ad spend and placement win rates by term
  • New business pitches built on real category data
  • Category benchmarks clients cannot get from their own Seller Central
Platforms and data teams
  • Embed marketplace intelligence into your own product — this requires a white-label license, so speak with your SmartScout rep to learn more
  • Enrich internal records with revenue estimates, fulfillment mix, and seller type
  • Build the dashboards and models your customers ask for without maintaining scrapers
Investors and corp dev
  • Diligence on brands and sellers using Sellers, Brands, and the history tables
  • Portfolio monitoring across holdings
  • Category growth analysis from MonthGrowth and MonthGrowth12
Sales and revenue teams
  • Enrich CRM records with brand, seller, and category data so reps open with something the prospect does not already know
  • Set market-shift triggers for outbound — a competitor's revenue climbing, a brand losing buy box share, a new seller appearing on a listing
  • Generate account-level reports automatically and drop them into outbound sequences
  • Prioritize territories and target lists by category growth rather than by guesswork
  • Build competitive displacement lists from seller and brand coverage data
07

Query library

Twelve queries, each with a note on what to change for your own use. Every snippet uses the US database, smartscoutus. Swap the marketplace code to query another marketplace, such as smartscoutuk. Queries 08, 09, and 11 use search term tables, which cover US, UK, and Germany.

01

Full price and revenue history for one ASIN

Change the ASIN in the where clause.
Copy
select h.Date, h.BuyBoxPrice, h.EstimatedUnitSales,
  h.BuyBoxPrice * h.EstimatedUnitSales as EstimatedRevenue, h.SalesRank
from smartscoutus.public.ProductHistories h
join smartscoutus.public.Products p on p.Id = h.ProductId
where p.Asin = 'B00XXXXXXX'
order by h.Date;
02

Weekly performance for one brand

Change the brand name. Revenue here is computed from price times units, not MonthlyRevenueEstimate.
Copy
select h.Date,
  sum(h.EstimatedUnitSales) as Units,
  sum(h.EstimatedUnitSales * h.BuyBoxPrice) as Revenue,
  count(distinct p.Id) as Asins
from smartscoutus.public.ProductHistories h
join smartscoutus.public.Products p on p.Id = h.ProductId
join smartscoutus.public.Brands b on b.Id = p.BrandId
where b.Name = 'Your brand'
group by h.Date
order by h.Date;
03

Weekly brand performance split by subcategory, date-bounded

Change the brand and the two dates. Bounding the date range first is what keeps this query cheap.
Copy
select s.Name as Subcategory, h.Date,
  sum(h.EstimatedUnitSales) as Units,
  sum(h.EstimatedUnitSales * h.BuyBoxPrice) as Revenue
from smartscoutus.public.ProductHistories h
join smartscoutus.public.Products p on p.Id = h.ProductId
join smartscoutus.public.Brands b on b.Id = p.BrandId
join smartscoutus.public.Subcategories s on s.BrowseNodeId = p.BrowseNodeId
where b.Name = 'Your brand'
  and h.Date between '2025-01-01' and '2025-06-30'
group by s.Name, h.Date
order by s.Name, h.Date;
04

Top 50 brands in a subcategory by trailing revenue

Change the subcategory name. Swap Trailing12Months for MonthlyRevenue to rank on the trailing 30 days instead.
Copy
select b.Name, b.MonthlyRevenue, b.Trailing12Months, b.MonthGrowth, b.TotalProducts
from smartscoutus.public.Brands b
join smartscoutus.public.Subcategories s on s.BrowseNodeId = b.BrowseNodeId
where s.Name = 'Your subcategory'
order by b.Trailing12Months desc
limit 50;
05

Every seller on a brand, with revenue contribution

Change the brand name. ShareOfBrandRevenue divides each seller's revenue by the brand total. This is the starting point for unauthorized seller work.
Copy
select se.Name as Seller, se.BusinessName, se.Country,
  bc.MonthlyRevenue,
  bc.MonthlyRevenue / sum(bc.MonthlyRevenue) over () as ShareOfBrandRevenue
from smartscoutus.public.BrandCoverages bc
join smartscoutus.public.Sellers se on se.Id = bc.SellerId
join smartscoutus.public.Brands b on b.Id = bc.BrandId
where b.Name = 'Your brand'
order by bc.MonthlyRevenue desc;
06

Brand revenue share within a subcategory over time

Change the subcategory. The window function gives each brand its share of the period total.
Copy
with periods as (
  select b.Name as Brand, h.Date,
    sum(h.EstimatedUnitSales * h.BuyBoxPrice) as Revenue
  from smartscoutus.public.ProductHistories h
  join smartscoutus.public.Products p on p.Id = h.ProductId
  join smartscoutus.public.Brands b on b.Id = p.BrandId
  join smartscoutus.public.Subcategories s on s.BrowseNodeId = p.BrowseNodeId
  where s.Name = 'Your subcategory'
  group by b.Name, h.Date
)
select Brand, Date, Revenue,
  Revenue / sum(Revenue) over (partition by Date) as ShareOfPeriod
from periods
order by Date, ShareOfPeriod desc;
07

New ASIN launches in a subcategory within a date window

Change the subcategory and the two dates. DateLaunched is the listing launch, ListedSince is when the listing first appeared to us.
Copy
select p.Asin, p.Title, b.Name as Brand, p.DateLaunched,
  p.MonthlyRevenueEstimate, p.ReviewCount
from smartscoutus.public.Products p
join smartscoutus.public.Subcategories s on s.BrowseNodeId = p.BrowseNodeId
left join smartscoutus.public.Brands b on b.Id = p.BrandId
where s.Name = 'Your subcategory'
  and p.DateLaunched between '2025-01-01' and '2025-06-30'
order by p.MonthlyRevenueEstimate desc;
08

Organic rank movement for a set of ASINs across a set of search terms

Change the two lists. Positive Change means the rank number rose, which is worse.
Copy
select st.SearchTermValue, p.Asin, o.HistoryDate, o.AvgRank,
  o.AvgRank - lag(o.AvgRank) over (
    partition by st.Id, p.Id order by o.HistoryDate
  ) as Change
from smartscoutus.public.SearchTermProductOrganicHistories o
join smartscoutus.public.SearchTerms st on st.Id = o.SearchTermId
join smartscoutus.public.Products p on p.Id = o.ProductId
where p.Asin in ('B00XXXXXXX', 'B00YYYYYYY')
  and st.SearchTermValue in ('term one', 'term two')
order by st.SearchTermValue, p.Asin, o.HistoryDate;
09

Estimated competitor ad spend by search term for a brand

Change the brand name.
Copy
select st.SearchTermValue, st.EstimateSearches, st.EstimatedCPC, bp.TopSpotWinRate,
  bp.SponsoredBrandSpend + bp.TopSpotSpend + bp.TopGroupSpend + bp.SponsoredVideoSpend as EstimatedSpend
from smartscoutus.public.SearchTermBrandPaids bp
join smartscoutus.public.SearchTerms st on st.Id = bp.SearchTermId
join smartscoutus.public.Brands b on b.Id = bp.BrandId
where b.Name = 'Competitor brand'
order by EstimatedSpend desc
limit 100;
10

Out-of-stock and price-change detection, period over period

Change the brand. Adjust the 0.05 threshold to set how large a price move you want flagged.
Copy
with moves as (
  select p.Asin, h.Date, h.BuyBoxPrice,
    lag(h.BuyBoxPrice) over (partition by p.Id order by h.Date) as PriorPrice,
    p.OutOfStockNow
  from smartscoutus.public.ProductHistories h
  join smartscoutus.public.Products p on p.Id = h.ProductId
  join smartscoutus.public.Brands b on b.Id = p.BrandId
  where b.Name = 'Your brand'
)
select *, (BuyBoxPrice - PriorPrice) / nullif(PriorPrice, 0) as PctChange
from moves
where abs((BuyBoxPrice - PriorPrice) / nullif(PriorPrice, 0)) > 0.05
  or OutOfStockNow = true
order by Date desc;
11

Search volume seasonality for a term set

Change the term list. Compare each period against the term’s own average to see the seasonal shape.
Copy
select st.SearchTermValue, sh.HistoryDate, sh.EstimateSearches,
  sh.EstimateSearches / avg(sh.EstimateSearches) over (partition by st.Id) as IndexToAverage
from smartscoutus.public.SearchTermHistories sh
join smartscoutus.public.SearchTerms st on st.Id = sh.SearchTermId
where st.SearchTermValue in ('term one', 'term two', 'term three')
order by st.SearchTermValue, sh.HistoryDate;
12

Join reference — the whole graph in one query

Nothing to change. Read it once and the rest of the schema follows.
Copy
/* Products is the hub. Every fact table carries ProductId,
   BrandId, SellerId, or SearchTermId back to it. */
select p.Asin,
  b.Name as Brand,          /* Products.BrandId to Brands.Id */
  s.Name as Subcategory,    /* Products.BrowseNodeId to Subcategories.BrowseNodeId */
  se.Name as Seller,        /* SellerProducts.SellerId to Sellers.Id */
  sp.BuyBoxPercentage, h.Date, h.EstimatedUnitSales
from smartscoutus.public.Products p
join smartscoutus.public.Brands b on b.Id = p.BrandId
join smartscoutus.public.Subcategories s on s.BrowseNodeId = p.BrowseNodeId
left join smartscoutus.public.SellerProducts sp on sp.ProductId = p.Id
left join smartscoutus.public.Sellers se on se.Id = sp.SellerId
left join smartscoutus.public.ProductHistories h on h.ProductId = p.Id
where p.Asin = 'B00XXXXXXX'
order by h.Date;
08

Connecting your tools

Platforms
Works with Snowflake, Google BigQuery, Databricks, and Amazon S3. Pick the platform your team already runs and we deliver the same tables there.

Because the data lands in your warehouse, anything that already reads Snowflake, BigQuery, or Databricks reads this. No connector to install and no separate credential to manage.

Power BI Looker Tableau Sigma Hex dbt Salesforce CDP and reverse ETL

The common pattern is worth naming: most clients build a thin dbt layer over the share, materialize the two or three aggregates their business asks about weekly, and point BI at that instead of at raw history.

Which SmartScout product fits

Data LakeAPIMCP server
Best forWarehouse-native analytics, large historical scans, embedding into your own productApplication integration, targeted lookups, real-time-ish workflowsAd hoc analysis in Claude and other AI tools, no code required
DeliverySnowflake, BigQuery, Databricks, or Amazon S3REST endpointsMCP connector
Typical userData engineer, analytics leadApplication developerAnalyst, account team, executive

They compose. Teams commonly query the Data Lake for scale, hit the API in-product, and use the MCP server for questions nobody wants to write SQL for.

09

FAQ

How fresh is the data?
Core tables refresh twice weekly on Snowflake. Other platforms refresh at least weekly.
Do we need to ingest anything?
Not on Snowflake, BigQuery, or Databricks. The data arrives as a share and nothing gets copied into your storage.
Can we choose specific marketplaces?
Yes, scope is custom.
Do we get web app access?
Yes. Data Lake clients receive SmartScout Portal seats.
How much history is included?
It depends on the table. Most historical tables carry 6–24 months.
Are there query limits?
None from us. You query on your own compute.
What if we do not use Snowflake?
We also deliver to Google BigQuery, Databricks, and Amazon S3. For anything else, your SmartScout rep can scope a path that works for your stack.
Do you offer implementation help for the first queries?
Yes. If you need it, your SmartScout rep can help you map out a path forward.
10

Next step

Existing enterprise clients

Your account manager can adjust scope, add marketplaces, or walk your team through the schema on a working call.

Contact your account manager

Evaluating

Request a scoped sample or a schema walkthrough with the data team.

Contact sales