Player Purchase Behavior Analytics with a Streaming Database

A streaming database like RisingWave continuously processes purchase events from your game's transaction pipeline, maintaining always-current views of spending velocity, conversion funnels, and high-value player segments—without scheduled batch jobs or stale ETL pipelines.

Why Purchase Behavior Analytics Must Be Real-Time

Player purchase behavior is one of the most volatile signals in a live game. A well-timed limited-time offer can 10x conversion for a 6-hour window. A price point misconfiguration can tank revenue for a weekend. Without real-time visibility, monetization teams are flying blind.

The traditional approach—nightly ETL from transaction databases into a data warehouse—means you see yesterday's purchase patterns at best. By the time you identify that a new bundle isn't converting, the sale window may have already closed.

RisingWave changes this by treating your transaction event stream as a continuously queryable database. Purchase events land in Kafka, RisingWave materializes spend analytics, and your monetization team queries live conversion rates, ARPU trends, and whale activity—all in real time.

Setting Up the Transaction Event Source

CREATE SOURCE purchase_events (
    transaction_id  VARCHAR,
    player_id       BIGINT,
    game_id         VARCHAR,
    product_id      VARCHAR,
    product_type    VARCHAR,    -- 'bundle', 'currency', 'cosmetic', 'battle_pass', 'booster'
    usd_amount      DOUBLE PRECISION,
    platform        VARCHAR,    -- 'ios', 'android', 'pc', 'console'
    country_code    VARCHAR,
    store           VARCHAR,    -- 'apple', 'google', 'steam', 'psn', 'xbox'
    purchase_time   TIMESTAMPTZ
)
WITH (
    connector = 'kafka',
    topic = 'purchase-transactions',
    properties.bootstrap.server = 'kafka:9092',
    scan.startup.mode = 'earliest'
)
FORMAT PLAIN ENCODE JSON;

Player Spend Segmentation in Real Time

Segmenting players by spend tier (free, minnow, dolphin, whale) is fundamental for monetization strategy. This materialized view maintains rolling 30-day spend per player:

CREATE MATERIALIZED VIEW player_spend_segments AS
SELECT
    player_id,
    game_id,
    window_end::DATE                        AS segment_date,
    SUM(usd_amount)                         AS spend_30d,
    COUNT(*)                                AS purchase_count_30d,
    AVG(usd_amount)                         AS avg_order_value_30d,
    MAX(usd_amount)                         AS max_single_purchase,
    MIN(purchase_time)                      AS first_purchase_in_window,
    MAX(purchase_time)                      AS last_purchase_in_window,
    CASE
        WHEN SUM(usd_amount) = 0            THEN 'free'
        WHEN SUM(usd_amount) < 5            THEN 'minnow'
        WHEN SUM(usd_amount) < 20           THEN 'dolphin'
        WHEN SUM(usd_amount) < 100          THEN 'spender'
        ELSE 'whale'
    END                                     AS spend_segment
FROM HOP(purchase_events, purchase_time, INTERVAL '1 day', INTERVAL '30 days')
GROUP BY player_id, game_id, window_end;

As new purchases arrive, the view updates each affected player's segment instantly. Your CRM system queries this view to trigger personalized offers to dolphins before they become whales—or retention messages to whales showing reduced activity.

Product Performance and Conversion Funnel

Track how each product type converts and generates revenue per hour:

CREATE MATERIALIZED VIEW product_performance_hourly AS
SELECT
    window_start,
    window_end,
    game_id,
    product_type,
    platform,
    COUNT(DISTINCT player_id)   AS unique_buyers,
    COUNT(*)                    AS total_transactions,
    SUM(usd_amount)             AS gross_revenue,
    AVG(usd_amount)             AS avg_transaction_value,
    MIN(usd_amount)             AS min_transaction,
    MAX(usd_amount)             AS max_transaction,
    COUNT(DISTINCT country_code) AS countries_reached
FROM TUMBLE(purchase_events, purchase_time, INTERVAL '1 hour')
GROUP BY window_start, window_end, game_id, product_type, platform;

This view powers your real-time monetization dashboard. When a flash sale goes live, you see conversion by product type updating live every minute as the window refreshes.

Revenue Velocity Anomaly Detection

Combine the hourly view with a reference table of expected revenue baselines to flag anomalies:

CREATE TABLE revenue_baselines (
    game_id         VARCHAR,
    product_type    VARCHAR,
    hour_of_week    INT,        -- 0-167 (0 = Monday midnight)
    expected_revenue DOUBLE PRECISION,
    lower_bound     DOUBLE PRECISION,
    upper_bound     DOUBLE PRECISION,
    PRIMARY KEY (game_id, product_type, hour_of_week)
);

A downstream alerting service joins product_performance_hourly against revenue_baselines to detect when revenue is outside expected bounds—flagging both underperformance (pricing bug, payment gateway issue) and overperformance (viral moment, exploit).

Comparison: Purchase Analytics Approaches

Sinking Spend Segments to Your CRM

CREATE SINK spend_segments_to_crm
FROM player_spend_segments
WITH (
    connector = 'jdbc',
    jdbc.url = 'jdbc:postgresql://crm-db:5432/players',
    table.name = 'player_spend_segments',
    type = 'upsert',
    primary_key = 'player_id,game_id,segment_date'
);

Your CRM system now has always-current spend segments for every player, enabling triggered campaigns the moment a player crosses a segment threshold.

Purchase Pattern Insights Enabled by Streaming

First-purchase velocity: Track how long after account creation players make their first purchase. If new players from a marketing campaign convert faster than organic users, double down on that channel.

Bundle vs. currency preference: The product_type breakdown in product_performance_hourly shows in real time whether a new bundle offer is cannibalizing currency pack revenue or expanding the spending pie.

Cross-platform spend differences: Compare avg_transaction_value by platform. PC players typically show higher AOV on cosmetics; mobile players on currency packs. Tailor offer timing and denomination to platform behavior using live data.

Weekend purchasing surge: Use the hour_of_week baseline table to identify that Saturday evenings see 40% higher conversion. Time your limited-time offer launches accordingly.

FAQ

Q: How do I connect RisingWave purchase data to a fraud detection system? A: Create a separate Kafka sink from purchase_events to a fraud detection topic. Your fraud service enriches transactions with risk scores and publishes back to Kafka, which RisingWave can ingest as a second source and join with purchase data for enriched analytics.

Q: Can I track LTV (Lifetime Value) per player in real time? A: Yes. Create a non-windowed materialized view that groups by player_id and accumulates SUM(usd_amount) across all time. This gives running LTV per player, updated on every new purchase event. Combine with a cohort table for cohort-adjusted LTV.

Q: How does RisingWave handle refunds and chargebacks? A: Publish refund events to the same Kafka topic with a negative usd_amount. RisingWave's SUM aggregation naturally nets these against purchases. Alternatively, use a separate refund_events source and subtract in your materialized view join.

Q: What's the best way to handle currency exchange for global games? A: Publish all transactions in a normalized currency (USD) by converting at the source (game server or payment processor). Alternatively, store a currency_rates reference table in RisingWave and join at query time using the rate closest to purchase_time.

Q: Can we power real-time personalized offer triggers from these views? A: Yes. Sink player_spend_segments to your CRM or offer engine via JDBC. The offer engine polls for segment changes or subscribes to a Kafka sink, then triggers personalized offers in real time when a player enters a new spend segment.

Get Started

Build the purchase analytics platform your monetization team needs:

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