mirror of
https://github.com/Kaelio/ktx.git
synced 2026-06-10 08:05:14 +02:00
97da9919e9
* refactor: remove legacy compatibility paths * fix: support legacy metabase native queries * test: use canonical semantic layer descriptions * Rename CLI description * Recover setup scan from SQLite ABI mismatch * Remove legacy product name from CLI help
291 lines
11 KiB
YAML
291 lines
11 KiB
YAML
name: churn_risk
|
|
descriptions:
|
|
user: |
|
|
Per-account churn risk scoring for B2B SaaS customers. Combines signals from
|
|
subscriptions (cancellation history), support tickets (severity, SLA breaches),
|
|
product usage (adoption decline), contracts (renewal proximity), CSM activities
|
|
(engagement recency), and invoices (payment issues) into a weighted composite
|
|
risk_score (0-1) and risk_tier (High/Medium/Low). One row per customer account.
|
|
sql: |
|
|
WITH sub_signals AS (
|
|
SELECT
|
|
account_id,
|
|
MAX(CASE WHEN canceled_at IS NOT NULL THEN 1 ELSE 0 END) AS has_canceled,
|
|
COUNT(CASE WHEN canceled_at IS NOT NULL THEN 1 END) AS canceled_count,
|
|
STRING_AGG(DISTINCT churn_reason, ', ') AS churn_reasons
|
|
FROM subscriptions
|
|
GROUP BY account_id
|
|
),
|
|
ticket_signals AS (
|
|
SELECT
|
|
account_id,
|
|
COUNT(*) AS total_tickets,
|
|
COUNT(CASE WHEN status = 'Open' THEN 1 END) AS open_tickets,
|
|
COUNT(CASE WHEN severity = 'High' THEN 1 END) AS high_severity_tickets,
|
|
COUNT(CASE WHEN sla_breached = '1' OR sla_breached = 'true' THEN 1 END) AS sla_breaches
|
|
FROM support_tickets
|
|
GROUP BY account_id
|
|
),
|
|
usage_signals AS (
|
|
SELECT
|
|
account_id,
|
|
AVG(CASE WHEN CURRENT_DATE - usage_date <= 90
|
|
THEN CAST(active_users AS NUMERIC) END) AS recent_active_users,
|
|
AVG(CASE WHEN CURRENT_DATE - usage_date > 90
|
|
AND CURRENT_DATE - usage_date <= 180
|
|
THEN CAST(active_users AS NUMERIC) END) AS prior_active_users,
|
|
AVG(CASE WHEN CURRENT_DATE - usage_date <= 90
|
|
THEN CAST(events_count AS NUMERIC) END) AS recent_events,
|
|
AVG(CASE WHEN CURRENT_DATE - usage_date > 90
|
|
AND CURRENT_DATE - usage_date <= 180
|
|
THEN CAST(events_count AS NUMERIC) END) AS prior_events
|
|
FROM product_usage
|
|
GROUP BY account_id
|
|
),
|
|
contract_signals AS (
|
|
SELECT
|
|
account_id,
|
|
MAX(arr) AS current_arr,
|
|
MIN(CASE WHEN status = 'Active'
|
|
THEN end_date - CURRENT_DATE END) AS days_to_renewal,
|
|
COUNT(CASE WHEN status = 'Active' THEN 1 END) AS active_contracts
|
|
FROM contracts
|
|
GROUP BY account_id
|
|
),
|
|
activity_signals AS (
|
|
SELECT
|
|
account_id,
|
|
COUNT(CASE WHEN CURRENT_DATE - activity_date::date <= 90
|
|
THEN 1 END) AS recent_activities,
|
|
MIN(CURRENT_DATE - activity_date::date) AS days_since_last_activity
|
|
FROM activities
|
|
GROUP BY account_id
|
|
),
|
|
invoice_signals AS (
|
|
SELECT
|
|
account_id,
|
|
COUNT(CASE WHEN status = 'Partial' THEN 1 END) AS partial_invoices,
|
|
COUNT(CASE WHEN CURRENT_DATE > due_date
|
|
AND status != 'Paid' THEN 1 END) AS overdue_invoices
|
|
FROM invoices
|
|
GROUP BY account_id
|
|
),
|
|
scored AS (
|
|
SELECT
|
|
a.account_id,
|
|
COALESCE(s.has_canceled, 0) AS has_canceled,
|
|
COALESCE(s.canceled_count, 0) AS canceled_count,
|
|
s.churn_reasons,
|
|
COALESCE(t.open_tickets, 0) AS open_tickets,
|
|
COALESCE(t.high_severity_tickets, 0) AS high_severity_tickets,
|
|
COALESCE(t.sla_breaches, 0) AS sla_breaches,
|
|
COALESCE(u.recent_active_users, 0) AS recent_active_users,
|
|
COALESCE(u.prior_active_users, 0) AS prior_active_users,
|
|
COALESCE(u.recent_events, 0) AS recent_events,
|
|
COALESCE(c.current_arr, 0) AS current_arr,
|
|
COALESCE(c.days_to_renewal, 999) AS days_to_renewal,
|
|
COALESCE(c.active_contracts, 0) AS active_contracts,
|
|
COALESCE(act.recent_activities, 0) AS recent_activities,
|
|
COALESCE(act.days_since_last_activity, 999) AS days_since_last_activity,
|
|
COALESCE(inv.partial_invoices, 0) AS partial_invoices,
|
|
COALESCE(inv.overdue_invoices, 0) AS overdue_invoices,
|
|
CASE WHEN COALESCE(s.has_canceled, 0) = 1 THEN 1.0
|
|
WHEN COALESCE(s.canceled_count, 0) > 0 THEN 0.7
|
|
ELSE 0.1 END AS subscription_risk,
|
|
CASE WHEN COALESCE(t.high_severity_tickets, 0) >= 3 THEN 0.9
|
|
WHEN COALESCE(t.sla_breaches, 0) >= 2 THEN 0.8
|
|
WHEN COALESCE(t.open_tickets, 0) >= 3 THEN 0.7
|
|
WHEN COALESCE(t.open_tickets, 0) >= 1 THEN 0.4
|
|
ELSE 0.1 END AS support_risk,
|
|
CASE WHEN COALESCE(u.recent_active_users, 0) = 0 THEN 0.9
|
|
WHEN COALESCE(u.prior_active_users, 0) > 0
|
|
AND COALESCE(u.recent_active_users, 0) < COALESCE(u.prior_active_users, 0) * 0.5
|
|
THEN 0.8
|
|
WHEN COALESCE(u.prior_active_users, 0) > 0
|
|
AND COALESCE(u.recent_active_users, 0) < COALESCE(u.prior_active_users, 0) * 0.8
|
|
THEN 0.5
|
|
ELSE 0.1 END AS usage_risk,
|
|
CASE WHEN COALESCE(c.days_to_renewal, 999) <= 30 THEN 0.9
|
|
WHEN COALESCE(c.days_to_renewal, 999) <= 60 THEN 0.7
|
|
WHEN COALESCE(c.days_to_renewal, 999) <= 90 THEN 0.5
|
|
WHEN COALESCE(c.active_contracts, 0) = 0 THEN 0.8
|
|
ELSE 0.1 END AS contract_risk,
|
|
CASE WHEN COALESCE(act.days_since_last_activity, 999) > 90 THEN 0.9
|
|
WHEN COALESCE(act.days_since_last_activity, 999) > 60 THEN 0.7
|
|
WHEN COALESCE(act.recent_activities, 0) <= 2 THEN 0.6
|
|
WHEN COALESCE(act.days_since_last_activity, 999) > 30 THEN 0.4
|
|
ELSE 0.1 END AS engagement_risk,
|
|
CASE WHEN COALESCE(inv.overdue_invoices, 0) >= 2 THEN 0.9
|
|
WHEN COALESCE(inv.overdue_invoices, 0) >= 1 THEN 0.7
|
|
WHEN COALESCE(inv.partial_invoices, 0) >= 2 THEN 0.6
|
|
WHEN COALESCE(inv.partial_invoices, 0) >= 1 THEN 0.3
|
|
ELSE 0.1 END AS payment_risk
|
|
FROM accounts a
|
|
LEFT JOIN sub_signals s ON a.account_id = s.account_id
|
|
LEFT JOIN ticket_signals t ON a.account_id = t.account_id
|
|
LEFT JOIN usage_signals u ON a.account_id = u.account_id
|
|
LEFT JOIN contract_signals c ON a.account_id = c.account_id
|
|
LEFT JOIN activity_signals act ON a.account_id = act.account_id
|
|
LEFT JOIN invoice_signals inv ON a.account_id = inv.account_id
|
|
WHERE a.is_customer = '1'
|
|
)
|
|
SELECT
|
|
account_id,
|
|
has_canceled,
|
|
canceled_count,
|
|
churn_reasons,
|
|
open_tickets,
|
|
high_severity_tickets,
|
|
sla_breaches,
|
|
recent_active_users,
|
|
prior_active_users,
|
|
recent_events,
|
|
current_arr,
|
|
days_to_renewal,
|
|
active_contracts,
|
|
recent_activities,
|
|
days_since_last_activity,
|
|
partial_invoices,
|
|
overdue_invoices,
|
|
subscription_risk,
|
|
support_risk,
|
|
usage_risk,
|
|
contract_risk,
|
|
engagement_risk,
|
|
payment_risk,
|
|
ROUND(
|
|
subscription_risk * 0.20
|
|
+ support_risk * 0.20
|
|
+ usage_risk * 0.20
|
|
+ contract_risk * 0.15
|
|
+ engagement_risk * 0.15
|
|
+ payment_risk * 0.10,
|
|
3
|
|
) AS risk_score,
|
|
CASE
|
|
WHEN (subscription_risk * 0.20
|
|
+ support_risk * 0.20
|
|
+ usage_risk * 0.20
|
|
+ contract_risk * 0.15
|
|
+ engagement_risk * 0.15
|
|
+ payment_risk * 0.10) >= 0.7 THEN 'High'
|
|
WHEN (subscription_risk * 0.20
|
|
+ support_risk * 0.20
|
|
+ usage_risk * 0.20
|
|
+ contract_risk * 0.15
|
|
+ engagement_risk * 0.15
|
|
+ payment_risk * 0.10) >= 0.4 THEN 'Medium'
|
|
ELSE 'Low'
|
|
END AS risk_tier
|
|
FROM scored
|
|
grain:
|
|
- account_id
|
|
columns:
|
|
- name: account_id
|
|
type: number
|
|
- name: has_canceled
|
|
type: number
|
|
description: "1 if the account has any canceled subscription"
|
|
- name: canceled_count
|
|
type: number
|
|
description: "Number of canceled subscriptions"
|
|
- name: churn_reasons
|
|
type: string
|
|
description: "Comma-separated distinct churn reasons from subscriptions"
|
|
- name: open_tickets
|
|
type: number
|
|
description: "Count of currently open support tickets"
|
|
- name: high_severity_tickets
|
|
type: number
|
|
description: "Count of high-severity support tickets"
|
|
- name: sla_breaches
|
|
type: number
|
|
description: "Count of support tickets with SLA breaches"
|
|
- name: recent_active_users
|
|
type: number
|
|
description: "Average active users in the last 90 days"
|
|
- name: prior_active_users
|
|
type: number
|
|
description: "Average active users 90-180 days ago (for trend comparison)"
|
|
- name: recent_events
|
|
type: number
|
|
description: "Average event count in the last 90 days"
|
|
- name: current_arr
|
|
type: number
|
|
description: "Highest ARR from active contracts"
|
|
- name: days_to_renewal
|
|
type: number
|
|
description: "Days until the nearest active contract expires"
|
|
- name: active_contracts
|
|
type: number
|
|
description: "Count of active contracts"
|
|
- name: recent_activities
|
|
type: number
|
|
description: "CSM activities (calls, meetings, emails, tasks) in the last 90 days"
|
|
- name: days_since_last_activity
|
|
type: number
|
|
description: "Days since the most recent CSM activity"
|
|
- name: partial_invoices
|
|
type: number
|
|
description: "Count of invoices with Partial payment status"
|
|
- name: overdue_invoices
|
|
type: number
|
|
description: "Count of overdue unpaid invoices"
|
|
- name: subscription_risk
|
|
type: number
|
|
description: "Subscription cancellation risk sub-score (0.0-1.0)"
|
|
- name: support_risk
|
|
type: number
|
|
description: "Support burden risk sub-score (0.0-1.0)"
|
|
- name: usage_risk
|
|
type: number
|
|
description: "Product usage decline risk sub-score (0.0-1.0)"
|
|
- name: contract_risk
|
|
type: number
|
|
description: "Contract renewal proximity risk sub-score (0.0-1.0)"
|
|
- name: engagement_risk
|
|
type: number
|
|
description: "CSM engagement gap risk sub-score (0.0-1.0)"
|
|
- name: payment_risk
|
|
type: number
|
|
description: "Payment issues risk sub-score (0.0-1.0)"
|
|
- name: risk_score
|
|
type: number
|
|
description: "Weighted composite churn risk score (0.0-1.0); higher = riskier"
|
|
- name: risk_tier
|
|
type: string
|
|
description: "Churn risk tier: High (>=0.7), Medium (>=0.4), Low (<0.4)"
|
|
joins:
|
|
- to: accounts
|
|
"on": account_id = accounts.account_id
|
|
relationship: one_to_one
|
|
measures:
|
|
- name: avg_risk_score
|
|
expr: avg(risk_score)
|
|
description: "Average churn risk score across accounts"
|
|
- name: high_risk_accounts
|
|
expr: count(account_id)
|
|
filter: "risk_tier = 'High'"
|
|
description: "Number of accounts in the High risk tier"
|
|
- name: medium_risk_accounts
|
|
expr: count(account_id)
|
|
filter: "risk_tier = 'Medium'"
|
|
description: "Number of accounts in the Medium risk tier"
|
|
- name: low_risk_accounts
|
|
expr: count(account_id)
|
|
filter: "risk_tier = 'Low'"
|
|
description: "Number of accounts in the Low risk tier"
|
|
- name: total_arr_at_risk
|
|
expr: sum(current_arr)
|
|
filter: "risk_tier = 'High'"
|
|
description: "Total ARR from accounts in the High risk tier"
|
|
- name: avg_support_risk
|
|
expr: avg(support_risk)
|
|
description: "Average support burden risk sub-score"
|
|
- name: avg_usage_risk
|
|
expr: avg(usage_risk)
|
|
description: "Average usage decline risk sub-score"
|
|
- name: accounts_expiring_90d
|
|
expr: count(account_id)
|
|
filter: "days_to_renewal <= 90"
|
|
description: "Accounts with contracts expiring within 90 days"
|