Ranked Keywords Export: What Your Competitors Rank For

Full transcript

1,061 words · script draft, under review.

Intro

You've picked your competitors. Four sites, each with a reason. Now you need to know what they actually rank for.

You can't get their Search Console. You can't scrape Google for a hundred thousand searches. And the numbers in the tool dashboards are hard to export, and harder to trust.

In this episode, we pull the ranked keywords for each competitor through the DataForSEO API, with filters that keep the cost down, and store everything so the next episode can do the comparison.

What the ranked keywords endpoint returns

DataForSEO Labs has an endpoint called ranked keywords. You give it a domain, a location and a language. It gives you back the keywords that domain ranks for, from DataForSEO's own database of search results.

That last part is important. It's not a live search. DataForSEO crawls results pages on a schedule, stores them, and this endpoint reads from that store. So it's fast and cheap, but it's a snapshot.

For each row you get the keyword and its search volume. You get the ranking URL, so you know which page ranks. You get the type of result: a normal organic listing, a featured snippet, and so on. And you get two positions.

Rank group is the position among results of the same type. Rank absolute counts every element on the page, including features. Position three in rank group can be position seven in rank absolute, if four features sit above it. For gap analysis I store both, and I sort by rank group.

Each row also carries a date for when that result was last seen. Keep it. We'll need it for the staleness check.

The call, with filters

Here's the call. It's a single POST request, set up exactly like the one in episode three. Credentials come from environment variables, never from the script.

Four parameters do most of the work. Target is the competitor's domain. Location and language must match your market. If you sell in one country, don't pull a global or a different country's data. Limit is the number of rows, up to a thousand per request. And then filters.

Filters run on DataForSEO's side, before you pay for rows. That's the cheapest cost control you have. I use three. Position: rank group thirty or better, because deeper rankings are mostly noise for a gap. Volume: at least ten a month, since below that the data reports zero anyway, as we saw in episode nineteen. And result type: organic only, for the first pass.

Then order by volume, descending, so the first page of results is the most important one. If a competitor has more rows than your limit, you page through with an offset. One request per thousand rows.

A word on the target. Use the bare domain, without "https" and without a trailing slash. Decide up front whether subdomains count. If a competitor runs its blog on a separate subdomain, that blog is part of what you compete with, so include it. And you can also target a single URL or a folder instead of a whole domain. That's handy when only one section of a big site competes with you, like a marketplace's category for your products.

Always run a dry run first. Same call, limit ten. It costs about one cent, and it tells you if the location is right, the domain is spelled right, and the filters do what you think.

What it costs

Pricing is per request plus per row. At the time of writing, roughly a cent per request and about a hundredth of a cent per row.

So let's estimate. Four competitors, five thousand rows each after filters. That's twenty thousand rows over twenty requests. About two dollars and forty cents in rows, plus about a quarter in requests. Under three dollars for the whole competitor set.

That's why the filters matter. Without the position and volume filters, one large competitor alone can return hundreds of thousands of rows. Same data, mostly useless, at fifty times the cost.

And remember the house rule from episode two: estimate first, then run. Write the estimate down before the call.

Staleness: a snapshot is not the truth

Now the pitfall. Labs data can be stale. A row says a competitor ranks fourth for a keyword. But that result might have been recorded weeks or months ago. Since then, the page may have dropped, moved, or been deleted.

So treat each row as "was seen ranking here", not "ranks here now". Look at the last seen date. For most of the export, it doesn't matter, because we're looking for patterns across thousands of keywords.

But for the few keywords that will drive a decision, like building a new page, you check the live results page. That costs a fraction of a cent per keyword, and we'll do exactly that in the next episode.

One more staleness trap: domain changes. If a competitor moved to a new domain, the old one may still show rankings in the store for a while. Check that the domain you pull is the one that actually serves the pages today.

Store it once, query it forever

Store two things. First, the raw JSON response, exactly as it came back, in your cache folder. One file per request, named by domain, location, date and offset. If you ever need a field you didn't parse, you have it, and you don't pay again.

Second, a clean table in SQLite. One row per domain and keyword, with the snapshot date, the URL, both positions, the volume, the result type and the last seen date.

Add one extra column now: a normalized keyword. Lowercase, trimmed, extra spaces removed. We'll use it in the next episode to compare keyword lists properly.

And here's a step people skip: run the same export for your own domain. Same location, same filters. You also have Search Console, and that's better data for your own site. But for the comparison you want both sides from the same source, measured the same way. Otherwise differences in the data look like gaps.

Keep the snapshot date on every row. When you rerun in three months, you can compare snapshots, and see which competitor is growing in which topic.

Wrap-up

One filtered export per competitor, plus your own, stored with dates. The export script and the SQLite schema are linked in the description. Next episode: the keyword gap, what your competitors have that you don't, and how to check it before you trust it. See you there.