Category: Data Analysis

Analysing Brand vs Generic Keyword Performance

Analysing Brand vs Generic Keyword Performance

Breaking down your GSC into brand vs generic keyword performance can give you a better insight into how an SEO campaign is going.

Most SEO activity won’t be affecting the brand too much, as it will be more to drive performance increased within the generic keywords.

Whilst you’ll get a flow-on effect, in that as generic performance improves, more traffic will then drive brand marketing essentially, increasing return visits via branded search.

That takes time though.

You’ll also want to monitor rankings separately, and rankings for brand-related keywords will bring down(up?) the average, and hide some of the generic keyword performance.

My first analysis like this many years ago took quite a while to figure out.

Now, they’re like 30 minutes.

Definitely a worthwhile analysis, considering how short they are.

What are brand keywords?

Brand keywords are any keywords that include your brand name.

This could include both brand + generic phrases, as long as there is a mention of your direct brand in the keyword it’s a branded keyword.

It also includes anything that should include the brand, and by that I mean misspellings. Whilst you might think there are 3 ways to spell your brand, I can assure you the internet will come up with 50 more.

 

What are generic keywords?

A generic keyword is any keyword that does not include a mention of the brand.

It’s basically the “everything else” bucket for this analysis.

There could be keywords mentioning other competitors, or other brand names from what you sell, but these will still be classed as generic (although, you could further categories these later to get additional insight).

 

Why analyse brand vs generic keyword performance?

There are two main reasons I like to do these analyses.

Brand vs Generic keyword split

Initially, they’re great to set the default pre-SEO brand vs generic split percent.

As your generic SEO activity increases, the assumption here is that the generic keywords will improve performance more than the brand. You would then see an increase in the overall % of the generic keywords.

You can then trend this monthly, and just add something else to the list to help show any performance increases.

Exclude branded keywords from GSC data

The other reason is to essentially just exclude brand performance from GSC data.

Sometimes brand performance, particularly spikes from campaigns, can mess with the GSC data.

Excluding these branded keywords can help you get a more accurate overview of ranking performance for the keywords you’re targeting.

 

How to do a Brand vs Generic keyword analysis

1. Use your favourite GSC data exporter to get a dataset of Query, Clicks, and Impressions. I personally use Search Analytics for Sheets, and would only select the latest 3 months of data, or even just the latest month if the sites big enough, to ensure that the analysis is with recent data. You could rerun with older data to compare.

2. Copy my Google sheet from the below link

3. Import your data into the rows highlighted below. If you have included CTR & Av rank then just ignore them, but most exporters by default will include that data so we may aswell save it for later.

4. Enter variations of you’re brand terms into the K column, and then mark them as ‘Branded’. These should be the shortest variations you can come up with, that still ensure generic keywords don’t get included. Leave the cells below it, as the formula will just fall back to ‘Generic’ when a brand seed word isn’t found. You should only really be adding 5-10 to start, and can come back and refine it to more later on.

 

5. (OPTIONAL) If you’d like to get deeper insight, add some categories & sub-categories into their columns. This will help you break the performance down by category, if that’s something you think would be useful for your brand.

6. Visit the ‘analysis’ tab to have a brand vs generic performance overview pivot table ready for you

One caveat to this is that due to exporting query performance data, you have a limited click count. Large portions of longer tail keywords are excluded from query exports, so whilst this % is a good indication, it should be used as only that and not a source of “truth”.

You will be able to see this shift over time though, as both branded and generic keywords will get excluded.

You can read more about that here under the ‘queries’ expander.

Monthly Brand vs Generic Keyword Analysis

This can be extended to include monthly data, and I might extend the Google Sheet later if I get a few asking for it.

Just throw on a month row, ensure your data exports are monthly, and then just add them all together in the same sheet with each different set having its associated month added.

 

Just another insight you can provide

Overall, this is just another little insight you can provide your clients, and hopefully, another metric you can just keep an eye on.

 

Bulk Keyword Categorisation & Classification

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 anything you want with this formula, it doesn’t only work on keywords. Another popular use of mine is to categorise landing pages, which can significantly help with showing how ranking performance has improved certain landing page’s traffic.

This is an older video, of doing categorisation in Excel. The overall principles are exactly the same, however this will not work in Google Sheets.

But, I have a solution for that. A different formula that works in Google Sheets.

Categorising & Classifying Keywords in Excel

To categorise and classify your keywords in Excel, just like in the video, you can download my Excel template below.

Download the Keyword Categorisation Template
  You will receive a download link via email. We hate spam and never share your details.

Categorising & Classifying Keywords in Google Sheets

