Marketing Funnel Analytics with Streaming SQL

Marketing funnel analytics with streaming SQL means your awareness-to-conversion metrics update continuously—every impression, lead, and purchase is reflected within seconds. RisingWave's materialized views maintain live funnel stage counts, conversion rates, and drop-off points without batch jobs, giving marketing teams always-current funnel visibility.

The Problem with Batch Funnel Reporting

Marketing funnels are the primary lens through which teams manage campaign health. When a paid campaign launches, marketing managers need to know immediately if the top of the funnel (impressions, clicks) is converting through to the middle (leads, trials) and bottom (purchases, activations) at expected rates.

With batch reporting, this feedback loop takes hours. An A/B test on a landing page that's crushing one variant takes until tonight's batch run to show statistical significance. A new campaign that's driving enormous top-of-funnel volume but zero bottom-of-funnel conversions (indicating a broken checkout or mismatched audience) goes undetected for an entire business day.

Streaming funnel analytics gives you the conversion rate signal in minutes instead of hours.

Funnel Data Architecture in RisingWave

The funnel analytics pipeline ingests events from every stage of the funnel—ad impressions from your DSP, web events from your analytics platform, CRM events from your sales system. Materialized views aggregate these into stage-level counts and compute conversion rates across stages in real time.

Setting Up Multi-Stage Funnel Ingestion

CREATE SOURCE funnel_events (
    event_id        VARCHAR,
    user_id         VARCHAR,
    session_id      VARCHAR,
    campaign_id     VARCHAR,
    channel         VARCHAR,
    stage           VARCHAR,
    event_type      VARCHAR,
    revenue         DECIMAL,
    event_time      TIMESTAMPTZ
)
WITH (
    connector = 'kafka',
    topic = 'funnel-events',
    properties.bootstrap.server = 'kafka:9092',
    scan.startup.mode = 'latest'
)
FORMAT PLAIN ENCODE JSON;

CREATE TABLE campaign_targets (
    campaign_id         VARCHAR PRIMARY KEY,
    target_impressions  BIGINT,
    target_clicks       BIGINT,
    target_leads        BIGINT,
    target_purchases    BIGINT,
    target_revenue      DECIMAL
);

Real-Time Funnel Stage Counts

Compute stage-by-stage event counts in hourly tumbling windows:

CREATE MATERIALIZED VIEW funnel_stage_metrics AS
SELECT
    campaign_id,
    channel,
    window_start,
    window_end,
    COUNT(*) FILTER (WHERE stage = 'impression')            AS impressions,
    COUNT(*) FILTER (WHERE stage = 'click')                 AS clicks,
    COUNT(*) FILTER (WHERE stage = 'landing_page_view')     AS lp_views,
    COUNT(*) FILTER (WHERE stage = 'lead')                  AS leads,
    COUNT(*) FILTER (WHERE stage = 'trial_signup')          AS trial_signups,
    COUNT(*) FILTER (WHERE stage = 'purchase')              AS purchases,
    SUM(revenue) FILTER (WHERE stage = 'purchase')          AS revenue,
    COUNT(DISTINCT user_id)                                  AS unique_users
FROM TUMBLE(funnel_events, event_time, INTERVAL '1 HOUR')
GROUP BY campaign_id, channel, window_start, window_end;

Conversion Rate Calculation

Build a conversion rate view that computes stage-to-stage rates in real time:

CREATE MATERIALIZED VIEW funnel_conversion_rates AS
SELECT
    f.campaign_id,
    f.channel,
    f.window_start,
    f.window_end,
    f.impressions,
    f.clicks,
    f.leads,
    f.purchases,
    f.revenue,
    -- Click-through rate
    ROUND(f.clicks::DECIMAL / NULLIF(f.impressions, 0) * 100, 2)    AS ctr_pct,
    -- Click to lead conversion rate
    ROUND(f.leads::DECIMAL / NULLIF(f.clicks, 0) * 100, 2)          AS click_to_lead_pct,
    -- Lead to purchase conversion rate
    ROUND(f.purchases::DECIMAL / NULLIF(f.leads, 0) * 100, 2)       AS lead_to_purchase_pct,
    -- Overall impression to purchase conversion rate
    ROUND(f.purchases::DECIMAL / NULLIF(f.impressions, 0) * 100, 4) AS overall_cvr_pct,
    -- Revenue per click
    ROUND(f.revenue / NULLIF(f.clicks, 0), 2)                        AS rpc,
    -- Revenue per impression
    ROUND(f.revenue / NULLIF(f.impressions, 0) * 1000, 2)            AS rpm,
    -- Pacing against targets
    ROUND(f.purchases::DECIMAL / NULLIF(t.target_purchases, 0) * 100, 1)
                                                                      AS purchase_target_pct
FROM funnel_stage_metrics f
LEFT JOIN campaign_targets t ON f.campaign_id = t.campaign_id;

Drop-Off Analysis

Identify where users drop out of the funnel to prioritize optimization:

CREATE MATERIALIZED VIEW funnel_dropoff_analysis AS
SELECT
    campaign_id,
    window_start,
    window_end,
    impressions - clicks                                            AS impression_to_click_dropoff,
    clicks - leads                                                  AS click_to_lead_dropoff,
    leads - purchases                                               AS lead_to_purchase_dropoff,
    ROUND((impressions - clicks)::DECIMAL / NULLIF(impressions, 0) * 100, 1)
                                                                    AS impression_dropoff_pct,
    ROUND((clicks - leads)::DECIMAL / NULLIF(clicks, 0) * 100, 1)
                                                                    AS click_dropoff_pct,
    ROUND((leads - purchases)::DECIMAL / NULLIF(leads, 0) * 100, 1)
                                                                    AS lead_dropoff_pct
FROM funnel_conversion_rates;

Comparison: Funnel Analytics Approaches

Pushing Funnel Data to Dashboards

Sink live funnel metrics to Kafka for your real-time dashboard:

CREATE SINK funnel_metrics_dashboard_sink
FROM funnel_conversion_rates
WITH (
    connector = 'kafka',
    topic = 'funnel-metrics-live',
    properties.bootstrap.server = 'kafka:9092'
)
FORMAT PLAIN ENCODE JSON;

Archive funnel data to Iceberg for historical analysis and quarter-over-quarter comparisons:

CREATE SINK funnel_metrics_archive
FROM funnel_conversion_rates
WITH (
    connector = 'iceberg',
    type = 'append-only',
    warehouse.path = 's3://your-bucket/marketing-analytics',
    s3.region = 'us-east-1',
    database.name = 'marketing',
    table.name = 'funnel_conversion_rates'
);

FAQ

Q: How does RisingWave handle funnel events from different sources with different schemas? Create separate source connections for each event provider (Kafka topic per platform, or CDC per database). Create a unified intermediate materialized view that normalizes events from all sources into the common funnel schema using UNION ALL logic. Downstream funnel views then query the normalized view.

Q: Can I compare funnel performance across campaigns in real time? Yes. The funnel_conversion_rates view groups by campaign_id, so a single query comparing multiple campaigns returns current data for all campaigns simultaneously. Add a WHERE clause or a GROUP BY ROLLUP to aggregate across dimensions.

Q: How do I set up alerts when conversion rates drop below threshold? Create a materialized view that filters funnel_conversion_rates to only show rows where a rate falls below your threshold. Connect this view to an alerting sink (Kafka → PagerDuty, or JDBC → your alerting database). When the view produces rows, your alert fires.

Q: How accurate is the real-time funnel compared to the final daily count? The real-time funnel reflects events processed so far in the current window. For the current hour, it will show partial counts that grow as events arrive. Completed past windows are stable and accurate. For campaigns where you need certainty, query completed windows (window_end < NOW()).

Q: Can I run multivariate tests and see results in real time? Yes. Add your experiment variant ID to the event schema and GROUP BY variant_id in the funnel materialized views. You'll see conversion rates per variant update in real time as test traffic arrives.

Get Started

Build a real-time marketing funnel that shows you what's happening right now.

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