Real-Time Ad Tech with Streaming SQL: Click Attribution, Bid Optimization, Audience Targeting, and Click Fraud

Real-time ad tech with streaming SQL means the four computations a demand-side platform or ad network depends on, click attribution, bid optimization, audience targeting features, and click fraud detection, run as continuously updated materialized views instead of nightly Spark jobs. This guide builds all four on RisingWave, a PostgreSQL-compatible streaming database, so the bidder reads fresh signals with a point lookup and the attribution dashboard reflects conversions that happened seconds ago.

Why Nightly Batch Is the Wrong Cadence for Ad Tech

A bid request lands in your DSP with roughly 100 milliseconds to answer, and about 10 of those are available for feature lookup. Attribution, pacing, frequency, and fraud signals computed overnight are already stale by the time the first auction of the day arrives. The costs compound in specific ways:

• Intent decays fast. For a high-consideration category such as auto or finance, conversion likelihood peaks in the first hour after a signal and is close to noise after seven days. A nightly segment rebuild puts the user who visited three dealer pages this morning in the same bucket as one who did it three days ago.
• Attribution drives budget. Last-click defaults undervalue the channels that open the journey. Overnight multi-touch jobs fix the model but not the latency, so today's bids still run on yesterday's credit.
• Frequency caps leak. A counter that is an hour old lets a user who has already seen ad 10 receive ads 11, 12, and 13.
• Pacing runs blind. A campaign at 60% of daily budget at 2 PM is overspending, and a batch pacing view will not say so until the next run.
• Fraud costs real money. Click fraud drains an estimated $35 to $50 billion a year from digital advertising. Detecting a click farm the next morning means the budget is already gone.

RisingWave keeps every one of these as an incrementally maintained materialized view. When a click, conversion, impression, or auction event arrives, only the affected rows update, and the result is queryable over the PostgreSQL wire protocol or pushed to Kafka for fan-out.

Architecture: Three Streams and a Bidder

Ad exchange / SSP     ──►  rtb.auction.events, ad-impression-events  ─┐
Ad platforms          ──►  ad-clicks                                  ├──►  Kafka  ──►  RisingWave materialized views
App backend           ──►  conversions                                │                         │
Campaign DB (Postgres)──►  campaigns, budgets via CDC                 ─┘                         ├──►  Bidder: point lookups over PostgreSQL protocol
                                                                                                  ├──►  Kafka: fraud scores, bid signals, audience updates
                                                                                                  └──►  BI: attribution dashboards

Clicks and conversions feed attribution. Auctions, impressions, and conversions feed bid features, frequency caps, and pacing. Clicks alone, with IP and device metadata, feed fraud detection. Campaign configuration arrives through PostgreSQL CDC so a budget change propagates without a deploy. RisingWave is open source under Apache 2.0, written in Rust, and checkpoints state to S3, so view state survives restarts and can be rebuilt from the Kafka log.

CREATE SOURCE ad_clicks (
    click_id      VARCHAR,
    user_id       VARCHAR,
    campaign_id   VARCHAR,
    channel       VARCHAR,       -- 'google_ads', 'facebook', 'linkedin', 'tiktok', 'email'
    ad_group      VARCHAR,
    publisher_id  VARCHAR,
    ip_address    VARCHAR,
    device_id     VARCHAR,
    user_agent    VARCHAR,
    landing_page  VARCHAR,
    click_ts      TIMESTAMPTZ
) WITH (
    connector = 'kafka',
    topic = 'ad-clicks',
    properties.bootstrap.server = 'kafka:9092',
    scan.startup.mode = 'latest'
) FORMAT PLAIN ENCODE JSON;

CREATE SOURCE conversions (
    conversion_id    VARCHAR,
    user_id          VARCHAR,
    campaign_id      VARCHAR,
    conversion_type  VARCHAR,    -- 'purchase', 'signup'
    revenue          NUMERIC,
    conversion_ts    TIMESTAMPTZ
) WITH (
    connector = 'kafka',
    topic = 'conversions',
    properties.bootstrap.server = 'kafka:9092',
    scan.startup.mode = 'latest'
) FORMAT PLAIN ENCODE JSON;

