# Amazon Seller Advertising Audit *A Claude Code prompt for Amazon sellers connected to Seller Labs MCP* **Version 3.7 (2026-09-25)** --- ## How to Use This File 1. Open this file in Claude Code with the **Seller Labs MCP** connected 2. Tell Claude your org ID and venue ID -- Claude handles everything else automatically and presents a single confirmation screen before starting 3. At the end you get a full audit report with prioritized action items 4. Run weekly for quick checks (Sections 2, 3, 5) and monthly for the full audit **Prerequisites**: > **Before you can run this audit, you need the Seller Labs Amazon MCP installed and connected.** > The MCP is what gives Claude access to your Amazon advertising data. Without it, none of the queries below will work. > > **Install it here**: [sellerlabs.com/amazon-mcp](https://www.sellerlabs.com/amazon-mcp) > > Once installed, open this file in Claude Code with the MCP connected, then proceed. - Seller Labs account with Data Hub access (org ID + venue ID required) - *Optional:* a [RankGenius](https://rankgeniusapp.com) API key for live star ratings in Section 12. You don't need to sign up yourself -- if you approve it in the Section 1 confirmation block, Claude creates a free trial key for you (no email, 100 free requests; the audit uses about 20). If you already have a key, set it as the `RANKGENIUS_API_KEY` environment variable and Claude will use it. --- ## SECTION 1: Gather Seller Inputs **What this does**: Runs all auto-queries immediately, infers as many parameters as possible from the data, then presents a single pre-filled confirmation block. In most cases the seller can reply "proceed" and the audit starts -- no sequential Q&A required. **The only two things to ask upfront before running anything**: - **Org ID** -- Seller Labs organization ID (in account settings or from Seller Labs rep) - **Venue ID** -- The marketplace to audit (common values: `2` = Amazon.com US, though actual IDs vary by account -- confirm if unsure) Once org ID and venue ID are confirmed, run all steps below without asking for anything else. --- ### Step 1a: Run all auto-queries First, if `ad-audit-settings.md` exists in the current working folder, read the section for this org and venue. Its values override the defaults in Step 1b and are pre-filled in the confirmation block (marked "from your saved settings"). Then run these five queries immediately. Do not ask the seller any questions yet. **Venue website** (needed for Section 12 direct links): ```sql SELECT website FROM venues WHERE venue_id = {venue_id} ``` **Brand list with SKU counts** (needed for seller type inference and Section 6b): ```sql SELECT brand_name, COUNT(DISTINCT listing_sku) AS sku_count FROM listings WHERE venue_id = {venue_id} AND brand_name IS NOT NULL AND brand_name != '' GROUP BY brand_name ORDER BY sku_count DESC ``` **Data freshness**: ```sql SELECT MIN(date) AS earliest, MAX(date) AS latest, COUNT(*) AS rows FROM campaign_performance WHERE venue_id = {venue_id} ``` **Margin** (auto-calculated -- do not ask the seller): > **CRITICAL -- use the right margin basis.** Do NOT use `AVG(profit_margin)`. That column does not exist on every account's `sl_daily_sku_profit` schema, and even where it does, an UNWEIGHTED per-SKU average over-weights low-volume SKUs and produces a meaningless number -- in practice an unweighted per-SKU gross-margin average can read roughly double the true revenue-weighted margin-after-fees, which in turn drives a target ACoS at roughly double the true break-even. Always compute REVENUE-WEIGHTED margins with `SUM(...)/SUM(sales)`, and pick the basis that matches what a target ACoS needs: **margin after Amazon fees, before ad spend**. That is the money available to spend on ads before a unit goes negative -- the true break-even ACoS. Net profit margin (which already subtracts ad spend) is the WRONG basis for an ad target because it double-counts the very spend you are sizing. ```sql SELECT ROUND(SUM(sales), 2) AS sales_30d, ROUND((SUM(sales) - SUM(cost_of_goods_product)) / NULLIF(SUM(sales),0) * 100, 1) AS gross_margin_cogs_only_pct, ROUND((SUM(sales) - SUM(cost_of_goods_product) + SUM(fba_fees) + SUM(referral_fee)) / NULLIF(SUM(sales),0) * 100, 1) AS margin_after_amazon_fees_pct, ROUND(SUM(profit) / NULLIF(SUM(sales),0) * 100, 1) AS net_profit_margin_pct, COUNT(DISTINCT listing_sku) AS sku_count FROM sl_daily_sku_profit WHERE venue_id = {venue_id} AND date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY) AND sales > 0 ``` > **Column basis (verify with `DESCRIBE sl_daily_sku_profit` -- fee columns are stored NEGATIVE, so they are ADDED back above):** `fba_fees`, `referral_fee`, and `sponsored_product_charge` are negative values; `cost_of_goods_product` is positive. The `profit` field is FULLY-LOADED net (already nets COGS + FBA + referral + ad spend + refunds + storage), which is why it is NOT the target-ACoS basis. > > **Set `{gross_margin_pct}` = `margin_after_amazon_fees_pct`** (the break-even basis). Then `{target_acos}` = `{gross_margin_pct}` minus a buffer (default 5-10 points). If `sl_daily_sku_profit` is unavailable or returns null, set `{gross_margin_pct}` = 30 as a placeholder and note "margin estimated -- seller should verify" in the confirmation block. > > **Performance note:** unbounded aggregates on `sl_daily_sku_profit` (a wide cross-join-style view) can time out on large accounts. Keep the window at 30 days as above; if it still times out, narrow to 7-14 days or filter to SKUs with recent sales. **Competitor candidates from top-converting search terms**: ```sql SELECT search_term, SUM(attributed_conversions_30d) AS orders, ROUND(SUM(cost), 2) AS spend FROM search_term_performance WHERE venue_id = {venue_id} AND date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY) AND attributed_conversions_30d >= 2 GROUP BY search_term ORDER BY orders DESC LIMIT 100 ``` Scan the results for brand-like terms (proper nouns, multi-word capitalized patterns) that do NOT appear in the seller's own brand list. These are likely competitor brand terms the seller is already capturing organically -- surface the top 3 as competitor candidates. --- ### Step 1b: Infer all parameters from query results Using the data from Step 1a, set each parameter using the rules below. Do not ask the seller about any of these -- only surface them in the confirmation block. **`{venue_website}`** -- from venues query result. **`{gross_margin_pct}`** -- use `margin_after_amazon_fees_pct` from the profit query (revenue-weighted margin after FBA + referral fees, before ad spend = the break-even basis). Surface the other two margins (`gross_margin_cogs_only_pct`, `net_profit_margin_pct`) in the confirmation block for context so the seller can sanity-check, but the target ACoS is driven off `margin_after_amazon_fees_pct`. If that value looks implausibly low/high, note it and let the seller correct row 1. **`{target_acos}`** -- `{gross_margin_pct}` minus a 5-10 point buffer (default 8). Round to nearest whole number. This is break-even-minus-buffer; a seller pursuing growth/organic-rank may deliberately set it higher (closer to break-even or above), a profit-focused seller lower. Treat it as a default the seller can override in the confirmation block. **`{lookback_days}`** -- 60. Always. Never ask. **`{attribution_window}`** -- 30. Always default to 30-day. Only change if the seller explicitly volunteers that their products are impulse purchases (typically under $30, no research required). Never ask. **`{category_targets}`** -- "single blended". Default. Only create per-category targets if the seller volunteers significantly different margin profiles unprompted. Never ask. **`{min_cover_days}`** -- the minimum FBA days of cover an ASIN must have before the audit will recommend putting MORE spend behind it (budget increase, bid increase, placement modifier increase, harvesting into a new campaign, or any new campaign launch). See Step 2e. It is one number: total days of FBA stock on hand at the current sales rate. **Default 14.** Every seller restocks differently, so it comes from the saved settings file when one exists, and the seller can change it in the confirmation block (a seller with slow replenishment might set 28 or more). **`{cover_gate_mode}`** -- how the days-of-cover check is applied. Default **block**. - **block** -- scale-side recommendations on ASINs below `{min_cover_days}` are held back and listed as [INVENTORY-GATED] (Step 2e) - **warn** -- the recommendations are still made, each carrying a [LOW COVER] flag with the ASIN's days of cover. Suits agencies that do not control reordering - **off** -- no days-of-cover check **Saved settings** -- all of the above, plus any other value the seller corrects in the confirmation block, are saved to `ad-audit-settings.md` in the seller's working folder after confirmation (see Step 1c) and pre-filled on the next run, so each seller or agency sets its own rules once. **Seller type** -- infer from brand list using these rules: - Brand list has 1-3 brands that plausibly match the seller's company name or read as private labels → **Brand owner** - Brand list has 5+ distinct manufacturer brands (e.g., Husqvarna, Sony, Bosch, Black+Decker) → **Third-party reseller** - Brand list has 1-2 apparent house brands AND 5+ manufacturer brands → **Mixed: brand owner + reseller** (note which brands are owned) - State your inference explicitly in the confirmation block with a one-line explanation **`{brand_name}`** (for Section 6b branded/non-branded split): - Brand owner: the seller's primary owned brand - Pure reseller: set to "reseller -- Section 6b will show manufacturer brand patterns" - Mixed: the seller's primary owned brand; note the reseller component separately **`{competitor_1}`, `{competitor_2}`, `{competitor_3}`** -- propose from two sources: 1. Brand-like terms found in the search term query (Step 1a) that aren't the seller's brands 2. Claude's knowledge of the competitive landscape for the inferred product category (from the brand list -- e.g., outdoor power equipment brands → parts aggregator competitors) Propose the 3 strongest candidates. These are proposals, not confirmed values -- the seller will confirm or swap in Step 1c. --- ### Step 1c: Present the pre-filled confirmation block Present the block below with all values filled in from Steps 1a-1b. This is the only thing the seller needs to respond to. Do not ask individual questions before or after this block. --- I've pulled your account data and set up the audit parameters automatically. | # | Parameter | Proposed Value | Basis | |---|---|---|---| | 1 | Margin (after Amazon fees, before ad spend) | {gross_margin_pct}% | Revenue-weighted from your profit data (last 30 days, {sku_count} SKUs). This is your ad break-even margin. | | 2 | Target ACoS | {target_acos}% | Row 1 ({gross_margin_pct}%) minus an 8-point buffer | | 3 | Seller type | [Brand owner / Third-party reseller / Mixed] | [one-line inference from brand list] | | 4 | Brand (Section 6b) | [brand name or "reseller"] | [inferred] | | 5 | Lookback window | 60 days | Standard default | | 6 | Attribution window | 30-day | Standard default (considered-purchase products) | | 7 | Category ACoS targets | Single blended | Default | | 8 | Data range | {earliest} to {latest} | From your campaign data | | 9 | Data freshness | [OK ✓ / ⚠️ STALE -- {N} days behind] | From data freshness check | | 10 | **Competitor brands** | **{competitor_1}, {competitor_2}, {competitor_3}?** | **Proposed -- confirm or swap** | | 11 | **Minimum days of cover before scaling** | **{min_cover_days} days** | **[From your saved settings / Default -- no scale-up advice on an ASIN with less than this many days of stock at Amazon. Raise it if restocking takes you longer]** | | 12 | Days-of-cover check | {cover_gate_mode} | block = hold back scale-up recommendations on low-cover ASINs; warn = recommend them with a low-cover flag; off = no check | | 13 | Live star ratings (Section 12) | [Use existing RankGenius key / Create free RankGenius trial key] | Data Hub has no lifetime star ratings. A free trial key needs no email and covers the ~20 lookups this audit uses. Reply "no ratings" to skip -- the audit still runs the conversion-rate check | > **On row 1 (margin)**: I'm using your revenue-weighted margin after Amazon fees (FBA + referral) but before ad spend = {gross_margin_pct}%, across {sku_count} SKUs over the last 30 days. For context, your gross margin (COGS only) is {gross_margin_cogs_only_pct}% and your fully-loaded net profit margin is {net_profit_margin_pct}%. I use the after-fees figure because that is the money available to spend on ads before a unit goes negative -- the true break-even. If your true blended margin is significantly different, correct row 1 -- it drives the target ACoS for every section that follows. > **On row 10 (competitor brands)**: {competitor_1} and {competitor_2} appear directly in your top-converting search terms. {competitor_3} is proposed based on your product category. If these aren't the right competitors, name the ones that matter and I'll swap them. **Reply "proceed" to start the audit immediately, or name a row number to correct it.** --- **After seller responds:** - **"proceed" or any positive confirmation**: lock in all values as proposed, move to Section 2 - **Correction to one or more items**: update those items, restate only the corrected rows, confirm, move to Section 2 -- do not re-ask the full block - **Seller provides different competitor names**: replace proposed candidates, note it, proceed - **Seller says they don't know their margin**: use {gross_margin_pct} from the auto-query with a note, or if that query failed, use 30% as a placeholder and flag it prominently at the top of the final report **Once confirmed, set all variables and verify column names:** ``` {org_id} = [confirmed] {venue_id} = [confirmed] {gross_margin_pct} = [confirmed or auto-calculated] {target_acos} = [confirmed or derived] {brand_name} = [confirmed or inferred] {lookback_days} = 60 {venue_website} = [from venues query] {competitor_1}, {competitor_2}, {competitor_3} = [confirmed or proposed] {attribution_window} = 30 {category_targets} = single blended (or per-category if volunteered) {min_cover_days} = 14 (or saved setting / seller override) {cover_gate_mode} = block (or warn / off) ``` **Save the confirmed settings.** Write (or update) `ad-audit-settings.md` in the current working folder, one section per venue so an agency can keep several accounts in one file: ```markdown ## org {org_id} / venue {venue_id} - min_cover_days: 14 - cover_gate_mode: block - target_acos_buffer_points: 8 - competitors: - updated: YYYY-MM-DD ``` Store only settings, never API keys or other secrets. Tell the seller the file was saved and that editing it changes the next run. **Text values go inside SQL quotes** (`{brand_name}`, `{competitor_1}`-`{competitor_3}`, and the ad-type values): double every apostrophe before substituting it, e.g. `Dr. Brown's` becomes `Dr. Brown''s`. Otherwise the query breaks. Run `DESCRIBE campaign_performance` to verify column names before writing queries in subsequent sections. The campaign name column is `name`, not `campaign_name`. Then check which ad types `campaign_performance` holds and how they are labelled -- the stored values are Amazon API names (e.g. `sponsoredProducts`, `sponsoredDisplay`), not `SP`/`SB`/`SD`: ```sql SELECT campaign_type, COUNT(DISTINCT campaign_id) AS campaigns FROM campaign_performance WHERE venue_id = {venue_id} AND date >= DATE_SUB(CURDATE(), INTERVAL {lookback_days} DAY) GROUP BY campaign_type ``` Record the exact values as `{sp_type}`, `{sb_type}`, `{sd_type}` (leave blank for any type the account does not run). Every later query that filters on ad type uses these values. **Output audit parameters to the top of the final report** as this block: ``` **Audit Parameters** | Parameter | Value | |---|---| | Seller name | | | Org ID | | | Venue ID | | | Gross margin | % | | Target ACoS | % | | Brand name(s) used for Section 6b | | | Seller type | Brand owner / Third-party reseller / Mixed | | Lookback window | days | | Data range | [earliest] to [latest] | | Data freshness flag | OK / STALE (> 3 days) | | Attribution window | 30-day | | Per-category ACoS targets | Single blended / [list if set] | | Competitor brands for Section 7d | [list] | | Minimum days of cover before scaling | days (default 14 / saved setting / seller-set) | | Days-of-cover check mode | block / warn / off | ``` --- ## SECTION 2: Out-of-Stock Campaign Scan **What this shows**: Campaigns actively spending money right now against products that have no FBA inventory. Amazon does NOT automatically pause Sponsored Products ads when you run out of stock -- the ad system and inventory system are completely separate. Every dollar spent in this state is waste with zero chance of conversion. > **Data reliability warning**: `fba_inventory` is populated from Amazon's `GET_FBA_MYI_UNSUPPRESSED_INVENTORY_DATA` SP-API report (the "Manage FBA Inventory" feed). That feed can go stale on individual SKUs for 7+ days -- showing `afn_fulfillable_quantity = 0` while the warehouse actually has stock. We have seen a SKU show 0 fulfillable for a week straight while `inventory_ledger_history` showed dozens of SELLABLE units with customer shipments every day. You cannot ship units that do not exist, so the ledger is the truth when the two disagree. Data Hub faithfully ingests what Amazon sends; the staleness is Amazon-side. **Never recommend pausing a campaign from `afn_fulfillable_quantity` alone.** Every OOS candidate must pass the Step 2b verification. **Step 2a -- Identify OOS candidate ASINs**: > **Multi-SKU note**: An ASIN with multiple SKUs (different colors, sizes, conditions) will have one row per SKU in `fba_inventory`. Query aggregates totals across all SKUs for each ASIN to avoid false OOS flags on ASINs where at least one SKU has stock. > **Snapshot note**: Run `DESCRIBE fba_inventory` first. If the table has a snapshot date column (e.g., `report_date`) with multiple dates per SKU, add a filter to the latest date so you read the current snapshot only -- summing across historical snapshots inflates quantities. ```sql SELECT asin, SUM(afn_fulfillable_quantity) AS total_fulfillable, SUM(afn_warehouse_quantity) AS total_warehouse, SUM(afn_inbound_shipped_quantity + afn_inbound_receiving_quantity) AS total_inbound FROM fba_inventory WHERE venue_id = {venue_id} GROUP BY asin HAVING total_fulfillable = 0 AND total_inbound = 0 ORDER BY asin ``` Results are **candidates only** -- do not flag anything as OOS yet. **Step 2b -- Verify each candidate with 2-of-3 signal agreement**: An ASIN is **CONFIRMED OOS** only if at least 2 of these 3 independent signals agree it is at zero: 1. `fba_inventory.afn_fulfillable_quantity` (already 0 from Step 2a) 2. `fba_inventory.afn_warehouse_quantity` -- includes reserved units, which are still sellable inventory about to ship 3. `inventory_ledger_history.ending_warehouse_balance` WHERE `disposition = 'SELLABLE'` for the most recent date -- sourced from Amazon's separate Inventory Ledger Detail report, so it fails independently of the Manage FBA Inventory feed ```sql SELECT fi.asin, SUM(fi.afn_fulfillable_quantity) AS fba_fulfillable, SUM(fi.afn_warehouse_quantity) AS fba_warehouse, (SELECT ending_warehouse_balance FROM inventory_ledger_history WHERE asin = fi.asin AND venue_id = {venue_id} AND disposition = 'SELLABLE' ORDER BY date DESC LIMIT 1) AS ledger_sellable FROM fba_inventory fi WHERE fi.venue_id = {venue_id} AND fi.asin IN ({oos_candidate_asins}) GROUP BY fi.asin ``` Then apply two guards. Either guard triggering overrides the signals and blocks any OOS action for that ASIN: **Guard 1 -- Feed staleness check**: if `afn_inbound_working_quantity` is > 0 and has not changed in 5+ days, the Manage FBA Inventory feed is likely stuck for that SKU (inbound normally cycles working → shipped → receiving → fulfillable within days). Treat the snapshot as suspect and skip OOS actions. *Requires snapshot history in `fba_inventory`.* Check first -- many accounts store only one snapshot date: ```sql SELECT COUNT(DISTINCT report_date) AS snapshot_dates, MIN(report_date) AS oldest, MAX(report_date) AS newest FROM fba_inventory WHERE venue_id = {venue_id} ``` If `snapshot_dates` < 5, Guard 1 cannot run: say "Guard 1 skipped -- single inventory snapshot" in the report and rely on the 2-of-3 signal check plus Guard 2. Do not treat a skipped Guard 1 as a pass. When there IS history, every other `fba_inventory` query in this audit must filter to `report_date = (SELECT MAX(report_date) FROM fba_inventory WHERE venue_id = {venue_id})` so snapshots are not summed together. ```sql SELECT listing_sku, COUNT(DISTINCT afn_inbound_working_quantity) AS distinct_inbound_vals, MAX(afn_inbound_working_quantity) AS inbound_working FROM fba_inventory WHERE venue_id = {venue_id} AND asin = '{candidate_asin}' AND report_date >= DATE_SUB(CURDATE(), INTERVAL 5 DAY) GROUP BY listing_sku HAVING distinct_inbound_vals = 1 AND inbound_working > 0 ``` **Guard 2 -- Sales sanity check**: if the ledger shows customer shipments in the last 48 hours, the SKU clearly has stock. Do not pause. (`customer_shipments` is negative for outbound units -- use `ABS()`.) ```sql SELECT asin, SUM(ABS(customer_shipments)) AS units_shipped_48h FROM inventory_ledger_history WHERE venue_id = {venue_id} AND asin IN ({oos_candidate_asins}) AND customer_shipments < 0 AND date >= DATE_SUB(CURDATE(), INTERVAL 2 DAY) GROUP BY asin HAVING units_shipped_48h > 0 ``` Classify every Step 2a candidate as one of: - **CONFIRMED OOS** -- 2+ signals at zero, no guard triggered → eligible for PAUSE in Step 2c - **INVENTORY DATA SUSPECT** -- signals disagree, or a guard triggered → do NOT pause; report the conflicting values and direct the seller to verify in Seller Central (Manage FBA Inventory) **Step 2c -- Cross-reference CONFIRMED OOS ASINs with active campaigns**: ```sql SELECT pa.campaign_name, pa.asin, ROUND(SUM(pa.cost), 2) AS spend_14d, SUM(pa.impressions) AS impressions_14d FROM product_ad_performance pa WHERE pa.venue_id = {venue_id} AND pa.date >= DATE_SUB(CURDATE(), INTERVAL 14 DAY) AND pa.asin IN ({confirmed_oos_asins}) GROUP BY pa.campaign_name, pa.asin HAVING spend_14d > 0 ORDER BY spend_14d DESC ``` > **WARNING**: Amazon does NOT auto-pause Sponsored Products ads on stockout. Every campaign on this list is burning budget with no chance of conversion. **Output to report**: - List of campaigns spending money on CONFIRMED OOS ASINs (verified 2-of-3) - List of INVENTORY DATA SUSPECT ASINs with their conflicting signal values -- explicitly NOT recommended for pause - Total wasted spend over the past 14 days (confirmed ASINs only) - Recommended action: **PAUSE immediately** (confirmed only) -- then reactivate when inventory is confirmed received at Amazon **Step 2d -- Check for paused campaigns now back in stock**: ```sql SELECT c.name AS campaign_name, c.campaign_id, pa.asin, SUM(fi.afn_fulfillable_quantity) AS fulfillable_qty, SUM(fi.afn_inbound_shipped_quantity + fi.afn_inbound_receiving_quantity) AS inbound_qty FROM campaigns c JOIN product_ads pa ON c.campaign_id = pa.campaign_id AND pa.venue_id = {venue_id} JOIN fba_inventory fi ON pa.asin = fi.asin AND fi.venue_id = {venue_id} WHERE c.venue_id = {venue_id} AND c.state = 'paused' GROUP BY c.campaign_id, c.name, pa.asin HAVING fulfillable_qty > 0 OR inbound_qty > 0 ORDER BY fulfillable_qty DESC ``` > **Note**: Confirm which campaigns were OOS-paused vs. paused for performance reasons before reactivating. Before recommending reactivation, cross-check the ledger the same way as Step 2b -- `inventory_ledger_history` SELLABLE balance should agree that stock is genuinely positive (the same stale feed that fakes a stockout can fake a restock). **Output to report**: - List of paused campaigns on ASINs that now have inventory - Flag each with fulfillable qty and inbound qty - Recommended action: **REACTIVATE** campaigns confirmed as OOS-paused only **Step 2e -- Days-of-cover map (the scale-side inventory gate)**: > **Why this exists.** Steps 2a-2d guard the PAUSE side: never stop spend on stock that is really there. The same risk exists in reverse. Sections 9, 10, 7 and 11 recommend budget increases, bid and placement increases, harvesting into new campaigns, and new campaign launches. Following those on an ASIN with three weeks of stock pours more spend onto a product that stocks out inside a month -- the extra sales accelerate the stockout, and the account then pays to rebuild rank. **No budget increase, bid increase, placement modifier increase, harvest into a new campaign, or new campaign launch may be recommended on an ASIN below `{min_cover_days}` days of FBA cover.** Build the map once here and reuse it in every later section: ```sql SELECT inv.asin, inv.fulfillable, inv.inbound, ROUND(v.avg_daily_units_30d, 2) AS avg_daily_units_30d, ROUND(inv.fulfillable / NULLIF(v.avg_daily_units_30d, 0), 0) AS days_cover_on_hand, ROUND((inv.fulfillable + inv.inbound) / NULLIF(v.avg_daily_units_30d, 0), 0) AS days_cover_with_inbound FROM ( SELECT asin, SUM(afn_fulfillable_quantity) AS fulfillable, SUM(afn_inbound_shipped_quantity + afn_inbound_receiving_quantity) AS inbound FROM fba_inventory WHERE venue_id = {venue_id} AND report_date = (SELECT MAX(report_date) FROM fba_inventory WHERE venue_id = {venue_id}) GROUP BY asin ) inv JOIN ( SELECT asin, SUM(ABS(customer_shipments)) / 30.0 AS avg_daily_units_30d FROM inventory_ledger_history WHERE venue_id = {venue_id} AND customer_shipments < 0 AND date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY) GROUP BY asin ) v ON inv.asin = v.asin ORDER BY days_cover_on_hand ASC ``` Rules: - Velocity comes from the inventory ledger (actual units shipped), not from `sl_daily_sku_profit`, which under-reads velocity on recently connected accounts. - Gate on `days_cover_on_hand`. Inbound is shown for context only -- inbound units are not sellable until received, and receiving can take weeks. - If the ASIN was classified **INVENTORY DATA SUSPECT** in Step 2b, the fulfillable figure is unreliable: recompute cover from the ledger SELLABLE balance, and if the two still disagree, treat the ASIN as gated. - Map campaigns to ASINs through `product_ad_performance`. A campaign is **INVENTORY-GATED** if any ASIN carrying 25%+ of that campaign's ad spend in the lookback window is below `{min_cover_days}`. - An increase recommendation that fails the gate is not dropped silently. Report it as **[INVENTORY-GATED]** with the ASIN, its days of cover, and "revisit when cover is back above {min_cover_days} days or the inbound shipment is received". The seller should see what the audit would have scaled. - **Apply `{cover_gate_mode}`** everywhere this audit says "apply the Step 2e gate": in **block** mode, follow the rules above. In **warn** mode, make the recommendation anyway and tag it **[LOW COVER -- X days]**. In **off** mode, skip the check, and say once in the report that the days-of-cover check was turned off. - If `{min_cover_days}` is the 14-day default rather than a saved or seller-set value, say so once next to the first gated or flagged item, so the seller knows they can change it. **Output to report**: - ASINs with active ad spend below `{min_cover_days}` days of cover (on hand), with velocity, inbound, and cover including inbound - Count of scale-side recommendations blocked by the gate (filled in from Sections 7, 9, 10, 11) --- ## SECTION 3: ACoS Health Dashboard + Power Ratio + Attribution Analysis **What this shows**: How every active campaign is performing against your target ACoS and ROAS, a single account-level metric (ACoS Power Ratio) that reveals how much of your total spend is actually driving sales vs. generating zero conversions, and an attribution window comparison that shows whether your 30-day ACoS is inflated by late conversions. > **Ad-type coverage check**: Use the `campaign_type` values recorded in Section 1. On most accounts `campaign_performance` holds SP, SB and SD together, and no separate SB/SD tables exist -- that is normal, not a gap. Only if SB or SD campaigns are known to run but are missing from `campaign_type`, run `SHOW TABLES` and look for separate tables (e.g. `sb_campaign_performance`, `sd_campaign_performance`); if found, run the equivalent queries against them. Either way, compare the campaign count from your queries against Seller Central's Campaign Manager to catch any gap. **Step 3a -- Campaign ACoS + ROAS dashboard**: ```sql SELECT name, campaign_type, SUM(impressions) AS impressions, SUM(clicks) AS clicks, ROUND(SUM(cost), 2) AS spend, ROUND(SUM(attributed_sales_30d), 2) AS sales, ROUND(SUM(cost) / NULLIF(SUM(attributed_sales_30d), 0) * 100, 1) AS acos_pct, ROUND(SUM(attributed_sales_30d) / NULLIF(SUM(cost), 0), 2) AS roas, ROUND(SUM(clicks) / NULLIF(SUM(impressions), 0) * 100, 2) AS ctr_pct, ROUND(SUM(cost) / NULLIF(SUM(clicks), 0), 2) AS cpc, SUM(attributed_conversions_30d) AS orders, CASE WHEN SUM(attributed_conversions_30d) = 0 THEN 'NO SALES' WHEN ROUND(SUM(cost) / NULLIF(SUM(attributed_sales_30d), 0) * 100, 1) > {target_acos} THEN 'OVER TARGET' ELSE 'HEALTHY' END AS status FROM campaign_performance WHERE venue_id = {venue_id} AND date >= DATE_SUB(CURDATE(), INTERVAL {lookback_days} DAY) GROUP BY name, campaign_type HAVING spend > 0 ORDER BY spend DESC ``` > **ROAS reference**: ROAS = attributed sales / ad spend. A target ACoS of 25% corresponds to a target ROAS of 4.0x. Count the HEALTHY / OVER TARGET / NO SALES campaigns and note the totals. Any campaign showing NO SALES needs immediate investigation (see Section 5). **Step 3b -- ACoS Power Ratio** (source: Michael Erickson Facchin): Power Ratio = Account ACoS / Converting-Only ACoS. Healthy: 1.3-1.5x. Above 2.0x: action required. ```sql SELECT ROUND(SUM(cost), 2) AS total_spend, ROUND(SUM(CASE WHEN attributed_conversions_30d > 0 THEN cost ELSE 0 END), 2) AS converting_spend, ROUND(SUM(CASE WHEN attributed_conversions_30d = 0 THEN cost ELSE 0 END), 2) AS wasted_spend, ROUND(SUM(CASE WHEN attributed_conversions_30d = 0 THEN cost ELSE 0 END) / NULLIF(SUM(cost), 0) * 100, 1) AS waste_pct, ROUND(SUM(cost) / NULLIF(SUM(attributed_sales_30d), 0) * 100, 1) AS account_acos, ROUND(SUM(CASE WHEN attributed_conversions_30d > 0 THEN cost ELSE 0 END) / NULLIF(SUM(attributed_sales_30d), 0) * 100, 1) AS converting_only_acos, ROUND( (SUM(cost) / NULLIF(SUM(attributed_sales_30d), 0)) / NULLIF(SUM(CASE WHEN attributed_conversions_30d > 0 THEN cost ELSE 0 END) / NULLIF(SUM(attributed_sales_30d), 0), 0) , 2) AS power_ratio FROM campaign_performance WHERE venue_id = {venue_id} AND date >= DATE_SUB(CURDATE(), INTERVAL {lookback_days} DAY) ``` Power Ratio thresholds: - 1.0-1.5 = healthy (normal discovery overhead) - 1.5-2.0 = caution (review search terms in Section 8) - > 2.0 = action required (aggressive negative keyword cleanup needed) **Step 3c -- Attribution window comparison**: The `late_attr_pct` column shows what % of 30-day attributed sales came from clicks 8-30 days ago. High late attribution (> 30%) means 30-day ACoS is materially understating true ad cost. First run `DESCRIBE campaign_performance` to confirm `attributed_sales_7d` exists. Skip if absent. ```sql SELECT name, ROUND(SUM(cost), 2) AS spend, ROUND(SUM(cost) / NULLIF(SUM(attributed_sales_7d), 0) * 100, 1) AS acos_7day, ROUND(SUM(cost) / NULLIF(SUM(attributed_sales_30d), 0) * 100, 1) AS acos_30day, ROUND( (SUM(attributed_sales_30d) - SUM(attributed_sales_7d)) / NULLIF(SUM(attributed_sales_30d), 0) * 100, 1 ) AS late_attr_pct FROM campaign_performance WHERE venue_id = {venue_id} AND date >= DATE_SUB(CURDATE(), INTERVAL {lookback_days} DAY) GROUP BY name HAVING spend > 10 ORDER BY late_attr_pct DESC LIMIT 20 ``` Interpretation: - `late_attr_pct` < 15% = window has minimal impact; 30-day and 7-day ACoS are close - `late_attr_pct` 15-30% = moderate late attribution; flag campaigns where 7-day ACoS > target even if 30-day looks healthy - `late_attr_pct` > 30% = significant late attribution; treat 7-day ACoS as the primary metric for bid decisions on these campaigns **Step 3d -- Category ACoS breakdown** *(run if {category_targets} were defined, or if seller has diverse product categories)*: ```sql SELECT COALESCE(l.item_type_keyword, 'Unknown Category') AS product_category, COUNT(DISTINCT p.campaign_name) AS campaigns, ROUND(SUM(p.cost), 2) AS spend, ROUND(SUM(p.attributed_sales_30d), 2) AS sales, ROUND(SUM(p.cost) / NULLIF(SUM(p.attributed_sales_30d), 0) * 100, 1) AS acos_pct, ROUND(SUM(p.attributed_sales_30d) / NULLIF(SUM(p.cost), 0), 2) AS roas FROM product_ad_performance p LEFT JOIN listings l ON p.asin = l.asin AND l.venue_id = {venue_id} WHERE p.venue_id = {venue_id} AND p.date >= DATE_SUB(CURDATE(), INTERVAL {lookback_days} DAY) GROUP BY l.item_type_keyword HAVING spend > 0 ORDER BY spend DESC ``` Compare each category's `acos_pct` against the target for that category (from `{category_targets}` if set, or `{target_acos}` if not). Flag any category exceeding its target. **Step 3e -- Secondary target for efficient accounts**: > **Why this exists.** `{target_acos}` is break-even minus a buffer, which is the right ceiling for "is this campaign losing money". On a tightly run account the actual ACoS can sit far below it (e.g. 12% actual against a 27% target), so almost nothing flags OVER TARGET and the dashboard returns an empty exception list on exactly the account where an operator most wants signal. Step 3e adds a relative target: flag campaigns that are profitable but clearly worse than the account's own norm, or getting worse. Run this step when account ACoS (Step 3b) is below 60% of `{target_acos}`. Otherwise skip it and say so. 1. **Account-norm target (percentile)**: from the Step 3a results, take converting campaigns with spend >= $50 and compute the **spend-weighted 75th percentile** of campaign ACoS. Call it `{norm_acos}`. Any campaign with ACoS above `{norm_acos}` but at or below `{target_acos}` gets status **ABOVE ACCOUNT NORM**. If fewer than 10 campaigns qualify, use `1.5 x account ACoS` instead and say so. 2. **Trend drift**: compare each campaign's ACoS in the most recent 30 days against the prior 30 days: ```sql SELECT name, ROUND(SUM(CASE WHEN date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY) THEN cost ELSE 0 END), 2) AS spend_recent, ROUND(SUM(CASE WHEN date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY) THEN cost ELSE 0 END) / NULLIF(SUM(CASE WHEN date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY) THEN attributed_sales_30d ELSE 0 END), 0) * 100, 1) AS acos_recent, ROUND(SUM(CASE WHEN date < DATE_SUB(CURDATE(), INTERVAL 30 DAY) THEN cost ELSE 0 END) / NULLIF(SUM(CASE WHEN date < DATE_SUB(CURDATE(), INTERVAL 30 DAY) THEN attributed_sales_30d ELSE 0 END), 0) * 100, 1) AS acos_prior FROM campaign_performance WHERE venue_id = {venue_id} AND date >= DATE_SUB(CURDATE(), INTERVAL 60 DAY) GROUP BY name HAVING spend_recent >= 50 AND acos_prior > 0 ORDER BY (acos_recent - acos_prior) DESC LIMIT 20 ``` Flag **[ACOS DRIFTING]** where `acos_recent` is 30%+ higher than `acos_prior` (relative, e.g. 10% → 13%+). Recent-window attributed sales are still accruing for the last few days, so ignore drift under 30% and say which campaigns were borderline. These flags feed bid-review recommendations, never pauses: a campaign ABOVE ACCOUNT NORM is still profitable. **Output to report**: - Table of all campaigns with status (HEALTHY / ABOVE ACCOUNT NORM / OVER TARGET / NO SALES), including ACoS and ROAS - `{norm_acos}` and how it was derived (percentile or 1.5x fallback), or "Step 3e skipped -- account ACoS near target" - Campaigns flagged [ACOS DRIFTING] with recent vs prior ACoS - Account-level Power Ratio with threshold flag - Count and total spend for each status category - Top 5 campaigns by late attribution % with 7-day vs 30-day ACoS side by side - Category ACoS breakdown (if Step 3d was run) --- ## SECTION 4: TACoS Trend **What this shows**: Total Advertising Cost of Sales (TACoS) = total ad spend / total revenue, read together with the direction of revenue and the share of revenue that ads are carrying. TACoS alone is ambiguous: a falling TACoS can mean organic sales are growing, or it can mean ad spend is being cut faster than revenue is falling. The revenue direction and ad-attributed share tell the two apart. > **Query notes.** Use `sl_daily_venue_profit` (one row per venue per day) for total revenue. Do not join `campaign_performance` to `sl_daily_sku_profit` on date: both have many rows per date, so the join multiplies spend and revenue, and grouped aggregates over long windows on `sl_daily_sku_profit` can fail outright. Aggregate each side to month first, then join. The trend uses 6 full months plus the current month -- the 60-day lookback is too short to show a trend. Run `DESCRIBE sl_daily_venue_profit` first; the total-revenue column is `sales_api_total_sales`. ```sql SELECT r.month, ROUND(r.total_revenue, 2) AS total_revenue, ROUND(a.ad_spend, 2) AS ad_spend, ROUND(a.ad_sales, 2) AS ad_sales, ROUND(a.ad_spend / NULLIF(r.total_revenue, 0) * 100, 1) AS tacos_pct, ROUND(a.ad_spend / NULLIF(a.ad_sales, 0) * 100, 1) AS acos_pct, ROUND(a.ad_sales / NULLIF(r.total_revenue, 0) * 100, 1) AS ad_share_pct, r.days_in_data FROM ( SELECT DATE_FORMAT(date, '%Y-%m') AS month, SUM(sales_api_total_sales) AS total_revenue, COUNT(DISTINCT date) AS days_in_data FROM sl_daily_venue_profit WHERE venue_id = {venue_id} AND date >= DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 6 MONTH), '%Y-%m-01') GROUP BY month ) r LEFT JOIN ( SELECT DATE_FORMAT(date, '%Y-%m') AS month, SUM(cost) AS ad_spend, SUM(attributed_sales_30d) AS ad_sales FROM campaign_performance WHERE venue_id = {venue_id} AND date >= DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 6 MONTH), '%Y-%m-01') GROUP BY month ) a ON r.month = a.month ORDER BY r.month ASC ``` If `sl_daily_venue_profit` does not exist on the account, fall back to `sl_daily_sku_profit` aggregated to month in its own subquery (same pattern), and narrow the window if it errors. Drop the first month if `days_in_data` shows it is partial. Treat the current month as partial and its ad sales as still accruing (30-day attribution). **Step 4a -- Revenue direction**: compare the average monthly revenue of the most recent 2 full months against the first 2 full months in the table. Revenue **GROWING** = up 10%+; **DECLINING** = down 10%+; otherwise **FLAT**. **Step 4b -- Read TACoS against revenue direction** (this matrix overrides the raw TACoS bands): | TACoS trend | Revenue | Reading | Flag | |---|---|---|---| | Falling | Growing | Organic sales growing faster than ads -- the healthy pattern | none | | Falling | Declining | Ad spend cut faster than revenue fell. NOT organic strength | **[SPEND-CUT DECLINE]** | | Rising | Flat or declining | Ads replacing lost organic sales -- organic rank eroding | **[ORGANIC EROSION]** | | Rising | Growing | Paid-led growth; check ad share before calling it healthy | none unless ad share > 50% | | Flat | Declining | Spend and revenue shrinking together -- check whether ad cuts caused the decline | **[SPEND-CUT DECLINE]** if ad spend fell faster than revenue | **Step 4c -- Ad-attributed share of revenue** (`ad_share_pct`, latest full month): - < 30% = organic-led; ads are supplementing - 30-50% = balanced but ad-reliant - > 50% = **[AD-DEPENDENT]** -- ads are carrying most of the revenue; cutting spend will cut revenue, and a low TACoS does NOT mean strong organic rank Ad-attributed sales include halo sales of other ASINs and some double counting across ad types, so treat ad share as directional, not exact. Raw TACoS bands (< 10% strong, 10-20% healthy, > 20% heavy) apply ONLY when revenue is FLAT or GROWING and ad share is under 50%. When revenue is DECLINING or ad share is over 50%, do not describe a low TACoS as strong organic rank. **Output to report**: - Month-by-month table: revenue, ad spend, ad sales, TACoS, ACoS, ad share - Revenue direction (GROWING / FLAT / DECLINING) with the % change - The Step 4b reading and flag, if any - Ad-attributed share with its band - If [AD-DEPENDENT] or [SPEND-CUT DECLINE]: warn that further spend cuts are likely to cut revenue, and prioritize organic rank work (listings, reviews, pricing) alongside any efficiency changes --- ## SECTION 5: Zero-Sale Campaigns **What this shows**: Campaigns with ad spend but zero attributed orders over the full lookback window. These have inventory available but are generating no sales -- the clearest waste in the account. ```sql SELECT name, campaign_type, ROUND(SUM(cost), 2) AS total_spend, SUM(clicks) AS total_clicks, SUM(impressions) AS total_impressions, ROUND(SUM(clicks) / NULLIF(SUM(impressions), 0) * 100, 2) AS ctr_pct, SUM(attributed_conversions_30d) AS total_orders FROM campaign_performance WHERE venue_id = {venue_id} AND date >= DATE_SUB(CURDATE(), INTERVAL {lookback_days} DAY) GROUP BY name, campaign_type HAVING total_orders = 0 AND total_spend > 0 ORDER BY total_spend DESC ``` **Output to report**: - List of campaigns with zero orders sorted by spend (highest waste first) - Total wasted spend across all zero-sale campaigns - For each: check listing quality (Section 12) before pausing -- a great campaign targeting a broken listing will always fail - Recommended action: PAUSE or, if the listing is healthy, investigate keyword relevance and bid levels --- ## SECTION 6: Campaign Structure Audit **What this shows**: Five structural checks -- campaign type performance breakdown, branded vs. non-branded keyword split, duplicate exact-match keywords (the real cannibalization risk), product mixing (campaigns with too many disparate ASINs), and full-funnel format coverage (whether the account uses Sponsored Brands, Sponsored Brands Video, and Sponsored Display). **Step 6a -- Campaign type breakdown**: ```sql SELECT CASE WHEN campaign_type = '{sb_type}' OR name LIKE 'SB -%' OR name LIKE 'SB-%' THEN 'Sponsored Brand' WHEN campaign_type = '{sd_type}' OR name LIKE 'SD -%' OR name LIKE 'SD-%' THEN 'Sponsored Display' WHEN targeting_type = 'AUTO' OR name LIKE '%Auto%' THEN 'SP Auto' WHEN name LIKE '% PAT %' OR name LIKE '%- PAT -%' OR name LIKE '%PAT%' THEN 'SP PAT' WHEN targeting_type = 'MANUAL' OR name LIKE '%Manual%' THEN 'SP Manual' ELSE 'Other' END AS type_bucket, COUNT(DISTINCT name) AS campaign_count, ROUND(SUM(cost), 2) AS total_spend, ROUND(SUM(attributed_sales_30d), 2) AS total_sales, ROUND(SUM(cost) / NULLIF(SUM(attributed_sales_30d), 0) * 100, 1) AS blended_acos FROM campaign_performance WHERE venue_id = {venue_id} AND date >= DATE_SUB(CURDATE(), INTERVAL {lookback_days} DAY) GROUP BY type_bucket ORDER BY total_spend DESC ``` > **Note**: Multiple campaign types per ASIN (Auto + Manual + PAT) is intentional best practice. Auto discovers search terms; manual bids on proven terms; PAT targets competitor product pages. Flag: if SP Auto ACoS is significantly higher than SP Manual, harvest converting search terms from auto campaigns into exact match manual campaigns. **Step 6b -- Branded vs. non-branded split** (source: Michael Erickson Facchin): ```sql SELECT CASE WHEN LOWER(keyword_text) LIKE LOWER(CONCAT('%', '{brand_name}', '%')) THEN 'Branded' ELSE 'Non-Branded' END AS keyword_type, COUNT(DISTINCT campaign_name) AS campaigns, ROUND(SUM(cost), 2) AS spend, ROUND(SUM(attributed_sales_30d), 2) AS sales, ROUND(SUM(cost) / NULLIF(SUM(attributed_sales_30d), 0) * 100, 1) AS acos_pct, SUM(attributed_conversions_30d) AS orders FROM keyword_performance WHERE venue_id = {venue_id} AND date >= DATE_SUB(CURDATE(), INTERVAL {lookback_days} DAY) GROUP BY keyword_type ORDER BY spend DESC ``` Flag: if branded and non-branded keywords appear in the same campaigns, recommend separating them. Show blended, branded-only, and non-branded-only ACoS side by side. > **Reseller note**: If the seller is a third-party reseller, the branded/non-branded split reflects manufacturer brand patterns rather than brand protection. If ACoS figures are nearly identical, note this context rather than flagging it as a structural problem. **Step 6c -- Duplicate exact-match keywords** (true cannibalization): ```sql SELECT keyword_text, COUNT(DISTINCT campaign_name) AS campaign_count, GROUP_CONCAT(DISTINCT campaign_name ORDER BY campaign_name SEPARATOR ' | ') AS campaigns, ROUND(SUM(cost), 2) AS total_spend FROM keyword_performance WHERE venue_id = {venue_id} AND match_type = 'exact' AND current_state = 'ENABLED' AND date >= DATE_SUB(CURDATE(), INTERVAL {lookback_days} DAY) GROUP BY keyword_text HAVING campaign_count > 1 ORDER BY total_spend DESC ``` **Step 6d -- Product mixing check**: Flags campaigns where 5+ ASINs have active spend and the ACoS spread between best- and worst-performing ASIN exceeds 30 percentage points -- a signal that products inside the campaign respond to different keywords and bids. ```sql SELECT campaign_name, COUNT(DISTINCT asin) AS asin_count, ROUND(SUM(total_cost), 2) AS total_spend, ROUND(SUM(total_cost) / NULLIF(SUM(total_sales), 0) * 100, 1) AS blended_acos, ROUND(MIN(asin_acos), 1) AS best_asin_acos, ROUND(MAX(asin_acos), 1) AS worst_asin_acos, ROUND(MAX(asin_acos) - MIN(asin_acos), 1) AS acos_spread_pp FROM ( SELECT campaign_name, asin, SUM(cost) AS total_cost, SUM(attributed_sales_30d) AS total_sales, SUM(cost) / NULLIF(SUM(attributed_sales_30d), 0) * 100 AS asin_acos FROM product_ad_performance WHERE venue_id = {venue_id} AND date >= DATE_SUB(CURDATE(), INTERVAL {lookback_days} DAY) GROUP BY campaign_name, asin HAVING SUM(cost) > 5 AND SUM(attributed_sales_30d) > 0 ) asin_level GROUP BY campaign_name HAVING asin_count >= 5 AND (MAX(asin_acos) - MIN(asin_acos)) > 30 ORDER BY asin_count DESC, total_spend DESC ``` Catch-all discovery campaigns (Section 11a, avg CPC <= $0.50) are intentionally multi-ASIN -- do not flag those. **Step 6e -- Full-funnel format coverage check**: Use the `{sb_type}` / `{sd_type}` values from Section 1. If they are set, run the two queries below against `campaign_performance` with `FROM campaign_performance ... AND campaign_type = '{sb_type}'` (or `'{sd_type}'`) in place of the separate table. Only if SB/SD are missing from `campaign_performance`, run `SHOW TABLES` and use `sb_campaign_performance` / `sd_campaign_performance` if they exist. Flag **NOT AVAILABLE** only when the ad type appears in neither place. **Sponsored Brands + Video** *(written for the separate-table layout; swap in `campaign_performance` + `campaign_type = '{sb_type}'` as above)*: ```sql SELECT name, CASE WHEN name LIKE '%Video%' OR name LIKE '%SBV%' OR name LIKE '%video%' THEN 'SBV' ELSE 'SB Standard' END AS sb_format, SUM(impressions) AS impressions, ROUND(SUM(cost), 2) AS spend, ROUND(SUM(attributed_sales_30d), 2) AS sales, ROUND(SUM(cost) / NULLIF(SUM(attributed_sales_30d), 0) * 100, 1) AS acos_pct FROM sb_campaign_performance WHERE venue_id = {venue_id} AND date >= DATE_SUB(CURDATE(), INTERVAL {lookback_days} DAY) GROUP BY name, sb_format HAVING spend > 0 ORDER BY sb_format, spend DESC ``` **Sponsored Display** *(same swap: `campaign_performance` + `campaign_type = '{sd_type}'`)*: ```sql SELECT name, SUM(impressions) AS impressions, ROUND(SUM(cost), 2) AS spend, ROUND(SUM(attributed_sales_30d), 2) AS sales, ROUND(SUM(cost) / NULLIF(SUM(attributed_sales_30d), 0) * 100, 1) AS acos_pct FROM sd_campaign_performance WHERE venue_id = {venue_id} AND date >= DATE_SUB(CURDATE(), INTERVAL {lookback_days} DAY) GROUP BY name HAVING spend > 0 ORDER BY spend DESC ``` Flag each as: **EXISTS** / **MISSING** / **NOT AVAILABLE** (table absent). For **brand owners**: SB MISSING is high priority -- competitors can capture your branded search traffic at the top of the page while you only run SP below. SBV MISSING is medium priority. For **resellers**: SB typically requires brand registry; SD MISSING is still worth flagging for retargeting. **Output to report**: - Campaign type breakdown with ACoS by type - Branded vs. non-branded ACoS comparison - Duplicate exact-match keywords with spend - Campaigns flagged for product mixing (exclude catch-all campaigns) - Full-funnel format table: SB Standard / SBV / SD -- EXISTS / MISSING / NOT AVAILABLE with spend and ACoS --- ## SECTION 7: Keyword Performance + CPS Threshold + Zero-Impression Fix + Competitor Gap **What this shows**: Which keywords are driving orders, which have exhausted patience without converting, which are starved of impressions, and whether the account has any competitor keyword targeting. **Step 7a -- Keyword performance by campaign**: ```sql SELECT keyword_text, match_type, campaign_name, SUM(impressions) AS impressions, SUM(clicks) AS clicks, SUM(attributed_conversions_30d) AS orders, ROUND(SUM(cost), 2) AS spend, ROUND(SUM(cost) / NULLIF(SUM(attributed_sales_30d), 0) * 100, 1) AS acos_pct, ROUND(SUM(cost) / NULLIF(SUM(clicks), 0), 2) AS cpc FROM keyword_performance WHERE venue_id = {venue_id} AND date >= DATE_SUB(CURDATE(), INTERVAL {lookback_days} DAY) AND current_state = 'ENABLED' GROUP BY keyword_text, match_type, campaign_name HAVING spend > 1 ORDER BY orders DESC, acos_pct ASC LIMIT 50 ``` **Step 7b -- Account-specific Clicks Per Sale threshold** (source: Michael Erickson Facchin): Step 1 -- calculate account CVR and thresholds (query ALL keywords, not just converting): ```sql SELECT ROUND(SUM(attributed_conversions_30d) / NULLIF(SUM(clicks), 0) * 100, 2) AS account_cvr_pct, ROUND(SUM(clicks) / NULLIF(SUM(attributed_conversions_30d), 0), 1) AS avg_clicks_per_sale, ROUND(SUM(clicks) / NULLIF(SUM(attributed_conversions_30d), 0) * 1.5, 0) AS action_threshold_low, ROUND(SUM(clicks) / NULLIF(SUM(attributed_conversions_30d), 0) * 2.5, 0) AS action_threshold_high FROM keyword_performance WHERE venue_id = {venue_id} AND date >= DATE_SUB(CURDATE(), INTERVAL {lookback_days} DAY) ``` Step 2 -- flag over-patient keywords: ```sql SELECT keyword_text, campaign_name, match_type, SUM(clicks) AS clicks, SUM(attributed_conversions_30d) AS orders, ROUND(SUM(cost), 2) AS spend FROM keyword_performance WHERE venue_id = {venue_id} AND date >= DATE_SUB(CURDATE(), INTERVAL {lookback_days} DAY) AND current_state = 'ENABLED' GROUP BY keyword_text, campaign_name, match_type HAVING orders = 0 AND clicks >= {action_threshold_high} ORDER BY spend DESC ``` > **Minimum data guard (recalibrated V3.5)**: Before flagging any keyword, search term, or product target for pause/negation/bid reduction, verify it has enough clicks to be statistically meaningful. The threshold is **CVR-derived, not a flat number**: negate at roughly **2-3x the clicks your account CVR implies for one sale**, with a practical floor of **~20 clicks** (industry standard) and **~15 for low-margin accounts**. Compute it from the account CVR in Step 7b: clicks-per-sale = 1 / CVR, then threshold ≈ 2x that (e.g. CVR 15% → ~7 clicks/sale → negate at ~15-20 clicks). A zero-conversion item with **>$35 spend** also clears the bar regardless of click count. Record the derived number as `{click_threshold}` (Section 8 uses it). Tag anything below it **[INSUFFICIENT DATA]** and advise waiting. (Earlier versions used a flat 50-click minimum; that is the "critical, act immediately" ceiling, not the floor for acting at all, and it wrongly suppressed valid negations on low-margin accounts.) This applies to all sections. **Step 7c -- Zero-impression keyword fix** (source: Ritu Java): ```sql SELECT k.keyword_text, k.match_type, k.campaign_name, SUM(k.impressions) AS total_impressions, SUM(k.clicks) AS total_clicks, ROUND(SUM(k.cost), 2) AS total_spend FROM keyword_performance k WHERE k.venue_id = {venue_id} AND k.date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY) AND k.current_state = 'ENABLED' GROUP BY k.keyword_text, k.match_type, k.campaign_name HAVING total_impressions < 10 ORDER BY k.campaign_name, k.keyword_text ``` **Step 7d -- Competitor keyword gap check**: > **Prerequisite**: `{competitor_1}`, `{competitor_2}`, `{competitor_3}` must be confirmed in Section 1. If the seller did not confirm competitors, skip this step and flag **[COMPETITOR GAP -- BRANDS UNKNOWN]**. Step 1 -- check if competitor keywords already exist in manual campaigns: ```sql SELECT keyword_text, match_type, campaign_name, current_state, SUM(clicks) AS clicks, SUM(attributed_conversions_30d) AS orders, ROUND(SUM(cost), 2) AS spend, ROUND(SUM(cost) / NULLIF(SUM(attributed_sales_30d), 0) * 100, 1) AS acos_pct FROM keyword_performance WHERE venue_id = {venue_id} AND date >= DATE_SUB(CURDATE(), INTERVAL {lookback_days} DAY) AND ( LOWER(keyword_text) LIKE LOWER(CONCAT('%', '{competitor_1}', '%')) OR LOWER(keyword_text) LIKE LOWER(CONCAT('%', '{competitor_2}', '%')) OR LOWER(keyword_text) LIKE LOWER(CONCAT('%', '{competitor_3}', '%')) ) GROUP BY keyword_text, match_type, campaign_name, current_state ORDER BY spend DESC ``` Step 2 -- check search term performance for competitor terms already converting through auto campaigns: ```sql SELECT search_term, campaign_name, SUM(clicks) AS clicks, SUM(attributed_conversions_30d) AS orders, ROUND(SUM(cost), 2) AS spend, ROUND(SUM(cost) / NULLIF(SUM(attributed_sales_30d), 0) * 100, 1) AS acos_pct FROM search_term_performance WHERE venue_id = {venue_id} AND date >= DATE_SUB(CURDATE(), INTERVAL {lookback_days} DAY) AND ( LOWER(search_term) LIKE LOWER(CONCAT('%', '{competitor_1}', '%')) OR LOWER(search_term) LIKE LOWER(CONCAT('%', '{competitor_2}', '%')) OR LOWER(search_term) LIKE LOWER(CONCAT('%', '{competitor_3}', '%')) ) GROUP BY search_term, campaign_name HAVING clicks > 0 ORDER BY orders DESC, spend DESC LIMIT 20 ``` Interpret: - Step 1 has rows: **COMPETITOR TARGETING EXISTS** -- review ACoS; if healthy, these are working - Step 1 empty, Step 2 has rows: competitor terms converting through auto -- **ACTION: harvest into manual exact match** - Both empty: **NO COMPETITOR TARGETING** -- recommend dedicated competitor campaign Harvesting and new competitor campaigns add spend: apply the Step 2e days-of-cover gate to the ASINs they would advertise. > **Note**: Competitor keyword targeting is permitted by Amazon policy. **Output to report**: - Account CVR and CPS action thresholds - Keywords exceeding threshold (pause or negate) - Zero-impression keywords (move to dedicated campaign with fixed bid) - Competitor keyword targeting status: EXISTS / HARVESTING NEEDED / NO COMPETITOR TARGETING --- ## SECTION 8: Search Term Waste + N-Gram Analysis **What this shows**: Exact dollar amounts wasted on search terms with zero orders, plus word-level pattern analysis that surfaces waste clusters invisible to term-by-term review. **Step 8a -- Zero-conversion search terms**: ```sql SELECT search_term, campaign_name, match_type, SUM(clicks) AS total_clicks, ROUND(SUM(cost), 2) AS total_spend, SUM(attributed_conversions_30d) AS total_orders, ROUND(SUM(cost) / NULLIF(SUM(clicks), 0), 2) AS cpc FROM search_term_performance WHERE venue_id = {venue_id} AND date >= DATE_SUB(CURDATE(), INTERVAL {lookback_days} DAY) GROUP BY search_term, campaign_name, match_type HAVING total_orders = 0 AND total_spend > 1.00 ORDER BY total_spend DESC LIMIT 50 ``` **Waste concentration** -- how much of the zero-conversion total is actually actionable term by term: ```sql SELECT CASE WHEN total_spend >= 35 OR total_clicks >= {click_threshold} THEN 'ACTIONABLE' ELSE 'LONG TAIL' END AS tier, COUNT(*) AS terms, ROUND(SUM(total_spend), 2) AS spend, ROUND(AVG(total_spend), 2) AS avg_spend_per_term FROM ( SELECT search_term, campaign_name, SUM(clicks) AS total_clicks, SUM(cost) AS total_spend FROM search_term_performance WHERE venue_id = {venue_id} AND date >= DATE_SUB(CURDATE(), INTERVAL {lookback_days} DAY) GROUP BY search_term, campaign_name HAVING SUM(attributed_conversions_30d) = 0 AND SUM(cost) > 0 ) t GROUP BY tier ``` `{click_threshold}` is the CVR-derived minimum-data threshold from Section 7 (floor ~20, ~15 for low-margin accounts). > **Why this exists.** The total zero-conversion spend usually reads far more actionable than it is. A typical pattern: a few dozen terms carry a meaningful share of the waste, and thousands of terms carry the rest at a dollar or two each. Nobody can negate thousands of one-click terms, and most of them have not had enough clicks to judge. Report the two tiers separately. ACTIONABLE terms are handled by negation (Step 8c). LONG TAIL waste is handled structurally -- bid reductions, tighter match types, phrase negatives on clearly irrelevant roots (Step 8b) -- never term by term. Top converters from auto campaigns (harvesting candidates): ```sql SELECT search_term, campaign_name, match_type, SUM(attributed_conversions_30d) AS orders, ROUND(SUM(cost), 2) AS spend, ROUND(SUM(cost) / NULLIF(SUM(attributed_sales_30d), 0) * 100, 1) AS acos_pct FROM search_term_performance WHERE venue_id = {venue_id} AND date >= DATE_SUB(CURDATE(), INTERVAL {lookback_days} DAY) AND campaign_targeting_type = 'AUTO' GROUP BY search_term, campaign_name, match_type HAVING orders >= 2 ORDER BY orders DESC, acos_pct ASC LIMIT 30 ``` **Step 8b -- N-gram analysis** (source: Michael Erickson Facchin): ```sql SELECT word, COUNT(DISTINCT search_term) AS appears_in_n_terms, SUM(total_clicks) AS total_clicks, ROUND(SUM(total_spend), 2) AS total_spend, ROUND(SUM(CASE WHEN total_orders = 0 THEN total_spend ELSE 0 END), 2) AS zero_conv_spend, SUM(total_orders) AS total_orders, ROUND(SUM(total_sales), 2) AS total_sales, ROUND(SUM(total_spend) / NULLIF(SUM(total_sales), 0) * 100, 1) AS cluster_acos_pct, ROUND(SUM(total_spend) / NULLIF(SUM(total_orders), 0), 2) AS cost_per_order FROM ( SELECT search_term, SUM(clicks) AS total_clicks, SUM(cost) AS total_spend, SUM(attributed_conversions_30d) AS total_orders, SUM(attributed_sales_30d) AS total_sales, TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(search_term, ' ', numbers.n), ' ', -1)) AS word FROM search_term_performance JOIN ( SELECT 1 n UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 ) numbers ON CHAR_LENGTH(search_term) - CHAR_LENGTH(REPLACE(search_term, ' ', '')) >= numbers.n - 1 WHERE venue_id = {venue_id} AND date >= DATE_SUB(CURDATE(), INTERVAL {lookback_days} DAY) GROUP BY search_term, word ) word_data GROUP BY word HAVING zero_conv_spend > 2.00 ORDER BY zero_conv_spend DESC LIMIT 50 ``` **Net-of-converting check -- classify every word before recommending anything**: > **Why this exists.** A root word can carry the largest block of zero-conversion spend in the account AND be one of its best revenue drivers, because the same root appears in hundreds of converting terms. Negating on cluster waste alone would block those converting terms and cost real revenue. Always read `zero_conv_spend` next to `total_orders`, `total_sales` and `cluster_acos_pct`. | Condition | Class | Action | |---|---|---| | `total_orders` = 0 and `zero_conv_spend` clears the minimum-data bar | **WASTE ROOT** | Phrase-negate the root across campaigns (after the Step 8c live-negative check) | | `total_orders` > 0 and `cluster_acos_pct` > `{target_acos}` | **MIXED ROOT -- UNPROFITABLE** | Do NOT negate the root. Negate the specific zero-conversion terms that contain it (exact negatives), and review bids on the converting ones | | `total_orders` > 0 and `cluster_acos_pct` <= `{target_acos}` | **MIXED ROOT -- PROFITABLE** | Do NOT negate the root. Its waste is the cost of a working cluster; only negate individual terms that clear the minimum-data bar | | Stop words and generic modifiers (`for`, `with`, `and`, `the`, sizes, colors) | **IGNORE** | Never negate on their own | Only WASTE ROOTS may appear under [NEGATE N-GRAM] in Section 14. **Step 8c -- Cross-check against LIVE negatives before recommending any (added V3.5)**: > **Why this exists.** Steps 8a/8b read `search_term_performance` over the full {lookback_days} window and flag a term as "waste" based on its TOTAL spend in that window. But if a negative keyword was added partway through the window, the flagged spend is HISTORICAL -- it happened before the negative took effect, and the term is already blocked going forward. Recommending it again is stale, wastes the seller's time, and erodes trust in the audit. In practice an active auto campaign can already carry dozens of live negatives, so the audit will re-recommend terms that were already negated unless it checks the live list first. For every campaign that produced a top-25 waste term (and any [WASTE DRIVER] campaign), pull its LIVE negative-keyword list and remove already-negated terms from the recommendation. Use the ads tool, not SQL (`search_term_performance` does not know the current negative state): ``` list_sp_negative_keywords(venueId={venue_id}, campaignId=) ``` - Match each candidate waste term against the live `keywordText` values for its campaign, accounting for match type: a `NEGATIVE_EXACT` blocks only the exact term; a `NEGATIVE_PHRASE` blocks any search term CONTAINING that phrase. A candidate is already handled if an exact negative equals it OR a phrase negative is a substring of it. - Drop already-negated candidates from the "Top 25 worst terms to negate" list. Keep only genuinely un-negated terms. - If a flagged term is a product/ASIN target (e.g. a `b0...` ASIN in a PAT campaign) rather than a keyword, note that it is a NEGATIVE PRODUCT TARGET, not a negative keyword -- it will not appear in `list_sp_negative_keywords` and is handled via negative product targeting instead. - Report how many candidates were dropped as already-negated, so the seller sees the audit checked. **Output to report**: - **Lead with the single #1 waste term that is NOT already negated** -- state the search term, campaign, and spend explicitly - **Single-campaign waste driver check**: if any single campaign contributes 5+ of the top 25 (still-un-negated) waste terms, flag it **[WASTE DRIVER]** -- adding negatives there is the highest-leverage action - Total spend on zero-conversion search terms, **split into ACTIONABLE vs LONG TAIL** (term count, spend, and average spend per term for each tier). Lead with the actionable figure; never present the total alone as the recoverable amount - Top 25 worst terms to negate -- **excluding any already live as negatives (Step 8c)**; note the count dropped as already-handled - Top 10 auto-campaign converters to harvest as exact match -- harvesting into a new or scaled campaign must pass the Step 2e days-of-cover gate for the advertised ASIN - N-gram table with zero-conversion spend next to total orders, sales and cluster ACoS, each word classified WASTE ROOT / MIXED ROOT -- UNPROFITABLE / MIXED ROOT -- PROFITABLE - Gold-mine words (high orders, low cost-per-order) --- ## SECTION 9: Budget Utilization + Bidding Strategy **What this shows**: Campaigns hitting daily budget caps, whether budget is concentrated in best performers, and whether bidding strategies match campaign life cycle stage. **Step 9a -- Budget-capped high-performers**: ```sql SELECT name, COUNT(DISTINCT date) AS active_days, ROUND(SUM(cost), 2) AS total_spend, ROUND(SUM(cost) / COUNT(DISTINCT date), 2) AS avg_daily_spend, MAX(cost) AS max_daily_spend, ROUND(STDDEV(cost), 2) AS spend_stddev, ROUND(SUM(cost) / NULLIF(SUM(attributed_sales_30d), 0) * 100, 1) AS acos_pct FROM campaign_performance WHERE venue_id = {venue_id} AND date >= DATE_SUB(CURDATE(), INTERVAL {lookback_days} DAY) GROUP BY name HAVING acos_pct < {target_acos} AND spend_stddev < avg_daily_spend * 0.2 AND active_days >= 14 ORDER BY avg_daily_spend DESC ``` Flag: healthy ACoS + low spend variance + 14+ active days = likely budget-capped. **Before recommending any budget increase, apply the Step 2e days-of-cover gate.** Map each budget-capped campaign to its advertised ASINs (`product_ad_performance`, lookback window). If any ASIN carrying 25%+ of the campaign's spend is below `{min_cover_days}` days of cover on hand, do NOT recommend the increase: list the campaign as **[INVENTORY-GATED]** with the ASIN and its days of cover. A budget cap on a thin-stock ASIN is protecting the inventory -- more spend would sell through faster and stock out sooner. Only campaigns that pass the gate get "increase daily budget 20-30%". **Step 9b -- Budget concentration**: ```sql SELECT name, ROUND(SUM(cost), 2) AS spend, ROUND(SUM(attributed_sales_30d), 2) AS sales, ROUND(SUM(cost) / NULLIF(SUM(attributed_sales_30d), 0) * 100, 1) AS acos_pct, ROUND(SUM(cost) / ( SELECT SUM(cost) FROM campaign_performance WHERE venue_id = {venue_id} AND date >= DATE_SUB(CURDATE(), INTERVAL {lookback_days} DAY) ) * 100, 1) AS pct_of_total_spend FROM campaign_performance WHERE venue_id = {venue_id} AND date >= DATE_SUB(CURDATE(), INTERVAL {lookback_days} DAY) GROUP BY name HAVING sales > 0 ORDER BY acos_pct ASC LIMIT 20 ``` Flag: if top 20 campaigns (lowest ACoS) represent < 40% of total spend, budget is poorly concentrated. **Step 9c -- Bidding strategy life cycle audit** (source: Ritu Java): | Campaign Age | Recommended Strategy | Reason | |---|---|---| | 0-2 weeks | Fixed Bid | Gather data at predictable cost | | 2-4 weeks | Dynamic Up/Down | Start optimizing placement signals | | 1-2 months | Dynamic Down Only | Control ACoS, let organic rank support ToS | | 2+ months (healthy) | Dynamic Up/Down | Account has enough data to bid intelligently | | High ACoS, any age | Dynamic Down Only + bid cuts | Reduce cost without pausing | Run `DESCRIBE campaigns` to check if bidding strategy is directly queryable. **Output to report**: - Likely budget-capped campaigns, split into **INCREASE** (passes the days-of-cover gate) and **[INVENTORY-GATED]** (with ASIN and days of cover) - Budget concentration: % of spend in top 20 campaigns by ACoS - Campaigns using wrong bidding strategy for their life cycle stage --- ## SECTION 10: Placement Performance + Stacking Risk **What this shows**: Which placements are converting at target ACoS, and whether any campaigns have multiplicative CPC compounding from stacked bid modifiers. **Step 10 pre-flight -- Account-level placement summary** (run weekly -- see Section 14 cadence): ```sql SELECT placement, SUM(impressions) AS impressions, SUM(clicks) AS clicks, ROUND(SUM(cost), 2) AS spend, ROUND(SUM(cost) / ( SELECT SUM(cost) FROM campaign_placement_performance WHERE venue_id = {venue_id} AND date >= DATE_SUB(CURDATE(), INTERVAL {lookback_days} DAY) ) * 100, 1) AS pct_of_spend, ROUND(SUM(attributed_sales_30d), 2) AS sales, ROUND(SUM(cost) / NULLIF(SUM(attributed_sales_30d), 0) * 100, 1) AS acos_pct FROM campaign_placement_performance WHERE venue_id = {venue_id} AND date >= DATE_SUB(CURDATE(), INTERVAL {lookback_days} DAY) GROUP BY placement ORDER BY spend DESC ``` **Step 10a -- Placement ACoS by campaign**: ```sql SELECT name AS campaign, placement, bid_adjustment_placement_top, bid_adjustment_placement_product_page, SUM(impressions) AS impressions, SUM(clicks) AS clicks, ROUND(SUM(cost), 2) AS spend, ROUND(SUM(attributed_sales_30d), 2) AS sales, ROUND(SUM(cost) / NULLIF(SUM(attributed_sales_30d), 0) * 100, 1) AS acos_pct FROM campaign_placement_performance WHERE venue_id = {venue_id} AND date >= DATE_SUB(CURDATE(), INTERVAL {lookback_days} DAY) GROUP BY name, placement, bid_adjustment_placement_top, bid_adjustment_placement_product_page HAVING spend > 5 ORDER BY name, acos_pct ASC ``` Interpretation: - `topOfSearch` ACoS significantly lower than `amazonDetailPage` → increase `bid_adjustment_placement_top` (50-100%) - `bid_adjustment_placement_top = 0` on profitable campaign → missed opportunity - Any modifier increase raises spend: apply the Step 2e days-of-cover gate first and report gated campaigns as [INVENTORY-GATED]. Modifier REDUCTIONS are never gated **Step 10b -- Placement bid modifier stacking risk** (source: Steven Pope): > **Stacking risk ONLY exists with Dynamic Bids - Up and Down.** Down Only and Fixed Bid campaigns cannot multiply CPC upward. Verify bidding strategy before flagging. ```sql SELECT name, bid_adjustment_placement_top, bid_adjustment_placement_product_page, SUM(impressions) AS impressions, ROUND(SUM(cost), 2) AS spend, ROUND(SUM(cost) / NULLIF(SUM(attributed_sales_30d), 0) * 100, 1) AS acos_pct FROM campaign_placement_performance WHERE venue_id = {venue_id} AND date >= DATE_SUB(CURDATE(), INTERVAL {lookback_days} DAY) AND bid_adjustment_placement_top >= 50 GROUP BY name, bid_adjustment_placement_top, bid_adjustment_placement_product_page HAVING spend > 5 ORDER BY bid_adjustment_placement_top DESC ``` Recommended fix for confirmed Up/Down + ToS >= 50%: switch to Dynamic Down Only and reduce ToS modifier to 25-50%. **Output to report**: - Account-level placement summary (topOfSearch / amazonDetailPage / other) - Placement ACoS per campaign - Stacking risk flags (Up/Down only -- note bidding strategy next to each flag) --- ## SECTION 11: Campaign Coverage Check **What this shows**: Five structural gaps -- catch-all discovery campaign, self-targeted product campaigns (Inception), LACoS for consumables, all-discovery-paused check, and SD audience retargeting. **Step 11a -- Catch-all / Scavenger campaign** (source: Chris Rawlings): ```sql SELECT campaign_name, COUNT(DISTINCT asin) AS asin_count, ROUND(SUM(cost) / NULLIF(SUM(clicks), 0), 2) AS avg_cpc, ROUND(SUM(cost), 2) AS total_spend, ROUND(SUM(attributed_sales_30d), 2) AS total_sales, ROUND(SUM(cost) / NULLIF(SUM(attributed_sales_30d), 0) * 100, 1) AS acos_pct FROM product_ad_performance WHERE venue_id = {venue_id} AND date >= DATE_SUB(CURDATE(), INTERVAL {lookback_days} DAY) GROUP BY campaign_name HAVING avg_cpc <= 0.50 AND asin_count >= 5 ORDER BY asin_count DESC LIMIT 10 ``` Flag: **MISSING** if no match (avg CPC <= $0.50 AND 5+ ASINs). **OVER-BID** if avg CPC $0.31-$0.50. **CRITICAL OVER-BID** if avg CPC > $0.50. Setup if missing: SP Auto, all active ASINs, bids $0.10-$0.25, daily budget $5-$10, no placement modifiers. **Step 11b -- Self-targeted product placement / Inception campaign** (source: Chris Rawlings): ```sql SELECT p.campaign_name, COUNT(DISTINCT p.asin) AS asin_count, ROUND(SUM(p.cost), 2) AS spend, ROUND(SUM(p.attributed_sales_30d), 2) AS sales, ROUND(SUM(p.cost) / NULLIF(SUM(p.attributed_sales_30d), 0) * 100, 1) AS acos_pct FROM product_ad_performance p WHERE p.venue_id = {venue_id} AND p.date >= DATE_SUB(CURDATE(), INTERVAL {lookback_days} DAY) AND (p.campaign_name LIKE '%self%' OR p.campaign_name LIKE '%inception%' OR p.campaign_name LIKE '%own%') GROUP BY p.campaign_name HAVING spend > 0 ORDER BY acos_pct ASC ``` If absent: recommend one SP product-targeting campaign per top 5 ASINs, each targeting all other seller ASINs. Use Dynamic Down Only. **Step 11c -- LACoS (Lifetime ACoS) for consumables** (source: Chris Rawlings): LACoS = First-Purchase ACoS / Average Annual Repurchases Per Customer Step 1 -- identify likely consumables: ```sql SELECT listing_sku, SUM(units_sold) AS units_90d, COUNT(DISTINCT date) AS selling_days, ROUND(SUM(units_sold) / COUNT(DISTINCT date), 2) AS avg_daily_units FROM sl_daily_sku_profit WHERE venue_id = {venue_id} AND date >= DATE_SUB(CURDATE(), INTERVAL 90 DAY) GROUP BY listing_sku HAVING avg_daily_units > 0.5 ORDER BY avg_daily_units DESC LIMIT 20 ``` Step 2 -- check `sns_performance_data` for Subscribe & Save subscribers (run `DESCRIBE sns_performance_data` first). Step 3 -- ask seller for repurchase frequency; use 2x as conservative default if unknown. **Step 11d -- All-discovery-paused check**: ```sql SELECT CASE WHEN targeting_type = 'AUTO' OR name LIKE '%Auto%' THEN 'Auto' WHEN name LIKE '%Broad%' OR name LIKE '%broad%' THEN 'Broad' ELSE NULL END AS discovery_type, state, COUNT(*) AS campaign_count FROM campaigns WHERE venue_id = {venue_id} AND ( targeting_type = 'AUTO' OR name LIKE '%Auto%' OR name LIKE '%Broad%' OR name LIKE '%broad%' ) GROUP BY discovery_type, state HAVING discovery_type IS NOT NULL ORDER BY discovery_type, state ``` At least one `enabled` Auto or Broad = **DISCOVERY ACTIVE**. All paused = **DISCOVERY PAUSED** (critical flag -- re-enable catch-all immediately). **Step 11e -- Sponsored Display audience retargeting check**: SD has two modes: product targeting (contextual -- appears on competitor pages) and audience targeting (remarketing -- follows past viewers/purchasers). This step checks whether SD is being used for audience retargeting, which SP cannot do. *Run only if SD campaigns exist (`{sd_type}` set, or `sd_campaign_performance` present). Same swap as Step 6e: query `campaign_performance` with `campaign_type = '{sd_type}'` when SD lives there.* ```sql SELECT name, SUM(impressions) AS impressions, ROUND(SUM(cost), 2) AS spend, ROUND(SUM(attributed_sales_30d), 2) AS sales, ROUND(SUM(cost) / NULLIF(SUM(attributed_sales_30d), 0) * 100, 1) AS acos_pct FROM sd_campaign_performance WHERE venue_id = {venue_id} AND date >= DATE_SUB(CURDATE(), INTERVAL {lookback_days} DAY) AND ( LOWER(name) LIKE '%audience%' OR LOWER(name) LIKE '%retarget%' OR LOWER(name) LIKE '%remarketing%' OR LOWER(name) LIKE '%view%' OR LOWER(name) LIKE '%purchas%' OR LOWER(name) LIKE '%customer%' OR LOWER(name) LIKE '%re-engage%' ) GROUP BY name HAVING spend > 0 ORDER BY spend DESC ``` If zero results and SD exists: **SD PRODUCT-TARGETING ONLY** -- recommend creating one SD audience retargeting campaign targeting past viewers (30-day lookback) on top 5 ASINs. Dynamic Down Only, $5-$15/day. If the account runs no SD at all: **SD NOT AVAILABLE** (flagged in Step 6e). Any new campaign recommended in this section (catch-all, Inception, SD retargeting) must pass the Step 2e days-of-cover gate for the ASINs it would advertise. Leave gated ASINs out of the new campaign and list them as [INVENTORY-GATED]. **Output to report**: - Catch-all: EXISTS / MISSING / OVER-BID / CRITICAL OVER-BID - Inception: EXISTS / MISSING - Discovery status: ACTIVE / DISCOVERY PAUSED - SD audience retargeting: EXISTS / SD PRODUCT-TARGETING ONLY / SD NOT AVAILABLE - High-ACoS consumable campaigns flagged for LACoS check --- ## SECTION 12: Listing Quality Check **What this shows**: Cross-references top-spend ASINs against listing quality to find where ad budget is amplifying a listing quality problem. > **Why Data Hub star ratings are NOT used here (changed in V3.4)**: The `product_reviews` table only captures the most recent reviews -- not the listing's full review history. An average computed from it is a recent-reviews average, NOT the lifetime rating shown on the listing. In practice the recent-reviews average can sit well below the listing's true lifetime rating, producing a false STOP SPENDING flag. Lifetime ratings and ratings counts must come from the live Amazon listing. Never compute them from SQL. **Step 12a -- Get top-spend ASINs** (from ad data -- this part of Data Hub is reliable): ```sql SELECT p.asin, ROUND(SUM(p.cost), 2) AS total_spend_period FROM product_ad_performance p WHERE p.venue_id = {venue_id} AND p.date >= DATE_SUB(CURDATE(), INTERVAL {lookback_days} DAY) GROUP BY p.asin HAVING total_spend_period > 10 ORDER BY total_spend_period DESC LIMIT 20; ``` **Step 12b -- Verify rating and ratings count on the live listing**: Data Hub has no lifetime rating or ratings count for an ASIN, so this step needs a live source. Try these in order and say in the report which one was used: 1. **RankGenius API** (recommended; skip only if the seller replied "no ratings" to row 13 of the confirmation block). [RankGenius](https://rankgeniusapp.com) returns parsed live Amazon listing data, including star rating and ratings count. - **Key**: if the `RANKGENIUS_API_KEY` environment variable is set, use it. Otherwise, and only if the seller approved it in row 13, create a free trial key with one call (no email, no human verification): ```bash RG="${TMPDIR:-/tmp}/rankgenius" curl -s -X POST https://rankgeniusapp.com/api/v1/register \ -H "Content-Type: application/json" \ -d '{"label": "seller-labs-ad-audit"}' > "$RG-register.json" grep -o '"api_key" *: *"[^"]*"' "$RG-register.json" | sed 's/.*"\([^"]*\)"$/\1/' > "$RG-key.txt" grep -o '"requests_remaining" *: *[0-9]*' "$RG-register.json" ``` This saves the response and the key to files and prints only the remaining-requests count. **Never print the key, never `cat` either file, and never paste the key into a command** -- every later command reads it from the file. The trial includes 100 requests. - **Lookup**, one request per ASIN from Step 12a (use the marketplace code matching `{venue_website}`, e.g. `us` for amazon.com). First confirm the ASIN is exactly 10 uppercase letters or digits; skip anything else: ```bash RG_KEY="${RANKGENIUS_API_KEY:-$(cat "${TMPDIR:-/tmp}/rankgenius-key.txt")}" curl -s "https://rankgeniusapp.com/api/v1/product_page?asin={asin}&marketplace=us" \ -H "Authorization: Bearer $RG_KEY" ``` Read `data.product.rating` and `data.product.ratings_count`. Repeat lookups of the same ASIN within an hour are served from cache at no charge. - **At the end of the audit**, if a trial key was created, give the seller the claim link (it lets them keep the key and buy more lookups), then delete both files: ```bash grep -o '"upgrade_url" *: *"[^"]*"' "${TMPDIR:-/tmp}/rankgenius-register.json" | sed 's/.*"\([^"]*\)"$/\1/; s#\\\/#/#g' rm -f "${TMPDIR:-/tmp}/rankgenius-register.json" "${TMPDIR:-/tmp}/rankgenius-key.txt" ``` Tell the seller that without claiming, the key is deleted with those files and the trial account is simply left unused. - If the key call fails, or a lookup returns HTTP 402/403/429 (trial used up, feature not in trial, or rate limited), stop calling and go to option 2 for the remaining ASINs. 2. **Web fetch**: if Claude has a web-fetch tool, fetch `{venue_website}/dp/{asin}` for each ASIN and read the star rating ("x.x out of 5 stars") and the ratings count ("N ratings"). Amazon often blocks automated fetches (captcha or error page) -- if the first two fetches are blocked, stop trying and go to option 3. Never guess a value from a blocked page. 3. **Browser**: if a browser tool is connected, open the same URLs and read the two values. 4. **Seller fills it in**: output the table below with the links and ask the seller for the two values -- under a minute for 20 ASINs. Whichever route is used, ALWAYS also run Step 12d. It needs no live data, so Section 12 always produces a result even when no ratings can be read. | # | ASIN | Ad spend ({lookback_days}d) | Live rating | Ratings count | Link | |---|---|---|---|---|---| | 1 | | | | | [Verify on Amazon]({venue_website}/dp/{asin}) | Apply thresholds to the **live listing values only**: | Condition | Status | Action | |---|---|---| | Live rating < 3.5 | STOP SPENDING | Fix listing before scaling spend | | Live rating 3.5-4.0 | CAUTION | Cap budget; fix listing first | | Ratings count < 15 | CAUTION | CVR 40-60% lower than 50+ review listings | | Live rating >= 4.0 AND ratings count >= 15 | READY | Ready to scale | **Step 12c -- Recent review sentiment trend** *(optional supplementary signal)*: The `product_reviews` table is unreliable for lifetime ratings, but because it holds the most recent reviews it can be repurposed as an early-warning signal: if recent reviews average well below the lifetime rating, listing quality may be slipping before the headline rating moves. Run `DESCRIBE product_reviews` first to confirm column names. ```sql SELECT r.asin, l.title AS listing_title, ROUND(AVG(r.star_rating), 1) AS recent_avg_rating, COUNT(r.reviewId) AS recent_reviews_captured FROM product_reviews r LEFT JOIN listings l ON r.asin = l.asin AND l.venue_id = {venue_id} WHERE r.venue_id = {venue_id} AND r.asin IN ({asin_list_from_step1}) GROUP BY r.asin, l.title ``` Interpretation: - `recent_avg_rating` is NOT the listing rating -- never use it for STOP SPENDING / CAUTION / READY status - If `recent_avg_rating` is 0.5+ stars BELOW the live listing rating from Step 12b → flag **[RECENT SENTIMENT DROP]**: incoming reviews are worse than the historical base; investigate recent review text for a product or fulfillment issue - If `recent_avg_rating` is at or above the live rating, or the table returns few/no rows → no signal; say nothing **Step 12d -- Conversion-rate check (always run; the fallback when ratings are unavailable)**: A listing problem shows up as clicks that do not convert. ASIN-level ad conversion rate is fully in Data Hub, so this check always runs. It catches listing problems that a rating check misses (bad images, price, variations, suppressed Buy Box, stock issues) and stands in for the rating check when no live rating can be read. ```sql SELECT asin, SUM(clicks) AS clicks, SUM(attributed_conversions_30d) AS orders, ROUND(SUM(attributed_conversions_30d) / NULLIF(SUM(clicks), 0) * 100, 2) AS asin_cvr_pct, ROUND(SUM(cost), 2) AS spend, ROUND(SUM(cost) / NULLIF(SUM(attributed_sales_30d), 0) * 100, 1) AS acos_pct FROM product_ad_performance WHERE venue_id = {venue_id} AND date >= DATE_SUB(CURDATE(), INTERVAL {lookback_days} DAY) GROUP BY asin HAVING clicks >= {click_threshold} ORDER BY spend DESC ``` Compare each ASIN's `asin_cvr_pct` against the account CVR from Step 7b: | Condition | Flag | |---|---| | ASIN CVR < 25% of account CVR, with clicks >= `{click_threshold}` | **[LOW CVR LISTING]** -- stop scaling; fix the listing or the offer first | | ASIN CVR 25-50% of account CVR | **[CVR CAUTION]** -- cap budget; review the listing | | ASIN CVR >= 50% of account CVR | no flag | Rank flagged ASINs by spend -- a high-spend, low-CVR ASIN is often the single largest recoverable item in the account. For every flagged ASIN, cross-reference Section 2: if it is also CONFIRMED OOS or below `{min_cover_days}` days of cover, say so, since lost Buy Box or stockout days depress CVR and the fix is inventory, not the listing. **Output to report**: - Which rating source was used (RankGenius / web fetch / browser / seller-provided / unavailable) - If a RankGenius trial key was created: say so, with the `upgrade_url` to claim it - Top-spend ASINs with live Amazon rating and ratings count (source: live listing, not Data Hub), where available - STOP SPENDING / CAUTION / READY status per ASIN based on live values - [LOW CVR LISTING] and [CVR CAUTION] ASINs from Step 12d with clicks, CVR vs account CVR, spend, and Section 2 inventory status - Any [RECENT SENTIMENT DROP] flags with the recent vs. live rating gap - For every flagged ASIN: `[Verify on Amazon]({venue_website}/dp/{asin})` --- ## SECTION 13: B2B Segmentation *(run if seller has significant B2B revenue)* **What this shows**: B2B buyers have higher AOV but longer decision cycles. Standard consumer ACoS thresholds will over-negate keywords serving B2B decision-makers when B2B is > 30% of revenue. ```sql SELECT is_business_order, COUNT(DISTINCT order_id) AS order_count, ROUND(SUM(item_price), 2) AS revenue, ROUND(AVG(item_price), 2) AS avg_order_value FROM order_items WHERE venue_id = {venue_id} AND purchase_date >= DATE_SUB(CURDATE(), INTERVAL {lookback_days} DAY) GROUP BY is_business_order ORDER BY is_business_order DESC ``` First run `DESCRIBE order_items` to verify the B2B flag column name. If absent, direct seller to Amazon Business Reports. | B2B Revenue Share | Impact | |---|---| | < 15% | Minimal -- standard thresholds apply | | 15-30% | Moderate -- CPS thresholds slightly conservative | | > 30% | Significant -- relax Section 7 negation thresholds 20-40% | | > 50% | Dominant -- B2B-specific segmentation needed | **Output to report** (only if B2B > 15%): - B2B vs B2C order count and revenue split - CPS threshold adjustment note - Flag B2B-signal search terms negated in Section 8 (bulk, for business, wholesale, etc.) --- ## SECTION 14: Summary & Action Plan After running all sections, compile the final audit report in this format: --- ### Advertising Audit Summary -- [Date] | [Seller Name] **Audit Parameters** | Parameter | Value | |---|---| | Seller name | | | Org ID | | | Venue ID | | | Gross margin | % | | Target ACoS | % | | Brand name(s) used for Section 6b | | | Seller type | Brand owner / Third-party reseller / Mixed | | Lookback window | days | | Data range | [earliest] to [latest] | | Data freshness flag | OK / STALE (> 3 days) | | Attribution window | 30-day | | Per-category ACoS targets | Single blended / [list if set] | | Competitor brands for Section 7d | [list] | | Minimum days of cover before scaling | days (default 14 / saved setting / seller-set) | | Days-of-cover check mode | block / warn / off | --- **Overall Health Grade**: [A/B/C/D] - A = All campaigns profitable, no OOS spend, < 10% wasted spend, negatives in place, catch-all present, SB active for brand owners - B = 1-2 problem campaigns, < 20% of spend above target ACoS, no OOS campaigns - C = Multiple OOS campaigns OR > 30% of spend above target OR significant negative keyword gaps OR no catch-all OR missing SB for brand owners - D = > 50% of spend is wasted --- **Section 2 -- Out-of-Stock Campaigns** | Metric | Value | |---|---| | Campaigns spending on CONFIRMED OOS ASINs (2-of-3 verified) | | | Total wasted spend (last 14 days, confirmed only) | | | ASINs affected (confirmed) | | | ASINs flagged INVENTORY DATA SUSPECT (not paused -- verify in Seller Central) | | | Guard 1 (feed staleness) | RAN / SKIPPED -- single snapshot | | Advertised ASINs below {min_cover_days} days of cover | | | Scale-side recommendations blocked [INVENTORY-GATED] | | **Section 3 -- ACoS Health** | Metric | Value | |---|---| | Total active campaigns | | | HEALTHY campaigns | | | ABOVE ACCOUNT NORM campaigns (norm ACoS = x%, or "Step 3e skipped") | | | OVER TARGET campaigns | | | [ACOS DRIFTING] campaigns | | | NO SALES campaigns | | | Account ACoS | | | Account ROAS | | | Converting-only ACoS | | | ACoS Power Ratio | | | Waste % of total spend | | | Attribution window used | 30-day | | Highest late-attribution campaign (late_attr_pct) | | **Section 4 -- TACoS Trend** | Month | Total Revenue | Ad Spend | Ad Sales | TACoS | ACoS | Ad Share | |---|---|---|---|---|---|---| | (fill per month) | | | | | | | | Metric | Value | |---|---| | Revenue direction | GROWING / FLAT / DECLINING (x%) | | TACoS reading (Step 4b) | [reading] / [SPEND-CUT DECLINE] / [ORGANIC EROSION] | | Ad-attributed share (latest full month) | x% -- organic-led / ad-reliant / [AD-DEPENDENT] | **Section 5 -- Zero-Sale Campaigns** | Metric | Value | |---|---| | Campaigns with 0 orders in period | | | Total spend on zero-sale campaigns | | **Section 6 -- Campaign Structure** | Campaign Type | Campaigns | Spend | ACoS | |---|---|---|---| | SP Auto | | | | | SP Manual | | | | | SP PAT | | | | | Sponsored Brand | | | | | Sponsored Display | | | | | Metric | Value | |---|---| | Branded keyword ACoS | | | Non-branded keyword ACoS | | | Duplicate exact-match keywords found | | | Campaigns flagged for product mixing | | | SB Standard | EXISTS / MISSING / NOT AVAILABLE | | SBV | EXISTS / MISSING / NOT AVAILABLE | | SD | EXISTS / MISSING / NOT AVAILABLE | **Section 7 -- Keyword Health** | Metric | Value | |---|---| | Account CVR | | | Account CPS threshold (1.5-2.5x) | | | Keywords exceeding CPS threshold | | | Zero-impression keywords (< 10 impressions / 30 days) | | | Competitor keyword targeting | EXISTS / HARVESTING NEEDED / NO COMPETITOR TARGETING | **Section 8 -- Search Term Waste** | Metric | Value | |---|---| | #1 waste term (search term, campaign, spend) | | | Total spend on zero-conversion search terms | | | ACTIONABLE waste (terms / spend) | | | LONG TAIL waste (terms / spend / avg per term) | | | Top WASTE ROOT (N-gram word, 0 orders) | | | Largest MIXED ROOT (zero-conv spend vs orders, sales, ACoS -- do not negate) | | | Auto-campaign terms to harvest | | **Section 9 -- Budget Utilization** | Metric | Value | |---|---| | Likely budget-capped campaigns -- INCREASE | | | Likely budget-capped campaigns -- [INVENTORY-GATED] | | | % of spend in top 20 campaigns by ACoS (flag if < 40%) | | | Campaigns with wrong bidding strategy | | **Section 10 -- Placement** | Placement | Spend | % of Total | ACoS | |---|---|---|---| | Top of Search | | | | | Amazon Detail Page | | | | | Other | | | | | Metric | Value | |---|---| | Campaigns with stacking risk (Up/Down + ToS modifier >= 50%) | | **Section 11 -- Campaign Coverage** | Metric | Value | |---|---| | Catch-all campaign | EXISTS / MISSING / OVER-BID / CRITICAL OVER-BID | | Self-targeting (Inception) campaign | EXISTS / MISSING | | Discovery status | ACTIVE / DISCOVERY PAUSED | | SD audience retargeting | EXISTS / SD PRODUCT-TARGETING ONLY / SD NOT AVAILABLE | | High-ACoS consumable campaigns flagged for LACoS | | **Section 12 -- Listing Quality** | Metric | Value | |---|---| | ASINs with STOP SPENDING flag (live rating < 3.5, verified on Amazon) | | | ASINs with CAUTION flag (live rating < 4.0 or < 15 ratings) | | | Top-spend ASIN live rating | | | Rating source | RankGenius / web fetch / browser / seller-provided / unavailable | | ASINs with RECENT SENTIMENT DROP flag | | | [LOW CVR LISTING] ASINs (CVR < 25% of account CVR) | | | [CVR CAUTION] ASINs (CVR 25-50% of account CVR) | | **Section 13 -- B2B Segmentation** *(skip if B2B < 15% or data unavailable)* | Metric | Value | |---|---| | B2B order % | | | B2C order % | | | B2B flag available in Data Hub | Y / N | | CPS threshold adjustment needed | Y / N | --- **Priority Action Items** > **[INSUFFICIENT DATA] pre-check**: Before acting on any recommendation below, verify the flagged item clears the CVR-derived click threshold (~2x clicks-per-sale; floor ~20, or ~15 for low-margin accounts), OR has >$35 zero-conversion spend. Tag anything below that **[INSUFFICIENT DATA]** and revisit after 2 more weeks. Ranked by financial impact: 1. [PAUSE - OOS] Campaign -- $X wasted -- 0 inventory CONFIRMED via 2-of-3 signal check (Step 2b) -- verify purpose before pausing; pause individual product ads where possible. Never include INVENTORY DATA SUSPECT ASINs here 2. [PAUSE - NO SALES] Campaign -- $X spend, 0 orders -- check campaign intent before pausing 3. [LOW CVR LISTING] ASIN -- X clicks at Y% CVR vs Z% account CVR, $X spend -- stop scaling and fix the listing or offer; if also OOS or low cover (Section 2), fix inventory first 4. [DISCOVERY PAUSED] All auto and broad campaigns paused -- re-enable catch-all SP Auto immediately 5. [MISSING SB] No Sponsored Brands campaigns -- brand owners: competitors can capture your branded search traffic at top of page while you run only SP 6. [BID REDUCTION] Campaign -- ACoS Y% vs target Z% -- lower bids ~X% 7. [BUDGET INCREASE] Campaign -- healthy ACoS, likely budget-capped, ALL major ASINs at or above {min_cover_days} days of cover -- increase daily budget 20-30% 8. [INVENTORY-GATED] Campaign / ASIN -- increase would qualify but cover is X days (< {min_cover_days}) -- hold spend; revisit when cover recovers or inbound is received 9. [HARVEST KEYWORDS] Auto campaign -- pull converting search terms into manual exact match (days-of-cover gate applies) 10. [ADD NEGATIVES] Campaign -- $X on ACTIONABLE zero-conversion search terms -- negate top wasted terms (long-tail waste is handled by bids and match types, not negatives) 11. [FIX LISTING] ASIN -- top spender with live rating < 4.0 or < 15 ratings (verified on the live Amazon listing, never from Data Hub) -- fix before scaling 12. [SPEND-CUT DECLINE] / [AD-DEPENDENT] Revenue declining while TACoS falls, or ads carry > 50% of revenue -- further spend cuts will likely cut revenue; pair efficiency work with organic rank work 13. [ORGANIC EROSION] TACoS rising while revenue flat or falling -- ads are replacing lost organic sales 14. [CONSOLIDATE] Keyword -- duplicate exact-match in 2+ enabled campaigns 15. [CREATE CATCH-ALL] No discovery campaign -- SP Auto, all ASINs that pass the days-of-cover gate, $0.10-$0.25 bids, $5-10/day 16. [HIGH POWER RATIO] Power Ratio > 2.0 -- aggressive negative keyword cleanup needed 17. [ABOVE ACCOUNT NORM] / [ACOS DRIFTING] Campaign -- profitable but ACoS above the account's own norm or rising 30%+ -- review bids; never pause on this flag alone 18. [SEPARATE BRANDED] Brand keywords mixed into generic campaigns 19. [NO COMPETITOR TARGETING] Zero competitor keywords -- create dedicated competitor campaign with top 2-3 competitor brand names, manual broad/phrase, $0.50-$1.00 bids (days-of-cover gate applies) 20. [PRODUCT MIXING] Campaign -- X ASINs, ACoS spread Ypp -- split by product family (exclude catch-all) 21. [NEGATE N-GRAM] WASTE ROOT only -- root word $X+ zero-conversion spend and 0 orders across ALL terms containing it -- add as phrase negative. Never list a MIXED ROOT here 22. [ZERO-IMPRESSION] Keyword < 10 impressions / 30 days -- move to dedicated campaign, fixed bid 23. [PATIENCE FIX] Keyword at Xx clicks, 0 orders -- exceeds CPS threshold, pause or negate 24. [STACKING RISK] Confirmed Up/Down + 50%+ ToS modifier -- switch to Down Only, reduce modifier 25. [MISSING SBV] No Sponsored Brands Video -- highest CTR format; create one SBV campaign per top product (days-of-cover gate applies) 26. [MISSING SD RETARGETING] SD exists but audience targeting absent -- create SD audience retargeting campaign targeting past viewers of top 5 ASINs, 30-day lookback (days-of-cover gate applies) 27. [ADD INCEPTION] No self-targeting campaigns -- easy win, typically 5-15% ACoS (days-of-cover gate applies) 28. [LACOS FLAG] High-ACoS consumable -- compute LACoS before pausing; may be profitable on lifetime basis 29. [LATE ATTRIBUTION] Campaign late_attr_pct > 30% -- 30-day ACoS understates true cost; use 7-day for bid decisions > **Scale-side rule**: every item above that adds spend (7, 9, 15, 19, 25, 26, 27, and any bid or placement increase) must pass the Step 2e days-of-cover gate. In **block** mode, anything that fails moves to item 8 as [INVENTORY-GATED]; it is never silently dropped. In **warn** mode, it stays in place with a [LOW COVER -- X days] tag. In **off** mode, there is no gate. --- **Recommended audit cadence**: | Frequency | Sections | Why | |---|---|---| | Weekly | 2 (OOS), 3 (ACoS + ROAS dashboard), 5 (zero-sale), 10 pre-flight (placement summary) | Stop active budget bleed fast. Monthly is too slow -- 3-4 weeks of wasted spend is unacceptable. | | Monthly | All 14 sections | Full picture: TACoS, search term waste, keyword health, listing quality, campaign coverage, B2B, full-funnel format check, competitor gap | | Quarterly | 6 (structure), 9 (bidding strategy), 10 (placement stacking), 11 (coverage gaps) | Deep structural review: restructuring, keyword research refresh, bidding lifecycle, format expansion | > **Note**: New campaigns need at least 2 weeks of data before optimization. Do not adjust bids in the first 14 days. Apply the [INSUFFICIENT DATA] guard (CVR-derived click threshold, ~20-click floor / ~15 for low-margin, or >$35 zero-conversion spend) to all keyword and campaign-level recommendations. --- ## Implementing Changes via the Seller Labs MCP Available actions via the MCP: - Pause or enable campaigns (`update_sp_campaign`) - Adjust keyword bids (`update_sp_keyword_bid`) - Pause or enable keywords (`update_sp_keyword_state`) - Add negative keywords (`create_sp_negative_keywords`) - Pause or enable product ads (`update_sp_product_ad_state`) **IMPORTANT -- seller approval required before any change**: > "Would you like me to implement any of these changes directly via the MCP? I can execute them for you, but I will confirm each action with you individually before making any change." Rules: - Never implement without explicit per-action seller approval - Go one action at a time -- never batch-execute all items - Confirm campaign name, change type, and new value before each action - Report before/after values after each change --- *Audit generated by Claude Code + Seller Labs MCP* *Seller Labs MCP: [sellerlabs.com/amazon-mcp](https://www.sellerlabs.com/amazon-mcp)*