Posthog Upsell Ready Accounts

Which accounts are ready to upsell?

Question: Which accounts are consistently near or over their plan limits but still on a lower plan? For: Sales & CS · Difficulty: Intermediate · Shape: a scheduled query Data sources: PostHog events (usage vs plan limits & premium features) + Stripe (current plan per account)

What this produces

A saved PostHog insight listing accounts whose measured product usage runs near or over the limits of their current Stripe plan — a live, refreshable expansion list for Sales/CS.

Workflow

First read references/posthog-workflow.md for the shared setup: confirm the PostHog MCP is connected, ensure the Stripe source exists (secure connect-link flow if not), and learn the real schema. Then the question-specific part:

1. Identify the pieces in this project

• Current plan per account. From Stripe: the active stripe_subscription → stripe_price / stripe_product gives each customer's current plan. Confirm how the user names their tiers.
• Plan limits. The numeric limit each plan implies (seats, events, API calls, etc.). This often isn't in Stripe — ask the user for the limit per tier, or read it from a plan/product metadata field if they store one.
• Real usage. The PostHog metric that maps to the limited resource (e.g. count of a usage event per account per month, or distinct active users per group). Find it with event-definitions-list / property-definitions.
• Account key. Map usage to the Stripe customer (person email or a group's account id). See the join gotchas in the shared reference.

2. Build and validate the query

Adapt and validate with query-run. The core idea: usage ÷ plan limit ≥ threshold, AND plan is not already the top tier.

-- Accounts whose usage is near/over their plan limit but still on a lower plan.
-- Adapt: usage event, the account key, plan→limit mapping (here inline as a CASE), and thresholds.
WITH usage AS (
    SELECT
        e.person.properties.account_id AS account,   -- or group key / email
        count() AS usage_last_30d
    FROM events AS e
    WHERE e.event = 'billable_action'                -- the resource you meter on
      AND e.timestamp >= now() - INTERVAL 30 DAY
    GROUP BY account
),
plan AS (
    SELECT
        lower(c.email) AS account,                   -- align this key with `usage.account`
        p.nickname AS plan_name,
        multiIf(p.nickname = 'Starter', 10000,
                p.nickname = 'Growth', 100000,
                p.nickname = 'Scale', 1000000, NULL) AS plan_limit,
        p.nickname != 'Scale' AS not_top_tier
    FROM stripe_subscription AS s
    INNER JOIN stripe_price AS p ON s.plan_id = p.id
    WHERE s.status = 'active'
)
SELECT
    pl.account,
    pl.plan_name,
    u.usage_last_30d,
    pl.plan_limit,
    round(u.usage_last_30d / nullif(pl.plan_limit, 0), 2) AS utilization
FROM usage AS u
INNER JOIN plan AS pl ON u.account = pl.account
WHERE pl.not_top_tier
  AND u.usage_last_30d >= 0.8 * pl.plan_limit          -- "near or over" threshold
ORDER BY utilization DESC

Prefer a "consistently over" signal (e.g. over threshold in 2+ of the last 3 months) to avoid flagging one-off spikes — extend the usage CTE to bucket by month if the user wants that.

3. Save the insight and schedule it

Save as a SQL/HogQL table insight named "Upsell-ready accounts (usage vs plan)". Because this is most useful fresh, offer to add it to a dashboard and to materialize the underlying query (view-create + view-materialize) so it refreshes on a schedule.

Self-driving development (offer this)

With a live list of accounts outgrowing their plan, the user can catch expansion moments as they happen. Offer to help trigger an in-product upgrade prompt or a Sales/CS outreach alert for accounts that cross the threshold — growing revenue without anyone watching a dashboard.