Marketing dashboards slow down for a predictable reason. The underlying data model is an event log: page views, clicks, form submissions, purchases, email opens. Each campaign produces hundreds of thousands of events. As campaigns accumulate, the events table grows into the hundreds of millions. Standard reporting queries that joined campaign metadata against raw events start taking 30 seconds. Teams respond with pre-computed reports, weekly exports, and row limits that make the dashboards less useful.
ClickHouse® solves this at the architecture level rather than the caching level. Its columnar storage means an attribution query that touches campaign_id, channel, and conversion_value out of 20 columns reads only 3 columns worth of data. Vectorized execution processes aggregations across millions of rows per second. The result is marketing dashboards that stay fast as the event volume grows, without pre-computing everything in advance.
This post covers the event schema design, attribution models, funnel analysis, cohort retention, and pre-aggregation patterns that make real-time marketing dashboards work at scale.
Marketing event schema
The core table captures all marketing-attributed user activity as an append-only event log. Attribution dimensions belong in LowCardinality columns because they are the first filter in nearly every marketing query:
CREATE TABLE marketing_events
(
event_time DateTime64(3),
event_type LowCardinality(String), -- 'page_view', 'click', 'lead', 'purchase', 'email_open'
user_id String,
session_id String,
campaign_id LowCardinality(Nullable(String)),
channel LowCardinality(String), -- 'paid_search', 'organic', 'email', 'social', 'direct'
source LowCardinality(Nullable(String)),
medium LowCardinality(Nullable(String)),
ad_group LowCardinality(Nullable(String)),
keyword LowCardinality(Nullable(String)),
landing_page LowCardinality(Nullable(String)),
conversion_value Nullable(Float64),
currency LowCardinality(Nullable(String)),
country LowCardinality(String),
device_type LowCardinality(String), -- 'desktop', 'mobile', 'tablet'
properties String
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(event_time)
ORDER BY (channel, campaign_id, event_time, user_id);
channel and campaign_id first in the sort key means channel performance and campaign-level queries skip irrelevant data. event_time before user_id reflects that most marketing queries filter on date ranges before filtering on users.
Campaign performance
Real-time campaign performance across all channels, computing cost-per-conversion and return on ad spend:
SELECT
channel,
campaign_id,
toStartOfDay(event_time) AS day,
uniq(user_id) AS unique_users,
count() AS total_events,
countIf(event_type = 'lead') AS leads,
countIf(event_type = 'purchase') AS purchases,
round(sum(conversion_value), 2) AS revenue,
round(
countIf(event_type = 'purchase') /
uniq(user_id) * 100, 2
) AS purchase_rate_pct,
avg(conversion_value) AS avg_order_value
FROM marketing_events
WHERE event_time >= today() - 30
GROUP BY channel, campaign_id, day
ORDER BY day DESC, revenue DESC;
For keyword-level paid search analysis:
SELECT
keyword,
ad_group,
uniq(session_id) AS sessions,
countIf(event_type = 'purchase') AS conversions,
round(sum(conversion_value), 2) AS revenue,
round(
sum(conversion_value) /
uniq(session_id), 2
) AS revenue_per_session
FROM marketing_events
WHERE channel = 'paid_search'
AND keyword IS NOT NULL
AND event_time >= today() - 14
GROUP BY keyword, ad_group
HAVING sessions >= 50
ORDER BY revenue_per_session DESC
LIMIT 100;
Multi-touch attribution
Last-touch attribution is simple but misleading for channels like email or social that rarely close conversions directly. ClickHouse computes multi-touch attribution in a single query using window functions over the full session history:
WITH user_journeys AS (
SELECT
user_id,
channel,
campaign_id,
event_time,
row_number() OVER (
PARTITION BY user_id
ORDER BY event_time
) AS touch_position,
count() OVER (
PARTITION BY user_id
) AS total_touches,
max(event_time) OVER (
PARTITION BY user_id
) AS conversion_time,
argMax(conversion_value, event_time) OVER (
PARTITION BY user_id
) AS conversion_value
FROM marketing_events
WHERE user_id IN (
SELECT user_id FROM marketing_events
WHERE event_type = 'purchase'
AND event_time >= today() - 30
)
AND event_time >= today() - 30
)
SELECT
channel,
campaign_id,
-- Linear attribution: equal credit per touch
round(sum(conversion_value / total_touches), 2) AS linear_attributed_revenue,
-- First-touch attribution
round(sumIf(conversion_value, touch_position = 1), 2) AS first_touch_revenue,
-- Last-touch attribution
round(sumIf(conversion_value, touch_position = total_touches), 2) AS last_touch_revenue,
count() AS touch_count
FROM user_journeys
GROUP BY channel, campaign_id
ORDER BY linear_attributed_revenue DESC;
Conversion funnel analysis
For real-time data ingestion from ad platforms and tracking pixels, funnel analysis measures drop-off at each stage of a campaign's conversion path:
SELECT
campaign_id,
channel,
uniq(user_id) AS visitors,
uniqIf(user_id, event_type = 'lead') AS leads,
uniqIf(user_id, event_type = 'trial_start') AS trial_starts,
uniqIf(user_id, event_type = 'purchase') AS purchasers,
round(
uniqIf(user_id, event_type = 'lead') /
uniq(user_id) * 100, 1
) AS visitor_to_lead_pct,
round(
uniqIf(user_id, event_type = 'purchase') /
nullIf(uniqIf(user_id, event_type = 'lead'), 0) * 100, 1
) AS lead_to_purchase_pct
FROM marketing_events
WHERE event_time >= today() - 30
GROUP BY campaign_id, channel
HAVING visitors >= 100
ORDER BY visitors DESC;
Cohort retention
Cohort retention measures how campaigns acquire users who return, not just users who convert once. ClickHouse computes cohort retention efficiently with a self-join pattern:
SELECT
toStartOfWeek(first_event) AS acquisition_week,
channel AS acquisition_channel,
dateDiff('week', first_event, activity_week) AS weeks_since_acquisition,
uniq(user_id) AS retained_users,
cohort_size,
round(uniq(user_id) / cohort_size * 100, 1) AS retention_pct
FROM (
SELECT
user_id,
channel,
min(event_time) AS first_event,
toStartOfWeek(event_time) AS activity_week
FROM marketing_events
WHERE event_time >= today() - 90
GROUP BY user_id, channel, toStartOfWeek(event_time)
) AS activity
INNER JOIN (
SELECT
toStartOfWeek(min(event_time)) AS acquisition_week,
channel,
uniq(user_id) AS cohort_size
FROM marketing_events
GROUP BY acquisition_week, channel
) AS cohorts USING (acquisition_week, channel)
WHERE weeks_since_acquisition BETWEEN 0 AND 8
GROUP BY acquisition_week, acquisition_channel, weeks_since_acquisition, cohort_size
ORDER BY acquisition_week DESC, weeks_since_acquisition;
Pre-aggregating for dashboard speed
For dashboards that need to be fast, the materialized view pattern pre-aggregates marketing metrics as events arrive, so dashboard queries read pre-computed rollups rather than scanning the full events table:
CREATE MATERIALIZED VIEW daily_campaign_rollup_mv TO daily_campaign_rollup AS
SELECT
toStartOfDay(event_time) AS day,
channel,
campaign_id,
country,
device_type,
uniqState(user_id) AS users_state,
uniqState(session_id) AS sessions_state,
countIf(event_type = 'purchase') AS purchases,
sum(conversion_value) AS revenue
FROM marketing_events
GROUP BY day, channel, campaign_id, country, device_type;
See the ClickHouse materialized view pattern for the full AggregatingMergeTree setup and the *Merge query functions needed to read partial aggregation states correctly.
Landing page and SEO performance
For marketing teams running content programs alongside paid campaigns, measuring organic acquisition performance alongside paid channels in the same query:
SELECT
landing_page,
channel,
toStartOfWeek(event_time) AS week,
uniq(session_id) AS sessions,
uniq(user_id) AS unique_users,
countIf(event_type = 'lead') AS leads,
countIf(event_type = 'purchase') AS purchases,
round(
countIf(event_type = 'lead') /
uniq(session_id) * 100, 2
) AS landing_to_lead_pct,
round(sum(conversion_value), 2) AS revenue
FROM marketing_events
WHERE channel IN ('organic', 'paid_search', 'social')
AND landing_page IS NOT NULL
AND event_time >= today() - 30
GROUP BY landing_page, channel, week
HAVING sessions >= 100
ORDER BY week DESC, sessions DESC;
Comparing landing_to_lead_pct across channels for the same landing page reveals whether organic traffic and paid traffic convert differently on the same page. A landing page with 8% lead rate from organic and 2% from paid search points to audience mismatch in the paid targeting, not a page quality problem.
Email marketing analytics
Email campaigns generate open, click, and conversion events at high volume, often arriving in bursts when a campaign is sent. ClickHouse handles the ingestion spike and makes campaign performance queryable as events arrive:
SELECT
campaign_id,
toStartOfHour(event_time) AS hour,
countIf(event_type = 'email_send') AS sends,
countIf(event_type = 'email_open') AS opens,
countIf(event_type = 'email_click') AS clicks,
countIf(event_type = 'purchase') AS conversions,
round(
countIf(event_type = 'email_open') /
nullIf(countIf(event_type = 'email_send'), 0) * 100, 2
) AS open_rate_pct,
round(
countIf(event_type = 'email_click') /
nullIf(countIf(event_type = 'email_open'), 0) * 100, 2
) AS click_to_open_pct,
round(sum(conversion_value), 2) AS revenue
FROM marketing_events
WHERE channel = 'email'
AND event_time >= today() - 7
GROUP BY campaign_id, hour
ORDER BY hour DESC, sends DESC;
Unsubscribe rate and list health, which affects deliverability:
SELECT
toStartOfWeek(event_time) AS week,
source,
countIf(event_type = 'email_send') AS sends,
countIf(event_type = 'unsubscribe') AS unsubscribes,
countIf(event_type = 'bounce') AS bounces,
round(
countIf(event_type = 'unsubscribe') /
nullIf(countIf(event_type = 'email_send'), 0) * 100, 3
) AS unsub_rate_pct,
round(
countIf(event_type = 'bounce') /
nullIf(countIf(event_type = 'email_send'), 0) * 100, 3
) AS bounce_rate_pct
FROM marketing_events
WHERE channel = 'email'
AND event_time >= today() - 90
GROUP BY week, source
ORDER BY week DESC, unsub_rate_pct DESC;
Unsubscribe rates consistently above 0.5% or bounce rates above 2% are deliverability risk signals. Catching them in a live ClickHouse dashboard, by list segment and send source, allows suppression of problematic segments before a campaign hits inbox placement penalties.
Tinybird for marketing dashboards
Marketing dashboards built on standard analytics databases hit a predictable wall around 50-100 million events: queries slow, teams add caching layers, pre-computed reports multiply, and the dashboard becomes a view of yesterday's data rather than today's.
Tinybird is managed ClickHouse purpose-built for in-product analytics and marketing dashboard use cases. Each marketing query in this post becomes a Tinybird Pipe: a parameterized SQL endpoint your dashboard calls with date range, channel, and campaign filters. Query response time stays in the 62ms p90 range measured by Resend in production, processing 100TB per month, regardless of whether the underlying events table contains 100 million or 10 billion rows.
For real-time data visualization, Tinybird endpoints connect directly to BI tools (Grafana, Metabase, Redash) and custom frontends. Your marketing team sees campaigns update in real time as conversions arrive. The attribution model query returns in under 100ms even with window functions running across millions of users. Filtering by channel, date range, or campaign refreshes the dashboard instantly rather than triggering a multi-second backend query.
The multi-touch attribution query in this post, window functions over millions of user journeys, returns in under 100ms from a Tinybird endpoint. Your growth team calls it with a date range and channel filter from a BI tool or a custom dashboard. They get fresh attribution numbers as conversions arrive, not a pre-computed report from last night's batch job.
Ingestion connects to the ad platforms and tracking infrastructure your marketing team already uses. Google Ads, Meta, and analytics pixel events all write to Tinybird datasources via the Events API or Kafka connector. Attribution and funnel queries join across sources in SQL Pipes. Tinybird is SOC 2 Type II certified. The pipeline from event source to a live marketing dashboard takes hours to set up rather than weeks to build. Your marketing team sees campaigns in real time. Your engineers stop maintaining caching layers and pre-computation pipelines that exist only because the underlying database was too slow.
When the data keeps up with the campaign
Most marketing dashboards are built with a quiet assumption baked into the architecture: numbers are approximate and slightly behind. The dashboard shows yesterday's conversion rate, last hour's spend, last week's cohort retention. Teams learn to compensate with judgment and experience. They know the data lags.
What changes when the underlying database can aggregate millions of events in milliseconds is that dashboards can be current by default. A campaign launched 10 minutes ago shows its first click-through rates. A paused ad group shows the immediate impact on session counts. Attribution numbers update as conversions arrive. The compensation habits that marketing teams developed to work around data latency become unnecessary. The dashboard becomes a real-time control surface rather than a historical report. That is a different tool, not just a faster version of the same one.