CREATE SOURCE auction_events (
    auction_id    VARCHAR,
    user_id       VARCHAR,
    publisher_id  VARCHAR,
    ad_slot_id    VARCHAR,
    floor_price   NUMERIC,
    event_time    TIMESTAMPTZ
) WITH (
    connector = 'kafka',
    topic = 'rtb.auction.events',
    properties.bootstrap.server = 'kafka:9092',
    scan.startup.mode = 'latest'
) FORMAT PLAIN ENCODE JSON;

CREATE SOURCE ad_impression_events (
    impression_id  VARCHAR,
    user_id        VARCHAR,
    campaign_id    VARCHAR,
    ad_slot_id     VARCHAR,
    bid_price      NUMERIC,
    served_at      TIMESTAMPTZ
) WITH (
    connector = 'kafka',
    topic = 'ad-impression-events',
    properties.bootstrap.server = 'kafka:9092',
    scan.startup.mode = 'latest'
) FORMAT PLAIN ENCODE JSON;

CREATE TABLE campaigns (
    campaign_id    VARCHAR PRIMARY KEY,
    advertiser_id  VARCHAR,
    daily_budget   NUMERIC,
    frequency_cap  INT
) WITH (
    connector = 'postgres-cdc',
    hostname = 'postgres', port = '5432',
    username = 'rw_user', password = 'secret',
    database.name = 'adserver', schema.name = 'public', table.name = 'campaigns'
);

Click Attribution: Linear, First-Touch, and Last-Touch

Attribution joins each conversion to the clicks from the same user inside an attribution window. A seven-day window means a click earns credit only if the conversion happens within seven days of it. The join runs continuously, so a conversion that arrives now is attributed now:

CREATE MATERIALIZED VIEW mv_click_conversions AS
SELECT
    c.click_id, c.user_id, c.campaign_id, c.channel, c.ad_group, c.click_ts,
    v.conversion_id, v.conversion_type, v.revenue, v.conversion_ts,
    v.conversion_ts - c.click_ts AS time_to_convert
FROM ad_clicks c
JOIN conversions v ON c.user_id = v.user_id
WHERE c.click_ts <= v.conversion_ts
  AND v.conversion_ts - c.click_ts <= INTERVAL '7 DAYS';

Linear attribution splits revenue equally across the touchpoints in the path. First-touch credits the click that introduced the user, and last-touch credits the final click before conversion. Computing all three in one view lets you compare models on the same data:

CREATE MATERIALIZED VIEW mv_attribution_scores AS
SELECT
    cc.*,
    tc.touch_count,
    cc.revenue / tc.touch_count                                             AS linear_revenue,
    CASE WHEN cc.click_ts = tc.first_click_ts THEN cc.revenue ELSE 0 END    AS first_touch_revenue,
    CASE WHEN cc.click_ts = tc.last_click_ts  THEN cc.revenue ELSE 0 END    AS last_touch_revenue
FROM mv_click_conversions cc
JOIN (
    SELECT conversion_id,
           COUNT(*)       AS touch_count,
           MIN(click_ts)  AS first_click_ts,
           MAX(click_ts)  AS last_click_ts
    FROM mv_click_conversions
    GROUP BY conversion_id
) tc USING (conversion_id);

CREATE MATERIALIZED VIEW mv_channel_attribution AS
SELECT
    channel,
    COUNT(DISTINCT conversion_id)   AS conversions_assisted,
    COUNT(*)                        AS total_touchpoints,
    SUM(linear_revenue)             AS linear_revenue,
    SUM(first_touch_revenue)        AS first_touch_revenue,
    SUM(last_touch_revenue)         AS last_touch_revenue,
    AVG(linear_revenue)             AS avg_linear_revenue_per_touch
FROM mv_attribution_scores
GROUP BY channel;

