Excel/Google Sheets

Market Analysis & Keyword Insights Part 2: Analysing SERP Data

After running through the keyword research in part 1 here, we’re now digging into the SERPs. We’ll be extracting the data, and then looking into a few of the different analyses we can do with it. Extract SERP data Format the data Top performing competitors Top-ranking pages & page types Look into the structure of …

Market Analysis & Keyword Insights Part 2: Analysing SERP Data Read More »

SERP Scraping & Keyword Clustering in Google Sheets

Ever wanted to scrape just a handful of Google SERPs inside a Google Sheet? Or wanted to group a bunch of keywords, to better de-duplicate them and isolate unique topics? Well, have I got a Google Sheet for you! My SERP Scraper & Keyword Grouping Google Sheet will do all of that for you. Check …

SERP Scraping & Keyword Clustering in Google Sheets Read More »

Market Analysis & Keyword Insights Part 1: Keyword Research

It’s time to dive into my full process of doing SEO market research & analysis. This is something I do for some of my bigger clients when they’re either kicking off their SEO and want to understand the market, or they want to understand current positioning. A market research & analysis piece is essentially keyword …

Market Analysis & Keyword Insights Part 1: Keyword Research Read More »

Dynamic Content Generation with Google Sheets

If you haven’t been following along, I’ve been documenting my journey to a 1,000+ page programmatic site in WordPress using Google sheets. The build is heavily reliant on Google Sheets to generate dynamic content based on templates. The content there is a little more advanced, as it is leveraging rules to determine which content template …

Dynamic Content Generation with Google Sheets Read More »

Extracting Keyword Templates/Patterns from a Keyword Set

When doing keyword research for programmatic sites, you shouldn’t be analysing each page individually. You especially can’t be optimising an entire programmatic build page by page. You should really be looking at the overall group, that each page falls into, and looking it as a whole. How do we do this though? How can we …

Extracting Keyword Templates/Patterns from a Keyword Set Read More »

Building Your Own CTR Model from Search Console Data

Rather than performing analysis using just the bare average rank, you can bring in a keyword’s search volume and add some weighting to the rank. To do this, you estimate SEO traffic for a keyword by multiplying its current rank, by the estimated click-through rate at that position. It’s not meant to be extremely accurate, …

Building Your Own CTR Model from Search Console Data Read More »

Bulk Keyword Categorisation & Classification

Categorising a large keyword list in bulk takes hours to do, unless you do it a special way. Learn how to categorise thousands of keywords in seconds through the use of a magical Excel formula. You can categorise data in Excel with this formula, it doesn’t only work on keywords. Another popular use of mine …

Bulk Keyword Categorisation & Classification Read More »

Scroll to Top