en
English Español Deutsch Français Italiano Português (Brasil) Русский 中文 日本語
Submit post
Go to Blog

Weekly Wisdom with Ross Tavendale: Time-Saving SEO Hacks

Weekly Wisdom with Ross Tavendale: Time-Saving SEO Hacks

Ross Tavendale

This video is part of our Weekly Wisdom series which features experts on a variety of topics. 

Modified Transcript

Hi guys, it's Ross here from Type A Media, welcome to another Weekly Wisdom video. Type A Media are known for our four day work weeks, and the way we can get away with that is by cutting out all the fat from our daily processes. So in this Weekly Wisdom video, I am going to go through ways I can save a second here, a minute here, an hour here. With some of these tips and hacks, as well as some tools that we used to kind of cut the fat and get straight to the point — so we can get the data in and analyze it, and more importantly, get it live on our client's site so we can start ranking them.

So without further ado, let's get into it. One of the things I find that people spend a lot of time on is finding all the URLs that ever existed for their website. Now typically they may crawl the site to find what is on there and maybe look at the XML site map. They may be jumping to Search Console, have a look at that. Maybe jumping into Majestic to see all the pages with links, and that is cool but what if the client has been migrated like 6 times over the last 12 years? Do you have that data? Is it sitting anywhere? Of course, you could go to something like archive.org, and you could search that and start pulling that out, but that is a bit slow as well, so I am going to show you a really fast way to put all this stuff together.

Wayback Machine 

On the topic of archive.org, did you know that there is an endpoint to pull CSVs from it? So what you can actually do is construct this entire URL. We are using my website, typeamedia.net, match type is a domain. You can see here a URL limit; I can actually say 'give me 10,000, 100,000 —you name it — as many URLs as you want or put it in a CSV, and do it from 2007 to 2018 and show me only things that had a 200 status code had a response. That is kind of cool, but I can't really do anything with the information unless it is in a spreadsheet; we all love a little bit of Google Sheets. What we are going to do is we are going to import the data — I need to put an equal sign at the start of that, so it knows that it is actually a formula — and once you do import data, make sure that you wrap it in parenthesis and all of sudden there are all the URLs. So what's next?

Sitemaps

I am going to get my sitemaps, if you use Yoast, and I absolutely love Yoast, you will probably get multiple site map URLs. What you want to do is set something up where you can just blast that in a spreadsheet. Now Import XML does that for you, but the problem with Import XML, it wouldn't give me a lovely clean list like this if I go 'Import XML'. What it is going to do, is it is actually going to give me the entire thing with all of the formatting, or it is going to just throw up a big ol' error. So we don't obviously want that, so when I do Import XML, get a little bit of RegEx in here to chop some of that out. Now would be a good time to pause the video and just take a note of what this is; I am not going to explain it, it is a little bit outside of the scope of this video. But ultimately it lets you strip out all of the unwanted stuff from your XML site map.

Majestic

Next up, Majestic. Now I really love Majestic, and it is mostly because they have got APIs into pretty much everything, so there is an add-on for Google Sheets. Go into the add-on, put your domain name in and we want to see the top pages — both historic and fresh. Hit 'Get data' and then you can see these new tabs appearing because it is pinging the API and it is dumping everything into Sheets. Beautiful.

But those are two separate sheets; I want them together, so what I am going to do is use this formula called Unique. So if we go 'Unique', because we are stacking two different things on top of one another and not just looking for one unique list, we need to turn this into an array. We are going to go 'curly brackets' and I'm just going to take the first three columns — 'semicolon', which we use inside of array inside of Sheets. Go to the next one, it is the same thing, close our curly brackets off like this, and then on we go. Alright, so that has pulled in all of the Majestic data in there which is fantastic.

SEMrush

Next, the fan favorite, it is, of course, S-E-M or should I say SEMrush. So looking at add-ons, I go into super metrics and launching my site bar, and what we are going to do is we are going to drop our domain name in. The report that we want is the "domain organic search keywords" and then we hit 'apply', and that is going to pull everything in for us.

Google Webmaster Tools