With a sample of four users converting across Google Ads, Facebook, LinkedIn, TikTok, and email, the channel view tells a story that no single model reveals. Email dominates last-touch revenue but has zero first-touch credit: it is a closer, not an opener, and cutting it because it "does not generate traffic" would be a mistake. TikTok shows the inverse, strong first-touch and no last-touch, so judging it on last-click return on ad spend undervalues it. Google Ads appears in most paths and scores evenly across models. These comparisons update as each new conversion arrives rather than in a weekly review.

Bid Optimization: Conversion Rates, Pacing, and Inventory Signals

The bidder should read a single precomputed row at auction time, not run joins and aggregations. Three views supply the bid signal.

A rolling one-hour conversion rate per user and campaign, sliding every five minutes, keeps the signal fresh without recomputing from scratch:

CREATE MATERIALIZED VIEW user_campaign_cvr AS
SELECT
    user_id,
    campaign_id,
    window_start,
    window_end,
    COUNT(*)                AS conversions,
    SUM(revenue)            AS total_value,
    AVG(revenue)            AS avg_value
FROM HOP(conversions, conversion_ts, INTERVAL '5 MINUTES', INTERVAL '1 HOUR')
GROUP BY user_id, campaign_id, window_start, window_end;

Inventory signals per publisher and slot show where cheap inventory is available right now:

CREATE MATERIALIZED VIEW inventory_signals AS
SELECT
    publisher_id,
    ad_slot_id,
    window_start,
    window_end,
    COUNT(*)                                     AS auctions_seen,
    COUNT(*) FILTER (WHERE floor_price < 2.50)   AS cheap_inventory,
    AVG(floor_price)                             AS avg_floor
FROM TUMBLE(auction_events, event_time, INTERVAL '10 MINUTES')
GROUP BY publisher_id, ad_slot_id, window_start, window_end;

Budget pacing compares spend so far today against the daily budget from the CDC-fed campaigns table. A pacing ratio of 0.60 at 2 PM against an expected 0.50 means the campaign is running hot and the bid multiplier should come down:

CREATE MATERIALIZED VIEW campaign_pacing AS
SELECT
    i.campaign_id,
    SUM(i.bid_price)                                            AS spend_today,
    MAX(c.daily_budget)                                         AS daily_budget,
    ROUND(SUM(i.bid_price) / NULLIF(MAX(c.daily_budget), 0), 3) AS pacing_ratio
FROM ad_impression_events i
JOIN campaigns c USING (campaign_id)
WHERE i.served_at >= DATE_TRUNC('day', NOW())
GROUP BY i.campaign_id;

Because campaigns is a CDC table, raising a budget in the ad server's PostgreSQL changes pacing_ratio within seconds with no redeploy.

Frequency Capping

Frequency caps are a hard constraint, not a model feature. A per-user, per-campaign impression count over the current day answers the question on every auction:

CREATE MATERIALIZED VIEW ad_frequency AS
SELECT
    i.user_id,
    i.campaign_id,
    COUNT(*)            AS impressions_today,
    MAX(i.served_at)    AS last_served_at,
    MAX(c.frequency_cap) AS frequency_cap
FROM ad_impression_events i
JOIN campaigns c USING (campaign_id)
WHERE i.served_at >= DATE_TRUNC('day', NOW())
GROUP BY i.user_id, i.campaign_id;

At bid time: SELECT impressions_today, frequency_cap FROM ad_frequency WHERE user_id = $1 AND campaign_id = $2. If the count has reached the cap, return no bid.

Three failure modes deserve attention. Cross-device caps require identity resolution before the impression event reaches Kafka; the streaming layer counts whatever identifier it is given. Impression lag of 10 to 15 seconds between serving and the event landing means a user can receive two or three impressions in quick succession before the view catches up, which is acceptable for a cap of 5 and not for a cap of 1, so very low caps may need an atomic Redis counter with the materialized view as the authoritative source for reporting. Day boundary resets depend on DATE_TRUNC('day', NOW()) and the time zone it runs in, so keep the pipeline and the capping logic on one time zone definition.

Audience Targeting Features

