posthog-pre-cancellation-behavior

# What do customers do right before they cancel? **Question:** How does account activity change in the ~30 days before a customer cancels? **For:** CS & Product · **Difficulty:** Intermediate · **Shape:** a window query **Data sources:** PostHog events (activity timeline per account) + Stripe / Chargebee (cancellation dates) ## What this produces A saved PostHog insight showing the shared pre-churn pattern — how engagement trends downward (or which last actions occur) in the weeks before cancellation — giving the user an early-warning signal. ## Workflow First read `references/posthog-workflow.md` for the shared setup: confirm the PostHog MCP is connected, ensure the churn source (Stripe or Chargebee) exists (secure connect-link flow if not), and learn the real schema. Then the question-specific part: ### 1. Identify the pieces in this project - **Cancellation dates.** From Stripe: `stripe_subscription` with `status = 'canceled'` and its `canceled_at` (or `ended_at`) timestamp per customer. Chargebee has an equivalent subscription cancellation field. Confirm which system holds the source of truth for churn. - **Activity timeline.** The events that represent meaningful engagement (`event-definitions-list`). - **Account key.** Map the churned Stripe/Chargebee customer to PostHog people/groups (email or customer id — see join gotchas in the shared reference). ### 2. Build and validate the query The idea: for each churned account, index activity to weeks-before-cancellation, then average across accounts to reveal the shared decline. Adapt names and validate with `query-run`. ```sql -- Average weekly activity in the 30 days before cancellation, aligned by weeks-to-churn. -- Adapt: the cancellation source/fields, activity events, and the email join key. WITH cancels AS ( SELECT lower(email) AS email, max(canceled_at) AS churn_ts FROM stripe_subscription WHERE status = 'canceled' AND canceled_at >= now() - INTERVAL 90 DAY -- "last quarter" churners GROUP BY lower(email) ), activity AS ( SELECT c.email AS email, -- how many days before cancellation each event happened, bucketed into weeks intDiv(dateDiff('day', e.timestamp, c.churn_ts), 7) AS weeks_before_churn, count() AS events FROM events AS e INNER JOIN cancels AS c ON lower(e.person.properties.email) = c.email WHERE e.timestamp >= c.churn_ts - INTERVAL 30 DAY AND e.timestamp <= c.churn_ts GROUP BY c.email, weeks_before_churn ) SELECT weeks_before_churn, -- 0 = final week before churn round(avg(events), 1) AS avg_events_per_account, count(DISTINCT email) AS accounts FROM activity GROUP BY weeks_before_churn ORDER BY weeks_before_churn DESC ``` For "what was the last action", instead select each account's final event before `churn_ts` and rank those events by frequency. Offer both readings to the user. ### 3. Save the insight Save as a SQL/HogQL insight named "Activity before cancellation" — a line/bar over weeks-to-churn works well for the trend; a table for the last-action ranking. Return the URL and tell the user the pattern you see (e.g. "engagement roughly halves in the final two weeks"). ## Self-driving development (offer this) With an early-warning signal, the user can spot at-risk accounts before they leave. Offer to help turn the pattern into a cohort or alert (e.g. "activity down >50% week-over-week") that triggers a save flow or CS outreach — cutting churn automatically.