Real-Time Search Analytics for E-Commerce Platforms

Real-time search analytics means knowing within seconds which search queries are returning zero results, which searches lead to purchases, and where customers are abandoning the search funnel — giving your merchandising team live data to act on rather than yesterday's reports.

Why This Matters for E-Commerce

Search is the highest-intent touchpoint in e-commerce. A customer who types a query into your search bar is telling you exactly what they want to buy. Your search engine's job is to not fail them.

But search failure is common and expensive. Studies show that 12-30% of e-commerce searches return zero results, and customers who hit a zero-result page have a 40% higher exit rate than those who find results. Without real-time search analytics, these failures are invisible until the next morning's batch report.

Real-time search analytics transforms the merchandising workflow:

• A product suddenly out of stock triggers a spike in zero-result searches for that product name
• A trending query shows up in real-time analytics before it peaks, giving merchandising teams time to create a landing page or promote related products
• A high search-to-cart but low cart-to-purchase rate signals a pricing or product description issue
• Seasonal query patterns (e.g., "valentine's gift" starting to appear) trigger automated catalog promotions

The search funnel has four stages: query entered → results shown → result clicked → purchase completed. Each stage is a measurable event. Streaming SQL lets you compute funnel metrics across all four stages continuously.

How Streaming SQL Solves This

RisingWave, a PostgreSQL-compatible streaming database, ingests search event streams from Kafka. Materialized views compute zero-result rates, click-through rates (CTR), and search-to-purchase conversion rates in real time.

The key is joining across event types within a session: a search event, a click event (if the user clicked a result), an add-to-cart event, and a purchase event — all linked by session_id and search_id. RisingWave's streaming joins maintain this linkage continuously as events arrive.

Step-by-Step Tutorial

Step 1: Data Source

Create a source for search events. Each record represents one event in the search funnel.

CREATE SOURCE search_events (
    event_id      VARCHAR,
    search_id     VARCHAR,
    session_id    VARCHAR,
    customer_id   VARCHAR,
    query_string  VARCHAR,
    event_type    VARCHAR,   -- 'search', 'result_click', 'add_to_cart', 'purchase', 'zero_result', 'exit'
    product_id    VARCHAR,
    result_count  INT,
    result_rank   INT,        -- position of clicked result (null if not a click event)
    event_ts      TIMESTAMPTZ
)
WITH (
    connector = 'kafka',
    topic = 'search_events',
    properties.bootstrap.server = 'kafka:9092',
    scan.startup.mode = 'earliest'
) FORMAT PLAIN ENCODE JSON;

Step 2: Core Materialized View — Query Performance Metrics

Compute per-query search volume, zero-result rate, click-through rate, and search-to-purchase conversion.

CREATE MATERIALIZED VIEW search_query_metrics AS
SELECT
    query_string,
    COUNT(DISTINCT search_id) FILTER (WHERE event_type = 'search')         AS search_count,
    COUNT(DISTINCT search_id) FILTER (WHERE event_type = 'zero_result')    AS zero_result_count,
    COUNT(DISTINCT search_id) FILTER (WHERE event_type = 'result_click')   AS click_count,
    COUNT(DISTINCT search_id) FILTER (WHERE event_type = 'add_to_cart')    AS cart_add_count,
    COUNT(DISTINCT search_id) FILTER (WHERE event_type = 'purchase')       AS purchase_count,
    -- Zero result rate
    COUNT(DISTINCT search_id) FILTER (WHERE event_type = 'zero_result')::NUMERIC
        / NULLIF(COUNT(DISTINCT search_id) FILTER (WHERE event_type = 'search'), 0)
                                                                            AS zero_result_rate,
    -- Click-through rate
    COUNT(DISTINCT search_id) FILTER (WHERE event_type = 'result_click')::NUMERIC
        / NULLIF(COUNT(DISTINCT search_id) FILTER (WHERE event_type = 'search'), 0)
                                                                            AS click_through_rate,
    -- Search to purchase rate
    COUNT(DISTINCT search_id) FILTER (WHERE event_type = 'purchase')::NUMERIC
        / NULLIF(COUNT(DISTINCT search_id) FILTER (WHERE event_type = 'search'), 0)
                                                                            AS search_to_purchase_rate,
    MAX(event_ts)                                                           AS last_seen_ts