Alright, so next up, we want to get Google Webmaster Tools, note that I said 'Webmaster Tools', not 'Search Console' because I have been doing this for more than two seconds. Okay, so how do we get Search Console in? Again, it is our favorite tool; it is going to be super metrics, but we are just going to change the data source to Search Console. Okay, dropping in your website, pulling it in as normal, make sure you put your dates as last year, so it pulls in loads and loads of stuff.

I want to get the search queries with the full URLs, hit 'Apply changes' and in it comes. Alright, and here is all the stuff that we rank for; I'm actually bothered with that and bothered with this landing page data. Look at all of that lovely duplication. So we have got all these different sources and now what we want to do is bring them all together in a nice kind of singular format and remove all the duplication, so the question is how do we do that?

Unique Formula

Well, we are going to go back to the wonderful formula, my favorite formula, Unique. We are literally just going to go 'unique' here, open with a normal bracket and then remember because we are about to do an array, which is multiple formulas stacked on one another, we are going to have a curly bracket here, and we are literally going to go to absolutely everything. We need to start with the archive.org; pull that in. We are then going to go into the sitemap; pull that in. We are then going to go to all Majestic; pull that in. Next we are then going to go into SEMrush and pull all that in, and then we are going to go into Webmaster Tools, formerly known as Webmaster Tools now is Search Console, pull that in, and we are going to close that off with a curly bracket and a normal one, hit the 'enter' button and there we go.

So what we have now got as a completely ordered list of every single URL that has ever existed on our website and every single duplicate removed. I think I can probably say with a high degree of certainty that that is all the URLs that have ever existed for my website. I can now do some really cool things with the list. So an example of what I would do with this data, well I could probably go to the frog (Screaming Frog). I would paste in a list, and I probably would want them to crawl it because when they finish, I am going to pull a report and I am going to see all of my redirect and canonical chains. After tons and tons of redirects before lots of site migrations, I can see where all the problems lie.

That is SEO speed hacks, tips, and tricks. Done.

Ross Tavendale
columnist

SEMrush columnists are authors who had proved their expertise in digital marketing and contribute regularly to our community.

Ross is the Managing Director at Type A Media, an independent search agency that work with FTSE250 companies and mid-sized brands to help them find the optimal way to talk to more people online.
When not obsessing over his clients rankings, he hosts the Canonical Chronicle, a weekly web show watched by 100k people every month.
If you want to ask him a direct question you can find him @rtavs on Twitter.
Share this post
or

Comments

2000
Digi Mark
Newcomer

Either just recently joined or is too shy to say something.

Really a great blog, I was struggling a lot in SEO, but this could surely help me to make it.
Pro

Asks great questions and provides brilliant answers.

Have been using these techniques for a while now, not exactly the same but similar to it, and I must say these do save a lot of time.
Edmund
Newcomer

Either just recently joined or is too shy to say something.

Great blog !
Ross Tavendale
columnist

SEMrush columnists are authors who had proved their expertise in digital marketing and contribute regularly to our community.

Edmund
Cheers bruh!
Vipish malhotra
Enthusiast

Occasionally takes part in conversations.

Great blog ! I read your blog now share great information here. it's really provide us more useful and time saving tips for SEO, with your commends we save a lot of time. Thank you.
Ross Tavendale
columnist

SEMrush columnists are authors who had proved their expertise in digital marketing and contribute regularly to our community.

Vipish malhotra
Thank you Vipish - pleasure to be of service. :-)
Marcus Wincott
Enthusiast

Occasionally takes part in conversations.

Really useful time-savers here, thanks Ross!

I'm scared of what I might find but I'm going to do this for the last 10 years of our business site and see what work need to be done! Wish me luck!
Ross Tavendale
columnist

SEMrush columnists are authors who had proved their expertise in digital marketing and contribute regularly to our community.

Marcus Wincott
Thanks Marcus - hope you find them useful in your day to day :-)
Newcomer

Either just recently joined or is too shy to say something.

Becoming a believer of Google Sheets as well!
Ross Tavendale
columnist