If you would rather use Google sheets, my preferred method these days, then you can copy my categorisation sheet below.

You will see a very similar template to what is shown in Excel in the video above, except with a slight tweak to the formula.

The formula I use for this is;

=IFERROR(ARRAYFORMULA(INDEX(*categoryRange*, MATCH( TRUE,ISNUMBER(SEARCH(*findSeedWordRange*,” “&*keyword*&” “)),0))), “Unclassified”)

(might need to change the quotations due to formatting issues)

You’ll see one major difference with the Google sheet though, and that comes down to the cell ranges used for the find/category keywords.

If a cell range in the formula is larger than what’s available, when no category is found it will leave the cell blank.

To use the ‘unclassified’ text, the range needs to be exactly the same as what’s available. This means every time you add keywords into the find column, you’ll need to expand that range.

I tend to use just the blanks as the other/generic/unclassified as it tends to make life a bit easier.

Categorising URLs into Site Sections

You can also use this directly on URLs, to categorise them into site sections, locations, or anything you’d like.

 

Let me know how you use this sort of sheet, or whether you do this all a different way.

 

Estimating SEO Traffic for a Keyword Set

Estimating SEO Traffic for a Keyword Set

Estimating your organic traffic will allow you better assess your current performance for a set of keywords, but also allows you to directly compare that performance with your competitors.

In this video, I run through how to properly set up your data and run the formula across a set of categorised keywords. This will be the basis of future videos, on how we can better use this data.

The estimated traffic is being worked out by multiplying the search volume of the keywords, with the expected CTR at their current rankings.

The formula I use for this in Excel is;

=IFERROR([@[Search Volume]]*(IFERROR(LOOKUP(9.99999999999999E+307,SEARCH(CTRModel16[Position],[@Rank]),CTRModel16[CTR]),”N/A”)),0)

Which is templated as;

=IFERROR([*CELL FOR SEARCH VOLUME*]*(IFERROR(LOOKUP(9.99999999999999E+307,SEARCH(*CTR MODEL TABLE RANK COLUMN*,*CURRENT RANK*),*CTR MODEL TABLE CTR AT RANK COLUMN*),”N/A”)),0)

Removing the bulk of it, we essentially have the search volume, along with a lookup for the CTR at that rank. The lookup could be simplified with a vlookup if you want to, however, I choose to just use my categorisation formula for this.

This can be simplified to just a vlookup for the rank table, especially if you’re using whole rankings rather than GSC average rankings that have a decimal place.

Estimating SEO Traffic in Google Sheets

You can duplicate a Google sheet below which is 95% the same as the Excel above to get you started.

Let me know if you have any issues, but it should just be plug and play with your keywords, rankings data, and any associated categories.

Top Ranking Domains Analysis for a Keyword Set

Top Ranking Domains Analysis for a Keyword Set

Ever wanted to see the top-ranking domains / URLs for a particular keyword set?

This analysis will let you do exactly that, and give you more refined performance data, allowing you to dig into the true top performers.

There are a few tools out there that let you see the top competitors and their estimated traffic.

However, many of these tools fail to give good insight into category-specific performance. This holds you back from being able to extra true competitive performance insights, as you’re only seeing the wider picture.

You’re missing out on the drilled-in numbers

Watching the video above, you’ll learn how to build out your own analysis, using a combination of Excel & SerpRobot.

 

Steps to extract the top-ranking domains

  1. Export SERPs data from SerpRobot
  2. Format the data appropriately in Excel
  3. Extract the domains from the ranking URLs
  4. Run estimated traffic formula based on ranking & search volume
  5. Categorise the keywords into required categories
  6. Pivot the data based on the domains, with slicers/filters for categories

 

And there you will have it. Your own analysis, with estimated traffic figures at the category level, so that you can get the direct competitor insights you’re after.

Categorise GSC Data into Site Sections in Data Studio

Categorise GSC Data into Site Sections in Data Studio

I’ve finally started playing in Google Data Studio, and obviously kicking off by messing around with my Search Console data.

One of the initial things I wanted to do was categorise the landing page data into site sections, like I normally do in Google Sheets.

Thought you would need to create a reference table of all the URLs and their site sections, but then came across this post that gave some more advanced methods of categorising on the fly directly in data studio.

 

Creating a custom dimension in Google Data Studio

The first thing you need to do is create a custom dimension in Google data studio.

Click the “add a field” button in the bottom right of the data editing sidebar.

You’ll be presented with a popup which is where you can paste your site section formula.

Site Section categorisation formula

The site section formula you want to use, will be a modified version of the below formula.