Segments defined as SQL views update the moment a user crosses the threshold. The traditional path, a nightly Spark job exporting segment IDs to a key-value store, serves yesterday's audience with yesterday's behavior.

A high-intent segment over the last 48 hours, with a minimum of three relevant touches to filter noise:

CREATE SOURCE user_events (
    user_id        VARCHAR,
    event_type     VARCHAR,     -- 'page_view', 'search', 'click', 'impression'
    page_category  VARCHAR,     -- IAB-style category
    campaign_id    VARCHAR,
    event_time     TIMESTAMPTZ
) WITH (
    connector = 'kafka',
    topic = 'user-events',
    properties.bootstrap.server = 'kafka:9092',
    scan.startup.mode = 'latest'
) FORMAT PLAIN ENCODE JSON;

CREATE MATERIALIZED VIEW audience_auto_buyers AS
SELECT
    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;

Recency-weighted affinity exposes several windows as separate columns so the bidding model can weight them differently per campaign:

CREATE MATERIALIZED VIEW user_category_affinity AS
SELECT
    user_id,
    page_category,
    COUNT(*)                                                               AS visits_7d,
    COUNT(*) FILTER (WHERE event_time >= NOW() - INTERVAL '24 HOURS')      AS visits_24h,
    COUNT(*) FILTER (WHERE event_time >= NOW() - INTERVAL '4 HOURS')       AS visits_4h,
    MAX(event_time)                                                        AS last_visit
FROM user_events
WHERE event_time >= NOW() - INTERVAL '7 DAYS'
GROUP BY user_id, page_category;

Click-through-rate features for the bidding model come from the same stream:

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_24h
FROM user_events
WHERE event_time >= NOW() - INTERVAL '24 HOURS'
GROUP BY user_id;

Consent belongs upstream of every segment. Build the segments on a consented view so that a revoked consent drops the user from every segment incrementally, and deletion requests propagate within GDPR's 72-hour window:

CREATE TABLE user_consent (user_id VARCHAR PRIMARY KEY, targeting_consent BOOLEAN, consent_updated_at TIMESTAMPTZ);

CREATE MATERIALIZED VIEW consented_user_events AS
SELECT e.*
FROM user_events e
JOIN user_consent c USING (user_id)
WHERE c.targeting_consent = TRUE
  AND c.consent_updated_at <= e.event_time;

As third-party cookies disappear, the same pattern applies to first-party streams: login events, purchase history, and hashed email matches feed the views in place of third-party behavioral graphs.

Click Fraud Detection

Fraud detection reads the same click stream with its IP, device, and user agent fields. Tumbling five-minute windows per IP catch brute-force click volume, and ten-minute hopping windows per device catch click farms that spread traffic across overlapping intervals:

CREATE TABLE fraud_ip_blocklist (ip_address VARCHAR PRIMARY KEY, reason VARCHAR, added_at TIMESTAMPTZ);

CREATE MATERIALIZED VIEW ip_click_rate AS
SELECT
    ip_address,
    window_start,
    window_end,
    COUNT(*)                        AS click_count,
    COUNT(DISTINCT campaign_id)     AS campaigns_targeted,
    COUNT(DISTINCT device_id)       AS device_count,
    CASE WHEN COUNT(*) > 100 THEN 'HIGH'
         WHEN COUNT(*) > 50  THEN 'MEDIUM'
         ELSE 'LOW' END             AS fraud_risk_level
FROM TUMBLE(ad_clicks, click_ts, INTERVAL '5 MINUTES')
GROUP BY ip_address, window_start, window_end;

CREATE MATERIALIZED VIEW device_click_velocity AS
SELECT
    device_id,
    window_start,
    window_end,
    COUNT(*)                        AS click_count,
    COUNT(DISTINCT ip_address)      AS ip_count,
    COUNT(DISTINCT campaign_id)     AS campaign_count
FROM HOP(ad_clicks, click_ts, INTERVAL '1 MINUTE', INTERVAL '10 MINUTES')
GROUP BY device_id, window_start, window_end;

