Real-Time Payment Analytics: Breakdowns by Method and Channel

Payment method analytics tell you which payment types your customers prefer, where conversion rates vary by checkout method, and when a payment gateway is degrading. With RisingWave — a PostgreSQL-compatible streaming database — you can maintain continuously updated breakdowns by payment method, channel, and merchant without running batch aggregation jobs.

The Problem: Payment Insights Arrive Too Late

Modern e-commerce platforms support a complex mix of payment methods: card, digital wallets (Alipay, PayPal, GrabPay), BNPL (buy now pay later), cash on delivery (COD), and installment plans. Each method carries different economics — different fees, different failure rates, different chargeback patterns.

Operations teams need payment analytics in real time for several reasons:

Gateway failure detection: A payment gateway going down or degrading produces a sudden drop in successful transactions for that method. Batch analytics detect this in the next scheduled run — 15 to 60 minutes later. A real-time view detects it within seconds.

BNPL limit exposure: BNPL providers impose volume limits per merchant per day. If you track BNPL volume only through hourly batch jobs, you may exceed your limit and start declining BNPL orders before you know there is a problem.

COD logistics planning: Cash on delivery orders require physical dispatch and cash handling at delivery. Operations teams need up-to-date COD order counts to plan same-day dispatch before the logistics window closes.

Campaign optimization: During a flash sale, real-time data on which payment methods customers are using lets marketing teams adjust incentive offers (e.g., add a discount for digital wallets if card adoption is higher than expected).

How Streaming SQL Solves This

RisingWave connects to the order event stream and maintains payment analytics views incrementally. Every time an order is placed or updated, the payment breakdown views update automatically — no batch job, no scheduled refresh, no stale data.

Because RisingWave is PostgreSQL-compatible, the payment analytics API queries it exactly like a Postgres database. Pre-computed views mean even complex breakdowns (by method, channel, merchant, and time window) return in milliseconds.

Building Real-Time Payment Analytics

Step 1: Data Source Setup

The orders source carries payment_method in every event. Payment status updates (success, failure, refund) flow through the same topic as status changes:

CREATE SOURCE orders (
    order_id        VARCHAR,
    merchant_id     VARCHAR,
    customer_id     VARCHAR,
    channel         VARCHAR,        -- 'web', 'mobile', 'pos', 'marketplace'
    payment_method  VARCHAR,        -- 'card', 'alipay', 'paypal', 'cod', 'bnpl', 'installment'
    subtotal        NUMERIC,
    discount        NUMERIC,
    total           NUMERIC,
    status          VARCHAR,
    created_at      TIMESTAMPTZ
) WITH (
    connector = 'kafka',
    topic = 'ecommerce.orders',
    properties.bootstrap.server = 'kafka:9092',
    scan.startup.mode = 'earliest'
) FORMAT PLAIN ENCODE JSON;

-- Payment gateway events for failure tracking
CREATE SOURCE payment_events (
    payment_id      VARCHAR,
    order_id        VARCHAR,
    merchant_id     VARCHAR,
    payment_method  VARCHAR,
    gateway         VARCHAR,  -- 'stripe', 'adyen', 'alipay_gateway', etc.
    amount          NUMERIC,
    currency        VARCHAR,
    outcome         VARCHAR,  -- 'success', 'failure', 'pending', 'refunded'
    failure_code    VARCHAR,
    occurred_at     TIMESTAMPTZ
) WITH (
    connector = 'kafka',
    topic = 'ecommerce.payments',
    properties.bootstrap.server = 'kafka:9092',
    scan.startup.mode = 'earliest'
) FORMAT PLAIN ENCODE JSON;

Step 2: Core Materialized View

The payment breakdown view computes transaction volume, success rates, and average transaction value by payment method and channel, in hourly windows:

CREATE MATERIALIZED VIEW mv_payment_hourly AS
SELECT
    merchant_id,
    payment_method,
    channel,
    WINDOW_START                                AS hour_start,
    COUNT(*)                                    AS transaction_count,
    SUM(CASE WHEN outcome = 'success'
             THEN amount ELSE 0 END)           AS successful_volume,
    SUM(CASE WHEN outcome = 'failure'
             THEN 1 ELSE 0 END)               AS failure_count,
    SUM(CASE WHEN outcome = 'refunded'
             THEN 1 ELSE 0 END)               AS refund_count,
    ROUND(
        SUM(CASE WHEN outcome = 'success'
                 THEN 1 ELSE 0 END)::NUMERIC /
        NULLIF(COUNT(*), 0) * 100,
        2
    )                                           AS success_rate_pct,
    ROUND(AVG(CASE WHEN outcome = 'success'
                   THEN amount END), 2)        AS avg_transaction_value
