How To Quantify Link Earning
After a brief time of silence in the site, it’s time to start again with something relatively easy. The purpose of this task is to show in a very easy way to quantify and demonstrate the impact of link earning – as part of your SEO strategy- in terms of traffic and revenue.
Most companies need to know what the impact of link acquisition is and they need to know if what they get back in return is worthy of the budget they spend.
This spreadsheet is really basic, but it gives you an idea of what a spreadsheet can do. Namely you can get data from different sources like Moz, Majestic, Ahrefs, Google Anaytics etc and match them all on a spreadsheet.
How to quantify data – Process
How do we do this? We combine three tools:
- Link analysis tool (e.g. Buzzstream, Ahrefs, Open Site Explorer, Majestic etc)
- Google Analytics (Referrals)
- Excel (VLOOKUP)
All you need to do is to add the Referral Analytics data in one tab with your traffic and transnational data and in the other tab you can create your mastersheet with all the processed data.
You use VLOOKUPs to match your data and in order to avoid the ugly N/A in the spreadsheet you create an IFERROR function and voila! For example, that’s how your standard VLOOKUP will look like, when you want to match your data:
Obviously all these figures and data are imaginary, thanks to the RANDBETWEEN function.
Like I aforementioned, this spreadsheet scratches only the surface and it gives you an idea of what you can do with a spreadsheet once you nicely match your data from different link analysis tools.
Until next time, keep on excelling.