Churn Signal Alerter

A nightly GitHub Action that queries your Postgres or Supabase database for three leading churn signals — login gaps, session frequency drops, and core feature abandonment — and posts a formatted digest to a Slack channel.

Runs free on GitHub Actions. No infrastructure to manage. Setup in under 15 minutes.

---

The three signals

Signal	Default threshold	What it means
Login gap	14 days	Account hasn't logged in. 🔴 Critical if > 21 days, ⚠️ Warning if 14–21 days.
Session drop	50% week-over-week	Weekly session count halved. ⚠️ Warning.
Feature abandonment	21 days	Adopted the core feature in their first 30 days but hasn't used it since. ⚠️ Warning. Requires CORE_FEATURE_EVENT to be set.

If an account triggers multiple signals, only the most severe is shown in the digest.

---

Database schema

The script expects two tables. If your schema differs, the SQL queries in churn_alerter.py are clearly commented and easy to adapt.

user_events

CREATE TABLE user_events (
    id          uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    user_id     text NOT NULL,
    account_id  text NOT NULL,
    event_type  text NOT NULL,  -- e.g. 'login', 'export_created', 'dashboard_viewed'
    created_at  timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX ON user_events (account_id, event_type, created_at);

accounts

CREATE TABLE accounts (
    id         text PRIMARY KEY,
    name       text NOT NULL,
    plan       text,             -- e.g. 'Starter', 'Growth', 'Pro'
    mrr        numeric,          -- monthly recurring revenue in pence/cents
    admin_url  text              -- link to the account in your admin panel or CRM
);

---

Setup — 5 steps

Step 1 — Fork or copy this repo

Add the files to an existing repo or a new one. The GitHub Action reads from .github/workflows/churn-alerter.yml.

Step 2 — Create a Slack Incoming Webhook

1. Go to api.slack.com/apps → Create New App → From scratch
2. Name it "Churn Signal Alerter", pick your workspace
3. Incoming Webhooks → toggle on → Add New Webhook to Workspace
4. Pick the #churn-risk channel (create it first if it doesn't exist)
5. Copy the webhook URL — it starts with https://hooks.slack.com/services/...

Step 3 — Add Secrets to GitHub

Go to your repo → Settings → Secrets and variables → Actions → New repository secret.

Required secrets:

Secret name	Value
SLACK_WEBHOOK_URL	The webhook URL from Step 2
DATABASE_URL	Your Postgres connection string (see below)

Getting your DATABASE_URL:

• Supabase: Dashboard → Project Settings → Database → Connection string → URI Copy the postgresql://postgres:...@db.xxxxx.supabase.co:5432/postgres string.
• Other Postgres: Standard postgresql://user:password@host:5432/dbname format.

Alternative (Supabase only): Instead of DATABASE_URL, you can set:

• SUPABASE_URL — your project URL, e.g. https://xxxxx.supabase.co
• SUPABASE_KEY — your service role key (from Dashboard → Settings → API)

Note: the Supabase auto-connect assumes eu-west-2 region. If your project is in a different region, use DATABASE_URL instead.

Step 4 — (Optional) Configure thresholds

Go to Settings → Secrets and variables → Actions → Variables tab.

These are optional — the defaults work well for most early-stage SaaS:

Variable	Default	Description
LOGIN_THRESHOLD_DAYS	14	Days without login before flagging
SESSION_DROP_THRESHOLD	50	% weekly session drop to flag
FEATURE_ABANDONMENT_DAYS	21	Days without core feature use to flag
CORE_FEATURE_EVENT	(empty)	Event name for Signal 3. Leave empty to disable Signal 3.
MAX_ACCOUNTS_PER_ALERT	10	Max accounts per digest (prevents alert fatigue)
NOTIFY_ON_CLEAN	false	Set to true to receive a "no at-risk accounts" confirmation

Setting CORE_FEATURE_EVENT: this should match the event_type value you log when a user performs the key action in your product. For example: export_created, report_published, connection_synced. Check your user_events table for the exact string.

Step 5 — Test it

Go to Actions → Churn Signal Alerter → Run workflow → Run workflow.

The action should complete in under 30 seconds. If it fails, check the logs — the script exits with a non-zero code and a descriptive error message.

If NOTIFY_ON_CLEAN is false (the default), a clean run sends nothing to Slack. Set it to true for your first test run to confirm the webhook is working.

---

Slack digest format

🔴 Churn Risk Digest — Mon 07 Apr

3 accounts need attention · £4,200 MRR at risk

━━━━━━━━━━━━━━━━━━━━━━━

🔴 Acme Corp · Growth (£800/mo)
No login in 18 days · View account

⚠️ TechStartup Ltd · Starter (£200/mo)
Sessions dropped 67% this week (12 → 4) · View account

⚠️ Widgets Inc · Pro (£400/mo)
Core feature unused for 22 days · View account

━━━━━━━━━━━━━━━━━━━━━━━

Tincture Churn Signal Alerter · tinctu.re

---

Running locally

pip install -r requirements.txt

export DATABASE_URL="postgresql://..."
export SLACK_WEBHOOK_URL="https://hooks.slack.com/services/..."
export NOTIFY_ON_CLEAN="true"   # so you see output on a clean run

python churn_alerter.py

---

Adapting the schema

If your tables have different column names, find the three SQL queries in churn_alerter.py (functions query_login_gaps, query_session_drops, query_feature_abandonment) and update the column references. The queries are written to be readable and each one has a comment explaining its logic.

If you don't have an accounts table and instead store everything in a single users table, change the JOIN to self-reference and adjust the GROUP BY.

---

Questions or issues

Open an issue at the repo, or find more resources at tinctu.re.