Streaming for AdTech: Real-Time Bidding and Attribution

AdTech requires processing millions of bid requests per second, real-time attribution across channels, and instant campaign performance analytics. Streaming databases process bidstream data, compute attribution models, and serve real-time campaign metrics — all with SQL.

AdTech Use Case	Latency Requirement	SQL Pattern
Bid request processing	<100ms	Stream filtering + enrichment
Real-time attribution	Seconds	Multi-touch event joining
Campaign performance	Sub-minute	Windowed aggregations
Frequency capping	Sub-second	COUNT per user per time window
Fraud detection	Real-time	Anomaly detection patterns

Real-Time Attribution

-- Multi-touch attribution with streaming joins
CREATE MATERIALIZED VIEW attribution AS
SELECT campaign_id, channel,
  COUNT(DISTINCT user_id) as unique_users,
  COUNT(*) FILTER (WHERE event='impression') as impressions,
  COUNT(*) FILTER (WHERE event='click') as clicks,
  COUNT(*) FILTER (WHERE event='conversion') as conversions,
  SUM(revenue) FILTER (WHERE event='conversion') as revenue,
  SUM(revenue) / NULLIF(SUM(spend), 0) as roas
FROM ad_events WHERE ts > NOW()-INTERVAL '24 hours'
GROUP BY campaign_id, channel;

Frequency Capping

CREATE MATERIALIZED VIEW frequency_caps AS
SELECT user_id, campaign_id,
  COUNT(*) FILTER (WHERE ts > NOW()-INTERVAL '1 hour') as impressions_1h,
  COUNT(*) FILTER (WHERE ts > NOW()-INTERVAL '24 hours') as impressions_24h
FROM impressions GROUP BY user_id, campaign_id;

Frequently Asked Questions

Can streaming SQL handle bidstream volume?

Bidstream data can exceed millions of events per second. RisingWave scales horizontally across compute nodes. For the highest-volume bidstream processing, pre-filter at the Kafka level before RisingWave.

How does real-time attribution differ from batch?

Batch attribution computes after the fact (next day). Real-time attribution updates as conversions happen, enabling immediate campaign optimization and budget reallocation.