Category: Excel/Google Sheets

Dynamic Content Generation with Google Sheets

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 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;

=SUBSTITUTE(SUBSTITUTE( SUBSTITUTE(SUBSTITUTE( SUBSTITUTE(SUBSTITUTE( SUBSTITUTE(Template!$A$2, “<“&$C$1&”>”,$C2), “<“&$D$1&”>”,$D2) ,”<“&$E$1&”>”,$E2), “<“&$F$1&”>”,$F2), “<“&$G$1&”>”,$G2), “<“&$H$1&”>”,$H2), “<“&$I$1&”>”,$I2)

To become;

=SUBSTITUTE(SUBSTITUTE( SUBSTITUTE(SUBSTITUTE( SUBSTITUTE(SUBSTITUTE( SUBSTITUTE(SUBSTITUTE( Template!$A$2, “<“&$C$1&”>”, $C2),”<“&$D$1&”>”,$D2), “<“&$E$1&”>”,$E2), “<“&$F$1&”>”,$F2), “<“&$G$1&”>”,$G2), “<“&$H$1&”>”,$H2), “<“&$I$1&”>”,$I2) ,”<“&$J$1&”>”,$J2)

Note the blue for the new modifications.

Since the new column of data was J, we copied the end and then changed the I to a J.

 

Content template preview

A preview column has also been added to the text template tab.

Once your generation formula is set up with all your variables, you will be able to see a live generation example as you edit your template.

This will allow you to see exactly what the text template looks like when you substitute some of your real data into it.

The example will randomly select from all of your examples every time you update the cell.

A great way to ensure you’re accounting for different outputs in your template, as you craft it.

 

Access the Google Sheet

You can access the dynamic content generation Google Sheet at the below link.

Let me know if you have any questions!

Feel free to leave a comment, I’d be happy to help out.

 

Extracting Keyword Templates/Patterns from a Keyword Set

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 best isolate the keywords that a programmatic system should fall into?

 

What are keyword templates/patterns?

A keyword template, or keyword pattern, is the overall structure of a keyword with dynamic elements removed.

So think about your keywords, without dynamic elements like property types, locations or something else.

They’re the core keyword with all that removed.

Some real estate examples are;

<propertyType> for sale in <location>

buy <propertyType> in <location>

<location> <propertyType>

You can’t primarily optimise for every variant of a keyword.

You’ll probably get one good option for a page title & H1.

They give you the opportunity to compare keywords like for like so that you know which one to pick for primary optimisation.

 

How to find your keyword patterns

Personally, the only tool I am using is Google sheets and keyword research data.

There are a few tools out there, but sheets give me everything I need to do this.

Before jumping into the below, I recommend you go check out my post/video on keyword categorisation here.

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;

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.

Just copy the template over to your own Google Drive and you will be able to edit it.

Let me know if you have any questions!

Building Your Own CTR Model from Search Console Data

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, 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.

 

Bulk Download, Convert & Rename Images

Bulk Download, Convert & Rename Images

Batch image downloading and renaming 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 you can bulk download and rename images;

1. Download and open the Excel image 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 new renamed images will be downloaded to. 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

 

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 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.

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

Successful batch download & rename images to JPG

You should have no successfully downloaded and renamed all the images you will need, extremely fast.

Export Google Search Console Data

Export Google Search Console Data

When you start doing SEO data analysis, one of the first things you’ll find out is that GSC only gives you 1,000 rows of data.

So how do you extract the rest of the data?

How do you get more keywords, and more landing pages downloaded from GSC?

Search Analytics for Sheets

Whilst there are a few ways, this is how I always end up doing it.

Every time.

Search Analytics for Sheets provides the easiest method of extracting more than the 1,000 rows available inside GSC.

In just a few steps, you can connect your search console, select your filters, and export your data.

 

How to export more than 1000 results in Search Console

1. Firstly install the GSC extension, available from here

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 and Setting up a File

Data Preparation and Setting up a File

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.

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.

 

New catorisation formula & setup

I was reached out to on Linkedin by Kyle Faber and he believed he could help tweak the system a little bit.

Using a combination of regex & a textjoin, he was able to help tweak the system.

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;

=textjoin(“[s]?|”,true,sort(I$3:I,1,false))

Then the formula used in the category cell, is;

=IFERROR(VLOOKUP(REGEXEXTRACT(” “&A2&” “,I$1),I$3:J,2,FALSE),”Unclassified”)

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!

Get the updated sheet at the link below;

Definitely give Kyle a follow on his socials, at https://linkedin.com/in/kylefaber & https://twitter.com/regal_kyle.

 

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.

Cannot Open PivotTable Source File Fix in Excel

Cannot Open PivotTable Source File Fix in Excel

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.

The Basics of Graphing in Excel

The Basics of Graphing in Excel

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.