# Amazon Seller Inventory Audit
*A Claude Code prompt for Amazon FBA sellers connected to Seller Labs MCP*

---

## How to Use This File

1. Open this file in Claude Code with the **Seller Labs MCP** connected
2. Claude will ask you a few questions to get started, then run each section automatically
3. At the end you get a full audit report with prioritized action items
4. Run monthly or quarterly to stay ahead of fees, stockouts, and slow movers

**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 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)

---

## SECTION 1: Gather Seller Inputs

Before running any queries, ask the seller for the following information:

1. **Org ID** — Your Seller Labs organization ID (find it in your account settings or ask your Seller Labs rep)
2. **Venue ID** — The marketplace you want to audit (default: `2` = Amazon.com; other common values: `6` = Amazon.com alternative account)
3. **Lead time** — How many days from placing a purchase order to units arriving at Amazon's warehouse?
4. **Safety buffer** — How many days of stock do you want to keep on hand at all times? (recommended: 30 days)

Set these variables for the rest of the audit:
- `{org_id}` = seller's org ID
- `{venue_id}` = seller's venue ID
- `{lead_time}` = days from PO to Amazon receiving
- `{safety_buffer}` = days of stock to maintain
- `{reorder_threshold}` = `{lead_time}` + `{safety_buffer}`

Then run `DESCRIBE fba_inventory` to confirm column names before querying — column names in the Data Hub differ from what you'd expect (e.g., the column is `afn_fulfillable_quantity`, not `fulfillable_quantity`).

**5. Check data freshness** — Run this before writing any velocity queries:

```sql
SELECT MIN(date) AS earliest, MAX(date) AS latest, COUNT(*) AS rows
FROM sl_daily_sku_profit
WHERE venue_id = {venue_id}
```

If `earliest` is less than 14 days ago, **do not use `sl_daily_sku_profit` for velocity**. Use `inventory_ledger_history` instead (see below). The profit table divides by 30 or 90 regardless of how many days of data exist, which severely underestimates daily rates for new accounts.

**Velocity fallback for new accounts — `inventory_ledger_history`:**

This table is populated from the SP-API Inventory Ledger report, which Amazon provides going back 18+ months even for newly connected accounts. The `customer_shipments` column (negative values = units shipped out) gives accurate historical sell-through.

```sql
SELECT listing_sku,
  ROUND(SUM(CASE WHEN date >= CURRENT_DATE - INTERVAL 7 DAY
    THEN ABS(customer_shipments) ELSE 0 END) / 7.0, 2)  AS avg_daily_7d,
  ROUND(SUM(CASE WHEN date >= CURRENT_DATE - INTERVAL 30 DAY
    THEN ABS(customer_shipments) ELSE 0 END) / 30.0, 2) AS avg_daily_30d,
  ROUND(SUM(CASE WHEN date >= CURRENT_DATE - INTERVAL 90 DAY
    THEN ABS(customer_shipments) ELSE 0 END) / 90.0, 2) AS avg_daily_90d
FROM inventory_ledger_history
WHERE venue_id = {venue_id}
  AND customer_shipments < 0
GROUP BY listing_sku
HAVING avg_daily_30d > 0
ORDER BY avg_daily_30d DESC
```

Use these velocity values in place of `avg_daily_30d` from `sl_daily_sku_profit` throughout Sections 2, 3, and 7.

---

## SECTION 2: Inventory Health Dashboard

**What this shows**: Current stock levels, sales velocity, days of supply remaining, and which SKUs need to be reordered now.

Run the following query (replace `{venue_id}` and `{reorder_threshold}` with actual values):

