The content there is a little more advanced, as it is leveraging rules to determine which content template is used.
I thought I’d take it back to basics, and create a dynamic content generator that was a bit simpler.
A straight-up data-to-content text replacement.
Dynamically generating text in Google Sheets
The data set
The generator starts with your data set.
Each column represents a different piece of data.
You wouldn’t need to use every single of pieces of data you put in the spreadsheet, however, you will need to ensure any data point you do want to use is there.
Each of the columns is a variable name, and that will be taken by the generator and replaced out.
The dynamic text template
On the second sheet, is a cell where you write out your text template.
You just write out the text you’d like to include, in the format you want it, and include the variables wherever you’d like them.
Next to the text template is a list of all your variables.
These are just a list of every header name from the first sheet, and it’s a great list to help you remember what you have to work with, rather than needing to flick back and forth between your data set.
Since it’s formula driven, you can’t copy/paste the variables. However, if you’re not adding more columns to the dataset you could paste the raw data so that you could just copy them in.
The content generation formula
You’ll find the actual formula that does the replacement on the main generator sheet.
The formula might look a little daunting, but it’s just a large nested substitute.
Each heading has “<” and “>” added on either side to convert it to something to use in the text templates.
The formula will then take these variables, and substitute them for the value in that column.
It will then repeat the process.
To add a new variable in;
1. Insert a column before the contentOutput and fill in your data.
2. Add an additional SUBSITUTE( at the front of the list
3. Copy the data after the 2nd to last bracket, and paste it after the last bracket
4. Modify the cell references to instead reference your new column
So if you inserted one into the current template, the formula would go from;
We will be using that same formula to loop through and make substitutions to our keywords.
Preparing your keyword data
The prerequisite to being able to do this, is having a keyword set with categorisation set up.
You should have sets of ‘find’ words, that are then bucketed into their categories, as we will be using these ‘find’ words to find the categories.
If you’ve already done keyword categorisation following my process, then you’re in luck! You’ll have pretty much everything you need already.
If you haven’t, then please go check out the categorisation setup here first. You can still do the categorisation in the process below, but that post will give you more information about the setup.
Setting up your keyword template replacements
For each categorisation set, you’ll need to add a third column. The new column is where the templated element will go, that you will replace the find word with.
For my example, when it finds a vehicle type it will then replace it with <vehicle>.
Any mentions of ‘car’ in the keyword, will be replaced with <vehicle>.
Any mentions of ‘truck’ in the keyword, will be replaced with <vehicle>.
etc.
Each version of a ‘find’ must be included, as that is what is substituted out when we create the template.
You can see in the above sample list that “cairns” and “airport” aren’t being swapped out.
‘Cairns’ needs to be added to the location list for it to be swapped. If you want to replace ‘brisbane airport’ and not just ‘brisbane’, then ‘brisbane airport’ needs to be added to the categories. It will need to sit higher in the list than ‘brisbane’, to ensure the entire location name is replaced and not just the ‘brisbane’ portion.
Adding a length, =LEN(x) can help sort the list by length.
Whilst you could create a fixed cell reference for each category, I figure this way left it open for easier modifications if you’d like to put a different template for different finds within the same category.
Extracting your keyword templates
As mentioned, the keyword template formula works the same as the categorisation formula.
It looks for the find word, and then replaces it with the template.
This formula is run in a new column for each category that needs replacement. My demo has 4 built-in, and can be extended if you need to replace more than 4 different categories.
The formulas essentially work just replacing one category set at a time. It will then use the previous category as the substitution source, building upon each as it goes.
The ‘keyword template’ column in G, grabs the fourth category replacement and brings it forward, so that we can hide the 4 actual replacements.
This way, you’re only left with the final keyword template visible.
So provided you’ve got your categories setup well enough, you’ll get some shiny keyword templates out at the end.
Even if it’s not perfect, as long as you correctly capture all the top keywords, that should cover the majority of the keyword set for keywords with the most volume.
Basically, you’ll still get a great insight with a minimal amount of work.
Analysing your keyword patterns
Throw your new data into a pivot table, pivoting the keyword template & the total search volume.
The pivot table will group all the templates together, significantly shortening your overall keyword list, and revealing your top keyword templates.
So from this fake sample data, we can see that our fake, sample users would prefer searching in the format of ‘<vehicle> hire <location>’.
This gives us a little better understanding of how users are search, across the entire data set.
Some little caveats
Each replacement needs to be listed exactly as mentioned in the keyword
Plurals will leave ‘s’ if the plural isn’t in the find. The formula will find the first one in the list, so any plurals (longer versions) should go to the top.
Must be ordered as required, multiple mentions within the same category don’t get included
Only the first one found will be used. Any plurals or longer variations of a find should go to the top to ensure the full word is replaced.
It could be extended to look for a second or third reference, but that might take a bit of work. A simple way would be to have a second category of the same keywords, sorted from shorted to longest rather than longest to shortest. Essentially a reverse search.
Keyword patterns with seoClarity
I came across this tweet being shared;
I've never heard anyone in my circles talk about @seoClarity but this is an interesting feature that I haven't seen around: keyword patterns.
A much easier way to understand what type of content a website has and quickly spot opportunity size. pic.twitter.com/5YP2BXp1wV
Whilst I don’t use seoClarity, and due to the price point never will unless a client is using it, this looks like a quick way to be able to understand your keyword templates.
So if seoClarity is in your budget, it could be a good option.
Using your keyword templates/patterns
Rather than optimising for keywords, with a programmatic build, you should optimise for your keyword templates.
Depending on your site structure, and inbuilt customisation available will depend on what templates you should look for.
For primary elements, you should be looking at your templates and comparing search volumes for templates like;
properties for <channel> <location>
or
<channel> properties in <location>
Then depending on whether you’re wanting to optimise for the highest volume, or the quick wins like low comp, you can then pick the correct template to optimise for.
I won’t go into keyword density and optimisation stuff like that, but you can also think about where you can use these templates on your page.
Other elements of the design could have the primary, or alternate, keyword templates included.
Different templates by category
A good example is comparing the top ‘rental’ keywords to the top ‘for sale’ keywords.
You’ll get something like;
rental properties <location>
vs
properties for sale <location>
as possible top keywords for each category.
You couldn’t simply target
properties for <channel> <location>
as you would end up primarily targeting
properties for rent <location>
and not the actual primarily search keyword of;
rental properties <location>
Purely an example, but you can see how this can change based on some categories.
The easiest way to build this would be to pick the one that most suits, and that’s how I would recommend an MVP be built.
You could then build it out with different templates for each category, and sub-category, to allow the primary targeting to include these differences.
Access my sample template
You can access my sample template at the link below.
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, but it’s meant to be a like-for-like comparison across the keywords, and show that positive movement for a 1,000/search keyword is more valuable than a 50/search keyword.
Now, I have a standard CTR model I use for these analyses.
However, if you want to be a bit more specific, be able to break it out of brand v generic, or be able to view it per category, then you will need to build your own model.
You can also run these CTR models at the landing page level, to analyse landing page performance
How to build an SEO CTR model
It’s actually extremely easy to extract your own CTR model, directly from your GSC data. Here’s how to do it.
1. Extract all your GSC data for the latest month (or longer if you don’t have a lot of data). Run separate exports for queries & URLs, and you can look at the CTRs for both of these.
2. Round the ranking data to the nearest whole number, by adding a new column and using the =ROUND(<rank>,0) formula to round it to the nearest whole number.
3. Create a pivot table from the data, but don’t just use the CTR. If you average the CTR you’ll give single-click keywords, the same weighting as keywords with 500 clicks. You want to create a calculated field, that looks at clicks & impressions from the data.
4. Under ‘values’ of the pivot table, create a calculated field for the CTR that is =Clicks/Impressions. A calculated field is needed, rather than the average of CTR, to weight the keywords. Low volume keywords will swing the CTR model if not weighted.
5. Plot a scatter graph using the pivot table, and throw on a trend line to make it a bit easier to interpret.
And presto! You’ve got a customised CTR model. Now replicate this using a landing page data set.
I prefer to remove some of the lower keywords, and particularly keywords with 0 clicks from the data, to ensure they’re not messing up the data too much as they have significant low volumes. Even with the weighted CTR using clicks/impressions, there can be a lot of junk with 0 clicks so it’s just easier to exclude it all.
It’s recommended you don’t export GSC data using both queries & landing pages at the same time. Both dimensions at once will cause GSC to sample your data, limiting what you get. So unless there is a specific analysis you want to do that requires both together, it’s best to export the data separately.
The data caveat
Unfortunately, GSC doesn’t give us all out query data.
Google explains the missing query data is related to privacy, and/or for long-tail phrases.
They’re basically excluding tonnes of the longer tail keywords, that will have low impressions/clicks each, yet might still drive significant quantities of traffic.
This missing data could skew results if they are indeed super-low individual click count keywords, due to how they may shift the averages.
Even with this missing data, you should be able to make some informed decisions you couldn’t before doing a CTR analysis.
Just know the caveat behind the data though.
What you can use your CTR model for
There are a few uses for your new CTR model.
Understanding brand vs generic CTR rates, and having a CTR rate with the brand removed
Analysing CTR rates per keyword category, and knowing underperforming areas
Analysing CTR rates per landing page, and knowing underperforming landing pages
Forecasting SEO traffic
Setting baseline CTRs for areas to then make CTR optimisations & monitor going forward
And many more…
Access my sample sheet
You can access my sample sheet with the below link.
You can paste in your data and it should do all the calculations and the graph for you.
How will you use your new CTR model?
Always up for hearing what people will do with this stuff. Whilst you can just roll with the default model, a customised CTR model gives you so many more options.
Batch downloading images, and renaming them, isn’t something you’ll use often, but when you do, doing it in bulk can really save you quite a bit of time.
Why would you want to do it?
Well, plenty of reasons. From migrating images, wanting to bulk optimise their file names, or downloading imagery from brands for products you sell.
Using the attached Excel file, you can insert all the new image names, along with their current source URLs, and the macro will download all the images, give them the new names, and also save them as .jpg files no matter their source extension.
How to batch download & rename images with Excel
The following is the process on how to download images from url in excel and rename them;
1. Download and open the Excel image URL downloader
2. Click on Developer > Macros and then hit edit on the selected one in the file
3. Edit the folder path that is highlighted, with it needing to point to a folder that currently exists. This is where the newly renamed images will be downloaded too. If you point it to a folder that doesn’t exist, it won’t visually create the folder. However, if you create the folder after the fact, all the files will be there. So, yeah.
4. Insert all the new image names in, ensuring the new name includes dashes, and doesn’t include a file extension
5. Click on ‘macros’ and then click run on the selected macro
You’ll now get ‘File Downloaded Successfully’ on all the files that have downloaded from the websites okay.
You’ll see an error if it doesn’t work, with most errors I have seen attributed to being blocked by the source you’re scraping. Depending on the use, you can ask for your IP to be unlocked, but sometimes it’s just quicker to throw on a VPN.
Another issue I have seen is that if you include an extension in the name, you will end up with double image extensions like .jpg .jpg. You can modify the script by dropping the .jpg extension that’s included if you really want, but it’s just easier to exclude the extension names.
2. Connect your search console account to Search Analytics for Sheets by following the steps, and approving its access
3. Open up a new Google Sheet, from the Google Drive account that is linked to your Google Search Console. You can’t use an account that just has shared Google Sheets access, it needs to be the account that is actually linked to the Search Console account.
4. Open the Search Analytics for Sheets sidebar, by clicking Extensions > Search Analytics for Sheets > Open Side
5. Select your GSC property, enter a date range, enter page or query as “Group By”, add any filters, and then click ‘request data’
You will need to be mindful of the ‘Group By’ area. If you select page & query at the same time, GSC won’t give you all your data. You will receive a sample data set, only giving you a portion of the data.
Using Page & query at the same time is great for a couple of different analyses, but if you’re just starting to play with GSC data then it is better to export queries, or pages, to ensure you get decent figures.
Pages is what you should use if you’re looking for actual URL totals, as Google will even hide some data from the ‘queries’ to protect privacy. Things with phone numbers or personally identifiable information, when it detects it.
Creating automatic Search Console data exports/backups
Search Analytics for Sheets even offers automatic backups.
In the sidebar, you’ll see a tab for “Backups”.
Flick over to this, and you’ll see the same options as the primary tab, except for the extra “period” area.
You can enter whether you want daily or monthly backups here. Just set that up as required, and click on ‘enable backup’, and it will run at the intervals you have set and pull the required data so that you can have backups of it.
This was definitely required back when we only had 3 months of history, and not 18 months, however, it’s still good to run it now and then. Particularly for your own properties, or client properties where you think they’ll be with you for a longer period.
Plenty of uses for GSC data
There are plenty of different analyses you can run using your GSC data, so knowing how to export it is vital for these.
Data preparation is the base of any good analysis. Getting it right from the start will just ensure that any analysis you do in the future, will be nice and easy for you.
It’s back to basics time!
I run through a number of different basics, from file naming and why you should follow a pattern, through to how you should be handling your raw data & your dashboards.
There are also a few different ways of setting up the raw data, and how to split your metrics. My personal preference includes a new row for each month across each segment. So multiple metrics could be used on the same row, however, each row could be a different traffic source, or a different site section.
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 is to categorise landing pages, which can significantly help with showing how ranking performance has improved certain landing page’s traffic.
Let me show you how you can categorise text with keywords in Excel, removing the need for another keyword categorization tool you need to pay for.
This is an older video, of the keyword categorisation process in Excel. The overall principles are exactly the same, however this one 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.
(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.
New categorisation formula & setup
I was reached out to on Linkedin by Kyle Faber and he believed he could help tweak the categorisation system a little bit.
Using a combination of regex & a textjoin, he was able to help tweak how the categories are applied.
It does appear to be faster that the setup posted above, and is also a lot simpler & cleaner which will help troubleshooting.
I’ll break it out a bit more later, but for now, the new formula for joining all the seed phrases together is;
Which will categorise the exact same setup above, with a different formula, by looking for the matching find, and then return the category with a vlookup.
It will default to partial matching, but if you’d like exact matching of a word, then enter a space before & after the find column.
That will then match full mentions of the word, rather than just partial matching.
Was great to get some feedback on this, and be able to tweak the setup that I’ve used for years!
You can also use this directly on URLs, to categorise them into site sections, locations, or anything you’d like. This would allow you to view SEO performance by site section, based on grouping the URLs by folders, or any structure within them.
Just use the exact same formula, but rather than putting keywords in the ‘find’, add portions of URLs that you would like to group together. The formula will then put each of the URL structures together in to their groups, allowing you o view their peformance.
Let me know how you use this sort of sheet to categorize in Excel or Google sheets, or whether you do this all a different way.
When downloading a pre-built Excel dashboard, or moving one around on your computer, you might experience an error and you won’t be able to open your PivotTable.
The error will come up and say ‘Cannot open PivotTable source file’ and then include the file name.
The simple fix is to disconnect all slicers, remove the original file name from the PivotTable data source, and then reconnect the slicers.
In the video, you can see I disconnect my slicers, and then remove the original filename from the data source leaving only ‘RAW’. ‘RAW’ is the table name that is on my ‘RAW’ data tab.
An overview of how graphs in Excel work, and the basic behind customising the look.
This is a great starting point if you’re just getting into SEO, and want to play around with a data set in Excel.
Yes, Google Data Studio and Google Sheets are replacing a lot of this now, but this is still good foundation work.
There are so many things I do with my graphs. Some of these things are smoothing the lines, moving the legend to the bottom instead of the right, and then ofcourse customising the style.
One of the best things to do for a client is match the design of graphs to their brand. This makes it look so much more professional, because you have gone above and beyond. It is so easy to do this, but adds so much value for the client.
The pre-work to this is the pivot table training video here.