SEMrush columnists are authors who had proved their expertise in digital marketing and contribute regularly to our community.

Karen Kannegiesser
We have a believer. Can I get an AMEN?
Lefto Chatziandreou
Newcomer

Either just recently joined or is too shy to say something.

Ross, you just gave me another good reason to ditch excel and switch to Google sheets!

Quick question, do you know if it's possible to get the latest or oldest crawl date along with the url from archive.org?
Ross Tavendale
columnist

SEMrush columnists are authors who had proved their expertise in digital marketing and contribute regularly to our community.

Lefto Chatziandreou
Hey Lefto, thanks for commenting.

Yes I believe you can pull pretty much any piece of information from the site via their API or constructing the correct URL to pull via CSV.

Here is the official documentation: https://archive.org/help/json.php
Tom Livingstone
Newcomer

Either just recently joined or is too shy to say something.

Some nice little nuggets of info in this mate nice one
Ross Tavendale
columnist

SEMrush columnists are authors who had proved their expertise in digital marketing and contribute regularly to our community.

Tom Livingstone
Cheers Tom. Hope you find it useful.
Newcomer

Either just recently joined or is too shy to say something.

wow great topic. It very helpful
Abbas Rajani
Newcomer

Either just recently joined or is too shy to say something.

Hey Ross,

I just got some time to catch up with your videos on SEMrush and seems like I missed some coolest SEO hacks over the internet. Honestly speaking I haven't used Google Sheets that way and definitely its something which will help optimizing the time. As SEO is all about getting maximum things done in less time. Thumbs up!
Ross Tavendale
columnist

SEMrush columnists are authors who had proved their expertise in digital marketing and contribute regularly to our community.

Abbas Rajani
Thanks Abbas - really appreciate you taking the time to comment.

