Real-Time Coupon and Promotion Analytics with SQL

Real-time coupon and promotion analytics means knowing redemption rates, total discount liability, and incremental revenue impact within seconds of each order — giving you the data to optimize a promotion while it's live, not after it ends.

Why This Matters for E-Commerce

Promotions are high-stakes, time-sensitive events. A flash sale runs for four hours. A coupon campaign launches to 500,000 email subscribers. A buy-one-get-one offer goes live on your homepage. In each case, the business needs immediate feedback: Is this working? Is it costing too much? Are we overselling?

With batch analytics, you fly blind. The morning-after report shows you that the 20%-off coupon had a 68% redemption rate — but by then the promo window has closed and the margin damage is done. If the coupon had an error (wrong discount amount, no minimum order threshold), you'd find out too late.

Real-time promotion analytics changes the operational model:

• Finance teams see discount liability accumulating in real time
• Marketing teams see redemption rates by channel and segment as they happen
• Operations teams can pause or modify a promotion mid-flight based on live data
• Fraud teams can detect coupon abuse patterns immediately

The key metrics: coupon code redemption count, unique customers redeeming, total discount amount, average order value with vs. without coupon, and incremental revenue (orders that wouldn't have happened without the promo). All of these flow from order events enriched with coupon application data.

How Streaming SQL Solves This

RisingWave, a PostgreSQL-compatible streaming database, ingests order events with coupon metadata from Kafka and maintains materialized views of promotion performance in real time. The SQL layer handles all the aggregation: redemption counts, discount sums, time-windowed redemption rates, and per-channel breakdowns.

Because RisingWave updates materialized views incrementally, each new order triggers only the affected promotion's metrics to update — not a full recomputation. This makes it practical to track dozens of simultaneous promotions at high order volumes without batch processing overhead.

SHOPLINE adopted exactly this pattern for merchant analytics, tracking GMV, order volume, and payment analytics across merchant dashboards in real time — eliminating the overnight batch pipeline entirely.

Step-by-Step Tutorial

Step 1: Data Source

Create a source for order events enriched with coupon and promotion metadata.

CREATE SOURCE orders_with_promotions (
    order_id          VARCHAR,
    customer_id       VARCHAR,
    coupon_code       VARCHAR,
    promotion_id      VARCHAR,
    promotion_type    VARCHAR,   -- 'percent_off', 'fixed_amount', 'bogo', 'free_shipping'
    discount_amount   NUMERIC,
    order_subtotal    NUMERIC,
    order_total       NUMERIC,
    channel           VARCHAR,
    customer_segment  VARCHAR,
    order_ts          TIMESTAMPTZ,
    status            VARCHAR
)
WITH (
    connector = 'kafka',
    topic = 'orders',
    properties.bootstrap.server = 'kafka:9092',
    scan.startup.mode = 'earliest'
) FORMAT PLAIN ENCODE JSON;

Step 2: Core Materialized View — Promotion Performance

Compute real-time redemption counts, discount totals, and average order values per promotion.

CREATE MATERIALIZED VIEW promotion_performance AS
SELECT
    promotion_id,
    promotion_type,
    COUNT(order_id)                               AS redemption_count,
    COUNT(DISTINCT customer_id)                   AS unique_customers,
    SUM(discount_amount)                          AS total_discount_given,
    AVG(discount_amount)                          AS avg_discount_per_order,
    AVG(order_subtotal)                           AS avg_order_subtotal,
    AVG(order_total)                              AS avg_order_total,
    SUM(order_total)                              AS total_revenue_with_promo,
    MIN(order_ts)                                 AS first_redemption_ts,
    MAX(order_ts)                                 AS last_redemption_ts
FROM orders_with_promotions
WHERE status = 'completed'
  AND promotion_id IS NOT NULL
GROUP BY promotion_id, promotion_type;

Step 3: Coupon Redemption Rate by Channel and Time Window

Track per-coupon performance by channel (email, social, homepage) and compute rolling redemption rates within the promo window.

-- Coupon redemption breakdown by channel
CREATE MATERIALIZED VIEW coupon_channel_breakdown AS
SELECT
    coupon_code,
    channel,
    COUNT(order_id)           AS redemptions,
    COUNT(DISTINCT customer_id) AS unique_redeemers,
    SUM(discount_amount)      AS discount_total,
    SUM(order_total)          AS revenue_total,
    AVG(order_total)          AS avg_order_value
FROM orders_with_promotions
WHERE status = 'completed'
  AND coupon_code IS NOT NULL
GROUP BY coupon_code, channel;

-- Hourly redemption rate for ongoing promo monitoring
CREATE MATERIALIZED VIEW coupon_redemption_hourly AS
SELECT
    window_start,
    window_end,
    coupon_code,
    promotion_id,
    COUNT(order_id)       AS redemptions_in_window,
    SUM(discount_amount)  AS discounts_in_window,
    SUM(order_total)      AS revenue_in_window
FROM TUMBLE(
    orders_with_promotions,
    order_ts,
    INTERVAL '1 HOUR'
)
WHERE status = 'completed'
  AND coupon_code IS NOT NULL
GROUP BY window_start, window_end, coupon_code, promotion_id;

Step 4: Serving Layer — Sink to Promotion Dashboard DB

Push live promotion metrics to a PostgreSQL database powering your promotions management dashboard.

CREATE SINK promotion_performance_sink
FROM promotion_performance
WITH (
    connector   = 'jdbc',
    jdbc.url    = 'jdbc:postgresql://promoDb:5432/marketing?user=rw&password=secret',
    table.name  = 'promotion_performance_live',
    type        = 'upsert',
    primary_key = 'promotion_id'
);

CREATE SINK coupon_channel_sink
FROM coupon_channel_breakdown
WITH (
    connector   = 'jdbc',
    jdbc.url    = 'jdbc:postgresql://promoDb:5432/marketing?user=rw&password=secret',
    table.name  = 'coupon_channel_live',
    type        = 'upsert',
    primary_key = 'coupon_code,channel'
);

Comparison Table

FAQ

Q: How do I detect coupon fraud — e.g., a single customer using a code hundreds of times? Create a materialized view that counts redemptions per customer_id per coupon_code. Add a filter for counts exceeding a threshold and sink the results to an alerting system. Because RisingWave updates incrementally, the per-customer count updates within seconds of each suspicious order.

Q: Can I measure incremental revenue — orders that wouldn't have happened without the coupon? Incremental revenue requires a control group (customers who didn't receive the coupon). If you A/B test promotions, add a test_group field to order events and compare AOV and conversion rate between groups in a single materialized view. The streaming approach keeps both groups' metrics updated simultaneously.

Q: How do I handle multi-coupon orders or stackable promotions? If an order can have multiple promotions, model each promotion application as a separate record in the source topic (one record per order_id × promotion_id). The aggregation views then naturally handle multi-promotion orders without double-counting order revenue.

Key Takeaways

• Real-time promotion analytics enables mid-flight campaign adjustments — stop a runaway coupon or double down on a high-performing code before the promo window closes
• Streaming SQL computes redemption rates, discount totals, and channel breakdowns continuously without batch reprocessing
• Hourly tumbling windows provide a clean time-series of promotion performance, enabling trend detection during the promo window
• The same event stream that powers order analytics (as used by SHOPLINE for GMV and channel breakdowns) also drives promotion analytics — no additional infrastructure needed
• Coupon fraud detection is a natural extension: filter for per-customer redemption counts exceeding a threshold and route to your alerting pipeline

Get started | Slack