Find accounts paying for 10 seats but using 3

Why this matters

Per-seat pricing is the most common B2B SaaS model and the most under-monitored. Acme buys 25 seats during the sales cycle, the original champion onboards 8 of them, and 17 seats sit dormant for the next 11 months. At renewal they ask “why are we paying for 25?” and downgrade to 8 — you lose 60% of that ARR overnight.

Two failure modes prevent teams from catching this:

1. Aggregating across accounts hides it. Total active users went up; nobody notices a single account where utilization dropped from 80% → 30%.
2. No one runs the query. Mixpanel can compute it but you have to build a custom dashboard per customer, and nobody does.

Agentry’s one prompt: utilization per account, sorted by lost-opportunity ARR. CS gets a call sheet; product gets a “why is the second seat hard to onboard” question.

What you get

• A ranked list of paid accounts by utilization gap (paid_seats − active_users in last 30d)
• ARR per account so you can prioritize by deal size
• A bucket: at risk (low and dropping), expansion candidates (high and ceiling), dormant (low and flat)

Walk through it

agentry_analytics_query {
  project_id: "default",
  hogql: "WITH seats AS (SELECT properties.account_id AS account_id, argMax(properties.seat_count, timestamp) AS paid_seats, argMax(properties.mrr, timestamp) AS mrr FROM events WHERE event = 'seat_count_set' GROUP BY account_id), active AS (SELECT properties.account_id AS account_id, uniq(distinct_id) AS active_users FROM events WHERE timestamp >= now() - INTERVAL 30 DAY GROUP BY account_id) SELECT s.account_id AS account, s.paid_seats AS paid_seats, COALESCE(a.active_users, 0) AS active_users, s.mrr AS mrr, round(COALESCE(a.active_users, 0) * 100.0 / s.paid_seats, 1) AS utilization_pct FROM seats s LEFT JOIN active a ON s.account_id = a.account_id WHERE s.paid_seats >= 5 ORDER BY (s.paid_seats - COALESCE(a.active_users, 0)) * s.mrr DESC LIMIT 20"
}

{
  "rows": [
    { "account": "acct_megacorp",  "paid_seats": 50, "active_users": 14, "mrr": 5000, "utilization_pct": 28.0 },
    { "account": "acct_globex",    "paid_seats": 25, "active_users": 8,  "mrr": 1875, "utilization_pct": 32.0 },
    { "account": "acct_acme",      "paid_seats": 10, "active_users": 9,  "mrr": 750,  "utilization_pct": 90.0 },
    { "account": "acct_tinyco",    "paid_seats": 5,  "active_users": 1,  "mrr": 250,  "utilization_pct": 20.0 }
  ]
}

agentry_get_distinct_id_summary {
  project_id: "default",
  account_id: "acct_megacorp",
  windows: [
    { name: "month_1", from_relative: "120d ago", to_relative: "90d ago" },
    { name: "month_2", from_relative: "90d ago",  to_relative: "60d ago" },
    { name: "month_3", from_relative: "60d ago",  to_relative: "30d ago" },
    { name: "month_4", from_relative: "30d ago",  to_relative: "now" }
  ]
}

{
  "windows": {
    "month_1": { "active_users": 32 },
    "month_2": { "active_users": 24 },
    "month_3": { "active_users": 18 },
    "month_4": { "active_users": 14 }
  }
}

The output

Seat Utilization Report (30d)

⚠ AT RISK (utilization dropping)
   megacorp   50 paid · 14 active · 28%  · $5,000/mo
              Trend: 32 → 24 → 18 → 14 (4-mo decline)
              ↗ $3,600/mo at risk · CS call today

   globex     25 paid ·  8 active · 32%  · $1,875/mo
              ↗ $1,275/mo at risk · usage review needed

   tinyco      5 paid ·  1 active · 20%  · $250/mo
              ↗ Solo founder bought 5 seats, never onboarded team
                Low ROI for CS effort, low risk dollar

✓ EXPANSION CANDIDATES (saturated)
   acme       10 paid ·  9 active · 90%  · $750/mo
              ↗ Add 5-seat upgrade conversation
              ↗ "Looks like the team is full — should we add seats for new hires?"

TOTAL ARR AT RISK:        ~$4,875/mo  ($58.5k/yr)
EXPANSION OPPORTUNITY:    ~$375/mo    ($4.5k/yr +)

Setting it up

Two events required:

1. seat_count_set whenever Stripe updates the quantity:

// In your Stripe webhook for customer.subscription.updated
const seatCount = subscription.items.data.find(i => i.price.id === SEAT_PRICE_ID)?.quantity ?? 1;

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": "myapp-stripe/1.0",  // REQUIRED — Cloudflare 403s default UAs
  },
  body: JSON.stringify({
    event: "seat_count_set",
    distinct_id: subscription.metadata.account_id,  // account-level distinct_id
    properties: {
      account_id: subscription.metadata.account_id,
      seat_count: seatCount,
      mrr: subscription.items.data[0].price.unit_amount * seatCount / 100,
    },
  }),
});

2. account_id on every per-user event:

// Every user-driven analytics event needs to know which account it belongs to
await fetch(`https://api.agentry.sh/v1/analytics/${PROJECT_ID}/`, {
  method: "POST",
  headers: { /* same */ "User-Agent": "myapp/1.0" },
  body: JSON.stringify({
    event: "dashboard_view",
    distinct_id: user.email,
    properties: {
      account_id: user.workspace_id,  // ← critical for grouping
      role: user.role,
    },
  }),
});

With both in place, the agent can answer utilization questions without any per-account dashboard setup.

Variations

• “Show seat utilization split by role — are admins active but ICs dormant?”
• “Find accounts where utilization dropped >20 points in the last quarter (faster decline).”
• “Publish this as a public dashboard our CS team can check daily.” (uses agentry_publish_query)
• “For each at-risk account, draft a personalized re-engagement email.”