What is click-through rate, and how do you measure it in Metabase?
Click-through rate is clicks divided by the opportunity to click — ad impressions in Google Ads or Meta Ads, search impressions in Google Search Console, delivered emails in Mailchimp or SendGrid. It's a health signal for creative and relevance, not a business outcome — and the denominator changes with every context.
TL;DR — Ads:
clicks / impressions. Email: unique_clicks / delivered. Search: clicks / search impressions, read alongside position. Never blend the three, and never average pre-computed rates — re-aggregate from raw counts.What CTR measures
Whether the message earned the click it asked for. In paid channels it's a creative and targeting signal — a falling CTR usually means ad fatigue or a mismatched audience, and it drags quality scores and cost per click with it. In organic search it measures how compelling your title and snippet are for the position you hold. In email — now that Apple Mail Privacy Protection has inflated open rates past usefulness — CTR is the engagement headline.
What it doesn't measure is value. A high CTR on traffic that never converts is an expensive compliment. Read it upstream of landing-page conversion rate and ROAS, not instead of them.
What data does it need?
- For ads: a daily performance table (
ad_performance_daily) withimpressionsandclicksby campaign, ad group, and placement. - For email: campaign stats with
deliveredandunique_clicks— unique, not total, so one enthusiastic reader doesn't inflate the rate. - For search: Search Console performance rows with clicks, impressions, and average position by query and page.
SQL patterns
Ad CTR by campaign by weekPostgreSQL
SELECT
date_trunc('week', stat_date) AS week,
campaign_name,
SUM(clicks) AS clicks,
SUM(impressions) AS impressions,
ROUND(100.0 * SUM(clicks) / NULLIF(SUM(impressions), 0), 2) AS ctr_pct
FROM ad_performance_daily
WHERE stat_date >= CURRENT_DATE - INTERVAL '90 days'
GROUP BY 1, 2
ORDER BY 1, 2;Email CTR by campaign (unique clicks over delivered)PostgreSQL
SELECT
campaign_name,
send_date,
SUM(delivered) AS delivered,
SUM(unique_clicks) AS unique_clicks,
ROUND(100.0 * SUM(unique_clicks) / NULLIF(SUM(delivered), 0), 2)
AS email_ctr_pct
FROM email_campaign_stats
WHERE send_date >= CURRENT_DATE - INTERVAL '90 days'
GROUP BY 1, 2
ORDER BY send_date DESC;Pitfalls
Comparing CTR across contexts.→ A 2% search-ad CTR and a 2% email CTR share nothing but a name — the denominators are different universes. Benchmark each channel against its own history, never against another channel.
Using email open rate as the engagement signal.→ Mail Privacy Protection preloads pixels and fires opens no human saw. Opens still work for rough deliverability triage; for engagement, lead with clicks over delivered.
Judging search CTR without position.→ CTR is position-dependent and falls off a cliff below position 3. A "bad" CTR at position 9 is expected; the same CTR at position 2 is a title-tag problem. Always pair CTR with average position.
Averaging CTR across placements.→
AVG(ctr) over rows weights a 100-impression placement the same as a million-impression one. Sum clicks and impressions first, then divide — one campaign at scale can move the real number while the naive average sits still.Where this metric applies
- Google Ads + Metabase — clicks and impressions by campaign, ad group, and keyword
- Meta Ads + Metabase — CTR by campaign, ad set, and creative
- Google Search Console + Metabase — organic CTR by query and page, with position
- Mailchimp + Metabase — unique clicks over delivered by campaign
- SendGrid + Metabase — click events from the event webhook
Related
Metrics
Dashboards
FAQ
How do you calculate CTR?
Clicks divided by the opportunity to click — and the opportunity changes by context. For ads it's
clicks / impressions from platforms like Google Ads or Meta Ads. For organic search it's clicks over search impressions from Google Search Console. For email it's unique_clicks / delivered. Same name, three different metrics — never blend them into one number.What's a good CTR?
There is no universal benchmark — it depends entirely on channel, placement, and position. Search ads on branded terms can clear 10%; display ads live below 1%; email CTR of 2–3% on delivered is respectable. The useful comparison is your own trend: the same campaign type, same placement, over time. Pin CTR next to cost per click and spend on a paid channel performance dashboard so a "good" CTR that costs too much doesn't hide.
Why is email CTR more useful than open rate now?
Apple Mail Privacy Protection preloads tracking pixels, which registers opens that never happened — open rates have been inflated and unreliable since 2021. Clicks still require a human decision, so click-through rate on delivered is the engagement headline for email. Sync campaign stats from Mailchimp or event data from SendGrid and chart unique clicks over delivered on an email engagement dashboard.
Does CTR depend on search position?
Heavily. Position 1 in organic search captures a large share of clicks, and CTR falls off a cliff below position 3 — a page at position 8 with a 1% CTR may be performing exactly as expected. Always read search CTR next to average position from Google Search Console, and compare pages within the same position band, not across the whole property. See organic clicks & impressions for the full search-side picture.
How do you track CTR in Metabase?
Sync daily performance rows from your ad platforms, Search Console, and email tool into a SQL database, then compute clicks over the context-appropriate denominator with
SUM(clicks) / NULLIF(SUM(impressions), 0) — always re-aggregate from raw counts rather than averaging pre-computed rates. Chart each channel on its own card and keep the trend next to landing-page conversion rate so you can see whether clicks actually convert.