FROM TUMBLE(payment_events, occurred_at, INTERVAL '1 HOUR')
GROUP BY merchant_id, payment_method, channel, WINDOW_START;

Step 3: Aggregations — Gateway Health and Payment Mix

Track gateway health in 5-minute windows to catch failures quickly:

CREATE MATERIALIZED VIEW mv_gateway_health_5min AS
SELECT
    gateway,
    payment_method,
    WINDOW_START                                AS window_start,
    COUNT(*)                                    AS attempts,
    SUM(CASE WHEN outcome = 'success'
             THEN 1 ELSE 0 END)               AS successes,
    ROUND(
        SUM(CASE WHEN outcome = 'success'
                 THEN 1 ELSE 0 END)::NUMERIC /
        NULLIF(COUNT(*), 0) * 100,
        2
    )                                           AS success_rate_pct,
    COUNT(DISTINCT failure_code)               AS distinct_failure_codes
FROM TUMBLE(payment_events, occurred_at, INTERVAL '5 MINUTES')
GROUP BY gateway, payment_method, WINDOW_START;

Build a payment method mix view that shows each method's share of total transaction volume per merchant:

CREATE MATERIALIZED VIEW mv_payment_mix AS
SELECT
    merchant_id,
    payment_method,
    hour_start,
    successful_volume,
    SUM(successful_volume) OVER (
        PARTITION BY merchant_id, hour_start
    )                                           AS total_volume,
    ROUND(
        successful_volume / NULLIF(
            SUM(successful_volume) OVER (
                PARTITION BY merchant_id, hour_start
            ),
            0
        ) * 100,
        2
    )                                           AS method_share_pct
FROM mv_payment_hourly;

Track BNPL daily exposure against limits:

CREATE MATERIALIZED VIEW mv_bnpl_daily_exposure AS
SELECT
    merchant_id,
    DATE_TRUNC('day', hour_start)               AS day,
    SUM(successful_volume)                      AS bnpl_volume,
    COUNT(DISTINCT merchant_id)                 AS transaction_count
FROM mv_payment_hourly
WHERE payment_method = 'bnpl'
GROUP BY merchant_id, DATE_TRUNC('day', hour_start);

Step 4: Downstream / Serving

Publish payment analytics to the application database and a gateway monitoring topic:

CREATE SINK sink_payment_analytics
FROM mv_payment_hourly
WITH (
    connector = 'jdbc',
    jdbc.url = 'jdbc:postgresql://app-db:5432/analytics',
    table.name = 'payment_hourly_breakdown',
    type = 'upsert',
    primary_key = 'merchant_id,payment_method,channel,hour_start'
);

-- Gateway health alerts to Kafka
CREATE SINK sink_gateway_alerts
FROM mv_gateway_health_5min
WITH (
    connector = 'kafka',
    properties.bootstrap.server = 'kafka:9092',
    topic = 'alerts.gateway_health'
) FORMAT PLAIN ENCODE JSON;

Payment Analytics: Batch vs. Streaming

FAQ

Q: How do I track payment failures by specific failure code (e.g., insufficient funds vs. card declined)? Add failure_code to your GROUP BY in the gateway health view. You can create a separate materialized view specifically for failure code analysis, joining to a reference table that maps codes to human-readable categories.

Q: Can I track partial payments and installment schedules in real time? Yes. Model each installment payment as a separate event with a payment_id linking back to the original order. A view that groups by payment_id and sums amount gives you running totals for each installment order.

Q: How do I correlate payment method choice with conversion rate? Join the payment events stream to a cart/session events stream using a temporal join on customer_id and order_id. The conversion rate by payment method is the ratio of payment_events.outcome = 'success' to session starts per method.

Key Takeaways

• Real-time payment analytics enable gateway failure detection, BNPL limit management, COD dispatch planning, and campaign optimization — none of which are possible with batch pipelines.
• RisingWave's 5-minute tumbling windows detect gateway failures within minutes rather than the next batch run.
• Payment mix views using SUM() OVER (PARTITION BY merchant_id, hour_start) compute each method's share without aggregation at query time.
• The gateway health sink to Kafka enables downstream alerting systems to react to degraded payment success rates automatically.
• RisingWave's PostgreSQL compatibility means payment analytics APIs use standard SQL — no custom streaming client needed.

Get started | Slack