Real-Time Quest and Achievement Tracking with RisingWave

RisingWave tracks quest and achievement progress in real time by streaming player action events through incrementally maintained materialized views. Game teams get live visibility into completion rates, bottleneck quests, and achievement unlock velocity—enabling same-day content balance adjustments instead of relying on weekly analytics reports.

Why Real-Time Quest Tracking Changes Game Design

Quests and achievements are the scaffolding of player progression. They direct players toward content, teach game mechanics, and provide the sense of accomplishment that drives retention. But quest design is notoriously difficult to balance: too easy and players feel no accomplishment; too hard and they bounce.

Traditional analytics surfaces quest completion rates in weekly reports. By the time your analytics team flags that only 12% of players complete the critical onboarding quest that teaches the crafting system, thousands of players have already dropped off at that exact bottleneck.

Real-time quest analytics means your design team sees the 12% completion rate within the first few hours of a content patch, giving them time to hotfix quest objectives or add tutorial hints before the week is out.

Ingesting Quest Progress Events

CREATE SOURCE quest_events (
    player_id       BIGINT,
    game_id         VARCHAR,
    quest_id        VARCHAR,
    quest_type      VARCHAR,    -- 'main', 'side', 'daily', 'weekly', 'event'
    event_type      VARCHAR,    -- 'quest_started', 'objective_completed', 'quest_completed', 'quest_abandoned'
    objective_id    VARCHAR,
    progress_value  INT,
    required_value  INT,
    event_time      TIMESTAMPTZ
)
WITH (
    connector = 'kafka',
    topic = 'quest-events',
    properties.bootstrap.server = 'kafka:9092',
    scan.startup.mode = 'latest'
)
FORMAT PLAIN ENCODE JSON;

Quest Metadata Reference Table

Store quest design metadata—expected difficulty, content chapter, unlock level—for context-aware analytics:

CREATE TABLE quest_metadata (
    quest_id            VARCHAR PRIMARY KEY,
    quest_name          VARCHAR,
    quest_type          VARCHAR,
    chapter             INT,
    designed_difficulty VARCHAR,   -- 'easy', 'medium', 'hard', 'optional'
    expected_completion_pct DOUBLE PRECISION,
    time_limit_hours    INT
);

Real-Time Quest Completion Funnel

This materialized view computes the live completion funnel for every quest, showing started vs. completed vs. abandoned rates:

CREATE MATERIALIZED VIEW quest_completion_funnel AS
SELECT
    q.quest_id,
    m.quest_name,
    m.quest_type,
    m.designed_difficulty,
    m.expected_completion_pct,
    COUNT(DISTINCT player_id) FILTER (WHERE event_type = 'quest_started')       AS players_started,
    COUNT(DISTINCT player_id) FILTER (WHERE event_type = 'quest_completed')     AS players_completed,
    COUNT(DISTINCT player_id) FILTER (WHERE event_type = 'quest_abandoned')     AS players_abandoned,
    ROUND(
        COUNT(DISTINCT player_id) FILTER (WHERE event_type = 'quest_completed')::NUMERIC
        / NULLIF(COUNT(DISTINCT player_id) FILTER (WHERE event_type = 'quest_started'), 0) * 100,
    2)                                                                           AS completion_rate_pct,
    ROUND(
        COUNT(DISTINCT player_id) FILTER (WHERE event_type = 'quest_abandoned')::NUMERIC
        / NULLIF(COUNT(DISTINCT player_id) FILTER (WHERE event_type = 'quest_started'), 0) * 100,
    2)                                                                           AS abandonment_rate_pct
FROM quest_events q
LEFT JOIN quest_metadata m ON q.quest_id = m.quest_id
GROUP BY q.quest_id, m.quest_name, m.quest_type, m.designed_difficulty, m.expected_completion_pct;

By joining with quest_metadata, you can immediately flag quests where completion_rate_pct is significantly below expected_completion_pct—the signal that a quest needs attention.

Hourly Quest Activity and Bottleneck Detection

Track quest activity velocity to identify time-of-day patterns and content bottlenecks:

CREATE MATERIALIZED VIEW quest_activity_hourly AS
SELECT
    window_start,
    window_end,
    quest_id,
    quest_type,
    COUNT(DISTINCT player_id)                                               AS active_players,
    COUNT(*) FILTER (WHERE event_type = 'objective_completed')              AS objectives_completed,
    COUNT(*) FILTER (WHERE event_type = 'quest_completed')                  AS quests_completed,
    COUNT(*) FILTER (WHERE event_type = 'quest_abandoned')                  AS quests_abandoned,
    AVG(progress_value::DOUBLE PRECISION / NULLIF(required_value, 0) * 100) AS avg_progress_pct
FROM TUMBLE(quest_events, event_time, INTERVAL '1 hour')
GROUP BY window_start, window_end, quest_id, quest_type;

When a specific objective_id shows near-zero completions while the quest has high starts, that objective is the bottleneck. Design team gets the signal immediately.

