Autron

List a brand's LIVE Brand Store pages: each page's name and URL. Only published (LIVE) pages are returned. Auto-executes (no approval card).

list_store_pages

List a seller's products in their primary marketplace. Use when the user asks about their products, wants to see product catalog, or needs to find products by ASIN, SKU, or name. Each product includes its current fulfillable inventory (FBA `afn_fulfillable_quantity`, or FBM available quantity), so this tool can drive inventory-aware actions — e.g. finding SKUs low on stock so their goals can be paused before they go out of stock. Products also carry Amazon's ad eligibility, which answers "can this ASIN advertise right now?" — use it when ads aren't serving or before spending on a product. Note it is a POLICY gate (category, condition, suppressed listing, missing content) and is independent of stock: an out-of-stock product is normally still ELIGIBLE. For whether a SKU can actually sell, read Amazon's ad availability — Amazon's own serving-side stock grading (IN_STOCK | IN_STOCK_SCARCE | OUT_OF_STOCK | PREORDER | LEADTIME | AVAILABLE_DATE) — together with the inventory fields; never infer stock from the eligibility status. NOT_REPORTED means the check ran but Amazon returned no grading for that SKU: treat it as unknown, not as in-stock, and fall back to the inventory fields. Eligibility is per SKU: judge whether a product can advertise by the seller's own SKU, and advertise that SKU. Used resale offers — Amazon Grade & Resell SKUs starting `amzn.gr.`, returned units Amazon resells as used — are left out unless include_resale is true: they can never be advertised, so their INELIGIBLE_CONDITION… reasons say nothing about the product.

list_products

