How to Create World Link Maps With Majestic SEO & Google Spreadsheets

4 Jun

Managers love visuals. Nice charts, infographics or pivot tables make excellent excuses for not having to actually *read* those long reports or documents. And since link building reports tend to be packed with data, they love to see something visually appealing every few pages to give their eyes some rest.

A possible chart that could spice up your SEO or link building document, is a World Link Map, which displays your link profile on a world heatmap. And although this may sound like a lot of work, it’s actually quite easy. All you need is Majestic SEO and Google Spreadsheets.

Step 1: Get the data

Create an Advanced Report of the website who’s link profile you’d like to see in a World Link Map via Majestic SEO. Select ‘Countries’ in the Report Menu and export this report to csv.

Besides using Majestic’s ‘Countries’ info, which uses the IP adress of a website to determine the country of origin, you can also use the TLD data that Majestic provides. Although such a report is more accurate for ccTLDs, it is also a bit more work to create a World Link Map with this data.

Step 2: Copy, paste & ISO codes

Now you can copy the first five columns and paste them into Google Spreadsheets (or delete the other columns if you have opened the csv file from Google Spreadsheets directly). Now insert one column between the first (Country) and the second (RefDomains) column, which you can title ‘Country code’, or something similar.

In order to let Google create a nice map of the world, you will have to add the ISO Country Codes of all countries into this column. This is not my favorite step in this process, but adding the ISO Country Codes to the country csv export is already on Majestic SEO’s feature request list :) Until then, if you’re planning on creating multiple maps, I’d suggest taking a look at using the QUERY formula.

Step 3: Create a World Link Map

Right-click anywhere in your spreadsheet, and select ‘Insert Gadget’. After clicking on ‘Maps’, select the option ‘Heatmap’.

Now you can select the field range and the map region, before you hit ‘Apply and close’. The result is a small map, which displays your link profile. Light colored countries indicate a low level of links, while you get linked a lot from bright colored countries. Grey countries indicate no links at all.

Some examples
These World Link Maps can highlight interesting situations. For example, the relatively high amount of links from Polish websites is a bit strange for a Scottish website.

And this African News website seems to get links from anywhere but Africa…

You can take things even further, by dividing the amount of links by (for example) the spent marketing budget in certain countries, population, or any other data.

The map below shows the link profile of the earlier mentioned African News website, but with the amount of links per country weighed against the amount of internet users in that country.

As you can see, there’s quite a lot you can do with link data and a map of the world, besides ‘just creating pretty maps’. While creating a World Link Map may not be very useful for most SMB websites, link visualizations like this may reveal very interesting overviews for large and/ or international websites.