CREATE MATERIALIZED VIEW suspicious_clicks AS
SELECT c.click_id, c.campaign_id, c.publisher_id, c.ip_address, c.device_id, c.click_ts,
       CASE WHEN b.ip_address IS NOT NULL THEN 'blocklisted'
            WHEN r.fraud_risk_level = 'HIGH' THEN 'ip_velocity'
            WHEN d.ip_count >= 20 THEN 'device_ip_rotation'
       END AS reason
FROM ad_clicks c
LEFT JOIN fraud_ip_blocklist b USING (ip_address)
LEFT JOIN ip_click_rate r
       ON r.ip_address = c.ip_address AND c.click_ts >= r.window_start AND c.click_ts < r.window_end
LEFT JOIN device_click_velocity d
       ON d.device_id = c.device_id AND c.click_ts >= d.window_start AND c.click_ts < d.window_end
WHERE b.ip_address IS NOT NULL OR r.fraud_risk_level = 'HIGH' OR d.ip_count >= 20;

In production, join velocity signals with account context to reduce false positives: a high-velocity IP that belongs to a logged-in user with conversion history scores differently from an anonymous one. Session windows over the click stream catch manual fraud operators who pause between bursts. Suspicious clicks should also be excluded from mv_click_conversions so fraudulent traffic does not earn attribution credit.

Serving at Auction Speed

At bid time the bidder issues point lookups keyed by user_id and campaign_id, joining the frequency, pacing, CTR, and segment views in one statement over a connection pool with prepared statements:

SELECT f.impressions_today, f.frequency_cap,
       p.pacing_ratio,
       x.observed_ctr_24h,
       a.auto_signals_48h
FROM ad_frequency f
JOIN campaign_pacing p ON p.campaign_id = $2
LEFT JOIN user_ad_context_features x ON x.user_id = $1
LEFT JOIN audience_auto_buyers a      ON a.user_id = $1
WHERE f.user_id = $1 AND f.campaign_id = $2;

At several hundred thousand bid requests per second, place a short-TTL cache such as Redis or a local process cache in front of the streaming database for the hottest keys, structure every lookup as an index seek by user, and write impression and spend events to Kafka asynchronously rather than on the bidding critical path. RisingWave maintains correct incremental state across millions of users; the serving tier absorbs the QPS with ordinary caching.

For fan-out to many bidder instances and to enforcement systems, push the views to Kafka:

CREATE SINK bid_signals_sink FROM user_campaign_cvr
WITH (connector = 'kafka', properties.bootstrap.server = 'kafka:9092', topic = 'rtb.bid.signals')
FORMAT UPSERT ENCODE JSON;

CREATE SINK fraud_scores_sink FROM suspicious_clicks
WITH (connector = 'kafka', properties.bootstrap.server = 'kafka:9092', topic = 'fraud-scores')
FORMAT PLAIN ENCODE JSON;

Ad servers and DSPs subscribe to fraud-scores and block the IP or device before the next click bills the advertiser.

Batch Pipelines vs Streaming SQL for Ad Tech

Frequently Asked Questions

How does a seven-day attribution window work in streaming SQL? It is a predicate on the join between clicks and conversions: the click must precede the conversion and the gap must be at most seven days. Change the interval to change the window, or store per-channel windows in a table and join on them.

Can RisingWave serve bid features at hundreds of thousands of QPS? The views stay correct at that event volume, but serve the hottest lookups through a short-TTL cache in front of RisingWave and keep every query an index seek by user. The streaming database is the authoritative, always-fresh source; the cache handles raw request rate.

How do I avoid attributing revenue to fraudulent clicks? Build the attribution join on a filtered click view that excludes rows present in suspicious_clicks. Because both are materialized views, a click flagged as fraud drops out of attribution incrementally.

What happens to frequency caps across devices? The view counts whatever user_id it receives. Resolve device identifiers to a user in your identity graph before publishing impression events, or caps will apply per device.

How do consent revocations propagate? Segments are built on consented_user_events, which joins against the consent table. When consent flips to false, the join no longer matches and the user leaves every downstream segment without a batch rebuild.