Battle Pass Analytics with Streaming SQL

RisingWave tracks battle pass progression in real time by streaming tier unlock events into continuously updated materialized views. Game monetization teams get live visibility into completion rates, XP velocity, and tier-gating drop-off—enabling same-day balance adjustments instead of post-season postmortems.

Why Battle Pass Analytics Needs Real-Time Data

The battle pass is one of the most important monetization mechanics in modern games. Players pay upfront for a season of content gated behind progressive tiers, creating pressure to engage regularly to get value from their purchase. For studios, it's a reliable recurring revenue model—but only if the XP curve, tier rewards, and seasonal duration are properly calibrated.

If the XP curve is too steep, players plateau at tier 40 of 100, feel cheated, and don't renew next season. If it's too easy, players finish in week two and churn until the next season. Live studios iterate on these parameters mid-season, but only if they have the data to act on.

Traditional analytics pipelines provide weekly summaries. By the time you know players are stuck at tier 45, three weeks of the season have passed. Streaming analytics with RisingWave gives you tier-by-tier drop-off data within hours of a season launch.

Ingesting Battle Pass Events

CREATE SOURCE battle_pass_events (
    player_id       BIGINT,
    season_id       VARCHAR,
    event_type      VARCHAR,    -- 'xp_earned', 'tier_unlocked', 'pass_purchased', 'reward_claimed'
    tier_number     INT,
    xp_amount       INT,
    is_premium      BOOLEAN,
    event_time      TIMESTAMPTZ
)
WITH (
    connector = 'kafka',
    topic = 'battle-pass-events',
    properties.bootstrap.server = 'kafka:9092',
    scan.startup.mode = 'latest'
)
FORMAT PLAIN ENCODE JSON;

Season Reference Data

Store the expected XP thresholds and reward metadata for each tier in a reference table:

CREATE TABLE battle_pass_tiers (
    season_id       VARCHAR,
    tier_number     INT,
    xp_required     INT,
    reward_type     VARCHAR,   -- 'cosmetic', 'currency', 'consumable'
    is_premium_only BOOLEAN,
    PRIMARY KEY (season_id, tier_number)
);

Real-Time Tier Progression Funnel

This materialized view computes how many players have reached each tier, updated continuously as XP events stream in:

CREATE MATERIALIZED VIEW tier_progression_funnel AS
SELECT
    season_id,
    tier_number,
    COUNT(DISTINCT player_id)                                           AS players_at_tier,
    COUNT(DISTINCT player_id) FILTER (WHERE is_premium = true)         AS premium_players_at_tier,
    COUNT(DISTINCT player_id) FILTER (WHERE is_premium = false)        AS free_players_at_tier,
    SUM(xp_amount)                                                      AS total_xp_earned_for_tier,
    AVG(xp_amount)                                                      AS avg_xp_per_event
FROM battle_pass_events
WHERE event_type = 'tier_unlocked'
GROUP BY season_id, tier_number;

Query this view to see exactly where players are dropping off in the tier funnel. A sharp drop between tier 30 and tier 31 flags an XP curve problem or a weak reward at that threshold.

XP Velocity and Completion Rate Tracking

Understanding how quickly players progress is as important as how far they get. This windowed view tracks XP velocity per 24-hour window:

CREATE MATERIALIZED VIEW xp_velocity_daily AS
SELECT
    season_id,
    window_start::DATE          AS activity_date,
    COUNT(DISTINCT player_id)   AS active_players,
    SUM(xp_amount)              AS total_xp_earned,
    AVG(xp_amount)              AS avg_xp_per_event,
    MAX(xp_amount)              AS max_xp_single_event,
    COUNT(*)                    AS total_xp_events,
    ROUND(SUM(xp_amount)::NUMERIC / NULLIF(COUNT(DISTINCT player_id), 0), 1)
                                AS avg_xp_per_player
FROM TUMBLE(battle_pass_events, event_time, INTERVAL '1 day')
WHERE event_type = 'xp_earned'
GROUP BY season_id, window_start;

If avg_xp_per_player drops significantly mid-week, it correlates with either reduced engagement or a specific XP source being exploited or bugged.

Battle Pass Revenue and Conversion Monitoring

Track the live conversion funnel from free to premium tier:

CREATE MATERIALIZED VIEW pass_purchase_metrics AS
SELECT
    window_start,
    window_end,
    season_id,
    COUNT(DISTINCT player_id) FILTER (WHERE event_type = 'pass_purchased')  AS purchases,
    COUNT(DISTINCT player_id) FILTER (WHERE event_type = 'xp_earned')       AS engaged_free_players,
    ROUND(
        COUNT(DISTINCT player_id) FILTER (WHERE event_type = 'pass_purchased')::NUMERIC
        / NULLIF(COUNT(DISTINCT player_id) FILTER (WHERE event_type = 'xp_earned'), 0) * 100,
    2)                                                                       AS purchase_conversion_pct
FROM TUMBLE(battle_pass_events, event_time, INTERVAL '1 hour')
GROUP BY window_start, window_end, season_id;

Comparison: Battle Pass Analytics Approaches

Sinking Analytics to Your Data Warehouse

For long-term retention and cross-season comparison, sink battle pass metrics to Iceberg:

CREATE SINK battle_pass_to_iceberg
FROM tier_progression_funnel
WITH (
    connector = 'iceberg',
    type = 'append-only',
    catalog.type = 's3',
    s3.region = 'us-east-1',
    s3.bucket = 'game-analytics-lake',
    s3.path = 'battle-pass/tier-funnel/'
);

This creates a durable historical record of each season's progression funnel, enabling cross-season comparison and seasonal planning.

Practical Insights from Streaming Battle Pass Data

Reward gap detection: If premium players progress normally through tiers 1-49 but stall at tier 50, the tier 50 reward may be underwhelming. Streaming data surfaces this pattern within days, not after the season ends.

Weekend vs. weekday XP velocity: The xp_velocity_daily view shows clear weekly patterns. Use this to time XP boost events on lower-engagement days to smooth out the progression curve.

Last-minute purchase surge: Watch pass_purchase_metrics in the final week of a season. A spike in conversions as the deadline approaches is normal—use it to calibrate end-of-season reminder messaging timing.

FAQ

Q: Can I track individual player progress in real time, not just aggregate metrics? A: Yes. Create a materialized view grouped by player_id and season_id to track each player's current tier and total XP earned. This powers in-game progress displays and personalized push notifications.

Q: How do I handle XP bonuses from paid boosts without skewing velocity averages? A: Add a source_type column to the event schema distinguishing base XP from bonus XP. Filter or segment in the materialized view to compute velocity for each XP source separately.

Q: Can RisingWave alert us when tier completion rates drop below forecast? A: Indirectly—create a Kafka sink from your progression views to an alerting consumer. The consumer compares current rates against a forecast table (also stored in RisingWave) and fires alerts when the gap exceeds a threshold.

Q: What's the best way to handle mid-season XP curve rebalances? A: Store the effective XP requirement per tier and date in battle_pass_tiers with a valid_from column. Use a temporal join (FOR SYSTEM_TIME AS OF) to apply the correct threshold at the time of each event, keeping historical data accurate after rebalances.

Q: How does this scale for games with 10+ million concurrent players? A: RisingWave scales horizontally. The key is partitioning Kafka topics by season_id or player_id hash to parallelize ingestion. Materialized view computation scales with the number of RisingWave compute nodes, typically handling millions of events per second across the cluster.

Get Started

Build real-time battle pass analytics that drive smarter season design:

• Documentation: docs.risingwave.com/get-started
• Community: Join the conversation on RisingWave Slack