Skip to content

Amazon Report Tables (Ads, Returns, Pricing, Brand Analytics)

Added 2026-06-24 to unblock Karl's self-serve access. Every report below is pulled by StickyMetrics into Supabase on a schedule; you read it with plain SQL. You do not need any Amazon credentials: the pipeline holds the SP-API and Ads-API tokens and lands the data in raw.* tables, where karl_ro (read) and karl_rw (read/write) already have grants.

All tables are in schema raw, keyed by seller_id (currently ecomhd_us).

Map: your request to the table

You asked for Table Refresh
Campaign names + status raw.ad_campaign_state nightly
Placement report raw.ad_placement_daily nightly
Search term / keyword report raw.ad_search_term_daily nightly
Keyword/target bid performance raw.ad_targeting_daily nightly
Sponsored Brands / Display spend raw.ad_spend_daily (ad_product SPONSORED_BRANDS / SPONSORED_DISPLAY) nightly
FBA Customer Returns raw.fba_customer_returns nightly (last 30d)
Product Pricing raw.product_pricing nightly snapshot
Brand Analytics (Search Query Performance) raw.search_query_performance submit weekly, harvest every 6h

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 (launchd com.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.py flags 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