Ad Monetization Analytics for Games with RisingWave

Ad monetization analytics with RisingWave means your eCPM, fill rate, and revenue-per-user metrics update continuously from a live event stream — no hourly batch jobs, no stale dashboards. You write standard SQL materialized views over Kafka-ingested ad events, and RisingWave maintains them incrementally so ad ops teams can optimize placements and waterfalls within the same day.

The Problem with Delayed Ad Analytics

Mobile and PC free-to-play games typically earn the majority of revenue through advertising. Rewarded video, interstitials, and banner ads generate thousands of impression events per second across a live title. Yet most studios analyze these events hours after the fact, using aggregated reports from ad networks or nightly ETL pipelines.

The consequence is slow reaction to problems: a poorly-placed interstitial causing session abandonment, a high-fill waterfall partner going dark, or a surge of low-eCPM inventory that could be rerouted. Real-time ad analytics closes this gap.

Ingesting Ad Events from Kafka

Ad mediation SDKs (AppLovin MAX, Google AdMob, ironSource) emit impression-level data that can be forwarded to Kafka. Create a RisingWave source to ingest it:

CREATE SOURCE ad_events (
    event_id      VARCHAR,
    player_id     BIGINT,
    ad_unit_id    VARCHAR,
    ad_network    VARCHAR,
    ad_type       VARCHAR,        -- 'rewarded', 'interstitial', 'banner'
    event_type    VARCHAR,        -- 'impression', 'click', 'complete', 'skip'
    ecpm_usd      NUMERIC,
    revenue_usd   NUMERIC,
    country       VARCHAR,
    platform      VARCHAR,        -- 'ios', 'android', 'pc'
    event_time    TIMESTAMPTZ
) WITH (
    connector = 'kafka',
    topic = 'ad-events',
    properties.bootstrap.server = 'kafka:9092',
    scan.startup.mode = 'latest'
) FORMAT PLAIN ENCODE JSON;

Also ingest a reference table of ad unit configurations:

CREATE TABLE ad_units (
    ad_unit_id    VARCHAR PRIMARY KEY,
    placement     VARCHAR,        -- 'lobby', 'end_of_level', 'shop'
    game_id       VARCHAR,
    floor_ecpm    NUMERIC
);

Core Metrics: eCPM, Fill Rate, Revenue per User

The heart of ad monetization analytics is a materialized view that computes per-ad-unit metrics over a sliding window:

CREATE MATERIALIZED VIEW ad_metrics_5min AS
SELECT
    ad_unit_id,
    ad_network,
    ad_type,
    country,
    window_start,
    window_end,
    COUNT(*) FILTER (WHERE event_type = 'impression')              AS impressions,
    COUNT(*) FILTER (WHERE event_type = 'click')                   AS clicks,
    COUNT(*) FILTER (WHERE event_type = 'complete')                AS completions,
    SUM(revenue_usd)                                               AS total_revenue,
    AVG(ecpm_usd)                                                  AS avg_ecpm,
    CASE WHEN COUNT(*) FILTER (WHERE event_type = 'impression') > 0
         THEN COUNT(*) FILTER (WHERE event_type = 'click')::NUMERIC
              / COUNT(*) FILTER (WHERE event_type = 'impression')
         ELSE 0
    END AS ctr
FROM HOP(ad_events, event_time, INTERVAL '1 minute', INTERVAL '5 minutes')
GROUP BY ad_unit_id, ad_network, ad_type, country, window_start, window_end;

The HOP() window function produces a 5-minute sliding window that advances every minute, giving ad ops a continuously refreshing view without waiting for a full window to close.

Detecting Waterfall Anomalies in Real Time

Build a second materialized view to flag ad networks whose fill rate drops below an operational threshold:

CREATE MATERIALIZED VIEW network_fill_health AS
SELECT
    m.ad_network,
    m.ad_unit_id,
    u.placement,
    m.window_start,
    SUM(m.impressions)                                         AS total_impressions,
    AVG(m.avg_ecpm)                                            AS rolling_ecpm,
    SUM(m.total_revenue)                                       AS rolling_revenue,
    CASE
        WHEN AVG(m.avg_ecpm) < u.floor_ecpm THEN 'below_floor'
        WHEN SUM(m.impressions) < 10          THEN 'low_fill'
        ELSE 'healthy'
    END AS health_status
FROM ad_metrics_5min m
JOIN ad_units u ON m.ad_unit_id = u.ad_unit_id
GROUP BY m.ad_network, m.ad_unit_id, u.placement, u.floor_ecpm, m.window_start;

When health_status flips to below_floor or low_fill, downstream alerting systems (PagerDuty, Slack webhook) can page the ad ops team immediately.

Streaming Results to Dashboards and Sinks

Push aggregated metrics to a downstream Kafka topic for your real-time dashboard:

CREATE SINK ad_metrics_to_kafka
FROM ad_metrics_5min
WITH (
    connector = 'kafka',
    topic = 'ad-analytics-live',
    properties.bootstrap.server = 'kafka:9092'
) FORMAT APPEND ENCODE JSON;

For long-term storage and BI tools, write to JDBC (e.g., PostgreSQL or a metrics store):

CREATE SINK ad_metrics_to_postgres
FROM network_fill_health
WITH (
    connector = 'jdbc',
    jdbc.url = 'jdbc:postgresql://analytics-db:5432/ads',
    table.name = 'network_fill_health'
) FORMAT PLAIN ENCODE JSON;

Ad Analytics Approach Comparison

Revenue Attribution and Session Context

One advantage of processing ad events alongside gameplay events in the same platform is contextual attribution. You can join ad revenue back to the game session that generated it:

CREATE MATERIALIZED VIEW revenue_by_game_context AS
SELECT
    a.player_id,
    a.ad_type,
    a.country,
    SUM(a.revenue_usd)    AS ad_revenue,
    COUNT(*)               AS ad_impressions
FROM ad_events a
WHERE a.event_type = 'impression'
  AND a.event_time >= NOW() - INTERVAL '1 hour'
GROUP BY a.player_id, a.ad_type, a.country;

This lets your analytics team answer questions like: "Do players who watch rewarded video in the first session retain better than those who do not?" — using the same streaming SQL infrastructure.

FAQ

Q: Can RisingWave ingest impression-level data at the scale of a major mobile title? A: A mid-tier mobile game can generate millions of impressions per day. RisingWave handles millions of events per second when horizontally scaled. Kafka partitioning distributes load, and materialized views compute incrementally rather than reprocessing full history.

Q: How do I handle late-arriving events from mobile SDKs that buffer offline? A: Configure your Kafka producer to use the SDK's event timestamp as the event time field. RisingWave's watermark mechanism allows you to accept late events within a configurable window before advancing the computation.

Q: Is the PostgreSQL interface compatible with common BI tools like Grafana or Metabase? A: Yes. RisingWave's PostgreSQL wire protocol works with any tool that has a Postgres connector, including Grafana, Metabase, Superset, and Tableau.

Q: Can I run A/B tests on ad placement using this infrastructure? A: Yes. Add an experiment_arm field to your ad event schema, then filter your materialized views by arm. RisingWave maintains separate aggregations per arm without additional pipeline complexity.

Q: How do I ensure GDPR compliance when streaming ad events with player IDs? A: Process pseudonymized player IDs in the streaming layer. Keep the mapping table in a separate, access-controlled store and do not replicate it to analytics sinks. RisingWave itself does not persist raw events beyond its internal state store.

Build Your Real-Time Ad Analytics Stack

Real-time ad monetization analytics is the difference between reacting to revenue drops in minutes versus discovering them the next morning. With RisingWave, a single SQL materialized view replaces an entire batch pipeline.

• Get started with RisingWave in under five minutes: https://docs.risingwave.com/get-started
• Connect with the streaming analytics community: https://risingwave.com/slack