Excel is the tool of choice for most SEOs as its so good at data manipulation but now that sheets has a Big Query integration (https://cloud.google.com/blog/products/g-suite/connecting-bigquery-and-google-sheets-to-help-with-hefty-data-analysis) as well as Query language - it's a really robust solution. That fact that its cloud based, connected to drive and has the ability for real time collaboration makes it our tool of choice. Oohhh and it also connects straight into datastudio for easy dash-boarding.
Newcomer

Either just recently joined or is too shy to say something.

Nowadays I see newly launched websites with low page rank come up to the first position within a week time.

Do these guys do Black hat seo technique?

IS SEO algorithm changed?

Any specific white hat techniques should I employ to come up to the top within a week?
Ross Tavendale
columnist

SEMrush columnists are authors who had proved their expertise in digital marketing and contribute regularly to our community.

Chitaranjan Natarajan
Hi Chitaranjan

Firstly, let me address the term "black hat". This is a term used by Google to suggest illegal and untoward activity - I'm trying to get the community to use it less as it's bad PR for the indsutry as a whole. As an alternative I like to say "Empirical SEOs" or talk about "Stress Testing the Algorithm".

Anyway, semantics aside, the site may be targeting low search volume terms or have redirected from a previously successful site. REmember, that toolbar pagerank no longer exists so there may be additional metrics you need to look at to diagnose why this site is ranking.

I recommend running it through the tools on SEMrush & checking their backlink profile to see the areas they are doing well.

Further reading for yourself on website quality metrics I can recommend the Search Raters Quality guidelines from Google which will tell you all the onpage quality metrics you need to be successful.

https://static.googleusercontent.com/media/www.google.com/en//insidesearch/howsearchworks/assets/searchqualityevaluatorguidelines.pdf
Ashif (Ace) Rashid
Enthusiast

Occasionally takes part in conversations.

#Ross, this is an excellent article and great explanation on using Google Sheets. I learned a few tricks here that will help shave some time from reporting as well as website audits. Thanks!
Ross Tavendale
columnist

SEMrush columnists are authors who had proved their expertise in digital marketing and contribute regularly to our community.

Ashif (Ace) Rashid
Thanks Ace. If you make anything cool on sheets I'd love to see it :-)
Paul Lovell
Master

A veteran community member.

Great Video Ross, some great tips and advice on how to pull this data together, Thanks so much for Sharing 😁😁
Ross Tavendale
columnist

SEMrush columnists are authors who had proved their expertise in digital marketing and contribute regularly to our community.

Paul Lovell
Anytime Paul. Our agency work a 4 day work week, so being effective > productive is key. A big part of that is automation and reducing sequential tasks into a single action.
Jaydeep Patel
Newcomer

Either just recently joined or is too shy to say something.

Such a powerful technique for importing the sitemap data/URLs. It is amazing!
Ross Tavendale
columnist

SEMrush columnists are authors who had proved their expertise in digital marketing and contribute regularly to our community.

Jaydeep Patel
Thanks Jaydeep. Let me know what creations you can come up with.
Anton Shulke
SEMrush

SEMrush employee.

Ross, what software do you use to record your screen?
Also, it is very difficult to read what you are typing into Google sheet, any chance of zooming the screen?
Thanks,
Ross Tavendale
columnist

SEMrush columnists are authors who had proved their expertise in digital marketing and contribute regularly to our community.

Anton Shulke
Hey Anton, I use SnagIt to record everything. I'll make sure and zoom to some of the formula boxes and slow it down for the next one.
Isabella Harriet
Enthusiast

Occasionally takes part in conversations.

Very Nice Post.
Cooper Hollmaier
Pro

Asks great questions and provides brilliant answers.

Great video, Ross! Thanks for sharing. I hadn't even considered some of the formulas/extensions you're using with Google Sheets. That said, I know that Majestic (https://majestic.com/plans-pricing) can cost prohibitive when you're working with smaller agencies/clients. Their API plan is around $399/month I believe. I personally utilize URL Profiler in conjunction with Ahrefs, which tends to be a bit more cost efficient in my experience (https://ahrefs.com/pricing-plans). Are there any other tools you'd use as a substitute or is there a particular reason you favor Majestic?
Ross Tavendale
columnist

SEMrush columnists are authors who had proved their expertise in digital marketing and contribute regularly to our community.

Cooper Hollmaier
Hey Cooper, thanks for chiming in. Want to hear my dirty little secret?

You only need $29 dollars to access the Majestic API.....dun, dun, dun! The things is - Google sheets dosen't use the API, it uses "index credits" and you get a whopping 5000 of them on the basic plan.
And the great thing is you can plug in the majestic data and use it with url profiler and screaming frog without using the API license. So if you really wanted to you could get the best SEO tool set for the following:

SEMrush license: $99
Majestic license (to use in sheets): $29
Supermetrics: $69
Screaming Frog: $10 (149 per year)
URL profiler: $10

So you could essentially run an entire agency on about $200 a month. And if you only want to use Majestic in sheets you don't need Supermetrics at all.

When i first started our agency I had, SEMrush, Screaming Frog, Majestic and Sheets. It cost me $100 a month (with the upfront "investment" in the Frog - which is the biggest SEO bargain of all time). Now that we are a bigger agency we spend about $4k a month on buying data from various providers to make our own internal tools and workflows but you really don't need that unless you're hitting scale.
Ross Tavendale
columnist

SEMrush columnists are authors who had proved their expertise in digital marketing and contribute regularly to our community.

Ross Tavendale
UPDATE

I am on the grandfathered pricing from 8 years ago :-D

The actually price of Majestic is £39.99 - which is still insanely good value.
Jonathan Björkskog
Enthusiast

Occasionally takes part in conversations.

Thanks Ross! The unique-command saves a lot of time, I'll now clean all my spreadsheets which are filled with vlookup's and hlookup´s and doing the same thing in an extremely difficult way. :) This kind of makes me feel stupid, that I've not tested unique-command earlier :-D
Ross Tavendale
columnist

SEMrush columnists are authors who had proved their expertise in digital marketing and contribute regularly to our community.

Jonathan Björkskog
Cheers Jonathan. Query is another absolute killer that I love to use - it's like a really like weight version of SQL. Give it a go if you haven't already.
Sona Bulgadaryan
Helper

An experienced member who is always happy to help.

I didn't know about the export feature in Wayback Machine :D
Ross Tavendale
columnist

SEMrush columnists are authors who had proved their expertise in digital marketing and contribute regularly to our community.

Sona Bulgadaryan
I know, right? I am amazed that it is free to get this data.
Andy Halliday
Enthusiast

Occasionally takes part in conversations.

Great article, some really useful tips.

It would be useful if within the body of the article, you included the links, like the http://archive.org link as well as the formulas so i can copy and paste
Ross Tavendale
columnist

SEMrush columnists are authors who had proved their expertise in digital marketing and contribute regularly to our community.

Andy Halliday
Hey Andy, good feedback for the future. I'll give away the sheet links in the body copy. For now, here is the archive endpoint: http://web.archive.org/cdx/search/cdx?url=yourdomain.com&matchType=domain&fl=original&collapse=urlkey&limit=20000&output=csv&from=2000&to=2018&filter=statuscode:200
Ric Rodriguez
Enthusiast

Occasionally takes part in conversations.

Great video, thanks for sharing your ideas Ross! The tip around importing sitemap data is ace (and who doesn’t love a good Sheets hack!)
Ross Tavendale
columnist

SEMrush columnists are authors who had proved their expertise in digital marketing and contribute regularly to our community.

Ric Rodriguez
Cheers Ric. I honestly think that if all SEOs learned to be a data analyst first, they would be in a great place for the rest of their careers.
Newcomer

Either just recently joined or is too shy to say something.

Great tips shown here , its very useful to us .
thanks for sharing
Newcomer

Either just recently joined or is too shy to say something.

I came to know about Majestic and Wayback Machine here..It is always good to learn new things..Informative tips.
Ross Tavendale
columnist

SEMrush columnists are authors who had proved their expertise in digital marketing and contribute regularly to our community.

Sindhu
Cheers Sindhu. Majestic is my favourite tool when it comes to backlink analysis in sheets.
James Norquay
Helper

An experienced member who is always happy to help.

Great tips Ross, useful for anyone looking to save time with day to day SEO.
Ross Tavendale
columnist

SEMrush columnists are authors who had proved their expertise in digital marketing and contribute regularly to our community.

James Norquay
Cheers James. Appreciate the feedback. A long time SEO like yourself must have a few good hacks up your selves too :-)
James Norquay
Helper