19 Responses to “How to Create World Link Maps With Majestic SEO & Google Spreadsheets”

  1. Mark Hannant June 4, 2010 at 10:24 am #

    NB: Gagdets don’t work in the new Spreadsheet beta – you’ll need to revert to the old version.

  2. Wiep June 4, 2010 at 11:11 am #

    Thanks for mentioning that, Mark. We’ll have to stick to the current version then :)

  3. richardbaxterseo June 4, 2010 at 1:44 pm #

    Really nice idea, Wiep!

  4. Sean June 4, 2010 at 2:53 pm #

    Great post Wiep. Would be great to see if we could get link data by state/prov. It would also be interesting to show a client their link “footprint”, could help with gaining relevance for a region.

  5. Wiep June 4, 2010 at 3:04 pm #

    @Richard: Thanks :)

    @Sean: That would be possible, but it would require some more steps than listed above. The problem is that the IP location is usually not the same as the location of the company behind the website. This would require building a crawler that tries to determine the actual location (or manually checking this), preferably followed by matching this with the lat/long of these addresses, as extra steps in the process. You can then use a service like http://maker.geocommons.com/ or http://www.heatmapapi.com/ to create even better looking maps :)

  6. Kristen June 6, 2010 at 1:26 pm #

    Google spreadsheets helps many places

  7. David June 6, 2010 at 7:32 pm #

    That is awesome, might be a good way to show a client all those cheap links are actually not really relevant, but could also be a good way for news sites to start to relate the geographic links to actual site traffic.

  8. Davide Corradi June 6, 2010 at 8:49 pm #

    Awesome! Working with multilingual International projects I found this idea incredibly useful! Thanks for sharing!

  9. Brighton SEO June 15, 2010 at 2:03 am #

    amazing stuff, thank you very much this is very helpful.

  10. Toby Jones June 17, 2010 at 2:08 pm #

    That’s a really well tought plan, never really cared much for google spreadsheets. Helps me alot since I just started a project involving alot of geolocation stuff. :)

  11. Emm Jay July 1, 2010 at 7:14 pm #

    Brilliant! The last time I heard of custom reports that included Google maps was when I studied a computer course online at Harvard U..

    I’m sure this will be a fun and cool report. Thanks!

  12. Bence July 13, 2010 at 11:44 am #

    Hi Wiep,

    did you actually test the backlink URLs reported by Majestic SEO? Because I have a script which checks the reported backlink domains, whether they actually have a backlink to my website, or not. For example, if a tool like Majestic SEO reports that the domain xyz.com has a backlink to my website, my script actually visit xyz.com and finds the backlink in the HTML source code.

    90% failure rate is not uncommon. For example, I checked 5 domains. According to Majestic, these domains have 3000 referring domains… After I checked all the reported backlink URLs, I found out that these domains have only 24 referring domains…

    Because Majestic shows a lot of outdated data.

    In other cases, the failure rate is about 50%. Majestic desperately needs a built-in link checker!

    I am very disappointed after reading your Link building tools review. For example, you wrote at http://wiep.net/link-building-tools/majestic-seo/ :
    “One of the most accurate link data sources available”

    Lol. You clearly haven’t tested it.

    I don’t like your reviews, they tell nothing. You should compare the reported backlinks of each tool. But the ACTUAL, REAL backlinks, not fake data. Use a script which checks the reported links.

    We need hard numbers, not just vague statements like “yeah, this is a very good link building tool”.

    So I really cannot understand how can you rate link reporitng services, if you don’t actually COMPARE the reported data, and check it yourself? I thought you are serious about link building.

  13. Wiep July 13, 2010 at 12:19 pm #

    @Bence: Of course I test Majestic’s backlink data, as I use Majestic on a daily basis. I never said that it is 100% accurate, but I do think that Majestic offers the most accurate set of data available at the moment.

    Make sure that you have excluded all deleted links (which Majestic keeps track of as well) from your reports. If you still get accuracy rates 24/3000 -which I doubt- I’d advise you to contact Majestic directly and to show them your example.

    As for comparing backlink data; InfluenceFinder’s John Straw has tried this, with http://www.slideshare.net/influencefinder/straw-john-linkbuilding-outside-of-the-box-updated-4136049 (sheet #6) as a result. All backlink data sets have some overlap, but offer unique data as well. As I said, not one tool is 100% accurate, which is why I don’t rely on just one tool.

  14. Dan Horton SEO Consultant July 26, 2010 at 1:57 pm #

    Thanks very much for the info. Iv’e been working on Geo-targeting and found what you say to ring true.

  15. Jeffery Jones July 31, 2010 at 1:42 pm #

    Hey, i also have some ideas

    Will submit them later at the weekend just have to much work to do to think abouth it yet.

    Is it also possible to submit 2 new ideas ?

Trackbacks and Pingbacks

  1. SearchCap: The Day In Search, June 4, 2010 - June 4, 2010

    [...] How to Create World Link Maps With Majestic SEO & Google Spreadsheets, wiep.net [...]

  2. links for 2010-06-05 « burningCat - June 5, 2010

    [...] How to Create World Link Maps With Majestic SEO & Google Spreadsheets [...]

  3. Weekly Search & Social News: 06/08/2010 | Search Engine Journal - June 8, 2010

    [...] How to Create World Link Maps With Majestic SEO & Google Spreadsheets – what can I say? Wiep never fails to offer great stuff when he gets down to it. Lesson; don’t just blog for the sake of doing it, have a purpose. [...]

  4. iPad Competition Winner and Highly commended - July 2, 2010

    [...] to Weip, for his web post on creating geo-heatmaps based on link data. He is about to be the proud owner of a spanking new iPad, just as soon as we work out how to get [...]

Liked this post? Share it!

del.icio.usFacebookStumbleUponTwitter