How to calculate Email Click-Through Rate

Formula

Email CTR = unique recipients who clicked ÷ emails delivered

SQL query

This example assumes: email_sends(id, campaign_id bigint, delivered_at timestamptz, first_click_at timestamptz). It is written in PostgreSQL syntax; date functions differ slightly in MySQL, BigQuery and SQLite, and your column names will differ from these.

SELECT campaign_id,
       COUNT(delivered_at) AS delivered,
       COUNT(first_click_at) AS clicked,
       COUNT(first_click_at)::numeric / NULLIF(COUNT(delivered_at), 0) AS click_through_rate
FROM email_sends
GROUP BY campaign_id
ORDER BY click_through_rate DESC NULLS LAST;

Common mistakes that make Email Click-Through Rate wrong

• Security scanners click links automatically and can inflate clicks; filter bot clicks if your tool flags them.
• Click-through (clicks ÷ delivered) and click-to-open (clicks ÷ opens) are different numbers.
• Use delivered, not sent, as the denominator, or bounces will drag the rate down.

Ask for Email Click-Through Rate in plain English

Instead of writing the query yourself, you can type the question. SchemaChat turns it into SQL against your connected database or uploaded file, runs it, and shows the SQL and the figures it used so you can check the answer before you share it. It does not replace your judgement about the definition: check that the query matches the formula your business uses.

• What is the click-through rate for each email campaign?
• Which campaign had the best click-through rate last month?