Real-Time Audience Segmentation with a Streaming Database

Real-time audience segmentation with a streaming database means users move in and out of segments automatically—within seconds of a behavioral event—without batch jobs or nightly recalculations. RisingWave maintains continuously updated segment membership as materialized views, so your ad targeting, personalization, and suppression lists always reflect current user behavior.

The Stale Segment Problem

Most audience segmentation systems run as scheduled jobs. A user browses your pricing page, abandons their cart, and reads three product comparison articles—but they won't be moved into your "high-intent" retargeting segment until the nightly batch runs. By then, they may have already purchased from a competitor.

Stale segments waste retargeting budget on users who have already converted, miss users at the peak of their buying intent, and fail to suppress ads to users who just complained to support.

A streaming database fixes this by treating segment membership as a continuously updated query result rather than a periodic snapshot.

Architecture

The audience segmentation pipeline in RisingWave connects behavioral event streams (page views, product interactions, purchases) to segment definition logic expressed as materialized views. Segment membership outputs flow to downstream ad platforms, CRM systems, and personalization engines via Kafka or JDBC sinks.

Key design principles:

• Segment rules are SQL queries—easy to create, modify, and audit
• Segment membership updates automatically when new events arrive
• Multiple segments share the same underlying event streams, reducing infrastructure redundancy

Ingesting Behavioral Events

CREATE SOURCE user_events (
    event_id        VARCHAR,
    user_id         VARCHAR,
    session_id      VARCHAR,
    event_type      VARCHAR,
    page_url        VARCHAR,
    product_id      VARCHAR,
    category        VARCHAR,
    value           DECIMAL,
    event_time      TIMESTAMPTZ
)
WITH (
    connector = 'kafka',
    topic = 'user-behavioral-events',
    properties.bootstrap.server = 'kafka:9092',
    scan.startup.mode = 'latest'
)
FORMAT PLAIN ENCODE JSON;

Load segment definitions from your CRM or CDP via CDC:

CREATE SOURCE segment_definitions
FROM postgres-cdc WITH (
    hostname = 'cdp-db',
    port = '5432',
    username = 'replicator',
    password = 'secret',
    database.name = 'cdp',
    schema.name = 'public',
    table.name = 'segment_definitions',
    slot.name = 'risingwave_slot'
);

Defining Behavioral Segments

Compute segment membership using rolling behavioral windows:

CREATE MATERIALIZED VIEW high_intent_buyers AS
SELECT
    user_id,
    window_start,
    window_end,
    COUNT(*) FILTER (WHERE event_type = 'product_view')     AS product_views,
    COUNT(*) FILTER (WHERE event_type = 'add_to_cart')      AS cart_adds,
    COUNT(*) FILTER (WHERE event_type = 'checkout_start')   AS checkout_starts,
    SUM(value) FILTER (WHERE event_type = 'product_view')   AS viewed_value,
    MAX(event_time)                                          AS last_seen
FROM TUMBLE(user_events, event_time, INTERVAL '24 HOURS')
GROUP BY user_id, window_start, window_end
HAVING
    COUNT(*) FILTER (WHERE event_type = 'product_view') >= 3
    OR COUNT(*) FILTER (WHERE event_type = 'add_to_cart') >= 1;

Build a suppression segment for recent purchasers to avoid wasting retargeting budget:

CREATE MATERIALIZED VIEW recent_purchasers AS
SELECT
    user_id,
    window_start,
    window_end,
    COUNT(*) FILTER (WHERE event_type = 'purchase')         AS purchases,
    SUM(value) FILTER (WHERE event_type = 'purchase')       AS total_spend,
    MAX(event_time)                                          AS last_purchase_time
FROM TUMBLE(user_events, event_time, INTERVAL '7 DAYS')
GROUP BY user_id, window_start, window_end
HAVING COUNT(*) FILTER (WHERE event_type = 'purchase') >= 1;

Cross-Category Interest Segments

For more nuanced targeting, use session windows to detect users exploring specific product categories within a browsing session:

CREATE MATERIALIZED VIEW category_interest_sessions AS
SELECT
    user_id,
    category,
    window_start,
    window_end,
    COUNT(*)                AS page_views_in_session,
    SUM(value)              AS viewed_value_in_session
FROM SESSION(user_events, event_time, '30 MINUTES', user_id)
WHERE event_type = 'product_view'
GROUP BY user_id, category, window_start, window_end
HAVING COUNT(*) >= 2;

Comparison: Audience Segmentation Approaches

Exporting Segments to Ad Platforms

Push active segment members to Kafka for consumption by DSPs and ad servers:

CREATE SINK high_intent_segment_sink
FROM high_intent_buyers
WITH (
    connector = 'kafka',
    topic = 'audience-segment-high-intent',
    properties.bootstrap.server = 'kafka:9092'
)
FORMAT PLAIN ENCODE JSON;

Sync suppression lists directly to your campaign management database:

CREATE SINK purchaser_suppression_sink
FROM recent_purchasers
WITH (
    connector = 'jdbc',
    jdbc.url = 'jdbc:postgresql://campaign-db:5432/targeting',
    table.name = 'suppression_list',
    type = 'upsert',
    primary_key = 'user_id'
);

FAQ

Q: How do I handle anonymous users who later log in? Store your identity resolution mapping in a RisingWave table (loaded via CDC from your identity graph). Use a regular SQL join in your segment materialized views to map anonymous session IDs to logged-in user IDs. When the mapping table updates, downstream views recompute accordingly.

Q: Can I create hundreds of segments without performance degradation? Yes, within limits. Each materialized view adds incremental computation overhead, but views that share source streams benefit from shared processing. Hundreds of segments built from the same user_events source are handled efficiently. For thousands of segments, consider parameterizing segments using a dimension table join rather than creating individual views.

Q: How does this compare to a Customer Data Platform (CDP)? A CDP provides segment creation UIs, identity resolution, and integrations but typically relies on batch processing internally. RisingWave provides the real-time computation engine that a CDP's backend can be built on top of. You get CDP-level segment logic with sub-second freshness.

Q: What happens to segment membership when the lookback window expires? In a tumbling window materialized view, users who haven't generated qualifying events in the current window period will no longer appear in the view results. This is the correct behavior—if a user's high-intent signals are more than 24 hours old, they shouldn't be in the high-intent segment anymore.

Get Started

Build real-time audience segments that update the moment behavior changes.

• Documentation: docs.risingwave.com/get-started
• Community: risingwave.com/slack