case
when regexp_contains(Landing Page, “/laptops/|/other-folder-as-example/”) then “Category 1”
when regexp_contains(Landing Page, “/second-category/”) then “Category 2”
when regexp_contains(Landing Page, “/third/”) then “Category 3”
when regexp_contains(Landing Page, “/another/|/also-this/”) then “Category 4”
else “Unclassified”
end

This formula will categorise the site sections based on the text you have that is in the quotes, next to ‘Landing Page’.

You can add multiple texts for each site section by using a pipe | and that will act as an OR. So /folder-1/ OR /folder-2/ basically.

Then just change the category names to your site sections.

Extend or shorted by just adding new lines in between the ‘case’ and the ‘else’.

You will need to keep unclassified at the end, or rename it to something else, to ensure there is a fallback.

When the URL includes this and this (so includes multiple elements)

If you’d like to categorise into site sections based on requiring 2 elements, ie maybe a subdomain and a specific subfolder, then you will need the following formula instead.

case
when regexp_contains(Landing Page, “/laptops/|/other-folder-as-example/”) then “Category 1”
when regexp_contains(Landing Page, “/second-category/”) then “Category 2”
when regexp_contains(Landing Page, “/third/”) then “Category 3”
when regexp_contains(Landing Page, “/another/|/also-this/”) AND regexp_contains(Landing Page, “/this-folder/”) then “Category 4”
else “Unclassified”
end

That formula, for the last folder anyway, will ensure that either of the first 2 exist, along with ensuring that the second part also exists.

 

When URL includes something, but not something also

If you’d like to include specific folders, but ensure that a different folder didn’t also exist, then this is the formula you need.

case
when regexp_contains(Landing Page, “/laptops/|/other-folder-as-example/”) then “Category 1”
when regexp_contains(Landing Page, “/second-category/”) then “Category 2”
when regexp_contains(Landing Page, “/third/”) then “Category 3”
when regexp_contains(Landing Page, “/another/|/also-this/”) AND NOT regexp_contains(Landing Page, “/but-not-this-folder/”) then “Category 4”
else “Unclassified”
end

For the last site section, that will ensure one of the first two pieces exist, and that the last piece does not exist, to assign it to that associated site section.

 

The outcome

When you’re done, just pull the dimension in like you would normally use ‘landing page’ and it will aggregate all your data into the associated site sections.

Pretty easy was to see aggregated data, rather than just landing page level!

 

* Due to WordPress being WordPress, some of the formulas may not copy paste. You may have to replace the quotations, with new ones, as the styling may be affected.

Excel Formulas Every SEO Needs to Know

Excel Formulas Every SEO Needs to Know

As an SEO, you really can’t escape Excel. Almost every month a new SEO tool pops up, that can do so much, but will never seem to replace those little things you can do in Excel.

You find yourself using a tool for 75% of the job, but will then always jump back into Excel to finish it off.

Well, that’s me anyway. If it’s not you, then you probably just don’t know many of the things that Excel can do!

I run through VLOOKUPs, IF statements, LEN, TRIM, PROPER / UPPER / LOWER, COUNTIFs & SUMs and show you some basic usage examples. I’ve also added a few extras to the bottom that aren’t in the video!

Basic Excel Formulas for SEOs

Whether you’re starting out, or an experienced SEO, these are the basic building blocks to so many advanced Excel uses for SEO. You need to know these.

VLOOKUP – Find data for a value, within a range

The simplest summary of what a VLOOKUP will do is to match two sets of data where you have something to match them with.

So is long as one column in each data set has some sort of unique identifier, then you can VLOOKUP between the two datasets and bring the data together.

If you don’t have a unique identifier, will you have one if you match two columns together?

You might have a date that isn’t unique, but if you add it to another column (like colour) you will get a unique value that you can use to match the data sets.

=VLOOKUP(*CELL of what you want to search for*,*RANGE of where you want to look for it*,*COLUMN NUMBER the data is in you want to extract*,*TRUE (1) or FALSE (0) but 99% will be FALSE*)

LEN – Count character length of a cell

Nice and simple formula. The LEN formula is great for counting the lengths of page titles and meta descriptions!

Plenty of other uses, but that’s what the majority of people will use it for.

=LEN(*CELL to count*)

TRIM – Remove spaces before & after a cell

Sometimes when trying to match data up, the cell might visually look the same as another cell, but you can’t seem to match it up!

The amount of times I have run into this is crazy, and the majority of times it happens there is a pesky space before or after the text.

The TRIM formula will clean these up for you and make sure there isn’t something hidden there wasting your time!

=TRIM(*CELL to trim*)