An experienced member who is always happy to help.

Ross Tavendale
Thanks Ross yeah I think you always do pick up a few tips after doing SEO for quite a few years. I have been doing SEO/ Online marketing since around 2005 when I was learning the ropes building affiliate sites as a young lad. I think you can always learn hacks from supplementary industries, we use to share a office with a "ethical hacker/ pentest company" company who showed many cool tools to diagnose security based SEO issues and pick up things on sites quickly, you do not really see many SEO blogs talking about things like this some really good tools like Pentest tools. I am also interested in accessibility scrapers like Tenon for SEO cross over many uses for SEO campaigns as well. Whilst many of these are not hacks they are just other methods to do things and other tools which are probably not built for SEO but they have SOE benefit. They do speed up your jobs and you can hack them for benefit.
Newcomer

Either just recently joined or is too shy to say something.

Really awesome and actionable, thanks for sharing your knowledge.. I haven’t read all the comments so i apologise if someone’s mentioned this already but you could also gather GET data from server logs to see which pages have hits on them, as well as pages which have sessions in GA that may have been forgotten about, not driven traffic in a while or haven’t been linked to during migrations.
Ross Tavendale
columnist

SEMrush columnists are authors who had proved their expertise in digital marketing and contribute regularly to our community.

Max Coupland
Yes all very good points. Assuming the client has had the same dev agency for every iteration of the site......but I rarely find that to be the case sonoreffer 3rd party databases. That being said, logs would be insanely helpful. Thanks for commenting Max :-)
Newcomer

Either just recently joined or is too shy to say something.

I am a complete newbie to this; can you explain what the point of this exercise is?
Ross Tavendale
columnist