```sql
SELECT
  inv.listing_sku,
  inv.asin,
  inv.afn_fulfillable_quantity AS units_in_stock,
  inv.afn_inbound_shipped_quantity + inv.afn_inbound_receiving_quantity AS units_inbound,
  COALESCE(vel.avg_daily_7d, 0) AS avg_daily_7d,
  COALESCE(vel.avg_daily_14d, 0) AS avg_daily_14d,
  COALESCE(vel.avg_daily_30d, 0) AS avg_daily_30d,
  CASE
    WHEN COALESCE(vel.avg_daily_30d, 0) > 0
    THEN ROUND(inv.afn_fulfillable_quantity / vel.avg_daily_30d, 0)
    ELSE 9999
  END AS days_of_supply,
  CASE
    WHEN COALESCE(vel.avg_daily_30d, 0) > 0
      AND (inv.afn_fulfillable_quantity / vel.avg_daily_30d) < {reorder_threshold}
    THEN 'REORDER'
    ELSE 'OK'
  END AS reorder_status,
  CASE
    WHEN COALESCE(vel.avg_daily_30d, 0) > 0
      AND (inv.afn_fulfillable_quantity / vel.avg_daily_30d) < {reorder_threshold}
    THEN GREATEST(CEIL({reorder_threshold} * vel.avg_daily_30d) - inv.afn_fulfillable_quantity, 0)
    ELSE 0
  END AS suggested_reorder_qty,
  COALESCE(prof.profit_per_unit, 0) AS profit_per_unit_30d
FROM fba_inventory inv
LEFT JOIN (
  SELECT listing_sku,
    ROUND(SUM(CASE WHEN date >= CURRENT_DATE - INTERVAL 7 DAY THEN units_sold ELSE 0 END) / 7.0, 2) AS avg_daily_7d,
    ROUND(SUM(CASE WHEN date >= CURRENT_DATE - INTERVAL 14 DAY THEN units_sold ELSE 0 END) / 14.0, 2) AS avg_daily_14d,
    ROUND(SUM(units_sold) / 30.0, 2) AS avg_daily_30d
  FROM sl_daily_sku_profit
  WHERE venue_id = {venue_id}
    AND date >= CURRENT_DATE - INTERVAL 30 DAY
  GROUP BY listing_sku
) vel ON inv.listing_sku = vel.listing_sku
LEFT JOIN (
  SELECT listing_sku,
    ROUND(SUM(profit) / NULLIF(SUM(units_sold), 0), 2) AS profit_per_unit
  FROM sl_daily_sku_profit
  WHERE venue_id = {venue_id}
    AND date >= CURRENT_DATE - INTERVAL 30 DAY
  GROUP BY listing_sku
) prof ON inv.listing_sku = prof.listing_sku
WHERE inv.venue_id = {venue_id}
ORDER BY
  CASE WHEN COALESCE(vel.avg_daily_30d, 0) > 0
    AND (inv.afn_fulfillable_quantity / vel.avg_daily_30d) < {reorder_threshold}
    THEN 0 ELSE 1 END,
  days_of_supply ASC
```

**Output to report**:
- Count of SKUs flagged REORDER vs. OK
- SKUs with 0 units in stock (out of stock)
- Top 10 highest-priority reorders by profit per unit

---

## SECTION 3: Seasonally-Adjusted Reorder Recommendations

**What this shows**: Adjusts the reorder quantities from Section 2 based on historical seasonal sales patterns. This prevents under-ordering in strong months (you could miss 2x sales) and over-ordering in slow months.

Step 1 — Pull same-month sales from prior years:

```sql
SELECT
  listing_sku,
  YEAR(date) AS year,
  MONTH(date) AS month,
  SUM(units_sold) AS monthly_units
FROM sl_daily_sku_profit
WHERE venue_id = {venue_id}
  AND MONTH(date) = MONTH(CURRENT_DATE)
  AND YEAR(date) < YEAR(CURRENT_DATE)
  AND units_sold > 0
GROUP BY listing_sku, YEAR(date), MONTH(date)
ORDER BY listing_sku, year DESC
```

Step 2 — Compare prior-year same-month sales to the trailing 30-day baseline from Section 2. Calculate a seasonal multiplier per SKU:

```
seasonal_multiplier = avg(prior year same-month daily rate) / avg(trailing 30-day daily rate)
```

Step 3 — Apply the multiplier to reorder quantities:
```
adjusted_reorder_qty = suggested_reorder_qty × seasonal_multiplier
```

Step 4 — Re-flag any SKUs that were "OK" in Section 2 but shift to "REORDER" after seasonal adjustment.

**Output to report**:
- Seasonal multiplier per SKU (flag anything above 1.5x — significant uplift)
- Revised reorder quantities
- SKUs that shifted from OK to REORDER after adjustment
- Total expected profit value across all recommended reorders

---

## SECTION 4: Unfulfillable & Stranded Inventory

**What this shows**: Units sitting at Amazon's warehouse that cannot be sold — either damaged/defective (unfulfillable) or not linked to an active listing (stranded). These units incur storage fees with zero revenue potential.

First, run `DESCRIBE fba_inventory` and check for these columns:
- `afn_unsellable_quantity` — damaged or defective units
- Any column containing "stranded" or "reserved" for inactive listings

If `afn_unsellable_quantity` exists, run:

```sql
SELECT
  inv.listing_sku,
  inv.asin,
  inv.afn_unsellable_quantity AS unfulfillable_units,
  COALESCE(prof.profit_per_unit, 0) AS profit_per_unit,
  ROUND(inv.afn_unsellable_quantity * 0.15, 2) AS est_monthly_storage_cost
FROM fba_inventory inv
LEFT JOIN (
  SELECT listing_sku,
    ROUND(SUM(profit) / NULLIF(SUM(units_sold), 0), 2) AS profit_per_unit
  FROM sl_daily_sku_profit
  WHERE venue_id = {venue_id}
    AND date >= CURRENT_DATE - INTERVAL 30 DAY
  GROUP BY listing_sku
) prof ON inv.listing_sku = prof.listing_sku
WHERE inv.venue_id = {venue_id}
  AND inv.afn_unsellable_quantity > 0
ORDER BY unfulfillable_units DESC
```

*(Note: `0.15` is an estimated monthly storage rate per unit — adjust based on product size tier)*

**If these columns don't exist in your Data Hub**: Check Seller Central → Inventory → Manage Inventory Health. Filter for "Unfulfillable" and "Stranded" inventory.

**Output to report**:
- Total unfulfillable units and estimated monthly storage waste
- Recommendation: submit removal orders for units where storage cost > profit per unit
- Link to Seller Central Manage Inventory Health for stranded inventory cleanup

---

## SECTION 5: Slow Mover & Storage Fee Monitoring

**What this shows**: SKUs at risk of aged inventory surcharges, monthly storage costs by SKU, and sell-through rate to identify chronic slow movers.

Step 1 — Monthly storage costs and sell-through rate per SKU:

```sql
SELECT
  p.listing_sku,
  SUM(p.units_sold) AS units_sold_90d,
  AVG(inv.afn_fulfillable_quantity) AS avg_units_on_hand,
  ROUND(
    SUM(p.units_sold) / NULLIF(SUM(p.units_sold) + AVG(inv.afn_fulfillable_quantity), 0) * 100, 1
  ) AS sell_through_rate_pct,
  SUM(p.monthly_storage) AS total_storage_cost_90d,
  SUM(p.profit) AS total_profit_90d,
  CASE
    WHEN SUM(p.monthly_storage) > SUM(p.profit) THEN 'STORAGE EXCEEDS PROFIT'
    WHEN ROUND(SUM(p.units_sold) / NULLIF(SUM(p.units_sold) + AVG(inv.afn_fulfillable_quantity), 0) * 100, 1) < 15 THEN 'SLOW MOVER'
    ELSE 'OK'
  END AS status
FROM sl_daily_sku_profit p
LEFT JOIN fba_inventory inv ON p.listing_sku = inv.listing_sku AND inv.venue_id = {venue_id}
WHERE p.venue_id = {venue_id}
  AND p.date >= CURRENT_DATE - INTERVAL 90 DAY
GROUP BY p.listing_sku
HAVING avg_units_on_hand > 0
ORDER BY
  CASE WHEN SUM(p.monthly_storage) > SUM(p.profit) THEN 0 ELSE 1 END,
  sell_through_rate_pct ASC
```

Step 2 — Check the dedicated `aged_inventory_storage_fees` table (confirmed present in Data Hub):

```sql
SELECT listing_sku, asin,
  DATE_FORMAT(MAX(snapshot_date), '%Y-%m') AS latest_charge_month,
  surcharge_age_tier,
  SUM(qty_charged) AS units_charged,
  SUM(amount_charged) AS total_surcharge,
  SUM(long_time_range_long_term_storage_fee) AS ltsf_amount
FROM aged_inventory_storage_fees
WHERE venue_id = {venue_id}
  AND snapshot_date >= CURRENT_DATE - INTERVAL 180 DAY
GROUP BY listing_sku, asin, surcharge_age_tier
ORDER BY total_surcharge DESC
```

**Output to report**:
- SKUs where monthly storage cost exceeds profit (strong removal candidates)
- Slow movers (sell-through rate below 15% over 90 days)
- Units in 181+ day age tiers at risk of long-term storage fees
- Recommended actions: price promotions, removal orders, or disposal

---

## SECTION 6: High Return Rate Charges

**What this shows**: The actual dollar amount Amazon has already charged you in high return rate fees, broken down by ASIN. This fee was introduced June 24, 2024 and first appeared in seller transaction reports in September 2024. A single ASIN can cost $1,000+/month if left unmonitored.