SUM – Add all numbers in a range together

Just adds numbers. It’s that simple. Just select the range you’d like to add together and away it goes.

=SUM(*RANGE to add together*)

COUNT – Display a count of cells with numbers in them

Out of your selected range, how many of the cells have a number in them?

=COUNT(*RANGE to count for numbers*)

COUNTA – Display a count of cells with numbers and letters in them (so not empty)

Out of your selected range, how many of the cells have a number of letters in them? This is what you need to use if you are trying to count cells with characters rather than numbers.

=COUNTA(*RANGE to count for numbers*)

COUNTIF – Count all the cells when a specific criterion is met

Only count a cell that matches your criteria. This is great for being able to select cell as your criteria.

So if you have a column of ‘Colour’ and a cell that has the text of ‘Blue’, you could select that ‘Blue’ cell and then the range that has all the colours, and the formula will count how many times ‘Blue’ appears in the range.

=COUNTIF(*RANGE to count for numbers*,*CRITERIA for when to count*)

PROPER – Capitalise the first letter of every word in a cell

Great for cleaning up a list of titles, or any words, where you’d like to capitalise the first letter of every word.

=PROPER(*CELL to capitalise first letters*)

UPPER – Capitalise every letter of every word in a cell

This will format it AND MAKE IT LOOK LIKE YOU’RE SHOUTING. Just use the upper formula when you need to make sure everyone knows you’re SERIOUS.

=UPPER(*CELL to capitalise everything*)

LOWER – Make every letter of every word in a cell lowercase

The lower formula will make everything lower case. Great to run through your keywords with to ensure there isn’t any weird capitalisation going on.

=LOWER(*CELL to lowercase*)

LEFT & RIGHT – Extract x characters from the left or right of a cell

This is a simple way of extracting a certain amount of characters from the left or the right of some text.

Great if you want to cull the end of a title, remove a domain, or trim some trailing slashes from a URL.

=LEFT(*cell reference for text to look in*, *number of characters*)

=RIGHT(*cell reference for text to look in*, *number of characters*)

However, if you are looking trim everything before or after a certain character, instead of a number of characters, check below for a more advanced formula!

IF – Only do something when a certain condition is met

The IF formula lets you create rules for your cells. Maybe you need to filter out titles that are too long.

You could do an IF formula in a new column that says =IF(length>70,”LONG”,”OKAY”). This will give you back a cell that says LONG if the title is too long, or OKAY if everything is okay.

Probably not the best use case, but gives you the idea!

=IF(*The condition to run*,*What to do if it’s true,*What to do if it’s false*)

Does Cell Contains Text – Run condition if the cell contains specific text

A quick formula to use if you want to do something off the back of a cell containing a specific piece of text.

This formula will return TRUE or FALSE, which is what I normally use with this one, but you can expand it do use IF. So IF bla = TRUE, do this.

Great way to quickly segregate data by whether something is in a URL, or keyword.

=ISNUMBER(SEARCH(“text to look for”,*Cell to look in*))

& – Yeap, the ampersand bad boy is rather useful

If you’re trying to merge stuff together in Excel, you need the ampersand. This thing will let you attach anything, to anything else.

It will let you run multiple formulas, then just stick them all together to form what is essentially a sentence.

=”Stick this “&”,with this “&”, and merge all “&2+2&” sentences together”

Which will output:

Stick this, with this, and merge all 4 elements together

SUBSTITUTE – Swap out any text, with any other text

This formula is invaluable if you are doing any sort of text editing or manipulation.

You select the cell you’d like to manipulate, enter the text that currently exists, then enter the text you wish to change it to.

=SUBSTITUTE(*text to look in*,”old text here”,”new text here”)

Trim URL to Domain / Subdomain

This is something that you will find incredibly useful if you’ve never done it before. Some extra tools do it, but you can achieve almost perfect trimming with just a formula – SOOOO much easier!

Quick and Dirty Trim Domain Prefixes Method

