Find quiet paying accounts before they churn

Why this matters

Paying customers who stop logging in are 60-90 days from cancellation. The CS-team folklore number is “if a paid account goes 14 days without activity, they’re 4-5x more likely to churn at renewal.” Saving them requires noticing within those 14 days.

Almost no SaaS company does. The list-of-quiet-accounts query is doable in BigQuery if you have a data team, exists buried in Mixpanel under “RFM analysis,” or requires you to install ChartMogul + connect Stripe + pay $200/mo. So nobody does it weekly.

Agentry makes the question one prompt. The agent runs the query, ranks by ARR, looks at the last activity each account had, and drafts a per-account email — no template-y “we miss you” — referencing what they were actually doing.

What you get

• List of paid accounts with 0 events in last 14 days (or your threshold), excluding canceled
• Sorted by ARR so CS time goes to highest-impact accounts first
• A draft email for the top 5, personalized to what they were last doing before going quiet

Walk through it

Endpoint: POST /v1/projects/:project_id/analytics/query
Exact shape: /v1/openapi.json?path=%2Fv1%2Fprojects%2F%7Bproject_id%7D%2Fanalytics%2Fquery&method=post
Custom HogQL goes in the OpenAPI-defined `query` field.
Concept fields:
  query: "WITH paid AS (SELECT properties.account_id AS aid, argMax(properties.plan, timestamp) AS plan, argMax(properties.mrr, timestamp) AS mrr FROM events WHERE event IN ('plan_changed','seat_count_set','subscription_started') GROUP BY aid HAVING mrr > 0), canceled AS (SELECT DISTINCT properties.account_id AS aid FROM events WHERE event = 'subscription_canceled'), activity AS (SELECT properties.account_id AS aid, max(timestamp) AS last_event_ts FROM events WHERE event != '$exception' GROUP BY aid) SELECT p.aid AS account, p.plan AS plan, p.mrr AS mrr, a.last_event_ts AS last_event_ts, dateDiff('day', a.last_event_ts, now()) AS days_quiet FROM paid p LEFT JOIN activity a ON p.aid = a.aid WHERE p.aid NOT IN (SELECT aid FROM canceled) AND (a.last_event_ts < now() - INTERVAL 14 DAY OR a.last_event_ts IS NULL) ORDER BY p.mrr DESC LIMIT 20"

{
  "rows": [
    { "account": "megacorp",  "plan": "enterprise", "mrr": 5000, "days_quiet": 18 },
    { "account": "globex",    "plan": "scale",      "mrr": 1875, "days_quiet": 22 },
    { "account": "scaleco",   "plan": "scale",      "mrr": 1875, "days_quiet": 15 },
    { "account": "midmarket", "plan": "pro",        "mrr": 750,  "days_quiet": 31 },
    { "account": "smallpaid", "plan": "starter",    "mrr": 250,  "days_quiet": 16 }
  ]
}

Endpoint: GET /v1/projects/:project_id/users/:distinct_id/summary
Exact shape: /v1/openapi.json?path=%2Fv1%2Fprojects%2F%7Bproject_id%7D%2Fusers%2F%7Bdistinct_id%7D%2Fsummary&method=get
Use query parameters, not a JSON body.
Query/filter concept:
  windows: [
    { name: "last_30d_active", from_relative: "44d ago", to_relative: "14d ago" }
  ]

{
  "windows": {
    "last_30d_active": {
      "total_events": 4200,
      "by_event": {
        "report_generated":  88,
        "api_call":         3200,
        "dashboard_view":    640,
        "team_member_invited": 4
      },
      "top_users": [
        { "distinct_id": "[email protected]", "events": 2840 },
        { "distinct_id": "[email protected]", "events": 1100 }
      ]
    }
  }
}

The output

Dormant Paying Accounts (>14 days quiet, not canceled, top by ARR)

ACCOUNT     PLAN         MRR     LAST ACTIVITY    PRIMARY USER
megacorp    enterprise   $5,000  18 days ago      [email protected]
globex      scale        $1,875  22 days ago      [email protected]
scaleco     scale        $1,875  15 days ago      multiple
midmarket   pro          $750    31 days ago      one user — gone?
smallpaid   starter      $250    16 days ago      [email protected]

Total ARR dormant: $9,750/mo ($117k/yr at risk)

DRAFT EMAILS:
  → megacorp (sarah@): "noticed your team's been quiet — API was 200+/day"
  → globex (ops@):    "your monthly report cycle hasn't run yet — anything we can help with?"
  → scaleco:           "team dispersed — would a quick 1:1 with each user help?"
  → midmarket:         "no activity for 31d — should we pause your sub temporarily?"
  → smallpaid:         "low-touch nudge — share new feature X"

Setting it up

Same instrumentation as account-health-score: every event needs account_id and you need an MRR-or-plan signal per account. Then the SQL just composes a paid ∩ not_canceled ∩ not_active set.

// Per-user event with account_id
await fetch(`https://api.agentry.sh/v1/analytics/`, {
  method: "POST",
  headers: {
    "Authorization": `Bearer ${process.env.AGENTRY_SERVER_API_KEY}`,
    "Content-Type": "application/json",
    "User-Agent": "myapp/1.0",  // REQUIRED — Cloudflare 403s default UAs
  },
  body: JSON.stringify({
    event: "report_generated",
    distinct_id: user.email,
    properties: { account_id: user.workspace_id, report_type: "monthly" },
  }),
});

// On Stripe cancellation
await fetch(`https://api.agentry.sh/v1/analytics/`, {
  method: "POST",
  headers: { /* same */ "User-Agent": "myapp-stripe/1.0" },
  body: JSON.stringify({
    event: "subscription_canceled",
    distinct_id: subscription.metadata.account_id,
    properties: {
      account_id: subscription.metadata.account_id,
      reason: subscription.cancellation_details?.reason,
    },
  }),
});

Why exclude canceled accounts? They’re a different workstream (winback campaigns, not save campaigns) and would dilute the priority list.

Variations

• “Run the same query for trial accounts — who’s mid-trial and gone silent? Probably abandoning.”
• “Find accounts that were active last quarter but completely silent this quarter. Slower-burn churn.”
• “For each dormant account, also pull any support tickets from the last 30 days — context for the outreach.”
• “Weekly cron Monday 9am: post the previous week’s new-dormant accounts to #cs-pulse.”