Building a Real-Time Seller Performance Dashboard

A real-time seller performance dashboard shows marketplace sellers their current GMV, order acceptance rate, fulfillment SLA compliance, customer ratings, and pending payout amounts — updated within seconds of each order event, not in end-of-day batch reports.

Why This Matters for E-Commerce

Marketplace platforms live or die by seller quality. High-performing sellers drive revenue. Underperforming sellers — those with high cancellation rates, slow fulfillment, or poor customer ratings — damage the customer experience for everyone on the platform.

Sellers need visibility to perform. When a seller can only see yesterday's metrics, they can't respond to problems in real time. An order acceptance rate dropping because a product is out of stock needs to be visible now, not tomorrow. A fulfillment SLA breach accumulating toward a penalty threshold should be visible before the threshold is crossed.

Platform operators need the same visibility for enforcement. When a seller's fulfillment SLA falls below the platform's minimum, that should trigger an automated warning or escalation — not a weekly review meeting.

Real-time seller dashboards serve both constituencies:

• Sellers see live GMV, order volume, acceptance rates, and ratings
• Operators see seller health scores, SLA compliance, and flagged underperformers
• Finance teams see real-time payout calculations (GMV minus commission and fees)

SHOPLINE delivers exactly this for their merchants: real-time GMV, order volume, channel breakdowns, and payment analytics through a streaming SQL pipeline — with a 76.7% reduction in API response times compared to their previous batch architecture.

How Streaming SQL Solves This

RisingWave, a PostgreSQL-compatible streaming database, ingests order, fulfillment, and review events from Kafka. Materialized views compute per-seller metrics continuously. The PostgreSQL interface means your seller dashboard API reads from a standard relational database — no specialized query layer needed.

Payout calculations are a particularly good streaming use case: as orders complete and returns/refunds are processed, the net payout amount for each seller updates in real time. Finance teams can see the current payout liability without waiting for a batch reconciliation.

Step-by-Step Tutorial

Step 1: Data Source

Create sources for orders, fulfillment events, and seller reviews.

-- Marketplace orders
CREATE SOURCE marketplace_orders (
    order_id          VARCHAR,
    seller_id         VARCHAR,
    customer_id       VARCHAR,
    order_value       NUMERIC,
    commission_rate   NUMERIC,   -- platform commission as decimal (e.g., 0.15 = 15%)
    order_accepted    BOOLEAN,
    cancellation_reason VARCHAR,
    order_ts          TIMESTAMPTZ,
    status            VARCHAR
)
WITH (
    connector = 'kafka',
    topic = 'marketplace_orders',
    properties.bootstrap.server = 'kafka:9092',
    scan.startup.mode = 'earliest'
) FORMAT PLAIN ENCODE JSON;

-- Fulfillment events (reuse fulfillment_events source or create new)
CREATE SOURCE seller_fulfillment (
    order_id    VARCHAR,
    seller_id   VARCHAR,
    stage       VARCHAR,   -- 'accepted', 'packed', 'shipped', 'delivered', 'failed'
    sla_hours   INT,       -- contracted SLA in hours
    event_ts    TIMESTAMPTZ
)
WITH (
    connector = 'kafka',
    topic = 'seller_fulfillment',
    properties.bootstrap.server = 'kafka:9092',
    scan.startup.mode = 'earliest'
) FORMAT PLAIN ENCODE JSON;

-- Customer ratings
CREATE SOURCE seller_ratings (
    rating_id   VARCHAR,
    order_id    VARCHAR,
    seller_id   VARCHAR,
    rating      INT,       -- 1-5
    rated_ts    TIMESTAMPTZ
)
WITH (
    connector = 'kafka',
    topic = 'seller_ratings',
    properties.bootstrap.server = 'kafka:9092',
    scan.startup.mode = 'earliest'
) FORMAT PLAIN ENCODE JSON;

Step 2: Core Materialized View — Seller GMV and Order Performance

The primary seller dashboard metrics: GMV, order acceptance rate, cancellation rate, and period-over-period breakdown.

CREATE MATERIALIZED VIEW seller_performance AS
SELECT
    seller_id,
    -- Volume metrics
    COUNT(order_id)                                           AS total_orders,
    COUNT(order_id) FILTER (WHERE status = 'completed')       AS completed_orders,
    COUNT(order_id) FILTER (WHERE status = 'cancelled')       AS cancelled_orders,
    COUNT(order_id) FILTER (WHERE order_accepted = TRUE)      AS accepted_orders,
    -- GMV
    SUM(order_value) FILTER (WHERE status = 'completed')      AS total_gmv,
    AVG(order_value) FILTER (WHERE status = 'completed')      AS avg_order_value,
    -- Rates
    COUNT(order_id) FILTER (WHERE order_accepted = TRUE)::NUMERIC
        / NULLIF(COUNT(order_id), 0)                          AS acceptance_rate,
    COUNT(order_id) FILTER (WHERE status = 'cancelled')::NUMERIC
        / NULLIF(COUNT(order_id), 0)                          AS cancellation_rate,
    -- Payout calculation: GMV × (1 - commission_rate)
    SUM(order_value * (1 - commission_rate))
        FILTER (WHERE status = 'completed')                   AS gross_payout,
    -- Today
    SUM(order_value) FILTER (
        WHERE status = 'completed' AND order_ts >= CURRENT_DATE
    )                                                         AS gmv_today,
    COUNT(order_id) FILTER (WHERE order_ts >= CURRENT_DATE)   AS orders_today,
    MAX(order_ts)                                             AS last_order_ts
