The bid request lands in your DSP in 100 milliseconds. You have maybe 10ms of that window to pull features, run your bidding model, and return a price. Meanwhile, the audience segment powering your decision was built from yesterday's batch job.
That mismatch is not a minor inefficiency. It's a fundamental mis-pricing of intent.
This post walks through how streaming SQL materialized views can serve as the feature layer for real-time bidding (RTB) systems — covering audience segmentation, CTR prediction features, frequency capping, and budget pacing. It's written for engineers at DSPs, ad networks, and ad tech platforms who are tired of building custom Flink topologies every time a new targeting signal is needed.
Why Real-Time Matters in Ad Targeting
Intent Decay Curves
Behavioral intent doesn't age well. A user who searched "mortgage rates" an hour ago is in a very different buying moment than one who searched the same term last week. That's not an opinion; it's backed by how conversion rates track against signal recency.
Consider the typical shape of an intent decay curve for a high-consideration purchase category like auto or finance:
- 0-1 hour after signal: conversion likelihood at peak, CPM can support 3-5x premium
- 1-24 hours: intent still elevated, 1.5-2x premium justified
- 24-72 hours: returning to baseline, only mild signal value remains
- 7+ days: effectively noise for most categories
Batch pipelines that rebuild segments nightly treat these as equivalent. A user who visited three car-dealer pages this morning shows up in the same "auto intender" bucket as one who did the same three days ago. The advertiser overpays for stale intent and underpays for fresh.
The Economics of Freshness
Audience segments are priced on CPM. But the true value driver is the probability that the impression converts. If your features lag 12-24 hours behind behavior, your predicted CTR diverges from actual CTR. At scale, that divergence means:
- Overbidding on users whose intent has cooled
- Underbidding on users showing fresh high-value signals
- Budget wasted on poorly-timed impressions that drive up your eCPM without delivering conversions
RTB platforms handle hundreds of millions of bid requests per day. Even a 5% improvement in feature freshness has measurable impact on campaign ROAS — which is why the DSPs investing in real-time feature infrastructure are compounding advantages against those still running nightly batch jobs.
Types of Real-Time Ad Targeting Features
Behavioral Signals
These are the raw material: page views, searches, clicks, product detail page visits, cart additions. The key attribute is recency. A cart abandonment from 20 minutes ago is a fire-alarm signal for retargeting. The same event from 48 hours ago still has value but demands a different bid strategy.
Behavioral signals need to be windowed — you want counts and aggregates over sliding time windows, not a full historical sum.
Content Affinity
Beyond individual events, content affinity captures the pattern of what a user engages with: which IAB content categories they visit, how long they spend, what they return to. A user who has spent 40 minutes reading automotive reviews across three visits in the past week is a different prospect than one who landed on a car page once via a search ad.
Content affinity features tend to decay more slowly than pure behavioral signals — category preference is stickier than in-session intent.
Contextual Features
Contextual targeting operates at the impression level rather than the user level: what is the current page about, what is the user doing right now, what time is it, what device are they on. These features don't need historical state — they're derived from the bid request itself — but they need to be joined against real-time inventory signals (current page context, current session behavior) to be predictive.
Cross-Device Signals
Deterministic and probabilistic identity graphs let you aggregate signals across a user's devices. The challenge is that these graphs are expensive to maintain in real time. Most implementations do the graph resolution offline and maintain a device-to-user mapping table that streaming systems can join against.
Audience Segment Materialized Views
The traditional approach: run a Spark job nightly, export segment membership to a key-value store, let the DSP look up segment IDs at bid time. This works at the scale of yesterday's audience, with yesterday's behavior.
The streaming approach: define segments as SQL materialized views. The view maintains its own state incrementally as events arrive. When the bid request lands, the segment membership is already computed.
High-Intent Auto Buyers
-- Audience segment: high-intent auto buyers (active in last 48h)
CREATE MATERIALIZED VIEW audience_auto_buyers AS
SELECT DISTINCT
user_id,
MAX(event_time) AS last_signal_at,
COUNT(*) AS auto_signals_48h,
array_agg(DISTINCT page_category) AS auto_categories_visited
FROM user_events
WHERE page_category IN ('auto', 'car-reviews', 'car-dealers', 'auto-financing')
AND event_time >= NOW() - INTERVAL '48 hours'
GROUP BY user_id
HAVING COUNT(*) >= 3;
This view updates incrementally as events stream in. When a user hits their third auto-category page, they enter the segment immediately — no next-day batch run required.
The HAVING COUNT(*) >= 3 threshold filters out noise. Single-event signals are weak; three relevant touches within 48 hours is a meaningful intent pattern.
Multi-Signal Segments with Intent Scoring
You can layer signals from different sources using joins across materialized views:
CREATE MATERIALIZED VIEW high_value_auto_intenders AS
SELECT
u.user_id,
u.auto_signals_48h,
s.search_count_7d,
CASE
WHEN u.auto_signals_48h >= 5 AND s.search_count_7d >= 2 THEN 'tier_1'
WHEN u.auto_signals_48h >= 3 THEN 'tier_2'
ELSE 'tier_3'
END AS intent_tier
FROM audience_auto_buyers u
LEFT JOIN user_search_signals s ON u.user_id = s.user_id
WHERE s.search_category = 'auto';
Joining materialized views lets you compose multi-signal segments without reprocessing the full event history.
Recency-Weighted Segments
For some categories, you want to weight recent events more heavily in segment scoring:
CREATE MATERIALIZED VIEW user_category_affinity AS
SELECT
user_id,
page_category,
COUNT(*) AS total_visits,
COUNT(*) FILTER (WHERE event_time >= NOW() - INTERVAL '4 hours') AS visits_4h,
COUNT(*) FILTER (WHERE event_time >= NOW() - INTERVAL '24 hours') AS visits_24h,
COUNT(*) FILTER (WHERE event_time >= NOW() - INTERVAL '7 days') AS visits_7d,
MAX(event_time) AS last_visit
FROM user_events
WHERE event_time >= NOW() - INTERVAL '7 days'
GROUP BY user_id, page_category;
Exposing multiple time windows as separate columns lets your bidding model weight them differently per campaign and category.
Real-Time Bid Features
CTR Prediction Features
Your CTR model needs features at bid time. Stale features mean stale predictions. The features that matter most in a CTR model — user-ad interaction history, category affinity recency, time-of-day session patterns — are exactly the ones that decay fastest.
A materialized view for serving CTR features:
CREATE MATERIALIZED VIEW user_ad_context_features AS
SELECT
user_id,
COUNT(*) FILTER (WHERE event_type = 'click') AS clicks_24h,
COUNT(*) FILTER (WHERE event_type = 'impression') AS impressions_24h,
COUNT(*) FILTER (WHERE event_type = 'click') * 1.0
/ NULLIF(COUNT(*) FILTER (WHERE event_type = 'impression'), 0) AS observed_ctr_24h,
COUNT(DISTINCT campaign_id) AS campaigns_seen_today,
MAX(event_time) AS last_interaction
FROM user_ad_events
WHERE event_time >= NOW() - INTERVAL '24 hours'
GROUP BY user_id;
At bid time, look up user_id in this view. The features are already computed, indexed, and fresh.
Frequency Capping
Frequency caps are a hard constraint, not a model feature. If a user has already seen a campaign ad 5 times today, you don't bid — full stop. Getting this wrong burns budget on overexposed users and degrades campaign performance.
-- Frequency cap: how many times has this user seen this campaign today?
CREATE MATERIALIZED VIEW ad_frequency AS
SELECT
user_id,
campaign_id,
COUNT(*) AS impressions_today,
MAX(served_at) AS last_served_at
FROM ad_impression_events
WHERE served_at >= date_trunc('day', NOW())
GROUP BY user_id, campaign_id;
At bid time, check: SELECT impressions_today FROM ad_frequency WHERE user_id = $1 AND campaign_id = $2. If impressions_today >= cap, skip the bid. This lookup needs to return in under 2ms to stay inside your bid latency budget.
The critical property here is that the view updates as impression events arrive. If a user was served the ad 30 seconds ago on a different device (assuming identity resolution is in place), the count already reflects that impression. Batch-computed frequency caps can't make that guarantee.
Budget Pacing
Budget pacing ensures you don't exhaust a campaign's daily budget in the first three hours of the day — and that you don't underspend by holding back too conservatively.
-- Budget pacing: how much of today's budget is spent?
CREATE MATERIALIZED VIEW campaign_pacing AS
SELECT
campaign_id,
SUM(bid_price) AS spend_today,
MAX(daily_budget) AS daily_budget,
SUM(bid_price) / NULLIF(MAX(daily_budget), 0) AS pacing_ratio
FROM ad_spend_events
WHERE event_date = CURRENT_DATE
GROUP BY campaign_id;
Your bidding logic can use pacing_ratio against the expected ratio for the current hour. If a campaign is at 60% of budget at 2 PM (expected ~50%), it's running ahead — reduce bid multiplier or increase throttling. If it's at 30% by midday, it's falling behind — increase aggressiveness.
A pacing view computed in real time responds to burst spend events within seconds. A batch-computed pacing view is flying blind until the next recalculation.
Integration with Bidding: Sub-10ms Feature Serving
Your bid decisioning flow at a DSP looks roughly like:
- Receive bid request from SSP (user_id, page context, floor price)
- Look up audience segments for user
- Fetch bid features (frequency, pacing, CTR features)
- Score against active campaigns
- Apply bid price calculation
- Return bid response
Steps 2 and 3 are the feature serving layer. For a system processing 500k bid requests per second, this layer needs to:
- Return results in under 5ms (leaving headroom in the 10ms window for model scoring)
- Handle lookup by user_id as the primary key
- Serve consistent, fresh data
Materialized views in a streaming database like RisingWave are queryable via standard PostgreSQL wire protocol. Your bidding service can treat them like any other Postgres table, using a connection pool with prepared statements.
# Pseudocode: bid feature serving
async def get_bid_features(user_id: str, campaign_id: str) -> BidFeatures:
row = await pool.fetchrow("""
SELECT
f.impressions_today,
f.last_served_at,
p.pacing_ratio,
c.observed_ctr_24h,
a.intent_tier
FROM ad_frequency f
JOIN campaign_pacing p ON p.campaign_id = $2
LEFT JOIN user_ad_context_features c ON c.user_id = $1
LEFT JOIN high_value_auto_intenders a ON a.user_id = $1
WHERE f.user_id = $1 AND f.campaign_id = $2
""", user_id, campaign_id)
return BidFeatures.from_row(row)
Scale Considerations
Let's be honest about where SQL streaming has limits. At 500k+ QPS on bid feature lookups, you need:
- Read replicas or a caching layer in front of the streaming database. Serving reads directly at that volume from a single streaming database node is not realistic. Use Redis or a local process cache with short TTLs (1-5 seconds) for the hottest keys.
- Point lookups, not scans. Materialized views need to be structured so that bid-time lookups are index seeks by user_id. Avoid serving full-table features at bid time.
- Async impression event writes. Impression and spend events should be written to Kafka first, then consumed by the streaming database. Don't write directly at bid time — that adds latency on the critical path.
The streaming database handles the hard part: maintaining correct incremental state across millions of users in real time. The serving tier handles the QPS pressure with standard caching patterns.
Privacy Considerations: GDPR, CCPA, and the Cookieless Future
Consent and User Identification
Behavioral data tied to persistent user identifiers falls under GDPR/CCPA in most jurisdictions. Before processing events through audience segment materialized views, ensure:
- Consent has been captured and stored per user
- Your streaming pipeline enforces consent status before enriching events with PII
- Deletion requests propagate to the streaming layer within required timeframes (72 hours under GDPR)
A consent-aware filtering pattern:
CREATE MATERIALIZED VIEW consented_user_events AS
SELECT e.*
FROM raw_user_events e
JOIN user_consent c ON e.user_id = c.user_id
WHERE c.targeting_consent = true
AND c.consent_updated_at <= e.event_time;
Downstream audience segment views build on consented_user_events rather than raw events. When consent is revoked, the join fails and the user drops from segments incrementally.
Cookieless Targeting
Third-party cookie deprecation shifts the foundation from third-party behavioral graphs to first-party data: logged-in user identity, CRM signals, hashed email matching. Streaming SQL works well here — first-party event streams (login events, purchase history, support interactions) feed the same materialized view pattern.
The difference is that first-party data requires advertiser participation. Your DSP or ad network needs to ingest and activate advertiser first-party data in a privacy-compliant way, often through clean rooms or hashed match tables. The streaming feature layer remains the same; the data source changes.
Frequency Capping: Getting It Right at Scale
Frequency capping looks simple but has several failure modes worth calling out explicitly.
The cross-device problem. A frequency cap of 5 impressions per day means nothing if each device is counted separately. You need identity resolution before you can cap at the user level across devices. That resolution happens in your identity graph, not in the streaming SQL layer.
The impression lag problem. There's always some latency between serving an impression and writing the impression event. If your streaming pipeline has 10-15 seconds of lag, a user could receive 2-3 impressions in quick succession before the first one updates the frequency view. Acceptable for many campaigns; not acceptable for campaigns with a cap of 1 or 2 impressions.
For hard caps at low frequencies, you may need a separate counter in Redis with atomic increment semantics, using the streaming materialized view as the slower but authoritative source for reporting and reconciliation.
Day boundary resets. The materialized view filters on date_trunc('day', NOW()). As UTC midnight rolls over, the filter window shifts. Make sure your data pipeline and capping logic use consistent timezone definitions — frequency cap bugs at day boundaries are a classic ad tech footgun.
FAQ
Can I use streaming SQL for real-time bidding directly, without a separate feature store?
For many DSPs at moderate scale (under 100k QPS), yes — materialized views in a streaming database can serve as the feature store directly, with a thin caching layer in front. At very high QPS (500k+), you need caching or Redis for the hottest features, but the streaming database remains the system of record for feature computation.
How does this compare to building custom Flink jobs for audience segmentation?
Flink gives you more flexibility (custom UDFs, complex stateful logic, exactly-once semantics on arbitrary topologies), but you're writing Java or Scala, managing operator state, handling backpressure manually, and owning the deployment infrastructure. Streaming SQL lets data engineers define new segments by writing SQL — same expressiveness for most audience segmentation use cases, dramatically lower operational overhead.
What's the cold start problem for new users?
New users have no history in the materialized views. Their features will return null or zero values until they accumulate events. This is a fundamental constraint of behavioral targeting, not a streaming SQL problem — batch systems have the same issue for users who arrive between segment refresh cycles. The difference is that streaming systems start accumulating features from the user's first event; batch systems wait until the next job run.
Can I handle look-alike modeling with this approach?
Look-alike modeling (find users who resemble a seed audience) typically requires ML similarity computations that don't map cleanly to SQL. The streaming layer feeds the seed audience definition in real time, but the look-alike expansion step is usually done offline in a model-building pipeline. The two layers are complementary.
How do I handle event deduplication in impression data?
Impression events can arrive more than once due to network retries or SSP reporting quirks. Your frequency cap counts need deduplication. Use a unique event ID on each impression event, and deduplicate in your Kafka consumer or at the streaming layer before events reach the materialized views. Getting this wrong inflates frequency counts and causes legitimate impressions to be capped.
Real-time audience segmentation is not a speculative future — DSPs that have invested in sub-second feature freshness are already pricing intent more accurately and winning on ROAS. The constraint has historically been engineering complexity: building custom streaming infrastructure for every new signal or segment type is slow and expensive.
Streaming SQL changes that calculus. Defining a new audience segment is a SQL statement, not a Flink job. Updating the segment definition is an ALTER MATERIALIZED VIEW, not a deployment. The feature layer becomes a place where data engineers can experiment and iterate at the speed of campaign strategy, not at the speed of infrastructure releases.
The ad tech stack is overdue for this layer to become as accessible as the rest of the modern data stack.