FROM search_events
GROUP BY query_string;

Step 3: Trending Queries and Zero-Result Alerts

Track trending search terms using a 1-hour window and surface zero-result queries requiring immediate merchandising action.

-- Trending queries in the last hour
CREATE MATERIALIZED VIEW trending_queries_1h AS
SELECT
    window_start,
    window_end,
    query_string,
    COUNT(DISTINCT search_id) FILTER (WHERE event_type = 'search')     AS searches_in_window,
    COUNT(DISTINCT search_id) FILTER (WHERE event_type = 'zero_result') AS zero_results_in_window,
    COUNT(DISTINCT search_id) FILTER (WHERE event_type = 'purchase')   AS purchases_in_window
FROM TUMBLE(search_events, event_ts, INTERVAL '1 HOUR')
GROUP BY window_start, window_end, query_string;

-- High-volume zero-result queries (immediate merchandising action needed)
CREATE MATERIALIZED VIEW zero_result_alerts AS
SELECT
    query_string,
    COUNT(DISTINCT search_id) AS zero_result_searches,
    MAX(event_ts)             AS last_zero_result_ts
FROM search_events
WHERE event_type = 'zero_result'
  AND event_ts >= NOW() - INTERVAL '1 HOUR'
GROUP BY query_string
HAVING COUNT(DISTINCT search_id) >= 10;  -- threshold: 10 zero-result searches per hour

Step 4: Serving Layer — Sink to Merchandising Dashboard

Push search analytics to your merchandising and UX team's dashboard database.

CREATE SINK search_query_metrics_sink
FROM search_query_metrics
WITH (
    connector   = 'jdbc',
    jdbc.url    = 'jdbc:postgresql://analyticsdb:5432/search?user=rw&password=secret',
    table.name  = 'search_query_metrics_live',
    type        = 'upsert',
    primary_key = 'query_string'
);

CREATE SINK zero_result_alerts_sink
FROM zero_result_alerts
WITH (
    connector   = 'jdbc',
    jdbc.url    = 'jdbc:postgresql://analyticsdb:5432/search?user=rw&password=secret',
    table.name  = 'zero_result_alerts_live',
    type        = 'upsert',
    primary_key = 'query_string'
);

Comparison Table

FAQ

Q: How do I link search events to purchase events across a long session? Use session_id as the join key. A session can span multiple searches and eventually a purchase. The search_id links a specific search to subsequent click and purchase events within the same session. If sessions can span days (e.g., saved carts), use customer_id with a time-bounded window instead.

Q: Can I track result rank position to measure the quality of search ranking? Yes. The result_rank field records the position of clicked results. Add AVG(result_rank) FILTER (WHERE event_type = 'result_click') to the search_query_metrics view to compute the average click rank per query. Low average rank (close to 1) indicates good result quality; high rank indicates poor ranking.

Q: How do I integrate these signals back into my search engine? Sink the search_query_metrics view to a PostgreSQL or Elasticsearch database that your search engine's re-ranking layer reads. High search-to-purchase rate for a query suggests those products should rank higher. Zero-result queries trigger merchandising workflows to create synonym rules or promote related products.

Key Takeaways

• Real-time search analytics enables merchandising response in minutes to zero-result spikes, trending queries, and search funnel drop-offs
• The search-to-purchase funnel (search → click → cart → purchase) is computable from a single event stream with event type filtering
• Hourly tumbling windows on trending queries surface emerging demand patterns before they peak
• Zero-result alert views with HAVING thresholds provide an actionable signal without requiring a separate alerting pipeline
• The same streaming infrastructure serving SHOPLINE's merchant dashboards applies directly to search analytics — unified event ingestion, SQL-driven aggregation, PostgreSQL-compatible serving

Get started | Slack