---
title: "ClickHouse marketing dashboards"
excerpt: "Marketing dashboards fail at scale for a predictable reason: they are built on databases designed for transactions, not for aggregating millions of events per campaign across dozens of attribution dimensions. ClickHouse solves the query performance problem. This post covers the schemas, attribution queries, funnel analysis, cohort retention, and pre-aggregation patterns that make marketing dashboards fast at any event volume."
authors: "Tinybird"
categories: "AI Resources"
createdOn: "2026-06-25 00:00:00"
publishedOn: "2026-06-25 00:00:00"
updatedOn: "2026-06-25 00:00:00"
status: "published"
---

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:

```sql
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:

```sql
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:

```sql
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:

```sql
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](https://www.tinybird.co/blog/real-time-data-ingestion) from ad platforms and tracking pixels, funnel analysis measures drop-off at each stage of a campaign's conversion path:

```sql
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:

```sql
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](https://www.tinybird.co/blog/7-tips-to-make-your-dashboards-faster), 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:

```sql
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](https://www.tinybird.co/blog/clickhouse-create-materialized-view-example) 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:

```sql
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:

```sql
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:

```sql
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](https://www.tinybird.co/blog/inproduct-dashboards) 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](https://www.tinybird.co/blog/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.

{% cta
  title="Marketing analytics that stays fast as your data grows"
  text="Tinybird is managed ClickHouse with streaming ingestion and SQL endpoints. Sub-second marketing dashboards at any event volume."
  button={href: "https://cloud.tinybird.co/signup", target: "_blank", text: "Try Tinybird free"}
/%}