Customer Journey Analytics with Streaming SQL

Customer journey analytics with streaming SQL tracks every touchpoint—ad impression, email open, site visit, product view, purchase—as a continuous event stream, building a live view of the customer path without waiting for overnight batch processing. RisingWave's materialized views assemble the full journey in real time, letting marketing teams see path-to-purchase patterns as they develop.

What Makes Customer Journey Analysis Hard

The customer journey spans multiple channels, devices, and time horizons. A user might see a display ad on mobile, research on desktop, click an email link three days later, and convert through organic search. Stitching together this journey requires:

1. Multi-source event ingestion — ad servers, email platforms, web analytics, CRM
2. Identity resolution — connecting events across devices and sessions to one person
3. Sequence analysis — understanding the order of touchpoints, not just their occurrence
4. Temporal aggregation — summarizing journey patterns across thousands of users

Batch systems handle this poorly because journey patterns that are visible over a 24-hour window are invisible during the day. A user completing their journey right now won't appear in your funnel analytics until tonight's batch runs.

Streaming Journey Architecture in RisingWave

All journey events flow into RisingWave from their respective sources: Kafka for real-time behavioral events, Postgres CDC for CRM updates, and MySQL CDC for email platform data. Materialized views then assemble path sequences, compute journey durations, and identify common conversion paths.

Connecting Journey Event Sources

CREATE SOURCE web_events (
    event_id        VARCHAR,
    user_id         VARCHAR,
    session_id      VARCHAR,
    event_type      VARCHAR,
    page_category   VARCHAR,
    referrer_source VARCHAR,
    referrer_medium VARCHAR,
    device_type     VARCHAR,
    event_time      TIMESTAMPTZ
)
WITH (
    connector = 'kafka',
    topic = 'web-analytics-events',
    properties.bootstrap.server = 'kafka:9092',
    scan.startup.mode = 'latest'
)
FORMAT PLAIN ENCODE JSON;

CREATE SOURCE email_events (
    event_id        VARCHAR,
    user_id         VARCHAR,
    campaign_id     VARCHAR,
    email_type      VARCHAR,
    event_type      VARCHAR,
    event_time      TIMESTAMPTZ
)
WITH (
    connector = 'kafka',
    topic = 'email-engagement-events',
    properties.bootstrap.server = 'kafka:9092',
    scan.startup.mode = 'latest'
)
FORMAT PLAIN ENCODE JSON;

Store channel and campaign lookup data in reference tables:

CREATE TABLE channel_mapping (
    referrer_source VARCHAR,
    referrer_medium VARCHAR,
    channel_group   VARCHAR,
    is_paid         BOOLEAN
);

Computing Journey Stage Transitions

Track users moving through the marketing funnel stages within rolling windows:

CREATE MATERIALIZED VIEW journey_stage_activity AS
SELECT
    user_id,
    window_start,
    window_end,
    COUNT(*) FILTER (WHERE event_type = 'page_view')            AS page_views,
    COUNT(*) FILTER (WHERE event_type = 'product_view')         AS product_views,
    COUNT(*) FILTER (WHERE event_type = 'add_to_cart')          AS cart_events,
    COUNT(*) FILTER (WHERE event_type = 'checkout_start')       AS checkout_starts,
    COUNT(*) FILTER (WHERE event_type = 'purchase')             AS purchases,
    MIN(event_time)                                              AS first_touch_in_window,
    MAX(event_time)                                             AS last_touch_in_window,
    COUNT(DISTINCT referrer_source)                              AS channel_count
FROM TUMBLE(web_events, event_time, INTERVAL '7 DAYS')
GROUP BY user_id, window_start, window_end;

Compute the time between first touch and conversion for journey duration analysis:

CREATE MATERIALIZED VIEW journey_duration_analysis AS
SELECT
    user_id,
    window_start,
    window_end,
    first_touch_in_window,
    last_touch_in_window,
    EXTRACT(EPOCH FROM (last_touch_in_window - first_touch_in_window)) / 3600
                                                AS journey_hours,
    page_views + product_views + cart_events + checkout_starts + purchases
                                                AS total_touchpoints,
    channel_count,
    CASE
        WHEN purchases > 0 THEN 'converted'
        WHEN checkout_starts > 0 THEN 'checkout_abandoned'
        WHEN cart_events > 0 THEN 'cart_abandoned'
        WHEN product_views > 0 THEN 'browsing'
        ELSE 'awareness'
    END                                         AS journey_stage
FROM journey_stage_activity;

Channel Path Analysis

Identify the most common channel sequences leading to conversion using session windows:

CREATE MATERIALIZED VIEW channel_touch_sessions AS
SELECT
    w.user_id,
    cm.channel_group,
    window_start,
    window_end,
    COUNT(*) FILTER (WHERE w.event_type = 'page_view')  AS page_views,
    MAX(w.event_time)                                    AS last_event
FROM SESSION(web_events, event_time, '30 MINUTES', user_id) w
LEFT JOIN channel_mapping cm
    ON w.referrer_source = cm.referrer_source
    AND w.referrer_medium = cm.referrer_medium
GROUP BY w.user_id, cm.channel_group, window_start, window_end;

Comparison: Journey Analytics Approaches

Exporting Journey Insights

Feed journey stage data into your marketing automation platform:

CREATE SINK journey_stage_to_crm
FROM journey_duration_analysis
WITH (
    connector = 'jdbc',
    jdbc.url = 'jdbc:postgresql://marketing-crm:5432/contacts',
    table.name = 'user_journey_stage',
    type = 'upsert',
    primary_key = 'user_id,window_start'
);

FAQ

Q: How do I track journeys that span more than 7 days? Increase the tumbling window interval in your materialized view, or use a combination of a rolling window view (for recent journeys) and a historical table (CDC-backed from your warehouse) joined together. For very long journeys (30+ days), the CDC-backed reference table pattern is more memory-efficient than a single large window.

Q: How does RisingWave handle multi-device journeys? Device-to-user mapping lives in a RisingWave table that receives updates via CDC from your identity resolution system. Join this table to your event streams on session_id to translate device-level events into user-level journey records.

Q: Can I use this for B2B account-level journey analysis? Yes. Replace user_id with account_id in your event schema and group journey stages at the account level. B2B journeys often have multiple stakeholders, so you may want a COUNT(DISTINCT user_id) dimension to track buying committee engagement.

Q: How do I detect journey abandonment in real time? Use a session window materialized view. When a session window closes without a purchase event, the user is classified as abandoned. Your marketing automation system can query the abandonment view and trigger retargeting or email flows accordingly.

Q: What's the difference between session windows and tumbling windows for journey analysis? Session windows group events by natural pause in activity (inactivity gap), making them ideal for per-visit journey analysis. Tumbling windows group by fixed calendar periods (daily, weekly), making them better for cross-visit journey aggregation and funnel reporting.

Get Started

Build real-time customer journey analytics that reveal intent as it happens.

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