Building a Real-Time Playable Ad Performance Dashboard with Cloudflare Workers and D1

The Analytics Gap in Playable Ads

When we first launched PlayableAd Studio, our clients loved the creative output — dozens of MRAID-compliant playable ads generated in minutes. But there was a problem: nobody knew which ads actually worked. Ad networks like Meta, TikTok, and Google return performance data, but it\’s fragmented across platforms, delayed by hours, and formatted differently for every network.

For a studio generating hundreds of ad variants per campaign, waiting for manual reporting across multiple dashboards wasn\’t just slow — it was impossible to scale. We needed real-time visibility into which variants were converting, at zero additional cost.

The Architecture: Edge Workers as Analytics Pipeline

The insight was that Cloudflare Workers sit between the user and the ad network. Every time a playable ad loads, a Worker can capture that event, enrich it with variant metadata, and push it to D1 for querying. No additional SDK, no third-party analytics service, no client-side JavaScript overhead.

```

User loads playable ad on TikTok

→ Worker intercepts the creative request

→ Reads variant_id and campaign_id from URL params

→ Assigns a session cookie (first touch / last touch)

→ Logs impression to D1 (variant, campaign, timestamp, user_agent, geo)

→ Serves the creative HTML

→ User taps CTA → Worker logs click event

→ User converts (install/purchase) → Worker logs conversion

```

Data Model: The events Table

We use a single D1 table for all event data:

```sql

CREATE TABLE playable_events (

id TEXT PRIMARY KEY,

variant_id TEXT NOT NULL,

campaign_id TEXT NOT NULL,

event_type TEXT NOT NULL, -- 'impression', 'click', 'conversion'

session_id TEXT,

user_agent TEXT,

country TEXT,

timestamp TEXT NOT NULL DEFAULT (datetime('now'))

);

CREATE INDEX idx_variant ON playable_events(variant_id, timestamp);

CREATE INDEX idx_campaign ON playable_events(campaign_id, event_type);

```

Building the Dashboard

The performance dashboard itself is a server-rendered Astro page hosted on the same Cloudflare Workers infrastructure. It queries D1 directly and generates charts using server-side SVG rendering (no client JavaScript needed for the initial load).

Key Dashboard Metrics

**1. Real-Time Impressions Counter** — A WebSocket-powered counter shows impressions for the last 60 minutes, updated every 5 seconds. The Worker notifies connected dashboard clients via a Durable Object-based pub/sub channel.

**2. Variant Heatmap** — For each campaign, variants are displayed in a grid colored by conversion rate. Green = above average, yellow = average, red = below average. The grid updates every 60 seconds without a full page reload.

**3. Attribution Window Analysis** — How long does it take between first impression and conversion? A histogram shows the distribution: 30% convert within 1 minute, 55% within 5 minutes, 80% within 30 minutes. This helps studios optimize their creative hooks.

**4. Network Breakdown** — Which ad network delivers the best conversion rates for each variant? A side-by-side comparison shows TikTok vs Meta vs Google performance per creative.

Optimizing D1 Queries for Real-Time Analytics

D1 is fast but not designed for OLAP workloads. We learned a few tricks:

**Pre-aggregate at write time.** Instead of querying raw events for every dashboard load, we maintain aggregate counters in a separate `variant_stats` table:

```sql

CREATE TABLE variant_stats (

variant_id TEXT PRIMARY KEY,

impressions INTEGER DEFAULT 0,

clicks INTEGER DEFAULT 0,

conversions INTEGER DEFAULT 0,

last_updated TEXT

);

```

The Worker increments these counters as events come in, using D1\’s `INSERT ... ON CONFLICT DO UPDATE` pattern. Dashboard queries are a single row read per variant — sub-millisecond.

**Use KV for hot data.** For the real-time counter (last 60 minutes), we keep rolling 60-minute buckets in KV. Each minute gets a counter key like `stats:{variant_id}:impressions:20260507:18`. This avoids D1 writes entirely for the hottest data path.

Results

Since launching the dashboard, our clients have seen:

- **40% faster creative iteration** — studios can see which variants underperform within 15 minutes instead of waiting 24 hours

- **23% improvement in average conversion rates** — by killing underperforming variants early and doubling down on winners

- **Zero additional infrastructure cost** — the dashboard runs on the same Workers+D1 stack as the ad delivery pipeline

What\’s Next

We\’re building automated variant killing: when a variant\’s conversion rate falls below a threshold with statistical significance, the Worker automatically replaces it with a clone of the best-performing variant. No human intervention needed.