**How it works**: Amazon looks at each ASIN's return rate vs. the category benchmark. If your ASIN's return rate is too high, Amazon charges you a fee per returned unit. Apparel and shoes are exempt.

**Where to find it outside Seller Labs**: Seller Central → Payments → Payments → Repository → pull the Unified Transaction Report → filter for "FBA customer return fee".

---

### Step 6a: Find the fee charges

First, run `DESCRIBE sl_transactions_pivot` (or your transactions table — ask Claude to check what table name is correct). Look for a column that holds the transaction/fee type and an amount column.

Then query actual fee charges already assessed by Amazon:

```sql
SELECT
  listing_sku,
  detail,
  COUNT(*) AS num_charges,
  SUM(ABS(amount)) AS total_fee_charged,
  DATE_FORMAT(MIN(created_ts), '%Y-%m') AS first_month_charged,
  DATE_FORMAT(MAX(created_ts), '%Y-%m') AS most_recent_month
FROM transactions
WHERE venue_id = {venue_id}
  AND event = 'ServiceFee'
  AND type = 'CustomerReturnHRRUnitFee'
GROUP BY listing_sku, detail
ORDER BY total_fee_charged DESC
```

**Confirmed field name**: `event = 'ServiceFee'` and `type = 'CustomerReturnHRRUnitFee'` — verified across multiple accounts. The `detail` column contains the ASIN and fee period when `listing_sku` is null (common for bulk fee assessments).

**If no results are returned**: Run this to confirm what service fee types exist on the account:

```sql
SELECT DISTINCT event, type, COUNT(*) AS count
FROM transactions
WHERE venue_id = {venue_id}
  AND created_ts >= '2024-06-01'
GROUP BY event, type
ORDER BY count DESC
LIMIT 40
```

Look for `CustomerReturnHRRUnitFee` in the results.

**Fallback — estimate at-risk ASINs via return rate**:

If the transactions table doesn't surface this fee directly, identify ASINs at risk using return rates from `sl_daily_sku_profit`:

```sql
SELECT
  listing_sku,
  SUM(units_sold) AS units_sold_90d,
  SUM(units_returned) AS units_returned_90d,
  ROUND(SUM(units_returned) / NULLIF(SUM(units_sold), 0) * 100, 1) AS return_rate_pct,
  SUM(profit) AS total_profit_90d
FROM sl_daily_sku_profit
WHERE venue_id = {venue_id}
  AND date >= CURRENT_DATE - INTERVAL 90 DAY
GROUP BY listing_sku
HAVING return_rate_pct > 10
ORDER BY return_rate_pct DESC
```

---

### Step 6b: Month-over-month trend

Show whether fees are growing or shrinking over the past 12 months:

```sql
SELECT
  DATE_FORMAT(created_ts, '%Y-%m') AS month,
  COUNT(*) AS num_charges,
  SUM(ABS(amount)) AS total_fee
FROM transactions
WHERE venue_id = {venue_id}
  AND event = 'ServiceFee'
  AND type = 'CustomerReturnHRRUnitFee'
GROUP BY month
ORDER BY month DESC
LIMIT 12
```

---

### Step 6c: Investigate each offending ASIN

For each ASIN with significant charges, pull the daily return pattern to see when it started:

```sql
SELECT
  date,
  units_sold,
  units_returned,
  ROUND(units_returned / NULLIF(units_sold, 0) * 100, 1) AS daily_return_rate_pct,
  profit
FROM sl_daily_sku_profit
WHERE venue_id = {venue_id}
  AND listing_sku = '{offending_sku}'
  AND date >= CURRENT_DATE - INTERVAL 90 DAY
ORDER BY date DESC
```

Then investigate the root cause:
- Read the product's customer reviews for clues about why buyers return it
- Check customer return reason codes in Seller Central → Reports → Returns
- If using Seller Labs: go to SKU Details → Feedback tab for an AI-summarized view of return reasons

---

### Step 6d: Actions and monitoring

Based on findings, recommend one of:

| Finding | Action |
|---|---|
| Misleading listing (wrong size, compatibility, description) | Update listing copy and images |
| Quality defect pattern | Work with supplier to fix product |
| Fee exceeds monthly profit | Consider removing the product |
| Fees declining after prior action | Monitor monthly, no action needed |
| No clear reason | Flag for seller to review Seller Central return reports |

**Monitoring cadence**: Monthly or bi-monthly. This fee compounds — an unmonitored ASIN can quietly cost $500-$1,000+ per month.

