Real-time churn prediction for games means every player's churn risk score updates within seconds of each session, progression, and engagement event — so your retention system triggers the right offer, message, or experience change while the player is still active, not the day after they have already left. RisingWave computes behavioral churn signals as SQL materialized views that your game backend and CRM can query at any moment.
Churn Prediction Needs to Be Real-Time
Traditional churn prediction uses scheduled ML pipelines: export player data nightly, run a model, push churn scores back to the CRM in the morning. This works for email re-engagement campaigns targeting players who stopped logging in. But it completely misses the highest-value intervention window: the session where a player's behavior first signals disengagement.
If you can detect the session where a player's time-in-level drops, quest completion rate falls, or social interactions disappear — and act within that session — you have a chance to re-engage them before they form the habit of not logging in. That requires behavioral churn signals that update in real time.
RisingWave provides this through streaming SQL that continuously recalculates engagement indicators from live event data.
Setting Up the Event Stream
Ingest the session and behavioral events that drive churn signals:
CREATE SOURCE player_events (
player_id BIGINT,
session_id VARCHAR,
event_type VARCHAR, -- 'session_start', 'session_end', 'level_complete',
-- 'level_fail', 'item_purchase', 'quest_abandon',
-- 'chat_message', 'friend_interact'
session_duration_s INT,
level_id VARCHAR,
event_time TIMESTAMPTZ
) WITH (
connector = 'kafka',
topic = 'player-events',
properties.bootstrap.server = 'kafka:9092',
scan.startup.mode = 'latest'
) FORMAT PLAIN ENCODE JSON;
Add player profile data via CDC for enrichment:
CREATE SOURCE player_profiles (
player_id BIGINT PRIMARY KEY,
cohort_week DATE,
acquisition_channel VARCHAR,
country VARCHAR,
vip_tier INT
) WITH (
connector = 'postgres-cdc',
hostname = 'gamedb',
port = '5432',
username = 'rw',
password = 'secret',
database.name = 'gamedb',
schema.name = 'public',
table.name = 'player_profiles'
);
Rolling Engagement Signal View
Compute a rolling 7-day behavioral signal that feeds the churn score:
CREATE MATERIALIZED VIEW player_engagement_7d AS
SELECT
player_id,
window_start::DATE AS period_start,
COUNT(*) FILTER (WHERE event_type = 'session_start') AS sessions_7d,
AVG(session_duration_s) FILTER (WHERE event_type = 'session_end') AS avg_session_duration,
COUNT(*) FILTER (WHERE event_type = 'level_complete') AS levels_completed_7d,
COUNT(*) FILTER (WHERE event_type = 'level_fail') AS levels_failed_7d,
COUNT(*) FILTER (WHERE event_type = 'quest_abandon') AS quests_abandoned_7d,
COUNT(*) FILTER (WHERE event_type = 'item_purchase') AS purchases_7d,
COUNT(*) FILTER (WHERE event_type = 'friend_interact') AS social_interactions_7d,
MAX(event_time) AS last_event_time
FROM TUMBLE(player_events, event_time, INTERVAL '7 days')
GROUP BY player_id, window_start;
Churn Risk Score Materialized View
Translate engagement signals into a churn risk score by combining behavioral features:
CREATE MATERIALIZED VIEW churn_risk_scores AS
SELECT
e.player_id,
p.country,
p.vip_tier,
p.acquisition_channel,
e.period_start,
e.sessions_7d,
e.avg_session_duration,
e.levels_completed_7d,
e.quests_abandoned_7d,
e.social_interactions_7d,
e.last_event_time,
-- Rule-based churn score (0-100; higher = more risk)
LEAST(100,
CASE WHEN e.sessions_7d = 0 THEN 50 ELSE 0 END +
CASE WHEN e.sessions_7d <= 1 THEN 25 ELSE 0 END +
CASE WHEN e.avg_session_duration < 120 THEN 15 ELSE 0 END +
CASE WHEN e.levels_completed_7d = 0 THEN 10 ELSE 0 END +
CASE WHEN e.quests_abandoned_7d > 3 THEN 15 ELSE 0 END +
CASE WHEN e.social_interactions_7d = 0 THEN 10 ELSE 0 END -
CASE WHEN e.purchases_7d > 0 THEN 20 ELSE 0 END
) AS churn_risk_score,
CASE
WHEN e.sessions_7d = 0 THEN 'lapsed'
WHEN LEAST(100,
CASE WHEN e.sessions_7d = 0 THEN 50 ELSE 0 END +
CASE WHEN e.sessions_7d <= 1 THEN 25 ELSE 0 END +
CASE WHEN e.avg_session_duration < 120 THEN 15 ELSE 0 END +
CASE WHEN e.levels_completed_7d = 0 THEN 10 ELSE 0 END +
CASE WHEN e.quests_abandoned_7d > 3 THEN 15 ELSE 0 END +
CASE WHEN e.social_interactions_7d = 0 THEN 10 ELSE 0 END -
CASE WHEN e.purchases_7d > 0 THEN 20 ELSE 0 END
) >= 60 THEN 'high_risk'
WHEN LEAST(100,
CASE WHEN e.sessions_7d = 0 THEN 50 ELSE 0 END +
CASE WHEN e.sessions_7d <= 1 THEN 25 ELSE 0 END +
CASE WHEN e.avg_session_duration < 120 THEN 15 ELSE 0 END +
CASE WHEN e.levels_completed_7d = 0 THEN 10 ELSE 0 END +
CASE WHEN e.quests_abandoned_7d > 3 THEN 15 ELSE 0 END +
CASE WHEN e.social_interactions_7d = 0 THEN 10 ELSE 0 END -
CASE WHEN e.purchases_7d > 0 THEN 20 ELSE 0 END
) >= 30 THEN 'medium_risk'
ELSE 'retained'
END AS churn_risk_tier
FROM player_engagement_7d e
JOIN player_profiles FOR SYSTEM_TIME AS OF NOW() p
ON e.player_id = p.player_id;
Pushing Churn Signals to Retention Systems
Stream high-risk players to a Kafka topic for CRM and in-game retention systems:
CREATE SINK churn_risk_to_kafka
FROM churn_risk_scores
WITH (
connector = 'kafka',
topic = 'churn-risk-live',
properties.bootstrap.server = 'kafka:9092'
) FORMAT UPSERT ENCODE JSON;
A retention microservice consumes churn-risk-live, checks churn_risk_tier, and dispatches the appropriate intervention: an in-game bonus offer for high_risk, a push notification for lapsed, or a guild recommendation for players with zero social interactions.
Churn Prediction Approach Comparison
| Dimension | Nightly ML Pipeline | Weekly Batch Report | RisingWave Streaming |
| Score freshness | 12–24 hours | 7 days | < 1 minute |
| Intervention window | Next-day email only | Weekly campaign | Same-session trigger |
| Model complexity | High (ML model) | Simple rules | SQL rule engine |
| Engineering overhead | Data scientist + pipeline | Analyst | SQL developer |
| VIP player sensitivity | Same latency | Same latency | Priority scoring possible |
| Integration with game backend | API call delay | Bulk import | Kafka consumer |
FAQ
Q: Can we replace the rule-based score with an ML model score in RisingWave? A: RisingWave computes features continuously; you can export these features to a model serving layer that pushes scores back via Kafka or a JDBC sink. For some studios, a well-tuned rule-based score in SQL outperforms a complex ML model in production because it is easier to debug and adjust.
Q: How do we measure whether the real-time churn intervention is actually working? A: Tag players who received an intervention and compare their 7-day and 30-day retention rates against a control group that did not receive the intervention. RisingWave can track intervention status as a separate materialized view joined back to engagement data.
Q: What is the difference between this approach and using a CDP (Customer Data Platform)? A: CDPs aggregate behavioral data but typically update on a delay of hours to a day. RisingWave processes events as they arrive. For real-time same-session interventions, RisingWave is the right tool; CDPs excel at longer-horizon segmentation and CRM synchronization.
Q: Can we score differently based on a player's lifetime value or VIP tier?
A: Yes. The p.vip_tier field from player_profiles is already joined into the view. Add a multiplier to the score calculation for high-VIP players to prioritize their retention interventions.
Q: How do we handle players who are genuinely inactive on vacation versus churning? A: Add a field from player metadata for "last return after absence" patterns. Long absences followed by returns suggest seasonal players, not churners. Filter them from high-risk classification using a subquery or a separate materialized view tracking return patterns.
Catch Players Before They Leave
The highest-value churn intervention is the one that happens in the session where disengagement begins. Batch churn models never reach that window. Real-time streaming SQL does.
- Start building churn prevention with RisingWave: https://docs.risingwave.com/get-started
- Talk to other gaming teams about retention analytics: https://risingwave.com/slack
