Why this matters
Your overall refund rate is 3.2%. Fine. Average. Industry norm.
But that average masks the truth: 4 SKUs are at 28-35% refund rate, dragging the whole catalog up. The remaining 800 SKUs are at 1.8%. The 4 bad ones cost you $84k in refunds last quarter and ~$50k in support tickets — plus the reputational damage from one-star reviews.
Aggregate refund rate is useless. Per-SKU refund rate is where the actionable signal lives. Most ecommerce tools don’t expose it because joining purchase and refund events per SKU is non-trivial — but it’s a one-liner in HogQL.
What you get
- Per-SKU refund rate ranked highest first, with order volume gate (min 20 orders)
- Quick segmentation of “always-refunded” vs “occasionally-refunded” SKUs
- Dashboard rows the CX team can pull up in a Monday triage
Walk through it
Compute refund rate per SKU over the last 90 days. Filter to SKUs with at least 20 orders. Rank by rate.
Joining orders and refunds on order_id, grouped by sku. Filtering to ≥20 orders so we don’t get sample-size noise.
Endpoint: POST /v1/projects/:project_id/analytics/query
Exact shape: /v1/openapi.json?path=%2Fv1%2Fprojects%2F%7Bproject_id%7D%2Fanalytics%2Fquery&method=post
Custom HogQL goes in the OpenAPI-defined `query` field.
Concept fields:
query: "
with orders as (
select properties.sku as sku, properties.order_id as order_id
from events
where event = 'order_completed' and timestamp > now() - interval 90 day
),
refunds as (
select properties.order_id as order_id
from events
where event = 'refund_issued' and timestamp > now() - interval 90 day
)
select
o.sku,
count(distinct o.order_id) as orders,
count(distinct r.order_id) as refunds,
count(distinct r.order_id) / count(distinct o.order_id) as refund_rate
from orders o
left join refunds r using (order_id)
group by o.sku
having orders >= 20
order by refund_rate desc
limit 30
"{
"rows": [
{ "sku": "wireless-buds-v2", "orders": 142, "refunds": 50, "refund_rate": 0.352 },
{ "sku": "smartwatch-band-l", "orders": 84, "refunds": 24, "refund_rate": 0.286 },
{ "sku": "yoga-mat-pro", "orders": 61, "refunds": 14, "refund_rate": 0.230 },
{ "sku": "blender-mini", "orders": 92, "refunds": 19, "refund_rate": 0.207 },
{ "sku": "tee-blk-xxl", "orders": 41, "refunds": 7, "refund_rate": 0.171 }
]
}Five SKUs are clearly outliers. The wireless buds at 35% refund rate are catastrophic — that’s $4-5k of refunds per month on just that one product, plus margin loss on the units. Two patterns I see:
- Defects:
wireless-buds-v2,blender-mini— high-tech, low-cost products with quality issues. Worth a chat with the supplier. - Sizing:
smartwatch-band-l,tee-blk-xxl— apparel/wearables where customers can’t try before buying. Better photos and a sizing chart usually halve this.
Let me prepare the dashboard rows.
Endpoint: POST /v1/projects/:project_id/analytics/query
Exact shape: /v1/openapi.json?path=%2Fv1%2Fprojects%2F%7Bproject_id%7D%2Fanalytics%2Fquery&method=post
Custom HogQL goes in the OpenAPI-defined `query` field.
Concept fields:
query: "<same query as above>"{ "rows": [/* dashboard rows */], "row_count": 25 }Dashboard data ready. I’d suggest pulling wireless-buds-v2 off the homepage hero until you investigate — at that refund rate you’re paying for the customer acquisition twice.
The output
Refund rate by SKU (90d, min 20 orders)
SKU Orders Refunds Rate Cohort
─────────────────────────────────────────────────────────
wireless-buds-v2 142 50 35.2% ★★★ pull from hero
smartwatch-band-l 84 24 28.6% ★★ sizing — fix chart
yoga-mat-pro 61 14 23.0% ★★ defects — check QC
blender-mini 92 19 20.7% ★★ defects — check supplier
tee-blk-xxl 41 7 17.1% ★ sizing
─────────────────────────────────────────────────────────
catalog avg ─ ─ 3.2%
Aggregate: 5 SKUs (0.6% of catalog) drive 41% of total refunds.
Public view:
Use Agentry's returned public_url after publishing a real query blueprint.
Suggested next investigations:
- inventory-sync-errors: are these "wrong item shipped" mistakes?
- add-to-cart-funnel: do these SKUs also convert poorly? (double signal)Setting it up
Two events on the order lifecycle, both with order_id so they can be joined:
In the browser snippet, window.AGENTRY_PUBLIC_API_KEY represents the public key injected through your framework’s browser-env convention; replace that placeholder with your framework’s actual public accessor.
// On the order confirmation page
await fetch(`https://api.agentry.sh/v1/analytics/`, {
method: "POST",
headers: {
"Authorization": `Bearer ${window.AGENTRY_PUBLIC_API_KEY}`,
"Content-Type": "application/json",
},
body: JSON.stringify({
event: "order_completed",
distinct_id: user.email,
properties: { order_id: order.id, sku: lineItem.sku, qty: lineItem.qty },
}),
});The refund webhook is a trusted server boundary, so it uses a separate
AGENTRY_SERVER_API_KEY rather than copying the browser headers:
// In the refund webhook handler (Stripe / Shopify / custom)
await fetch(`https://api.agentry.sh/v1/analytics/`, {
method: "POST",
headers: {
"Authorization": `Bearer ${process.env.AGENTRY_SERVER_API_KEY}`,
"Content-Type": "application/json",
"User-Agent": "myshop-refunds/1.0",
},
body: JSON.stringify({
event: "refund_issued",
distinct_id: customer.email,
properties: {
order_id: refund.order_id,
amount: refund.amount,
reason: refund.reason, // useful for grouping
},
}),
});If a single order has multiple SKUs, emit one order_completed event per line item — the join above expects one row per SKU. (Alternative: store line_items as an array and unnest in the query.)
Variations
- “Per-SKU refund rate broken down by refund_reason — separate ‘defective’ from ‘changed my mind’.”
- “Last 30 days vs prior 30 days per SKU — which products are getting worse?”
- “For my top 5 worst-refunding SKUs, pull the actual refund messages from CX. Are there themes?”
- “Compare the worst five SKUs with the prior week and draft a merch summary for human review.”