SEMrush columnists are authors who had proved their expertise in digital marketing and contribute regularly to our community.

Bob Lope
Hey Bob, welcome to the community. So the reason you would want all your URLs is so you can crawl them to see if they return a 200 or a 404. If they 404 you should redirect them to a better location.

Why? Lot of reasons but primarily
-you retain link equity
-you help users get to the right page
-you signal to Google you have a quality site
-Google uses it's resources on pages that you care about

In short, you're more likely to rank well if your site isn't chock full of errors and daisy chain redirects.

Other reason would be to start making a content inventory of the site, creating a starting point for working out internal linking, etc.
Newcomer

Either just recently joined or is too shy to say something.

Ross Tavendale
Thanks but what if you are happy with a 404? Isn't the point of a 404 to say content doesn't exist.

If I 301 all my 404s so to speak, aren't I just creating more redirects?
Ross Tavendale
columnist

SEMrush columnists are authors who had proved their expertise in digital marketing and contribute regularly to our community.

Bob Lope
Yes, that's absolutely the case. If you are happy with the content "not existing" are you happy with your website visitor clicking from Google to your site to receive an error or would you prefer it to be removed from the index entirely? I'd argue it's better to remove it from the index. If this is the case then a 410 would be a better solution. However, if there are links pointing to your 404 then it's a much better idea to redirect as it retains link value on the site. If you leave pages with external links as 404s, you won't recognise the link value from them.
Tristam Jarman
Expert

Provides valuable insights and adds depth to the conversation.

This is a great tip. Especially the Wayback Machine data pull. I usually head to a few different sources to try and collate data. This can become trickier as you noted with sites that have been migrated several times. Leaving you to make a patch work quilt of URLs from different data sources.

What you’ve shown here is a reliable process to pull this all together, with reasonable ease. Still need to get me some Supermetrics!

Thanks for sharing another great tip! Loving the series. That seat is awesome btw :)
Ross Tavendale
columnist

SEMrush columnists are authors who had proved their expertise in digital marketing and contribute regularly to our community.

Tristam Jarman
Thanks Tristam. I love spending time to save time. My entire team are now sheets wizards with their data manipulation. A real step up is learning query as this lets you do computation on a dataset whilst pivoting and filtering and sorting it - really cool stuff.

If you are signing up for Supermetrics use the code "ross" at checkout and they will give you 20% off ;-)
Tristam Jarman
Expert

Provides valuable insights and adds depth to the conversation.

Ross Tavendale
It's definitely about being streamlined with tasks and saving time where you can. Thanks for the code :)
Newcomer

Either just recently joined or is too shy to say something.

So .. I never learnt that you could use Google Sheets to pull in XML Sitemaps - it has been my one frustration from going to a PC from a Mac - on a PC you could just open the URL in Excel and get everything!! Great video series Ross .
Ross Tavendale
columnist

SEMrush columnists are authors who had proved their expertise in digital marketing and contribute regularly to our community.

Gerry White
Thanks Gerry. It's one of the major fails of excel for Mac. I actually run parallels so I can have windows Excel running. But to be perfectly honest - now that sheets has a BigQuery integration built in...it's getting hard to work out why you would want Excel. I know a bunch of SEOs will pillory me for saying that BUT i'd take ease of sharing and collaboration and syncing across devices if it means I need to give up slicers (which i really miss)
Tommy Griffith
Helper

An experienced member who is always happy to help.

Loving the Majestic data extractor - hadn't seen this before. Great video, thanks!
Ross Tavendale
columnist

SEMrush columnists are authors who had proved their expertise in digital marketing and contribute regularly to our community.

Tommy Griffith
Cheers Tommy. It's the major reason I love Majestic. The data is great and its sooooo easy to get into a form where I can start analysing what's going on to see if there are any issues.
Randy Milanovic
Enthusiast

Occasionally takes part in conversations.

Brilliant. Wait, how many subscriptions is that?
Ross Tavendale
columnist

SEMrush columnists are authors who had proved their expertise in digital marketing and contribute regularly to our community.