---

## SECTION 7: Low-Inventory-Level Fee Exposure

**What this shows**: ASINs at risk of Amazon's low-inventory-level fee (introduced April 2024). Amazon charges this fee per unit fulfilled on standard-size products when your inventory is historically low relative to demand. Both under-stocking and over-stocking are now penalized.

This fee is hard to query directly. Use this proxy to identify at-risk ASINs — flag any standard-size SKU that has had fewer than 14 days of supply in the past 90 days:

```sql
SELECT
  p.listing_sku,
  p.asin,
  COUNT(DISTINCT p.date) AS days_in_period,
  SUM(CASE WHEN inv.afn_fulfillable_quantity < (p.units_sold * 14) THEN 1 ELSE 0 END) AS days_below_14d_supply,
  ROUND(
    SUM(CASE WHEN inv.afn_fulfillable_quantity < (p.units_sold * 14) THEN 1 ELSE 0 END) /
    NULLIF(COUNT(DISTINCT p.date), 0) * 100, 1
  ) AS pct_days_at_risk,
  SUM(p.units_sold) AS units_sold_90d,
  SUM(p.profit) AS total_profit_90d
FROM sl_daily_sku_profit p
LEFT JOIN fba_inventory inv ON p.listing_sku = inv.listing_sku AND inv.venue_id = {venue_id}
WHERE p.venue_id = {venue_id}
  AND p.date >= CURRENT_DATE - INTERVAL 90 DAY
GROUP BY p.listing_sku, p.asin
HAVING pct_days_at_risk > 20
ORDER BY pct_days_at_risk DESC
```

Also check the `fba_fee_preview` table — run `DESCRIBE fba_fee_preview` to see if a low-inventory fee column exists there.

**Output to report**:
- ASINs that were understocked more than 20% of the past 90 days
- Note to seller: verify actual fee charges in Seller Central → Reports → FBA Fee Preview
- Recommendation: increase reorder quantities for these SKUs (cross-reference Section 2/3)

---

## SECTION 8: Summary & Action Plan

After running all sections above, compile a final audit report in this format:

---

### Inventory Audit Summary — [Date] | [Seller Name]

**Overall Health Grade**: [A/B/C/D]
- A = fewer than 10% of SKUs flagged across all sections, no high return fees, storage cost < 5% of profit
- B = 10-25% of SKUs flagged, high return fees < $200/month, storage cost < 10% of profit
- C = 25-50% of SKUs flagged or high return fees $200-$500/month
- D = more than 50% of SKUs flagged or high return fees > $500/month

---

**Section 2 — Inventory Health**
| Metric | Value |
|---|---|
| Total active SKUs | |
| SKUs needing reorder | |
| SKUs out of stock | |
| Top reorder priority | |

**Section 3 — Seasonal Adjustment**
| Metric | Value |
|---|---|
| SKUs with 1.5x+ seasonal uplift | |
| SKUs shifted to REORDER after adjustment | |
| Total adjusted reorder value (profit) | |

**Section 4 — Unfulfillable & Stranded**
| Metric | Value |
|---|---|
| Total unfulfillable units | |
| Estimated monthly storage waste | |
| Removal recommended | |

**Section 5 — Slow Movers & Storage**
| Metric | Value |
|---|---|
| SKUs where storage > profit | |
| Slow movers (sell-through < 15%) | |
| Units in 181+ day age tier | |

**Section 6 — High Return Rate Fees**
| Metric | Value |
|---|---|
| Total fees charged (last 90 days) | |
| Number of offending ASINs | |
| Top offender (ASIN + fee) | |
| Trend (growing/declining) | |

**Section 7 — Low-Inventory Fee Risk**
| Metric | Value |
|---|---|
| SKUs at risk (>20% days understocked) | |

---

**Priority Action Items**

List the top 5-10 actions ranked by financial impact:

1. [REORDER] SKU X — suggested qty Y units — $Z profit value
2. [HIGH RETURN FEE] ASIN X — $Y/month in fees — investigate listing
3. [REMOVE] SKU X — storage exceeds profit, Y unfulfillable units
4. [SEASONAL] SKU X — reorder qty needs to increase Y% for this month
5. [SLOW MOVER] SKU X — sell-through only Y%, consider price promotion
...

---

*Audit generated by Claude Code + Seller Labs MCP*
*Reference: Nhan Dinh's video on return processing fees — https://www.youtube.com/watch?v=n0U93hnwRAA*