GA4 + GSC + DataForSEO: One Data Model

Full transcript

1,221 words · script draft, under review.

Intro

You have Search Console in one tab, Analytics in another, and a folder of results pages from DFS. Each one answers part of the question. None of them answers all of it.

So you copy numbers between spreadsheets, and the numbers don't match, and you don't know which one is wrong.

In this episode we build one data model, using one of my own sites as the example. A small set of tables with shared keys, so you can ask one question and get one answer: for this page, how visible is it, how many clicks does it get, what happens after the click, and who is it up against.

What each source knows

Start by writing down what each source actually knows, and at what level.

Search Console knows queries and pages, per day. It knows nothing after the click.

Analytics knows pages, sessions and what visitors did, per day. And it knows the hostname, which is more important than it sounds. What Analytics doesn't know is the search query. Not in its own data. So there is no way to join Analytics to Search Console on the query.

DFS knows keywords and results pages: for a keyword, on a date, in a location, which URLs rank and where.

Look at what they share. Page and date connect all three. Query connects Search Console with DFS. That's it. Those are your join keys, and everything in this episode is about making them match.

Join key 1: the URL

The URL is where most joins fail. Search Console gives you the full URL, with protocol and hostname. Analytics gives you a path, often with tracking parameters attached. DFS gives you whatever Google shows in the result.

Here's one page on my site, written five ways. The path is anonymized, but every one of these forms turns up in real exports. So you normalize every URL into one canonical form before you store it. My rules are simple.

Separate the hostname from the path, and keep the hostname in its own column. Lowercase the hostname. Remove the fragment, everything after the hash. Remove tracking parameters, like the UTM ones. And decide one rule for the trailing slash, then apply it everywhere.

Be careful with parameters that actually change the page, like a page number or a filter. Those you keep. If you strip everything, two different pages become one, and their numbers get added together.

Write this as one function, and use the same function for all three sources. If each import has its own cleaning logic, they will drift apart.

Join key 2: hostname, the filter you can't skip

Why keep the hostname in its own column? Because Analytics properties live longer than domains.

If you move a site to a new domain and keep the same Analytics property, the old domain can keep sending data into it. Often that's bot traffic, hitting pages that still carry your tag.

This happened to me, on a different site than the one in this episode. A dashboard showed over fifty thousand sessions. When I split it by hostname, about fifty-five of them came from the current domain. The rest was bot spam on a retired domain. Every chart built on that property was measuring the wrong thing.

So every Analytics query in this model filters on the current hostname. Not at the end, in a chart, but in the pull itself. And when a domain changes, check hostname against sessions before you reuse a property.

Join key 3: date and query

Date sounds trivial, but check the time zones. Search Console reports in Pacific time. Analytics reports in your property's time zone. On a daily level that can shift a few hours of traffic into the wrong day. For weekly or monthly analysis it doesn't matter. For a day-by-day comparison, it can.

Query is the key between Search Console and DFS. Store it lowercase, trimmed, with single spaces. Keep the original form too, because singular and plural are different keywords in volume data. We'll cover why in episode twenty.

The page-level table

Now the design. Keep the raw tables separate, one per source, each in its own natural shape. Then build one view on top: the page summary.

One row per page and period. From Search Console: clicks, impressions and impression-weighted position. From Analytics: sessions and conversions, filtered on hostname. From DFS: for the page's main keywords, the latest rank and how many competitors rank above it.

Here are the first two sources joined, on my own site. A note about this data: it's real, from one of my own sites, but anonymized. Domain, page URLs and search terms are replaced, to keep the focus on the principles and not on the keywords. The numbers are untouched.

Look at what one row tells you. This page had about three thousand impressions, at an average position just under ten, and zero clicks. Zero sessions. The row below it is almost the same story: three and a half thousand impressions, three clicks. Those are leads, and you got them from one query instead of three tabs.

The DFS columns join the same way, on the page and the query. I'll show them on a made-up example shop, because results-page data is paid and we cover it in its own part of the series.

Notice what's not in the view: sessions per query. You can't have that. Analytics doesn't know queries, so any number like that would be invented.

Asking the model a question

Here's why this is worth the effort. With one model, questions that used to take an afternoon become one query.

For example: which pages gained impressions over the last month, but didn't gain sessions? That combines Search Console and Analytics on the page key, for two periods. On my site, that's a list of two. One page gained about sixty percent more impressions, and its sessions went down. The other gained more than half again, and its sessions stayed exactly flat. More visible, not visited more. Both sit around position seven, on page one, so the question is the click: the title, or what else is on that results page. Either way, you know where to look.

Or: which pages rank on page two for a keyword with real volume, where a competitor sits just above? That's Search Console, DFS and volume data in one join. We'll use exactly that kind of query in the next module.

What the numbers will never match

One last thing. Search Console clicks and Analytics sessions will never be equal for the same page. Clicks are counted by Google. Sessions are counted by your tag, after consent, ad blockers, and bots. Here they are side by side for my site. Notice that Analytics shows more organic sessions than Search Console shows clicks, about one point seven per click over the four weeks. Analytics' organic channel counts every search engine, Search Console counts only Google. A steady ratio is normal. What you watch for is a ratio that suddenly changes. And here it does: for two weeks it sits around one and a half, then from the twenty-sixth of September it jumps to around two, while clicks barely move. A shift like that is usually measurement, not SEO, so that's where I'd look first.

Wrap-up

Three sources, three keys, one normalization function, and a hostname filter on every pull. The schema and the URL function are linked in the description. Next episode: setting up the whole workspace with Python, SQLite and Claude Code. See you there.