Cross-Platform Player Analytics with Streaming SQL

RisingWave unifies cross-platform player analytics by streaming events from PC, console, and mobile into shared materialized views that maintain a single, continuously updated view of player behavior across all platforms—without the data silos and reconciliation delays typical of platform-specific analytics pipelines.

The Cross-Platform Analytics Challenge

Modern games ship on PC (Steam, Epic), consoles (PlayStation, Xbox, Nintendo Switch), and mobile (iOS, Android)—often simultaneously. Cross-platform play and shared progression are now expected by players. But analytics infrastructure rarely keeps pace: each platform has its own event format, identity system, and SDK, resulting in data silos that make holistic player understanding nearly impossible.

Studios that can answer "how do cross-platform players behave differently from single-platform players?" have a fundamental competitive advantage in game design, monetization, and retention strategy. Answering it requires a unified streaming pipeline that ingests, normalizes, and correlates events across platforms in real time.

Unified Event Schema

The first step is normalizing events from all platforms into a canonical schema before they hit RisingWave:

CREATE SOURCE unified_player_events (
    event_id        VARCHAR,
    player_id       BIGINT,
    platform_user_id VARCHAR,   -- platform-native ID (Steam64, PSN ID, etc.)
    platform        VARCHAR,    -- 'pc_steam', 'pc_epic', 'ps5', 'xbox', 'ios', 'android', 'switch'
    game_id         VARCHAR,
    event_type      VARCHAR,
    session_id      VARCHAR,
    level_id        VARCHAR,
    session_length_s INT,
    event_value     DOUBLE PRECISION,
    app_version     VARCHAR,
    event_time      TIMESTAMPTZ
)
WITH (
    connector = 'kafka',
    topic = 'unified-player-events',
    properties.bootstrap.server = 'kafka:9092',
    scan.startup.mode = 'latest'
)
FORMAT PLAIN ENCODE JSON;

Platform-specific normalization happens in a lightweight transformation layer (Kafka Streams, Flink, or a simple microservice) before events reach RisingWave. This keeps the analytics database clean and platform-agnostic.

Player Identity Mapping

Cross-platform players may use different IDs on each platform. The identity mapping table links them to a canonical player_id:

CREATE TABLE player_platform_identities (
    player_id           BIGINT,
    platform            VARCHAR,
    platform_user_id    VARCHAR,
    linked_at           TIMESTAMPTZ,
    PRIMARY KEY (platform, platform_user_id)
);

Cross-Platform Engagement Comparison

This view computes core engagement metrics by platform, enabling apples-to-apples comparison:

CREATE MATERIALIZED VIEW platform_engagement_daily AS
SELECT
    window_start::DATE                          AS activity_date,
    game_id,
    platform,
    COUNT(DISTINCT player_id)                   AS dau,
    COUNT(DISTINCT session_id)                  AS total_sessions,
    ROUND(
        COUNT(DISTINCT session_id)::NUMERIC
        / NULLIF(COUNT(DISTINCT player_id), 0),
    2)                                          AS sessions_per_player,
    AVG(session_length_s) FILTER (
        WHERE event_type = 'session_end'
    )                                           AS avg_session_length_s,
    MAX(session_length_s) FILTER (
        WHERE event_type = 'session_end'
    )                                           AS max_session_length_s,
    COUNT(*) FILTER (WHERE event_type = 'purchase')     AS purchases,
    SUM(event_value) FILTER (WHERE event_type = 'purchase') AS revenue
FROM TUMBLE(unified_player_events, event_time, INTERVAL '1 day')
GROUP BY window_start, game_id, platform;

When you discover that Switch players have 28% shorter sessions than PC players but 45% higher purchase conversion, you have the data to design a platform-optimized monetization strategy.

Cross-Platform Player Behavior Profile

Identify players who are active on multiple platforms—they behave differently and are often your most engaged audience:

CREATE MATERIALIZED VIEW cross_platform_players AS
SELECT
    player_id,
    game_id,
    window_end::DATE                            AS snapshot_date,
    COUNT(DISTINCT platform)                    AS active_platforms,
    SUM(event_value) FILTER (WHERE event_type = 'purchase')     AS total_spend,
    COUNT(DISTINCT session_id)                  AS total_sessions,
    MAX(event_time)                             AS last_active,
    ARRAY_AGG(DISTINCT platform)                AS platforms_used
FROM HOP(unified_player_events, event_time, INTERVAL '1 day', INTERVAL '30 days')
GROUP BY player_id, game_id, window_end
HAVING COUNT(DISTINCT platform) > 1;

Cross-platform players typically show 2-3x higher LTV than single-platform players. Identifying them in real time lets you target them with cross-platform exclusive content and retention campaigns before they churn.

Platform-Specific Feature Usage

Track which game features are used differently across platforms—critical for informing platform-specific UX decisions:

CREATE MATERIALIZED VIEW feature_usage_by_platform AS
SELECT
    window_start,
    window_end,
    game_id,
    platform,
    event_type                                          AS feature_name,
    COUNT(*)                                            AS usage_count,
    COUNT(DISTINCT player_id)                           AS unique_users,
    AVG(event_value)                                    AS avg_value
FROM TUMBLE(unified_player_events, event_time, INTERVAL '1 hour')
WHERE event_type NOT IN ('session_start', 'session_end', 'purchase')
GROUP BY window_start, window_end, game_id, platform, event_type;

Comparison: Cross-Platform Analytics Approaches

Sinking Unified Analytics to Your Data Platform

CREATE SINK platform_analytics_to_iceberg
FROM platform_engagement_daily
WITH (
    connector = 'iceberg',
    type = 'append-only',
    catalog.type = 's3',
    s3.region = 'us-west-2',
    s3.bucket = 'game-data-lake',
    s3.path = 'cross-platform/engagement/'
);

Archive the unified analytics to your data lake for long-term analysis, machine learning feature pipelines, and cross-season trend reporting—while RisingWave continues serving real-time queries.

Actionable Cross-Platform Insights

Platform-specific churn signals: If Android players stop playing after day 7 at twice the rate of iOS players, the Android experience has an issue. Streaming data surfaces this within the first week, not the first month.

Optimal cross-platform push timing: Cross-platform players shift between devices throughout the day. Real-time data shows when a player transitions from mobile (morning commute) to PC (evening)—ideal for timing platform-specific push notifications.

Version adoption velocity: Track app_version adoption per platform. If iOS players upgrade 3x faster than Android players, your analytics need to account for version-segmented behavior differently per platform.

Cross-platform progression parity: Streaming data verifies that shared progression (XP, items, currency) is consistent across platforms. Discrepancies surface as anomalies in the player_id-level aggregations.

FAQ

Q: How do we handle players who use different accounts on different platforms? A: This is an identity resolution problem. RisingWave stores the player_platform_identities mapping table. For players who link accounts, the canonical player_id is set at link time. For unlinked accounts, each platform user appears as a separate player until they connect their accounts in-game.

Q: Can we do real-time platform comparison for A/B tests running simultaneously across platforms? A: Yes. Add platform to the experiment_assignments table and include it in your A/B test materialized view. You can then query variant performance per platform separately—critical since the same variant often performs differently on mobile vs. PC.

Q: What about platform-specific events (e.g., PlayStation trophy unlocks)? A: Include a platform_metadata JSON column in the event schema for platform-specific fields. Parse it in platform-specific materialized views while keeping the core unified views clean.

Q: How do we normalize session length across platforms where the definition differs? A: Define session boundaries consistently at the normalization layer before events hit RisingWave. A "session" should mean the same thing regardless of platform—typically a continuous play period with no gap exceeding N minutes.

Q: Can RisingWave join real-time events with historical player data stored in PostgreSQL? A: Yes. Use a PostgreSQL CDC source (postgres-cdc connector) to stream changes from your PostgreSQL player database into RisingWave, then join with real-time events in materialized views using standard SQL joins.

Get Started

Build unified cross-platform analytics and understand your players across every device:

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