Find which products silently kill conversion

Why this matters

“Improve product pages” is the most useless advice in ecommerce. Of your 800 SKUs, maybe 30 are quietly dragging your conversion average down — bad hero image, missing reviews, confusing variant selector, copy that scares people off. The other 770 are fine.

The fix isn’t generic PDP overhauls. It’s identifying which 30 SKUs underperform their category average and fixing those. But nobody runs the per-SKU funnel manually because it means 800 funnel queries.

Agentry’s HogQL builds the funnel for every SKU in one pass, computes the per-category baseline, and ranks the offenders. The whole loop — query, find the laggards, ship product page updates — becomes a weekly cadence instead of a once-a-year project.

What you get

• Per-SKU conversion rate at each funnel step (view → cart → checkout → purchase)
• Per-category baseline so you can spot the SKUs that are 2-3x worse than their cohort
• A published dashboard so the merchandising team can pull up the list every Monday

Walk through it

agentry_analytics_query {
  project_id: "default",
  query: "
    with per_sku as (
      select
        properties.sku as sku,
        properties.category as category,
        countIf(event = 'product_viewed') as views,
        countIf(event = 'order_completed') as orders
      from events
      where timestamp > now() - interval 30 day
        and event in ('product_viewed', 'order_completed')
      group by sku, category
      having views > 100
    ),
    category_mean as (
      select category, sum(orders) / sum(views) as cat_conv
      from per_sku
      group by category
    )
    select
      p.sku, p.category, p.views, p.orders,
      p.orders / p.views as sku_conv,
      c.cat_conv,
      (p.orders / p.views) / c.cat_conv as ratio_to_cat
    from per_sku p
    join category_mean c using (category)
    where (p.orders / p.views) < c.cat_conv * 0.5
    order by ratio_to_cat asc
    limit 25
  "
}

{
  "rows": [
    { "sku": "tee-blk-xxl", "category": "apparel", "views": 4210, "orders":   8, "sku_conv": 0.0019, "cat_conv": 0.041, "ratio_to_cat": 0.046 },
    { "sku": "mug-12oz-grey", "category": "drinkware", "views": 2840, "orders":  14, "sku_conv": 0.0049, "cat_conv": 0.038, "ratio_to_cat": 0.130 },
    { "sku": "sock-stripe-m", "category": "apparel", "views": 1190, "orders":   9, "sku_conv": 0.0076, "cat_conv": 0.041, "ratio_to_cat": 0.184 },
    { "sku": "hat-snap-blk", "category": "headwear", "views":  920, "orders":  11, "sku_conv": 0.0120, "cat_conv": 0.048, "ratio_to_cat": 0.249 }
  ]
}

agentry_publish_query {
  recipe_id: "raw_hogql",
  description: "SKU funnel laggards (30d, < 50% of category)",
  params: { query: "<same query as above>" }
}

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

agentry_analytics_query {
  project_id: "default",
  query: "
    select count() as n, properties.referrer as ref
    from events
    where event = 'product_viewed'
      and properties.sku = 'tee-blk-xxl'
      and timestamp > now() - interval 7 day
    group by ref order by n desc limit 10
  "
}

{
  "rows": [
    { "n": 1804, "ref": "/category/tees" },
    { "n":  920, "ref": "/search?q=black+tee" },
    { "n":  410, "ref": "google.com/ads" }
  ]
}

The output

SKU funnel laggards (30d, conversion < 50% of category)

SKU              Cat        Views    Orders   SKU%   Cat%   Ratio
─────────────────────────────────────────────────────────────────
tee-blk-xxl      apparel    4,210      8     0.19%  4.10%  0.05x ★★★
mug-12oz-grey    drinkware  2,840     14     0.49%  3.80%  0.13x ★★
sock-stripe-m    apparel    1,190      9     0.76%  4.10%  0.18x ★★
hat-snap-blk     headwear     920     11     1.20%  4.80%  0.25x ★

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

Top offender breakdown (tee-blk-xxl):
  Traffic source        Views
  /category/tees        1,804  ← organic browse
  /search?q=black+tee     920  ← high-intent search
  google.com/ads          410  ← paid traffic burning $$

Recommended actions:
  1. Stockout check (mechanical conversion cap)
  2. Hero image — fit on size-appropriate model
  3. Add visible size guide above fold

Setting it up

You need sku and category on both product_viewed and order_completed. If you only have one of the two, the per-category baseline can’t be computed.

// Product 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: "product_viewed",
    distinct_id: visitorId,
    properties: {
      sku: product.sku,
      category: product.category,
      referrer: document.referrer,
    },
  }),
});

// Order confirmation
await fetch(`https://api.agentry.sh/v1/analytics/${PROJECT_ID}/`, {
  method: "POST",
  headers: { /* same headers */ },
  body: JSON.stringify({
    event: "order_completed",
    distinct_id: user.email,
    properties: {
      sku: lineItem.sku,
      category: lineItem.category,
      total: order.total,
    },
  }),
});

For Shopify sites, the events fire from Web Pixel — patch the pixel to forward to Agentry’s /v1/analytics/ endpoint instead of (or in addition to) Shopify Analytics.

Variations

• “Same query, but show me top-converting SKUs too — the ones at 2x+ category average. Those are the ones to feature in email and ads.”
• “Per-SKU breakdown of the cart abandonment step specifically — which SKUs end up in carts but never bought?”
• “Compare conversion by referrer for my top 10 SKUs — does paid traffic convert worse than organic on the same product?”
• “Weekly Routine: post the top 5 laggards to #merch every Monday morning.”