Track buyer-seller dispute frequency per seller

Why this matters

Marketplace trust is built on the absence of bad actors. A small percentage of sellers cause a disproportionate share of disputes — fraudulent listings, broken products shipped, post-purchase ghosting. Letting them operate burns trust on the buyer side and reputation across the platform.

The classic mistake is reactive triage: wait for buyers to complain loudly enough that CS escalates a specific seller. By the time that happens, 50 buyers have had bad experiences and your refund liability is real.

The proactive version: rank every seller by dispute rate, look at the top decile every Monday, intervene before buyers notice. This recipe is the rank.

What you get

• Per-seller dispute rate (disputes ÷ transactions) over a 90-day window
• Volume threshold so you don’t false-positive on sellers with 3 transactions
• The top decile flagged for proactive CS review
• A published dashboard the trust & safety team can pull up

Walk through it

agentry_analytics_query {
  project_id: "default",
  query: "
    with orders as (
      select properties.seller_id as seller, count() as txns
      from events
      where event = 'order_completed' and timestamp > now() - interval 90 day
      group by seller
    ),
    disputes as (
      select properties.seller_id as seller, count() as disp
      from events
      where event = 'dispute_opened' and timestamp > now() - interval 90 day
      group by seller
    )
    select
      o.seller,
      o.txns,
      coalesce(d.disp, 0) as disputes,
      coalesce(d.disp, 0) / o.txns as dispute_rate
    from orders o
    left join disputes d using (seller)
    where o.txns >= 20
    order by dispute_rate desc
    limit 100
  "
}

{
  "rows": [
    { "seller": "s_8421", "txns":  142, "disputes": 38, "dispute_rate": 0.268 },
    { "seller": "s_1840", "txns":   62, "disputes": 14, "dispute_rate": 0.226 },
    { "seller": "s_3110", "txns":   84, "disputes": 18, "dispute_rate": 0.214 },
    { "seller": "s_7720", "txns":   42, "disputes":  8, "dispute_rate": 0.190 },
    { "seller": "s_4421", "txns":  220, "disputes": 38, "dispute_rate": 0.173 }
  ]
}

agentry_analytics_query {
  project_id: "default",
  query: "
    with rates as (
      select properties.seller_id as seller, coalesce(d, 0) / o as rate
      from (
        select properties.seller_id, count() as o,
          countIf(event = 'dispute_opened') as d
        from events
        where event in ('order_completed', 'dispute_opened')
          and timestamp > now() - interval 90 day
        group by properties.seller_id
        having count() >= 20
      )
    )
    select
      avg(rate)      as avg_rate,
      quantile(0.5)(rate)  as p50,
      quantile(0.9)(rate)  as p90,
      quantile(0.99)(rate) as p99,
      max(rate)      as max_rate,
      count() as n_sellers
    from rates
  "
}

{
  "rows": [
    { "avg_rate": 0.022, "p50": 0.014, "p90": 0.062, "p99": 0.184, "max_rate": 0.268, "n_sellers": 4280 }
  ]
}

agentry_publish_query {
  recipe_id: "raw_hogql",
  description: "Seller dispute rates — top decile (90d)",
  params: { query: "<first query above>" }
}

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

The output

Seller dispute rates — top decile (90d)

Seller       Txns      Disputes    Rate     Decile    Action
─────────────────────────────────────────────────────────────────
s_8421        142         38       26.8%    P99       ★★★ deep-dive
s_1840         62         14       22.6%    P99       ★★ review
s_3110         84         18       21.4%    P99       ★★ review
s_7720         42          8       19.0%    P99       ★★ review
s_4421        220         38       17.3%    P99       ★ contact

Platform stats:
  Median dispute rate:    1.4%
  P90:                    6.2%
  P99:                   18.4%
  Sellers analyzed:      4,280

Recommended workflow:
  - Daily: review P99 (top ~42 sellers); confirm legitimacy
  - Weekly: outreach to P90-P99 (next ~380 sellers); offer training
  - Auto-flag: Routine pages CS when any new seller crosses 15%

Public dashboard:
  https://agentry.sh/q/pub_disp?key=agp_xxx

Setting it up

Two events, both with seller_id and order_id:

// On order confirmation
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": "marketplace/1.0",  // REQUIRED — Cloudflare 403s default UAs
  },
  body: JSON.stringify({
    event: "order_completed",
    distinct_id: buyer.id,
    properties: {
      order_id: order.id,
      seller_id: listing.seller_id,
      gmv: order.amount,
    },
  }),
});

// In the dispute creation handler
await fetch(`https://api.agentry.sh/v1/analytics/${PROJECT_ID}/`, {
  method: "POST",
  headers: { /* same */ },
  body: JSON.stringify({
    event: "dispute_opened",
    distinct_id: buyer.id,
    properties: {
      order_id: dispute.order_id,
      seller_id: dispute.seller_id,
      reason: dispute.reason,  // "not_received" | "not_as_described" | "fraudulent" | "other"
      amount: dispute.amount,
    },
  }),
});

The min 20 transactions filter in the query is important — without it, a seller with 2 transactions and 1 dispute shows up as 50% and dominates the ranking with no signal.

Variations

• “Same query split by dispute reason. ‘Not received’ vs ‘not as described’ point to different bad-actor types.”
• “Trend per seller — show me sellers whose dispute rate jumped in the last 14 days vs prior 76. Newly-bad actors.”
• “Cohort the rate by seller tenure — do new sellers dispute more, or seasoned sellers?”
• “Set up a Routine that auto-pauses any seller crossing 25% dispute rate pending CS review.”