List the Amazon seller workspaces the authenticated user can access. The result lists every seller this account can access. Each seller exposes its `sellerId` (Amazon selling-partner id) and per-marketplace `marketplaceId` — to read a seller other than the active one, pass its `sellerId` as `seller_id` on the read tool (it reads that seller's default marketplace). The `current` flags identify which seller and marketplace are active now.

list_sellers

Do not use for general requests to show metrics or numbers — use get_seller_metrics / get_asin_metrics / get_parent_asin_metrics instead. Use it only when the user explicitly asks for a visual dashboard, charts, or an interactive metrics view. It runs the matching get_* handler (routed on `scope`) with the previous-period comparison on, and returns its metrics plus only the breakdowns your sections name, capped for inline display. Hosts that render embedded views draw those sections inline in the conversation; elsewhere the result is the JSON alone. On MCP hosts that cap tool-result size, keep primary_metrics minimal so the JSON is not externalized (otherwise the embedded widget receives no data). Required 'sections' argument composes the visualization per question — you MUST supply at least one section. Each section is rendered in order; data is resolved from the tool's own metrics/decompositions — you choose the shape and the inputs, the renderer supplies the values. Available sections (discriminator: 'type'): - 'metric-cards' { metrics: string[] } — KPI tiles that auto-adapt: when previous-period data is available the tile shows current + previous + delta; otherwise current only. Use for **any** set of headline KPIs; do NOT create two metric-cards sections covering the same metrics, and do NOT also emit a redundant bar-chart over the same metrics unless a chart adds something the tiles don't already convey. - 'bar-chart' { metrics: string[], mode?: 'grouped' | 'indexed' } — current vs previous bars per metric. Use 'indexed' (previous=100) when metrics have different units (dollars + counts + percent). Prefer this over metric-cards only when the visual comparison matters more than the raw numbers. - 'line-chart' { decompositionKey: string, labelKey?: string, topN?: number } — a ranked line across one decomposition's items (e.g. 'total_sales_by_asin'), one point per item. NOT a trend over time: a decomposition holds each item's totals for the range, with no dates, so this tool cannot chart how something changed day by day. - 'pie-chart' { decompositionKey: string, labelKey?: string, topN?: number, variant?: 'pie' | 'donut' } — part-to-whole distribution from a decomposition. Use ONLY for low-cardinality splits (≤6 slices, e.g. 'total_sales_by_channel'). For more slices or when precise comparison matters, prefer bar-chart. - 'callout' { source?, subtextSource?, value?, subtext?, tone?, title? } — titled tile for a highlight. **Prefer `source`/`subtextSource` over `value`/`subtext`** so numbers and labels come from the tool output, not LLM prose. Resolvers: · { "type": "metric", "key": "total_sales", "show": "value" | "delta_pct" | "previous" } · { "type": "top", "decompositionKey": "total_sales_by_asin", "labelKey": "asin", "show": "label" | "label_share" | "label_delta" | "value" } Only use free-form `value`/`subtext` when no resolver fits (e.g. a purely qualitative headline like "Strong week"). Examples: Question: "how did last 30 days compare to the previous 30?" sections: [ { "type": "metric-cards", "metrics": ["total_sales", "units_sold", "avg_selling_price", "ad_sales"] } ] Question: "show me seller performance headlines for last 30 days" sections: [ { "type": "metric-cards", "metrics": ["total_sales","ad_spend","ad_sales","acos","roas"] } ] Question: "which ASINs sell the most?" sections: [ { "type": "line-chart", "title": "Top ASINs by sales", "decompositionKey": "total_sales_by_asin", "labelKey": "asin", "topN": 5 } ] Question: "what's my sales mix by channel?" sections: [ { "type": "pie-chart", "title": "Sales by channel", "decompositionKey": "total_sales_by_channel", "labelKey": "sales_channel", "variant": "donut" } ] Question: "what stood out?" sections: [ { "type": "callout", "tone": "positive", "title": "Top performer", "source": { "type": "top", "decompositionKey": "total_sales_by_asin", "labelKey": "asin", "show": "label_share" } }, { "type": "callout", "tone": "neutral", "title": "Ad efficiency", "source": { "type": "metric", "key": "acos", "show": "value" }, "subtextSource": { "type": "metric", "key": "acos", "show": "delta_pct" } } ] AVAILABLE METRICS FOR SELLER/ASIN/PARENT_ASIN SCOPE: IMPORTANT: Use EXACT metric names below. Do NOT abbreviate (e.g., use "ad_spend" not "spend", "ad_sales" not "sales"). Sales & Traffic Base Metrics: total_sales, units_sold, sessions, page_views, browser_sessions, mobile_app_sessions, total_order_items, search_volume, asin_impression_count, asin_impression_share, asin_click_count, asin_click_share, asin_cart_add_count, asin_cart_add_share, asin_purchase_count, asin_purchase_share, total_query_impression_count, total_click_count, total_cart_add_count, total_purchase_count, total_click_rate, total_cart_add_rate, total_purchase_rate, asin_median_purchase_price, total_median_purchase_price, buy_box_percentage, unit_session_percentage, rank, total_cogs, total_amazon_fees, total_referral_fees, total_fba_fees, total_profit, units_refunded Advertising Performance Metrics: ad_impressions, ad_clicks, ad_spend, ad_sales, ad_conversions Ad Structure Counts (active within period): ad_active_campaigns, ad_active_ad_groups, ad_active_targets Ad Bleeder Metrics (targets still spending with zero conversions over 180 days): ad_bleeder_count, ad_bleeder_spend, ad_bleeder_earning_elsewhere_count, ad_bleeder_earning_elsewhere_spend - ad_bleeder_count / ad_bleeder_spend: targets that are dead EVERYWHERE — still spending as of the seller's latest ad data, and the same target text earns no ad sales anywhere else in the account. Safe to quote on its own as wasted spend. - ad_bleeder_earning_elsewhere_count / ad_bleeder_earning_elsewhere_spend: zero-conversion targets whose SAME text DOES earn ad sales on other targets. That is a misallocation signal (right keyword, wrong product or campaign), not proven waste — do not add it to the waste total without saying so. - Both pairs are TOTALS over every bleeder. ad_bleeder_spend_by_target is only the worst 100, truncated again to the 20 rows you can see, so on a big seller the scalars are larger than the rows sum to — quote the scalars for totals and the rows for what to act on, and never present the visible rows as the whole picture. - Every dollar here is a LIFETIME accumulation (up to 180 days), window-invariant: the range (l7d/l30d/…) only filters WHICH targets appear, never the amounts. Attach no time window to these dollars — not 'this month', not 'this period'. lifetime_waste is what the target has burned in total, not its current rate (last_spend_date is how recent the burn is); in-period spend per target comes from query_analytics (ppc, bounded by report_datetime). Decomposition ad_bleeder_spend_by_target: top 100 rows with target, match_type, target_id, campaign_id, campaign_name, promoted_asin, last_spend_date, lifetime_waste, lifetime_clicks, lifetime_orders, same_target_elsewhere Reading a row: 1. One row = one TARGET (target_id), not a keyword. The same keyword text runs as many separate targets across campaigns and ad products, so `lifetime_orders: 0` is that target's verdict alone, NOT the keyword's. Name the row by its campaign_name/target_id, never as a bad keyword. 2. same_target_elsewhere is what that SAME text earns on OTHER targets account-wide over the same 180 days: {ad_sales, orders, ad_spend, target_ids}. ad_sales 0 means genuinely dead and a clean kill. A large ad_sales means the keyword works and THIS placement of it does not — recommend moving or rebalancing spend, and never propose a negative keyword on that text. A NULL same_target_elsewhere means the row has no target text to compare (auto-targeting), so the check could not run at all — do not read it as 0. 3. Every row is STILL SPENDING: a target is listed only if its last positive-cost day (last_spend_date) is within 14 days of the seller's newest ad report. Anything remediated by anyone — archived, paused, negated, via this agent, the managed engine, or Seller Central — stops spending and drops off on its own, so do not treat the list as needing a snapshot-state check. Quote last_spend_date when asked whether something is still bleeding; a date near the window edge is waste that has likely already been dealt with. A row may also carry agent_action = {tool, status, date}: the newest successful write made through this agent's tools (by the caller or a teammate) touching that campaign/target. It proves a touch, not a fix — compare the dates: last_spend_date on or before agent_action.date means the waste was already addressed on that date and is aging out (present as handled, naming the tool and date); spend continuing after the action means the action did not stop the bleed (present as live, with the action as context). B2B Base Metrics: b2b_sales, b2b_units Sales & Traffic Derived Metrics: avg_selling_price, conversion_rate, page_views_per_session, click_through_rate, cart_add_rate, purchase_rate, search_conversion_rate, market_conversion_rate, conversion_rate_vs_market, price_vs_market, share_of_voice, market_share_purchases, market_share_clicks, b2c_sales, b2c_units, b2b_percentage, asin_purchase_sales, total_purchase_sales, profit_margin, refund_rate Advertising Derived Metrics: acos, roas, cpc, ctr, ad_conversion_rate, tacos Available Decompositions (78 total): total_sales_by_asin, units_sold_by_asin, sessions_by_asin, page_views_by_asin, total_sales_by_parent_asin, units_sold_by_parent_asin, total_profit_by_asin, total_profit_by_parent_asin, asin_purchase_count_by_search_query, search_volume_by_search_query, asin_click_count_by_search_query, total_sales_by_channel, units_sold_by_channel, ad_impressions_by_campaign_type, ad_impressions_by_campaign_name, ... (58 more) Special decompositions: sqp_by_search_query, rank_by_category, ad_bleeder_spend_by_target, keyword_opportunities_by_search_query, bids_by_target You can request any metric from the base or derived lists. Decompositions are automatically included when their parent metric is requested. sqp_by_search_query is a rich multi-metric table: each row is a search query with columns for all SQP metrics (search_volume, asin_purchase_count, asin_click_count, asin_purchase_share, search_conversion_rate, asin_purchase_sales, total_purchase_sales, etc.), sorted by purchases. Present as a table. SCALE: every *_share, *_rate and *_conversion_rate value here is ALREADY A PERCENT — asin_purchase_share 0.03 means 0.03% (three hundredths of one percent), total_purchase_rate 9.7 means 9.7%. Never multiply one by 100; append the % sign and present the number as given. COMMON QUESTIONS -> METRICS MAPPING: - "wasted spend" / "waste" / "inefficient spend" -> ad_bleeder_spend, ad_bleeder_count, ad_bleeder_earning_elsewhere_spend, ad_bleeder_spend_by_target (request the earning_elsewhere pair too — without it you only see the dead-everywhere half and will understate what the list contains) - "how many campaigns/targets" -> ad_active_campaigns, ad_active_ad_groups, ad_active_targets - "ad efficiency" -> acos, roas, tacos, ad_bleeder_spend - "search query performance" / "top search queries" / "search funnel" -> asin_purchase_count (triggers sqp_by_search_query table) - "listing optimization" / "keyword gaps" / "missing keywords" -> asin_purchase_count (triggers keyword_opportunities_by_search_query) - "how is my [product name] doing" -> total_sales (scan total_sales_by_asin for product_name match, then get_asin_metrics for details) - "profit" / "margins" / "COGS" / "fees" / "pricing" / "product economics" -> call this tool with total_profit as primary_metrics — the response includes total_profit, total_cogs, total_amazon_fees, ad_spend, profit_margin, a profit_breakdown waterfall (Sales → Costs → Fees → Ad Spend → Profit), and a product_economics object with per-unit costs, Amazon fees, and pricing - "bid multiplier" / "placement adjustment" / "bidding strategy" on a named campaign -> ad_spend + placement_by_campaign (filter to campaign; multiplier_percentage, bidding_strategy) - "most aggressive placement multipliers" / "compare placement boosts across campaigns" -> ad_spend + multipliers_by_campaign (placement_*_percentage, bid_multiplier) - "recommended bid" / target bid -> ad_spend + bids_by_target (asin/parent_asin scope only) On this connection each breakdown (decomposition) lists at most 20 rows, and the result says when one was cut.

view_metrics_dashboard

Read the AGENT's Amazon Ads action log (audit trail) for the current workspace profile — every write action invoked through this agent's own tools (bid updates, campaign creates, target adds, archives, …), from any teammate's chat or routine, newest first. Each row has the tool used, status (pending | success | partial | failed | rolled_back), timestamps, latency, error message, an `actor`, and a truncated request preview; set includePayloads=true for the full request/response/rollback JSON. ATTRIBUTION — the log is per ad-account, so it mixes several people's work. Every row carries `actor` = { name, email?, isYou, isAutronStaff? } (`email` is omitted over a connected third-party client). `isYou: true` means the person you are talking to performed it; `isYou: false` means a colleague or a routine they own did, and you must say so by name rather than presenting it as your own or as theirs (e.g. "Ada Lovelace paused that campaign on 3 May", not "I paused it" or "you paused it"). `isAutronStaff: true` is an Autron team member working on the account — refer to them as Autron support and do not invent a personal name. Filter by status, tool, and date range; paginated with a total count. USE THIS for 'what did you change?', 'did that update actually go through?', 'what did you do last week?', and to confirm the exact values a past action SENT. It records what each write changed things TO — it does NOT record what they were BEFORE, and no payload here holds a prior field value. So never state a previous bid/budget/state as fact from this log: to undo something, re-read live state with the query_* tools and get the target value from the user. SCOPE LIMIT — agent actions ONLY. This log does NOT include Autron's managed bidding engine: the Pro/dashboard goal-based bid optimization and campaign management that runs continuously outside this agent. A short or empty log therefore does NOT mean 'nothing changed on this account' — on a managed account the engine may have made millions of bid changes that never appear here. Never present this log as the complete record of changes to the account, and never conclude from it that Autron has done little or nothing. For engine activity (bids optimized, hours saved, campaigns/keywords/targets created) use get_autron_activity; answering 'what has Autron done for me' on a managed account needs BOTH tools. This reads Autron's own audit database (instant, no Amazon API call) — it does NOT list campaigns or their current state; use the query_* tools for that. Auto-executes (no approval card).

query_action_log

Read existing ads (creatives) from the user's Amazon Ads account. Returns up to 50 per call with each ad's id, parent ad group/campaign, ad type, advertised product (ASIN/SKU), state. A Sponsored Brands collection ad carries `collection`: `MANUAL` with its `products` (the featured ASINs, first one also in `productId`), landing page and title; `AUTO` with no products (Amazon picks them) and `excludedProductCount`. Useful for finding which ASINs/SKUs are running in which campaigns. Auto-executes (no approval card).

query_ads

Read existing ad groups from the user's Amazon Ads account. Returns up to 50 per call with each ad group's id, name, parent campaign, state, ad product, default bid, start date. Use this to find real ad group IDs — never invent them. `defaultBid` is a FALLBACK only — it's the bid Amazon uses when a target under this ad group has no explicit bid (typically auto-campaign targets). For manual keyword/product targets, the target-level bid from query_targets is the bid that applies; do NOT cite `defaultBid` as 'the bid' for those. Filter by campaign, ad group ids, name, or state. Auto-executes (no approval card).

query_ad_groups

Read existing Sponsored Products / Brands / Display / TV campaigns from the user's Amazon Ads account. Returns a paginated list (up to 150 per call — 75 with includePlacementBids — and 50 by default) with each campaign's id, name, state, ad product, daily budget, portfolio id, and start date. Each row also carries managedByAutron: true when the campaign sits in the ad engine's own portfolio (Autron AI), so the engine built it and runs it; false for everything else — the seller's own or an agency's campaigns, and campaigns in Autron Strategic (where campaigns created through the agent land by default, and which the engine deliberately leaves alone). It is the ONLY basis for calling a campaign the engine's — never infer it from the name. The field is absent when the lookup could not run; then say you can't tell. Review Requests campaigns (Amazon asks verified buyers for a rating or review; the seller pays per review) are a separate list: pass `goal: "reviews"` to see them, and expect them nowhere else — rows then carry reviewRequests: true. Scanning or auditing every campaign? Set maxResults=150 on the first call and follow nextToken. Use this to look up real campaign IDs — never invent IDs. Filter by name, state, portfolio, or specific IDs. Auto-executes (no approval card).

query_campaigns

List portfolios on the user's Amazon Ads account. Returns each portfolio's id, name, state, in-budget flag, and budget envelope. Primary use case: resolving a portfolio referenced by name into its portfolioId — for example to list the campaigns in it with query_campaigns' portfolio filter. Also useful for answering "what portfolios do I have?" or auditing portfolio assignments. States are SCREAMING_SNAKE_CASE (ENABLED / PAUSED / ARCHIVED). No `adProduct` filter — portfolios span ad products. Auto-executes (no approval card).

query_portfolios

Read existing keyword and product targets from the user's Amazon Ads account. Returns up to 50 per call with each target's id, parent ad group/campaign, state, type, match type, bid, expression, and negative flag. `bid` is the target's AUTHORITATIVE configured bid — the bid this target enters the auction with, before campaign placement adjustments and any dynamic bidding. When a manual target has a `bid` value, that's the real bid; the parent ad group's `defaultBid` is irrelevant. Only when `bid` is missing (typically auto-campaign auto-targets) does the ad group's `defaultBid` apply. For SP auto-campaign theme targets (targetType=THEME), the `matchType` and `expression` fields both carry the theme label — KEYWORDS_CLOSE_MATCH, KEYWORDS_LOOSE_MATCH, PRODUCT_SUBSTITUTES, PRODUCT_COMPLEMENTS, etc. Use this to fetch real target IDs — never invent them. Filter by campaign, ad group, target id, type, match type, keyword, or product (ASIN). With `productId` (or several ASINs in `productIds`, checked in one scan), the result carries `complete`: when it is false the scan stopped early — call again with `nextToken` before concluding an ASIN is not targeted or not negated anywhere. Auto-executes (no approval card).

query_targets

FALLBACK TOOL — do NOT default to this. For any standard performance question use get_seller_metrics / get_asin_metrics / get_parent_asin_metrics first: they're faster and cover the fixed windows (l7d, l30d, l90d, last_month, last_quarter) plus search-term decompositions (ad_*_by_query for paid queries, sqp_by_search_query for Search Query Performance). Reach for this raw SQL when those structurally can't express the request ACCURATELY at the window and grain asked for — an arbitrary or rolling date window, week-over-week movement the fixed windows flatten away, a grouping/grain no decomposition offers, a metric they don't return, or a join across domains. Don't re-fetch a headline number a get_*_metrics call already returns correctly. THREE SQP CASES BELONG HERE, not to the decomposition, even though sqp_by_search_query looks like it covers them: zero-purchase queries (it is a top-100-BY-PURCHASES cut, so they are simply absent), per-query impression share (sqp_by_search_query carries no impression figures per query; the cache only has a seller/ASIN-level asin_impression_share for its fixed windows), and share or rank movement between weeks (nothing in the cache carries week-over-week change). When it IS the right call, it runs a single read-only PostgreSQL SELECT against this seller's data warehouse and returns rows as JSON. RULES - Read-only: a single SELECT or WITH…SELECT. No INSERT/UPDATE/DELETE/DDL, no multiple statements. - Queryable relation(s): `ppc`, `ppc_placement`, `review_requests`, `business_metrics`, `sqp`, `engine_actions`, `engine_bids`, `product_ads`, `campaigns`, `negatives`, `review_request_ads` ONLY. Anything else is not accessible and will error. - Standard PostgreSQL, not MySQL: a date window is now() - interval '7 days' (INTERVAL 7 DAY is a syntax error, and there is no DATE_SUB()). Do the aggregation/grouping/sorting in SQL. Always constrain rows (filters, GROUP BY, LIMIT) — max 1000 rows are returned, and results are truncated if too large. - numeric and bigint columns may come back as strings; cast if you compute on them. SCOPE & PERFORMANCE - Results are scoped to your active workspace's Amazon Ads profile/marketplace by default. The seller's DB may hold multiple marketplaces — pass scope="seller" ONLY for an explicit cross-marketplace comparison. - Every relation is partitioned monthly by its period column — report_datetime (ppc, ppc_placement), start_datetime (sqp), date (business_metrics), purchase_date (review_requests), created_at (engine_actions), updated_at (engine_bids), snapshot_datetime (product_ads), report_date (review_request_ads). Bound time windows on THAT column so only the relevant months are scanned — an unbounded query reads all history. On the report relations, created_at/updated_at are row-ingest time and won't prune partitions — never window on them there. product_ads is the exception to bounding: its rows are change-driven, so a current-map query (latest row per ad) takes NO time window. campaigns is already collapsed to one current row per campaign and takes no time window either. RELATION: ppc — search-term-grain ad performance — SP/SB/SD keyword & target query reports, one row per (ad entity × customer search term × report snapshot) Columns: - tab text source report: sp_keyword_query | sp_target_query | sb_keyword_query | sb_target | sd_target - campaign_type text 'Sponsored Products' | 'Sponsored Brands' | 'Sponsored Display' - profile_id bigint Amazon Ads profile id - seller_id text seller's selling-partner id - marketplace_id text Amazon marketplace id - campaign_id bigint - campaign_name text - ad_group_id bigint - ad_group_name text - id bigint keyword / target id - target text the keyword text or targeting expression being bid on - match_type text EXACT | PHRASE | BROAD (keywords) or a targeting expression (targets) - query text the actual CUSTOMER SEARCH TERM that triggered the ad (this is the search-term grain) - manager text 'Autron' if the ad sits in an Autron-managed portfolio, else 'Non-Autron'. A property of the CAMPAIGN, not of the product: it does NOT say whether the ad engine runs a product (a product's switch is in list_goals — goal_state "enabled" lists the products it runs) - portfolio_id bigint the ad's portfolio on that report day — manager is derived from it, so the portfolios with manager = 'Autron' are Autron's own - impressions numeric - clicks numeric - cost numeric ad spend - attributed_conversions numeric attribution = GREATEST(7d,14d,30d) for SP/SD, 14d for SB - attributed_sales numeric same attribution basis as attributed_conversions - report_datetime timestamptz report period timestamp AND the monthly partition key — filter/GROUP BY this for all date windows - created_at timestamptz row INGEST time — do NOT use for performance date windows (won't prune partitions and is semantically wrong; use report_datetime) - promoted text Autron-promoted child token (Autron-managed rows only) - promoted_parent text parent ASIN - promoted_category text Clicks only, for Sponsored Products: an SP search-term row (tab sp_keyword_query / sp_target_query), like an SB keyword row, exists only for a term that drew at least one click. Impressions that drew no click are NOT here, so a campaign missing from ppc may still have delivered thousands of impressions — to ask whether a campaign delivered at all, sum impressions in ppc_placement (campaign grain, every impression). SB and SD target rows do carry impression-only rows. Performance: partitioned monthly by report_datetime — always bound time-based queries with a report_datetime range (an unbounded query scans all history). Never filter created_at for a time window (it won't prune). Results are scoped to the active marketplace's profile by default (scope="seller" to span all). Attribution caveat: attributed_conversions/attributed_sales use a widest-window (GREATEST) basis, so totals will NOT exactly match a fixed-window Amazon console export — say so if a user compares to console numbers. Derived metrics (compute in SQL): ACoS % = 100*sum(cost)/nullif(sum(attributed_sales),0); CVR % = 100*sum(attributed_conversions)/nullif(sum(clicks),0); CPC = sum(cost)/nullif(sum(clicks),0). Examples: - Top converting search terms, last 90 days: select query, sum(clicks) clicks, round(sum(cost),2) cost, sum(attributed_conversions) conv, round(sum(attributed_sales),2) sales from ppc where report_datetime >= now() - interval '90 days' and query is not null group by query order by sales desc limit 50 - Negative-keyword candidates (spend, no sales) over 6 months: select query, sum(clicks) clicks, round(sum(cost),2) cost from ppc where report_datetime >= now() - interval '180 days' group by query having sum(attributed_sales) = 0 and sum(cost) > 50 order by cost desc limit 50 RELATION: ppc_placement — campaign-placement-grain ad performance — SP/SB placement reports, one row per (campaign × placement × report snapshot); the placement cut of the same data as `ppc` Columns: - tab text source report: sp_campaign_placement | sb_campaign_placement - campaign_type text 'Sponsored Products' | 'Sponsored Brands' (SD has no placement reports) - profile_id bigint Amazon Ads profile id - seller_id text seller's selling-partner id - marketplace_id text Amazon marketplace id - campaign_id bigint - campaign_name text - campaign_status text - campaign_budget numeric - campaign_site text SP only (NULL for SB): 'US' | 'AmazonAll' | 'AmazonBusiness' — Amazon Business spend is sliced by THIS column, not by placement (AB rows carry normal placement labels) - creative_type text SB only (NULL for SP) - placement text normalized bucket: 'Top of Search' | 'Product Pages' | 'Rest of Search' | 'Homepage' | 'Off Amazon' (unmapped locales fall back to the raw label) - placement_raw text the report's original placement label (e.g. 'Top of Search (first page)', 'Detail Page on-Amazon') - manager text 'Autron' if the campaign sits in an Autron-managed portfolio, else 'Non-Autron' (same derivation as ppc; a campaign property, not whether the ad engine runs a product — that is list_goals) - portfolio_id bigint the campaign's portfolio on that report day — manager is derived from it, so the portfolios with manager = 'Autron' are Autron's own - impressions numeric - clicks numeric - cost numeric ad spend - attributed_conversions numeric attribution = GREATEST(7d,14d,30d) for SP, 14d for SB - attributed_sales numeric same attribution basis as attributed_conversions - report_datetime timestamptz report period timestamp AND the monthly partition key — filter/GROUP BY this for all date windows - created_at timestamptz row INGEST time — do NOT use for date windows - promoted text Autron-promoted ASIN (Autron-managed rows only) - promoted_parent text parent ASIN - promoted_category text - campaign_term text Autron campaign-name keyword/theme label (e.g. 'microfiber mop', or 'auto'/'PT' for non-keyword campaigns); NULL for non-Autron - campaign_match text Autron campaign-name match label (exact | phrase | broad | …); NULL for non-Autron Grain: campaign × placement — there is NO keyword dimension here (Amazon placement reports are campaign-level). campaign_term is a naming-convention LABEL of the campaign's theme, not keyword attribution: even Autron campaigns can bundle multiple targets (brand/PT/auto variants). For a campaign's true target list, look it up SEPARATELY in ppc — e.g. join (select distinct campaign_id, target from ppc where report_datetime >= …) by campaign_id. NEVER join the two relations' raw rows and then sum cost/sales: both are multi-row fact tables and the join multiplies every metric. Performance: partitioned monthly by report_datetime — always bound queries with a report_datetime range. Results are scoped to the active marketplace's profile by default (scope="seller" to span all). Attribution caveat: same widest-window (GREATEST) basis as ppc, so totals will NOT exactly match a fixed-window Amazon console export. Placement totals also won't exactly equal ppc totals for the same window (campaign-grain placement reports vs query-triggered search-term rows). Examples: - Placement split, last 90 days: select placement, round(sum(cost),2) spend, round(sum(attributed_sales),2) sales, round(100*sum(cost)/nullif(sum(attributed_sales),0),1) acos_pct from ppc_placement where report_datetime >= now() - interval '90 days' group by placement order by spend desc - Campaigns overspending on Product Pages vs their Top of Search: select campaign_name, placement, round(sum(cost),2) spend, round(100*sum(cost)/nullif(sum(attributed_sales),0),1) acos_pct from ppc_placement where report_datetime >= now() - interval '60 days' and placement in ('Top of Search','Product Pages') group by campaign_name, placement order by spend desc limit 50 RELATION: review_requests — review-request automation trail — one row per eligible order, with whether/when Amazon was asked to request a review (a VIEW over review_solicitation minus buyer geography). Reviews drive conversion rate, which is what makes ad spend efficient, so this is the evidence behind 'is Autron actually getting me reviews' Columns: - amazon_order_id text the Amazon order id (e.g. 112-3456789-0123456) - seller_id text seller's selling-partner id - marketplace_id text Amazon marketplace id - purchase_date timestamptz - earliest_delivery_date timestamptz start of the delivery window (null for some seller-fulfilled orders) - latest_delivery_date timestamptz end of the delivery window (null likewise) - earliest_ship_date timestamptz - latest_ship_date timestamptz - order_type text - solicit_sent boolean true once the review request went out - solicit_date timestamptz when it went out (null until sent) - solicit_skip_reason text why it was skipped (e.g. Canceled, Refunded); null otherwise - order_status text - fulfillment_type text - sales_channel text - created_at timestamptz - updated_at timestamptz Scoped to the ACTIVE MARKETPLACE by default (like ppc is scoped to the active profile); scope="seller" spans all marketplaces. Only meaningful while review automation is on for the marketplace (get_review_automation). Status logic: sent = solicit_sent; skipped = solicit_skip_reason is not null; pending = neither, while now() <= coalesce(latest_delivery_date, latest_ship_date, purchase_date) + interval '30 days' (the worker's window — delivery dates aren't guaranteed); after that the window closed unsent. Performance: partitioned monthly by purchase_date — bound THAT column too, even when the question is about solicit_date, or the query scans every partition (an order bought in one month can be solicited in the next, so give purchase_date the wider bound). Example — this month's requests by day: select solicit_date::date d, count(*) sent from review_requests where solicit_sent and solicit_date >= date_trunc('month', now()) and purchase_date >= date_trunc('month', now()) - interval '2 months' group by 1 order by 1 RELATION: business_metrics — daily business performance — Amazon's Sales & Traffic report at (marketplace × SKU × day) grain: ordered product sales, units, order items and the session/page-view traffic behind them Columns: - date timestamptz the report DAY (UTC midnight) AND the monthly partition key — filter/GROUP BY this for all date windows - marketplace_id text Amazon marketplace id - seller_id text seller's selling-partner id - sku text the seller SKU (one ASIN can have several) - asin text - parent_asin text - category text - currency text ISO 4217 code the money columns are in - units_ordered integer - units_ordered_b2b integer - ordered_product_sales numeric SALES — this is the "total sales" number the app's KPI tiles show - ordered_product_sales_b2b numeric - total_order_items integer orders (order items, not units) - total_order_items_b2b integer - sessions bigint visits in 24h; sessions = browser_sessions + mobile_app_sessions - page_views bigint page views across both devices - buy_box_percentage numeric % of the SKU's page views where this seller held the featured offer - unit_session_percentage numeric Amazon's conversion rate for the listing (units / sessions) - browser_sessions, mobile_app_sessions bigint the desktop/mobile-web vs Amazon-app split of sessions (they sum to sessions) NOT projected, deliberately: the share-of-seller *_percentage columns (this SKU's cut of the SELLER'S daily traffic — they read like conversion rates and are not; recompute the session and total page-view ones with a window over (marketplace_id, date), e.g. 100.0*sessions/sum(sessions) over (partition by marketplace_id, date)), the per-device page-view splits and their shares, the B2B conversion rate, and created_at/updated_at (write time, not report time — use max(date) for freshness). Say a per-device page-view share is not available rather than approximating it from sessions. This is the SAME table the precomputed metrics tools read (autron-sync sums ordered_product_sales into the agent_data cache), so a window here and a get_seller_metrics range are two windows of ONE definition of sales. Use this relation when the question needs a window those tools don't offer (an arbitrary date range, a month-by-month trend, sales by day). Grain: one row per marketplace/sku/day — SUM over sku for a seller total, GROUP BY asin or parent_asin for a product cut. Rows exist only for SKUs with activity that day. Scope: no profile_id — sales are reported per MARKETPLACE. The default scope="profile" shadows this by the active marketplace; scope="seller" spans all of them. Performance: partitioned monthly by date — always bound queries with a date range. NOT here: refunds, average selling price (seller-grain rollup), ad spend (that's `ppc`), and anything cost- or profit-shaped — per-unit COGS is not in this database, so profit CANNOT be computed from these columns. Ads and sales are separate fact tables: aggregate each side first and join the aggregates, never the raw rows. Restatement & history: Amazon restates recent days and ingestion re-pulls each day at 1/7/30/90-day lookbacks, so the last ~90 days can shift after first landing. History reaches back at most ~90 days before the seller connected to Autron. Derived metrics (compute in SQL): TACoS % = 100 * (ad spend from `ppc`) / nullif(sum(ordered_product_sales),0) — compute each side in its own subquery; recompute unit-session % over any window as 100*sum(units_ordered)/nullif(sum(sessions),0) — do NOT average the precomputed column; average selling price = sum(ordered_product_sales)/nullif(sum(units_ordered),0) (per UNIT — this is not AOV; average order value = sum(ordered_product_sales)/nullif(sum(total_order_items),0)); B2C slice = the additive column minus coalesce(its _b2b column, 0) — the _b2b columns are NULL, not 0, for sellers with no B2B sales, and this only works for the count/amount columns, never the percentages; weight buy_box_percentage by page_views when rolling up. vs get_seller_metrics: its sales/traffic decompositions (total_sales_by_asin, units_sold_by_asin, sessions_by_asin, page_views_by_asin, …) serve this same data pre-aggregated at ASIN grain — fixed windows, top 100, zero rows dropped. Prefer those for headline snapshots; use this view for SKU grain, arbitrary windows, conversion analysis, and buy-box tracking. Totals may differ slightly across the layers — say so if the user compares. Joining `sqp`: aggregate BOTH sides to (asin, week) in their own CTEs first, then join on both keys. A raw-row join fans out — one ASIN with 7 daily rows here and 100 weekly query rows there returns 700 rows, each side repeated by the other's row count, and every sum is then wrong. THE WEEK KEYS DO NOT LINE UP BY DEFAULT: SQP weeks start SUNDAY and date_trunc('week', …) is ISO, starting MONDAY, so the two keys sit a day apart and the join silently returns no rows. Bucket this side to its preceding Sunday — (date at time zone 'UTC')::date - extract(dow from date at time zone 'UTC')::int — and key the sqp side on (start_datetime at time zone 'UTC')::date. Examples: - Sales and units by day, last 45 days: select date::date as day, round(sum(ordered_product_sales),2) sales, sum(units_ordered) units from business_metrics where date >= now() - interval '45 days' group by 1 order by 1 - Top products by sales for a custom window: select asin, round(sum(ordered_product_sales),2) sales, sum(units_ordered) units from business_metrics where date >= timestamptz '2026-08-01' and date < timestamptz '2026-08-15' group by 1 order by sales desc limit 20 - SKUs with real traffic but weak conversion, last 4 weeks: select sku, max(asin) asin, sum(sessions) sessions, sum(units_ordered) units, round(100.0 * sum(units_ordered) / nullif(sum(sessions), 0), 1) usp_pct, round(sum(ordered_product_sales), 2) sales from business_metrics where date >= now() - interval '28 days' group by sku having sum(sessions) >= 100 and 100.0 * sum(units_ordered) / nullif(sum(sessions), 0) < 5 order by sessions desc limit 50 - Weekly page-view-weighted buy-box % by ASIN, last 8 weeks: select asin, date_trunc('week', date)::date week, round(sum(page_views * buy_box_percentage) / nullif(sum(page_views), 0), 1) buybox_pct, sum(sessions) sessions, sum(units_ordered) units from business_metrics where date >= now() - interval '56 days' and asin is not null group by 1, 2 order by 1, 2 limit 200 RELATION: sqp — Search Query Performance (Brand Analytics) — weekly search-funnel rows per (customer search query × ASIN): market-wide impressions/clicks/cart-adds/purchases for the query plus this ASIN's own counts and median prices. The whole-market complement to `ppc` (which sees only ad-attributed traffic) Columns: - marketplace_id text Amazon marketplace id - asin text the ASIN this row's asin_* metrics describe (reports are ingested per ASIN) - search_query text the customer search query - start_datetime timestamptz week start (Sunday 00:00 UTC) — the report week AND the monthly partition key; filter/GROUP BY this for all date windows - seller_id text seller's selling-partner id - currency text currency of the *_price columns - parent_asin text parent ASIN, from the seller's catalogue — NULL for a standalone ASIN that has no variation family, so an equality filter or inner join on it drops those rows by design - category text product category (nullable enrichment) - end_datetime timestamptz week end (Saturday 23:59:59 UTC) - search_query_score numeric Amazon's rank of the query within this ASIN's weekly query set (1 = top query) - search_query_volume numeric how many times the query was searched that week, market-wide — the denominator for every funnel RATE - total_query_impression_count numeric market-wide impressions for the query that week (every ASIN shown, not just this seller's) - asin_impression_count numeric impressions THIS ASIN got for the query - total_click_count numeric market-wide clicks on the query's results - asin_click_count numeric clicks on this ASIN - total_median_click_price numeric market median price of clicked items - asin_median_click_price numeric this ASIN's median clicked price - total_cart_add_count numeric market-wide cart-adds; asin_cart_add_count is this ASIN's - asin_cart_add_count numeric - total_median_cart_add_price, asin_median_cart_add_price numeric median prices at the cart-add stage - total_purchase_count numeric market-wide purchases; asin_purchase_count is this ASIN's - asin_purchase_count numeric - total_median_purchase_price, asin_median_purchase_price numeric median prices at the purchase stage NOT projected, deliberately: every *_share and *_rate column. Each is a PERCENT (46.41 = 46.41%) that is exactly recomputable from the counts above, so the column adds nothing — and reading one straight is a trap over any window wider than a single row, because a share cannot be averaged across ASINs or weeks. Compute it. Also not projected: the text start_date/end_date duplicates, the shipping-speed split counts, and created_at/updated_at (write time, not report time — use max(start_datetime) for freshness). Scoped to the ACTIVE MARKETPLACE by default (like review_requests); scope="seller" spans all marketplaces. Performance: partitioned monthly by start_datetime — always bound queries with a start_datetime range (an unbounded query scans all history). The index is (marketplace_id, start_datetime, category, parent_asin, asin); narrow by category/parent_asin/asin when possible. Never filter created_at/updated_at for time windows. Coverage: weekly grain, one row per (search_query × asin × week). Rows exist ONLY for the seller's top-selling ASINs (ingestion requests per-ASIN reports, roughly the top 20 ASINs by 90-day sales) — a missing ASIN means "not in the ingested cohort", NOT "no search traffic". An entirely empty relation usually means the seller is not brand-registered (Amazon denies the report) or ingestion hasn't caught up — treat as no data, not an error. A week's rows land a few days after the week closes (Sun–Sat UTC). Aggregation trap: search_query_volume and every total_* column are QUERY-WEEK MARKET totals repeated on each ASIN row of the same (search_query, start_datetime). Sum the asin_*_count columns freely; for market totals across several ASINs take max() (or dedupe) per (search_query, start_datetime), or you double-count the market. NEVER sum or average a *_share / *_rate column — see below. Shares and rates: compute them, they are not columns. Amazon's own values are PERCENTS (a stored click rate of 46.41 means 46.41%), so a recomputed figure must be scaled the same way: impression share % = 100*sum(asin_impression_count)/nullif(sum-of-deduped total_query_impression_count,0), and click/cart-add/purchase share the same way against their total_*_count. All three of Amazon's market rates use SEARCHES as the denominator, not impressions or clicks: market CTR % = 100*total_click_count/nullif(search_query_volume,0), cart-add rate % = 100*total_cart_add_count/nullif(search_query_volume,0), purchase rate % = 100*total_purchase_count/nullif(search_query_volume,0). Market click→purchase CVR % = 100*total_purchase_count/nullif(total_click_count,0). Price gap = asin_median_purchase_price - total_median_purchase_price (positive = priced above what the market buys at). vs get_seller_metrics: its sqp_by_search_query decomposition serves this same data pre-aggregated — fixed windows only, top 100 queries by ASIN purchases, ZERO-purchase queries dropped, and a week counts when its Sunday start falls inside the window. Its *_share / *_rate columns are on the same PERCENT scale as the stored ones (46.41 = 46.41%), so never multiply one by 100. Prefer it for headline snapshots; use this view for arbitrary windows, week-over-week movement, zero-purchase high-volume queries (invisible in the Redis layer), and joins against ppc. Its counts and its market figures are both computed the same way this view is (autron-sync de-duplicates each query-week's market totals before aggregating and recomputes shares from counts), so the two layers agree. Small differences remain from report lag and window basis — say so if the user compares. Joining `business_metrics` (daily sales/traffic): aggregate both sides to (asin, week) first — a raw-row join multiplies each side by the other's row count. Weeks here start SUNDAY, so bucket the daily side to its preceding Sunday with (date at time zone 'UTC')::date - extract(dow from date at time zone 'UTC')::int and key this side on (start_datetime at time zone 'UTC')::date; date_trunc('week', …) is ISO/Monday and would miss by a day, returning nothing. Examples: - Impression-share movement, last 2 weeks vs the 4 before: with w as ( select search_query, start_datetime wk, sum(asin_impression_count) asin_impr, max(total_query_impression_count) query_impr from sqp where start_datetime >= now() - interval '42 days' group by 1, 2 ), agg as ( select search_query, 100.0 * sum(asin_impr) filter (where wk >= now() - interval '14 days') / nullif(sum(query_impr) filter (where wk >= now() - interval '14 days'), 0) recent_share, 100.0 * sum(asin_impr) filter (where wk < now() - interval '14 days') / nullif(sum(query_impr) filter (where wk < now() - interval '14 days'), 0) prior_share from w group by 1 having sum(query_impr) filter (where wk < now() - interval '14 days') > 0 ) select search_query, round(recent_share, 2) recent_share_pct, round(prior_share, 2) prior_share_pct from agg order by prior_share - recent_share desc nulls last limit 50 - High-volume queries with ZERO purchases (not answerable from the Redis layer): select search_query, max(search_query_volume) peak_weekly_searches, sum(asin_impression_count) impressions, sum(asin_click_count) clicks from sqp where start_datetime >= now() - interval '28 days' group by 1 having sum(asin_purchase_count) = 0 order by peak_weekly_searches desc limit 50 - Paid spend vs TOTAL search-result purchases for the same query (join ppc on the query text). SQP counts every purchase for the query, ads included — organic-only cannot be isolated from these relations, so never label this side "organic": with paid as ( select lower(query) q, round(sum(cost), 2) spend, sum(attributed_conversions) paid_conv from ppc where report_datetime >= now() - interval '28 days' and query is not null group by 1 ), weekly as ( -- dedupe the repeated market totals BEFORE aggregating them select lower(search_query) q, start_datetime wk, sum(asin_purchase_count) asin_purchases, max(total_purchase_count) market_purchases from sqp where start_datetime >= now() - interval '28 days' group by 1, 2 ), market as ( select q, sum(asin_purchases) purchases, round(100.0 * sum(asin_purchases) / nullif(sum(market_purchases), 0), 1) purchase_share_pct from weekly group by 1 ) select q query, p.spend, p.paid_conv, m.purchases total_purchases, m.purchase_share_pct from paid p full join market m using (q) order by coalesce(p.spend, 0) desc limit 50 RELATION: engine_actions — the ad engine's write ledger — one row per entity per change the ENGINE sent to Amazon (bid, state, placement, create, archive), stamped with the run that sent it; the only relation that says 'Autron did this' Columns: - profile_id bigint Amazon Ads profile id - created_at timestamptz when the engine sent it — the TIME AXIS and the monthly partition key: ALWAYS bound queries on created_at (the opposite of ppc/business_metrics, where created_at is ingest time) - run_id text one id per engine invocation; every row of one run shares it - entity_type text campaign | ad_group | target | keyword | negative | product_ad - entity_id text the Amazon id of that entity (a campaign id, a keyword id, ...) - action text bid_update | state_update | placement_update | create | archive - request_type text the Amazon Ads API request family the write went through - field text the field changed on an update (e.g. bid, state, placement), null on a create/archive - new_value text the value sent, as text - old_value text what the engine read before sending, as text (null when it had nothing to read) - new_value_num numeric new_value as a number where it is one (a bid) - campaign_id bigint the campaign the entity belongs to - ad_group_id bigint the ad group, where the entity has one - seller_id text - marketplace_id text Grain: one row per (run × entity × action). A campaign with rows here is ENGINE-OWNED: the engine wrote to it. A campaign with delivery in ppc but no row here in the window is the seller's own (or an agency's) — the engine never touched it. This is the join that separates "our campaigns" from "theirs". Time: created_at is BOTH the event time and the partition key — always bound it (an unbounded scan reads every monthly partition). Rows only exist from the day the ledger shipped on this seller; absence before that is not "the engine did nothing". Volume: the engine re-bids most targets daily, so bid_update rows dominate — aggregate by campaign_id/run_id or filter action first; never SELECT * over a month. Examples: - Campaigns the engine wrote to in the last 30 days that delivered nothing (the "writes into the void" pattern): with written as (select campaign_id, count(*) writes, max(created_at) last_write from engine_actions where created_at >= now() - interval '30 days' and campaign_id is not null group by 1), delivered as (select campaign_id, sum(impressions) impressions, sum(cost) cost from ppc where report_datetime >= now() - interval '30 days' group by 1) select w.campaign_id, w.writes, w.last_write, coalesce(d.impressions, 0) impressions, coalesce(d.cost, 0) cost from written w left join delivered d using (campaign_id) where coalesce(d.impressions, 0) = 0 order by w.writes desc limit 50 - What the engine last did to one campaign: select created_at, entity_type, entity_id, action, field, old_value, new_value from engine_actions where campaign_id = 123 and created_at >= now() - interval '14 days' order by created_at desc limit 50 - Did the engine pause/enable anything this week: select created_at, entity_type, entity_id, old_value, new_value from engine_actions where action = 'state_update' and created_at >= now() - interval '7 days' order by created_at desc limit 50 RELATION: engine_bids — what the ad engine PRICED per target each cycle — the stored base bid before the time-of-day multiplier; beside engine_actions and ppc it says whether a campaign that never delivered was priced to win Columns: - profile_id bigint Amazon Ads profile id - target_table text which id space target_id lives in: sp_keyword_query | sp_target_query | sb_keyword_query | sb_target - campaign_id bigint - ad_group_id bigint - target_id bigint keyword id or product-target id (same id as ppc.id, in the same table) - bid numeric the base bid the engine stored for this target in this cycle - seller_id text - marketplace_id text - updated_at timestamptz the cycle's stamp — bound queries on it; the latest row per (target_table, target_id) is the bid in force Grain: one row per (cycle × target). The LATEST row per (target_table, target_id) is the current base bid: use DISTINCT ON (target_table, target_id) ... ORDER BY target_table, target_id, updated_at DESC. This is the base the engine STORED, not necessarily the bid on Amazon at a given hour (the time-of-day multiplier is applied when it is published) — and on a seller whose bids the agent has claimed, the engine's stored bid is not what runs. Join to ppc on (target_table = ppc.tab AND target_id = ppc.id), or to engine_actions on campaign_id. Always bound updated_at. Examples: - Current base bid per campaign for the campaigns that never delivered (pair with the engine_actions example): select campaign_id, count(*) targets, round(avg(bid), 2) avg_bid, round(max(bid), 2) max_bid from (select distinct on (target_table, target_id) campaign_id, bid from engine_bids where updated_at >= now() - interval '7 days' order by target_table, target_id, updated_at desc) latest group by 1 order by targets desc limit 50 - How a target's bid moved over the last month: select updated_at::date day, round(avg(bid), 2) bid from engine_bids where target_id = 123 and updated_at >= now() - interval '30 days' group by 1 order by 1 RELATION: product_ads — the campaign-to-ASIN map — one row per product ad per snapshot (which ASIN/SKU each Sponsored Products campaign advertises), for every campaign in the account, the engine's and the seller's own alike Columns: - profile_id bigint Amazon Ads profile id - ad_id bigint the product ad's Amazon id - snapshot_datetime timestamptz when this row was written — the time axis; the LATEST row per ad_id is the current state (see notes: rows are change-driven, so never window this for a current map) - campaign_id bigint - ad_group_id bigint - asin text the advertised ASIN - sku text the advertised SKU (seller-side id) - state text enabled | paused | archived (the ad's own state, not the campaign's) - seller_id text - marketplace_id text - campaign_type text 'Sponsored Products' (product ads exist for SP only) - portfolio_id bigint the campaign's portfolio (an Autron-managed campaign sits in a portfolio named 'Autron …') Grain: one row per (ad × snapshot) — a history, not a current table, and rows are written only when an ad changes (new, edited, archived), so the latest row for a stable ad can be months old. The CURRENT map is the latest row per ad with NO time bound: select distinct on (ad_id) campaign_id, asin, sku, state from product_ads order by ad_id, snapshot_datetime desc — the tool already scopes it to this profile, and the primary key (profile_id, ad_id, snapshot_datetime) makes that an index descent, not a scan. Never put a snapshot_datetime window on a current-map query: a 14-day bound drops every ad that has not changed in 14 days, which is most of them. Bound snapshot_datetime only when the question is about history (when did this ad change), and say so. This is the ONLY relation that names the product for a campaign the engine did not build (ppc.promoted is set for Autron-managed rows only). Join to ppc and engine_actions on campaign_id. engine_actions.campaign_id can be null (account-level rows), so exclude the engine's campaigns with NOT EXISTS, never with NOT IN. Examples: - Which ASINs the seller's own campaigns (no engine writes) are advertising with spend in the last 30 days, and whether an engine-written campaign advertises the same ASIN with no impressions (self-competition): with current_ads as (select distinct on (ad_id) campaign_id, asin from product_ads where state <> 'archived' order by ad_id, snapshot_datetime desc), engine as (select distinct campaign_id from engine_actions where created_at >= now() - interval '90 days' and campaign_id is not null), spend as (select campaign_id, sum(cost) cost_30d, sum(impressions) imp_30d from ppc where report_datetime >= now() - interval '30 days' group by 1), theirs as (select a.asin from current_ads a join spend s using (campaign_id) where s.cost_30d > 0 and not exists (select 1 from engine e where e.campaign_id = a.campaign_id)), ours as (select a.campaign_id, a.asin from current_ads a join engine e using (campaign_id)) select o.campaign_id, o.asin, coalesce(s.imp_30d, 0) imp_30d from ours o join theirs t using (asin) left join spend s using (campaign_id) where coalesce(s.imp_30d, 0) = 0 order by o.campaign_id limit 200 RELATION: campaigns — the current state of every Sponsored Products campaign in the account — one row per campaign (the latest snapshot): state, budget, portfolio, Amazon's delivery status — the engine's campaigns and the seller's own alike Columns: - profile_id bigint Amazon Ads profile id - campaign_id bigint - snapshot_datetime timestamptz when this state was last recorded — rows are change-driven, so an old value means the campaign has not changed since; the export runs six times a day, so a change made in the last few hours may not be here yet - name text - campaign_type text 'Sponsored Products' - targeting_type text auto or manual, as Amazon reports it - state text enabled | paused | archived — the CAMPAIGN's own latest recorded state (an ad's state is product_ads.state) - daily_budget numeric marketplace currency - start_date text YYYYMMDD - portfolio_id bigint null when the campaign is in no portfolio - delivery_status text DELIVERING | NOT_DELIVERING | UNAVAILABLE, Amazon's own flag; null on rows older than the flag - delivery_reasons text Amazon's reason codes when not delivering, sorted and '|'-joined: CAMPAIGN_OUT_OF_BUDGET, PORTFOLIO_OUT_OF_BUDGET, CAMPAIGN_PAUSED, AD_GROUP_LOW_BID, … - bidding_strategy text - seller_id text - marketplace_id text - goal text ENGAGEMENT marks Amazon's Review Requests campaign (autron-agent#786): it pays per rating or review a verified buyer leaves and has NO sales to show — leave it out of every spend-without-sales verdict (retire, bleeder, wasted spend); null for every ordinary campaign and on rows older than the column Grain: ONE row per campaign — the latest snapshot per (profile, campaign), collapsed in the view. No DISTINCT ON needed and NO time window: a window on snapshot_datetime would drop every campaign that has not changed lately, which is most of them. This is the relation for a campaign's STATE — enabled, paused or archived — whoever set it: the seller, the agent, or the engine. It is the latest recorded state, not a live read: a change made in the last few hours may not be here yet, so a write that must not touch a just-paused campaign checks the ids live with query_campaigns first. product_ads.state is the AD's state, ppc has none, and the engine_actions ledger holds only what the ENGINE last sent. Join to ppc, ppc_placement, product_ads and engine_actions on campaign_id. Sponsored Products only. Examples: - Campaigns the engine wrote to in the last 30 days that are no longer enabled (paused by the seller or the agent): select c.campaign_id, c.name, c.state, c.snapshot_datetime from campaigns c where c.state <> 'enabled' and exists (select 1 from engine_actions e where e.campaign_id = c.campaign_id and e.created_at >= now() - interval '30 days') order by c.snapshot_datetime desc limit 100 - Enabled campaigns Amazon reports as not delivering, with its reason codes: select campaign_id, name, delivery_reasons from campaigns where state = 'enabled' and delivery_status = 'NOT_DELIVERING' order by name limit 100 - Spend with no sales, excluding the Review Requests campaign (its spend buys reviews, not orders): select c.campaign_id, c.name, sum(p.cost) cost_30d from campaigns c join ppc_placement p using (campaign_id) where c.state = 'enabled' and c.goal is distinct from 'ENGAGEMENT' and p.report_datetime >= now() - interval '30 days' group by 1, 2 having sum(p.attributed_sales) = 0 and sum(p.cost) > 0 order by cost_30d desc limit 50 RELATION: negatives — the current negative keywords in the account — one row per negative (the latest snapshot), at the ad group or the campaign level: what a search term is already blocked by, so a negatives proposal never repeats one Columns: - level text 'ad_group' | 'campaign' — where the negative sits (a campaign-level one blocks the term in every ad group of the campaign) - profile_id bigint Amazon Ads profile id - keyword_id bigint the negative keyword's Amazon id - snapshot_datetime timestamptz when this state was last recorded — rows are change-driven; the export runs daily, so a negative written today shows tomorrow - campaign_id bigint - ad_group_id bigint null on a campaign-level negative - keyword_text text the negated text, as Amazon holds it - match_type text negativeExact | negativePhrase - state text enabled | paused | archived — an archived negative blocks nothing - seller_id text - marketplace_id text Grain: ONE row per negative keyword per level — the latest snapshot per (profile, keyword), collapsed in the view. Normalise keyword_text the same way as a search term before comparing (lower, single spaces). A term is already negated at a place when an enabled negative with the same text sits in that ad group, or at the campaign level of that campaign; a negativePhrase on the text blocks the exact term too. Example — search terms already blocked where they served: select p.campaign_id, p.ad_group_id, p.query from ppc p where p.tab = 'sp_keyword_query' and p.report_datetime >= now() - interval '7 days' and exists (select 1 from negatives n where n.state = 'enabled' and n.campaign_id = p.campaign_id and (n.ad_group_id = p.ad_group_id or n.ad_group_id is null) and lower(btrim(regexp_replace(n.keyword_text, '\s+', ' ', 'g'))) = lower(btrim(regexp_replace(p.query, '\s+', ' ', 'g')))) RELATION: review_request_ads — the daily performance of Amazon's Review Requests ads (autron-agent#786) — one row per ad per day: delivery and the reviews it bought, for the ENGAGEMENT-goal campaign (campaigns.goal) Columns: - profile_id bigint Amazon Ads profile id - ad_id bigint the Review Requests ad — one ad per ASIN (product_ads.ad_id) - report_date date the day the metrics are FOR — the time axis AND the partition key: bound every read on it - campaign_id bigint the account's Review Requests campaign (campaigns.goal = 'ENGAGEMENT') - campaign_name text - ad_group_id bigint - asin text the advertised ASIN as the report names it; null when the report carried something else — product_ads has it either way - impressions bigint - clicks bigint for this ad type a click is a shopper opening the review card - cost numeric marketplace currency; Amazon charges only for a rating or review left - currency_code text - reviews integer ratings + written reviews attributed to the ad that day; NULL = Amazon did not report it (the review metrics went live with Amazon's 30 Oct 2026 release — rows before carry NULL, never 0) - written_reviews integer - rating_only_reviews integer - percent_of_reviews_written numeric as Amazon reports it - average_star_rating numeric of the reviews the ad brought, as Amazon reports it - seller_id text - marketplace_id text Grain: one row per (ad × day), re-pulled for the last week each day so a late-attributed review restates its day — sum over a window, never read one day as final. Delivery metrics are present from the first pull; the five review columns are NULL until Amazon's API reports them, and NULL means "not reported", not zero — never coalesce them to 0 in a verdict. Cost per review only over the day-rows whose reviews ARE reported — sum(cost) filter (where reviews is not null) / nullif(sum(reviews), 0) — and say how many day-rows carried no review figure (count(*) - count(reviews)): a plain sum(cost) / sum(reviews) charges unreported days' spend to the reported reviews. Before Amazon reports the metrics every row is unreported and there is no cost per review to quote. This is the ONLY relation with review outcomes; ppc and ppc_placement carry the same campaign's clicks and cost under the ordinary SP columns, and attributed_sales there stays 0 — that is the ad working, not failing. Join to product_ads on ad_id for the ASIN and to campaigns on campaign_id. US only (the ad is). Examples: - Reviews bought per ASIN in the last 28 days, with cost per review: select asin, sum(reviews) reviews, sum(written_reviews) written, round(sum(cost)::numeric, 2) cost, round(sum(cost) filter (where reviews is not null) / nullif(sum(reviews), 0), 2) cost_per_review, count(*) - count(reviews) days_unreported from review_request_ads where report_date >= current_date - 28 group by asin order by reviews desc nulls last limit 50 - Is the Review Requests ad delivering at all this week: select report_date, sum(impressions) impressions, sum(clicks) clicks, sum(cost) cost, sum(reviews) reviews from review_request_ads where report_date >= current_date - 7 group by 1 order by 1 OUTPUT - Up to 1000 rows inline. NEVER hand-format the rows into a CSV/TSV code block — you will mis-escape commas/quotes in the data (search terms contain commas). CSV export is not offered on this connection: it writes a file, and this tool only reads. Auto-executes (read-only, no approval card).

query_analytics

Deterministically validate proposed Amazon listing content (title, bullet points, backend search terms) BEFORE presenting it to the user. Checks for third-party trademarks and Amazon-prohibited phrases (including the account's guardrails: company-wide and seller-workspace banned terms, with own-brand allowed exceptions), special characters (en/em dashes, curly quotes, ™/®, emoji — with a listing-safe normalized version), exact character counts (never hand-count), the title character limit (titles at exactly the limit stop Item Highlights displaying — target under it), and the byte limit on backend search terms. ALWAYS run every suggested title, bullet set, or search-term string through this tool and present the tool's counts and normalized text. Limits: the built-in trademark and prohibited-phrase lists are English (character, count and house-rule checks apply in any language, and the result says so on a non-English marketplace); if the account's guardrails can't be loaded it validates against the built-ins and says so. Read-only: validates text only, changes nothing on Amazon.

validate_listing_content