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. ```sql -- 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.