Amazon Dataset and Report Tables¶
Added 2026-06-24; expanded 2026-09-15. This is the canonical inventory for
the Amazon datasets covered by this reporting surface. LIVE means a collector
is scheduled and landing rows. SCHEMA_READY means storage exists but
collection is not active.
BLOCKED means an external connection or permission is still required. Never
infer collection from the presence of an empty table.
The same status registry is queryable in raw.amazon_dataset_catalog. Report
collectors use raw.amazon_report_queue for durable submit/harvest lifecycle
state; API and notification collectors write directly to their destination.
Per-account access evidence is stored in raw.amazon_dataset_capabilities; a
global SCHEMA_READY row does not imply every connected seller is eligible.
All tables are in schema raw, keyed by seller_id (for example,
ecomhd_us and partyprints_us).
Map: your request to the table¶
| Dataset | Table | Intended cadence | Status |
|---|---|---|---|
| Campaign names + status | raw.ad_campaign_state |
nightly | LIVE |
| Placement report | raw.ad_placement_daily |
nightly | LIVE |
| Search term / keyword report | raw.ad_search_term_daily |
nightly | LIVE |
| Keyword/target bid performance | raw.ad_targeting_daily |
nightly | LIVE |
| Sponsored Brands / Display spend | raw.ad_spend_daily |
nightly | LIVE |
| FBA Customer Returns | raw.fba_customer_returns |
nightly | LIVE |
| Product Pricing | raw.product_pricing |
nightly | LIVE |
| Brand Analytics: Search Query Performance | raw.search_query_performance |
weekly submit / 6-hour harvest | LIVE |
| Customer Feedback review and return topics | raw.customer_feedback_insights |
weekly rotating coverage | LIVE |
| Brand Analytics: Search Catalog Performance | raw.search_catalog_performance |
monthly | LIVE |
| Brand Analytics: Market Basket | raw.brand_market_basket |
monthly | LIVE |
| Brand Analytics: Repeat Purchase | raw.brand_repeat_purchase |
monthly | LIVE |
| FBA Inventory Planning | raw.fba_inventory_planning |
daily | LIVE |
| FBA Stranded Inventory | raw.fba_stranded_inventory |
daily | LIVE; valid empty reports retained |
| FBA Inbound Noncompliance | raw.fba_inbound_noncompliance |
daily | LIVE; valid empty reports retained |
| Ads Purchased Product attribution | raw.ad_purchased_product_daily |
nightly | LIVE for ecomHD; PartyPrints has a verified Amazon selling connection but no separately registered Ads API profile |
| FBA estimated fee preview | raw.fba_fee_estimates |
daily | LIVE |
| FBA storage and overage fees | raw.fba_storage_fees |
monthly | LIVE; valid empty reports retained |
| FBA reimbursements | raw.fba_reimbursements |
daily | LIVE |
| FBA replacements | raw.fba_replacements |
daily | LIVE |
| Detailed FBA inventory ledger | raw.inventory_ledger_detail |
daily | LIVE |
| FBA inventory availability notifications | raw.inventory_availability_events |
event driven | SCHEMA_READY |
Expansion design¶
- Tables expose stable dimensions and decision-critical metrics as typed columns.
- Every new Amazon surface also retains
source_payload jsonb, preventing data loss when Amazon adds optional fields before Sticky promotes them to columns. - Variable-shape flat files use a deterministic
source_record_key, computed by the collector, for idempotent ingestion. - Creating a table does not change status to
LIVE; activation requires an access probe, a collector, a backfill, a schedule, and a freshness check.
Tables¶
raw.ad_campaign_state: campaign names + status + budget¶
Current-state lookup (one row per campaign), upserted nightly. ad_product is
SPONSORED_PRODUCTS / SPONSORED_BRANDS / SPONSORED_DISPLAY. Columns:
campaign_id, campaign_name, state (ENABLED/PAUSED/ARCHIVED), targeting_type,
budget_amount, budget_type, start_date, end_date, pulled_at.
-- Active SP campaigns and their daily budget
SELECT campaign_name, budget_amount
FROM raw.ad_campaign_state
WHERE seller_id='ecomhd_us' AND ad_product='SPONSORED_PRODUCTS' AND state='ENABLED'
ORDER BY budget_amount DESC;
raw.ad_placement_daily: SP placement performance¶
Per day x campaign x placement. placement is the placementClassification
(Top of Search / Product Pages / Rest of Search). Metrics: impressions, clicks,
spend_usd, attributed_sales_usd, attributed_units.
-- Spend + ACOS by placement, last 7 days
SELECT placement, SUM(spend_usd) spend, SUM(attributed_sales_usd) sales,
ROUND(SUM(spend_usd)/NULLIF(SUM(attributed_sales_usd),0)*100,1) acos
FROM raw.ad_placement_daily
WHERE seller_id='ecomhd_us' AND spend_date >= current_date - 7
GROUP BY placement;
raw.ad_search_term_daily: what customers actually searched¶
The customer's actual query that triggered the ad (the negative-keyword goldmine),
distinct from the bid keyword. Per day x campaign x ad group x keyword x search
term: keyword_text, match_type, search_term, impressions, clicks, spend_usd,
attributed_sales_usd, attributed_units.
-- Wasted spend: clicks, no sales, last 14 days
SELECT search_term, SUM(clicks) clicks, SUM(spend_usd) spend
FROM raw.ad_search_term_daily
WHERE seller_id='ecomhd_us' AND spend_date >= current_date - 14
GROUP BY search_term
HAVING SUM(attributed_sales_usd)=0 AND SUM(spend_usd) > 5
ORDER BY spend DESC;
raw.ad_targeting_daily: bid keyword / target performance¶
Performance at the level you bid on (keyword or product target). Per day x
campaign x ad group x keyword: targeting, match_type, impressions, clicks,
spend_usd, attributed_sales_usd, attributed_units.
raw.ad_spend_daily: SP / SB / SD spend (pre-existing)¶
Sponsored Brands and Sponsored Display live here, filter on ad_product.
SB is campaign-level (no ASIN); SP/SD carry ASIN.
raw.fba_customer_returns: FBA customer returns¶
One row per returned unit, last 30 days refreshed nightly. Columns: return_date,
order_id, sku, asin, fnsku, product_name, quantity, fulfillment_center_id,
detailed_disposition (SELLABLE/CUSTOMER_DAMAGED/DEFECTIVE/...), reason, status,
license_plate_number, customer_comments.
-- Return rate signal: units returned by ASIN, last 30 days
SELECT asin, SUM(quantity) units_returned
FROM raw.fba_customer_returns
WHERE seller_id='ecomhd_us'
GROUP BY asin ORDER BY units_returned DESC LIMIT 20;
raw.product_pricing: pricing snapshot¶
One row per (asin, snapshot_date), nightly. listing_price_usd, shipping_usd,
landed_price_usd (our offer), buybox_price_usd (featured offer),
lowest_price_usd (NULL in v1), currency. Rows only exist for ASINs with a
live offer.
-- Where our price is above the buy box (latest snapshot)
SELECT asin, listing_price_usd, buybox_price_usd
FROM raw.product_pricing
WHERE seller_id='ecomhd_us' AND snapshot_date=(SELECT MAX(snapshot_date) FROM raw.product_pricing)
AND buybox_price_usd IS NOT NULL AND landed_price_usd > buybox_price_usd;
raw.search_query_performance: Brand Analytics SQP¶
Per (asin, search_query, period). Periods are WEEK / MONTH / QUARTER. Holds
impressions/clicks/cart-adds/purchases plus this ASIN's share of each. Cadence is
not daily: the nightly run submits a batch on Mondays and the
com.stickymetrics.sqp_harvest launchd job (every 6h) ingests completed reports.
How it stays fresh¶
- Daily tables run inside
scripts/nightly_refresh.sh(launchdcom.stickymetrics.daily, 6am). Steps 10c2 (ad reports), 10d (campaigns), 10e (pricing), 10f (returns). - SQP submit is step 10g (Mondays); harvest is its own launchd job every 6h.
scripts/freshness_check.pyflags any daily table that has not refreshed in 26h (SQP excluded by design: wrong cadence for a 26h check).
Pulling manually (if you ever need to)¶
These are run by the pipeline; you generally don't. But for a one-off:
python scripts/pull_ad_campaigns.py --seller ecomhd_us
python scripts/pull_ad_reports.py --seller ecomhd_us --report search_term --days 7
python scripts/pull_product_pricing.py --seller ecomhd_us
python scripts/pull_fba_returns.py --seller ecomhd_us --days 30
python scripts/pull_sqp.py --seller ecomhd_us --poll-pending