I was recently lucky enough to be asked to speak at BrightonSEO’s fringe event MeasureFest this past September where I manifested my recent blog post on Fantasy Football stats for SEO reporting into something hopefully a little actionable.
The talk, which was both in-person and online (indeed the first in-person BrightonSEO event since before covid times) was great fun to do and made the countless arbitrary hours spent extracting silly football stats actually somewhat worthwhile.
Even prior to covid, it had also been a long time for me personally since I’d last gotten up on stage and delivered an SEO talk. Thank you to everyone who attended both virtually and in person.
As penance to you all, I thought I’d do a mini-write up of the talk where you can access all the resources, Excel formulas and reporting templates I whizzed through in the session.
Firstly, here’s the video recording of the talk which went out during the virtual version of the conference:
And here’s the SlideShare link:
You’ll find a fresh version of the reporting template sheets where you can input and mess them with as you see fit here. The talk itself offers suggestions for how to use these sheets for SEO reporting purposes, and you can find some further help on this in the original blog post I wrote prior to the talk.
Resources Used
Finally, here’s a list of some of the resources I detailed in the talk which I hope will inform part of this particular SEO reporting journey or at least offer new ways of thinking in the world of SEO reporting and data pivoting in general.
- SEOTools for Excel. It’s been around forever though it’s got a lot better in recent years. Use a variety of APIs and data connectors from your everyday SEO toolset to plug everything in one place and combine accordingly.
- OFFSET formula. This has been in the Excel arsenal for some time though I’d not used it for a while. It came in handy when I was extracting data over two compared time periods from Google Analytics and I wanted to segment this data side by side.
For example, a raw export in Google Analytics across two different date periods looks something like this:
Using the correct OFFSET formula, you’ll end up with something like this:
I got reminded of this handy formula via this article detailing a wider list of very useful Excel formulas for SEO.
- Extracting data from subfolders. This was detailed during the talk when I wanted to match data against particular subfolders across a website:
To do this, I utilsied the below formula:
=SUBSTITUTE(TRIM(LEFT(RIGHT(SUBSTITUTE("/"&IF(RIGHT(A2,1)="/",LEFT(A2,LEN(A2)-1),A2),"/",REPT(" ",99)),2*99),999))," ","/")
This was inspired by an even more brilliant discovery on Excel formulas for Excel via the guys over at Builtvisible in this article.
- IF/ISNA/VLOOKUP. This was used when wanting to cross reference data that had been extracted into multiple reporting sheets or templates and we wanted to match data accordingly.
The question I posed here was:
“Do the reporting templates contain the same domain/URL that I can cross reference in one combined sheet?”
And the formula:
=IF(ISNA(VLOOKUP(A2,Sheet1!A:F,6,FALSE)),”NotFound”, VLOOKUP(Sheet2!A2,Sheet1!A:F,6,FALSE))
And here’s ideally what we ended up with, with the second column confirming whether the data was in another template and allowing us to compare other data points as such:
- SEO Dashboard in Excel. To help illustrate the overarching point of using some degree of manual reporting (with Excel) for SEO in 2021, I pulled in a brilliant example repoting template used by someone called Zach Olsen. Go check it out.