Achievement Unlock Velocity Tracking

Achievements track differently from quests—they often have long completion horizons (e.g., "complete 1000 PvP matches"). A separate source tracks achievement-specific events:

CREATE SOURCE achievement_events (
    player_id       BIGINT,
    game_id         VARCHAR,
    achievement_id  VARCHAR,
    achievement_tier VARCHAR,   -- 'bronze', 'silver', 'gold', 'platinum'
    progress_value  INT,
    required_value  INT,
    is_unlocked     BOOLEAN,
    event_time      TIMESTAMPTZ
)
WITH (
    connector = 'kafka',
    topic = 'achievement-events',
    properties.bootstrap.server = 'kafka:9092',
    scan.startup.mode = 'latest'
)
FORMAT PLAIN ENCODE JSON;

CREATE MATERIALIZED VIEW achievement_unlock_rates AS
SELECT
    window_start::DATE                          AS unlock_date,
    game_id,
    achievement_id,
    achievement_tier,
    COUNT(DISTINCT player_id) FILTER (WHERE is_unlocked = true)     AS new_unlocks,
    COUNT(DISTINCT player_id)                                       AS players_with_progress,
    AVG(progress_value::DOUBLE PRECISION / NULLIF(required_value, 0) * 100)
                                                AS avg_progress_pct,
    COUNT(DISTINCT player_id) FILTER (
        WHERE progress_value::DOUBLE PRECISION / NULLIF(required_value, 0) >= 0.9
        AND is_unlocked = false
    )                                           AS near_completion_players
FROM TUMBLE(achievement_events, event_time, INTERVAL '1 day')
GROUP BY window_start, game_id, achievement_id, achievement_tier;

The near_completion_players column is gold for engagement teams: players who are 90%+ of the way to an achievement are prime targets for a timely nudge notification.

Comparison: Quest Tracking Approaches

Capability	Log Analysis (Ad Hoc)	Weekly Batch Reports	Real-Time (RisingWave)
Bottleneck detection speed	Hours of manual work	Next week	Hours after launch
Completion rate accuracy	Approximate	Accurate (stale)	Accurate (live)
Achievement nudge targeting	Not possible	Batch campaign	Real-time trigger
Daily/weekly quest refresh	Manual query	Overnight job	Automatic
Design iteration speed	Days	Weeks	Same-day hotfix
Infrastructure required	Log tools + manual work	ETL + DW + BI	Single streaming DB

Sinking Quest Analytics to Your Live Operations Platform

CREATE SINK quest_funnel_to_kafka
FROM quest_completion_funnel
WITH (
    connector = 'kafka',
    topic = 'quest-analytics-live',
    properties.bootstrap.server = 'kafka:9092'
)
FORMAT PLAIN ENCODE JSON;

Your live ops dashboard subscribes to this topic and displays a live quest health table—color-coded by deviation from expected completion rate—giving designers instant signal on content balance.

Operational Best Practices

Daily quest reset tracking: For daily quests that reset at midnight, use a TUMBLE window aligned to UTC midnight. Add quest_type = 'daily' filters to isolate daily quest performance from multi-day quests.

Seasonal event quest monitoring: During seasonal events with limited-time quests, the quest_activity_hourly view shows real-time participation. If an event quest has fewer than expected starts two hours after launch, investigate whether the quest trigger is firing correctly.

Tutorial funnel depth: Map your tutorial as a sequence of quests and track the cumulative drop-off at each stage using quest_completion_funnel filtered by quest_type = 'main'. This is your most important retention funnel.

FAQ

Q: How do we track multi-step quests where players must complete objectives in sequence? A: Use objective_id to distinguish steps within a quest. The avg_progress_pct in quest_activity_hourly tracks overall progress, while per-objective filtering in a derived view shows exactly where in the sequence players are stopping.

Q: Can we use this data to dynamically adjust quest difficulty mid-season? A: RisingWave provides the data signal. The adjustment happens in your game server configuration system. When completion_rate_pct drops below a threshold in quest_completion_funnel, trigger a configuration update via your live ops tooling—a process your platform's API would handle, not RisingWave.

Q: How do we handle abandoned quests that players restart after abandonment? A: Track restarts as a new quest_started event. The funnel view counts unique players per event type, so a player who starts, abandons, and restarts counts once in players_started and once in players_abandoned—giving an accurate picture of friction without double-counting.

Q: Can achievement progress data power personalized recommendations? A: Yes. The near_completion_players count from achievement_unlock_rates feeds your recommendation engine. When a player is 90%+ toward an achievement, your push notification service can send a personalized "You're almost there!" message—a high-conversion engagement trigger.

Q: How do we retain historical quest completion data for cross-season analysis? A: Sink quest_completion_funnel to Iceberg periodically using a scheduled snapshot. This creates an immutable historical record per season, enabling year-over-year quest design quality comparison.

Get Started

Build real-time quest and achievement analytics that make your game better, faster:

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