Match supply to demand per region or category

Why this matters

Marketplaces grow on liquidity — the ratio of demand to supply in a market. Too much demand and not enough supply means buyers leave empty-handed and never come back. Too much supply and not enough demand means sellers list, get no orders, and churn off.

The interesting fact about marketplaces is that liquidity is local. Aggregate liquidity is usually fine. Per-region (or per-category) liquidity is where the leaks are: SF has tons of sellers and no buyers; Phoenix has buyers searching with no sellers to fulfill. Aggregating these averages out and tells you nothing.

Most marketplaces never compute per-region liquidity because joining buyer events and seller events across a region dimension is annoying. Agentry has both event types in one table — the cross-tab is a 10-line HogQL pass.

What you get

• A per-region (or per-category) table: buyer events, seller events, ratio, health flag
• A published dashboard the CS / supply / marketing teams can pull up
• A clear list of starved regions (needs supply growth) and saturated regions (needs demand growth)

Walk through it

agentry_analytics_query {
  project_id: "default",
  query: "
    with buyer_events as (
      select properties.region as region, count() as buyer_n
      from events
      where event in ('search', 'listing_viewed', 'add_to_cart', 'order_placed')
        and timestamp > now() - interval 30 day
      group by region
    ),
    seller_events as (
      select properties.region as region, count() as seller_n
      from events
      where event in ('listing_published', 'order_fulfilled')
        and timestamp > now() - interval 30 day
      group by region
    )
    select
      coalesce(b.region, s.region) as region,
      coalesce(b.buyer_n, 0) as buyers,
      coalesce(s.seller_n, 0) as sellers,
      coalesce(b.buyer_n, 0) / nullIf(coalesce(s.seller_n, 0), 0) as ratio
    from buyer_events b
    full outer join seller_events s on b.region = s.region
    having buyers > 100 or sellers > 100
    order by ratio desc
  "
}

{
  "rows": [
    { "region": "Phoenix-AZ",   "buyers": 14200, "sellers":   180, "ratio": 78.89 },
    { "region": "Austin-TX",    "buyers":  8400, "sellers":   320, "ratio": 26.25 },
    { "region": "NYC",          "buyers": 24800, "sellers":  1800, "ratio": 13.78 },
    { "region": "Chicago-IL",   "buyers":  6200, "sellers":   620, "ratio": 10.00 },
    { "region": "LA",           "buyers": 18200, "sellers":  2400, "ratio":  7.58 },
    { "region": "SF-Bay",       "buyers":  4200, "sellers":  3800, "ratio":  1.11 },
    { "region": "Seattle-WA",   "buyers":  2800, "sellers":  4200, "ratio":  0.67 }
  ]
}

agentry_publish_query {
  recipe_id: "raw_hogql",
  description: "Per-region liquidity (30d)",
  params: { query: "<same query as above>" }
}

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

The output

Per-region liquidity (30d)

Region           Buyer events   Seller events   Ratio    Health
─────────────────────────────────────────────────────────────────
Phoenix-AZ          14,200            180        78.9    ★ supply starved
Austin-TX            8,400            320        26.3    ★ supply starved
NYC                 24,800          1,800        13.8    ✓ healthy
Chicago-IL           6,200            620        10.0    ✓ healthy
LA                  18,200          2,400         7.6    ✓ healthy
SF-Bay               4,200          3,800         1.1    ★ demand starved
Seattle-WA           2,800          4,200         0.7    ★★ demand starved (urgent)

Healthy band: 5-15 (varies by category)

Action plan:
  Phoenix, Austin     → ramp seller recruitment (paid + organic)
  Seattle, SF-Bay     → ramp buyer ads / promo spend in-region
  NYC, Chicago, LA    → maintain, no action

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

Suggested next:
  - Per-region seller churn rate (sellers in SF/Seattle probably leaving)
  - Re-run monthly to track shifts as growth programs land
  - Cross-cut by category for narrower targeting

Setting it up

The only requirement is that buyer-side and seller-side events both carry region (or category, or both). The region usually comes from one of:

• The user profile (user.region)
• IP geolocation at request time
• Explicit per-event metadata (shipping address, listing location)

Pick one source per event type and stay consistent. Mixing IP geo on the buyer side with profile-region on the seller side will look like noise:

// Buyer-side example (search event)
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: "search",
    distinct_id: buyer.id,
    properties: {
      region: buyer.region,         // stable from profile
      query: searchTerms,
      category: filters.category,
    },
  }),
});

// Seller-side example (listing published)
await fetch(`https://api.agentry.sh/v1/analytics/${PROJECT_ID}/`, {
  method: "POST",
  headers: { /* same */ },
  body: JSON.stringify({
    event: "listing_published",
    distinct_id: seller.id,
    properties: {
      region: listing.location.region,  // explicit on the listing
      category: listing.category,
      price: listing.price,
    },
  }),
});

For higher-fidelity liquidity, weight events differently — a search is weaker demand signal than order_placed. Adjust the buyer-event SUM to count order_placed * 5 + add_to_cart * 2 + search * 1. Agentry’s HogQL handles weighted sums natively.

Variations

• “Per-category liquidity instead of per-region — same query, swap the group key.”
• “Weighted liquidity: count buyer events as $X = order * 5 + cart * 2 + search * 1. Apply to seller events as listings * 1 + fulfillments * 5.”
• “Time-series: per-region ratio per week over the last 90 days. Which regions are improving/worsening?”
• “Cross-cut region × category. Phoenix might be supply-starved in furniture but saturated in electronics.”