Randy Milanovic
Haha - it's 3 in total. Supermetrics, SEMrush and Majestic. As an agency we spend more on tools than we do on rent. Considering we're at London Bridge - that's a considerable annual spend (gulp!) But there are ways to do it for free or slightly cheaper. I'm sure if you reach out to the SEMrush folks for a "solo API" license, they would be happy to accommodate you.
Randy Milanovic
Enthusiast

Occasionally takes part in conversations.

Ross Tavendale
:) I’m currently using MOZ and SiteBulb. I tend to switch tools about every 18 months. Eventually coming all the way around.
David Sayce
Helper

An experienced member who is always happy to help.

Many more of these and we will all be doing a 2-day working week!
Ross Tavendale
columnist

SEMrush columnists are authors who had proved their expertise in digital marketing and contribute regularly to our community.

David Sayce
Haha make it happen. 4 day work weeks! #FridaysAreForMugs
Jason Barnard
columnist

SEMrush columnists are authors who had proved their expertise in digital marketing and contribute regularly to our community.

columnist
That is a great way to get all the pages on a site.
I find with clients that if I ask them to tell me how many pages they have (or worse, have had over the site's lifetime), they always vastly underestimate.
Your list will really bring home to them in a very solid manner how far they underestimate :)

Just to be safe, I would also add GA data to that list... although I have no idea if that would actually add anything that isn't there already
Would I be wasting my (precious :) time ?
Ross Tavendale
columnist

SEMrush columnists are authors who had proved their expertise in digital marketing and contribute regularly to our community.

Jason Barnard
Hey Jason, yes totally agree on GA. The sheet actually has GA in it but I don't think I included it in this video. It's amazing that clients don't know their own site and how many URLs they have had through the ages. GA probably would pull in more URLs on top of this to be fair. One thing to note is the number of random GA accounts clients have and making sure we set the dates back 10 years to pull everything.
Jason Barnard
columnist

SEMrush columnists are authors who had proved their expertise in digital marketing and contribute regularly to our community.

columnist
Ross Tavendale
Oooh. Random GA accounts. I hear you !
Tim Capper
Guru

A bearer of digital marketing wisdom.

Nice little roundup there, spot on mate
Ross Tavendale
columnist

SEMrush columnists are authors who had proved their expertise in digital marketing and contribute regularly to our community.

Tim Capper
Thanks Tim :-)
Milosz Krasinski
Helper

An experienced member who is always happy to help.

Ah Wayback Machine trick! Here is the endpoint: http://web.archive.org/cdx/search/cdx?url=yourdomain.com&matchType=domain&fl=original&collapse=urlkey&limit=20000&output=csv&from=2000&to=2018&filter=statuscode:200 here you can change the parameters.
Randy Milanovic
Enthusiast

Occasionally takes part in conversations.

Milosz Krasinski
Aces
Jason Mun
Expert

Provides valuable insights and adds depth to the conversation.

Great video Ross.

BIG BIG fan of Supermetrics. Automation for the WIN!

When dealing with larger sites, don't you feel that G Sheets is super sluggish and you hit their column limits really quickly? What do you do in that instance.

The archive.org trick is dope. I learn something new every day.
Ross Tavendale
columnist

SEMrush columnists are authors who had proved their expertise in digital marketing and contribute regularly to our community.

Jason Mun
Hey jason, yes i completely agree. Anything with a visual UI is going to be RAM reliant so if you are in to the hundreds of thousands of lines or even millions you can't use sheets. For larger projects we use BigQuery with Google Sheets or Jupyter Labs for the python heads out there.
Newcomer

Either just recently joined or is too shy to say something.

Great vid, this was really insightful!
Ross Tavendale
columnist

SEMrush columnists are authors who had proved their expertise in digital marketing and contribute regularly to our community.

Amritan Walia
Thanks Amritan - appreciate it :-)
Arnout Hellemans
Helper

An experienced member who is always happy to help.

Nice write up @Ross. Apart from this I would also use GA as a source. And if available log files with user agents. But again a solid process.
GA can be automated through the API too.
Ross Tavendale
columnist

