Churn is not an event — it's a process. By the time a user clicks "Cancel Subscription," they've already mentally left days or weeks ago. The behavioral signals that mark that departure are sitting in your event stream right now: session frequency dropping off, a support ticket filed with a frustration-laced message, three visits to your billing page in one afternoon. Batch pipelines that aggregate weekly snapshots will never see these signals at the resolution that matters.
This article is for data scientists and ML engineers building churn models for SaaS, subscription software, and consumer apps. We'll walk through how to build a real-time churn feature pipeline using streaming SQL — specifically using RisingWave — so your model sees the same behavioral deterioration a sharp CSM would notice in a daily standup.
When Batch Churn Models Fail: The "Already Churned When You Noticed" Problem
Most production churn models follow the same pattern: a weekly ETL job exports a feature matrix from the data warehouse, the model scores each account, and the CSM queue gets updated by Monday morning. This is fine when churn is slow-moving — enterprise contracts with 12-month terms and deliberate offboarding processes. It's a disaster for anything with monthly billing, freemium, or self-serve cancellation.
The problem is temporal resolution. Weekly batch features smear out the signal. Consider what actually happens in the 72 hours before a self-serve cancellation:
- Daily active usage drops sharply — the user stops logging in or opens the app briefly and closes it
- A support ticket gets filed, often with language like "not getting value" or "too complicated"
- Billing page visits spike — the user is scoping the cancellation flow
- Core feature usage drops; they've stopped trying to get value from the product
By the time your Monday batch job sees this, the account has already churned. Your model scored them "low risk" last week when they were still nominally active, and by this week they're already gone.
The financial impact compounds. Net Revenue Retention (NRR) and Gross Revenue Retention (GRR) are both downstream of churn you failed to intercept. Every account you lose without intervention is LTV you can't recover. For companies with significant SMB exposure, even a 1-point improvement in monthly GRR compounds meaningfully over a year. That 1-point improvement often comes from catching and intervening on accounts that batch models mark as safe until they're already gone.
The fix is not a better model — it's better features. Specifically, it's features that reflect what happened in the last 24-72 hours, not the last 7-14 days.
High-Signal Leading Indicators of Churn
Before writing SQL, it's worth being precise about which signals actually predict churn. Feature engineering quality matters more than model architecture for churn — a logistic regression with excellent real-time features will outperform a gradient boosted tree trained on stale weekly aggregates.
Engagement decline is the most reliable signal for SaaS churn. The relevant features are not just raw session count — it's the trajectory. A user who logged in 20 times last month and 3 times this week is showing a meaningfully different pattern than a user who logged in 5 times both periods. You need both absolute level and week-over-week delta.
Feature adoption breadth signals whether the user has found value. A user who uses only one feature in a multi-feature platform is at far higher churn risk than one who uses five features regularly. This narrows during pre-churn: users often "retreat" to a single workflow before abandoning entirely.
Support escalation signals are powerful, especially when combined with sentiment and category. A neutral "how do I export data?" ticket looks nothing like an angry "this keeps breaking my workflow" ticket, even though both count as support interactions. Cancellation-category tickets are the strongest single-feature predictor in most SaaS churn models.
Billing behavior is an underused signal. Billing page visits and payment failure events are high-precision indicators — users who visit the billing or cancellation page without completing a downgrade or cancellation are in consideration mode. Failed payments are a mechanical churn trigger for consumer apps.
Feature adoption gaps — the absence of adoption for features that are strongly correlated with retention — are often missed in feature engineering. If users who adopt your collaboration feature have 40% lower churn, then non-adoption of that feature should be a feature in your model.
Data Sources
A complete churn feature pipeline for a SaaS product typically draws from three event streams:
Product events — every session start/end, page view, feature invocation, and in-app action. These should be streaming into Kafka or a similar system from your application servers or analytics SDKs. Key fields: user_id, event_type, feature_used, event_time, session_id, session_duration_min.
Support events — ticket creation, replies, status changes, and resolution events from your support system (Zendesk, Intercom, Freshdesk). These often require a CDC connector or webhook integration. Key fields: user_id, ticket_id, category, sentiment, ticket_created_at, event_time.
Billing events — subscription changes, payment attempts, invoice events, and page visits from Stripe or similar. Key fields: user_id, event_type (e.g., page_view_billing, payment_failed, subscription_downgrade), event_time.
All three streams land in Kafka topics and are consumed by RisingWave, which maintains the materialized views as a continuously updated feature store.
SQL Feature Implementation
Engagement Decline Features
CREATE MATERIALIZED VIEW user_engagement_features AS
SELECT
user_id,
COUNT(*) AS sessions_30d,
COUNT(DISTINCT feature_used) AS feature_breadth_30d,
SUM(session_duration_min) AS total_time_30d,
MAX(event_time) AS last_active_at,
EXTRACT(DAY FROM (NOW() - MAX(event_time))) AS days_since_last_active,
-- Week-over-week engagement trend
COUNT(*) FILTER (WHERE event_time >= NOW() - INTERVAL '7 days') AS sessions_this_week,
COUNT(*) FILTER (WHERE event_time BETWEEN NOW() - INTERVAL '14 days' AND NOW() - INTERVAL '7 days') AS sessions_last_week,
-- Derived: session decline ratio (< 1 means falling, > 1 means growing)
CASE
WHEN COUNT(*) FILTER (WHERE event_time BETWEEN NOW() - INTERVAL '14 days' AND NOW() - INTERVAL '7 days') = 0 THEN NULL
ELSE COUNT(*) FILTER (WHERE event_time >= NOW() - INTERVAL '7 days')::FLOAT
/ COUNT(*) FILTER (WHERE event_time BETWEEN NOW() - INTERVAL '14 days' AND NOW() - INTERVAL '7 days')
END AS session_trend_ratio,
-- Feature adoption: is the user using the stickiest features?
COUNT(*) FILTER (WHERE feature_used IN ('collaboration', 'reporting', 'api_access')) AS sticky_feature_sessions_30d
FROM product_events
WHERE event_time >= NOW() - INTERVAL '30 days'
GROUP BY user_id;
The session_trend_ratio is particularly useful as a model feature: a value below 0.5 (sessions cut in half week-over-week) is a strong churn predictor even when absolute session count looks acceptable.
Support Escalation Signal
CREATE MATERIALIZED VIEW support_signals AS
SELECT
user_id,
COUNT(*) AS tickets_30d,
COUNT(*) FILTER (WHERE sentiment = 'negative') AS negative_tickets_30d,
COUNT(*) FILTER (WHERE category = 'cancellation') AS cancellation_inquiries_30d,
COUNT(*) FILTER (WHERE category IN ('bug', 'outage')) AS technical_issue_tickets_30d,
MAX(ticket_created_at) AS last_ticket_at,
EXTRACT(DAY FROM (NOW() - MAX(ticket_created_at))) AS days_since_last_ticket,
-- Escalation rate: negative tickets as fraction of total
CASE
WHEN COUNT(*) = 0 THEN 0
ELSE COUNT(*) FILTER (WHERE sentiment = 'negative')::FLOAT / COUNT(*)
END AS negative_ticket_rate
FROM support_events
WHERE event_time >= NOW() - INTERVAL '30 days'
GROUP BY user_id;
The cancellation_inquiries_30d feature almost never fires for healthy accounts. When it does, the model weight for this feature should be very high — you should be triggering an intervention workflow immediately, regardless of the overall churn probability score.
Billing Behavior Features
CREATE MATERIALIZED VIEW billing_signals AS
SELECT
user_id,
COUNT(*) FILTER (WHERE event_type = 'page_view_billing') AS billing_page_views_7d,
COUNT(*) FILTER (WHERE event_type = 'page_view_cancellation') AS cancellation_page_views_7d,
COUNT(*) FILTER (WHERE event_type = 'payment_failed') AS payment_failures_30d,
COUNT(*) FILTER (WHERE event_type = 'subscription_downgrade') AS downgrades_90d,
MAX(event_time) FILTER (WHERE event_type = 'page_view_billing') AS last_billing_page_at,
-- Binary flag: did user visit billing or cancellation page this week?
(COUNT(*) FILTER (WHERE event_type IN ('page_view_billing', 'page_view_cancellation')
AND event_time >= NOW() - INTERVAL '7 days') > 0)::INT AS billing_intent_signal_7d
FROM billing_events
WHERE event_time >= NOW() - INTERVAL '90 days'
GROUP BY user_id;
The billing_intent_signal_7d binary feature deserves special attention for model interpretation. In A/B tests on intervention timing, accounts where this flag is set respond measurably better to immediate outreach than to the weekly CSM queue cadence.
Combining Real-Time Features with Offline Features
Real-time behavioral features tell you what is happening right now. But churn risk is also shaped by account-level attributes that don't change event-by-event — plan type, contract value, onboarding cohort, product tier, and company size. These live in your data warehouse and get joined at scoring time.
-- Full feature set for churn scoring, joining streaming features with account-level attributes
CREATE MATERIALIZED VIEW churn_feature_set AS
SELECT
a.user_id,
a.account_id,
-- Offline / account-level features (from accounts dimension table or CDC from your CRM)
acc.plan_type,
acc.monthly_contract_value,
acc.cohort_month,
acc.team_size,
acc.industry,
EXTRACT(DAY FROM (NOW() - acc.subscription_start_date)) AS account_age_days,
-- Real-time engagement features
COALESCE(e.sessions_30d, 0) AS sessions_30d,
COALESCE(e.sessions_this_week, 0) AS sessions_this_week,
COALESCE(e.sessions_last_week, 0) AS sessions_last_week,
e.session_trend_ratio,
COALESCE(e.feature_breadth_30d, 0) AS feature_breadth_30d,
COALESCE(e.days_since_last_active, 999) AS days_since_last_active,
COALESCE(e.sticky_feature_sessions_30d, 0) AS sticky_feature_sessions_30d,
-- Real-time support signals
COALESCE(s.tickets_30d, 0) AS tickets_30d,
COALESCE(s.negative_tickets_30d, 0) AS negative_tickets_30d,
COALESCE(s.cancellation_inquiries_30d, 0) AS cancellation_inquiries_30d,
COALESCE(s.negative_ticket_rate, 0) AS negative_ticket_rate,
-- Real-time billing signals
COALESCE(b.billing_page_views_7d, 0) AS billing_page_views_7d,
COALESCE(b.cancellation_page_views_7d, 0) AS cancellation_page_views_7d,
COALESCE(b.payment_failures_30d, 0) AS payment_failures_30d,
COALESCE(b.billing_intent_signal_7d, 0) AS billing_intent_signal_7d
FROM user_account_map a
JOIN accounts acc ON a.account_id = acc.account_id
LEFT JOIN user_engagement_features e ON a.user_id = e.user_id
LEFT JOIN support_signals s ON a.user_id = s.user_id
LEFT JOIN billing_signals b ON a.user_id = b.user_id;
The accounts table here comes from a CDC source (Postgres or your CRM) that RisingWave consumes as a change stream. The join between streaming materialized views and a CDC-sourced table is native in RisingWave — the materialized view reflects account attribute changes automatically without manual refresh.
Model Serving: Calling the Churn Model with Fresh Features
At scoring time, your model needs the feature vector for a specific user. Because churn_feature_set is a materialized view maintained in real time, reading from it is a simple point query:
SELECT * FROM churn_feature_set WHERE user_id = $1;
This query returns the freshest available feature values — updated as events arrive, not as batch jobs complete. Your model serving layer calls this query, packs the row into the input format your model expects (scikit-learn pipeline, XGBoost DMatrix, or a feature vector for an online serving endpoint), and returns a probability score.
A practical serving architecture looks like this:
- A scoring service subscribes to a trigger topic (e.g., daily cron per-user, or event-driven triggers like billing page visits)
- For each user to score, it queries
churn_feature_setfrom RisingWave - The feature vector is passed to the model — either a locally loaded model artifact or a call to an online prediction endpoint (Vertex AI, SageMaker, etc.)
- The score is written back to a
churn_scorestable in RisingWave or your operational database
import psycopg2
import numpy as np
def score_user(user_id: str, model, feature_columns: list[str]) -> float:
conn = psycopg2.connect(
host="risingwave-host",
port=4566,
database="dev",
user="root"
)
cursor = conn.cursor()
cursor.execute(
"SELECT %s FROM churn_feature_set WHERE user_id = %%s" % ", ".join(feature_columns),
(user_id,)
)
row = cursor.fetchone()
if row is None:
return 0.0 # Unknown user, no features
feature_vector = np.array(row, dtype=float).reshape(1, -1)
return float(model.predict_proba(feature_vector)[0][1])
Because the feature store is always fresh, you can score on-demand — triggered by a specific user event rather than only on a nightly schedule. A billing page visit is a natural trigger: when a user opens the billing page, score them immediately and route high-probability churn cases to an intervention queue within seconds.
Triggering Interventions: Sinks on Churn Probability
Real-time features are only valuable if they drive real-time actions. Once you have churn scores written back to RisingWave, you can use a materialized view and a sink to trigger downstream interventions automatically.
-- Detect accounts crossing the high-churn threshold
CREATE MATERIALIZED VIEW high_churn_risk_alerts AS
SELECT
cs.user_id,
cs.account_id,
cs.plan_type,
cs.monthly_contract_value,
sc.churn_probability,
sc.scored_at,
-- Explain why: which signals are elevated?
CASE
WHEN cfs.cancellation_inquiries_30d > 0 THEN 'cancellation_inquiry'
WHEN cfs.billing_intent_signal_7d = 1 THEN 'billing_page_visit'
WHEN cfs.days_since_last_active > 14 THEN 'engagement_lapse'
WHEN cfs.session_trend_ratio < 0.4 THEN 'engagement_decline'
ELSE 'composite_risk'
END AS primary_signal
FROM churn_scores sc
JOIN churn_feature_set cfs ON sc.user_id = cfs.user_id
JOIN user_account_map cs ON sc.user_id = cs.user_id
WHERE sc.churn_probability > 0.70
AND sc.scored_at >= NOW() - INTERVAL '1 hour';
-- Sink high-risk alerts to your CRM or alerting system
CREATE SINK churn_alert_sink
FROM high_churn_risk_alerts
WITH (
connector = 'kafka',
properties.bootstrap.server = 'kafka:9092',
topic = 'churn-interventions'
);
The primary_signal column is load-bearing for intervention design. A user who triggered the alert via cancellation_inquiry needs a different response than one who triggered via engagement_lapse. The former needs immediate CSM outreach with retention framing; the latter needs a re-engagement campaign or a check-in call around value realization. Routing based on the causal signal — not just the probability score — is what separates effective churn programs from noise.
The Kafka sink means your intervention system (your CRM, your customer success platform, your in-app notification system) receives a structured event within seconds of the churn score crossing the threshold.
Frequently Asked Questions
Does this require restructuring my existing event schema?
Not necessarily. RisingWave can consume events in their existing Kafka schema. You add computed columns and filters in the materialized view layer rather than changing your producers. The main requirement is that events include a user_id (or an account-level identifier you can join on) and a reliable event_time timestamp.
How do I handle users who haven't had any events in the feature window?
The COALESCE(..., 0) and COALESCE(..., 999) patterns in the churn_feature_set view handle null joins for users with no recent activity. A user who has never appeared in the engagement window is itself a strong signal — days_since_last_active = 999 is a meaningful imputed value for your model. You can also separately maintain a user_last_seen table updated on every event to track truly inactive accounts.
Can I use these features for model training as well as serving?
Yes, with one caveat: materialized views reflect current state, not historical state at a point in time. For training data, you need point-in-time correct feature values (features as of the label date, not as of today). This requires either storing historical snapshots of the feature store, or reconstructing features from raw event tables using time-windowed queries anchored to the training label date. A common pattern is to use the streaming feature store for serving and a separate historical reconstruction pipeline for training, then validate that the distributions match.
How does this integrate with an existing feature platform like Feast or Tecton?
RisingWave can serve as the online store backend for feature platforms that support custom online store implementations. Alternatively, you can treat RisingWave as a standalone low-latency feature store and bypass the feature platform for churn-specific features. Many teams find that the SQL-native feature definition in RisingWave is easier to iterate on than the Python-based feature definitions in traditional feature platforms, particularly for window aggregations.
What model types work best with this feature set?
For churn probability calibration, gradient boosted trees (XGBoost, LightGBM) and logistic regression are both strong choices. Tree-based models handle the nonlinear interactions between features well (e.g., a billing page visit matters more when combined with engagement decline than in isolation). Logistic regression with interaction terms is more interpretable and easier to audit for fairness. Neither model architecture will save you if your feature set is stale — which is why the feature pipeline described here matters more than the model choice.
Won't intervention-triggered churn inflate my training labels?
Yes — this is the classic survivorship bias problem in churn modeling. Accounts that received interventions and were retained will have features that look like churners but labels that say "retained." This biases your training data over time. A few approaches: hold out a random control group from interventions for label purity, use inverse propensity scoring to weight training examples, or explicitly model the intervention effect separately. None of these are simple, but they're necessary if you want your model to remain calibrated after the intervention program matures.
Churn prediction is one of the clearer cases where data infrastructure choices directly affect business outcomes. The model is not the bottleneck — getting fresh, high-resolution behavioral features to the model at the moment they matter is. Streaming SQL makes it practical to build and iterate on those features without the overhead of custom Python jobs or brittle batch orchestration. The pipeline described here can be operational in days, not quarters, and the feature definitions are readable SQL that your whole data team can review and improve.
The rest — model quality, intervention design, feedback loops — still requires real work. But at least your model will be seeing the same signals your best CSM would catch on a daily call review.