FROM marketplace_orders
GROUP BY seller_id;

Step 3: Fulfillment SLA and Ratings

Compute SLA compliance rates and average seller ratings, both updated continuously.

-- Seller fulfillment SLA compliance
CREATE MATERIALIZED VIEW seller_sla_metrics AS
SELECT
    sf_ship.seller_id,
    COUNT(DISTINCT sf_ship.order_id) AS shipped_orders,
    COUNT(DISTINCT sf_ship.order_id) FILTER (
        WHERE EXTRACT(EPOCH FROM (sf_ship.event_ts - sf_accept.event_ts)) / 3600
              <= sf_ship.sla_hours
    )                                AS on_time_shipments,
    COUNT(DISTINCT sf_ship.order_id) FILTER (
        WHERE EXTRACT(EPOCH FROM (sf_ship.event_ts - sf_accept.event_ts)) / 3600
              > sf_ship.sla_hours
    )                                AS late_shipments,
    COUNT(DISTINCT sf_ship.order_id) FILTER (
        WHERE EXTRACT(EPOCH FROM (sf_ship.event_ts - sf_accept.event_ts)) / 3600
              <= sf_ship.sla_hours
    )::NUMERIC
        / NULLIF(COUNT(DISTINCT sf_ship.order_id), 0)  AS sla_compliance_rate
FROM seller_fulfillment sf_ship
JOIN seller_fulfillment sf_accept
    ON  sf_ship.order_id   = sf_accept.order_id
    AND sf_ship.stage      = 'shipped'
    AND sf_accept.stage    = 'accepted'
GROUP BY sf_ship.seller_id;

-- Average seller rating
CREATE MATERIALIZED VIEW seller_rating_metrics AS
SELECT
    seller_id,
    COUNT(rating_id)   AS total_ratings,
    AVG(rating)        AS avg_rating,
    COUNT(rating_id) FILTER (WHERE rating >= 4) AS positive_ratings,
    COUNT(rating_id) FILTER (WHERE rating <= 2) AS negative_ratings
FROM seller_ratings
GROUP BY seller_id;

Step 4: Serving Layer — Sink to Seller Dashboard API

Push all seller metrics to a PostgreSQL database. Sellers query their own metrics using seller-scoped API authentication.

CREATE SINK seller_performance_sink
FROM seller_performance
WITH (
    connector   = 'jdbc',
    jdbc.url    = 'jdbc:postgresql://dashboarddb:5432/marketplace?user=rw&password=secret',
    table.name  = 'seller_performance_live',
    type        = 'upsert',
    primary_key = 'seller_id'
);

CREATE SINK seller_sla_sink
FROM seller_sla_metrics
WITH (
    connector   = 'jdbc',
    jdbc.url    = 'jdbc:postgresql://dashboarddb:5432/marketplace?user=rw&password=secret',
    table.name  = 'seller_sla_live',
    type        = 'upsert',
    primary_key = 'seller_id'
);

Comparison Table

FAQ

Q: How do I calculate payouts after returns and refunds? Add a refund events source. When a refund is issued, subtract the refund amount from the seller's gross_payout by including refund events with negative order_value in the aggregation. The materialized view will automatically reduce the seller's payout when a refund event arrives.

Q: Can I create automated SLA violation alerts? Yes. Create a separate materialized view filtering seller_sla_metrics where sla_compliance_rate < 0.95 (or your platform's threshold). Sink that view to a Kafka topic or webhook endpoint that triggers your enforcement workflow — automated warning emails, dashboard flags, or account review escalation.

Q: How do I show sellers a breakdown of GMV by product category? Add a category field to marketplace_orders and include it in the aggregation. Create a separate seller_category_breakdown materialized view grouped by seller_id, category. This gives sellers the same channel breakdown analytics that SHOPLINE provides to their merchants — visible in real time.

Key Takeaways

• Real-time seller dashboards give marketplace sellers live GMV, acceptance rate, SLA compliance, and payout calculations — not end-of-day summaries
• Payout calculations in a streaming view update with every order completion and refund event, giving finance teams accurate real-time liability figures
• SLA compliance rates computed in a streaming view enable automated enforcement workflows without manual batch review
• RisingWave's multi-tenant pattern (group by seller_id) scales to thousands of sellers on a single pipeline — the same architecture SHOPLINE uses for merchant analytics
• Henry Chi at SHOPLINE noted: "It provides the low-latency real-time analytics we need and delivers a much smoother experience for our merchants" — the exact outcome a real-time seller dashboard delivers

Get started | Slack