The Search Console UI is fine for one URL at a time. It's miserable for an entire site at once.
What you actually want to know on most audits is not "how did the homepage do" - it's "which URLs gained the most clicks quarter-over-quarter, and which ones lost the most?" That's the question that drives every "where do we focus next?" conversation, and the GSC UI does not answer it well.
The fix is a 100-line pandas script that takes a single CSV export and turns it into the right answer.
The shape
Input: a CSV from GSC with one row per URL per day - the schema you get from a "Pages" export with the date dimension enabled. Columns: Date, Url, Clicks, Impressions.
Output: a CSV with one row per URL and these columns:
Clicks_90d,Impressions_90d(most recent 90 days)Clicks_prev90d,Impressions_prev90d(the prior 90 days)Clicks_delta,Impressions_delta(absolute change)Clicks_pct_change,Impressions_pct_change(percent change, with division-by-zero handling)
Sort that output by Clicks_delta descending and the top of the file is your biggest gainers. Sort ascending and the top is your biggest losers. Pivot it any direction and you have the data to argue any "where do we focus" debate.
The math is the easy part
A typical run looks like:
def safe_pct_change(curr, prev):
if prev == 0:
return None if curr == 0 else float("inf")
return (curr - prev) / prev
# Window boundaries
end_date = df["Date"].max().normalize()
curr_start = end_date - timedelta(days=CURRENT_DAYS - 1)
prev_end = curr_start - timedelta(days=1)
prev_start = prev_end - timedelta(days=PREV_DAYS - 1)
# Group by URL within each window, sum the metrics
g_curr = df[mask_curr].groupby("Url")[["Clicks", "Impressions"]].sum()
g_prev = df[mask_prev].groupby("Url")[["Clicks", "Impressions"]].sum()
# Outer-join so URLs that exist in only one window are still represented
out = g_curr.merge(g_prev, on="Url", how="outer").fillna(0)
The interesting parts are not the math - they're the edge cases.
Edge case 1: URLs that didn't exist in the prior period
A new URL that ranked great in the current 90 days has zero clicks in the prior 90. Naive percent-change math divides by zero and explodes. The safe_pct_change helper handles this by returning inf when going from zero to a positive number (which is technically correct - an infinite percentage increase) and None when both periods are zero (no change in either direction).
The downstream Excel pivot needs to know how to handle inf and None - some clients see #NUM! or #DIV/0! and panic. The right move is to filter those out in the report and present them in a separate section called "New URLs this period" with absolute numbers, not percentages.
Edge case 2: URLs that existed only in the prior period
A URL that ranked in the prior 90 days but got zero clicks in the current 90 is either:
- Deindexed (the page is gone or noindexed now)
- Lost ranking (still indexed, no longer ranked for anything substantial)
- Renamed/redirected (the URL changed and the new one is in a different row)
The outer join keeps these rows in the output with Clicks_90d = 0. Sort by Clicks_delta ascending and they pop to the top of the loser list. Whether they're "real losses" depends on whether the URL still exists. The script doesn't crawl the URL to check - that's a future enhancement - but the operator can do a quick spot-check of the top 20 losers to separate "actually broken" from "got renamed."
Edge case 3: weekend effects
A 90-day window that ends on a Sunday vs one that ends on a Friday can look meaningfully different because of weekend traffic patterns. The script anchors both windows to the max date in the data, so they're always the same number of weekend days in each window. This is the kind of detail that's invisible until somebody asks "why did the numbers shift overnight?" and the answer is "we ran the report on a different day of the week."
Why this is a pandas script and not a SQL query
The same analysis could be a 30-line SQL query if your data lived in a database. For my workflow, the data lives in CSV exports from GSC because that's the lowest-friction way to get it out. The export-to-Pandas path is the natural shape; spinning up a database would be three more steps that don't add value.
If this analysis was running daily across dozens of sites, the math should move to SQL and the CSV middleware should disappear. For ad hoc audits and quarterly review work, pandas-on-CSV is the right scale.
What the output actually drives
The report's most valuable output is the top movers in both directions. Not "the site grew 12% overall" - that's the kind of headline number that hides the underlying mix. The actually-useful conversation is:
- "These 20 URLs gained 70% of the total click growth. Why? What's the pattern?"
- "These 20 URLs lost 60% of the click decline. What changed? Did we redirect them? Did Google?"
- "These 50 URLs are net flat but had huge swings in impressions. Did intent change? Are we getting new visibility but no clicks?"
That's the discussion the script makes possible. Without it, the data is too big to look at, the GSC UI is too narrow to compare across, and the audit defaults to "well, traffic was up - let's call it a win." That's the kind of vague conclusion that wastes the next quarter.
What I would change
A few enhancements worth doing on the next iteration:
Page-type segmentation. Right now the analysis is flat - all URLs equal. Adding a column for "page type" (product / blog / category / location / etc.) based on URL patterns would let the report compare apples to apples. "Our blog grew 30% but products are flat" is much more actionable than the aggregate.
Query attribution alongside URL. The current script analyzes URLs only. Adding a parallel run for queries (or query+URL pairs) would surface "this URL gained clicks because it started ranking for these new queries" - which is the next-step analysis after the URL-level one.
Automated win/loss commentary. Once you have the top movers, the next layer is "what changed about each one?" An LLM commentary pass on the top 20 winners and top 20 losers - given page content snapshots, query trends, and SERP changes - would generate a draft narrative for the quarterly report. Same rule as elsewhere: AI writes the commentary, not the numbers.
But the core - "give me a CSV of the URLs that moved the most, in both directions" - is the report I run on every audit. The 100 lines that produce it have paid for themselves on every engagement.
Source: github.com/schandler7171/portfolio-example-scripts/tree/main/Pulling-GSC-API