Restore deep link URLs into shallow link URLs with Excel
This is a piece of admin annoyance that plagues most of us who deal with database upon database for link building campaigns. Picture yourself working on a link building campaign: let’s pretend it is a UK-only campaign where a client only wants links from strictly UK websites – meaning, they should either:
1. Be hosted in the UK
2. Have a .uk domain
3. Have content that indicates a UK readership
Most people would head straight for Google to start building up a comprehensive list and spend hour upon hour quality-scoring websites that fit the brief – not exactly my favourite way to spend a sunny Friday.
If you’ve worked in SEO for a number of years, chances are you should have a links database teeming with resources from previous clients that will inevitably fit future briefs as well. If you don’t possess one, you should seriously consider either developing one internally or possibly finding an online service. It’s worth pointing out that building one is a piece of cake, if you have a developer in-house or working for you as a contractor.
Right, so you have a links database with dozens of live and past clients and your database enables you to export them to Excel – if not, buy your developer a drink and get them to make that happen.
Instead of wasting away your Friday looking for resources, recycle your existing ones! ‘But they’re all deep links with tons of client information!’ I hear you cry. Fear not intrepid reader, here’s a way you can effectively “reset” all your URLs back to their virgin state – as if you had just found them, turn it around in a few minutes and impress your clients with the speedy delivery.
We start from the assumption you are able to quickly export lists of links to Excel. Once you have identified a past or live client that fits the brief of your new client, export all the link activity for that running account onto Excel. In my case, it looks vaguely familiar to this:
It’s just a long list of deep linked URLs, riddled with client information and the inner workings of tons of websites – not really the most ideal way to impress a new client. Here’s how you make that list look thought-through and essentially neat-looking.
You’re on Excel with that list.
Type “Ctrl+F” and write this down:
The only ‘arduous’ task of this exercise is remembering all the domains that exist in your list, which should be a mix of .com, .co.uk, .net, .info, .org.uk, etc. Asterisk (*) acts as a regular expression for “whatever comes after that point.”
Now switch to the “Replace” tab on that same “Find” window.
Now ask Excel to replace .com/* for .com/ – this will convert all deep link URLs into domain-level URLs.
Once you “Replace”a ll. Your list should now look like this:
Now you have a ready source of potential websites to contact, with PageRank and whatever else your links database can export (e.g., website type, etc.) – all tidied up and sleek-looking enough to look like a decent deliverable.