return-refund-analyzer
Skill8 starsChanged 45 days agoMITView on GitHub
Find the SKUs bleeding the most margin to returns, why they come back (the real return reasons, bucketed into product / listing / sizing / delivery), and the fix for each - ranked by money lost, not by return rate, and filtered so you chase the actionable returns, not the noise. Live from DataDoe, read-only. Use for "returns", "refunds", "return rate", "why are people returning", "return reasons", "which products get returned", "returns costing me money", or "reduce returns".
In this repository26 other files it gives its agents.
- amazon-asin-search-auditorSkill
- amazon-listing-optimizerSkill
- buy-box-loss-root-causeSkill
- create-amazon-reconciliation-dashboardSkill
- create-orders-managerSkill
- daily-account-health-checkSkill
- daily-performance-health-checkSkill
- fba-inventory-ledger-reportSkill
- fba-stock-restock-plannerSkill
- keyword-rank-sqp-trackerSkill
- net-profit-pl-analyzerSkill
- ppc-asin-acos-alertSkill
- ppc-bid-optimizer-applySkill
- ppc-campaign-acos-alertSkill
- ppc-negative-keyword-applierSkill
- ppc-profit-bid-applySkill
- ppc-wasted-spend-watchdogSkill
- promotions-managerSkill
- restock-priority-alertSkill
- sales-movers-scannerSkill
- suppressed-inactive-listings-checkSkill
- vendor-inventory-reportSkill
- vendor-performance-dashboardSkill
- vendor-pnl-dashboardSkill
- weekly-business-reviewSkill
- weekly-sales-briefingSkill
The file
We split this skill into the rules it gives an agent
3 rules. About 0 are common, 0 are distinctive, and 3 are very specific to this project.
≈0
Common · estimate
Many files like this have a rule of this kind.
≈0
Distinctive · estimate
This author's own choices. Worth a look.
3
Very specific
Tied to their project's names, files or tools. Probably skip.
- Common
- Distinctive
- Very specific
- Very specific: line 153
Prefer pulling grouped and filtering client-side - you need the full per-SKU reason histogram anyway, so one grouped pull is the natural …
- Very specific: line 255
Read-only (analysis). Any listing edit that follows (fixing images/size chart) is a separate write skill via AMAZON_LISTINGS_UPDATE …
- Very specific: line 262
Prefer pulling amazon_returns grouped and filtering client-side (you need the full per-SKU reason histogram regardless).
≈ “Common” is an estimate: at least 1 in 5 of the 372,429 skill files we read have a rule of the same kind, not the same words. “Very specific” is our guess from the words: it names a file, a command, a setting or the project itself.
Files
Return & Refund Analyzer
Finds the SKUs bleeding the most margin to returns, tells you why they come back (the actual return reasons, not just the rate), and routes each to the right fix - product/QC, listing accuracy, sizing, or fulfilment. It ranks by money lost, not by return rate, so a high-volume hero SKU with a "small" rate outranks a tiny SKU that returns half the time. Live from DataDoe, read-only. No spreadsheets.
When to use this
- Margin looks thin despite good sales - returns are a quiet, common cause.
- A SKU's reviews or refunds are creeping up and you want the reason.
- Weekly/monthly quality review, or before scaling ad spend on a SKU (returns kill ROI).
- Sourcing or listing decisions - is this a product defect or a listing-accuracy problem?
- Trigger phrases: "returns", "refunds", "return rate", "why are people returning", "return reasons", "which products get returned", "returns costing me money", "reduce returns".
The framework. Rank by cost, diagnose the reason, route the fix
- Return rate per SKU =
return_units/total_units_soldover the window, both fromamazon_profit_by_sku_and_date(the denominator is the units actually sold;return_unitsis dated on return receipt/request, the same basis asamazon_returns, and is that table's summedquantity- returned units, not a count of return events). Compare each SKU to the catalog median - flag the outliers, not everything. Return-lag guard: returns lag the sale, so a return this window can belong to a sale from a prior window - if a SKU's returns exceed its in-window units sold (rate > 100%) the rate is a lag artifact, not a real >100% return rate. Do NOT report it as a percentage; mark it "lag-inflated" and rank it by cost / returned units instead. - Return cost per SKU = the signed
refund_costflipped to a positive cost + return handling/label cost.refund_costalready includes the return-COGS adjustment (the scheme: "Return COGS is added on the return date"), so do NOT addreturn_cogson top - show it as information only. Rank by cost, not rate - that is where the money actually is. - Reason - bucket it, don't read raw enums.
amazon_return_reasoncarries ~30 values (more than it looks), and FBM prefixes them - so read the actual reason histogram first and route from it, and normalize before matching:- Strip channel prefixes
CR-andAMZ-PG-before matching. FBM prefixes the enum:CR-DEFECTIVE->DEFECTIVE,CR-ORDERED_WRONG_ITEM-> listing, etc. The twoAMZ-PG-shorthands map by meaning:AMZ-PG-BAD-DESC-> listing,AMZ-PG-APP-TOO-*-> sizing. - Match by prefix/family, not a fixed exact list (the enum grows). Buckets:
- Product / quality (
DEFECTIVE,QUALITY_UNACCEPTABLE,MISSING_PARTS,EXTRA_ITEM,DAMAGED_BY_*e.g.DAMAGED_BY_CARRIER/DAMAGED_BY_FC) -> supplier / QC / packaging fix. (actionable) - Listing accuracy (
NOT_AS_DESCRIBED,NOT_COMPATIBLE,ORDERED_WRONG_ITEM,SWITCHEROO,PRODUCT_NOT_*e.g.PRODUCT_NOT_ITALIAN/PRODUCT_NOT_SPANISH) -> the page misleads or variations are unclear. Fix images, bullets, dimensions, and especially colour/size/variation clarity. (For multi-variation SKUs,NOT_COMPATIBLEORDERED_WRONG_ITEMusually means the buyer picked the wrong variant - clarify the variation picker + images.) (actionable)
- Sizing / fit (
APPAREL_TOO_SMALL,APPAREL_TOO_LARGE,APPAREL_STYLE,APPAREL_*) -> add/repair the size chart and set fit expectations in bullets + images. (actionable) - Delivery / fulfilment (
UNDELIVERABLE_*e.g.UNDELIVERABLE_UNKNOWN/UNDELIVERABLE_REFUSED,MISSED_ESTIMATED_DELIVERY,NEVER_ARRIVED) -> carrier / address / logistics, not a listing or product problem (flag for ops). (non-actionable) - Noise / low actionability (
NO_REASON_GIVEN,UNWANTED_ITEM,MISORDERED,FOUND_BETTER_PRICE,UNAUTHORIZED_PURCHASE) -> do NOT over-invest; count as noise. (non-actionable) - Unclassified (catch-all) - any reason matching none of the above (a new/unknown enum value). Do NOT drop it silently: report a small "unclassified: N (values...)" line so new reasons surface and get bucketed next run. The dominant actionable bucket is the diagnosis and picks the fix.
- Strip channel prefixes
- Trend + channel: is the SKU's return rate rising, and is it FBA or FBM
(
amazon_fulfillment_channel)? A rising quality/defect bucket is an early product signal; an FBM label-cost drain is a shipping/policy signal. Report the top cost SKUs with their dominant actionable bucket and the one lever that moves it - and separately note how much of the volume is unfixable noise.
Configuration
- MCP base:
https://mcp.datadoe.com/mcp/v1 - Data sources (resolve each by table name with
exports_sources_get):amazon_returns- the reason engine. Per return:sku,child_asin,date(return request date),amazon_return_reason(the enum),amazon_fulfillment_channel(FBA/FBM),amazon_return_request_status, and for FBM:amazon_return_refunded_amount,amazon_return_label_cost,amazon_return_label_to_be_paid_by. Money columns (amazon_return_refunded_amount,amazon_return_label_cost) are FBM-only / nullable - FBA returns carry no money here. So FBA per-return cost is always an estimate from sales/COGS (optionally settlements), never actuals from this table - and FBA is the bulk of most accounts.Profit by SKU & Date(amazon_profit_by_sku_and_date) - the rate and money engine. Persku/child_asin/date:total_units_sold(the rate denominator),return_units(returned units on return receipt/request date - the same basis asamazon_returns),refund_cost(signed: refund outflows negative, fee reversals positive, on posting date),return_cogs(item cost of the returned units; informational - already folded intorefund_cost; not part ofprofit),cogs_total/cogs_item,total_sales,currency. Premium export (5 AI Tokens instead of 2 - nothing else differs); part of the always-on default dataset, so it is never disabled.requiresDatePeriod: true-exports_createmust send top-levelfrom/to. Refreshed intraday, sales lag ~1 day. Source of truth:amazon_returnsfor the reasons, channel and per-return detail; the profit table for the rate denominator and the money (refund_cost,return_cogsper SKU). Usereturn_unitsfrom the profit table as the rate numerator so numerator and denominator share one table; if it diverges from the summedamazon_returns.quantityby more than a few %, say so rather than mixing them. Compare units with units: one return event can carry several units, so a count of return rows is not the same measure.amazon_settlements_with_cogs(optional, for exact money) - actual refund amounts and returned-item fees settled in the window; use it to firm up the cost estimate and to confirm whether a returned unit was reimbursed/resellable.
- Currency/marketplace: read
marketplace_country_code; refund amounts are in each marketplace's currency - group and report per currency, never sum across currencies. - Window: use a full trailing window (>= 30-60 days). Returns lag the sale by days to
weeks, so a too-short window understates the true rate. Pull returns and the profit
table over the same
from/to- a denominator from a different window makes the rate meaningless. - Returns-side completeness:
return_units(andamazon_returnsrows) lag the sales-complete day by about a week (measured: latest return 6 days before the last sales-complete day, dailyreturn_units= 0 after it). End the rate window on the last day withreturn_units > 0that is followed only by zero days, otherwise the rate reads ~10% low; say which day you used. - Completeness check (~1-day lag). The profit table is intraday but sales lag about
a day: the newest date is a partial day. Detect the last complete day from the daily
total_units_sold(walk back from the tail until a day is at/above ~60% of the median day) and end both windows there; never let a partial day shrink the denominator.
Step-by-step workflow (MCP-native)
sellers_and_vendors_list-> pick the seller.exports_sources_get-> confirmamazon_returnsisenabledand resolveamazon_profit_by_sku_and_date(always-on, premium; settlements optional).- Returns by SKU + reason:
exports_createonamazon_returnsfor the window,groupBy [child_asin, sku, amazon_return_reason, amazon_fulfillment_channel],sum(quantity) as returned_units(+countasreturn_eventsif useful, +sum amazon_return_refunded_amount,sum amazon_return_label_costfor FBM). Aggregate to per-SKU returned units and a unit-weighted per-SKU reason histogram, and comparereturned_unitswithreturn_unitsfrom step 4. One event can carry several units (one UK account, 60 days: 2,640 return rows held 2,650 units - one row of 10, one of 2), so a row count under-states units and trips a false divergence warning. Prefer pulling grouped and filtering client-side - you need the full per-SKU reason histogram anyway, so one grouped pull is the natural shape. (Server-sidesku/child_asinfiltering onamazon_returnshas been unreliable in the past; if you do filter server-side, verify it returns rows before relying on it.) - Units sold, rate + money:
exports_createonamazon_profit_by_sku_and_datefor the samefrom/toas step 3 (top-level dates are required),groupBy [child_asin, sku, currency],sum total_units_sold,sum return_units,sum refund_cost,sum return_cogs,sum total_sales(distinct aliases), CSVlimit 5000and page withskip(a 60-day pull grouped by SKU and ASIN exceeded 5,000 groups - 5,709 - on a ~3,000-SKU account). Also roll up perchild_asinfor the ASIN view. Return rate per SKU =return_units/total_units_sold; compute the catalog median from it. The denominator must come from this table - it is the shipped-units count the profit reconciles to; a sales/traffic-style source under-counts units and inflates rates. - Cost per SKU:
refund_cost(flip the sign for display - outflows are negative)- return handling/label cost (FBM:
amazon_return_label_cost). Do not addreturn_cogs:refund_costalready carries the return-COGS adjustment by disposition (positiverefund_costrows exist - returns of sellable units with no refund), so adding it double-counts (observed: top SKU GBP 905 vs ~158 net). Showreturn_cogsas context only; use settlements if pulled for exactness. For FBA returns there is no per-return money inamazon_returns- the FBA money comes from the profit table'srefund_cost/return_cogs(or settlements), never from the returns table.
- return handling/label cost (FBM:
- Rank by cost, attach the dominant reason + channel + trend, and map each to its fix lever (product / listing / sizing / fulfilment / low-actionability).
Output format
Return & Refund Analyzer - {marketplace} - last {N} days (returns lag sales; trailing window)
SKU / ASIN units ret% returns cost top bucket (share) -> fix
{sku} {u} {r}% {n} {cur}{c} Listing accuracy (61%) clarify variation + images
{sku} {u} lag* {n} {cur}{c} Product/quality (55%) supplier / QC
{sku} {u} {r}% {n} {cur}{c} Sizing (44%) add size chart
*lag = returns exceed in-window units sold (return-lag artifact); ranked by cost, not rate.
units = total_units_sold, returns = returned units (sum of quantity), ret% = return_units / units, cost = -refund_cost + label cost (Profit by SKU & Date; return_cogs shown as context)
Catalog return rate: {median}% · Total refund cost in window: {cur}{sum} (return_cogs: {cur}{c})
Actionable vs non-actionable: {a}% actionable (product/listing/sizing) · {x}% non-actionable (delivery + noise) [unclassified: {u}]
Biggest lever: {sku} - {bucket} ~{cur}{c}/window -> {fix}
Rising: {sku} return rate {was}% -> {now}%Worked example (illustrative)
A hero SKU sells 2,000 units at a 6% return rate; a novelty SKU sells 40 units at 45%. Ranked by rate the novelty looks worst - but ranked by cost the hero (120 returns x price + COGS) dwarfs it, so it leads. Its reason histogram is 55% "defective" -> that's a product/QC fix, and worth a supplier conversation, not a copy tweak. A second SKU returns mostly "not as described" -> the listing over-promises; fix the images and bullets. A third is "unwanted item" -> low actionability, leave it. The output is a short, money-ranked list where each line already says what to do.
Quality self-check
- Did I rank by return COST, not return rate?
- Did I compare each SKU's rate to the catalog median (flag outliers, not everything)?
- Did I read the actual
amazon_return_reasonhistogram and route the correct fix per dominant reason (product vs listing vs sizing vs fulfilment)? - Did I normalize reasons (strip
CR-/AMZ-PG-prefixes, match by family) so unclassified drops to ~0, and surface any leftover unknown values rather than dropping? - Did I use a trailing window long enough that return lag doesn't understate the rate?
- Is the denominator
total_units_soldfromamazon_profit_by_sku_and_date, pulled over the samefrom/toas the returns, with both windows ending on the last complete day (~1-day lag)? - Did I take the money (
refund_cost,return_cogs) from the profit table and the reasons fromamazon_returns- and say which table each number came from? - Did I compare
return_unitswith the summedquantityofamazon_returns(units), not with a count of return rows, and weight the reason histogram by units? - Did I keep each marketplace in its own currency?
- Did I separate low-actionability reasons (unwanted item, misordered) from fixable ones instead of inflating the "problem"?
Common mistakes
- Ranking by return rate and chasing a tiny SKU while a hero SKU quietly loses more.
- Counting return rows as returned units - one return event can carry several units; sum
quantity, or the comparison withreturn_unitsreports a divergence that is not there. - Reporting a rate with no reason - the reason is the whole point (it picks the fix).
- Reporting a return rate above 100% as if real - that is the return-lag artifact (returns from earlier sales vs in-window units); flag it and rank by cost instead.
- Reading raw reason enums instead of bucketing - the actionable share (product / listing / sizing) is what matters; unwanted / no-reason / undeliverable is noise.
- Matching reasons against a fixed exact list - FBM prefixes (
CR-,AMZ-PG-) and new enum values then fall through as unclassified; strip prefixes, match by family, and surface a catch-all so nothing is silently dropped. - Blaming the product when the bucket is listing-accuracy (
NOT_COMPATIBLE/ORDERED_WRONG_ITEM= clarify variations/images, not a QC fix). - Treating delivery reasons (
UNDELIVERABLE_*) as a listing/product problem - that's ops. - Too short a window - returns lag the sale, so recent-only data understates the rate.
- Building the rate on a units count from a sales/traffic-style source instead of
total_units_sold- on one UK account such a source held 49 of 60 days and ~17% fewer units than the profit table, so every return rate came out 15-20% too low. - Mismatched windows - returns over 60 days against units over 49 - or a denominator that includes the newest partial day.
- Reading
refund_costas a positive number - it is signed (outflows negative); flip it for display; it already includes the return-COGS adjustment, so never addreturn_cogsto it. - Summing refund amounts across marketplaces/currencies into one number.
Notes
- Read-only (analysis). Any listing edit that follows (fixing images/size chart) is a
separate write skill via
AMAZON_LISTINGS_UPDATE(dryRun-gated). - Exact refund money is most precise from settlements;
refund_cost/return_cogsonamazon_profit_by_sku_and_dategive a solid per-SKU figure when settlements aren't pulled - state which you used. amazon_returnsmoney columns are FBM-only; FBA per-return money comes from the profit table (or settlements), never from the returns table.- Prefer pulling
amazon_returnsgrouped and filtering client-side (you need the full per-SKU reason histogram regardless). Server-sidesku/child_asinfiltering has been unreliable historically; verify it returns rows before depending on it. - A DataDoe skill, built on DataDoe returns, Profit by SKU & Date (units, refunds, COGS) and settlement data.
Does it work?
Reviews from people and from AI agents that ran it: what they used it for, and whether it worked.
No reviews yet
People and AI agents can review whether this works in practice. Say what you used it for, and whether it worked.
- It worked
- Partly
- It didn't
Using an agent? It can post a review for you after it has run the file: the MCP tool registry_write, action report. How to connect one.
Your trail
Files you open will collect here, so you can find them again.
Open two or more, and you can remix the rules you like from each into one list. Kept in this browser only.
Now do this for your own.
Bring your CLAUDE.md, AGENTS.md or skills. agentleFS shows which of your rules are common, which are yours and which only fit one project, and keeps one version your agents read everywhere.

