Build a composite account-health dashboard

Why this matters

Every QBR starts with 20 minutes of data wrangling: pull usage from PostHog, ARR from Stripe, support tickets from Zendesk, errors from Sentry. CSMs glue it together in Notion and the doc is stale by next week.

A composite account-health score — one number, 0-100, that combines usage trajectory, revenue size, and friction (support tickets + errors) — is what CS leaders ask for and almost no team builds. The bottleneck is that the data lives in four tools and pulling it requires four logins.

Agentry sees three of those four signals natively (usage, errors, revenue if you send mrr on events). The fourth (support tickets) can be sent as support_ticket_opened events. One HogQL query composes the score; one publish call ships it as a CORS-open dashboard URL.

What you get

• A table: account_name | health_score | trend | top_risk_signal | mrr
• A public dashboard URL — pin it in the CS Slack channel
• A drill-down: “show me everything we know about the bottom 5 accounts”

Walk through it

agentry_analytics_query {
  project_id: "default",
  hogql: "WITH usage AS (SELECT properties.account_id AS aid, count() AS cnt_30d, countIf(timestamp >= now() - INTERVAL 14 DAY) - countIf(timestamp < now() - INTERVAL 14 DAY AND timestamp >= now() - INTERVAL 28 DAY) AS trend FROM events WHERE timestamp >= now() - INTERVAL 30 DAY AND event != '$exception' GROUP BY aid), friction AS (SELECT properties.account_id AS aid, count() AS errors_30d FROM events WHERE event = '$exception' AND timestamp >= now() - INTERVAL 30 DAY GROUP BY aid), revenue AS (SELECT properties.account_id AS aid, argMax(properties.mrr, timestamp) AS mrr, argMax(properties.plan, timestamp) AS plan FROM events WHERE event IN ('plan_changed','seat_count_set','subscription_started') GROUP BY aid), maxes AS (SELECT max(cnt_30d) AS max_usage, max(errors_30d) AS max_friction FROM usage, friction) SELECT r.aid AS account, r.plan AS plan, r.mrr AS mrr, COALESCE(u.cnt_30d, 0) AS events_30d, COALESCE(u.trend, 0) AS trend, COALESCE(f.errors_30d, 0) AS errors_30d, round( 40 * COALESCE(u.cnt_30d, 0) / m.max_usage + 30 * least(greatest(COALESCE(u.trend, 0) / 1000 * 30, -30), 30) + 30 * (1 - COALESCE(f.errors_30d, 0) / nullIf(m.max_friction, 0)), 1 ) AS health_score FROM revenue r LEFT JOIN usage u ON r.aid = u.aid LEFT JOIN friction f ON r.aid = f.aid CROSS JOIN maxes m WHERE r.mrr > 0 ORDER BY health_score ASC LIMIT 30"
}

{
  "rows": [
    { "account": "megacorp",  "plan": "enterprise", "mrr": 5000, "events_30d": 4200,  "trend": -800, "errors_30d": 89, "health_score": 18.5 },
    { "account": "globex",    "plan": "scale",      "mrr": 1875, "events_30d": 6800,  "trend": -210, "errors_30d": 22, "health_score": 41.2 },
    { "account": "tinyco",    "plan": "starter",    "mrr": 250,  "events_30d": 120,   "trend": 30,   "errors_30d": 2,  "health_score": 48.0 },
    { "account": "acme",      "plan": "pro",        "mrr": 750,  "events_30d": 18400, "trend": 4100, "errors_30d": 4,  "health_score": 88.0 },
    { "account": "innovate",  "plan": "scale",      "mrr": 1875, "events_30d": 22100, "trend": 2200, "errors_30d": 8,  "health_score": 91.4 }
  ]
}

agentry_publish_query {
  recipe_id: "account_health_score",
  description: "Account Health Score (composite — usage + trend + friction)",
  params: { sort: "ascending" }
}

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

The output

Account Health Dashboard (composite score, 0-100)

ACCOUNT       PLAN       MRR      USAGE(30d)  TREND       ERRORS  SCORE
megacorp      ent      $5,000        4,200    -800         89    18.5  CRITICAL ★
globex        scale    $1,875        6,800    -210         22    41.2  WARN
tinyco        starter    $250          120     +30          2    48.0  ok
acme          pro        $750       18,400   +4100          4    88.0  healthy
innovate      scale    $1,875       22,100   +2200          8    91.4  champion

Score formula:
  40 pts usage    — events_30d / max(events_30d across accounts)
  30 pts trend    — (last 14d) - (prior 14d), capped at ±30
  30 pts friction — inverse of errors_30d / max(errors_30d)

Public dashboard:
  https://agentry.sh/q/pub_ahs_1?key=agp_xxx
  ↳ Pin in #cs-pulse — refresh on page load

Action queue:
  [megacorp]   CSM call this week. Identify the engineering team that stopped.
  [globex]    Health check email. Trend is decelerating but not crashing.
  [innovate]  Champion candidate — ask for a logo/quote.
  [acme]      Expansion opportunity (see seat-utilization).

Setting it up

You need three signal streams keyed by account_id:

// 1. EVERY analytics event carries account_id
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/1.0",  // REQUIRED — Cloudflare 403s default UAs
  },
  body: JSON.stringify({
    event: "dashboard_view",
    distinct_id: user.email,
    properties: { account_id: user.workspace_id },
  }),
});

// 2. EVERY error carries account_id
await fetch(`https://api.agentry.sh/v1/logs/${PROJECT_ID}/`, {
  method: "POST",
  headers: { /* same */ "User-Agent": "myapp/1.0" },
  body: JSON.stringify({
    message: err.message,
    stack: err.stack,
    user: { id: user.email },
    tags: { account_id: user.workspace_id },  // ← important
  }),
});

// 3. Plan / MRR snapshot from Stripe webhook
// (already covered in tier-downgrade / seat-utilization recipes)

For the score to be meaningful, the rolling window comparison (“last 14d vs prior 14d”) needs 28+ days of data. If you instrument now, the dashboard reaches stable values in ~6 weeks.

Variations

• “Add a column for last_login_days_ago so CS knows if their champion is still active.”
• “Add a column for NPS score from the latest survey response per account.” (see nps-churn-correlation)
• “Weight the score differently — make MRR matter 50% instead of 0%.”
• “Daily cron: post any account whose score dropped >15 pts in the last 24h to #cs-pulse.”