SEMrush columnists are authors who had proved their expertise in digital marketing and contribute regularly to our community.

Arnout Hellemans
Yes I totally agree. Log files would be superb.....getting a client to give us them can be challenging though.
Nitin Manchanda
Helper

An experienced member who is always happy to help.

Really useful stuff, thanks for sharing that Ross. Loved the way you generated the list of unique URLs using different sources in a few minutes!
Ross Tavendale
columnist

SEMrush columnists are authors who had proved their expertise in digital marketing and contribute regularly to our community.

Nitin Manchanda
Thank you Mr Manchego! Our first port of call nowadays is usually sheets - next tutorial I might go into how we use bigquery to do this over millions of rows without the sheet crashing- that's where the real fun stuff is :-)
Nitin Manchanda
Helper

An experienced member who is always happy to help.

Ross Tavendale
Sounds exciting. Can't wait for the next one, Rossy ;)
Judith Lewis
Legend

Getting here is not easy at all!

Can you just get that added to SEMRush (S-E-M Rush because I’ve been doing this longer than two seconds as well 😂😂😂) for me please? Ya - thanks!
Actually, in all seriousness, this is a seriously good set of tips because the number of times I find links to dead and not redirected pages is high! I’ve just sent a report off to a client with 108 unique non-redirected URLs with multiple links pointing to them which is just killing me slowly.

Fabulous and hey if it does get added to SEMRush it will add some serious power!
Ross Tavendale
columnist

SEMrush columnists are authors who had proved their expertise in digital marketing and contribute regularly to our community.

Judith Lewis
Haha, i know right - next big killer app feature.

I feel your pain. Most of the issue we find on sites are dead pages from sites that have had millions of redesigns and now are a rats nest of daisy chain redirects and dead ends. All of which have valuable links pointing to them. In one instance we found a client with an entire subfolder of old products that was no longer in the index but had over 100 links pointing to it....madness
Lukasz Zelezny
Pro

Asks great questions and provides brilliant answers.

Ross is good in this. Remember London meetup where we were both speaking. Lots of useful tips and hacks. Day has only 24 hours... when you may have theoretically unlimited amount of clients. So time saving in SEO is a must.
Ross Tavendale
columnist

SEMrush columnists are authors who had proved their expertise in digital marketing and contribute regularly to our community.

Lukasz Zelezny
Hey Lukasz - hope to see you at another event soon. You're so right - saving as much time as possible is such a big deal. We deliberately choke the number of clients that we having coming through the door so we can continue to be effective. It's very counter intuitive to say no but it's something that has to be done.....or just automate the world and save tons of time lol.
Milosz Krasinski
Helper

An experienced member who is always happy to help.

Ross Tavendale
and scale...
Harry Sanders
Helper

An experienced member who is always happy to help.

Love the concept of 80-20'ing your SEO, I think it's so important and something a lot of SEO professionals overlook. When working with a lot of different accounts you want to make sure that you are absolutely maximising your time focused on things that are going to bring clients benefit. Love the importing .xml trick!
Ross Tavendale
columnist

SEMrush columnists are authors who had proved their expertise in digital marketing and contribute regularly to our community.

Harry Sanders
Hey Harry - i love the Pareto principle of 80/20. I first learned it from Tim Ferriss of all people. We literally now sit and spend hours automating things to save 10 mins here and there - it all adds up over time.
Enthusiast

Occasionally takes part in conversations.

That is some beautiful work with the Unique formula Ross. Be nice to setup as a datasource too for Datastudio. I can see several applications for client reporting to. Comparisons with of page values that have duplicates etc. Good stuff
Ross Tavendale
columnist

SEMrush columnists are authors who had proved their expertise in digital marketing and contribute regularly to our community.

Jim Stewart
Hey Jim! Love the unique formula. Datastudio would be a great shout. I'm sure there are some additional layers of optimisation that we could run to make this even faster.

Send feedback

Your feedback must contain at least 3 words (10 characters).

We will only use this email to respond to you on your feedback. Privacy Policy

Thank you for your feedback!