If you are just looking to trim the HTTP part, here is a hacky formula that will just substitute out the HTTP part. It’ll leave subdomains / domains, and all their folders intact however.

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(substitute(SUBSTITUTE(A2 ,”https://www.”,””),”https://”,””),”http://www.”,””),”http://”,””),”www.”,””)

Trim URL to Subdomain

Spend a bit more time playing with a formula, and you can get something that will do a much better job though!

This formula will trim any URL back to its subdomain. It will remove all prefixes, or any suffixes (aka folders) from a URL so that you are just left with the subdomain & domain combo.

=SUBSTITUTE(REPLACE(REPLACE(A2, 1, IFERROR(FIND(“//”, A2)+1, 0), TEXT(,))&”/”, FIND(“/”, REPLACE(A2, 1, IFERROR(FIND(“//”, A2)+1, 0), TEXT(,))&”/”), LEN(A2), TEXT(,)), “www.”, TEXT(,))

And another formula is;

=REGEXREPLACE(REGEXREPLACE(A2,”(http(s)?://)?(www\.)?”,””),”/.*”,””)

With A2 being the cell reference to where you have a URL to trim.

Trim URL to Root Domain

If you are wanting to get rid of the subdomain too, so you are just left with the root domain, you can use this formula. It will remove folders, subdomains, prefixes, and anything else so that you are just left with the root domain stripped out.

=SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(REPLACE(REPLACE(A1, 1, IFERROR(FIND(“//”, A1)+1, 0), TEXT(,))&”/”, FIND(“/”, REPLACE(A1, 1, IFERROR(FIND(“//”, A1)+1, 0), TEXT(,))&”/”), LEN(A1), TEXT(,)), CHAR(46), REPT(CHAR(32), LEN(A1))), LEN(A1)*2)), CHAR(32), CHAR(46))

Stripping Text Before/After Characters

This is something I have only picked up in the last couple of years, and these formulas allow you to strip out the text before or after a character or a set of characters.

I will leave this up to your imagination with how they work, but a great usage is stripped title tags of anything after a | (pipe).

Yes, you could find/replace this stuff out, or substitute it, but maybe there are 50 different items to swap out. That will take ages!

If they all have a pipe right before them, you can strip them all out with one formula.

Found these formulas here, thanks guys!

Strip Text Before a Character

This formula will remove everything before the pipe. Just edit the pipe out with any character, word, phrase, anything, and it will remove all the text before it.

=RIGHT(A1,LEN(A1)-FIND(“|”,A1))

Strip Text After a Character

Again, just replace the pipe with anything you want to have this formula strip all the text that is after it.

=LEFT(A1,FIND(“|”,A1)-1)

 

Insert Row Every Nth Row in Google Sheets

Got a list of keywords you need to break up? This formula is magic and will split your rows into lists of x values. This one in particularly breaks the keywords into groups of 20.

=ARRAYFORMULA(QUERY(FLATTEN({A2:A,IF(MOD(SEQUENCE(ROWS(A2:A),1,0),20)=19,” “,)}),”where Col1<>””))

 

Finding Your Own Excel Formulas

The biggest thing to remember with Excel formulas is that chances are someone else has tried what you’re doing before.

Just break down what you’re doing, and Google it piece by piece. You might end up putting something together yourself, through what you find from others.

Building a Keyword Research Dashboard in Excel

Building a Keyword Research Dashboard in Excel

An extremely large table of all their keywords, isn’t going to give the insight some clients would want. Some would like more a bit more insight into the data they’ve paid thousands for.

Building a keyword research dashboard in Excel is a great way to allow a client to interact with their keyword data.

In this short video, I will show you how you can take a categorised keyword list and turn it into a client-friendly dashboard!

The video is a couple years old now, and I may eventually update it, however, for the most part, it still holds true today.

Now, this won’t be great for some clients. You could end up giving them way too much information, and have them asking way too many questions.

However, I personally don’t mind that. I love when clients have an interest in SEO as it gets them more involved in the project, and more excited when the results kick in.

How to build a keyword research dashboard in Excel

  1. Categorise up your keyword research data
  2. Create pivot tables for each category level
  3. Attach slicers to the categories so you can filter the data
  4. Design the tables & slicers to match your client’s branding

And there you have it, you will have an interactive Excel keyword research dashboard to send to your clients.

Creating an online keyword research dashboard

You can save this Excel dashboard to Onedrive and then share with your clients via a link instead of sending a file.

For dashboards under a certain size this means they will be able to view the dashboard online instead of having to download the file. You can then simply update the keyword research file, and they will be able to see the update when they refresh their browser.

This becomes a massive help when trying to send updated data to a client. No more v53 tacked onto the end of a file because you’ve updated it 53 times!

Download the Excel Keyword Research File

If you watched the video and can’t be bothered doing the work yourself, you can just have my file!

Or if you’re doing keyword research for car hire you can also just steal my keywords – chances of that have to be slim.

Download the Excel Keyword Research Template
  You will receive a download link via email. We hate spam and never share your details.

 

Don’t forget to add some proper styling to the file though! She ain’t exactly pretty at the moment.

If you have any issues with the Excel file, or just need a hand with anything from the video, just leave me a comment below and I can help you out.