Find the products driving refunds

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
• A published dashboard the CX team can pull up in a Monday triage

Walk through it

agentry_analytics_query {
  project_id: "default",
  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 }
  ]
}

agentry_publish_query {
  recipe_id: "raw_hogql",
  description: "Refund rate by SKU (90d, min 20 orders)",
  params: { query: "<same query as above>" }
}

{ "publication_id": "pub_refund_sku", "public_url": "https://agentry.sh/q/pub_refund_sku?key=agp_…" }

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 dashboard:
  https://agentry.sh/q/pub_refund_sku?key=agp_xxx

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:

// On the order confirmation page
await fetch(`https://api.agentry.sh/v1/analytics/${PROJECT_ID}/`, {
  method: "POST",
  headers: {
    "Authorization": `Bearer ${process.env.AGENTRY_DSN}`,
    "Content-Type": "application/json",
    "User-Agent": "myshop/1.0",  // REQUIRED — Cloudflare 403s default UAs
  },
  body: JSON.stringify({
    event: "order_completed",
    distinct_id: user.email,
    properties: { order_id: order.id, sku: lineItem.sku, qty: lineItem.qty },
  }),
});

// In the refund webhook handler (Stripe / Shopify / custom)
await fetch(`https://api.agentry.sh/v1/analytics/${PROJECT_ID}/`, {
  method: "POST",
  headers: { /* same headers */ },
  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?”
• “Weekly Routine: post the worst 5 SKUs to #merch every Monday with the delta vs last week.”