Building a CTR Curve From Your Own Search Console Data

Every few months someone publishes a table of click-through rate by position, and it gets pasted into forecasting spreadsheets across the industry. Position 1 gets some number in the twenties or thirties, position 10 gets something near 2%, and a revenue projection gets built on top of it.

Those tables are averages across wildly heterogeneous query sets, measured on SERPs that don't look like the SERPs your queries return. Your own Search Console data can produce a far better baseline — but only if you understand the two things that break a naive fit: the way average position is computed, and the fact that CTR is not a function of position alone.

Why published CTR curves don't fit your site

A CTR curve is a conditional expectation: given that my result appeared at position p, what fraction of impressions became clicks? The conditioning set matters enormously.

The brand mix alone destroys comparability. A navigational query where your brand is the intent will take a very high share of clicks at position 1. An informational query where Google renders an AI Overview, a People Also Ask block, and a video carousel above your result will not, even though Search Console may report the same position number for both. Google has said that links surfaced in AI Overviews are counted within overall Search performance metrics rather than broken out separately, so an "organic position 1" in your export can sit under a large generated answer that absorbs the click.

Position in Search Console is an ordinal rank among search results, not a pixel offset. Two results at position 3 can be two screens apart depending on what features render above them. Any curve that ignores this is averaging over a variable that matters more than position does.

Average position hides the distribution that drives clicks

This is the subtle one. Search Console's average position is impression-weighted: for each impression, it records the topmost position your property ranked at, and averages over impressions. Google documents this, and it also documents that when you aggregate over a long window you get one number standing in for a distribution.

CTR as a function of position is convex and decreasing — the drop from 1 to 2 is much larger than from 8 to 9. Jensen's inequality then tells you something concrete: the average of CTR over a distribution of positions is greater than CTR evaluated at the average position. A query that spends half its impressions at position 1 and half at position 9 reports an average position of 5 and earns the CTR of a position-1 query on half its impressions. In a naive fit, it lands in the position-5 bucket as a wild overperformer.

An illustration of the same effect across two queries with identical reported position:

The fix is granularity. Fit on the smallest rows you can get — daily, per query, per URL, per country, per device — so each observation covers a narrow position range. Fitting on a 90-day aggregate export is where most homemade curves go wrong.

Segment before you fit, or you average away the signal

Before fitting anything, split the data on the dimensions that shift the whole curve:

• Brand vs. non-brand. Brand queries belong in their own curve or out of the dataset entirely. A regex on your brand tokens and common misspellings is enough.
• Device. Mobile SERPs push organic results further down and have different interaction patterns. Fit desktop and mobile separately.
• Country and language. Different SERP feature density, different competitive sets.
• Search type. Web, image, video, and news behave differently. Start with WEB only.
• Search appearance. The BigQuery bulk export carries boolean flags for various result appearances (merchant listings, review snippets, video, and others). Rows with rich result flags often have measurably different CTR at the same position; keeping them mixed in blurs the curve.
• Query intent or template. If you run a programmatic section, its pages have a different curve than your editorial content. Fit per template when volume allows.

A minimal fit from the bulk export

The Search Console bulk data export gives you daily rows without the UI's row limits. Average position per row is sum_top_position / impressions + 1 — that formula is documented, and the + 1 catches people out because the stored field is zero-based.

SELECT
  ROUND(SAFE_DIVIDE(sum_top_position, impressions) + 1) AS pos_bucket,
  SUM(clicks) AS clicks,
  SUM(impressions) AS impressions,
  SAFE_DIVIDE(SUM(clicks), SUM(impressions)) AS ctr_weighted,
  APPROX_QUANTILES(SAFE_DIVIDE(clicks, impressions), 100)[OFFSET(50)] AS ctr_median
FROM `project.searchconsole.searchdata_url_impression`
WHERE data_date BETWEEN '2024-01-01' AND '2024-03-31'
  AND search_type = 'WEB'
  AND is_anonymized_query = FALSE
  AND device = 'MOBILE'
  AND country = 'usa'
  AND NOT REGEXP_CONTAINS(query, r'(?i)yourbrand|your brand|yourbrnd')
  AND impressions >= 10
GROUP BY pos_bucket
HAVING SUM(impressions) > 1000
ORDER BY pos_bucket

Report both the impression-weighted CTR and the median row CTR. The weighted number is dominated by your few highest-volume queries; the median treats every query-day equally. If they diverge sharply in a bucket, your head terms behave differently from your tail, which is itself worth knowing.

One caveat that applies to every query-level analysis: Google filters rare queries out of the query dimension for privacy, flagged as anonymized in the export. Your query-level curve therefore describes the non-anonymized portion of your traffic, which skews toward higher-volume queries.

Reading deviations without chasing noise

Once you have a curve, the tempting use is to rank every query by CTR minus expected CTR and call the bottom of the list a title-tag backlog. Most of that list is noise and volatility.

Two filters remove most of the false positives. First, require enough impressions that the confidence interval on observed CTR is narrower than the gap you're claiming — a query with 40 impressions tells you almost nothing. Second, require the deviation to persist across two non-overlapping time windows. Anything that fails the second test was probably regression to the mean waiting to happen.

Also check the position variance of flagged queries before acting. High-variance queries will look like overperformers for the reason described above, and low-variance queries sitting just below a feature block will look like underperformers no matter what you write in the title.

What to do with this

1. Pull 90 days of daily query-URL rows from the bulk export, or the API if you don't have the export set up, and keep the daily granularity.
2. Fit separate curves for brand and non-brand, desktop and mobile, and your largest country. Store them as a table keyed on those dimensions.
3. Compute both weighted and median CTR per position bucket, and keep the impression counts so you can tell which buckets are thin.
4. Use the curve for relative prioritization — which pages deviate, which templates underperform their peers — not for absolute traffic forecasts. A curve fitted on your current SERP feature mix stops describing reality when that mix changes.
5. Refit quarterly and diff the curves. A shift in the shape of the curve across a section of your site is itself a signal that the SERPs those queries return have changed.