)
8 min.
How to Master Ahrefs' SERP Feature Export (Without Losing Your Mind)
If you’re optimizing for modern SEO, you can't ignore what actually shows up on the SERP — not just links, but videos, knowledge panels, image packs, People Also Ask, shopping ads, and more.
So yeah, we really need to know this. But that is easier said than done.
In this post, I'll show you a dead-simple way to clean and analyze Ahrefs' SERP-feature export — using just Google Sheets. You'll be able to see exactly which SERP features dominate your market — and how often.
The problem #
Ahrefs does include SERP features in its export, but dumps them into messy, comma-separated strings — one per keyword. Not exactly analysis-ready. Something like this:
Nice to know, but not structured, not usable.
I can help you though!
Because to make it useful, you need to tear all these results apart. And there are some other things to account for too:
-
-
we only need to know which ones are used in our search market
-
-
it's probably useful to deduplicate the list too ;)
-
-
the data needs to be cleaned: excess comma's, whitespaces, empty values etc. need to be filtered
-
-
we need to make sure that Google Sheets doesn't break when using a large dataset (for example:
=TEXTJOIN()has a limit of 50.000 characters)
The formula #
So let's say that the 'SERP features' column in your Google Sheets is in column C and the values start at row 2. With this formula you'll turn chaos into structure. Just copypaste it. You might need to change the cell references, but i'm sure you'll manage:
=UNIQUE(FILTER(TRIM(SPLIT(FLATTEN(SPLIT(C2:C, ",")), ",")), TRIM(SPLIT(FLATTEN(SPLIT(C2:C, ",")), ",")) <> ""))What this does:
-
1.
SPLIT(C2:C, ",")breaks up each row of comma-separated features. -
2.
FLATTEN(...)turns the result into a single vertical list of all features. -
3.
TRIM(...)removes stray whitespace (e.g., " local pack" becomes "local pack"). -
4.
FILTER(... <> "")removes any empty strings (like those caused by trailing commas). -
5.
UNIQUE(...)gives you a deduplicated list of all features across the dataset.
Using it would turn that huge column of comma-separated texts in cells into a single neat, deduplicated column with only the unique SERP-components present in the whole dataset:
A nice, usable, deduplicated list of the unique SERP-components in your export
How to use it #
So let's say that for each SERP component, you want to attach it to a keyword (and you're clustering your keywords into topics, right? RIGHT?!?), so you can later use it in a pivot table. Then you simply =TRANSPOSE() them:
=TRANSPOSE(UNIQUE(FILTER(TRIM(SPLIT(FLATTEN(SPLIT(C2:C, ",")), ",")), TRIM(SPLIT(FLATTEN(SPLIT(C2:C, ",")), ",")) <> "")))And then in the row below it, you can use =REGEXMATCH() to return TRUE if there is indeed a match. Please note that in my example, the first SERP-component horizontal row is column O (you may need to change that for your specific sheet setup):
=ARRAYFORMULA(IF(ISBLANK($C2:$C),"",IF(REGEXMATCH($C2:$C,O$4),TRUE,"")))And in the row above it, use a =SUMIF() to sum up the occurences (cell B3 holds the SUM of all CPC values per keyword, which I use to gauge the value of these components ;)):
=SUMIF(O5:O,TRUE,$B5:$B)/$B3That would lead to something like this:
For every keyword (not visible in the image; they are to the left in my sheet) I now can see which SERP-component is visible for it. And above the name of the column I've added a simple =SUMIF() to show the total CPC-value of these components, expressed in a percentage.
Conclusion #
Once you've got this in place, you'll know exactly which SERP features are dominating your space — and where your content needs to compete. You can even sort them by value using CPC, click-through potential, or visibility rate — all from inside your spreadsheet.